MySQL数据库运行状态数据采集的脚本

最新推荐文章于 2024-11-08 18:30:00 发布

爱不到要偷

最新推荐文章于 2024-11-08 18:30:00 发布

阅读量117

点赞数

文章标签：数据库 awk 操作系统

原文链接：http://www.cnblogs.com/ylqmf/archive/2011/10/18/2216267.html

版权

本篇文章，将讲述数据库运行状态数据采集的脚本，如何配置和使用？

n 系统环境

操作系统环境：CentOS release 5.4 (Final)

MySQL版本：5.1.40-community-log，5.5.15-log

需要的数据库表结构：

 
         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;

 
         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

独立到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`

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”

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

;;

usage

;;

esac

shift

SPCE_TIME=`expr $SPCE_TIME + 1`

while [ "$MYSQL_PORT_START" -le "$MYSQL_PORT_END" ]

#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

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

for var in ${VARIABLES[@]}

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”;”

MYSQL_PORT_START=`expr $MYSQL_PORT_START + 1`

done

转载于:https://www.cnblogs.com/ylqmf/archive/2011/10/18/2216267.html