MySQL high availability solution MMM (MySQL multi-master replication manager)

1. Introduction to MMM:

MMM stands for Multi-Master Replication Manager for MySQL: MySQL multi-master replication manager, based on Perl, is a scalable suite of scripts for monitoring, failover and management of MySQL master-master replication configuration (only one node can be written at any time). MMM can also perform read load balancing on slave servers, so it can be used to start virtual IPs on a group of servers used for replication. In addition, it also has scripts for implementing data backup and resynchronization between nodes. MySQL itself does not provide a replication failover solution. The MMM solution can achieve server failover, thereby achieving high availability of MySQL. MMM not only provides the function of floating IP, but also automatically switches your backend slave servers to the new master server for synchronous replication if the current master server fails, without having to manually change the synchronization configuration. This solution is currently a relatively mature solution. For details, please visit the official website: http://mysql-mmm.org

Advantages : high availability, good scalability, automatic switching in case of failure, and for master-master synchronization, only one database is provided for write operations at the same time to ensure data consistency. When the master server fails, another master server takes over immediately, and other slave servers can switch automatically without manual intervention.

Disadvantages : The monitor node is a single point, but you can also combine keepalived or haertbeat to make it highly available; at least three nodes, there are requirements on the number of hosts, read-write separation needs to be implemented, and a read-write separation program needs to be written on the front end. In a business system with very busy read and write traffic, the performance is not very stable, and problems such as replication delays and switching failures may occur. The MMM solution is not very suitable for environments with high requirements for data security and heavy reading and writing.

Applicable scenarios:

MMM is applicable to scenarios where the database has a large amount of access and can achieve read-write separation.
Mmm's main functions are provided by the following three scripts:
mmm_mond is the monitoring daemon responsible for all monitoring tasks, and decides on node removal (mmm_mond process performs regular heartbeat detection, and if it fails, the write IP will float to another master), etc.
mmm_agentd is an agent daemon running on the MySQL server and is provided to the monitoring node through a simple remote service set.
mmm_control manages the mmm_mond process through the command line. During the entire supervision process, you need to add relevant authorized users in MySQL. The authorized users include an mmm_monitor user and an mmm_agent user. If you want to use mm's backup tool, you also need to add an mmm_tools user.

2. Deployment and Implementation

1. Environment Introduction

OS: centos7.2 (64-bit) Database system: mysql5.7.13

Turn off selinux

Configure ntp to synchronize time

2. Configure the /etc/hosts file on all hosts and add the following content:

192.168.31.83 master1
192.168.31.141 master2
192.168.31.250 slave1
192.168.31.225 slave2
192.168.31.106 monitor1

Install perl, perl-devel, perl-CPAN, libart_lgpl.x86_64, rrdtool.x86_64, rrdtool-perl.x86_64 packages on all hosts
#yum -y install perl-* libart_lgpl.x86_64 rrdtool.x86_64 rrdtool-perl.x86_64

Note: Use centos7 online yum source installation

Install perl related libraries

#cpan -i Algorithm::Diff Class::Singleton DBI DBD::mysql Log::Dispatch Log::Log4perl Mail::Send Net::Ping Proc::Daemon Time::HiRes Params::Validate Net::ARP

3. Install mysql5.7 and configure replication on master1, master2, slave1, and slave2 hosts

Master1 and master2 are master-slave to each other, and slave1 and slave2 are slaves of master1. Add the following content to each MySQL configuration file /etc/my.cnf. Note that server_id cannot be repeated.

master1 host:

log-bin = mysql-bin
binlog_format = mixed
server-id = 1
relay-log = relay-bin
relay-log-index = slave-relay-bin.index
log-slave-updates = 1
auto-increment-increment = 2
auto-increment-offset = 1
master2 host:
log-bin = mysql-bin
binlog_format = mixed
server-id = 2
relay-log = relay-bin
relay-log-index = slave-relay-bin.index
log-slave-updates = 1
auto-increment-increment = 2
auto-increment-offset = 2
slave1 host:
server-id = 3
relay-log = relay-bin
relay-log-index = slave-relay-bin.index
read_only = 1
slave2 host:
server-id = 4
relay-log = relay-bin
relay-log-index = slave-relay-bin.index
read_only = 1

After completing the modification of my.cnf, restart the MySQL service through systemctl restart mysqld

To enable the firewall on the 4 database hosts, either disable the firewall or create access rules:

firewall-cmd --permanent --add-port=3306/tcp
firewall-cmd --reload
Master-slave configuration (master1 and master2 are configured as masters, slave1 and slave2 are configured as slaves of master1):
Authorize on master1:
mysql> grant replication slave on *.* to rep@'192.168.31.%' identified by '123456';
Authorize on master2:
mysql> grant replication slave on *.* to rep@'192.168.31.%' identified by '123456';
Configure master2, slave1, and slave2 as slaves of master1:
Execute show master status on master1; obtain binlog files and Position points
mysql> show master status;
+------------------+----------+--------------+------------------+--------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------------------------------------------+
| mysql-bin.000001 | 452 | | | |
+------------------+----------+--------------+------------------+-----------------------------------------------------+
Execute on master2, slave1 and slave2

mysql> change master to master_host='192.168.31.83',master_port=3306,master_user='rep',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=452;
mysql>slave start;
Verify master-slave replication:
master2 host:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.31.83
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:mysql-bin.000001
Read_Master_Log_Pos: 452
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
slave1 host:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.31.83
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:mysql-bin.000001
Read_Master_Log_Pos: 452
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
slave2 host:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.31.83
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:mysql-bin.000001
Read_Master_Log_Pos: 452
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
If Slave_IO_Running and Slave_SQL_Running are both yes, then the master-slave configuration is OK. Configure master1 as the slave of master2:
Execute show master status on master2 to obtain binlog files and Position points
mysql> show master status;
+------------------+----------+--------------+------------------+--------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------------------------------------------+
| mysql-bin.000001 | 452 | | | |
+------------------+----------+--------------+------------------+----------------------------------------------------+
Execute on master1:
mysql> change master to master_host='192.168.31.141',master_port=3306,master_user='rep',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=452;
mysql> start slave;
Verify master-slave replication:
master1 host:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.31.141
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:mysql-bin.000001
Read_Master_Log_Pos: 452
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
If Slave_IO_Running and Slave_SQL_Running are both yes, then the master-slave configuration is OK.
4.mysql-mmm configuration:
Create a user and proxy account on the four MySQL nodes:
mysql> grant super,replicationclient,process on *.* to 'mmm_agent'@'192.168.31.%' identified by '123456';
Create a monitoring account:
mysql> grant replication client on *.* to 'mmm_monitor'@'192.168.31.%' identified by '123456';
Note 1: Because the previous master-slave replication and master-slave were already ok, I executed it on the master1 server and it was ok.
Check whether monitoring and proxy accounts exist on the three databases of master2, slave1 and slave2
mysql> select user,host from mysql.user where user in ('mmm_monitor','mmm_agent');
+-------------+----------------------------+
| user | host |
+-------------+----------------------------+
| mmm_agent | 192.168.31.% |
| mmm_monitor | 192.168.31.% |
+-------------+------------------------------+
or
mysql> show grants for 'mmm_agent'@'192.168.31.%';
+-----------------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected].% |
+-----------------------------------------------------------------------------------------------------------------------------+
| GRANT PROCESS, SUPER, REPLICATION CLIENT ON *.* TO 'mmm_agent'@'192.168.31.%' |
+-----------------------------------------------------------------------------------------------------------------------------+
mysql> show grants for 'mmm_monitor'@'192.168.31.%';
+-----------------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected].% |
+-----------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'192.168.31.%' |
Note 2:
mmm_monitor user: mmm monitoring is used to check the health of the MySQL server process
mmm_agent user: mmm agent is used to change the read-only mode, replication master server, etc.
5. Install mysql-mmm on the monitor host (192.168.31.106) to install the monitoring program
cd /tmp
wget http://pkgs.fedoraproject.org/repo/pkgs/mysql-mmm/mysql-mmm-2.2.1.tar.gz/f5f8b48bdf89251d3183328f0249461e/mysql-mmm-2.2.1.tar.gz
tar -zxf mysql-mmm-2.2.1.tar.gz
cd mysql-mmm-2.2.1
make install
Install the agent on the database servers (master1, master2, slave1, slave2)
cd /tmp
wget http://pkgs.fedoraproject.org/repo/pkgs/mysql-mmm/mysql-mmm-2.2.1.tar.gz/f5f8b48bdf89251d3183328f0249461e/mysql-mmm-2.2.1.tar.gz
tar -zxf mysql-mmm-2.2.1.tar.gz
cd mysql-mmm-2.2.1
make install

6. Configure mmm

Write the configuration file. The five hosts must be consistent:
After the installation is complete, all configuration files are placed under /etc/mysql-mmm/. Both the management server and the database server must contain a common file mmm_common.conf, the content of which is as follows:
active_master_rolewriter#Indicates the active master role. All db servers must enable the read_only parameter. The monitoring agent for the writer server will automatically turn off the read_only attribute.
<host default>
cluster_interfaceeno16777736#Cluster network interface
pid_path /var/run/mmm_agentd.pid#pid path
bin_path /usr/lib/mysql-mmm/#executable file path
replication_user rep#Replication user
replication_password 123456#Replication user password
agent_usermmm_agent#Agent user
agent_password 123456#Agent user password
</host>
<host master1>#host name of master1
ip 192.168.31.83#master1's ip
mode master#Role attribute, master represents the master
peer master2#The host name of the server that is equivalent to master1, that is, the server host name of master2
</host>
<host master2>#Same concept as master
ip 192.168.31.141
mode master
peer master1
</host>
<host slave1>#host name of the slave library. If there are multiple slave libraries, the same configuration can be repeated.
ip 192.168.31.250#slave ip
mode slave#slave role attribute represents the current host is from
</host>
<host slave2>#Same concept as slave
ip 192.168.31.225
Mode slave
</host>
<role writer>#writer role configuration
hosts master1,master2#Host name of the server that can perform write operations. If you do not want to switch write operations, you can only configure the master here. This can also avoid switching writes due to network delays. However, once the master fails, the current MMM will have no writer and only external read operations.
ips 192.168.31.2#Virtual IP for external write operations
mode exclusive#exclusive means that only one master is allowed to exist, that is, only one write IP can be provided
</role>
<role reader>#read role configuration
hosts master2,slave1,slave2#The host name of the server that provides read operations to the outside world. Of course, master can also be added here
ips 192.168.31.3, 192.168.31.4, 192.168.31.5#Virtual ips for external read operations. These three ips and hosts do not correspond one to one, and the number of ips and hosts can also be different. If configured in this way, one of the hosts will be assigned two ips
mode balanced#balanced represents load balancing
</role>
At the same time, copy this file to other servers without changing the configuration
#for host in master1 master2 slave1 slave2 ; do scp /etc/mysql-mmm/mmm_common.conf $host:/etc/mysql-mmm/ ; done
Agent file configuration Edit /etc/mysql-mmm/mmm_agent.conf on 4 MySQL node machines
On the database server, there is also a mmm_agent.conf that needs to be modified, and its content is:
includemmm_common.conf
this master1
Note: This configuration only configures the db server. The monitoring server does not need to be configured. The host name after this is changed to the host name of the current server.
Start the agent process. Add the following content below #!/bin/sh in the script file /etc/init.d/mysql-mmm-agent
source /root/.bash_profile
Add it as a system service and set it to start automatically
#chkconfig --add mysql-mmm-agent
#chkconfigmysql-mmm-agent on
#/etc/init.d/mysql-mmm-agent start
Note: The purpose of adding source /root/.bash_profile is to enable the mysql-mmm-agent service to start automatically on startup.
The only difference between automatic startup and manual startup is activating a console. This means that when the service is started, it may fail to start due to the lack of environment variables, and the error message is as follows:
Daemon bin: '/usr/sbin/mmm_agentd'
Daemon pid: '/var/run/mmm_agentd.pid'
Starting MMM Agent daemon... Can't locate Proc/Daemon.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/sbin/mmm_agentd line 7.
BEGIN failed--compilation aborted at /usr/sbin/mmm_agentd line 7.
failed

Solution:

# cpanProc::Daemon
# cpan Log::Log4perl
# /etc/init.d/mysql-mmm-agent start
Daemon bin: '/usr/sbin/mmm_agentd'
Daemon pid: '/var/run/mmm_agentd.pid'
Starting MMM Agent daemon... Ok
# netstat -antp | grep mmm_agentd
tcp 0 0 192.168.31.83:9989 0.0.0.0:* LISTEN 9693/mmm_agentd
Configuring the Firewall
firewall-cmd --permanent --add-port=9989/tcp
firewall-cmd --reload
Edit /etc/mysql-mmm/mmm_mon.conf on the monitor host
includemmm_common.conf

<monitor>
ip 127.0.0.1##For security reasons, set it to listen only on the local machine. mmm_mond listens to 9988 by default
pid_path /var/run/mmm_mond.pid
bin_path /usr/lib/mysql-mmm/
status_path/var/lib/misc/mmm_mond.status
ping_ips192.168.31.83,192.168.31.141,192.168.31.250,192.168.31.225#IP address list for testing network availability. As long as one of the addresses can be pinged, it means the network is normal. Do not enter the local address here.
auto_set_online 0#Set the time for automatic online. The default is to set it online after 60 seconds. The default is 60 seconds. Here, setting it to 0 means it will be online immediately.
</monitor>

<check default>
check_period 5
trap_period 10
timeout 2
#restart_after 10000
max_backlog 86400
</check>
check_period
Description: The default check period is 5 seconds.
Default value: 5s
trap_period
Description: If a node fails to be detected for trap_period seconds, it is considered to have failed.
Default value: 10s
timeout
Description: Check timeout time Default value: 2s
restart_after
Description: Restart the checker process after completing restart_after checks. Default value: 10000
max_backlog
Description: Records the maximum number of times to check the rep_backlog log. Default value: 60

<host default>
monitor_usermmm_monitor#Monitor the user of the db server
monitor_password 123456#Monitor the password of the db server
</host>
debug 0#debug 0 is normal mode, 1 is debug mode to start the monitoring process:
Add the following content below #!/bin/sh in the script file /etc/init.d/mysql-mmm-agent
source /root/.bash_profile
Add it as a system service and set it to start automatically
#chkconfig --add mysql-mmm-monitor
#chkconfigmysql-mmm-monitor on
#/etc/init.d/mysql-mmm-monitor start

Startup error:

Starting MMM Monitor daemon: Can not locate Proc/Daemon.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/sbin/mmm_mond line 11.
BEGIN failed--compilation aborted at /usr/sbin/mmm_mond line 11.
failed
Solution: Install the following perl libraries
#cpanProc::Daemon
#cpan Log::Log4perl
[root@monitor1 ~]# /etc/init.d/mysql-mmm-monitor start
Daemon bin: '/usr/sbin/mmm_mond'
Daemon pid: '/var/run/mmm_mond.pid'
Starting MMM Monitor daemon: Ok
[root@monitor1 ~]# netstat -anpt | grep 9988
tcp 0 0 127.0.0.1:9988 0.0.0.0:* LISTEN 8546/mmm_mond
Note 1: If the configuration file is modified on either the database side or the monitoring side, the agent process and the monitoring process need to be restarted.
Note 2: MMM startup sequence: start the monitor first, then start the agent

Check the cluster status:

[root@monitor1 ~]# mmm_control show
master1(192.168.31.83) master/ONLINE. Roles: writer(192.168.31.2)
master2(192.168.31.141) master/ONLINE. Roles: reader(192.168.31.5)
slave1(192.168.31.250) slave/ONLINE. Roles: reader(192.168.31.4)
slave2(192.168.31.225) slave/ONLINE. Roles: reader(192.168.31.3)

If the server status is not ONLINE, you can use the following command to bring the server online, for example:

#mmm_controlset_online hostname

For example: [root@monitor1 ~]#mmm_controlset_onlinemaster1
From the above display, we can see that the VIP of the write request is on master1, and all slave nodes also regard master1 as the master node.
Check whether VIP is enabled
[root@master1 ~]# ipaddr show dev eno16777736
eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP>mtu 1500 qdiscpfifo_fast state UP qlen 1000
link/ether 00:0c:29:6d:2f:82 brdff:ff:ff:ff:ff:ff
inet 192.168.31.83/24 brd 192.168.31.255 scope global eno16777736
valid_lft forever preferred_lft forever
inet 192.168.31.2/32 scope global eno16777736
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe6d:2f82/64 scope link
valid_lft forever preferred_lft forever
[root@master2 ~]# ipaddr show dev eno16777736
eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP>mtu 1500 qdiscpfifo_fast state UP qlen 1000
link/ether 00:0c:29:75:1a:9c brdff:ff:ff:ff:ff:ff
inet 192.168.31.141/24 brd 192.168.31.255 scope global dynamic eno16777736
valid_lft 35850sec preferred_lft 35850sec
inet 192.168.31.5/32 scope global eno16777736
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe75:1a9c/64 scope link
valid_lft forever preferred_lft forever
[root@slave1 ~]# ipaddr show dev eno16777736
eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP>mtu 1500 qdiscpfifo_fast state UP qlen 1000
link/ether 00:0c:29:02:21:19 brdff:ff:ff:ff:ff:ff
inet 192.168.31.250/24 brd 192.168.31.255 scope global dynamic eno16777736
valid_lft 35719sec preferred_lft 35719sec
inet 192.168.31.4/32 scope global eno16777736
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe02:2119/64 scope link
valid_lft forever preferred_lft forever
[root@slave2 ~]# ipaddr show dev eno16777736
eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP>mtu 1500 qdiscpfifo_fast state UP qlen 1000
link/ether 00:0c:29:e2:c7:fa brdff:ff:ff:ff:ff:ff
inet 192.168.31.225/24 brd 192.168.31.255 scope global dynamic eno16777736
valid_lft 35930sec preferred_lft 35930sec
inet 192.168.31.3/32 scope global eno16777736
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fee2:c7fa/64 scope link
valid_lft forever preferred_lft forever
Check the direction of the main mysql on the master2, slave1, and slave2 hosts
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.31.83
Master_User: rep
Master_Port: 3306
Connect_Retry: 60

MMM High Availability Testing:

The server uses the VIP address for reading and writing. When a failure occurs, the VIP will drift to other nodes and provide services from other nodes.
First check the status of the entire cluster, you can see that the entire cluster is normal
[root@monitor1 ~]# mmm_control show
master1(192.168.31.83) master/ONLINE. Roles: writer(192.168.31.2)
master2(192.168.31.141) master/ONLINE. Roles: reader(192.168.31.5)
slave1(192.168.31.250) slave/ONLINE. Roles: reader(192.168.31.4)
slave2(192.168.31.225) slave/ONLINE. Roles: reader(192.168.31.3)
Simulate the downtime of master1, manually stop the MySQL service, and observe the monitor log. The log of master1 is as follows:
[root@monitor1 ~]# tail -f /var/log/mysql-mmm/mmm_mond.log
2017/01/09 22:02:55 WARN Check 'rep_threads' on 'master1' is in unknown state! Message: UNKNOWN: Connect error (host = 192.168.31.83:3306, user = mmm_monitor)! Can't connect to MySQL server on '192.168.31.83' (111)
2017/01/09 22:02:55 WARN Check 'rep_backlog' on 'master1' is in unknown state! Message: UNKNOWN: Connect error (host = 192.168.31.83:3306, user = mmm_monitor)! Can't connect to MySQL server on '192.168.31.83' (111)
2017/01/09 22:03:05 ERROR Check 'mysql' on 'master1' has failed for 10 seconds! Message: ERROR: Connect error (host = 192.168.31.83:3306, user = mmm_monitor)! Can't connect to MySQL server on '192.168.31.83' (111)
2017/01/09 22:03:07 FATAL State of host 'master1' changed from ONLINE to HARD_OFFLINE (ping: OK, mysql: not OK)
2017/01/09 22:03:07 INFO Removing all roles from host 'master1':
2017/01/09 22:03:07 INFO Removed role 'writer(192.168.31.2)' from host 'master1'
2017/01/09 22:03:07 INFO Orphaned role 'writer(192.168.31.2)' has been assigned to 'master2'
View the latest status of the cluster
[root@monitor1 ~]# mmm_control show
master1(192.168.31.83) master/HARD_OFFLINE. Roles:
master2(192.168.31.141) master/ONLINE. Roles: reader(192.168.31.5), writer(192.168.31.2)
slave1(192.168.31.250) slave/ONLINE. Roles: reader(192.168.31.4)
slave2(192.168.31.225) slave/ONLINE. Roles: reader(192.168.31.3)
From the displayed results, we can see that the status of master1 has changed from ONLINE to HARD_OFFLINE, and the write VIP has been transferred to the master2 host.
Check the status of all db server clusters
[root@monitor1 ~]# mmm_control checks all
master1 ping [last change: 2017/01/09 21:31:47] OK
master1 mysql [last change: 2017/01/09 22:03:07] ERROR: Connect error (host = 192.168.31.83:3306, user = mmm_monitor)! Can't connect to MySQL server on '192.168.31.83' (111)
master1 rep_threads [last change: 2017/01/09 21:31:47] OK
master1 rep_backlog [last change: 2017/01/09 21:31:47] OK: Backlog is null
slave1 ping [last change: 2017/01/09 21:31:47] OK
slave1mysql [last change: 2017/01/09 21:31:47] OK
slave1 rep_threads [last change: 2017/01/09 21:31:47] OK
slave1 rep_backlog [last change: 2017/01/09 21:31:47] OK: Backlog is null
master2 ping [last change: 2017/01/09 21:31:47] OK
master2 mysql [last change: 2017/01/09 21:57:32] OK
master2 rep_threads [last change: 2017/01/09 21:31:47] OK
master2 rep_backlog [last change: 2017/01/09 21:31:47] OK: Backlog is null
slave2 ping [last change: 2017/01/09 21:31:47] OK
slave2mysql [last change: 2017/01/09 21:31:47] OK
slave2 rep_threads [last change: 2017/01/09 21:31:47] OK
slave2 rep_backlog [last change: 2017/01/09 21:31:47] OK: Backlog is null
From the above, we can see that master1 can be pinged, which means that only the service has died.

View the IP address of the master2 host:

[root@master2 ~]# ipaddr show dev eno16777736
eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP>mtu 1500 qdiscpfifo_fast state UP qlen 1000
link/ether 00:0c:29:75:1a:9c brdff:ff:ff:ff:ff:ff
inet 192.168.31.141/24 brd 192.168.31.255 scope global dynamic eno16777736
valid_lft 35519sec preferred_lft 35519sec
inet 192.168.31.5/32 scope global eno16777736
valid_lft forever preferred_lft forever
inet 192.168.31.2/32 scope global eno16777736
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe75:1a9c/64 scope link
valid_lft forever preferred_lft forever
slave1 host:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.31.141
Master_User: rep
Master_Port: 3306
slave2 host:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.31.141
Master_User: rep
Master_Port: 3306
Start the mysql service of the master1 host and observe the monitor log. The log of master1 is as follows:
[root@monitor1 ~]# tail -f /var/log/mysql-mmm/mmm_mond.log
2017/01/09 22:16:56 INFO Check 'mysql' on 'master1' is ok!
2017/01/09 22:16:56 INFO Check 'rep_backlog' on 'master1' is ok!
2017/01/09 22:16:56 INFO Check 'rep_threads' on 'master1' is ok!
2017/01/09 22:16:59 FATAL State of host 'master1' changed from HARD_OFFLINE to AWAITING_RECOVERY
From the above, you can see that the status of master1 changes from hard_offline to awaiting_recovery. Use the following command to bring the server online:
[root@monitor1 ~]#mmm_controlset_onlinemaster1
View the latest status of the cluster
[root@monitor1 ~]# mmm_control show
master1(192.168.31.83) master/ONLINE. Roles:
master2(192.168.31.141) master/ONLINE. Roles: reader(192.168.31.5), writer(192.168.31.2)
slave1(192.168.31.250) slave/ONLINE. Roles: reader(192.168.31.4)
slave2(192.168.31.225) slave/ONLINE. Roles: reader(192.168.31.3)
You can see that the master database will not take over the master when it is started, until the existing master goes down again.
Summarize
(1) The failure of the master2 candidate master node does not affect the status of the cluster, but only removes the read status of the master2 candidate node.
(2) If the master1 master node fails, the master2 candidate master node will take over the write role. Slave1 and slave2 will point to the new master2 master library for replication. Slave1 and slave2 will automatically change master to master2.
(3) If the master database of master1 crashes and the master2 replication application lags behind master1, the data becomes writable by the master, and the data master cannot guarantee consistency at this time.
If master2, slave1, slave2 lag behind master1, and master1 crashes, slave1 and slave2 will wait for the data to catch up with db1 before re-pointing to the new master node2 for replication. At this time, the data synchronization consistency cannot be guaranteed.
(4) If the MMM high-availability architecture is used, the configuration of the master and master-slave nodes is the same, and semi-synchronization is enabled to further improve security or MariaDB/MySQL5.7 is used for multi-threaded slave replication to improve replication performance.

Attachment:

1. Log files:
Log files are often the key to analyzing errors, so you must be good at using log files to analyze problems.
DB side: /var/log/mysql-mmm/mmm_agentd.log
Monitoring end: /var/log/mysql-mmm/mmm_mond.log
2. Command file:
mmm_agentd: startup file of db agent process
mmm_mond: startup file for monitoring process
mmm_backup: backup files
mmm_restore: restore files
mmm_control: monitoring operation command file
There is only mmm_agentd program on the db server side, and the others are on the monitor server side.
3. Usage of mmm_control
The mmm_control program can be used to monitor cluster status, switch writers, set online\offline operations, etc.
Valid commands are:
help - show this message #Help information
ping - ping monitor #ping the current cluster to see if it is normal
show - show status #Cluster online status check
checks [<host>|all [<check>|all]] - show checks status#Execute monitoring and checking operations
set_online<host> - set host <host> online #Set the host to online
set_offline<host> - set host <host> offline #Set the host to offline
mode - print current mode. #Print out the current mode
set_active - switch into active mode.

set_manual - switch into manual mode.
set_passive - switch into passive mode.
move_role [--force] <role><host> - move exclusive role <role> to host <host> #Remove the writer server to the specified host server (Only use --force if you know what you are doing!)
set_ip<ip><host> - set role with ip<ip> to host <host>
Check the status of all db server clusters:
[root@monitor1 ~]# mmm_control checks all
Check items include: ping, whether mysql is running normally, whether the replication thread is normal, etc. Check the online status of the cluster environment:
[root@monitor1 ~]# mmm_control show
Perform offline operation on the specified host:
[root@monitor1 ~]# mmm_controlset_offline slave2
Execute the onine operation on the specified host:
[root@monitor1 ~]# mmm_controlset_online slave2
Execute write switch (manual switch):
View the master corresponding to the current slave
[root@slave2 ~]# mysql -uroot -p123456 -e 'show slave status\G;'
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.31.141
To switch writers, make sure that the writer attribute in the mmm_common.conf file has the corresponding host configured, otherwise the switch cannot be made.
[root@monitor1 ~]# mmm_controlmove_role writer master1
OK: Role 'writer' has been moved from 'master2' to 'master1'. Now you can wait some time and check new roles info!
[root@monitor1 ~]# mmm_control show
master1(192.168.31.83) master/ONLINE. Roles: writer(192.168.31.2)
master2(192.168.31.141) master/ONLINE. Roles: reader(192.168.31.5)
slave1(192.168.31.250) slave/ONLINE. Roles: reader(192.168.31.4)
slave2(192.168.31.225) slave/ONLINE. Roles: reader(192.168.31.3)
The save slave automatically switched to the new master
[root@slave2 ~]# mysql -uroot -p123456 -e 'show slave status\G;'
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.31.83

4. Other processing issues

If you do not want the writer to switch from master to backup (including the master-slave delay which will also cause the writer VIP to switch), you can remove backup in <role write> when configuring /etc/mysql-mmm/mmm_common.conf
<role writer>#writer role configuration
hosts master1 #Only one Hosts is configured here
ips 192.168.31.2#Virtual IP for external write operations
mode exclusive #exclusive means that only one master is allowed to exist, that is, only one write IP can be provided
</role>
In this way, when master1 fails, the writer's write operation will not be switched to the master2 server, and the slave will not point to the new master. At this time, the current MMM will provide write services to the outside world.

5. Summary

1. The virtual IP that provides external reading and writing is controlled by the monitor program. If the monitor is not started, the db server will not be assigned a virtual IP. However, if a virtual IP has been assigned, when the monitor program closes the previously assigned virtual IP, it will not be closed immediately and external programs can still connect and access (as long as the network is not restarted). The advantage of this is that the reliability requirements for the monitor will be lower. However, if one of the db servers fails at this time, the switch cannot be processed, that is, the original virtual IP remains unchanged, and the virtual IP of the failed DB will become inaccessible.

2. The agent program is controlled by the monitor program to handle write switching, slave switching and other operations. If the monitor process is shut down, the agent process will not be able to handle failures by itself.

3. The monitor program is responsible for monitoring the status of the db server, including the Mysql database, whether the server is running, whether the replication thread is normal, the master-slave delay, etc.; it is also used to control the agent program to handle faults.

4. The monitor will monitor the status of the db server every few seconds. If the db server has changed from a fault to a normal state, the monitor will automatically set it to the online state after 60 seconds (the default is 60 seconds and can be set to other values). This is determined by the configuration file parameter "auto_set_online" of the monitoring end. There are three states of the cluster server: HARD_OFFLINE→AWAITING_RECOVERY→online
5. By default, the monitor will control mmm_agent to change the read_only of the writer db server to OFF, and the read_only of other db servers to ON. Therefore, for strictness, you can add read_only=1 to the my.cnf files of all servers to control the writer and read by the monitor. The root user and the replication user are not affected by the read_only parameter.