Mysql性能监控及优化，基于Prometheus+grafana

本篇不详写prometheus、grafana的搭建，需要可以翻阅linux监控篇

一、mysql监控

1、运行mysql-exporter

注：mysql的搭建在文章尾部，这里直接进入主题

Prometheus对Mysql进行数据采集需要在被mysql所在服务器安装mysql-exporter
注意修改命令中数据库连接信息

docker run -d -p 9104:9104 -e DATA_SOURCE_NAME="root:root@(192.168.9.61:3306)/" --name mysqldexporter prom/mysqld-exporter

2、配置Prometheus.yml

- job_name: mysql
static_configs:
- targets: ['192.168.9.61:9104']

3、重启Prometheus，查看mysql是否成功监控

http://ip:9104/metrics

4、grafana 7362

二、慢查询索引优化

tps =并发/时间（链路时间）

1、打开慢查询日志

slow_query_log ：打开 ON ，关闭 OFF

slow_query_log_file：慢查询日志路径，可自定义

show variables like '%slow_query_log%';

2、设置慢查询的标准

long_query_time：超过该值即为慢查询，单位秒

show variables like 'long_query_time';

3、查看数据库最大连接池

show variables like '%max_connections%';

4、修改数据库最大连接数

程序连接池优化 druid c3po

https://blog.csdn.net/weixin_42102220/article/details/113389471

https://blog.csdn.net/weixin_42102220/article/details/113389471

5、查看慢查询日志

 #修改后，拷贝到mysql路径下
 docker cp /usr/local/my.cnf dbDockerName:/etc/mysql/conf.d/
 docker restart dbDockerName
 #进入mysql容器
 docker exec -it dbDockerName bash
 #查看慢查询日志，路径参考2.1 slow_query_log_file
 tail -100f /var/log/slow-query.log

6、grafana监控慢查询

7、查看慢查询sql是否建立索引

没有建立索引就加索引

#关键词 EXPLAIN
EXPLAIN select * from tb_user where tb_user.userid='susu';

8、mysqldumpslow sql日志分析

mysqldumpslow 是mysql官方提供的一个慢日志分析工具

s：sort排序
c:访问次数
i：锁定时间
r：返回记录
t：查询时间
al:平均锁定时间
ar：平均返回记录数
at：平均查询时间
t：即为返回前面多少条数据
g：后边搭配一个正则匹配模式，大小写不敏感

1、显示出耗时最长的 50 个SQL 语句的执行信息
mysqldumpslow –s at -t 50 /var/log/slow-query.log;
2、访问次数最多的20个sql语句 （访问最频繁）
mysqldumpslow -s c -t 20 /var/lib/mysql/sql-slow.log;
3、返回记录集最多的20个sql （返回结果最大排序）
mysqldumpslow -s r -t 20 /var/lib/mysql/sql-slow.log;
4、得到按照时间排序的前10条里面含有做了连接的查询SQL
mysqldumpslow -s t -t 10 -g "join" /var/lib/mysql/sql-slow.log;

三、索引问题

1、数据库数据过大

拆分表
采用分布式缓存优化（redis）

2、索引失效问题

采用全文搜索引擎（ES）

四、Mysql搭建

docker run -p 3307:3306 --name mysql3307 -v
/root/mysql3307/conf:/etc/mysql/conf.d -v /root/mysql3307/logs:/logs -v
/root/mysql3307/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql --
lower_case_table_names=1

-p 代表端口映射，格式为宿主机映射端口：容器运行端口
-e 代表添加环境变量 MYSQL_ROOT_PASSWORD是root用户的登录密码
lower_case_table_name mysql8高版本 忽略大小写

数据库连接报错，需调整权限

进入mysql容器

docker exec -it mysql3307 /bin/bash

登录mysql

mysql -uroot -p123456

status;

更改、刷新mysql权限

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;

再次连接数据库