High availability for MySQL database based on Mha+semi sync

First, the experimental principle: 1, the topological structure diagram is as follows:

2, Working principle:

• Save binary log events from the Master of Downtime crashes (Binlog events)
• Identify slave that contain the latest updates
• Apply the difference of the trunk log (relay log) to the other slave
• Apply binary log events saved from master (Binlog events)
• Raise a Slave for the new master
• Make other slave connect to the new master for replication

3. Toolkit: MHA software consists of two parts, the Manager Toolkit and Node Toolkit 1, the Manager Toolkit includes the following tools:

• Masterha_check_ssh checking SSH configuration status of MHA
• MASTERHA_CHECK_REPL Check MySQL replication status
• Masterha_manger Start MHA
• Masterha_check_status Detecting current MHA running state
• Masterha_master_monitor Checking Master for downtime
• Masterha_master_switch failover (automatic or manual)
• Masterha_conf_host adding or removing configured server information

2. Node Toolkit: These tools are typically triggered by MHA manager scripts, without human action, and include the following tools:

• Save_binary_logs Saving and copying the binary log of master
• Apply_diff_relay_logs identify the difference between the relay log events and apply their differential events to other slave
• Filter_mysqlbinlog Removing unnecessary rollback events (MHA no longer using this tool) Purge_relay_logs clearing the relay log (does not block SQL threads)

Second, the experimental environment preparation: 1, VMware four virtual host host names for the MANAGER,MASTER,SLAVE1,SLAVE2 system for the centos7 corresponding ip:192.168.190.128-1312, Setenforce 0 Turn off the selinuxiptables-f, clear the firewall. 3, the configuration of the Yum source, and Epel source 4, four virtual host to ensure time synchronization, Ntpdate command Three, the experimental steps: 1, four hosts to achieve SSH without a secret connection

Ssh-keygen Any one host generates a public key to Ssh-copy-id 192.168.190.128 generated file in the. ssh directory scp-r/root/.ssh 192.168.190.129scp-r/root/.ssh 1 92.168.190.130scp-r/root/.ssh 192.168.190.131

2. Manager node (1) Yum install mha4mysql-manager-0.56-0.el6.noarch.rpm mha4mysql-node-0.56-0.el6.noarch.rpm (2) mkdir/ data/mastermha/app1/-PV (3) vim/etc/mastermha/app1.cnf

[Server default]
User=mhauser Management account for the primary server
Password=centos Mhauser's password
MANAGER_WORKDIR=/DATA/MASTERMHA/APP1/working Directory
Manager_log=/data/mastermha/app1/manager.log Log Directory
remote_workdir=/data/mastermha/app1/
Ssh_user=root SSH Connection account
Repl_user=repluser Copy Account
Repl_password=centos Copy the password of the account
Ping_interval=1
[Server1]
HOSTNAME=192.168.190.129 Primary Server
Candidate_master=1 have a chance to be promoted.
[Server2]
hostname=192.168.190.130 from the server
Candidate_master=1 have a chance to be promoted.
[Server3]
hostname=192.168.190.131 from the server
Candidate_master=1 have a chance to be promoted.

3. master,slave1,slave2 node yum install MHA4MYSQL-NODE-0.56-0.EL6.NOARCH.RPM4, master node (1) systemctl start mariadb (2) Vim /etc/my.cnf

Log-bin enable two-tier system
Server_id=1 server ID, each node must be different
Innodb_file_per_table each library, each table
Skip_name_resolve=1 through IP connection, does not resolve host name
Log_slave_updates = 1 for master-slave switching
Plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" load the semi-synchronous copy of the plugin, master and slave are loaded, the main bad To achieve semi-synchronization from the top
loose_rpl_semi_sync_master_enabled = 1 Turn on the master's semi-synchronous function
loose_rpl_semi_sync_slave_enabled = 1 turn on slave semi-sync function
Loose_rpl_semi_sync_master_timeout = 5000

            mysql            mysql>show master logs            mysql>grant replication slave on *.* to [email protected]‘192.168.31.%‘ identified by ‘centos‘;            mysql>grant all on *.* to [email protected]‘192.168.190.%’ identified by ‘magedu‘;            show variables like ‘%semi%‘;

5. slave1 node and slave2 node

Vim/etc/my.cnf

[Mysqld]
server_id=2 different nodes This value varies
Log-bin
READ_ONLY read-only, invalid for superuser, the global variable will change when main bad switch is primary
relay_log_purge=0 do not delete the relay log
Skip_name_resolve=1 does not resolve host name, via IP connection
Plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" load the semi-synchronous copy of the plugin, master and slave are loaded, the main bad To achieve semi-synchronization from the top
loose_rpl_semi_sync_master_enabled = 1 Turn on the master's semi-synchronous function
loose_rpl_semi_sync_slave_enabled = 1 turn on slave semi-sync function
Loose_rpl_semi_sync_master_timeout = 5000

        mysql>CHANGE MASTER TO MASTER_HOST=‘MASTER_IP‘, MASTER_USER=‘repluser‘, MASTER_PASSWORD=‘centos‘, MASTER_LOG_FILE=‘mariadb-bin.000001‘, MASTER_LOG_POS=245;

6, the Manager node Test 1, masterha_check_ssh--conf=/etc/mastermha/app1.cnf detect SSH Connectivity 2, Masterha_check_repl--conf=/etc/ MASTERMHA/APP1.CNF Detection copy function

3, Masterha_manager--conf=/etc/mastermha/app1.cnf start MHA Management 4, Stop the master node mariadb service, view the corresponding log

found that the original primary node has been transferred for the bad failure of the primary node can be reset to the cluster inside the slave node, need to modify the manager's profile again note: the first master-slave switchover,/data/mastermha/app1/ will generate App1.failover.complete empty file, the next time you switch again if found in the directory that the file will not allow the trigger switch, if you switch again, delete it. MHA management tools, one-time in the foreground execution, after the failure, you need to manually open again.

High availability for MySQL database based on Mha+semi sync