目录
- 1. 环境部署
- 2. 主从复制部署
- 3. 主从复制节点授权exporter
- 4. 配置 mysql 主从复制的指标暴露器
- 5. 配置 mysql 服务器指标暴露器
- 6. 部署 prometheus
- 7. 部署 grafana-server
1. 环境部署
主机 | IP地址 | 软件 |
prometheus + grafana | 192.168.8.18 | prometheus-2.27.1.linux-amd64.tar.gz/grafana-7.3.6-1.x86_64.rpm |
master | 192.168.8.19 | node_exporter/mysqld_exporter |
slave | 192.168.8.20 | node_exporter/mysqld_exporter |
2. 主从复制部署
master:192.168.8.19
slave:192.168.8.20
systemctl stop firewalld && systemctl disable firewalld
setenforce 0
- ① 主从服务器时间同步
#master
yum install -y ntp
vim /etc/ntp.conf #尾行加入下面内容
server 127.127.8.0 #设置本地是时钟源,注意修改自己的网段
fudge 127.127.8.0 stratum 8 #设置时间层级为 8(限制在 15 内)
systemctl start ntpd
------------------------------------------------------------------------------------------------
#slave
yum install -y ntp ntpdate
systemctl start ntpd
/usr/sbin/ntpdate 192.168.8.19 #时间同步
crontab -e #设置计划任务
*/30 * * * * /usr/sbin/ntpdate 192.168.8.19
- ② 主服务器的 mysql 配置
vim /etc/my.cnf
#添加下面配置
server-id = 1 #定义 server-id,每台主机不可相同
log-bin=master-bin #主服务器开启二进制日志
binlog_format = MIXED #本次使用 MIXED 模式
log-slave-updates=true #允许从服务器更新二进制日志
#注意:default-character-set=utf8 这个需要注释掉,不然会报错
systemctl restart mysqld.service #重启服务
#设置从服务器账号并授权
mysql -uroot -p123456
#给从服务器授权
mysql> GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.8.%' IDENTIFIED BY '123456';
mysql> flush privileges;
mysql> use mysql;
mysql> select user,host,authentication_string from user;
+-----------+-------------+-------------------------------------------+
| user | host | authentication_string |
+-----------+-------------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| myslave | 192.168.8.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 | 602 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#File 列显示日志名,Position 列显示偏移量
- ③ 从服务器的 mysql 配置
vim /etc/my.cnf
server-id = 2 #注意 id 与其他主机都不能相同
relay-log=relay-log-bin #开启中继日志,从主服务器上同步日志文件记录到本地
relay-log-index=slave-relay-bin.index #定义中继日志文件的位置和名称
relay_log_recovery = 1 #选配项
#当 slave 从库宕机后,假如 relay-log 损坏了,导致一部分中继日志没有处理,则自动放弃所有未执行的 relay-log,
并且重新从 master 上获取日志,这样就保证了relay-log 的完整性。
默认情况下该功能是关闭的,将 relay_log_recovery 的值设置为 1 时, 可在 slave 从库上开启该功能,建议开启。
systemctl restart mysqld
mysql -uroot -p123456
#配置同步,注意 master_log_file 和 master_log_pos 的值要与 Master 查询的一致
mysql> CHANGE master to master_host='192.168.8.19',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=602;
mysql> start slave; #启动同步,如有报错执行 reset slave;
mysql> show slave status\G; #查看 Slave 状态,确保 IO 和 SQL 线程都是 Yes,代表同步正常
3. 主从复制节点授权exporter
#master节点数据库授权执行
create user 'exporter'@'%' identified by '123456';
create user 'exporter'@'127.0.0.1' identified by '123456';
create user 'exporter'@'localhost' identified by '123456';
grant process,replication client,select on *.* to 'exporter'@'%' identified by 'admin123';
grant process,replication client,select on *.* to 'exporter'@'127.0.0.1' identified by 'admin123';
grant process,replication client,select on *.* to 'exporter'@'localhost' identified by 'admin123';
flush privileges;
4. 配置 mysql 主从复制的指标暴露器
上传 mysqld_exporter,配置 mysqld 主从复制的指标暴露器
master:192.168.8.19
slave:192.168.8.20
#上传安装包 mysqld_exporter-0.12.1.linux-amd64.tar.gz
tar -zxvf mysqld_exporter-0.12.1.linux-amd64.tar.gz -C /usr/local
cd /usr/local/mysqld_exporter-0.12.1.linux-amd64/ #进入目录,创建配置文件
cat > /usr/local/mysqld_exporter-0.12.1.linux-amd64/my.cnf << EOF
[client]
user=exporter
password=admin123
EOF
cat > /usr/lib/systemd/system/mysqld_exporter.service << EOF
[Unit]
Description=mysqld_exporter
After=network.target
[Service]
User=root
Type=simple
ExecStart=/usr/local/mysqld_exporter-0.12.1.linux-amd64/mysqld_exporter \
--config.my-cnf /usr/local/mysqld_exporter-0.12.1.linux-amd64/my.cnf \
--collect.info_schema.processlist
Restart=on-failure
[Install]
WantedBy=multi-user.target
EOF
systemctl daemon-reload
systemctl start mysqld_exporter.service
netstat -natp | grep 9104
5. 配置 mysql 服务器指标暴露器
master:192.168.8.19
slave:192.168.8.20
#上传安装包 node_exporter-1.1.2.linux-amd64.tar.gz
tar zxvf node_exporter-1.1.2.linux-amd64.tar.gz -C /opt
cd /opt
mv node_exporter-1.1.2.linux-amd64 node_exporter
cat > /usr/lib/systemd/system/node_exporter.service << EOF
[Unit]
Description=node_exporter
Documentation=https://prometheus.io/
After=network.target
[Service]
Type=simple
User=root
ExecStart=/opt/node_exporter/node_exporter
Restart=on-failure
[Install]
WantedBy=multi-user.target
EOF
systemctl daemon-reload
systemctl start node_exporter && systemctl enable node_exporter
netstat -antp | grep 9100
6. 部署 prometheus
#上传并解压 prometheus-2.27.1.linux-amd64.tar.gz
tar zxvf prometheus-2.27.1.linux-amd64.tar.gz -C /usr/local
- 修改配置文件
cd /usr/local/prometheus-2.27.1.linux-amd64
vim prometheus.yml
#最后插入:
- job_name: 'mysql-master-slave' #定义主从复制的job
scrape_interval: 5s #指标数据采集周期
static_configs: #静态采集方式
- targets: ['192.168.8.19:9104','192.168.8.20:9104'] #定义targets
- job_name: 'nodes' #定义nodes主机信息job
scrape_interval: 5s
static_configs:
- targets: ['192.168.8.19:9100','192.168.8.20:9100']
- job_name: 'mysql-master-slave'
scrape_interval: 5s
static_configs:
- targets: ['192.168.8.19:9104','192.168.8.20:9104']
- job_name: 'nodes'
scrape_interval: 5s
static_configs:
- targets: ['192.168.8.19:9100','192.168.8.20:9100']
- 启动 prometheus
./prometheus
netstat -antp | grep prometheus
tcp 0 0 192.168.8.18:33584 192.168.8.19:9104 ESTABLISHED 45282/./prometheus
tcp 0 0 192.168.8.18:50022 192.168.8.20:9100 ESTABLISHED 45282/./prometheus
tcp 0 0 192.168.8.18:54936 192.168.8.19:9100 ESTABLISHED 45282/./prometheus
tcp 0 0 192.168.8.18:53908 192.168.8.20:9104 ESTABLISHED 45282/./prometheus
tcp6 0 0 :::9090 :::* LISTEN 45282/./prometheus
tcp6 0 0 ::1:9090 ::1:52512 ESTABLISHED 45282/./prometheus
tcp6 0 0 ::1:52512 ::1:9090 ESTABLISHED 45282/./prometheus
- 浏览器登录查看
7. 部署 grafana-server
账号密码默认为 admin,admin
grafana 默认配置文件目录 /etc/grafana/grafana.ini
可直接访问 ip:3000 进入 grafana 控制台
#上传安装包grafana-7.3.6-1.x86_64.rpm
yum -y install grafana-7.3.6-1.x86_64.rpm
systemctl start grafana-server
创建 data sources 选择 prometheus
定义 prometheus 节点 http://192.168.8.18:9090
点击 save & text
点击 "+" 输入 import ,选择模板 7371,选择 prometheus 数据源 -> save 保存 mysqld-master-slave 监控
点击 "+" 输入 import ,选择模板 8919,选择 prometheus 数据源 -> save 保存 mysqld nodes 监控
点击 dashboard 选择以上两个模板