MySQL dba Script

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
• 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
  

  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
  
  

  A successful installation will result in:
  • 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

  Not run as root

      ./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'
  

  Not specifying an environment directory with -d.

      ./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'
  

  Not specifying a binding IP Address with -i

      ./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'
  

  Specifying an environment directory that already contains an instance

      ./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'
  

  Specifying an environment directory that does not exist.

      /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'
  

  Specify an invaid version of MySQL to install

      ./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