本篇文章，将讲述数据库运行状态数据采集的脚本，如何配置和使用？
n 系统环境
操作系统环境：CentOS release 5.4 (Final)
MySQL版本：5.1.40-community-log，5.5.15-log
需要的数据库表结构：
|
1
2
3
4
5
6
7
8
9
10
|
CREATE TABLE `performance_innodb` ( `ID` bigint(20) unsigned NOT NULL auto_increment, `host_ip` varchar(20) NOT NULL default '', `host_port` smallint(5) unsigned NOT NULL default '0', `statu_item` varchar(50) NOT NULL default '', `total_num` bigint(20) unsigned NOT NULL default '0', `CreateDate` timestamp NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`ID`), KEY `idx_ip_port_cdate` (`host_ip`,`host_port`,`CreateDate`)) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
CREATE TABLE `performance_innodb_log` ( `ID` bigint(20) unsigned NOT NULL auto_increment, `host_ip` varchar(20) NOT NULL default '', `host_port` smallint(5) unsigned NOT NULL default '0', `statu_item` varchar(50) NOT NULL default '', `total_num` bigint(20) unsigned NOT NULL default '0', `CreateDate` timestamp NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`ID`), KEY `idx_ip_port_cdate` (`host_ip`,`host_port`,`CreateDate`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `performance_tmp` ( `host_ip` varchar(20) NOT NULL default '', `host_port` smallint(5) unsigned NOT NULL default '0', `statu_item` varchar(50) NOT NULL default '', `total_num` bigint(20) unsigned NOT NULL default '0', `CreateDate` timestamp NOT NULL default '0000-00-00 00:00:00', KEY `idx_ip_port` (`host_ip`,`host_port`)) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
业务的原因，生产环境采用一台物理服务器部署多个实例的MySQL数据库环境为主，为此我们主要是先默认把采集的状态数 据写入到服务器上3306端口上运行的MySQL数据库实例test数据库中，多个实例的状态数据存储在一起，便于统计分析和再转移到集中的数据库服务器 上，再利用其它软件绘制成图表，便于我们集中管理和分析。
若你们的生产环境为一台一个实例的模式，且数据库服务器都在一个局域网中，则可以考虑把每台数据库服务器上采集的状态数 据，直接写入到集中管理平台的数据库服务器中，同时调度的脚本也可以考虑放集中管理平台服务器上运行，每个脚本对应一台数据库服务器的方式，那么数据采集 脚本的数据写入代码段则需要做适当修改，把代码段：
if [ 3306 -eq "$MYSQL_PORT_START" ] ; then
MY_USER=$MYSQL_USER
MY_PASSWORD=$MYSQL_PASSWORD
MY_SOCK=$MYSQL_SOCK
fi
独立到while循环段外面，并且读单独的一个配置文件解决。
n 数据采集脚本的配置项
1) 采集的数据项
VARIABLES=(Connections Queries Questions Uptime Com_insert Com_insert_select Com_delete Com_delete_multi Com_select Com_update Com_update_multi Com_rollback Com_commit Slow_queries Sort_range Sort_rows Sort_scan Qcache_free_blocks Qcache_free_memory Qcache_hits Qcache_inserts Qcache_lowmem_prunes Qcache_not_cached Qcache_queries_in_cache Key_blocks_used Key_blocks_unused Key_read_requests Key_reads Key_write_requests Key_writes Max_used_connections Bytes_sent Bytes_received Aborted_connects Created_tmp_files Created_tmp_disk_tables Created_tmp_tables Innodb_buffer_pool_read_ahead_rnd Innodb_buffer_pool_read_ahead_seq Innodb_buffer_pool_read_requests Innodb_buffer_pool_reads Innodb_buffer_pool_wait_free Innodb_buffer_pool_write_requests Innodb_rows_deleted Innodb_rows_inserted Innodb_rows_read Innodb_rows_updated)
上述内容为需要采集的状态数据项，若不同MySQL版本会增加或减少参数项，甚至变更参数名称，可以针对你需要的参数进行添加或删除，只要变更此参数的值即可。
NOT_DIFF_VARIABLES参数的值为：部分参数值不是累计型统计值，而是某一个状态的实时值，则不需要进行采集数据项的差值计算，我们上述列的采集项中，有三个不需要进行计算的状态项：
NOT_DIFF_VARIABLES=(\’Max_used_connections\’,\’Qcache_free_memory\’,\’Qcache_free_blocks\’)
2) 其他配置
MYSQL_PORT_START=3306
— 需要进行采集的MySQL数据库实例最小端口号，也是采集数据存储的MySQL实例端口号；
MYSQL_PORT_END=3308
— 需要进行采集的MySQL数据库实例最大端口号，最小端口号和最大端口号，决定需要采集的MySQL数据库实例个数；
DB_NAME=test — 存储数据库实例状态数据的MySQL数据库实例的数据库名称；
3) 脚本执行的参数
sh mysql_performance_status.sh –space-of-time=5
其中，–space-of-time=5 表示每个五分钟执行一次，那么c rontab调度任务对应的配置项目为：
*/5 * * * * /data/bin/mysql_performance_status.sh –space-of-time=5 > /dev/null 2>&1
n 数据采集脚本代码
#!/bin/sh
#Author: Eugene
#Description: every N minutes to get mysql status’s value and import to database
#Example: ./mysql_performance_status.sh –space-of-time=5
#WebSite:mysqlops
#weibo:www.weibo.com/mysqlops
#Create_Time: 2011-09-27 16:00:00
#ALter_Time : 2011-10-11 18:00:00
BASE_DIR=/data
STATUS_DIR=/data/backup
MYSQL_PORT_START=3306
MYSQL_PORT_END=3308
DB_NAME=test
Curdatetime=`date +%Y%m%d%H%M%S`
Curdate=`date +%Y%m%d`
HOST_IP=`/sbin/ifconfig | grep “inet addr” | awk -F: ‘{print $2}’ | awk {‘print $1′} | head -1`
VARIABLES=(Connections Queries Questions Uptime Com_insert Com_insert_select Com_delete Com_delete_multi Com_select Com_update Com_update_multi Com_rollback Com_commit Slow_queries Sort_range Sort_rows Sort_scan Qcache_free_blocks Qcache_free_memory Qcache_hits Qcache_inserts Qcache_lowmem_prunes Qcache_not_cached Qcache_queries_in_cache Key_blocks_used Key_blocks_unused Key_read_requests Key_reads Key_write_requests Key_writes Max_used_connections Bytes_sent Bytes_received Aborted_connects Created_tmp_files Created_tmp_disk_tables Created_tmp_tables Innodb_buffer_pool_read_ahead_rnd Innodb_buffer_pool_read_ahead_seq Innodb_buffer_pool_read_requests Innodb_buffer_pool_reads Innodb_buffer_pool_wait_free Innodb_buffer_pool_write_requests Innodb_rows_deleted Innodb_rows_inserted Innodb_rows_read Innodb_rows_updated)
NOT_DIFF_VARIABLES=(\’Max_used_connections\’,\’Qcache_free_memory\’,\’Qcache_free_blocks\’)
if [ ! -d "$STATUS_DIR" ] ; then
mkdir -p “$STATUS_DIR”
chown -R mysql:mysql “$STATUS_DIR”
fi
INPUT_DATA=$1
usage ()
{
cat <<EOF
Usage: $0 [OPTIONS]
–space-of-time=N every N minutes to get mysql status’s value,For example: –space-of-time=5;
EOF
exit 1
}
case “${INPUT_DATA}” in
–space-of-time=*)
SPCE_TIME=`echo “$INPUT_DATA” | sed -e “s;–[^=]*=;;”`
if [ -z "$SPCE_TIME" ] ; then
usage
fi
;;
*)
usage
;;
esac
shift
SPCE_TIME=`expr $SPCE_TIME + 1`
while [ "$MYSQL_PORT_START" -le "$MYSQL_PORT_END" ]
do
#induct MySQL’s USERNAME AND PASSWORD
F_PASS=”$BASE_DIR”/conf/.mysql_info.”$MYSQL_PORT_START”
if [ -f $F_PASS ] ; then
. $F_PASS
if [ 3306 -eq "$MYSQL_PORT_START" ] ; then
MY_USER=$MYSQL_USER
MY_PASSWORD=$MYSQL_PASSWORD
MY_SOCK=$MYSQL_SOCK
fi
MY_STATUS=”$STATUS_DIR”/status_”$MYSQL_PORT_START”.txt
mysql -u$MYSQL_USER -p$MYSQL_PASSWORD –socket=$MYSQL_SOCK -e “SHOW GLOBAL STATUS;”>”$MY_STATUS”
#add ip address to text
sed -i “s/$/\t”$HOST_IP” /” “$MY_STATUS”
#add port to text
sed -i “s/$/\t”$MYSQL_PORT_START” /” “$MY_STATUS”
#UPTIME_VALUE=`cat “$MY_STATUS” | grep “Uptime” | awk ‘{print $2}’`
if [ -f "$MY_STATUS".tmp ] ; then
rm -f “$MY_STATUS”.tmp
fi
for var in ${VARIABLES[@]}
do
cat $MY_STATUS | grep -w “$var” >> “$MY_STATUS”.tmp
done
rm -f “$MY_STATUS”
cat “$MY_STATUS”.tmp > “$MY_STATUS”
rm -f “$MY_STATUS”.tmp
strSQL=”LOAD DATA INFILE ‘$MY_STATUS’ INTO TABLE performance_tmp(statu_item,total_num,host_ip,host_port);”;
mysql -u$MY_USER -p$MY_PASSWORD –socket=$MY_SOCK -D “$DB_NAME” -e “DELETE FROM performance_tmp WHERE host_ip=’”$HOST_IP”‘ AND host_port=”$MYSQL_PORT_START”;”
mysql -u$MY_USER -p$MY_PASSWORD –socket=$MY_SOCK -D “$DB_NAME” -e “$strSQL”
mysql -u$MY_USER -p$MY_PASSWORD –socket=$MY_SOCK -D “$DB_NAME” -e “UPDATE performance_tmp SET CreateDate=DATE_FORMAT(“$Curdatetime”,’%Y-%m-%d %H:%i:%s’) WHERE host_ip=’”$HOST_IP”‘ AND host_port=”$MYSQL_PORT_START”;”
mysql -u$MY_USER -p$MY_PASSWORD –socket=$MY_SOCK -D “$DB_NAME” -e “INSERT INTO performance_innodb(statu_item,total_num,host_ip,host_port,CreateDate) SELECT T.statu_item,T.total_num-L.total_num AS CurNum,T.host_ip,T.host_port,T.CreateDate FROM performance_tmp T INNER JOIN performance_innodb_log L ON T.statu_item=L.statu_item WHERE L.CreateDate >=DATE_ADD(T.CreateDate,INTERVAL -”$SPCE_TIME” MINUTE) AND L.CreateDate <=T.CreateDate AND L.host_ip=’”$HOST_IP”‘ AND L.host_port=”$MYSQL_PORT_START” AND T.host_ip=’”$HOST_IP”‘ AND T.host_port=”$MYSQL_PORT_START” AND T.statu_item NOT IN (“$NOT_DIFF_VARIABLES”);”
#Don’t need computer
mysql -u$MY_USER -p$MY_PASSWORD –socket=$MY_SOCK -D “$DB_NAME” -e “INSERT INTO performance_innodb(statu_item,total_num,host_ip,host_port,CreateDate) SELECT T.statu_item,T.total_num,T.host_ip,T.host_port,T.CreateDate FROM performance_tmp T WHERE T.host_ip=’”$HOST_IP”‘ AND T.host_port=’”$MYSQL_PORT_START”‘ AND T.statu_item IN (“$NOT_DIFF_VARIABLES”);”
mysql -u$MY_USER -p$MY_PASSWORD –socket=$MY_SOCK -D “$DB_NAME” -e “INSERT INTO performance_innodb_log(statu_item,total_num,host_ip,host_port,CreateDate) SELECT statu_item,total_num,host_ip,host_port,CreateDate FROM performance_tmp WHERE host_ip=’”$HOST_IP”‘ AND host_port=”$MYSQL_PORT_START”;”
fi
MYSQL_PORT_START=`expr $MYSQL_PORT_START + 1`
done