监控mysql主从状态
环境
名称 | 主机名 | IP |
---|---|---|
zabbix服务端 | wangyitong | 192.168.232.128 |
zabbix客户端,mysql主库 | wyt3 | 192.168.232.132 |
zabbix客户端,mysql从库 | wyt2 | 192.168.232.130 |
1.配置MySQL主从
安装数据库
[root@wyt3 ~]# yum -y install mariadb*
[root@wyt2 ~]# yum -y install mariadb*
配置主数据库
[root@wyt3 ~]# mysql //在主数据库里创建一个同步账号授权给从数据库使用
MariaDB [(none)]> grant replication slave on *.* to 'repl'@'192.168.232.130' identified by 'repl123';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.232.130';
Query OK, 0 rows affected (0.00 sec)
ariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
[root@wyt3 ~]# vim /etc/my.cnf //编辑配置文件
server-id = 10 //数据库服务器唯一标识符,主库的server-id值必须比从库的大
log-bin = mysql_bin //启用binlog日志
[root@wyt3 ~]# systemctl restart mariadb //重启mysql服务
[root@wyt3 scripts]# ss -antl|grep 3306
LISTEN 0 50 *:3306 *:*
[root@wyt3 ~]# mysql
MariaDB [(none)]> show master status; //查看主库状态
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 245 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)
配置从数据库
[root@wyt2 ~]# vim /etc/my.cnf //编辑配置文件
server-id = 20 //设置从库的唯一标识符,从库的server-id值必须小于主库的该值
relay-log = myrelay_bin //启用中继日志relay-log
[root@wyt3 ~]# systemctl restart mariadb //重启mysql服务
[root@wyt3 scripts]# ss -antl|grep 3306
LISTEN 0 50 *:3306 *:*
//配置并启动主从复制
MariaDB [(none)]> change master to \
-> master_host='192.168.232.132',
-> master_user='repl',
-> master_password='repl123',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=245;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
MariaDB [(none)]> start slave; //启用
Query OK, 0 rows affected (0.03 sec)
//查看从服务器状态
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.232.132
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 245
Relay_Log_File: myrelay.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes //此处必须是Yes
Slave_SQL_Running: Yes //此处必须是Yes
.....