MMM, MHA and MGR, the high availability architecture have the following in common:

  • Monitor the Master node in the primary and secondary replication clusters
  • Automatically migrate the Master through the VIP.
  • Reconfigure other slaves in the cluster to synchronize the new Master

MMM

Two masters are required, and only one Master provides services at a time. It can be said to be in active/standby mode.

Basic resources required:

resources The number of instructions
Master DB 2 Used for active/standby replication
From the DB 0 ~ N You can configure up to N secondary servers as required
The IP address 2n+1 N indicates the number of MySQL servers
Monitoring the user 1 User Monitoring database status MySQL user (Replication)
Proxy user 1 Used to change the read_only state of the MMM agent

Failover steps:

  • Operations performed on the Slave server
    • The replicated logs on the original master are restored
    • Run the Change Master command to configure the new Master
  • Operate on the primary server
    • Disable the read_only function
    • Migrate viPs to the new master server

Advantages:

  • Provide read and write VIP configuration, try read and write requests can reach high availability
  • The toolkit is relatively complete and requires no additional development scripts
  • After failover is complete, you can monitor the MySQL cluster for high availability

Disadvantages:

  • The faults are simple, and transactions are likely to be lost. You are advised to use semi-synchronous replication to reduce the failure probability
  • Currently, MMM community is not maintained and does not support gtid-based replication

Application scenario:

  • Both reads and writes need to be highly available
  • Log point-based replication

MHA

Resources required:

resources The number of instructions
Master DB 2 Used for active/standby replication
From the DB 2 ~ N Taiwan You can configure up to N secondary servers as required
The IP address n+2 N indicates the number of MySQL servers
Monitoring the user 1 User Monitoring database status MySQL user (Replication)
Copy the user 1 User used to configure the MySQL replication

MHA uses the following method to select Master from slave and failover:

  • Slave server:
    • Elections have the latest updated slave
    • Try to save binary logs from the down master
    • Apply differential relay logs to other slaves
    • Application Binary logs saved from the master
    • Elevate the selected slave to master
    • Configure other slaves to synchronize to the new master

Advantages:

  • MHA supports GTID in addition to log point replication
  • Compared to MMM, MHA will try to recover the old binary log from the old Master, but not always successfully. If you want fewer data loss scenarios, you are advised to use the MHA architecture.

Disadvantages:

MHA needs to develop its own VIP transfer script.

The MHA monitors only the Master status, but not the Slave status

MGR

MGR is a replication plug-in based on the existing MySQL architecture. It can realize multiple masters to modify data and use paxOS protocol for replication, which is different from the multi-master replication cluster of asynchronous replication.

Multi-master mode is supported, but single master mode is officially recommended:

  • In multi-master mode, clients can randomly write data to MySQL nodes
  • In single primary mode, the primary node will be selected by the MGR cluster to take charge of write requests, and the primary node and other nodes can handle read requests.

Select * from performance_schema.replication_group_members; Select * from performance_schema.replication_group_member_stats; // Show variables like'group%'; // Check whether the server is read-only. Show variables like'read_only%';
Copy the code

Advantages:

  • Basically no latency, latency is much smaller than asynchronous
  • Multiwrite mode is supported, but is not yet mature
  • Strong data consistency, can ensure that the data transaction is not lost

Disadvantages:

  • Only supports innodb
  • This parameter can be used only in GTID mode and the log format is ROW

Applicable service scenarios:

  • It is sensitive to master-slave delay
  • You want high availability for write to write services, but you don’t want to install third-party software
  • The data is strongly consistent

Large read/write load problem

Large read load:

  • Increase the slave

  • Add middle layer (MyCat, ProxySQL, Maxscale)

  • Reading and writing separation

About heavy write loads:

  • Depots table
  • Add intermediate layer

The last

For moOCs, go to s.imooc.com/S8KFBvs