mysql分析过程（如何在mysql数据库搭建一个类似Oracle）

概述

继续前面的内容，这里整理一下mysqlawr的被监控端部署及测试部分。

---

一、被监控端部署

1、安装与Perl相关的模块

1.1、安装perl(注意和监控端安装的版本要保持一致)

rpm -ivh mysql-community-libs-compat-5.7.26-1.el7.x86_64.rpm rpm -ivh perl-DBD-MySQL-4.023-5.el7.x86_64.rpm

1.2、安装perl-IO-Socket-SSL

yum install perl-IO-Socket-SSL.noarch

1.3、安装perl-Time-HiRes

yum install perl-Time-HiRes.x86_64

2、安装percona-toolkit --mysql工具包

2.1、下载和安装percona toolkit的包

到https://www.percona.com/downloads/percona-toolkit/LATEST/ 下载最新稳定的版本

wget https://www.percona.com/downloads/percona-toolkit/3.0.13/binary/redhat/7/x86_64/percona-toolkit-3.0.13-re85ce15-el7-x86_64-bundle.tar rpm -ivh percona-toolkit-3.0.13-1.el7.x86_64.rpm

缺啥直接yum list|grep XX然后yum install就可以了

例如：

[root@ZL-FSL-PAS-TEST-DB opt]# yum list|grep Digest perl-Digest.noarch 1.17-245.el7 InstallMedia perl-Digest-HMAC.noarch 1.03-5.el7 InstallMedia perl-Digest-MD5.x86_64 2.52-3.el7 InstallMedia perl-Digest-SHA.x86_64 1:5.85-3.el7 InstallMedia perl-Digest-SHA1.x86_64 2.13-9.el7 InstallMedia [root@ZL-FSL-PAS-TEST-DB opt]# yum install -y perl-Digest-MD5.x86_64

2.2、安装后,可以通过下面的命令确认是否安装成功:

pt-query-digest --help

如果命令提示可以正常显示,则说明pt工具已经正常安装和使用了。

3、被监控数据库设置

3.1、设置慢查询

修改/etc/my.cnf并重启

#设置慢查询阀值，单位为秒 long_query_time=5 slow_query_log=1 #开启mysql慢sql的日志 log_output=table,File #日志输出会写表，也会写日志文件，为了便于程序去统计，所以最好写表 slow_query_log_file=/data/log/slow.log

3.2、授权

set global validate_password_policy=0; set global validate_password_length=1; grant all on *.* to 'superuser'@'localhost' identified by 'fswl@1234';

3.3、开启show_compatibility_56

--把show_compatibility_56打开 mysql> set global show_compatibility_56=on; mysql> show variables like '%show_compatibility_56%';

3.4、开启performance schema

为了收集events_waits_summary_global_by_event_name、file_summary_by_event_name等等待事件，需在my.cnf配置如下参数：

#监控 performance_schema=on performance-schema-instrument='wait/io/file/%=on' performance-schema-instrument='wait/io/table/%=on' performance-schema-instrument='statement/com/%=on' performance-schema-instrument='statement/sql/%=on' performance-schema-instrument='stage/sql/%=on' # 注：只有current=on，performance_schema才会检查是否需要填充到history和long history中，后两者没有关系，均依赖于current，因此current必须无条件启用 performance-schema-consumer-events_stages_current=on # performance-schema-consumer-events_stages_history=on # performance_schema_events_stages_history_size=30 performance-schema-consumer-events_stages_history_long=on performance_schema_events_statements_history_long_size=10000 performance-schema-consumer-events_statements_current=on # performance-schema-consumer-events_statements_history=on # performance_schema_events_statements_history_size=30 performance-schema-consumer-events_statements_history_long=on performance_schema_events_statements_history_long_size=10000 performance-schema-consumer-events_waits_current=on # performance-schema-consumer-events_waits_history=on # performance_schema_events_statements_history_size=30 performance-schema-consumer-events_waits_history_long=on performance_schema_events_waits_history_long_size=10000 performance-schema-consumer-statements_digest=on performance_schema_digests_size=10000

---

二、测试

1、测试perl

perl /home/scripts/awr/myawr.pl -u superuser -p passwd -lh localhost -P 3306 -tu myawr -tp passwd -TP 3306 -th 172.26.151.91 -n ens160 -d sda1 -I 11

备注：-tu为远程主机账号 -n -d均为本地参数 -i是自己设定的主机id

说明：这些参数是：

-h,--help Print Help Info. -i,--interval Time(second) Interval(default 1). -d,--disk Disk Info(can't be null,default sda1). -n,--net Net Info(default eth0). -P,--port Port number to use for local mysql connection(default 3306). -u,--user user name for local mysql(default user). -p,--pswd user password for local mysql(can't be null). -lh,--lhost localhost(ip) for mysql where info is got(can't be null). -TP,--tport Port number to use formysql where info is saved (default 3306) -tu,--tuser user name for mysql where info is saved(default user). -tp,--pswd user password for mysql where info is saved(can't be null). -th,--thost host(ip) for mysql where info is saved(can't be null). -I,--tid db instance register id(can't be null,Reference myawr_host.id)

2、测试pt

pt-query-digest --user=myawr --password=fxx --port=3306 --review h=172.26.151.91,D=myawr,t=myawr_query_review \ --history h=172.26.151.91,D=myawr,t=myawr_query_review_history --no-report \ --filter="\$event->{add_column} = length(\$event->{arg}) and \$event->{hostid}=11" /data/log/slow.log

备注：--user是master的账号，h均为master的ip，hostid是被监控机自身的id,D是master数据库

3、部署crontab

*/5 * * * * perl /home/scripts/awr/myawr.pl -u superuser -p passwd -lh localhost -P 3306 -tu myawr -tp passwd -TP 3306 -th 172.26.151.91 -n ens160 -d sda1 -I 11 >> /home/scripts/awr/myawr_pas_prod.log 2>&1

备注：-tu为远程主机账号 -n -d均为本地参数 -i是自己设定的主机id

00 12 * * * pt-query-digest --user=myawr --password=passwd --port=3306 --review h=172.26.151.91,D=myawr,t=myawr_query_review --history h=172.26.151.91,D=myawr,t=myawr_query_review_history --no-report --filter="\$event->{add_column} = length(\$event->{arg}) and \$event->{hostid}=11" /data/log/slow.log >> /home/scripts/awr/pt-query_run.log 2>&1

备注：--user是master的账号，h均为master的ip，hostid是被监控机自身的id

---

三、输出报告

1、输出报告

在master上执行脚本，然后查看html即可。

# perl /home/scripts/awr/myawrrpt.pl -u myawr -p passwd -P 3306 -lh 172.26.151.91 -I 11

2、查看报告

输出HTML报告如下：

---

四、Mysql Snapshot Report

We can use myawrsrpt.pl to generate mysql snapshot report. You can execute the script on MySQL database machine where store the performance data,but perl-DBD-MySQL is required.We also can execute the script in any linux machine with perl-DBD-MySQL installed.

You can execute it for help Info:

perl /home/scripts/awr/myawrsrpt.pl -u myawr -lh 172.26.151.91 -p fxxx -P 3306 -I 11 -s 1

---

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~

,