MySQL high-availability cluster--mmm high-availability architecture

Why to use MySQL high availability cluster

• In the actual production environment, when the normal MySQL master-slave replication and read/write separation can not meet the actual requirements, it is necessary to consider the MySQL high-availability cluster, for security reasons, when the data access is too large to bring the primary server load, when the primary server down one, to ensure that the data service is not interrupted, From the server will automatically look for another master server, and from the server will be like the primary server, when one of the downtime, can still guarantee that the service will not be interrupted.

MMM high-availability architecture

What is MMM?

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

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.

• Disadvantages: Monitor node is a single point, can be combined with keepalived to achieve high availability, the number of host requirements, the need to achieve read and write separation, the program is a challenge.

How MMM works

• MMM (master-master replication managerfor mysql,mysql primary master Replication Manager) is a flexible script that is based on Perl and is used to monitor and fail-over Mysql replication. And can manage the configuration of MySQL Master-master replication (only one node is writable at the same time).

MMM high-availability Architecture description

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

Experimental deployment what is MARIADB?

• MARIADB database management System is a branch of MySQL, mainly by the open source community in the maintenance, the use of GPL licensing MARIADB is fully compatible with MySQL, including the API and command line, so that it can easily become a substitute for MySQL. For the storage engine, use XtraDB (English: XtraDB) instead of the MySQL InnoDB.
• MARIADB, the transaction-based Maria Storage engine, replaces the MySQL MyISAM storage engine, which uses a variant of the Percona Xtradb,innodb, which the branch developers want to provide access to the upcoming MySQL 5.4 InnoDB performance. This version also includes the PrimeBase XT (PBXT) and Federatedx storage engines.
• Here we are convenient for testing, no longer a single server to install MySQL, this will consume a lot of time, choose to use mariadb temporarily instead of MySQL, but the trial operation is the same as the MySQL server.

Experimental environment

Server	Operating System	IP Address	Required Software
Master1	centos7.3 x86_64	192.168.144.128	MARIADB, Mysql-mmm
Master2	centos7.3 x86_64	192.168.144.145	MARIADB, Mysql-mmm
Slave1	centos7.3 x86_64	192.168.144.141	MARIADB, Mysql-mmm
Slave2	centos7.3 x86_64	192.168.144.129	MARIADB, Mysql-mmm
Monitor	centos7.3 x86_64	192.168.144.136	Mysql-mmm

Building a Yum Source

• Since CentOS does not have a mysql-mmm package by default, we need to choose an online installation by configuring the Yum source and by using the Epel source.
• Start by building a cloud source on each server that needs to be clustered, and then install the epel-release source.

• It is important to note that we choose mariadb instead of MySQL.

• Configure the Ali cloud source to install the Epel source:

Wget-o/etc/yum.repos.d/centos-base.repo Http://mirrors.aliyun.com/repo/Centos-7.repo
Yum-y Install Epel-release

• Clear the original Yum cache and re-cache Yum Meta

Yum Clean all && yum Makecache

Installing MARIADB

• Install mariadb instead of MySQL on each server

Yum-y Install Mariadb-server mariadb
Systemctl Disable Firewalld.service
Systemctl Stop Firewalld.service
Setenforce 0

• Configure each MySQL server master configuration file

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_schemacharacter_set_server=utf8log_bin=mysql_binserver_id=1        //这里需要区分每台服务器ID需不同log_slave_updates=truesync_binlog=1auto_increment_increment=2auto_increment_offset=1

Set Primary master sync master1 sync Master2

Mysql>show Master status;

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

• Grant permissions from M2 on M1, and also grant permissions to M1 on M2

Mysql>grant replication Slave on . to ' replication ' @ ' 192.168.144.% ' identified by ' 123456 '; Both masters are executed and never need

• Set up synchronization with each other, note the location parameters between the two primary servers

Mysql>change Master to master_host= ' 192.168.144.145 ', master_user= ' replication ', master_password= ' 123456 ', master _log_file= ' mysql_bin.000001 ', master_log_pos=577;
Mysql>start slave; Open mutual Slave mode between main owners
Mysql>flush privileges;
Mysql>show slave status\g; View status

Mater2 Synchronous Master1

• Operation Steps above

Setting up master-Slave synchronization

• Two slave servers only need to be synchronized with one of the primary servers, and the other master server will be automatically synchronized.

Mysql>change Master to master_host= ' 192.168.144.128 ', master_user= ' replication ', master_password= ' 123456 ', master _log_file= ' mysql_bin.000001 ', master_log_pos=577;

Mysql>start slave;

Mysql>show slave status\g;

• Master-Slave synchronization is not described here, details can be found in the MySQL master-slave synchronization service detailed

Install MYSQL-MMM on all servers

Yum-y Install mysql-mmm*

• After the installation is complete, the MMM is configured

cd/etc/mysql-mmm/
VI mmm_common.conf//configuration on all hosts

• Remember that you need to configure the address pool for each server that has Mysql-mmm installed

• Follow the above changes to determine the DB serial number for each server

Vi/etc/mysql-mmm/mmm_agent.conf

this db1 //根据规划进行逐一调整

• Mmm_agent authorization on all database servers for easy point-of-failure switching

Mysql>grant Super, replication client, process on . to ' mmm_agent ' @ ' 192.168.144.% ' identified by ' 123456 ';

• Mmm_moniter authorization on all databases, allowing monitor to monitor the server

Mysql>grant replication Client on . to ' mmm_monitor ' @ ' 192.168.144.% ' identified by ' 123456 ';

Mysql>flush privileges;

• Start the MYSQL-MMM service on all database servers

Systemctl Start Mysql-mmm-agent.service
Systemctl Enable Mysql-mmm-agent.service Join boot

Configuring on Monitor

cd/etc/mysql-mmm///Change Password

VI mmm_mon.conf

<monitor>    ip                  127.0.0.1    pid_path            /run/mysql-mmm-monitor.pid    bin_path            /usr/libexec/mysql-mmm    status_path         /var/lib/mysql-mmm/mmm_mond.status    ping_ips            192.168.144.128,192.168.144.145,192.168.144.141,192.168.144.129  //设置监控地址池    auto_set_online     10   //设置自动上线时间10S······

• Start the Monitoring service

Systemctl Start Mysql-mmm-monitor.service

• View each server Status

Mmm_control Show

• A set of virtual IPs is displayed

  [[email protected] mysql-mmm]# mmm_control showdb1(192.168.144.128) master/ONLINE. Roles: writer(192.168.144.200)db2(192.168.144.145) master/ONLINE. Roles: db3(192.168.144.141) slave/ONLINE. Roles: reader(192.168.144.202)db4(192.168.144.129) slave/ONLINE. Roles: reader(192.168.144.201)

The failure test stops M1 confirm that virtual address 200 is moved to M2. Note: The Lord does not preempt

mmm_control show  db1(192.168.144.128) master/HARD_OFFLINE. Roles:  db2(192.168.144.145) master/ONLINE. Roles: writer(192.168.144.200)

Authorize login for the monitor address on the M1 server

Mysql>grant all in . to ' testdba ' @ ' 192.168.144.136 ' identified by ' 123456 ';
Flush privileges;

Log on to the monitoring server

Mysql-utestdba-p-H 192.168.144.200//virtual address

• Create the data and test the synchronization situation.

MySQL high-availability cluster--mmm high-availability architecture