Deploy MYSQL-MMM highly available cluster on CentOS7

About MMM

MMM (MySQL master replication Manager) is a set of scripting programs that support dual-master failover and dual-master daily management. 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 master provides a partial read service to speed up the preheating of the alternate host during the main switchover. 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 is a flexible scripting program based on Perl, used to monitor and failover MySQL replication, and to manage the configuration of MySQL Master-master replication:

A description of the MMM high-availability architecture is as follows:

• Mmm_mon: 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 virtual IP (VIP), including a writable VIP, a number of readable VIPs, through regulatory management, these IP will be tied to the available MySQL, when a certain downtime, the supervision will transfer VIP to other MySQL.

Experimental environment: (MARIADB database is a branch of MySQL, their commands, operations are the same)

This experiment environment uses five server simulation to build, the experiment environment as shown in the table.

1. Build MARIADB Multi-master multi-slave mode

(1) Installation mariadb

1) All servers are configured with Ali Cloud Source and then install the epel-release source.

[[email protected] ~]# wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo[[email protected] ~]# yum -y install epel-release[[email protected] ~]# yum clean all && yum makecache

2) Build local Yum source.

[[email protected] ~]# yum -y install mariadb-server mariadb[[email protected] ~]# systemctl stop firewalld.service    //关闭防火墙//[[email protected] ~]# setenforce 0

3) Modify the MARIADB-M1 master configuration file.

[[email protected] ~]# vim /etc/my.cnf[mysqld]         //添加//log_error=/var/lib/mysql/mysql.errlog=/var/lib/mysql/mysql_log.loglog_slow_queries=/var/lib/mysql_slow_queris.logbinlog-ignore-db=mysql,information_schema   //不需要同步的数据库名称//character_set_server=utf8log_bin=mysql_bin    //开启binlog日志用于主从数据复制//server_id=1         //每台server-id的值不要相同//log_slave_updates=true  //此数据库宕机，备用数据库接管//sync_binlog=1auto_increment_increment=2   //字段一次递增多少//auto_increment_offset=1      //自增字段的起始值//[[email protected] ~]# systemctl start mariadb.service    //开启mariadb//[[email protected] ~]# netstat -anpt | grep 3306tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      3434/mysqld

4) After the problem, copy the configuration file to the other 3 database servers.

[[email protected] ~]# scp /etc/my.cnf [email protected]:/etc/    //m2//[[email protected] ~]# scp /etc/my.cnf [email protected]:/etc/   //m3//[[email protected] ~]# scp /etc/my.cnf [email protected]:/etc/   //m4//

Note: The Server-id of each MARIADB host cannot be the same, and the other configuration file parameters are the same.

(2) Configuring MARIADB-M1, mariadb-m2 main main mode

1) Check the location of the log bin and POS values first.

  m1:[[email protected] ~]# mysqlmariadb [(none)]> Show Master status;+------------------+--------- -+--------------+--------------------------+| File | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+--------------------------+|      mysql_bin.000001 |              245 | | Mysql,information_schema |+------------------+----------+--------------+--------------------------+1 row in Set ( 0.00 sec) m2:[[email protected] ~]# mysqlmariadb [(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) MARIADB-M1, mariadb-m2 each other to improve access rights.

m1:MariaDB [(none)]> grant replication slave on *.* to ‘replication‘@‘192.168.126.%‘ identified by ‘123456‘; Query OK, 0 rows affected (0.03 sec)MariaDB [(none)]> change master to master_host=‘192.168.126.139‘,master_user=‘replication‘,master_password=‘123456‘,master_log_file=‘mysql_bin.000003‘,master_log_pos=411;Query OK, 0 rows affected (0.18 sec)m2:MariaDB [(none)]> grant replication slave on *.* to ‘replication‘@‘192.168.126.%‘ identified by ‘123456‘; Query OK, 0 rows affected (0.01 sec)MariaDB [(none)]> change master to master_host=‘192.168.126.138‘,master_user=‘replication‘,master_password=‘123456‘,master_log_file=‘mysql_bin.000001‘,master_log_pos=411;Query OK, 0 rows affected (0.03 sec)

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

MariaDB [(none)]> start slave;MariaDB [(none)]> show slave status\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.126.139                  Master_User: replication                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql_bin.000003          Read_Master_Log_Pos: 411               Relay_Log_File: mariadb-relay-bin.000002                Relay_Log_Pos: 529        Relay_Master_Log_File: mysql_bin.000003             Slave_IO_Running: Yes            Slave_SQL_Running: Yes

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 Master synchronization and create a new library DBA in MARIADB-M2.

m2:MariaDB [(none)]> create database dba;   //创建数据库//Query OK, 1 row affected (0.09 sec)m1:MariaDB [(none)]> show databases;+--------------------+| Database           |+--------------------+| information_schema || dba                |              //同步成功//| mysql              || performance_schema || test               |+--------------------+5 rows in set (0.02 sec)

(3) Configuring MARIADB-M3 and MARIADB-M4 as mariadb-m1 from the library

1) Check the status value of Mariadb-m1 master first.

MariaDB [(none)]> show master status;+------------------+----------+--------------+--------------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         |+------------------+----------+--------------+--------------------------+| mysql_bin.000001 |      581 |              | mysql,information_schema |+------------------+----------+--------------+--------------------------+1 row in set (0.00 sec)

2) mariadb-m3 and MARIADB-M4 are executed separately.

MariaDB [(none)]> change master to master_host=‘192.168.126.138‘,master_user=‘replication‘,master_password=‘123456‘,master_log_file=‘mysql_bin.000001‘,master_log_pos=581;Query OK, 0 rows affected (0.01 sec)

3) View the master-slave status of the MARIADB-M3 and MARIADB-M4 servers, respectively, with the following results:

MariaDB [(none)]> start slave;MariaDB [(none)]> show slave status\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.126.138                  Master_User: replication                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql_bin.000001          Read_Master_Log_Pos: 581               Relay_Log_File: mariadb-relay-bin.000002                Relay_Log_Pos: 529        Relay_Master_Log_File: mysql_bin.000001             Slave_IO_Running: Yes            Slave_SQL_Running: Yes

4) Set up a database in mariadb-m1, test master and slave, main master, synchronization situation.

MariaDB [(none)]> create database dba01;  //在m1创建数据库//Query OK, 1 row affected (0.01 sec)MariaDB [(none)]> show databases;   //mariadb-m3查看结果//+--------------------+| Database           |+--------------------+| information_schema || dba01              || mysql              || performance_schema || test               |+--------------------+5 rows in set (0.03 sec)MariaDB [(none)]> show databases;  //mariadb-m4查看结果//+--------------------+| Database           |+--------------------+| information_schema || dba01              || mysql              || performance_schema || test               |+--------------------+5 rows in set (0.04 sec)

2. Installation Configuration Mysql-mmm

(1) Install MMM on all servers, note that the Epel source should be configured well.

[[email protected] ~]# yum -y install mysql-mmm*

(2) Modify the/etc/mysql-mmm/mmm_common.conf configuration file, the same configuration file contents of all hosts in the system, including monitoring host mysql-monitor.

[[email protected] ~]# cd/etc/mysql-mmm/[[email protected] mysql-mmm]# vim Mmm_common.confactive_master_ Role Writer

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

  [[email protected] ~]# vim/etc/mysql-mmm/mmm_mon.conf include mmm_common.conf<monitor> IP 127.0.0.1 pid_path/run/mysql-mmm-monitor.pidinclude mmm_common.conf<monitor> IP 127.0.0.1 pid_path/run/mysql-mmm-monitor.pid bin_path/usr/libexec/mysql-mmm STA Tus_path/var/lib/mysql-mmm/mmm_mond.status ping_ips 192.168.126.138,192.168.126.139,192.168.126.136 , 192.168.126.137/Monitoring Server Ip/auto_set_online 10//Auto-launch time 10 sec./# The Kill_host_bin does not exist by default, th Ough the monitor would # throw a warning about it missing. See the sections 5.10 "Kill Host # Functionality" in the PDF documentation. # # Kill_host_bin/usr/libexec/mysql-mmm/monitor/kill_host #</monitor> 

(4) Authorization for mmm_agent and mmm_moniter on all databases.

MariaDB [(none)]> grant super, replication client, process on *.* to ‘mmm_agent‘@‘192.168.126.%‘ identified by ‘123456‘;MariaDB [(none)]> grant replication client on *.* to ‘mmm_monitor‘@‘192.168.126.%‘ identified by ‘123456‘;

(5) Modify the mmm_agent.conf of all databases.

[[email protected] mysql-mmm]# 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 //根据规划进行逐一调整//

(6) Start mysql-mmm-agent on all database servers.

[[email protected] mysql-mmm]# systemctl start mysql-mmm-agent.service[[email protected] mysql-mmm]# systemctl enable mysql-mmm-agent.service //开机自启动//

(7) Start the Mysql-mmm-monitor monitoring host.

[[email protected] ~]# systemctl start mysql-mmm-monitor.service

(8) View the situation of each node on the monitoring server.

[[email protected] ~]# mmm_control show db1(192.168.126.138) master/ONLINE. Roles: writer(192.168.126.188) //虚拟IP// db2(192.168.126.139) master/ONLINE. Roles: db3(192.168.126.136) slave/ONLINE. Roles: reader(192.168.126.190) db4(192.168.126.137) slave/ONLINE. Roles: reader(192.168.126.199

(9) Failover switching

1) Stop M1 confirm that virtual address 188 is moved to M2. Note: The Lord does not preempt

[[email protected] mysql-mmm]# systemctl stop mariadb.service //停止 m1主服务器//[[email protected] ~]# mmm_control show db1(192.168.126.138) master/HARD_OFFLINE. Roles: //离线状态// db2(192.168.126.139) master/ONLINE. Roles: writer(192.168.126.188) db3(192.168.126.136) slave/ONLINE. Roles: reader(192.168.126.190) db4(192.168.126.137) slave/ONLINE. Roles: reader(192.168.126.199)

[[email protected] mysql-mmm]# systemctl stop mariadb.service//stop M3 from server//

[[email protected] ~]# mmm_control show db1(192.168.126.138) master/HARD_OFFLINE. Roles: db2(192.168.126.139) master/ONLINE. Roles: writer(192.168.126.188) db3(192.168.126.136) slave/HARD_OFFLINE. Roles: db4(192.168.126.137) slave/ONLINE. Roles: reader(192.168.126.190), reader(192.168.126.199)

3) Authorize login on the M1 server for the monitoring machine address.

MariaDB [(none)]> grant all on *.* to ‘testdba‘@‘192.168.126.140‘ identified by ‘123456‘;MariaDB [(none)]> flush privileges; //刷新//

4) Log on to the monitoring server.

[[email protected] ~]# yum install mariadb-server mariadb -y[[email protected] ~]# mysql -utestdba -p -h 192.168.126.188 //虚拟IP//Enter password: //密码123456//.....//省略//MariaDB [(none)]>

5) Create the data on the monitoring server and test the synchronization situation.

MariaDB [(none)]> create database abc01;Query OK, 1 row affected (0.01 sec)MariaDB [(none)]> show databases; //主服务器M1//+--------------------+| Database |+--------------------+| information_schema || abc01 || dba01 || mysql || performance_schema || test |+--------------------+6 rows in set (0.03 sec)MariaDB [(none)]> show databases; //从服务器M3//+--------------------+| Database |+--------------------+| information_schema || abc01 || dba01 || mysql || performance_schema || test |+--------------------+6 rows in set (0.05 sec)

Deploy MYSQL-MMM highly available cluster on CentOS7