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.
Installationsudo 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
Configurationsu - monitor # -c Create Necessary MySQL Permissions # -u User with SUPER permissions, defaults to root scripts/log-mysql-stats.sh -udba -c
Testingmkdir 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
扫一扫
391
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
