High availability cluster for MMM in MySQL

Introduced

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 and is primarily used to monitor and manage MySQL Master-master (dual master) replication, which can be said to be the Master Master Replication Manager for MySQL. Although it is called double-master replication, but only one master is allowed to write at the same time in the business, and the other one provides partial read service to accelerate the preheating of the alternate master at the main switch time, it can be said that the MMM script program realizes the function of failover. On the other hand, its internal additional tool scripts can also achieve load balancing of multiple slave.

Advantages

High availability, scalability, failure auto-switching, for master synchronization, only one database write operation at the same time, ensure the consistency of the data.

• Disadvantages

The monitor node is a single point and can be used in conjunction with keepalived for high availability.

Experimental requirements

Two master servers Master

master1:192.168.177.128

master2:192.168.177.135

Two slave servers slave

slave1:192.168.177.132

slave2:192.168.177.133

Monitoring Server Monitor

monitor:192.168.177.134

Configure the Ali cloud Source, then install the epel-release source (four master-slave)

# 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

Build local Yum Source (four master-slave)

# yum -y install mariadb-server mariadb# systemctl stop firewalld.service # setenforce 0# systemctl start mariadb.service

Modify the M1 master configuration file

# vim /etc/my.cnf //删掉原来的[mysqld]9行,添加如下内容：[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_schemacharacter_set_server=utf8log_bin=mysql_binserver_id=1log_slave_updates=truesync_binlog=1auto_increment_increment=2auto_increment_offset=1==注意==server_id要不同# systemctl restart mariadb.service

Configure primary master replication-two primary servers replicate with each other

# mysql># show master status; //记录日志文件名称和位置值，在两台主上查看在m1上为m2授予从的权限，在m2上也要为m1授予从的权限# grant replication slave on *.* to ‘replication‘@‘192.168.177.%‘ identified by ‘123456‘; //m1上# grant replication slave on *.* to ‘replication‘@‘192.168.177.%‘ identified by ‘123456‘;//m2上#change master to master_host=‘192.168.177.135‘,master_user=‘replication‘,master_password=‘123456‘,master_log_file=‘mysql_bin.000001‘,master_log_pos=245;//m1上# change master to master_host=‘192.168.177.128‘,master_user=‘replication‘,master_password=‘123456‘,master_log_file=‘mysql_bin.000001‘,master_log_pos=245;//m2上# start slave;# show slave status\G;

Do it on both-note the change of log files and positional parameters (all pointing to M1)

# change master to master_host=‘192.168.177.128‘,master_user=‘replication‘,master_password=‘123456‘,master_log_file=‘mysql_bin.000001‘,master_log_pos=245;# start slave; //开启同步# show slave status\G;

Install MMM (four master and slave)

# yum-y Install mysql-mmm* after installation, the MMM configuration # cd/etc/mysql-mmm/#vim mmm_common.conf//on all hosts to be configured, directly copy multiple 





Configuring on the monitor server

# systemctl stop firewalld.service# setenforce 0# 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# yum -y install mysql-mmm*# cd /etc/mysql-mmm# vim mmm_mon.conf

Authorized for Mmm_agent on all databases-four master and slave

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

Authorized for Mmm_moniter on all databases-four master and slave

# grant replication client on *.* to ‘mmm_monitor‘@‘192.168.177.%‘ identified by ‘123456‘;# flush privileges;

Modify mmm_agent.conf-of all databases four master-slave

# vim /etc/mysql-mmm/mmm_agent.confthis db1 //根据规划进行逐一调整this db2this db3this db4

Start mysql-mmm-agent-four master-slave on all database servers

# systemctl start mysql-mmm-agent.service# systemctl enable mysql-mmm-agent.service   #开机自启动

Configuring on the monitor server

# systemctl start mysql-mmm-monitor.service # mmm_control show# mmm_control checks all# mmm_control move_role writer db2    //指定db2绑定虚拟IP

Fault Testing

停止m1 确认 虚拟地址 200 是否移动到 m2 上。注意：主不会抢占# systemctl stop mariadb.service //m1上# mmm_control show   db1(192.168.177.128) master/HARD_OFFLINE. Roles:  db2(192.168.177.135) master/ONLINE. Roles: writer(192.168.177.200)

Mmm in MySQL implements a highly available cluster