Ben Lobaugh Online

If you need to count the number of tables that exist in a MySQL database you can do so with the following query. Just remember to swap out the database name!

SELECT count(*) 
FROM information_schema.tables 
WHERE table_schema = 'YOUR_DB_NAME'

MySQL has the ability to show you size of the information contained in your database. You can find this information per database or per table in a database pretty easily.

Size of databases

To list the size of all the databases on the server run the following:

SELECT table_schema "Database", sum( data_length + index_length) / 1024 / 1024 
"Size in MB" FROM information_schema.TABLES GROUP BY table_schema;

Size of tables in database

To list the size of all the tables in an individual database run:

SELECT table_name, table_rows, data_length, index_length, 
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES where table_schema = "{{NAME OF YOUR DATABASE}}";

Need to determine who is connected to your MySQL server and how many connections they have open? I tossed together this little script to display the information.

#!/usr/bin/perl

use DBI;
use 5.010;
use Getopt::Long;

# Set defaults
my $host = 'localhost';
my $db = 'information_schema';
my $user = ''; 
my $pass = ''; 

# Get commandline options
GetOptions (
    "host=s"    => \$host,
    "db=s"      => \$db,
    "user=s"    => \$user,
    "pass=s"    => \$pass,
) or die ( "Valid options\n--host\n--user\n--password\n--db database" );

# Create database connection
my $dbh = DBI->connect( "DBI:mysql:$db:$host", $user, $pass, {'PrintError'=>0} )
        or die "** Connection error!\nTry different options:\n--host\n--user\n--password\n--db database\n\n $DBI::         +++errstr\n";

# Get the current processes to check user
my $sql = "show processlist";
my $sth = $dbh->prepare($sql);
$sth->execute() or die "SQL error: $DBI::errstr\n";

# Create an array of all the users
my %user;
while (my @row = $sth->fetchrow_array()) {
    $user{$row[1]}++;
}

# Display User details
print "MySQL - Logged in users\n";
print "=" x 15, "\n";

for my $x (keys %user) {
    print "$x - $user{$x} connections\n";
}


print "\n";