Mysql-mmm for MySQL High availability

• MMM (Master-master Replication Manager for Mysql,mysql Master Replication Manager) 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 also 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 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 the 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.

• 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: The agent process running on each MySQL server, completing the monitored probe work and performing a simple remote service shezhi.cijiaoben need to be run on the 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.

Deployment environment

Host	Operating System	IP Address	VIP Address
MYSQL-M1 Primary Server	CentOS 7.4 x86_64	192.168.100.200	192.168.100.100 (Writer)
MYSQL-M2 Primary Server	CentOS 7.4 x86_64	192.168.100.201	192.168.100.100 (Writer)
Mysql-monitor	CentOS 7.4 x86_64	192.168.100.204
MYSQL-M3 from the server	CentOS 7.4 x86_64	192.168.100.202	192.168.100.101 (Reader)
MYSQL-M4 from the server	CentOS 7.4 x86_64	192.168.100.203	192.168.100.102 (Reader)

Start deployment

• Install required packages, modify configuration files (four MySQL server operations are consistent)

2. Build Ali Cloud Source and install epel-release source.

   wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo    #下载ALI云源yum -y install epel-release    #安装epel-release源yum clean all && yum makecache    #清空缓存，生成新的缓存文件

4. Install the MariaDB database, turn on the service (same as MySQL, build the same steps)

   yum -y install mariadb-server mariadbsystemctl stop firewalld.servicesetenforce 0systemctl start mariadb

6. Modify the database Master profile, send to another host, and start the server

   # 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    #不生成二进制文件的库character_set_server=utf8    #使用的字符集log_bin=mysql_bin    #二进制日志文件功能server_id=1log_slave_updates=true    #开启同步sync_binlog=1auto_increment_increment=2auto_increment_offset=1# scp /etc/my.cnf [email protected]:/etc# scp /etc/my.cnf [email protected]:/etc# scp /etc/my.cnf [email protected]:/etc# 注：另外三台主机的配置文件里的server_id要做修改，保证不一致即可

• Configure primary master replication (M1 and M2 two master servers synchronize with each other)

• View each other's log file name and offset values

  mysql> show master status;

• Primary server improves access to each other (M1, M2 server)

  # mysql-m1grant replication slave on *.* to ‘replication‘@‘172.168.100.%‘ identified by ‘123456‘;# 使用账户为replication 密码为123456change master to master_host=‘172.168.100.201‘,master_user=‘replication‘,master_password=‘123456‘,master_log_file=‘mysql_bin.000003‘,master_log_pos=245;# 当在MariaDB-m1上执行时，master_host地址为MariaDB-m2地址···# mysql-m2grant replication slave on *.* to ‘replication‘@‘172.168.100.%‘ identified by ‘123456‘;change master to master_host=‘172.168.100.200‘,master_user=‘replication‘,master_password=‘123456‘,master_log_file=‘mysql_bin.000003‘,master_log_pos=245;# 当在MariaDB-m2上执行时，master_host地址为MariaDB-m1地址

• 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

• Configure M3, M4 server as Slave server

  show master status;# 查看m1服务器的日志文件以及偏移值（注意日志文件和偏移值的改变）# m1和m2互相同步，在此只需执行m1即可change master to master_host=‘172.168.100.200‘,master_user=‘replication‘,master_password=‘123456‘,master_log_file=‘mysql_bin.000004‘,master_log_pos=245;# m3和m4上分别执行

• View the master and slave states of M3 and M4, respectively

  start slave;show slave status\G;# Slave_IO_Running: Yes# Slave_SQL_Running: Yes

• Installation Configuration MMM architecture
  We used wget to configure the Ali cloud source and easy to install MariaDB instead of MySQL, and set up master synchronization, master-slave replication to complete the experiment, now we have to complete the MMM architecture

• Install mysql-mmm, modify the configuration file (five host configuration same)

    # yum-y Install mysql-mmm*//front We configured the Epel source, direct yum Install # vim/etc/mysql-                MMM/MMM_COMMON.CONF//configured as follows Active_master_role Writer 

• Quickly modify configuration files for other hosts

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

• Modify the name of the mmm_agent.conf file on the host

  # vim /etc/mysql-mmm/mmm_agent.confthis db1  //按照规划分别修改为db1、db2、db3、db4

• Authorization of Mmm_agent, Mmm_moniter on all hosts

  mysql> grant super, replication client, process on *.* to ‘mmm_agent‘@‘192.168.100.%‘ identified by ‘123456‘;mysql> grant replication client on *.* to ‘mmm_monitor‘@‘192.168.100.%‘ identified by ‘123456‘;# flush privileges;  //重新加载权限表

• Configuring the Monitoring host (configured on Mysql-monitor)

  # vim /etc/mysql-mmm/mmm_mon.confinclude 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            172.168.100.200,172.168.100.201,172.168.100.202,172.168.100.203auto_set_online     10    #上线时间修改为10s# 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>

• Firewall off and enhanced XXX

  systemctl disable firewalld.servicesystemctl stop firewalld.servicesetenforce 0

• Start the service to view the status of each node

  systemctl start mysql-mmm-monitor.service# ERROR: Can‘t connect to monitor daemon!,如若出现报错，可尝试重启服务解决mmm_control check all# 检查监控服务器对所有主机的监控是否完善# 检查结果全部OK，则部署完成

At this point, MYSQL-MMM has been deployed successfully, further, we can combine amoeba to achieve read and write separation, writer's virtual VIP can be written to the database, and reader can be used to read data. And through the keepalived to monitor server for dual-machine hot standby.

Mysql-mmm for MySQL High availability