Mysql-mmm implementing MySQL high-availability cluster

Brief introduction

MMM (Master-master Replication Manager for MySQL) is a set of scripting programs that support dual-master failover and dual-master daily management. MMM is developed using the Perl language, which is mainly used to monitor and manage MySQL Master-master (dual master) replication, although it is called dual master replication, but only one master is allowed to write at the same time in the business, and the other one provides partial read services to speed up the preheating of the alternate master at the time of primary master switching. Can say mmm this set of scripts to implement the function of failover, on the other hand, its internal additional tool script can also achieve multiple slave read load balancing. Because MMM does not fully guarantee the consistency of data, MMM is suitable for the data consistency requirements are not very high, but also want to maximize the business availability of the scene. For businesses that have a high level of conformance to data, it is highly recommended that you use MMM as a highly available architecture.

How MMM works

MMM (master-master replication managerfor mysql,mysql primary master Replication Manager) is a flexible script that is based on Perl and is used to monitor and fail-over Mysql replication. And can manage the MySQL master-master replication configuration. ：

• Mmm_mond: Monitor processes, responsible for all monitoring tasks, determine and process all node role activities. This script needs to be run on a supervised machine.

• Mmm_agent: Run the agent process on each MySQL server, complete the monitoring of probe work and perform simple remote service settings. This script needs to be run on a supervised machine.

• Mmm_control: A simple script that provides commands to manage the mmm_mond process.

• Mysql-mmm's regulatory side will provide multiple virtual IP (VIP), including a writable VIP, multiple readable VIPs, through regulatory management, these IP will be tied to the available MySQL, when a certain MySQL downtime, the supervision will transfer VIP to other MySQL.

Throughout the regulatory process, it is necessary to add relevant authorized users to MySQL so that MySQL can support the maintenance of the supervision machine. Authorized users include a mmm_monitor user and a mmm_agent user, and add a mmm_tools user if you want to use the MMM Backup tool.

Lab Environment:

Prepare five server simulations, as shown in the table:

Host	Operating System	IP Address	main software
MYSQL-M1 Server	CentOS7.4 x86_64	192.168.113.160	MySQL5.7, Mysql-mmm
MYSQL-M2 Server	CentOS7.4 x86_64	192.168.113.161	MySQL5.7, Mysql-mmm
MYSQL-M3 Server	CentOS7.4 x86_64	192.168.113.162	MySQL5.7, Mysql-mmm
MYSQL-M4 Server	CentOS7.4 x86_64	192.168.113.165	MySQL5.7, Mysql-mmm
Mysql-monitor	CentOS7.4 x86_64	192.168.113.156	MARIADB, Mysql-mmm

The monitoring host is also used as a client

Virtual IP address (VIP):

IP	Role
192.168.113.100	Writer
192.168.113.210	Reader
192.168.113.220	Reader

Procedure of experimental operation all hosts turn off the firewall, and the security policy is turned off.

sysemctl stop firewalld.servicesetenforce 0

1. Build MySQL multi-master multi-slave mode (1) Install MySQL on all database servers

Installation steps refer to installing MySQL services on Linux platforms in detail.

(2) Configure MYSQL-M1, mysql-m2, MYSQL-M3, MYSQL-M4

[[email protected] ~]# vim /etc/my.cnf[mysqld]                              //添加以下内容binlog-ignore-db=mysql,information_schema  //不需要同步的数据库名称character_set_server=utf8 log_bin=mysql_bin                //开启binlog日志用于主从数据复制server_id=1                      //每台server-id的值不要相同log_slave_updates=true        //此数据库宕机，备用数据库接管sync_binlog=1                 //每条自动更新，安全性高，默认是0auto_increment_increment=2    //字段一次递增多少auto_increment_offset=1       //自增字段的起始值：1,3,5,7...等奇数ID

Each MySQL host Server-id can not be the same, other configuration file parameters are the same, you can copy the configuration file to the other 3 database server, modify the Server-id on the line.

scp /etc/my.cnf [email protected]:/etc/     scp /etc/my.cnf [email protected]:/etc/ scp /etc/my.cnf [email protected]:/etc/

(3) Start the MYSQL-M1, mysql-m2, MYSQL-M3, mysql-m4 four database servers

systemctl start mysqld.service

(4) Configuring MYSQL-M1, mysql-m2 main main mode 1) check the location of the log bin log and POS values first

MYSQL-M1:

[[email protected] ~]# mysql -uroot -pabc123mysql> show master status;+------------------+----------+--------------+--------------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |+------------------+----------+--------------+--------------------------+-------------------+| mysql_bin.000007 |      154 |              | mysql,information_schema |                   |+------------------+----------+--------------+--------------------------+-------------------+1 row in set (0.00 sec)

MYSQL-M2:

[[email protected] ~]# mysql -uroot -pabc123mysql> show master status;+------------------+----------+--------------+--------------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |+------------------+----------+--------------+--------------------------+-------------------+| mysql_bin.000013 |      154 |              | mysql,information_schema |                   |+------------------+----------+--------------+--------------------------+-------------------+1 row in set (0.01 sec)

2) MYSQL-M1, mysql-m2 each other to improve access rights, configure synchronization

MYSQL-M1:

mysql> grant replication slave on *.* to    ‘replication‘@‘192.168.113.%‘ identified by ‘123456‘;  //给m2授予从的权限Query OK, 0 rows affected (0.00 sec)  mysql> flush privileges;         //刷新数据库Query OK, 0 rows affected (0.00 sec)

mysql>  change master to     ->  master_host=‘192.168.113.161‘,     ->  master_user=‘replication‘,    ->  master_password=‘123456‘,    ->  master_log_file=‘mysql_bin.000013‘,    ->  master_log_pos=154;             //配置同步Query OK, 0 rows affected, 2 warnings (0.01 sec)

MYSQL-M2:

mysql> grant replication slave on *.* to ‘replication‘@‘192.168.113.%‘ identified by ‘123456‘;  //给m1授予从的权限Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;         //刷新数据库Query OK, 0 rows affected (0.00 sec)

mysql>  change master to     ->  master_host=‘192.168.113.161‘,     ->  master_user=‘replication‘,    ->  master_password=‘123456‘,    ->  master_log_file=‘mysql_bin.000007‘,    ->  master_log_pos=154;             //配置同步Query OK, 0 rows affected, 2 warnings (0.01 sec)

3) View the master-slave status of the MYSQL-M1 and MYSQL-M2 servers respectively.

mysql> start slave;   //启动同步mysql> show slave status\G;

Results:

Status of MYSQL-M1:

Status of MYSQL-M2:

Master synchronization configuration is complete, check the synchronization status Slave_io and Slave_sql are yes, indicating that the master synchronization was successful.

4) Test Master Sync and create a new library KGC in mysql-m2.

mysql> create database kgc;Query OK, 1 row affected (0.01 sec)

Go back to the MYSQL-M1 database server and review the library KGC you just created, and the synchronization is complete.

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || kgc                || mysql              || performance_schema || sys                |+--------------------+5 rows in set (0.09 sec)

(5) Configure MYSQL-M3 and MYSQL-M4 as mysql-m1 from the library. 1) Check the status value of Mysql-m1 master first.

mysql> show master status;+------------------+----------+--------------+--------------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |+------------------+----------+--------------+--------------------------+-------------------+| mysql_bin.000007 |      460 |              | mysql,information_schema |                   |+------------------+----------+--------------+--------------------------+-------------------+1 row in set (0.00 sec)

2) The following statements are executed in MYSQL-M3 and MYSQL-M4 respectively to achieve master-slave synchronization.

mysql>  change master to     ->  master_host=‘192.168.113.161‘,     ->  master_user=‘replication‘,    ->  master_password=‘123456‘,    ->  master_log_file=‘mysql_bin.000007‘,    ->  master_log_pos=460; Query OK, 0 rows affected, 2 warnings (0.01 sec)

3) View the master-slave status of the MYSQL-M3 and MYSQL-M4 servers, respectively.

mysql> start slave;   //启动同步mysql> show slave status\G;

In MYSQL-M3 and mysql-m4 you can see that the master-slave configuration replication succeeds.

2. Installation configuration mysql-mmm (1) Install MMM

The CentOS default does not have the MYSQL-MMM software package, the official recommendation uses the Epel source, the five hosts must install Epel source and the MMM.

wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repoyum -y install epel-releaseyum clean all && yum makecache

yum -y install mysql-mmm*

(2) in Mysql-m1, mysql-m2, MYSQL-M3, MYSQL-M4 node authorized monitor Access.

-------------Authorized for Mmm-agent--------------------

mysql> grant super, replication client, process on *.* to ‘mmm_agent‘@‘192.168.113.%‘ identified by ‘123456‘;

-------------Authorized for Mmm-monitor--------------------

mysql> grant replication client on *.* to ‘mmm_monitor‘@‘192.168.113.%‘ identified by ‘123456‘;

(3) Modify the/etc/mysql-mmm/mmm_common.conf configuration file.

The configuration file contents of all hosts in the system are the same, including monitoring host mysql-monitor.

vim /etc/mysql-mmm/mmm_common.conf       ……

Through the SCP command to the other four units:

scp /etc/mysql-mmm/mmm_common.conf [email protected]:/etc/mysql-mmm/scp /etc/mysql-mmm/mmm_common.conf [email protected]:/etc/mysql-mmm/scp /etc/mysql-mmm/mmm_common.conf [email protected]:/etc/mysql-mmm/scp /etc/mysql-mmm/mmm_common.conf [email protected]:/etc/mysql-mmm/

(4) Edit the/etc/mysql-mmm/mmm_agent.conf file on the database host (MYSQL-M1, mysql-m2, MYSQL-M3, MYSQL-M4) and modify it to different values depending on the host.

vim /etc/mysql-mmm/mmm_agent.conf      include mmm_common.conf      this db1     //分别修改为db1、db2、db3和db4

(5) Edit the/etc/mysql-mmm/mmm_mon.conf file on the monitoring host

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.113.160,192.168.113.161,192.168.113.162,192.168.113.165  //监控服务器的ip    auto_set_online     10    # 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>

(6) Start agent and monitoring 1) Start the Agent service on all database hosts

systemctl start mysql-mmm-agent.servicesystemctl enable mysql-mmm-agent.service

2) Start the Monitoring service on the monitoring host

systemctl start mysql-mmm-monitor.service

(7) Test cluster

[[email protected] ~]# cd /etc/mysql-mmm/[[email protected] ~]# mmm_control show  db1(192.168.113.160) master/ONLINE. Roles: writer(192.168.113.100)  db2(192.168.113.161) master/ONLINE. Roles:   db3(192.168.113.162) slave/ONLINE. Roles: reader(192.168.113.220)  db4(192.168.113.165) slave/ONLINE. Roles: reader(192.168.113.210)

The virtual IP behind is the real access to the MySQL database.

3. Failover switchingThe monitoring host also acts as a client and can be installed by Yum Mariadb.

yum -y install mariadb-server mariadbsystemctl start mariadb

authorize login for the monitor address on the M1 server.

mysql> grant all on *.* to ‘testdba‘@‘192.168.113.156‘ identified by ‘123456‘;Query OK, 0 rows affected, 1 warning (0.05 sec)mysql> flush privileges;Query OK, 0 rows affected (0.03 sec)

1) Stop the main DB1 database, you can see the database db1 in Hard_offline (offline state), the existence of the database is not detected.

[[email protected] ~]# cd /etc/mysql-mmm/[[email protected] ~]# mmm_control show  db1(192.168.113.160) master/HARD_OFFLINE. Roles:   db2(192.168.113.161) master/ONLINE. Roles: writer(192.168.113.100)  //虚拟IP转移  db3(192.168.113.162) slave/ONLINE. Roles: reader(192.168.113.220)  db4(192.168.113.165) slave/ONLINE. Roles: reader(192.168.113.210)

When the DB1 database is down, the virtual IP is transferred to another normal database DB2

2) simulating master failure to create a database test

The database is connected with the virtual IP on the monitor machine to create the database test.

[[email protected] ~]# mysql -utestdba -p -h 192.168.113.100Enter password:   //输入授权的密码Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MySQL connection id is 14304Server version: 5.7.17-log Source distributionMySQL [(none)]>   //成功连接上数据库

MySQL [(none)]> create database test;Query OK, 1 row affected (0.01 sec)

Next, go back to the MYSQL-M2 database server to see if the test database you created is on the MYSQL-M2 database.

After starting the primary DB1 database, you can see that the MYSQL-M1 database is back online, but the virtual IP is not transferred (you can execute the command mmm_control move_role writer db1 manual transfer).

[[email protected] ~]# cd /etc/mysql-mmm/[[email protected] ~]# mmm_control show  db1(192.168.113.160) master/ONLINE. Roles: //恢复在线状态  db2(192.168.113.161) master/ONLINE. Roles: writer(192.168.113.100)  //虚拟IP转移  db3(192.168.113.162) slave/ONLINE. Roles: reader(192.168.113.220)  db4(192.168.113.165) slave/ONLINE. Roles: reader(192.168.113.210)

At this point, enter the MYSQL-M1 database to see if the primary master synchronization is normal and see if the data is healthy.

As a result, mysql-mmm failover is normal.

This is also true for analog master-slave failures, where the MYSQL-M4 database is dropped and the virtual IP is transferred to another normal database.

[[email protected] ~]# cd /etc/mysql-mmm/[[email protected] ~]# mmm_control show  db1(192.168.113.160) master/ONLINE. Roles:   db2(192.168.113.161) master/ONLINE. Roles: writer(192.168.113.100)    db3(192.168.113.162) slave/ONLINE. Roles: reader(192.168.113.210), reader(192.168.113.220) //虚拟IP转移  

Mysql-mmm implementing MySQL high-availability cluster