MySQL数据库——MMM高可用群集配置（实战！）
MMM简介
MMM（Master-Master replication manager for MySQL）是一套支持双主故障切换和双主日常管理的脚本程序。MMM使用Perl语言开发，主要用来监控和管理MySQL Master-Master（双主）复制，可以说是mysql主主复制管理器。虽然叫做双主复制，但是业务上同一时刻只允许对一个主进行写入，另一台备选主上提供部分读服务，以加速在主主切换时刻备选主的预热，可以说MMM这套脚本程序一方面实现了故障切换的功能，另一方面其内部附加的工具脚本也可以实现多个slave的read负载均衡。关于mysql主主复制配置的监控、故障转移和管理的一套可伸缩的脚本套件（在任何时候只有一个节点可以被写入），这个套件也能对居于标准的主从配置的任意数量的从服务器进行读负载均衡，所以你可以用它来在一组居于复制的服务器启动虚拟ip，除此之外，它还有实现数据备份、节点之间重新同步功能的脚本。
MMM提供了自动和手动两种方式移除一组服务器中复制延迟较高的服务器的虚拟ip，同时它还可以备份数据，实现两节点之间的数据同步等。由于MMM无法完全的保证数据一致性，所以MMM适用于对数据的一致性要求不是很高，但是又想最大程度的保证业务可用性的场景。MySQL本身没有提供replication failover的解决方案，通过MMM方案能实现服务器的故障转移，从而实现mysql的高可用。对于那些对数据的一致性要求很高的业务，非常不建议采用MMM这种高可用架构。
实验环境
主服务器1：192.168.52.135 db1 writer VIP：192.168.52.200 主服务器2：192.168.52.133 db2 从服务器1：192.168.52.134 db3 reader VIP：192.168.52.210,192.168.52.220 从服务器2：192.168.52.148 db4 监控服务器：192.168.52.150
实验操作
一、分别在四台MySQL服务器上安装mariadb服务
1、配置本地yum源（四台服务器上分别进行相同操作）
[ ~]# wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo #配置aliyun源 --2019-11-25 09:51:39-- http://mirrors.aliyun.com/repo/Centos-7.repo 正在解析主机 mirrors.aliyun.com (mirrors.aliyun.com)... 124.14.2.219, 124.14.2.218, 124.14.2.234, ... 正在连接 mirrors.aliyun.com (mirrors.aliyun.com)|124.14.2.219|:80... 已连接。 已发出 HTTP 请求，正在等待回应... 200 OK 长度：2523 (2.5K) [application/octet-stream] 正在保存至: “/etc/yum.repos.d/CentOS-Base.repo” 100%[====================================================================================>] 2,523 --.-K/s 用时 0s 2019-11-25 09:51:39 (225 MB/s) - 已保存 “/etc/yum.repos.d/CentOS-Base.repo” [2523/2523]) [ ~]# yum -y install epel-release #安装epel-release源 .......................//省略过程 [ ~]# yum clean all && yum makecache #清除缓存旧的缓存，同步现在服务器的缓存 ........................//省略过程
2、在主服务器1上安装mariadb（MySQL服务的一种）服务
[ ~]# hostnamectl set-hostname db1 #修改主机名 [ ~]# su #切换bash环境 [ ~]# [ ~]# yum install mariadb-server mariadb -y #安装服务 ....................//省略过程 [ ~]# vim /etc/my.cnf #修改配置文件 [mysqld] log_error=/var/lib/mysql/mysql.err log=/var/lib/mysql/mysql_log.log log_slow_queries=/var/lib/mysql_slow_queris.log binlog-ignore-db=mysql,information_schema character_set_server=utf8 log_bin=mysql_bin server_id=1 log_slave_updates=true sync_binlog=1 auto_increment_increment=2 auto_increment_offset=1 [ ~]# systemctl stop firewalld.service #关闭防火墙 [ ~]# setenforce 0 #关闭增强性安全功能 [ ~]# systemctl start mariadb.service #开启服务 [ ~]# netstat -ntap | grep 3306 #查看端口 tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 4601/mysqld [ ~]#
3、在主服务器2上安装mariadb服务
[ ~]# hostnamectl set-hostname db2 [ ~]# su [ ~]# [ ~]# yum install mariadb-server mariadb -y [ ~]# vim /etc/my.cnf [mysqld] log_error=/var/lib/mysql/mysql.err log=/var/lib/mysql/mysql_log.log log_slow_queries=/var/lib/mysql_slow_queris.log binlog-ignore-db=mysql,information_schema character_set_server=utf8 log_bin=mysql_bin server_id=2 log_slave_updates=true sync_binlog=1 auto_increment_increment=2 auto_increment_offset=1 [ ~]# systemctl stop firewalld.service [ ~]# setenforce 0 [ ~]# systemctl start mariadb.service [ ~]# netstat -ntap | grep 3306 tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 50643/mysqld [ ~]#
4、在从服务器1上安装mariadb服务
[ ~]# hostnamectl set-hostname db3 [ ~]# su [ ~]# [ ~]# yum install mariadb-server mariadb -y [ ~]# vim /etc/my.cnf [mysqld] log_error=/var/lib/mysql/mysql.err log=/var/lib/mysql/mysql_log.log log_slow_queries=/var/lib/mysql_slow_queris.log binlog-ignore-db=mysql,information_schema character_set_server=utf8 log_bin=mysql_bin server_id=11 log_slave_updates=true sync_binlog=1 auto_increment_increment=2 auto_increment_offset=1 [ ~]# systemctl stop firewalld.service [ ~]# setenforce 0 [ ~]# systemctl start mariadb.service [ ~]# netstat -ntap | grep 3306 tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 50578/mysqld [ ~]#
5、在从服务器2上安装mariadb服务
[ ~]# hostnamectl set-hostname db4 [ ~]# su [ ~]# [ ~]# yum install mariadb-server mariadb -y [ ~]# vim /etc/my.cnf [mysqld] log_error=/var/lib/mysql/mysql.err log=/var/lib/mysql/mysql_log.log log_slow_queries=/var/lib/mysql_slow_queris.log binlog-ignore-db=mysql,information_schema character_set_server=utf8 log_bin=mysql_bin server_id=22 log_slave_updates=true sync_binlog=1 auto_increment_increment=2 auto_increment_offset=1 [ ~]# systemctl stop firewalld.service [ ~]# setenforce 0 [ ~]# systemctl start mariadb.service [ ~]# netstat -ntap | grep 3306 tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 39804/mysqld [ ~]#
二、配置MySQL多主多从模式
1、分别查看主服务器1和主服务器2的log_bin日志和pos值的位置
主服务器1： [ ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. MariaDB [(none)]> show master status; #查看状态 +------------------+----------+--------------+--------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+--------------------------+ | mysql_bin.000003 | 245 | | mysql,information_schema | +------------------+----------+--------------+--------------------------+ 1 row in set (0.00 sec)
主服务器2： [ ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. MariaDB [(none)]> show master status; #查看状态 +------------------+----------+--------------+--------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+--------------------------+ | mysql_bin.000003 | 245 | | mysql,information_schema | +------------------+----------+--------------+--------------------------+ 1 row in set (0.00 sec)
2、主服务器1与主服务器2互相提升访问权限
主服务器1： MariaDB [(none)]> grant replication slave on *.* to ‘replication‘@‘192.168.52.%‘ identified by ‘123456‘; #提权 Query OK, 0 rows affected (0.01 sec) 主服务器2： MariaDB [(none)]> grant replication slave on *.* to ‘replication‘@‘192.168.52.%‘ identified by ‘123456‘; #提权 Query OK, 0 rows affected (0.01 sec)
主服务器1：
MariaDB [(none)]> change master to master_host=‘192.168.52.133‘,master_user=‘replication‘,master_password=‘123456‘,master_log_file=‘mysql_bin.000003‘,master_log_pos=245;
#指定同步服务器的具体信息
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> start slave; #开启同步
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G; #查看状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.52.133
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000003
Read_Master_Log_Pos: 575
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 694
Relay_Master_Log_File: mysql_bin.000003
Slave_IO_Running: Yes #确认开启
Slave_SQL_Running: Yes #确认开启
Master_Server_Id: 2
1 row in set (0.00 sec)
ERROR: No query specified主服务器2：
MariaDB [(none)]> change master to master_host=‘192.168.52.135‘,master_user=‘replication‘,master_password=‘123456‘,master_log_file=‘mysql_bin.000003‘,master_log_pos=245;
#指定同步服务器的具体信息
Query OK, 0 rows affected (0.02 sec)
MariaDB [(none)]> start slave; #开启同步
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> show slave status\G; #查看状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.52.135
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000003
Read_Master_Log_Pos: 575
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 694
Relay_Master_Log_File: mysql_bin.000003
Slave_IO_Running: Yes #确认开启
Slave_SQL_Running: Yes #确认开启
Master_Server_Id: 1
1 row in set (0.00 sec)
ERROR: No query specified3、测试主主同步
主服务器1： MariaDB [(none)]> create database school; #创建数据库school Query OK, 1 row affected (0.01 sec) MariaDB [(none)]> show databases; #查看数据库 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | #创建成功 | test | +--------------------+ 5 rows in set (0.00 sec)
主服务器2： MariaDB [(none)]> show databases; #查看数据库 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | #同步成功 | test | +--------------------+ 5 rows in set (0.00 sec) MariaDB [(none)]> drop database school; #删除数据库school Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> show databases; #查看数据库（删除成功） +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) MariaDB [(none)]>
主服务器1： MariaDB [(none)]> show databases; #查看数据库（同步成功） +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) MariaDB [(none)]>
4、配置从服务器1、2作为主服务器1的从库
从服务器1： [ ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. MariaDB [(none)]> change master to master_host=‘192.168.52.135‘,master_user=‘replication‘,master_password=‘123456‘,master_log_file=‘mysql_bin.000003‘,master_log_pos=245; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]>
从服务器2： [ ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. MariaDB [(none)]> change master to master_host=‘192.168.52.135‘,master_user=‘replication‘,master_password=‘123456‘,master_log_file=‘mysql_bin.000003‘,master_log_pos=245; Query OK, 0 rows affected (0.02 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]>
5、测试多主多从模式
主服务器1： MariaDB [(none)]> create database myschool; #创建数据库myschool Query OK, 1 row affected (0.01 sec) MariaDB [(none)]> show databases; #查看数据库 +--------------------+ | Database | +--------------------+ | information_schema | | myschool | #创建成功 | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) MariaDB [(none)]> quit Bye [ ~]#
主服务器2： MariaDB [(none)]> show databases; #查看数据库 +--------------------+ | Database | +--------------------+ | information_schema | | myschool | #同步成功 | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) MariaDB [(none)]> quit Bye [ ~]#
从服务器1： MariaDB [(none)]> show databases; #查看数据库 +--------------------+ | Database | +--------------------+ | information_schema | | myschool | #同步成功 | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) MariaDB [(none)]> quit Bye [ ~]#
从服务器2： MariaDB [(none)]> show databases; #查看数据库 +--------------------+ | Database | +--------------------+ | information_schema | | myschool | #同步成功 | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) MariaDB [(none)]> quit Bye [ ~]#
三、安装配置MySQL-MMM
1、四台MySQL服务器分别安装MMM
[ ~]# yum -y install mysql-mmm* ........//省略过程 [ ~]# [ ~]# yum -y install mysql-mmm* .......//省略过程 [ ~]# [ ~]# yum -y install mysql-mmm* ........//省略过程 [ ~]# [ ~]# yum -y install mysql-mmm* .........//省略过程 [ ~]#
2、修改/etc/mysql-mmm/mmm_common.conf 配置文件
[ ~]# vim /etc/mysql-mmm/mmm_common.conf
<host default>
cluster_interface ens33
pid_path /run/mysql-mmm-agent.pid
bin_path /usr/libexec/mysql-mmm/
replication_user replication
replication_password 123456
agent_user mmm_agent
agent_password 123456
</host>
<host db1>
ip 192.168.52.135
mode master
peer db2
</host>
<host db2>
ip 192.168.52.133
mode master
peer db1
</host>
<host db3>
ip 192.168.52.134
mode slave
</host>
<host db4>
ip 192.168.52.148
mode slave
</host>
<role writer>
hosts db1, db2
ips 192.168.52.200
mode exclusive
</role>
<role reader>
hosts db3, db4
ips 192.168.52.210, 192.168.52.220
mode balanced
</role>3、将/etc/mysql-mmm/mmm_common.conf配置文件，推送到其它三个服务器
[ ~]# scp /etc/mysql-mmm/mmm_common.conf :/etc/mysql-mmm/ ‘s password: mmm_common.conf 100% 837 1.1MB/s 00:00 [ ~]# scp /etc/mysql-mmm/mmm_common.conf :/etc/mysql-mmm/ ‘s password: mmm_common.conf 100% 837 1.1MB/s 00:00 [ ~]# scp /etc/mysql-mmm/mmm_common.conf :/etc/mysql-mmm/ ‘s password: mmm_common.conf 100% 837 951.0KB/s 00:00
4、在监控服务器monitor上配置本地yum源
[ ~]# hostnamectl set-hostname monitor [ ~]# su [ ~]# wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo --2019-11-25 11:40:39-- http://mirrors.aliyun.com/repo/Centos-7.repo 正在解析主机 mirrors.aliyun.com (mirrors.aliyun.com)... 124.14.2.222, 124.14.2.218, 124.14.2.234, ... 正在连接 mirrors.aliyun.com (mirrors.aliyun.com)|124.14.2.222|:80... 已连接。 已发出 HTTP 请求，正在等待回应... 200 OK 长度：2523 (2.5K) [application/octet-stream] 正在保存至: “/etc/yum.repos.d/CentOS-Base.repo” 100%[====================================================================================>] 2,523 --.-K/s 用时 0s 2019-11-25 11:40:39 (663 MB/s) - 已保存 “/etc/yum.repos.d/CentOS-Base.repo” [2523/2523]) [ ~]# [ ~]# yum -y install epel-release ..........//省略过程 [ ~]# yum clean all && yum makecache .........//省略过程 [ ~]# yum -y install mysql-mmm* .........//省略过程
5、在主服务器1，将配置文件/etc/mysql-mmm/mmm_common.conf推送到monitor
[ ~]# scp /etc/mysql-mmm/mmm_common.conf :/etc/mysql-mmm/ ‘s password: mmm_common.conf 100% 837 1.4MB/s 00:00 [ ~]#
6、修改配置文件/etc/mysql-mmm/mmm_mon.conf
[ ~]# vim /etc/mysql-mmm/mmm_mon.conf
ping_ips 192.168.52.135,192.168.52.133,192.168.52.134,192.168.52.148 #监控服务器IP
auto_set_online 10
<host default>
monitor_user mmm_monitor #用户名
monitor_password 123456 #密码
</host>7、分别在四台MySQL服务器授权monitor访问
主服务器1： [ ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 8 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. MariaDB [(none)]> grant super, replication client, process on *.* to ‘mmm_agent‘@‘192.168.52.%‘ identified by ‘123456‘; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> grant replication client on *.* to ‘mmm_monitor‘@‘192.168.52.%‘ identified by ‘123456‘; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> quit Bye [ ~]#
主服务器2： [ ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 6 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. MariaDB [(none)]> grant super, replication client, process on *.* to ‘mmm_agent‘@‘192.168.52.%‘ identified by ‘123456‘; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> grant replication client on *.* to ‘mmm_monitor‘@‘192.168.52.%‘ identified by ‘123456‘; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> quit Bye [ ~]#
从服务器1： [ ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 5 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. MariaDB [(none)]> grant super, replication client, process on *.* to ‘mmm_agent‘@‘192.168.52.%‘ identified by ‘123456‘; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> grant replication client on *.* to ‘mmm_monitor‘@‘192.168.52.%‘ identified by ‘123456‘; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> quit Bye [ ~]#
从服务器2： [ ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 5 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. MariaDB [(none)]> grant super, replication client, process on *.* to ‘mmm_agent‘@‘192.168.52.%‘ identified by ‘123456‘; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> grant replication client on *.* to ‘mmm_monitor‘@‘192.168.52.%‘ identified by ‘123456‘; Query OK, 0 rows affected (0.02 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> quit Bye [ ~]#
8、分别修改MySQL服务器的/etc/mysql-mmm/mmm_agent.conf文件
主服务器1： [ ~]# vim /etc/mysql-mmm/mmm_agent.conf include mmm_common.conf # The ‘this‘ variable refers to this server. Proper operation requires # that ‘this‘ server (db1 by default), as well as all other servers, have the # proper IP addresses set in mmm_common.conf. this db1 #根据不同主机修改 [ ~]# systemctl start mysql-mmm-agent.service #开启服务 [ ~]# systemctl enable mysql-mmm-agent.service #开机自启动 Created symlink from /etc/systemd/system/multi-user.target.wants/mysql-mmm-agent.service to /usr/lib/systemd/system/mysql-mmm-agent.service. [ ~]#
主服务器2： [ ~]# vim /etc/mysql-mmm/mmm_agent.conf include mmm_common.conf # The ‘this‘ variable refers to this server. Proper operation requires # that ‘this‘ server (db1 by default), as well as all other servers, have the # proper IP addresses set in mmm_common.conf. this db2 #根据不同主机修改 [ ~]# systemctl start mysql-mmm-agent.service #开启服务 [ ~]# systemctl enable mysql-mmm-agent.service #开机自启动 Created symlink from /etc/systemd/system/multi-user.target.wants/mysql-mmm-agent.service to /usr/lib/systemd/system/mysql-mmm-agent.service. [ ~]#
从服务器1： [ ~]# vim /etc/mysql-mmm/mmm_agent.conf include mmm_common.conf # The ‘this‘ variable refers to this server. Proper operation requires # that ‘this‘ server (db1 by default), as well as all other servers, have the # proper IP addresses set in mmm_common.conf. this db3 #根据不同主机修改 [ ~]# systemctl start mysql-mmm-agent.service #开启服务 [ ~]# systemctl enable mysql-mmm-agent.service #开机自启动 Created symlink from /etc/systemd/system/multi-user.target.wants/mysql-mmm-agent.service to /usr/lib/systemd/system/mysql-mmm-agent.service. [ ~]#
从服务器2： [ ~]# vim /etc/mysql-mmm/mmm_agent.conf include mmm_common.conf # The ‘this‘ variable refers to this server. Proper operation requires # that ‘this‘ server (db1 by default), as well as all other servers, have the # proper IP addresses set in mmm_common.conf. this db4 #根据不同主机修改 [ ~]# systemctl start mysql-mmm-agent.service #开启服务 [ ~]# systemctl enable mysql-mmm-agent.service #开机自启动 Created symlink from /etc/systemd/system/multi-user.target.wants/mysql-mmm-agent.service to /usr/lib/systemd/system/mysql-mmm-agent.service. [ ~]#
四、测试MMM高可用
1、开启mysql-mmm-monitor.service
[ ~]# systemctl stop firewalld.service #关闭防火墙 [ ~]# setenforce 0 #关闭增强性安全功能 [ ~]# systemctl start mysql-mmm-monitor.service #开启服务 [ ~]# mmm_control show #测试群集 db1(192.168.52.135) master/ONLINE. Roles: writer(192.168.52.200) db2(192.168.52.133) master/ONLINE. Roles: db3(192.168.52.134) slave/ONLINE. Roles: reader(192.168.52.220) db4(192.168.52.148) slave/ONLINE. Roles: reader(192.168.52.210)
2、检查群集（全部ok）
[ ~]# mmm_control checks all db4 ping [last change: 2019/11/25 12:15:00] OK db4 mysql [last change: 2019/11/25 12:15:00] OK db4 rep_threads [last change: 2019/11/25 12:15:00] OK db4 rep_backlog [last change: 2019/11/25 12:15:00] OK: Backlog is null db2 ping [last change: 2019/11/25 12:15:00] OK db2 mysql [last change: 2019/11/25 12:15:00] OK db2 rep_threads [last change: 2019/11/25 12:15:00] OK db2 rep_backlog [last change: 2019/11/25 12:15:00] OK: Backlog is null db3 ping [last change: 2019/11/25 12:15:00] OK db3 mysql [last change: 2019/11/25 12:15:00] OK db3 rep_threads [last change: 2019/11/25 12:15:00] OK db3 rep_backlog [last change: 2019/11/25 12:15:00] OK: Backlog is null db1 ping [last change: 2019/11/25 12:15:00] OK db1 mysql [last change: 2019/11/25 12:15:00] OK db1 rep_threads [last change: 2019/11/25 12:15:00] OK db1 rep_backlog [last change: 2019/11/25 12:15:00] OK: Backlog is null [ ~]#
3、切换writer为db2
[ ~]# mmm_control move_role writer db2 OK: Role ‘writer‘ has been moved from ‘db1‘ to ‘db2‘. Now you can wait some time and check new roles info! [ ~]# mmm_control show db1(192.168.52.135) master/ONLINE. Roles: db2(192.168.52.133) master/ONLINE. Roles: writer(192.168.52.200) db3(192.168.52.134) slave/ONLINE. Roles: reader(192.168.52.220) db4(192.168.52.148) slave/ONLINE. Roles: reader(192.168.52.210) [ ~]#
4、在monitor安装mariadb服务
[ ~]# yum install mariadb-server mariadb -y .......//省略过程 [ ~]#
5、分别在主服务器1授权monitor访问
主服务器1： [ ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 464 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. MariaDB [(none)]> grant all on *.* to ‘testdb‘@‘192.168.52.150‘ identified by ‘123456‘; #授权 Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]>
6、用monitor登录主服务器虚拟IP
[ ~]# mysql -u testdb -p123456 -h 192.168.52.200 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 584 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | myschool | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) MariaDB [(none)]>
7、关闭主服务器1的MySQL服务
[ ~]# systemctl stop mariadb.service [ ~]#
8、在monitor服务器上删除数据库myschool
monitor： MariaDB [(none)]> drop database myschool; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) MariaDB [(none)]>
9、分别查看三个没有关闭的服务器数据库
主服务器2: MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) MariaDB [(none)]>
从服务器1: MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) MariaDB [(none)]>
从服务器2: MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) MariaDB [(none)]>
10、重启主服务器1的MySQL服务，查看数据库
[ ~]# systemctl stop mariadb.service [ ~]# [ ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 464 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. MariaDB [(none)]> show databases; #查看数据库（同步成功） +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) MariaDB [(none)]>
相关推荐
huanglgln 2020-07-19
林大鹏 2020-02-01
贤冰 2019-12-20
cyhgogogo 2019-12-15
chenjiazhu 2019-12-04
azhou 2019-12-03
ThedakeLaugh 2011-12-26
helloworlddm 2011-08-08
magiclake 2015-01-19
ZoeYen 2019-06-29
tangjianft 2016-03-09
xjywp00 2019-06-27
xushxbigbear微信 2013-05-21
源码佳人 2011-08-26
九天银河技术 2018-08-03
magiclake 2018-08-13
hejunbinfendou 2018-01-16
xkorey 2017-04-08