Building MySQL Replication cluster high availability architecture based on MMM

MMM introduction

MMM is Multi-Master Replication Manager for MySQL It is a multi master replication manager provided by mysql. Its core is a set of scripts written in perl. In fact, mmm is a relatively early and even a little old way to build a highly available MySQL architecture, but because it has certain application scenarios, this article will demonstrate how to build an MMM architecture.

MMM consists of two components:

  • Monitor: monitor the status of the database in the cluster, issue the switch command when there is an exception, and generally deploy separately from the database
  • Agent: the agent process running on each MySQL server, the executor of the monitor command, completing the monitoring probe work and specific service settings, such as setting up VIP and pointing to the new synchronization node

MMM's main role:

  • Monitor and manage the primary and secondary replication topology of MySQL. When the current primary server fails, perform the master-slave switch and failover between the primary and secondary servers

What MMM provides:

  • MMM can monitor the health of MySQL master-slave replication, including the status of MySQL server and the status of slave copy activity
  • It can fail over when the primary database goes down, and automatically configure the replication of other slave databases to the new primary database
  • A write virtual IP is provided for the master database, and the virtual IP can be migrated automatically when the master and slave servers have problems
  • When multiple slave databases are configured, multiple virtual read IP S can be configured to realize the load balancing function with the third-party load balancing software (such as LVS)

MMM how to synchronize the log points of the slave database after the master-slave switch:

  • Mmm is not safe for this aspect of processing, just simply and roughly let the slave database synchronize the current log point of the new master database, so using MMM in a busy system is likely to cause data loss

Start to build MMM architecture

The MMM architecture to be built in this paper is as follows:

Take the above architecture as an example to describe the process of failover. Now assume that Master1 is down:

  1. Monitor detected Master1 connection failure
  2. Monitor sends set offline instruction to Agent of Master1
  3. If Master1 Agent survives, log off to write VIP, and try to set Master1 to read only = 1
  4. Moniotr sends set online instruction to Master2
  5. The Master2 Agent receives the instruction and executes select master POS wait() to wait for the synchronization to complete
  6. Master2 Agent writes VIP online and sets master2 node to read only = 0
  7. Monitor sends the instruction to change the synchronization object to the Agent of each Slave node
  8. Each Slave node synchronizes data to the new Master

From the whole process, it can be seen that if the master node fails, mmm will automatically switch without human intervention. At the same time, we can see some problems, that is, after the database is hung up, only the switch is made, and the missing data will not be supplemented actively, so MMM will have the risk of data inconsistency.

Description of resources needed to build MMM architecture:

Name Number Explain
Master server 2 Primary primary replication configuration for primary standby mode
Slave server 0-N 0 or more slave servers can be configured, but too many are not recommended
Monitoring server 1 Install MMM to monitor MySQL replication cluster
IP address 2*(n+1) n is the number of MySQL servers
Monitoring users 1 MySQL user for monitoring database status (at least with replication client permission)
Proxy user 1 MySQL user for MMM agent (with at least super, replication client, and process permissions)
Replication user 1 The user used to configure MySQL master-slave replication (at least with replication slave permission)

Description of the machine used in this document:

Name IP role
master-01 192.168.190.146 Main library
master-02 192.168.190.148 Reserve library
slave-01 192.168.190.149 Slave Library
manager 192.168.190.147 Cluster manager (MMM)

Environment Version Description:

  • Operating system version: CentOS 7
  • MySQL version: 8.0.19
  • MMM version: 2.2.1

Additional notes:

  • The guys who will come to know the MMM architecture must have mastered the MySQL installation mode, and introduced MySQL installation There are also many articles, so in order to reduce unnecessary space, this article will not demonstrate the installation of MySQL. The machines used in this article have already installed MySQL in advance.

Configure primary and secondary replication and synchronization cluster

1. On master-01 and master-02, use the following statements to create MySQL users for primary and primary replication respectively:

create user 'repl'@'%' identified with mysql_native_password by 'Abc_123456';
grant replication slave on *.* to 'repl'@'%';
flush privileges;
  • Tips: after creating an account, it's best to log in to each other at two nodes to ensure that the account is available

2. Modify the MySQL configuration file on master-01:

[root@master-01 ~]# vim /etc/my.cnf
[mysqld]
# Set the id of the node
server_id=101
# Enable binlog and specify the name of binlog file
log_bin=mysql_bin
# Open relay log and specify the name of the relay log file
relay_log=relay_bin
# Record the synchronization content of relaylog to binlog
log_slave_updates=on

The same configuration is also added to the configuration file of master-02, except that the server ID is different:

[root@master-02 ~]# vim /etc/my.cnf
[mysqld]
server_id=102
log_bin=mysql_bin
relay_log=relay_bin
log_slave_updates=on

Next, configure slave-01. Since this node does not exist as a standby database but as a separate slave database, you do not need to turn on the log slave updates parameter

[root@slave-01 ~]# vim /etc/my.cnf
[mysqld]
server_id=103
log_bin=mysql_bin
relay_log=relay_bin

After modifying the above configuration file, restart the MySQL services on the three nodes respectively:

[root@master-01 ~]# systemctl restart mysqld
[root@master-02 ~]# systemctl restart mysqld
[root@slave-01 ~]# systemctl restart mysqld

Configure master-02 master-01 master-slave relationship

Enter the MySQL command line terminal of master-01, and query the binary log currently in use and the current execution binary log location of master-01 through the following statement:

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001 |      155 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

Record the values of File and Position, then enter the MySQL command-line terminal of master-02, and execute the following statements respectively:

MySQL > stop slave; -- stop master-slave synchronization
 MySQL > change master to master host = 192.168.190.146 ', master port = 3306, master user = repl, master password = ABC 123456, master log file = MySQL bin. 00000 1', master log POS = 155; -- configure the connection information of the master-01 node and the location from which the binlog file is copied
 MySQL > start slave; -- start master slave synchronization

After configuring the master-slave relationship, use the show slave status\G; statement to view the master-slave synchronization status. If the slave IO running and slave SQL running values are Yes, the master-slave synchronization status is normal

Configure master-01 master-02 master-slave relationship

In order to realize master master master replication, master-01 and master-02 need to be master-slave relationship, so master-01 and master-02 need to be configured. Enter the MySQL command line terminal of master-02, and query the binary log currently in use and the current execution binary log location of master-02 through the following statement:

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001 |      155 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

Record the values of File and Position, then enter the MySQL command line terminal of master-01, and execute the following statements respectively:

mysql> stop slave;
mysql> change master to master_host='192.168.190.148', master_port=3306, master_user='repl', master_password='Abc_123456', master_log_file='mysql_bin.000001', master_log_pos=155;
mysql> start slave;

After the same configuration is completed, use the show slave status\G; statement to view the master-slave synchronization status. If the slave IO running and slave SQL running values are all Yes, the master-slave synchronization status is normal

Configure master-slave relationship between slave-01 and master-01

Next, configure the master-slave relationship between the slave database and the master database. Here, it is the same as master-02. Only when the data on master-01 is modified during this period, the log point needs to be retrieved:

mysql> stop slave;
mysql> change master to master_host='192.168.190.146', master_port=3306, master_user='repl', master_password='Abc_123456', master_log_file='mysql_bin.000001', master_log_pos=155;
mysql> start slave;

Building MMM services

1. Install the epel source on all machines:

yum install -y epel-release

2. Install the MMM proxy client on all primary and secondary nodes:

yum install -y mysql-mmm-agent

3. Install all MMM packages on the manager node:

yum install -y mysql-mmm*

4. Then create MySQL users for monitoring and agent on master-01:

-- Monitoring users
create user 'mmm_monitor'@'%' identified with mysql_native_password by 'Abc_123456';
grant replication client on *.* to 'mmm_monitor'@'%';

-- Proxy user
create user 'mmm_agent'@'%' identified with mysql_native_password by 'Abc_123456';
grant super, replication client, process on *.* to 'mmm_agent'@'%';
flush privileges;

Because the master and slave are configured, the other two database nodes will synchronize these new users. Execute the following statements on the other two nodes to query:

mysql> use mysql;
mysql> select host,user,plugin from user;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| %         | mmm_agent        | mysql_native_password |
| %         | mmm_monitor      | mysql_native_password |
| %         | repl             | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
| localhost | root             | caching_sha2_password |
+-----------+------------------+-----------------------+

5. Edit the MMM ﹐ common.conf configuration file of all nodes, including monitoring nodes, mainly to configure the information of current nodes and other nodes in the cluster. Take the master-01 node as an example, and its configuration is as follows:

[root@master-01 ~]# vim /etc/mysql-mmm/mmm_common.conf
active_master_role      writer

<host default>
    cluster_interface       ens32  # The network card name of the current node, which is used to bind virtual IP, can be queried by ip addr command
    pid_path                /run/mysql-mmm-agent.pid  # pid file storage path
    bin_path                /usr/libexec/mysql-mmm/  # Path where executable files are stored
    replication_user        repl  # MySQL users for replication
    replication_password    Abc_123456  # repl user's password
    agent_user              mmm_agent   # MySQL user for proxy
    agent_password          Abc_123456  # Password of MMM agent user
</host>

# Configure ip address and role of master-01
<host db1>
    ip      192.168.190.146
    mode    master
    peer    db2
</host>

# Configure ip address and role of master-02
<host db2>
    ip      192.168.190.148
    mode    master
    peer    db1
</host>

# Configure the ip address and role of slave-01
<host db3>
    ip      192.168.190.149
    mode    slave
</host>

# Configure the library responsible for write operations
<role writer>
    hosts   db1, db2  # Specifies the writable library, which is the name defined in the host tag above
    ips     192.168.190.90  # Configure write virtual IP, multiple can be separated by commas
    mode    exclusive   # Indicates that there is only one master database providing services at the same time
</role>

# Configure the library responsible for read operations
<role reader>
    hosts   db1, db2, db3  # Specify a readable Library
    ips     192.168.190.91,192.168.190.92,192.168.190.93  # Configure read virtual IP
    mode    balanced  # Indicates load balancing read requests to the above configured db
</role>

The other three nodes are also configured in the same way. Except that the network card name may be different, other parameters should be consistent.

6. Then configure the MMM agent.conf file of each node to declare the name of the current node defined in the host tag. The configuration of the master-01 node is as follows:

[root@master-01 ~]# vim /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this db1

The configuration of the master-02 node is as follows:

[root@master-02 ~]# vim /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this db1

The slave-01 node is configured as follows:

[root@slave-01 ~]# vim /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this db3

7. Next, configure the mmm_mon.conf file on the monitoring node. The configuration content is as follows:

[root@manager ~]# vim /etc/mysql-mmm/mmm_mon.conf
include mmm_common.conf

<monitor>
    ip                  127.0.0.1
    pid_path            /run/mysql-mmm-monitor.pid
    bin_path            /usr/libexec/mysql-mmm
    status_path         /var/lib/mysql-mmm/mmm_mond.status
    ping_ips            192.168.190.146,192.168.190.148,192.168.190.149  # Configure the IP address of each node in the cluster
    auto_set_online     60  # Set the time, in seconds, when the node automatically goes online after downtime and recovery

    # The kill_host_bin does not exist by default, though the monitor will
    # throw a warning about it missing.  See the section 5.10 "Kill Host
    # Functionality" in the PDF documentation.
    #
    # kill_host_bin     /usr/libexec/mysql-mmm/monitor/kill_host
    #
</monitor>

<host default>
    # Configure MySQL users and passwords for monitoring
    monitor_user        mmm_monitor
    monitor_password    Abc_123456
</host>

debug 0

8. Start the MMM proxy service for all master and slave nodes:

[root@master-01 ~]# systemctl start mysql-mmm-agent
[root@master-02 ~]# systemctl start mysql-mmm-agent
[root@slave-01 ~]# systemctl start mysql-mmm-agent

The agent service will listen to port 9989 by default. If the firewall is enabled, the port needs to be opened:

firewall-cmd --zone=public --add-port=9989/tcp --permanent
firewall-cmd --reload

9. Start the monitoring service on the monitoring node:

[root@manager ~]# systemctl start mysql-mmm-monitor

10. After all the above steps are completed, you can view the status of each node in the cluster and its assigned virtual IP by using the MMM ﹣ control show command on the monitoring node, as shown in the following example:

[root@manager ~]# mmm_control show
  db1(192.168.190.146) master/ONLINE. Roles: reader(192.168.190.91), writer(192.168.190.90)
  db2(192.168.190.148) master/ONLINE. Roles: reader(192.168.190.93)
  db3(192.168.190.149) slave/ONLINE. Roles: reader(192.168.190.92)

[root@manager ~]#

test

So far, we have completed the construction of MMM high availability architecture, and then we will carry out some simple tests on it. For example, whether the virtual IP can be ping ed normally under the test, after all, the virtual IP is connected when the application side accesses the database, so the first thing to do is to ensure that the virtual IP can be accessed. As follows:

After ping, use Navicat and other remote connection tools to test whether the connection is normal:

After confirming that each virtual IP can be accessed normally, test whether MMM can fail over normally. First, stop the MySQL service on the master-01:

[root@master-01 ~]# systemctl stop mysqld

Under normal circumstances, you can see that the master-01 node is offline by using the MMM control show command on the monitoring node, and the virtual IP used for writing is normally switched to the master-02 node:

[root@manager ~]# mmm_control show
  db1(192.168.190.146) master/HARD_OFFLINE. Roles: 
  db2(192.168.190.148) master/ONLINE. Roles: reader(192.168.190.93), writer(192.168.190.90)
  db3(192.168.190.149) slave/ONLINE. Roles: reader(192.168.190.91), reader(192.168.190.92)

[root@manager ~]# 

Then enter the MySQL terminal on the slave-01 node. Before, we configured master-01 as the main database of slave-01. Now that master-01 has been stopped, you can see that the master database of slave-01 has been switched to master-02 by MMM

After the above tests, we can see that the MMM architecture we built can operate normally, which has enabled the Replication cluster to have the basic high availability capability. Even after the primary library is offline, it can switch to the standby library normally, and also correctly establish the relationship between the slave library and the new primary Library.

Advantages and disadvantages of MMM architecture

Advantage:

  • Perl script language is used for development and fully open source, and developers can carry out secondary development according to their own needs
  • It provides delay monitoring of slave server and read-write VIP (virtual IP), which makes the change of server role transparent to front-end application. When a large number of master-slave delays or master-slave replication links are interrupted in the slave database, the read virtual IP on the slave database can be drifted to other normal readable nodes in the cluster
  • It provides the resynchronization function of the slave database to the new master database after the master database fails over, so it is easy to re launch the failed master database, which makes the Replication cluster highly available

Disadvantages:

  • MMM is an old tool, and its last version was released several years ago, so there will be some small bug s, and it does not support the new GTID based replication in MySQL 5.6 +, only supports the replication based on log points
  • No read load balancing function is provided, and additional tools such as LVS need to be introduced for implementation
  • In the process of master-slave switch, it is easy to cause data loss or repeated transaction commit. Because MMM does not compare multiple log points from the database, it directly selects the standby database to switch. Because the Replication cluster is asynchronous Replication, when the synchronization delay of the standby database is large, the log point from the database may be more updated than that of the standby database. Therefore, when the primary database goes offline unexpectedly, the MMM will forcibly switch to the standby database, which may result in data loss or duplicate transactions committed from the secondary database
  • MMM does not provide the relevant high availability function, so the monitoring node itself has a single point of failure, and the high availability of Replication cluster depends on the monitoring node, when the monitoring node hangs up, the Replication cluster will no longer have high availability. However, we can introduce a third-party tool to do dual machine hot standby for monitoring nodes, such as kept alive

From the comprehensive advantages and disadvantages, it can be seen that MMM is only suitable for situations where the data consistency requirements are not high and a small amount of data is allowed to be lost, such as comments, information and other data

Tags: MySQL Database vim yum

Posted on Sat, 01 Feb 2020 07:59:11 -0800 by filmixt