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
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
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
Mmm_control Show
The failure test stops M1 confirm that virtual address 200 is moved to M2. Note: The Lord does not preemptmmm_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