mysql数据库巡检
TSMYK Java技术编程
前言
shell 中执行 mysql 命令
各项巡检命令
shell 脚本实现
前言
在系统运行的过程中，DBA需要经常的对数据库进行一些检查，如数据库磁盘的占用量，缓存的命中率，内存的分配等；由于有个客户需要对系统的数据库进行检查，所以进行了一些学习，在此记录下；由于不可能让用户手动的输入这些繁琐的命令，所以写了个 shell 脚本。
shell 脚本中连接数据库执行mysql 命令
在 shell 脚本中，去链接数据库，并执行相关的命令的步骤如下：
首先使用 touch 命令创建个文件， 使用 chmod 赋给这个文件执行权限
在文件中输入如下shell：
#!/bin/bash
host="127.0.0.1" #数据库IP
port="3306" #数据库端口
userName="root" #用户名
password="root" #密码
dbname="dbname" #数据库 名称
dbset="--default-character-set=utf8 -A" # 字符集
cmd="show variables like '%datadir%';"
/home/mysql/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${cmd}")
如果要一次执行多个命令，则直接写多条命令就可以了，但是记得要换行，如下所示：
cmd2="show variables like '%datadir%';
show tables;
show databases;"
/home/mysql/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${cmd}")
各项巡检命令
mysql 的数据文件存放的位置
有时候需要知道mysql数据文件的存放位置，此时，可以使用 datadir 变量查看，命令如下：
进入到MySQL的bin目录下，执行如下命令来登陆mysql
./mysql -h127.0.0.1 -uroot -proot
2.然后执行 show variables like '%datadir%'; 或者 elect @@datadir; 命令查看数据文件的存放路径：
shell脚本如下：
#!/bin/bash
host="127.0.0.1" #数据库IP
port="3306" #数据库端口
userName="root" #用户名
password="root" #密码
dbname="dbname" #数据库 名称
dbset="--default-character-set=utf8 -A" # 字符集
datadir="show variables like '%datadir%';"
datadir_val=$(/home/mysql/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${datadir}")
echo "mysql 数据文件存放位置：" `echo ${datadir_val} | cut -d' ' -f4`
其中，“cut -d' ' -f4” 意思是获取到字符串按照空格(‘ ’)进行分割，然后取第 4 个；
查看MySQL中执行次数最多的前 10 条SQL
在MySQL中，要统计执行次数最大的SQL ，需要开启慢查询，通过慢查询日志进行统计，
1.查看是否开启慢查询日志命令：
show variables like '%slow_query%';
其中，slow_query_log 表示是否开启慢查询，OFF表示未开启，ON 表示开启。slow_query_log_file表示慢查询日志的路径。
2.开启慢查询日志
set global slow_query_log=ON;
慢查询是指SQL的执行时间超过一定的秒数之后才算是慢查询，这个时间默认是10秒，可以通过 long_query_time 变量查看，如下：
show variables like '%long_query_time%';
在测试的时候，可以把这个时间设置短一些，可以设置为1秒，0.1秒或者0.01秒都可以，通过如下命令设置：
set global long_query_time=秒数
当设置成功后，再次执行show variables like '%long_query_time%';命令来查看发现还是10秒，这时需要重新退出的，在进行登录，再查看就好了。
当开启慢查询日志后，就可以通过慢查询日志来分析执行次数最多的SQL了。
使用MySQL提供的 mysqldumpslow 工具来进行分析慢查询日志。mysqldumpslow 工具的主要功能是统计不同慢SQL的：
执行次数(count)
执行最长时间(time)
等待锁的时间(lock)
发送给客户端的总行数(rows)
进入到mysql的bin目录下，执行 mysqldumpslow -help 来查看参数，如下：
-s：表示按照哪种方式进行排序，c, t, l, r, 分别表示按照执行次数，执行时间，等待锁时间和返回的记录数来排序，at, al, ar 分别按照平均执行时间，平均等待锁时间和平均发送行数进行排序。
-r：是前面排序的逆序
-t：top n 的意思，即返回排序后前面 n 条的数据
-g：正则匹配
现在可以通过该工具来统计执行次数最多的前 10 条SQL了，命令如下：
# -s c -t 10 表示按照执行次数排序，之后，取前10条
./mysqldumpslow -s c -t 10 /home/datas/mysql/data/R6-slow.log;
查看数据库缓存的命中率
首先看下是否开启了查询缓存：
show variables like '%query_cache%';
其中 query_cache_type为 ON 表示开启查询缓存，OFF 表示关闭缓存
query_cache_size 允许设置的值最小为40K，对于最大值则可以几乎认为无限制，但是，该值并不是越大， 查询缓存的命中率就越高，需要根据情况来定。
开启了查询缓存之后，接下来来看下缓存的相关选项说明：
执行查看命令：
show global status like 'QCache%';
Qcache_free_blocks：目前还处于空闲状态的 Query Cache 中内存 Block 数目
Qcache_free_memory：目前还处于空闲状态的 Query Cache 内存总量
Qcache_hits：Query Cache 命中次数
Qcache_inserts：向 Query Cache 中插入新的 Query Cache 的次数，也就是没有命中的次数
Qcache_lowmem_prunes：当 Query Cache 内存容量不够，需要从中删除老的 Query Cache 以给新的 Cache 对象使用的次数
Qcache_not_cached：没有被 Cache 的 SQL 数，包括无法被 Cache 的 SQL 以及由于 query_cache_type 设置的不会被 7. Cache 的 SQL
Qcache_queries_in_cache：目前在 Query Cache 中的 SQL 数量
Qcache_total_blocks：Query Cache 中总的 Block 数量
此时可以根据这些值进行计算缓存的命中率和缓存的内存使用率
公式：
查询缓存命中率 ≈ (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%
查询缓存内存使用率 ≈ (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
shell脚本计算缓存命中率：
#!/bin/bash
host="127.0.0.1" #数据库IP
port="3306" #数据库端口
userName="root" #用户名
password="root" #密码
dbname="dbname" #数据库 名称
dbset="--default-character-set=utf8 -A" # 字符集
cache_hits="show global status like 'QCache_hits';"
hits=$(/home/mysql/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${cache_hits}")
hits_val=`echo ${hits} | cut -d' ' -f4`
echo "缓存命中次数：" ${hits_val}
cache_not_hits="show global status like 'Qcache_inserts';"
not_hits=$(/home/mysql/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${cache_not_hits}")
not_hits_val=`echo ${not_hits} | cut -d' ' -f4`
echo "缓存未命中次数：" ${not_hits_val}
cache_hits_rate_1=$(($hits_val - $not_hits_val))
cache_hits_rate_2=`echo | awk "{print $cache_hits_rate_1/$hits_val * 100}"`
echo "缓存命中率：" ${cache_hits_rate_2} "%"
执行该脚本，如下所示：
查询等待事件的TOP 10
查询等待事件相关的需要通过 performance_schema 来进行统计，MySQL的 performance schema 主要用于监控MySQL server在一个较低级别的运行过程中的资源消耗、资源等待等.
统计 top 10 的等待事件 SQL 如下：
select event_name, count_star, sum_timer_wait from performance_schema.events_waits_summary_by_user_by_event_name where count_star > 0 order by sum_timer_wait desc limit 10;
shell脚本执行
#!/bin/bash
host="127.0.0.1" #数据库IP
port="3306" #数据库端口
userName="root" #用户名
password="root" #密码
dbname="dbname" #数据库 名称
dbset="--default-character-set=utf8 -A" # 字符集
top_event_10="select event_name, count_star, sum_timer_wait from performance_schema.events_waits_summary_global_by_event_name where count_star > 0 order by sum_timer_wait desc limit 10;"
echo "等待事件 TOP 10："
/home/mysql/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${top_event_10}"
mysql的内存配置情况，
可以通过查看相关的变量来查看mysql内存 分配：
show variables like 'innodb_buffer_pool_size'; //InnoDB 数据和索引缓存
show variables like 'innodb_log_buffer_size'; // InnoDB 日志缓冲区
show variables like 'binlog_cache_size'; // 二进制日志缓冲区
show variables like 'thread_cache_size'; // 连接线程缓存
show variables like 'query_cache_size'; // 查询缓存
show variables like 'table_open_cache'; // 表缓存
show variables like 'table_definition_cache'; // 表定义信息缓存
show variables like 'max_connections'; // 最大线程数
show variables like 'thread_stack'; // 线程栈信息使用内存
show variables like 'sort_buffer_size'; // 排序使用内存
show variables like 'join_buffer_size'; // Join操作使用内存
show variables like 'read_buffer_size'; // 顺序读取数据缓冲区使用内存
show variables like 'read_rnd_buffer_size'; // 随机读取数据缓冲区使用内存
show variables like 'tmp_table_size'; // 临时表使用内存
除了使用 show variables 的方式。还可以使用 select @@xxx 的方式：
shell 脚本：
#!/bin/bash
host="127.0.0.1" #数据库IP
port="3306" #数据库端口
userName="root" #用户名
password="root" #密码
dbname="dbname" #数据库 名称
dbset="--default-character-set=utf8 -A" # 字符集
echo "================= 内存配置情况 ==============================="
mem_dis_1="show variables like 'innodb_buffer_pool_size';"
mem_dis_1_val=$(/home/mysql/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_1}")
mem_dis_1_val_1=`echo ${mem_dis_1_val} | cut -d' ' -f4`
mem_dis_1_val_2=`echo | awk "{print $mem_dis_1_val_1/1024/1024}"`
echo "InnoDB 数据和索引缓存：" $mem_dis_1_val_1
mem_dis_2="show variables like 'innodb_log_buffer_size';"
mem_dis_2_val=$(/home/mysql/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_2}")
mem_dis_2_val_1=`echo ${mem_dis_2_val} | cut -d' ' -f4`
mem_dis_2_val_2=`echo | awk "{print $mem_dis_2_val_1/1024/1024}"`
echo "InnoDB 日志缓冲区：" $mem_dis_2_val_1
mem_dis_3="show variables like 'binlog_cache_size';"
mem_dis_3_val=$(/home/mysql/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_3}")
mem_dis_3_val_1=`echo ${mem_dis_3_val} | cut -d' ' -f4`
mem_dis_3_val_2=`echo | awk "{print $mem_dis_3_val_1/1024/1024}"`
echo "二进制日志缓冲区：" $mem_dis_3_val_1
mem_dis_4="show variables like 'thread_cache_size';"
mem_dis_4_val=$(/home/mysql/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_4}")
echo "连接线程缓存：" `echo $mem_dis_4_val | cut -d' ' -f4`
mem_dis_5="show variables like 'query_cache_size';"
mem_dis_5_val=$(/home/mysql/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_5}")
echo "查询缓存：" `echo ${mem_dis_5_val} | cut -d' ' -f4`
mem_dis_6="show variables like 'table_open_cache';"
mem_dis_6_val=$(/home/mysql/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_6}")
echo "表缓存：" `echo ${mem_dis_6_val} | cut -d' ' -f4`
mem_dis_7="show variables like 'table_definition_cache';"
mem_dis_7_val=$(/home/mysql/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_7}")
echo "表定义缓存：" `echo ${mem_dis_7_val} | cut -d' ' -f4`
mem_dis_8="show variables like 'max_connections';"
mem_dis_8_val=$(/home/mysql/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_8}")
echo "最大线程数：" `echo ${mem_dis_8_val} | cut -d' ' -f4`
mem_dis_9="show variables like 'thread_stack';"
mem_dis_9_val=$(/home/mysql/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_9}")
echo "线程栈信息使用内存：" `echo ${mem_dis_9_val} | cut -d' ' -f4`
mem_dis_10="show variables like 'sort_buffer_size';"
mem_dis_10_val=$(/home/mysql/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_10}")
echo "排序使用内存：" `echo ${mem_dis_10_val} | cut -d' ' -f4`
mem_dis_11="show variables like 'join_buffer_size';"
mem_dis_11_val=$(/home/mysql/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_11}")
echo "Join操作使用内存：" `echo ${mem_dis_11_val} | cut -d' ' -f4`
mem_dis_12="show variables like 'read_buffer_size';"
mem_dis_12_val=$(/home/mysql/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_12}")
echo "顺序读取数据缓冲区使用内存：" `echo ${mem_dis_12_val} | cut -d' ' -f4`
mem_dis_13="show variables like 'read_rnd_buffer_size';"
mem_dis_13_val=$(/home/mysql/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_13}")
echo "随机读取数据缓冲区使用内存：" `echo ${mem_dis_13_val} | cut -d' ' -f4`
mem_dis_14="show variables like 'tmp_table_size';"
mem_dis_14_val=$(/home/mysql/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_14}")
echo "临时表使用内存：" `echo ${mem_dis_14_val} | cut -d' ' -f4`
执行