Shulou(Shulou.com)06/01 Report--

I. Overview of MHA

MHA (Mater High Availability) is a set of very popular and practical MySQL high availability solution software, which ensures the high availability of the master library in the MySQL master-slave replication cluster and ensures that the cluster business is not affected. When the master is down abnormally, MHA can guarantee the failover within 1: 30 seconds, select an optimal slave to upgrade to the latest master, maintain the state of data consistency, and minimize all data losses of the entire cluster. Therefore, the MHA scheme is very popular.

II. MHA architecture

MHA consists of Manager (management node) and Node (data node). The Manager service can run on a single read server (virtual machine) to manage multiple master-slave clusters, or it can be a slave node or an application server node, while the Node service needs to run on each MySQL server. Manager regularly monitors the master database through the Node service on the master database to ensure that when the master database fails, the optimal slave database can be automatically (or specified) upgraded to the new master database, so that all slave libraries and the latest master can be kept normal.

Third, the switching principle

The principle of automatic switching of MHA: the full name of MHA is mysql-master-ha. After configuration, the automatic switching of master can be completed within 10-30 seconds. The switching process is as follows: 1. Check the status of master by "SELECT 1 As Value" once a second. If no response is found, the check will be repeated 3 times. If there is no response, shutdown and repeat SELECT 1 As Value to confirm that master is off 2. Confirm that the machine from SSH to master can reach 3. 0. Give the message: Connecting to a master server failed, and start reading the configuration files masterha_default.conf and app1.conf4. Confirm the replication switching mode: [info] GTID failover mode = 15. Report the survival of machines in the entire architecture 6. Check the surviving instance version, whether GTID is enabled, whether read_only is enabled, and replication filtering 7. The next step is the handover process based on GTID replication (1) configuration check phase, which specifically checks as follows: (2) the stage in which the master connection is completely closed to avoid brain fissure caused by the failure of master. After the shutdown is completed, a report is given (3) master recovery phase: > 1 confirm the latest slave instance of relay log > 2 determine the new master if the candidate master is set in the configuration file, the preset machine instance will be directly determined to be master. If there is no default, the slave > 3 that contains the latest relay log will be selected to confirm the new master, the sql_log_bin=0 will be set to block master log writes so that other slave can catch up with replication, the latest relay log will be applied, and finally this level of data consistency will be obtained, and then set sql_log_bin=1 will make the recovery log write. Semi-synchronous replication can be used to solve the problem of transaction loss caused by unable to ssh to the master machine until all the data is consistent. Determine the log location of the new master through show master status and execute change master statements on other slave to create a new master-slave connection to the results of this phase. Report Fri Jul 1 13:35:33 2016-[info] * * Finished master recovery successfully.Fri Jul 1 13:35:33 2016-[info] * Phase 3: Master Recovery Phase completed. (4) slaves recovery phase: first stop the IO thread, wait for the SQL thread execution to complete, stop slave, clear the original slave information Re-change master points to the new master,start slave, and over (5) clears the slave information reset slave all on the newly selected master At this point, the whole handover process is completed. Finally, the handover report 3.2 MHA online handover principle is given: 1. Checking the current configuration information and master-slave server information includes reading the MHA configuration file / etc/masterha/app1.cnf and checking the health status of the current slave 2. The main steps to prevent updates to the current master are as follows: 1 > wait 1.5s ($time_until_kill_threads*100ms) and wait for the current connection to be disconnected. 2 > execute read_only=1 to block new DML operations 3 > wait 0.5s for the current DML operation to complete. 4 > kill drops all connections. 5 > FLUSH NO_WRITE_TO_BINLOG TABLES6 > FLUSH TABLES WITH READ LOCK3. Wait for the new master to finish executing all relay logWaiting to execute all relay logs on 192.168.244.20 (192.168.244.20). 4. Set the read_only of the new master to off and add VIP5. Exe. Slave switches to the new master. 1 > wait for slave (192.168.244.30) to apply the relay log generated by the original master-slave replication, and then perform the change master operation to switch to the new master. 2 > release the lock added on the original master. 3 > because the masterha_master_switch command line has the-- orig_master_is_new_slave parameter, the original master is also switched to the slave of the new master. 6. Clean up information about the new master. The main reason is to perform the reset slave all operation to clear the previous replication information. Note: keepalived can be used when accessing the data cluster.

III. MySQL replication

Asynchronous replication (Asynchronous replication) MySQL default replication is asynchronous. The master database will immediately return the result to the client after executing the transaction submitted by the client, and does not care whether the slave database has received and processed it. So there will be a problem. If the master crash is lost, the committed transaction on the master may not be transferred to the slave. If the slave is forced to be promoted at this time, the data on the new master may be incomplete. Fully synchronous replication (Fully synchronousreplication) means that when the master library executes a transaction, all slave libraries execute the transaction before returning to the client. Because you need to wait for all slaves to complete the transaction before returning, the performance of fully synchronous replication is bound to be seriously affected. Semi-synchronous replication (Semisynchronous replication) is between asynchronous replication and full-synchronous replication. The main library does not return to the client immediately after executing the transaction committed by the client, but waits for at least one to be received from the library and written to relay log before returning to the client. Compared with asynchronous replication, semi-synchronous replication improves the security of data, and it also causes a certain degree of delay, which is at least one TCP/IP round trip time. Therefore, semi-synchronous replication is best used in low-latency networks.

Asynchronous replication

MySQL-MHA architecture diagram

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.