mysql怎么采集数据_MySQL数据库运行状态数据采集的脚本

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

n  系统环境

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

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

需要的数据库表结构：

业务的原因，生产环境采用一台物理服务器部署多个实例的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

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

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