目录
10.所有数据库启动mysql agent代理服务、启动监控
11.监控服务器(192.168.1.138)查看集群状态、查看监控状态
3.将db1重新加回来,看看‘ceshi’数据库有没有同步到db1服务器开启db1 mariadb服务
4.查看db1、db3、db4服务器有没有同步db2添加ceshi数据库的操作
一、工作原理
mmm_mond:监控进程,负责所有的监控工作,决定和处理所有节点角色活动。因此脚本需要在监管机上运行
mmm_agentd:运行在每个mysql服务器上的代理进程,完成监控的探针工作和执行简单的远端服务设置(此脚本需要在被监管机上运行)
mmm_control:一个简单的脚本,提供管理mmm_mond进程的命令
mysql-mmm:监管端会提供多个虚拟IP(VIP),包括一个写VIP,多个可读VIP,通过监管的管理,这些IP会绑定在可用MySQL之上,当某台MySQL宕机时,监管会将VIP地址迁移至其他MySQL服务器
注意:
在整个监管过程中,需要在mysql中添加相关授权用户,以便让mysql可以支持监管机的维护
授权的用户包括mmm_monito用户和一个mmm_agent用户,如果想使用mmm的备份工具则还要添加一个mmm_tools用户
二、环境准备
准备四台服务器并修改主机名、一台监控服务器:192.168.1.138
192.168.1.12(db1)、192.168.1.133(db2)、192.168.1.134(db3)、192.168.1.135(db4)
[root@localhost ~] hostnamectl set-hostname db1
[root@localhost ~] bash
[root@db1 ~]
关闭防火墙
[root@db1 ~] systemctl stop firewalld
[root@db1 ~] iptables -F
[root@db1 ~] setenforce 0
三、实现步骤
1.所有服务器安装epel源,mariadb数据库
[root@db1 ~] yum -y install epel-release
[root@db1 ~] yum -y install mariadb mariadb-server
2.修改数据库配置文件
[root@db1 ~] vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
log-slave-updates=true #变成其他从库的主库的必要参数
server-id=1 #db2服务器将这里改为2即可
relay-log=relay-log-bin
relay-log-index=relay-log-bin.index
[root@db3 ~] vim /etc/my.cnf
[mysqld]
server-id=3 #db4 将这里改为4即可
relay-log=relay-log-bin
relay-log-index=relay-log-bin.index
3.设置db1服务器、db2服务器互为主从
[root@db1 ~] systemctl start mariadb
[root@db1 ~] mysql
#db1给db2的用户授权
MariaDB [(none)]> grant replication slave on *.* to 'replication'@'192.168.1.133' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#db2给db1的用户授权
[root@db2 ~] systemctl start mariadb
[root@db2 ~] mysql
MariaDB [(none)]> grant replication slave on *.* to 'replication'@'192.168.1.12' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#db1查看日志文件
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 481 | | |
+------------------+----------+--------------+------------------+
#db2设置为db1从服务器
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
MariaDB [(none)]> change master to master_host="192.168.1.12", master_user="replication", master_password="123456", master_log_file="mysql-bin.000003", master_log_pos=481;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show master status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#同理将db1设置为db2的从服务器
4.将db3、db4设置为db1的从服务器
#db1给db3、db4的用户授权
MariaDB [(none)]> grant replication slave on *.* to 'replication'@'192.168.1.134' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> grant replication slave on *.* to 'replication'@'192.168.1.135' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 878 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
#db3、db4给db1的用户授权
MariaDB [(none)]> grant replication slave on *.* to 'replication'@'192.168.1.12' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#db3、db4设置为db1从服务器
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> change master to master_host="192.168.1.12", master_user="replication", master_password="123456", master_log_file="mysql-bin.000003", master_log_pos=878;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
5.所有服务器下载 mmm* 所有软件
[root@db1 ~] yum -y install mysql-mmm*
6.在所有数据库授权使monitor可以访问
[root@db1 ~] mysql
MariaDB [(none)]> grant super, replication client, process on *.* to 'mmm_agent'@'192.168.1.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
7.所有主机修改配置文件
[root@db1 ~] vim /etc/mysql-mmm/mmm_common.conf
active_master_role writer
<host default>
cluster_interface ens33
pid_path /run/mysql-mmm-agent.pid
bin_path /usr/libexec/mysql-mmm/
replication_user repliction #复制用户
replication_password 123456
agent_user mmm_agent #管理用户
agent_password 123456
</host>
<host db1>
ip 192.168.1.12
mode master
peer db2 #db1监控db2
</host>
<host db2>
ip 192.168.1.133
mode master
peer db1 #db2监控db1
</host>
<host db3>
ip 192.168.1.134
mode slave
</host>
<host db4>
ip 192.168.1.135
mode slave
</host>
<role writer>
hosts db1, db2
ips 192.168.100.200
mode exclusive #独断IP
</role>
<role reader>
hosts db3, db4
ips 192.168.100.198, 192.168.100.199
mode balanced #方式
</host>
#剩下的直接远程复制会快点哦!
[root@db1 ~] scp /etc/mysql-mmm/mmm_common.conf 192.168.1.133:/etc/mysql-mmm/mmm_common.conf
The authenticity of host '192.168.1.133 (192.168.1.133)' can't be established.
ECDSA key fingerprint is SHA256:mgc4iZ/w11vaana8aZbfLvgUSFhlAbUK/ghw1ANkBNw.
ECDSA key fingerprint is MD5:c2:c7:85:09:96:72:84:a1:13:6b:72:f2:e1:d5:2b:e6.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.1.133' (ECDSA) to the list of known hosts.
root@192.168.1.133's password:
8.所有服务器修改角色
[root@db1 ~] vim /etc/mysql-mmm/mmm_agent.conf
this db1
[root@db2 ~] vim /etc/mysql-mmm/mmm_agent.conf
this db2
[root@db3 ~] vim /etc/mysql-mmm/mmm_agent.conf
this db3
[root@db4 ~] vim /etc/mysql-mmm/mmm_agent.conf
this db4
9.所有服务器修改监控主机配置文件
[root@mmm ~] vim /etc/mysql-mmm/mmm_mon.conf
#ips后添加服务器 真实IP
ping_ips 192.168.1.138
monitor_user mmm_agent
monitor_password 123456
10.所有数据库启动mysql agent代理服务、启动监控
[root@db1 ~] systemctl start mysql-mmm-monitor
[root@db1 ~] systemctl start mysql-mmm-agent
11.监控服务器(192.168.1.138)查看集群状态、查看监控状态
[root@mmm ~] mmm_control show
db1(192.168.1.12) master/ONLINE. Roles: writer(192.168.100.200)
db2(192.168.1.133) master/ONLINE. Roles:
db3(192.168.1.134) slave/ONLINE. Roles: reader(192.168.100.198)
db4(192.168.1.135) slave/ONLINE. Roles: reader(192.168.100.199)
[root@mmm ~] mmm_control checks all
db4 ping [last change: 2023/06/30 19:03:25] OK
db4 mysql [last change: 2023/06/30 19:03:25] OK
db4 rep_threads [last change: 2023/06/30 19:03:25] OK
db4 rep_backlog [last change: 2023/06/30 19:03:25] OK: Backlog is null
db2 ping [last change: 2023/06/30 19:03:25] OK
db2 mysql [last change: 2023/06/30 19:03:25] OK
db2 rep_threads [last change: 2023/06/30 19:03:25] OK
db2 rep_backlog [last change: 2023/06/30 19:03:25] OK: Backlog is null
db3 ping [last change: 2023/06/30 19:03:25] OK
db3 mysql [last change: 2023/06/30 19:03:25] OK
db3 rep_threads [last change: 2023/06/30 19:03:25] OK
db3 rep_backlog [last change: 2023/06/30 19:03:25] OK: Backlog is null
db1 ping [last change: 2023/06/30 19:03:25] OK
db1 mysql [last change: 2023/06/30 19:03:25] OK
db1 rep_threads [last change: 2023/06/30 19:06:40] OK
db1 rep_backlog [last change: 2023/06/30 19:03:25] OK: Backlog is null
四、测试
1.停止第一个主服务器,查看监控状态、主从状态
[root@db1 ~] systemctl stop mariadb.service
[root@mmm ~] mmm_control show
db1(192.168.1.12) master/HARD_OFFLINE. Roles:
db2(192.168.1.133) master/ONLINE. Roles: writer(192.168.100.200)
db3(192.168.1.134) slave/ONLINE. Roles: reader(192.168.100.198)
db4(192.168.1.135) slave/ONLINE. Roles: reader(192.168.100.199)
[root@mmm ~] mmm_control checks all
db4 ping [last change: 2023/06/30 19:03:25] OK
db4 mysql [last change: 2023/06/30 19:03:25] OK
db4 rep_threads [last change: 2023/06/30 19:03:25] OK
db4 rep_backlog [last change: 2023/06/30 19:03:25] OK: Backlog is null
db2 ping [last change: 2023/06/30 19:03:25] OK
db2 mysql [last change: 2023/06/30 19:03:25] OK
db2 rep_threads [last change: 2023/06/30 19:03:25] OK
db2 rep_backlog [last change: 2023/06/30 19:03:25] OK: Backlog is null
db3 ping [last change: 2023/06/30 19:03:25] OK
db3 mysql [last change: 2023/06/30 19:03:25] OK
db3 rep_threads [last change: 2023/06/30 19:03:25] OK
db3 rep_backlog [last change: 2023/06/30 19:03:25] OK: Backlog is null
db1 ping [last change: 2023/06/30 19:03:25] OK
db1 mysql [last change: 2023/06/30 19:16:32] ERROR: Connect error (host = 192.168.1.12:3306, user = mmm_agent)! Can't connect to MySQL server on '192.168.1.12' (111)
db1 rep_threads [last change: 2023/06/30 19:06:40] OK
db1 rep_backlog [last change: 2023/06/30 19:03:25] OK: Backlog is null
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
发现VIP从db1,转移到db2了,db2、db3、db4的主从复制也断开连接
2.在db2服务器创建一个数据库‘ceshi’
MariaDB [(none)]> create database ceshi;
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ceshi |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
发现db3、db4并没有跟着复制db2的操作
3.将db1重新加回来,看看‘ceshi’数据库有没有同步到db1服务器
开启db1 mariadb服务
[root@db1 ~] systemctl restart mariadb.service
手动切换Roles规则 将VIP从db2切换到db1
[root@mmm ~] mmm_control move_role writer db1
OK: Role 'writer' has been moved from 'db2' to 'db1'. Now you can wait some time and check new roles info!
[root@mmm ~] mmm_control show
db1(192.168.1.12) master/ONLINE. Roles: writer(192.168.100.200)
db2(192.168.1.133) master/ONLINE. Roles:
db3(192.168.1.134) slave/ONLINE. Roles: reader(192.168.100.198)
db4(192.168.1.135) slave/ONLINE. Roles: reader(192.168.100.199)
4.查看db1、db3、db4服务器有没有同步db2添加ceshi数据库的操作
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ceshi |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
五、总结
MMM集群,当任意master服务器(1)宕机后,另一台master服务器会(2)继承VIP,虽然表面上主从复制断开连接,但另一台master服务(2)还是担任master服务器的角色,将宕机master服务器(1)重新启动后,会自动同步master服务器(2)的数据,同时传给从服务器,从而保证数据的完整性!