1, MHA overview
1.1 what is MHA
MHA (MasterHigh Availability) is a set of excellent software for failover and master-slave replication in MySQL high availability environment.
The emergence of MHA is to solve the problem of MySQL single point.
During MySQL failover, MHA can automatically complete the failover operation within 0-30 seconds.
MHA can ensure the consistency of data to the greatest extent in the process of failover, so as to achieve high availability in the real sense.
1.2 composition of MHA
1) MHA Node
The MHA Node runs on each MySQL server.
2) MHA Manager (management node)
MHA Manager can be deployed on an independent machine to manage multiple master slave clusters; It can also be deployed on a slave node.
The MHA Manager will periodically probe the master node in the cluster. When the master fails, it can automatically promote the slave of the latest data to the new master, and then point all other slave to the new master again. The entire failover process is completely transparent to the application.
1.3 characteristics of MHA
In the process of automatic failover, MHA tries to save binary logs from the down main server to ensure no data loss to the greatest extent;
Using semi synchronous replication can greatly reduce the risk of data loss. If only one slave has received the latest binary log, MHA can apply the latest binary log to all other slave servers, so it can ensure the data consistency of all nodes;
At present, MHA supports a master - slave architecture, with at least three services, i.e. one master and two slaves
2, MHA construction preparation
1.1 experimental ideas
1.MHA architecture ① database installation ② one master and two slave ③ MHA construction
2. Fault simulation # ① the main library fails ② the alternative main library becomes the main library ③ the original fault main library recovers and rejoins to MHA to become the slave library
1.2 experimental preparation
|Node server||system||host name||IP address||Installation services and tools|
|MHA manager node server||CentOS7.4(64 bit)||manager||192.168.80.129||Install MHA node and manager components|
|Master node server||CentOS7.4(64 bit)||mysql1||192.168.80.130||Install mysql5 7 and MHA node components|
|Slave 1 node server||CentOS7.4(64 bit)||mysql2||192.168.80.131||Install mysql5 7 and MHA node components|
|Slave2 node server||CentOS7.4(64 bit)||mysql3||192.168.80.200||Install mysql5 7 and MHA node components|
3, Steps of MHA construction
3.1 configuring master-slave replication
1. Initialize the environment
#Initialize the environment on four servers systemctl stop firewalld systemctl disable firewalld setenforce 0
2. Modify the host names of Master, Slave1 and Slave2 nodes
#On Master hostnamectl set-hostname Mysql1 su #In Slave1 hostnamectl set-hostname Mysql2 su #In Slave2 hostnamectl set-hostname Mysql3 su
3. Add domain name resolution in Master, Slave1 and Slave2
vim /etc/hosts 192.168.80.130 Mysql1 192.168.80.131 Mysql2 192.168.80.200 Mysql3
4. Configure master-slave synchronization
#Modify the Mysql main configuration file of Master, Slave1 and Slave2 nodes / etc / my cnf ##Master node## vim /etc/my.cnf [mysqld] server-id = 1 log_bin = master-bin log-slave-updates = true systemctl restart mysqld ##Slave1 node## vim /etc/my.cnf server-id = 2 #The server IDs of three servers cannot be the same log_bin = master-bin relay-log = relay-log-bin relay-log-index = slave-relay-bin.index systemctl restart mysqld ###Slave2 node## vim /etc/my.cnf server-id = 3 log_bin = master-bin relay-log = relay-log-bin relay-log-index = slave-relay-bin.index systemctl restart mysqld
5. Create two soft links on master, Slave1 and Slave2 nodes
ln -s /usr/local/mysql/bin/mysql /usr/sbin/ ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/ ls /usr/sbin/mysql*
6. Login database master-slave configuration authorization
###Master,Slave1,Slave2 All nodes are authorized### grant replication slave on *.* to 'myslave'@'192.168.80.%' identified by '123456'; #Authorized master-slave user grant all privileges on *.* to 'mha'@'192.168.80.%' identified by 'manager'; grant all privileges on *.* to 'mha'@'Mysql1' identified by 'manager'; grant all privileges on *.* to 'mha'@'Mysql2' identified by 'manager'; grant all privileges on *.* to 'mha'@'Mysql3' identified by 'manager'; #Refresh Library flush privileges;
7. The master node views the binary files and synchronization points, and performs synchronization operations at Slave1 and Slave2 nodes
###stay master upper### show master status; ###stay slave1,slave2 The node performs synchronization operations## change master to master_host='192.168.80.130',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=1745; start slave; show slave status\G;
###On the master ###
8. Set the read-only mode of two slave nodes
set global read_only=1;
9. Verify master-slave synchronization
##In the Lord master Create libraries, tables, and insert data on## create database test1; use test1; insert into info values('Alline'); select * from test1.info; ##stay slave1,slave2 Upper verification## select * from test1.info;