Mysql-mmm High Availability

Mysql-mmm High Availability MMM introduction

• MMM (Master-master Replication Manager for Mysql,mysql primary master Replication Manager) is a set of scripts 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 master provides a partial read service to speed up the preheating of the alternate host during the main master 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 provides both automatic and manual ways to remove the virtual IP of a server with high replication latency in a set of servers, and it can also back up data to achieve data synchronization between two nodes. Because MMM does not fully guarantee the consistency of the data, MMM is suitable for scenarios where the consistency of data is not very high, but wants to maximize the availability of the business. For businesses that have a high level of consistency with broken data, it is highly recommended to use MMM as a highly available architecture.

MMM high-availability architecture diagram (from Baidu)

Experimental environment

Host	Operating System	IP Address	main software
MYSQL-M1 Primary Server	CentOS 7	192.168.27.128	Mysql-mmm
MYSQL-M2 Primary Server	CentOS 7	192.168.27.139	Mysql-mmm
MYSQL-M3 from the server	CentOS 7	192.168.27.147	Mysql-mmm
MYSQL-M4 from the server	CentOS 7	192.168.27.142	Mysql-mmm
Mysql-monitor	CentOS 7	192.168.27.143	Mysql-mmm

Build MySQL Multi-master multi-slave mode 1, configuration mysql-m1, mysql-m2, mysql-m3, MYSQL-M4;

Systemctl Stop Firewalld.service

Setenforce 0

Vim/etc/my.cnf

  [mysqld]user = Mysqlbasedir =/usr/local/mysqldatadir =/usr/local/mysql/dataport = 3306character_set_ Server=utf8pid-file =/usr/local/mysql/mysqld.pidsocket =/usr/local/mysql/mysql.socklog-error=/usr/local/mysql/ Data/mysql_error.log #错误日志general_log =on #开启通用日志general_log_file =/usr/loc                                       Al/mysql/data/mysql_general.loglog_bin=mysql-bin #二进制日志slow_query_log =on #慢日志slow_query_log_file =mysql_slow_query.loglong_query_time=1binlog-ignore-db=mysql,information                                           _schema #不生成二进制日志文件log_slave_updates =true #同步sync_binlog =1                                  #同步二进制日志auto_increment_increment =2 #字段一次递增多少auto_increment_offset =1 #自增字段起始值server-id = 1 #四台服务器的server-id not the same  

Systemctl Start Mysqld.service

NETSTAT-NATP | grep 3306

2, the configuration mysql-m1, mysql-m2 main main mode;

Mysql-u root-p

Show master status;

Grant replication Slave on . to ' replication ' @ ' 192.168.27.% ' identified by ' 123456 '; #m1和m2都要授权

Change Master to master_host= ' 192.168.27.139 ', master_user= ' replication ', master_password= ' 123456 ', master_log_file= ' mysql_bin.000001 ', master_log_pos=154; #m1同步m2

Change Master to master_host= ' 192.168.27.128 ', master_user= ' replication ', master_password= ' 123456 ', master_log_file= ' mysql_bin.000001 ', master_log_pos=154; #m2同步m1

Start slave; #开启同步

show slave status\g; #查看slave状态

Slave_IO_Running: YesSlave_SQL_Running: Yes

3, configuration mysql-m3, mysql-m4 as MYSQL-M1, mysql-m2 from the library

Mysql-u root-p

Change Master to master_host= ' 192.168.27.128 ', master_user= ' replication ', master_password= ' 123456 ', master_log_file= ' mysql_bin.000001 ', master_log_pos=154; #分别让m3和m4同步m1

Start slave;

show slave status\g; #查看slave状态

Slave_IO_Running: YesSlave_SQL_Running: Yes

Installation Configuration Mysql-mmm

-centos default does not install the MYSQL-MMM package, the recommended use of Epel source, five hosts are installed Epel source and MMM.

Installation Configuration MMM

Wget-o/etc/yum.repos.d/centos-base.repo Http://mirrors.aliyun.com/repo/Centos-7.repo

Yum-y Install Epel-release

Yum Clean all && yum Makecache #清空所有, re-set the original data cache

Yum-y Install mysql-mmm*

cd/etc/mysql-mmm/

Vim mmm_common.conf #所有主机上都要配置

2. Authorization for mmm_agent and Mmm_moniter on all database servers

Mysql-u root-p

Grant Super, replication client, process on . to ' mmm_agent ' @ ' 192.168.27.% ' identified by ' 123456 ';

Grant replication Client on . to ' mmm_monitor ' @ ' 192.168.27.% ' identified by ' 123456 ';

Flush privileges; #刷新权限

3. Modify the mmm_agent.conf file on all database hosts and start mysql-mmm-agent

Vi/etc/mysql-mmm/mmm_agent.conf

includemmm_common.confthis db1    #分别修改为db1、db2、db3、db4

Systemctl Start Mysql-mmm-agent.service

Systemctl Enable Mysql-mmm-agent.service

4. Configuring the Monitor Server

Vim/etc/mysql-mmm/mmm_mon.conf

----省略内容----ping_ips            192.168.27.128,192.168.27.139,192.168.27.147,192.168.27.142 #数据库服务器地址 auto_set_online     10----省略内容----

Systemctl start Mysql-mmm-monitor.service//Start Mysql-mmm-monitor

Mmm_control Show//view the situation of each node

db1(192.168.27.128) master/ONLINE. Roles: writer(192.168.27.111)db2(192.168.27.139) master/ONLINE. Roles: db3(192.168.27.147) slave/ONLINE. Roles: reader(192.168.27.101)db4(192.168.27.142) slave/ONLINE. Roles: reader(192.168.27.102)

Failover Switch Test

• 1, Stop DB1 database, wait a few seconds, you can see the virtual IP switch to db2,db1 offline state;

Mmm_control Show//view the situation of each node

db1(192.168.27.128) master/HARD.OFFLINE.Roles:db2(192.168.27.139) master/ONLINE. Roles: writer(192.168.27.111)db3(192.168.27.147) slave/ONLINE. Roles: reader(192.168.27.101)db4(192.168.27.142) slave/ONLINE. Roles: reader(192.168.27.102)

• 2, after the DB1 database is restored, the preemption virtual IP will not appear;

Mmm_control Show//view the situation of each node

db1(192.168.27.128) master/ONLINE. Roles:db2(192.168.27.139) master/ONLINE. Roles: writer(192.168.27.111) db3(192.168.27.147) slave/ONLINE. Roles: reader(192.168.27.101)db4(192.168.27.142) slave/ONLINE. Roles: reader(192.168.27.102)

• 3, Stop DB3 database, wait a few seconds, you can see the virtual IP switch to db4,db3 offline state;

Mmm_control Show//view the situation of each node

db1(192.168.27.128) master/ONLINE. Roles:db2(192.168.27.139) master/ONLINE. Roles: writer(192.168.27.111) db3(192.168.27.147) slave/HARD.OFFLINE. Roles: db4(192.168.27.142) slave/ONLINE. Roles: reader(192.168.27.102)reader(192.168.27.101)

• 4, after the DB3 database is restored, the original virtual IP will be returned;

Mmm_control Show//view the situation of each node

db1(192.168.27.128) master/ONLINE. Roles:db2(192.168.27.139) master/ONLINE. Roles: writer(192.168.27.111) db3(192.168.27.147) slave/ONLINE. Roles: reader(192.168.27.101)db4(192.168.27.142) slave/ONLINE. Roles: reader(192.168.27.102)

Mysql-mmm High Availability