mysql慢sql可视化监控

Prometheus 监控Mysql服务器及Grafana可视化1、安装mysql服务2、配置mysql主从服务3、安装mysql_exporter节点收集数据4、配置promethus采集mysql_exporter数据5、配置grafna 展示mysql一、快速部署mysql服务wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpmyum -y install mysql57-community-release-el7-10.noarch.rpmyum -y install mysql-community-serversystemctl startmysqld.service查找密码 grep "password" /var/log/mysqld.log修改密码 ALTER USER ‘root‘@‘localhost‘ IDENTIFIED BY ‘testSs@2019‘;授权 exporter权限账号（可查看主从运行情况查看线程，及所有数据库。）mysql -uroot -ptestSs@2019CREATE USER ‘exporter‘@‘localhost‘ IDENTIFIED BY ‘Nj2020@tTsx‘;ｇｒａｎｔ PROCESS, REPLICATION CLIENT, ｓｅｌｅｃｔ ON *.* TO ‘exporter‘@‘localhost‘;二、配置mysql主从服务主服务器：114.67.116.119从服务器：114.67.94.331、配置mysql主服务 主配置：vim /etc/my.cnflog-bin=mysql-binserver-id=1innodb_flush_log_at_trx_commit=1sync_binlog=1 #binlog-ignore-db=mysqlbinlog-ignore-db=information_schemabinlog-ignore-db=performance_schemabinlog-ignore-db=sysexpire_logs_days=7重启动服务 授权repl权限，显示mastersystemctl restart mysqldmysql -uroot -ptestSs@2019grant replication slave on *.* to ‘repl‘@‘%‘ identified by ‘Nj2020@tTsx‘;show master status;结果展示mysql> ｇｒａｎｔ replication slave on *.* to ‘repl‘@‘%‘ identified by ‘Nj2020@tTsx‘;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> show master status;+------------------+----------+--------------+-------------------------------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB| Executed_Gtid_Set |+------------------+----------+--------------+-------------------------------------------+-------------------+| mysql-bin.000001 |437 || information_schema,performance_schema,sys ||+------------------+----------+--------------+-------------------------------------------+-------------------+1 row in set (0.00 sec)2、配置mysql从服务 从配置：vim /etc/my.cnflog-bin=mysql-binserver-id=2expire_logs_days=7重启动服务 change master权限systemctl restart mysqldmysql -uroot -ptestSs@2019stop slave;change master to master_user=‘repl‘,master_password=‘Nj2020@tTsx‘,master_host=‘10.0.0.11‘,master_port=3306,master_log_file=‘mysql-bin.000001‘,master_log_pos=437;start slave;查看主从是否正常 show slave status\G;mysql> show slave status\G;*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 10.0.0.11Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 1705Relay_Log_File: k8s-test-01-relay-bin.000004Relay_Log_Pos: 320Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 1705Relay_Log_Space: 925Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: 538ecda5-5850-11ea-a490-fa163e1c3642 Master_Info_File: /var/lib/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.01 sec)3 、校验数据同步是否成功,可以主库插入测试数据，在从库查看数据是否同步成功mysql -uroot -ptestSs@2019create database test01;use test01;create table tb1(id int,c2 varchar(100),primary key(id));ｉｎｓｅｒｔ into tb1(id,c2) values(1,‘12‘);ｉｎｓｅｒｔ into tb1(id,c2) values(2,‘14‘);三、安装mysql_exporter节点收集数据1、 安装mysql_exporter服务wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.12.1/mysqld_exporter-0.12.1.linux-amd64.tar.gztar zxvf mysqld_exporter-0.12.1.linux-amd64.tar.gzmv mysqld_exporter-0.12.1.linux-amd64 /usr/local/exporter/mysql_exportercd /usr/local/exporter/mysql_exporter/cat > .my.cnf