mysql巡检脚本_转 mysql 巡检脚本

##感谢TSMYK Java技术大杂烩 2019-06-29

前言

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

然后执行 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 ，需要开启慢查询，通过慢查询日志进行统计，

查看是否开启慢查询日志命令：

show variables like '%slow_query%';

其中，slow_query_log 表示是否开启慢查询，OFF表示未开启，ON 表示开启。slow_query_log_file表示慢查询日志的路径。

开启慢查询日志

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%';