Mysql-mmm High-availability clusters (this article uses mariadb instead of MySQL for experimental deployment)

1.MYSQL-MMM Overview

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.
MMM provides both automatic and manual removal of the virtual IP of a server with high replication latency in a set of servers, as well as the ability to back up data, achieve data synchronization between two nodes, and more. 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.

2, mysql-mmm advantages and disadvantages

Advantages : High availability, scalability, automatic failover, for master synchronization, at the same time only provide a database write operation, to ensure the consistency of the data.
cons : The monitor node is a single point that can be combined with keepalived for high availability.

3. Mysql-mmm Working principle

MMM is a flexible script, based on Perl, used to monitor and failover MySQL replication, and to manage the configuration of MySQL Master-master replication (only one node is writable at a time).

• 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_AGENTD: 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.

System environment

Operating system: CentOS 7.0 x86_64
Database allocation :

Function	IP	Hostname	Sever ID
Mysql-monitor	192.168.100.163	Monitor	No
Master1	192.168.100.155	Db1	1
Master2	192.168.100.166	Db2	2
Slave1	192.168.100.159	Db3	3
Slave2	192.168.100.158	Db4	4

Virtual IP address (VIP):

IP	role
192.168.100.170	Writer
192.168.100.171	Reader
192.168.100.172	Reader

Experimental deployment of one, in Master1, Master2, slave1, Slave2 installed MARIADB, the experiment with mariadb instead of MySQL, the experimental results are the same

1. Configure the Ali cloud source and install the epel-release source

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   //清空所有，重新更新元数据缓存

2. Installing MARIADB

yum -y install mariadb-server mariadb

3. Turn off the firewall and enhance security settings

systemctl stop firewalld.servicesetenforce 0

Perform the same operation on the other three service side

4. Modify the M1 master configuration file

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        //mysql,information_schema这两个数据库不生成二进制日志文件character_set_server=utf8       //字体设置为utf8log_bin=mysql_bin                                //二进制日志文件功能开启server_id=1                                  //本台server_id为1，其他三台server_id与本台不同log_slave_updates=true                           //开启同步sync_binlog=1                                    //1为安全值auto_increment_increment=2auto_increment_offset=1

5. Turn on the service and view the status of the service

systemctl start mariadbnetstat -anpt | grep 3306

6. After you have no problem, copy the configuration file to the other 3 database servers and start the server, note: server_id in the configuration file to be modified

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

Ii. Configuring Primary master replication (two primary servers replicate with each other)

1. View log file name and location values on two primary servers, respectively

mysql               //登录数据库>show master status;

2. On the M1, grant the permission from the M2 on the M2, and also the permissions granted to the M1 on the
MARIADB-M1:

grant replication slave on *.* to ‘replication‘@‘192.168.100.%‘ identified by ‘123456‘;//使用账户为replication 密码为123456change master to master_host=‘192.168.100.166‘,master_user=‘replication‘,master_password=‘123456‘,master_log_file=‘mysql_bin.000001‘,master_log_pos=245;//当在MariaDB-m1上执行时，master_host地址为MariaDB-m2地址

MARIADB-M2:

grant replication slave on *.* to ‘replication‘@‘192.168.100.%‘ identified by ‘123456‘;       change master to master_host=‘192.168.100.155‘,master_user=‘replication‘,master_password=‘123456‘,master_log_file=‘mysql_bin.000001‘,master_log_pos=245;//当在MariaDB-m1上执行时，master_host地址为MariaDB-m2地址

3. Turn on sync to see the server's master-slave status

>start slave;>show slave status\G;       //Slave_IO_Running: Yes、Slave_SQL_Running这两个条目状态为yes

4. Build the library Test Master Sync

>create database dba;       //在m1上创建dba数据库，应当在m2上能看到，否则主主同步失败>drop database dba;     //在m1上删除后，m2上的dba也应当没有

Third, master-slave replication (slave1, slave2 two server operation the same)

1. Login MARIADB

>mysql

2. Execute separately on two slave servers

change master to master_host=‘192.168.100.155‘,master_user=‘replication‘,master_password=‘123456‘,master_log_file=‘mysql_bin.000001‘,master_log_pos=245;

3. Turn on sync and view from server status

>start slave;>show slave status\G;       //需看到slave1和slave2的Slave_IO_Running: Yes、Slave_SQL_Running参数值为yes

4. Test Master-Slave synchronization

To create a DBA on M1, you need to see the same database DBA on Slave1 and Slave2, and the master-slave synchronization succeeds

Iv. installation of Mysql-mmm server

1. Installing the MYSQL-MMM Package

wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo    yum -y install epel-releaseyum -y install mysql-mmm*

2. Configure Mysql-mmm

vim /etc/mysql-mmm/mmm_common.confactive_master_role      writer

3. mmm_common.conf file content of other server is same, transfer directly to other server

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 mmm_mon.conf configuration file on the monitor server
Vim/etc/mysql-mmm/mmm_mon.conf

include mmm_common.conf<monitor>ip 127.0.0.1pid_path /run/mysql-mmm-monitor.pidbin_path /usr/libexec/mysql-mmmstatus_path /var/lib/mysql-mmm/mmm_mond.statusping_ips 192.168.100.155,192.168.100.166,192.168.100.159,192.168.100.158auto_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>

5. Start Mysql-mmm-monitor

systemctl start mysql-mmm-monitor.service //启动mysql-mmm-monitor

Five. Configure MYSQL-MMM agent for four MySQL servers

1. Authorized for mmm_agent on four MySQL servers

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

2. Authorization for mmm_monitor on all databases

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

3. Refresh Permissions

>flush privileges;

4, modify the mmm_agent.conf of each database host name is DB1, DB2, DB3, DB4

vi /etc/mysql-mmm/mmm_agent.conf···this db1            //其他三个服务器分别为db2、db3、db4

5. Start the Mysql-mmm-agent service on all servers

systemctl enable mysql-mmm-agentsystemctl start mysql-mmm-agent

Vi. return to the Mmm-monitor for inspection

1, view the situation of each node

mmm_control showdb1(192.168.100.155) master/ONLINE. Roles: writer(192.168.100.170)db2(192.168.100.166) master/ONLINE. Roles:db3(192.168.100.159) slave/ONLINE. Roles: reader(192.168.100.171)db4(192.168.100.158) slave/ONLINE. Roles: reader(192.168.100.171)

2, check all options, need to be OK

mmm_control checks all

3. Use the following command to manually switch the master server

mmm_control move_role writer db2

Seven, fault testing

1, stop the M1 Server MARIADB service, check the server status, see if M2 preemption virtual address

systemctl stop mariadbmmm_control showdb1(192.168.100.155) master/HARD_OFFLINE. Roles:db2(192.168.100.166) master/ONLINE. Roles: writer(192.168.100.170)db3(192.168.100.159) slave/ONLINE. Roles: reader(192.168.100.171)db4(192.168.100.158) slave/ONLINE. Roles: reader(192.168.100.172)

here DB1 's virtual IP address has drifted to DB2.

2, suspend the DB3 server MARIADB service, check the status

systemctl stop mariadbmmm_control showdb1(192.168.100.155) master/HARD_OFFLINE. Roles:db2(192.168.100.166) master/ONLINE. Roles: writer(192.168.100.170)db3(192.168.100.159) slave/HARD_OFFLINE. Roles: db4(192.168.100.158) slave/ONLINE. Roles: reader(192.168.100.171),reader(192.168.100.172)

3, restore DB1, DB3 server mariadb service, check the status

mmm_control showdb1(192.168.100.155) master/ONLINE. Roles:db2(192.168.100.166) master/ONLINE. Roles: writer(192.168.100.170)db3(192.168.100.159) slave/ONLINE. Roles: reader(192.168.100.171)db4(192.168.100.158) slave/ONLINE. Roles: reader(192.168.100.172)

Note: When DB1 is restored, it does not preempt vip!

Mysql-mmm High-availability clusters (this article uses mariadb instead of MySQL for experimental deployment)