今天我们使用prometheus+Grafana+mysql_exporter实现监控mysql数据库各项指标数据。
mysql_exporter：采集mysql数据库各项指标数据
prometheus：获取mysql_exporter数据
Grafana：展示prometheus采集的数据
如何使用prometheus和Grafana监控mysql数据库各项指标呢，请君继续往下看。
一、安装mysql数据库
上传mysql安装包到指定目录下
tar -zxvf mysql-5.7.31.tar.gz
cd mysql-5.7.31
mkdir data etc tmp log上传my.cnf，修改本地路径后、端口号等，进行保存
[mysqld]
server_id = 2
binlog_format=Mixed
read_rnd_buffer_size = 128M
federated
basedir = /app/mysql/mysql-5.7.31 ###################### 重要 ！按实际目录配置
datadir = /app/mysql/mysql-5.7.31/data ####################### 重要 ！按实际目录配置
socket = /app/mysql/mysql-5.7.31/tmp/mysql-5308.sock ################## 按安装目录名+sock，统一放置于/tmp下
init_connect='SET collation_connection = utf8_general_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake
lower_case_table_names=1
max_connections = 1000
wait_timeout=180
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
port = 5308 ####################### 重要 ！按实际目录配置
#skip-grant-tables
#default_authentication_plugin=mysql_native_password
tmp_table_size = 512M
event_scheduler=1
join_buffer_size = 512M
log_bin_trust_function_creators=1
read_rnd_buffer_size = 32M
sort_buffer_size = 64M
innodb_buffer_pool_size = 2048M
innodb_log_file_size = 64M
innodb_file_per_table=1
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 256M
key_buffer_size = 256M
read_buffer_size = 32M
max_allowed_packet = 100M
max_heap_table_size= 256M
#binlog_expire_logs_seconds=432
#query_cache_size = 512M`
[client]
default-character-set=utf8
port = 5308 ################################# 根据实际修改
[mysql]
default-character-set=utf8
[mysql.server]
user=mysql ############################# 在哪个用户下安装就是哪个
log-err
#log
log-slow-queries
log-update
#log-binmysql初始化，生成临时密码，并安装ssl
# 初始化
./mysqld --initialize --user=mysql --basedir=/app/mysql/mysql-5.7.31/ --datadir=/app/mysql/mysql-5.7.31/data
# 安装ssl
./mysql_ssl_rsa_setup --datadir=/app/mysql/mysql-5308/data启动mysql服务
nohup ./mysqld_safe --defaults-file=/app/mysql/mysql-5.7.31/etc/my.cnf >/dev/null &
查看启动状态
[mysql@192 mysql-5.7.31]$ netstat -an|grep 5308
tcp6 0 0 :::5308 :::* LISTEN
unix 2 [ ACC ] STREAM LISTENING 44227 /app/mysql/mysql-5.7.31/data/mysql_5308.sock使用root用户进行登录，修改root用户密码，root用户授权
# 登录
./mysql -uroot -p -S /home/mysql/mysql-5.7.31/data/mysql_5308.sock
#修改密码
mysql> ALTER USER "root"@"localhost" IDENTIFIED BY "123!@#..";
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#root用户授权
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update mysql.user set Host='%' where User='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL ON *.* TO 'root'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123!@#..';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)二、安装Mysqld_exporter
mysql_exporter是用来收集MysQL或者Mariadb数据库相关指标的
下载Mysqld_exporter
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.14.0/mysqld_exporter-0.14.0.linux-amd64.tar.gz
将Mysqld_exporter安装包进行解压
tar -zxvf mysqld_exporter-0.14.0.linux-amd64.tar.gz
mv mysqld_exporter-0.14.0.linux-amd64 mysqld_exporter-0.14.0
cd mysqld_exporter-0.14.0登录mysql数据库，并创建mysql_exporter用户，并授权
#创建用户
create user 'mysql_exporter'@'localhost' identified by '123!@#...';
#授权
grant process, replication client, select on *.* to 'mysql_exporter'@'%';进入到mysqld_exporter-0.14.0目录下，创建my.cnf，并添加以下内容
#创建该文件主要用于免密登录
[client]
user=mysql_exporter
password=123!@#...启动mysqld_exporter
nohup ./mysqld_exporter --config.my-cnf=./my.cnf > mysql_exporter.log &访问mysqld_exporter
三、Prometheus配置修改
切换到Prometheus安装目录下，修改Prometheus.yml配置文件
- job_name: "mysql"
static_configs:
- targets: ['192.168.52.130:9104']
查看Prometheus是否监控到mysqld_exporter 
四、导入模板
1、导入MySQL_Overview_prometheus_new.json文件
2、mysql数据库各项指标信息一屏全揽
学习更多内容，请关注IT运维先森微信公众号，将为你分享更多技术内容。
加入QQ交流群，我们一起探讨技术，共同进步。。QQ群号：809556380