MySQL DBA Tools - MySQL Expert

The essential MySQL DBA tools, scripts and SQL that MySQL Expert Ronald Bradford uses day to day for MySQL Database Administration, MySQL Performance Tuning and MySQL Architecture.

MySQL DBA scripts

The following various scripts as used for various different tasks when reviewing MySQL installations.

Calculate Your MySQL Database Size
Calculate Your Individual MySQL Schema Size
Log various MySQL statistics
Analyze MySQL statistics
Gather Long/Locked MySQL Sessions
Automated MySQL Installation

Calculate Your Total MySQL Database Size

I use the following SQL script as an audit of for each current MySQL Database Instance. You can use this for example on a daily basis to get an indication of the overall growth of your database.
This SQL uses the MySQL Information_schema that is available for MySQL Version 5.0 and higher.
For those using MySQL 4.x, using the CHECK TABLE STATUS for each schema and manually computing the same produces a similar result.

Usage

$ wget http://ronaldbradford.com/mysql-dba/allschemas.sql
$ mysql -u[user] -p  -v -v -v < allschemas.sql

Output

The following as sample outputs from clients.

+--------------------+-----------------+-----------------+-----------------+--------+
| table_schema       | total_mb        | data_mb         | index_mb        | tables |
+--------------------+-----------------+-----------------+-----------------+--------+
| xxxxxx_xxx         | 993088.84375000 | 776273.18750000 | 216815.65625000 |     54 |
| information_schema |      0.00390625 |      0.00000000 |      0.00390625 |     17 |
+--------------------+-----------------+-----------------+-----------------+--------+
2 rows in set (59.81 sec)

+----------------------------+----------------+----------------+---------------+--------+
| table_schema               | total_mb       | data_mb        | index_mb      | tables |
+----------------------------+----------------+----------------+---------------+--------+
| xxxxxxx_xxx_xxxx_xxx       | 45314.47730350 | 38458.88941288 | 6855.58789063 |   2359 |
| xxxxxxx_xxx_xxxx_xxx       | 28758.38682079 | 24461.27060986 | 4297.11621094 |    275 |
| xxxxxxx_xxx_xxxx_xxx       | 28732.41495800 | 24464.20304394 | 4268.21191406 |    368 |
| xxxxxxx_xxx_xxxx_xxx       | 24586.48277569 | 20941.44176006 | 3645.04101563 |    302 |
| xxxxxxx_xxx_xxxx_xxx       |  3128.63515377 |  2664.54726315 |  464.08789063 |     48 |
| xxxxxxx_xxx_xxxx_xxx       |  2865.36613274 |  2440.44328117 |  424.92285156 |    265 |
| xxxxxxx_xxx_xxxx_xxx       |  1635.16585350 |  1388.68831444 |  246.47753906 |   2034 |
| xxxxxxx_xxx_xxxx_xxx       |  1442.15766239 |  1231.41840458 |  210.73925781 |     17 |
...
+----------------------------+----------------+----------------+---------------+--------+
74 rows in set (20.56 sec)

Calculate Your Individual Schema Size

Usage

$ wget http://ronaldbradford.com/mysql-dba/perschema.sql
$ mysql -u[user] -p -v -v -v [table-schema] < perschema.sql

Output

+------------------+--------+------------+------------+----------------+--------------+--------------+--------------+
| table_name       | engine | row_format | table_rows | avg_row_length | total_mb     | data_mb      | index_mb     |
+------------------+--------+------------+------------+----------------+--------------+--------------+--------------+
| xxxxxxx          | InnoDB | Compact    |    1778523 |            314 | 658.39062500 | 533.84375000 | 124.54687500 |
| xxxxxxxxx        | InnoDB | Compact    |     553266 |            846 | 472.25000000 | 446.75000000 |  25.50000000 |
| xxxxxxx          | InnoDB | Compact    |     435892 |            884 | 392.25000000 | 367.81250000 |  24.43750000 |
| xxxxxxxxxxxxxx   | InnoDB | Compact    |    1106547 |             65 | 133.26562500 |  68.59375000 |  64.67187500 |
| xxxxxxxxxxxxxxxx | InnoDB | Compact    |      58281 |            531 |  30.34375000 |  29.51562500 |   0.82812500 |
| xxxxxxxxxx       | InnoDB | Compact    |      68721 |            298 |  28.12500000 |  19.54687500 |   8.57812500 |
...

NOTE: The table_rows and corresponding avg_row_length is only approximate for Innodb tables. These values under certain circumstances can be wildly inaccurate. The data and index size are considered very accurate.

Logging MySQL Stats

Usage

$ wget http://ronaldbradford.com/mysql-dba/log-mysql-stats.sh
$ chmod +x log-mysql-stats.sh
$ export LOG_DIR=`pwd`
$ export MYSQL_AUTHENTICATION="-umonitor -psakila"
$ ./log-mysql-stats.sh variables
$ ./log-mysql-stats.sh status
$ ./log-mysql-stats.sh processlist
$ ./log-mysql-stats.sh innodbstatus
$ ./log-mysql-stats.sh masterstatus
$ ./log-mysql-stats.sh slavestatus

Output

$ more mysql.variables.090629.003542.txt 
| date_time | 090629.003542 |
| timezone | -0400 |
+---------------------------------+-----------------------------------------------------------+
| Variable_name                   | Value                                                     |
+---------------------------------+-----------------------------------------------------------+
| auto_increment_increment        | 1                                                         |
| auto_increment_offset           | 1                                                         |
| autocommit                      | ON                                                        |
| automatic_sp_privileges         | ON                                                        |
| back_log                        | 50                                                        |
| basedir                         | /opt/mysql-5.1.25-rc-linux-x86_64-glibc23/                |
| big_tables                      | OFF                                                       |
| binlog_cache_size               | 32768                                                     |
| binlog_format                   | MIXED                                                     |
| bulk_insert_buffer_size         | 8388608                                                   |
...

$ more mysql.status.090629.003747.txt 
| date_time | 090629.003747 |
| timezone | -0400 |
+-----------------------------------+-------------+
| Variable_name                     | Value       |
+-----------------------------------+-------------+
| Aborted_clients                   | 4           |
| Aborted_connects                  | 6037        |
| Binlog_cache_disk_use             | 0           |
| Binlog_cache_use                  | 0           |
| Bytes_received                    | 2303295323  |
| Bytes_sent                        | 50239245119 |
| Com_admin_commands                | 0           |
| Com_assign_to_keycache            | 0           |
| Com_alter_db                      | 0           |
| Com_alter_db_upgrade              | 0           |
| Com_alter_event                   | 0           |
| Com_alter_function                | 0           |
| Com_alter_procedure               | 0           |
| Com_alter_server                  | 0           |
| Com_alter_table                   | 59          |
| Com_alter_tablespace              | 0           |
...

To implement logging in the real world.

Installation

sudo su -
useradd -m -s /bin/bash monitor
mkdir /home/monitor/scripts
cd /home/monitor/scripts
wget http://ronaldbradford.com/mysql-dba/log-mysql-stats.sh
chmod +x log-mysql-stats.sh
mkdir -p /somedir/mysqlstatlogs
chown monitor:monitor /somedir/mysqlstatlogs
chown -R monitor:monitor /home/monitor

Configuration

su - monitor
# -c Create Necessary MySQL Permissions
# -u User with SUPER permissions, defaults to root
scripts/log-mysql-stats.sh -udba -c

Testing

mkdir tmp
LOG_DIR=tmp scripts/log-mysql-stats.sh -m all
ls -l tmp
rm -rf tmp

Operation

# m h  dom mon dow   command
* * * * * LOG_DIR="/somedir/mysqlstatlogs" /home/monitor/scripts/log-mysql-stats.sh -m -l status innodbstatus masterstatus slavestatus
0 * * * * LOG_DIR="/somedir/mysqlstatlogs" /home/monitor/scripts/log-mysql-stats.sh -m -l processlist
0 0 * * * LOG_DIR="/somedir/mysqlstatlogs" /home/monitor/scripts/log-mysql-stats.sh -m -l variables
0 0 * * * LOG_DIR="/somedir/mysqlstatlogs" /home/monitor/scripts/log-mysql-stats.sh -z

Analyze MySQL statistics

$ wget -O mysql-stats.sh http://ronaldbradford.com/mysql-dba/mysql-stats.sh
$ chmod +x mysql-stats.sh
$ export LOG_DIR=`pwd`
$ ./mysql-stats.sh all

Gather Long/Locked MySQL Sessions

Usage

$ wget -O mysql-sessions.sh http://ronaldbradford.com/mysql-dba/mysql-sessions.sh
$ chmod +x ./mysql-sessions.sh
$ export MYSQL_AUTHENTICATION="-uusername -ppassword"
$ ./mysql-sessions.sh

Under normal operations, the log file will capture any SQL statements found via SHOW FULL PROCESSLIST when they are caught in action. Generally this is a few queries per sample. This is a list of SQL that you may want to analyze for slow queries for example.

When Locking is found after a timeout (currently 5 seconds), all statements from the processlist are logged to a seperate log file. This enables you to look for the blocker that is causing the locking.

Install MySQL

Usage

$ wget -O meta.sh http://ronaldbradford.com/mysql-dba/meta.sh
$ wget -O install-mysql.sh http://ronaldbradford.com/mysql-dba/install-mysql.sh
$ chmod +x ./install-mysql.sh
$ sudo ./install-mysql.sh -d [/environment] [-i [ip-address]]

where:

-d [/environment] is the base directory of the environment in question, for simplicity, use a base directory (e.g. from /) with the environment name. NOTE: Script is untested for directories that are not root level '/' directories.
-i [ip] is a virtual IP that is assigned to the database environment

Pre-requisites

This script must be run as the 'root' user, as it creates files and sets appropriate permissions.
The base environment directory (e.g. /[environment]) must exist and be empty
The Installed MySQL binary version must be in the /software directory
The meta.sh file is required in the same directory as the install_mysql.sh file

Specific example

$ sudo su -
$ mkdir /software /db1
$ cd /software
$ wget http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.37-linux-x86_64-glibc23.tar.gz/from/http://mirror.trouble-free.net/mysql_mirror/
$ mkdir /db1
$ ./install-mysql.sh -d /db1

Example Output

# ./install-mysql.sh -d /db1
16:50:55 INFO  Starting installation of MySQL
16:50:55 WARN  IP not specified with -i, not binding to any IP
16:50:55 WARN  No MySQL Version specified, using '5.1'
16:50:55 INFO  Installing MySQL . . .
16:50:55 INFO  Creating default MySQL user
16:50:55 INFO  Group 'mysql' already exists
16:50:55 INFO  User 'mysql' already exists
16:50:55 INFO  Installing MySQL from binary tar file '/software/mysql-5.1.37-linux-x86_64-glibc23.tar.gz
16:51:03 INFO  Setting MYSQL_HOME to '/db1/mysql'
16:51:03 INFO  Installing mysql starter database
16:51:13 INFO  Setting mysql directory ownership & permissions
16:51:13 INFO  Configuring MySQL
16:51:13 INFO  Setting MYSQL_HOME to '/db1/mysql'
16:51:13 INFO  Generating my.cnf
16:51:13 INFO  Configuring services for 'mysql_db1'
16:51:15 INFO  Defining User Permissions
16:51:15 INFO  Completed installation of MySQL

MySQL installed and running in /db1

$ export MYSQL_HOME=/db1/mysql
$ $MYSQL_HOME/bin/mysql -udba -psakila -h${IP} -e "SELECT VERSION();SHOW SCHEMAS;"
+------------+
| VERSION()  |
+------------+
| 5.1.37-log | 
+------------+
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
| test_db1           | 
+--------------------+

Common Errors

    ./install_mysql.sh
    14:11:05 INFO  Starting installation of MySQL
    14:11:05 ERROR This script must be run as root
    14:11:05 INFO  Exiting with status code of '1'

    ./install_mysql.sh
    14:14:16 INFO  Starting installation of MySQL
    14:14:16 ERROR You must specify an install directory with -d
    14:14:16 INFO  Exiting with status code of '1'

    ./install_mysql.sh -d /xxx
    14:30:45 INFO  Starting installation of MySQL
    14:30:45 ERROR You must specify a binding IP with -i
    14:30:45 INFO  Exiting with status code of '1'

    ./install_mysql.sh -d /qa -i 192.168.1.22
    14:33:33 INFO  Starting installation of MySQL
    14:33:33 WARN  No MySQL Version specified, using '5.1'
    14:33:33 ERROR A MySQL Instance at '/qa/mysql' has been detected, ensure no MySQL instance exists at '/qa'
    14:33:33 INFO  Exiting with status code of '1'

    /install_mysql.sh -d /xxx -i 192.168.1.22
    14:34:29 INFO  Starting installation of MySQL
    14:34:29 WARN  No MySQL Version specified, using '5.1'
    14:34:29 ERROR Base deployment directory '/xxx' does not exist
    14:34:29 INFO  Exiting with status code of '1'

    ./install_mysql.sh -d /xxx -i 192.168.1.22 -v 5.2
    14:37:34 INFO  Starting installation of MySQL
    14:37:34 ERROR Unable to find a '5.2' version of MySQL in '/software/'
    14:37:34 INFO  Exiting with status code of '1'

Unsupported Scripts

$ wget http://ronaldbradford.com/mysql-dba/mysql-sessions.sh