mysql数据库巡检

本文首发于个人公众号 Java 技术大杂烩,欢迎关注

前言

在系统运行的过程中,DBA需要经常的对数据库进行一些检查,如数据库磁盘的占用量,缓存的命中率,内存的分配等;由于有个客户需要对系统的数据库进行检查,所以进行了一些学习,在此记录下;由于不可能让用户手动的输入这些繁琐的命令,所以写了个 shell 脚本。

shell 脚本中连接数据库执行mysql 命令

在 shell 脚本中,去链接数据库,并执行相关的命令的步骤如下:

  1. 首先使用 touch 命令创建个文件, 使用 chmod 赋给这个文件执行权限
  2. 在文件中输入如下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 变量查看,命令如下:

  1. 进入到MySQL的bin目录下,执行如下命令来登陆mysql
./mysql -h127.0.0.1 -uroot -proot 
  1. 然后执行 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表示慢查询日志的路径。

  1. 开启慢查询日志
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_typeON 表示开启查询缓存,OFF 表示关闭缓存

query_cache_size 允许设置的值最小为40K,对于最大值则可以几乎认为无限制,但是,该值并不是越大, 查询缓存的命中率就越高,需要根据情况来定。

开启了查询缓存之后,接下来来看下缓存的相关选项说明: 执行查看命令:

show global status like 'QCache%';
  1. Qcache_free_blocks:目前还处于空闲状态的 Query Cache 中内存 Block 数目
  2. Qcache_free_memory:目前还处于空闲状态的 Query Cache 内存总量
  3. Qcache_hits:Query Cache 命中次数
  4. Qcache_inserts:向 Query Cache 中插入新的 Query Cache 的次数,也就是没有命中的次数
  5. Qcache_lowmem_prunes:当 Query Cache 内存容量不够,需要从中删除老的 Query Cache 以给新的 Cache 对象使用的次数
  6. Qcache_not_cached:没有被 Cache 的 SQL 数,包括无法被 Cache 的 SQL 以及由于 query_cache_type 设置的不会被 7. Cache 的 SQL
  7. Qcache_queries_in_cache:目前在 Query Cache 中的 SQL 数量
  8. 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在一个较低级别的运行过程中的资源消耗、资源等待等.

关于 performance_schema 的介绍,可以参考 https://mp.weixin.qq.com/s?__biz=MzU0MTczNzA1OA==&mid=2247483711&idx=1&sn=aef31942ae6294053cddc0ba83630597&chksm=fb242832cc53a12477febfb309aa5ed9c4c5652a7dafee28ac32ce81a214a0fd72758ff045e9&scene=21#wechat_redirect,介绍得比较详细。

统计 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`

执行?结果:

查看数据库的磁盘占用量

1.查询整个数据库的占用量

select sum((data_length+index_length)/1024/1024) m from information_schema.tables where table_schema="dbname";

2.某个表的占用量:

select (data_length+index_length)/1024/1024 M from information_schema.tables where table_schema="dbname" and table_name="table_name";

3.整个mysql server 所有数据库的磁盘用量

select table_schema, sum((data_length+index_length)/1024/1024) M from information_schema.tables where table_schema is not null group by table_schema;

shell 脚本:

host="127.0.0.1"
port="3306"
userName="root"
password="root"
dbname="dbname"
dbset="--default-character-set=utf8 -A"

echo "================= 数据库磁盘占用量 ==========================="

_disk_used="select sum((data_length+index_length)/1024/1024) M from information_schema.tables where table_schema=\"m_dp_eup\""

_disk_used_val=$(/home/mysql/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${_disk_used}")
echo "磁盘占用量(单位:M):" `echo ${_disk_used_val} | cut -d' ' -f2`

分析 mysql 的错误日志

当系统在运行中,mysql 可以会有一些异常,可以通过查看错误日志来分析。

查看mysql的错误日志文件:

show global variables like 'log_error'; 
#或 
select @@log_error;

使用 grep 命令查找错误信息输出到文件:

grep 'error' ./mysql.err* > error.log 或 egrep -i 'error|Failed' ./mysqld.err* > error.log

如下想根据时间来过滤,则可以在后面加上日期就可以了

grep -i -E 'error' ./mysqld.err* | grep -E '2019-03-28|2019-06-14' > error.log

shell 脚本:

查看最近一周的错误日志文件中是否有错误

_time=$(date -d '6 days ago' +%Y-%m-%d)\|$(date -d '5 days ago' +%Y-%m-%d)\|$(date -d '4 days ago' +%Y-%m-%d)\|$(date -d '3 days ago' +%Y-%m-%d)\|$(date -d '2 days ago' +%Y-%m-%d)\|$(date -d '1 days ago' +%Y-%m-%d)\|$(date -d '0 days ago' +%Y-%m-%d)

echo "==================最近一周的错误日志 =========================="

#grep -i -E 'error' /home/logs/mysql/mysqld.err* | grep -E '2019-03-28|2019-06-14'
grep -i -E 'error' /home/logs/mysql/mysql.err*| grep -E \'$_time\'

_time 是获取最近一周的日期,形如:'2019-06-13|2019-06-14|………………………'

当然还有很多的检查项,这里就不一一列出来了。

以上就是一些检查项及其 shell 脚本的实现。

本文分享自微信公众号 - Java技术大杂烩(tsmyk0715),作者:TSMYK

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-06-29

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 源码分析 Mybatis 的 foreach 为什么会出现性能问题

    最近在做一个类似于综合报表之类的东西,需要查询所有的记录(数据库记录有限制),大概有1W条记录,该报表需要三个表的数据,也就是根据这 1W 个 ID 去执行查询...

    Java技术大杂烩
  • 公众号开发:获取用户消息和回复消息

    最近在看微信公众号的开发文档,觉得很有意思,可以自定义开发一些功能,比如有人关注了公众号之后,你可以做出稍微复杂点的回复(简单的回复在公众号后台配置就好啦);比...

    Java技术大杂烩
  • Win10 微软拼音添加小鹤双拼

    今天发现 Win10 自带的微软输入法还可以添加 小鹤双拼,终于可以抛弃其他的广告输入法了。

    Java技术大杂烩
  • 【DB笔试面试812】在Oracle中,什么是闪回表(Flashback TABLE)?

    闪回表(Flashback TABLE)是将表恢复到过去的某个时间点或某个SCN值时的状态。对闪回表语句不能进行回滚,如果要闪回表,那么需要有对表的FLASHB...

    小麦苗DBA宝典
  • InnoDB Cluster 和 consul 碰撞出什么火花?

    cluster.addInstance('root@kh-oms4-sit-innodbcluster-db02:3306');

    [3306 Pai ] 社区
  • (三)dict哈希结构1

    昨天分析完adlist的Redis代码,今天马上马不停蹄的继续学习Redis代码中的哈希部分的结构学习,不过在这里他不叫什么hashMap,而是叫dict,而且...

    范蠡
  • Python 进阶(九):JSON 基本操作

    JSON(JavaScript Object Notation)是一种轻量级的数据交换格式,它具有简洁、清晰的层次结构,易于阅读和编写,还可以有效的提升网络传输...

    Python小二
  • 前端Tips#7 - 用 6 行代码实现文本复制

    核心的代码片段就 6 行,利用动态创建 textarea,和 document.execCommand 命令就实现了:

    JSCON简时空
  • 浅析XSS的几种测试方法

    0x00 背景 最近看到一个好玩的xss社区,准备通过几个经典的关卡来剖析一下XSS,本文仅提供经典案例。 试玩链接:http://tr.secevery...

    用户1467662
  • andrax pentest 高级专业黑客安卓渗透测试工具

    试了下还是很不错的,andrax是一款专为Android智能手机设计的渗透测试平台,而kali linux的NetHunter只是一款Debian模拟运行工具一...

    周俊辉

扫码关注云+社区

领取腾讯云代金券