First, log format
1.1 Binary Log Format
The MySQL binary log is the basis for primary/secondary replication. It records all changes to the MySQL database, including additions, deletions, changes, searches, and table structure changes. Currently, MySQL supports three binary log formats. You can use the binlog-format parameter to control the format. The possible values are as follows:
- STATEMENT: indicates the format of a segment. Is the earliest binary log format supported by MySQL. It records the SQL statements that actually perform the modification. Therefore, a small amount of data needs to be recorded during batch modification. However, UUID() or other context-dependent execution statements may produce different results on the primary and secondary systems.
- ROW: the binary log format is the default after MySQL 5.7. It records data before and after modification. Therefore, a large amount of data needs to be recorded during batch modification. However, it has high security and does not cause inconsistency between the master and the slave. It also reduces the use of locks because the ROW format applies the changed data directly from the library.
- MIXED: indicates a mixture of the above two types of logs. The segment format is used by default. When the segment format is not applicable (for example, UUID()), the ROW format is used by default.
Generally, when the network between the master and the slave is in good condition, ROW format is preferred. In this case, data consistency is the highest, followed by MIXED format. When formatting a ROW, there is also a very important parameter, binlog_row_image:
1.2 binlog_row_image
Binlog_row_image has the following three optional values:
-
Full: The default value, which records the values of all columns of the row before and after modification.
-
Minimal: Records only the values of the columns involved in the modification.
-
Noblob: Similar to full, but not logged if the BLOB or TEXT column has not been modified.
The default values of binlog-format and binlog_row_image may vary with different versions. You can run the following command to view the values. In general, you can set the value of binlog_ROW_image to minimal or noblob to reduce the amount of data that needs to be transferred in a master/slave replication.
show variables like 'binlog_format';
show variables like 'binlog_row_image';
Copy the codeBinary log-based replication
2.1 Replication Principles
MySQL replication works as shown in the following figure:
- The master library first writes the changes to its binary log;
- The secondary starts an IO thread and then proactively goes to the primary node to fetch the change log and write it to its own relay log.
- The change event is then read from the slave log and executed on the slave library.
- When the data state of the standby database is consistent with that of the primary database, the I/O thread of the standby database goes to sleep. When the primary library changes again, it signals the standby library to wake up the IO thread and start working again.
Without any configuration, the master library returns a response to the client after the changes are written to the binary log, so replication by default is completely asynchronous and there may be temporary data inconsistencies between the master and the standby.
2.2 Configuration Procedure
The binary log function must be enabled on the active node. In the same replication environment, the server ids of the active and standby nodes must be different.
[mysqld] server-id = 226 # log-bin=mysql-binCopy the codeConfigure trunk logs on the backup node:
[mysqld] server-id = 227 # set relay_log = mysql-relay_bin # set read_only = 1 # Log-bin = mysql-bin # Whether to write replication events received by the relay node to its own binary log log_slave_updates = 1Copy the codeLog in to the MySQL service on the primary node, create an account for replication, and grant permission to the account:
CREATE USER 'repl'@'192.168.0.%' IDENTIFIED WITH mysql_native_password BY '123456'; GRANT REPLICATION SLAVE on *.* TO 'repl'@'192.168.0.Copy the codeView the binary log status of the primary node:
mysql> SHOW MASTER STATUS;+------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 887 | | | | +------------------+----------+--------------+------------------+-------------------+Copy the codeSet up replication links based on logs and offsets:
CHANGE MASTER TO MASTER_HOST='192.168.0.226',\
MASTER_USER='repl', \
MASTER_PASSWORD='123456',\
MASTER_LOG_FILE='mysql-bin.000001',\
MASTER_LOG_POS=887;
Copy the codeStart copying:
START SLAVE;
Copy the codeCheck the replication status of the secondary node. Slave_IO_Running and Slave_SQL_Running are the main parameters. If the STATUS is Yes, the I/O process for replication is started. The Seconds_Behind_Master parameter indicates the secondary node replication delay. At this point, you can make any changes on the primary and view the situation on the standby.
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.226
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 887
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 322
Relay_Master_Log_File: mysql-bin.000001
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 887
Relay_Log_Space: 530
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
# Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 226
# Master_UUID: e1148574-bdd0-11e9-8873-0800273acbfd
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
Copy the code2.3 the advantages and disadvantages
Binary log-based replication is one of the earliest replication techniques used by MySQL, so it is well supported by MySQL and has almost no restrictions on SQL statements that can execute modifications. The main disadvantage is that in a high availability replication architecture with one master and many slaves, it is difficult to automatically identify the new master from the logs and offsets of the slave library if the master library goes down.
Gtid-based replication
2.1 GTID profile
MySQL 5.6 provides a new replication mode: GTID-based replication. GTID is a Global Transaction ID. It consists of the unique ID of each service node and the transaction ID on it in the format server_uuid: transaction_id. GTID ensures that every transaction on the primary can be executed on the standby without any omissions.
2.2 Configuration Procedure
Add the following GTID configurations for both primary and secondary servers:
Gtid-mode = ON # To prevent execution of unsupported statements, enforce-gtid-consistency = ONCopy the codeTo configure the above binary log-based replication, run the following command on the secondary server to shut down the original replication link:
STOP SLAVE IO_THREAD FOR CHANNEL '';
Copy the codeCreate a new gTId-based replication link and specify MASTER_AUTO_POSITION = 1 to indicate that the program will automatically confirm the location of the GTID to start synchronization:
CHANGE MASTER TO MASTER_HOST='192.168.0.226',\
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_AUTO_POSITION=1;
Copy the codeStart copying:
START SLAVE;
Copy the codeTo perform any modification on the primary node and view the status of the slave node, the key output is as follows: Retrieved_Gtid_Set represents the two transactions received from the primary node, and Executed_Gtid_Set indicates that these two transactions have been executed on the slave library.
mysql> SHOW SLAVE STATUS\G
....
Master_UUID : e1148574-bdd0-11e9-8873-0800273acbfd
Retrieved_Gtid_Set : e1148574-bdd0-11e9-8873-0800273acbfd:1-2
Executed_Gtid_Set : e1148574-bdd0-11e9-8873-0800273acbfd:1-2
.....
Copy the code2.3 the advantages and disadvantages
The advantage of GTID replication is that the program automatically recognizes the GTID point at which replication begins. But it still has the following limitations:
-
CREATE TABLE is not supported… The SELECT statement. Because in ROW format, the statement will be logged as two transactions with different GtiDs, the slave server will not be able to process it correctly.
-
CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements inside transactions, procedures, functions, and triggers are not supported.
To prevent the execution of unsupported statements, it is recommended that you enable the invincible-gtid-consistency attribute. After this attribute is enabled, exceptions are thrown and a message is displayed when the unsupported statements are executed on the master database.
Semi-synchronous replication
As mentioned above, both binary log-based replication and GTId-based replication are asynchronous replication in nature. If the master node is down before binary log information is obtained from the slave node, data inconsistency will occur. To solve this problem, you can configure semi-synchronous replication. During semi-synchronous replication, the master node will wait for at least one slave node to obtain binary logs before returning the transaction execution results to the client. The configuration procedure is as follows:
1. Install the plug-in
MySQL supports semi-synchronous replication in the form of plug-ins since 5.5. Therefore, you need to install the plug-ins first.
Mysql > INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; Mysql > INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';Copy the codeIf your replication is based on a high availability architecture, that is, the slave node may become the new master node after the master node goes down, and the original master node may become the slave node after the failure recovery, then you can install master/slave plug-ins on both the master and slave nodes to ensure that semi-synchronous replication is still effective. Run the following command to check whether the installation is successful:
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
| rpl_semi_sync_slave | ACTIVE |
+----------------------+---------------+
Copy the code2. Configure semi-synchronous replication
Semi-synchronous replication can be enabled based on log replication or GTID replication. You only need to add the following configuration to the original configuration:
Plugin-load =rpl_semi_sync_master=semisync_master.so rpl_semi_sync_master_enabled=1 Plugin-load =rpl_semi_sync_slave=semisync_slave.so rpl_semi_sync_slave_enabled=1 # As mentioned above, if the architecture is high availability, the master/slave configuration can be added to both master/slave nodes: plugin-load = "rpl_semi_sync_master=semisync_master.so; rpl_semi_sync_slave=semisync_slave.so" rpl-semi-sync-master-enabled = 1 rpl-semi-sync-slave-enabled = 1Copy the code3. Start replication
In this case, you can run the following command to check whether semi-synchronous logs are being executed:
#The master node
mysql> SHOW STATUS LIKE 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON |
+-----------------------------+-------+
#From the node
mysql> SHOW STATUS LIKE 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
Copy the codeIf the value is ON, the semi-synchronous replication is successfully configured.
4. Optional configuration
Semi-synchronous logs also have the following two optional configurations: One is rpl_semi_sync_master_WAIT_for_slave_count, which indicates that the master node needs to wait for at least several slave nodes to complete the replication. The default value is 1. Waiting for too many slave nodes may cause a long delay, so the default value is usually used. Another commonly used parameter is rpl_semi_SYNc_master_WAIT_point, which is mainly used to control the wait point. It has the following two optional values:
- AFTER_SYNC (default) : The master server writes each transaction to its binary log and synchronizes the binary log to disk to begin waiting. After receiving acknowledgement from the slave node, the transaction is committed to the storage engine and the result is returned to the client.
- AFTER_COMMIT: The primary server writes each transaction to its binary log and synchronizes it to disk, then commits the transaction to the storage engine and waits after the commit. After receiving an acknowledgement from the slave node, the result is returned to the client.
The second method is the default method before MySQL 5.7.2, but this method can lead to data loss. Therefore, the first method was introduced as the default method after MySQL 5.7.2, which can achieve lossless replication with almost no data loss. Therefore, the rpl_semi_SYNC_MASTER_WAIT_point parameter usually does not need to be modified and the default value is used. To view the value of this parameter in the current version, run the following command:
mysql> SHOW VARIABLES LIKE 'rpl_semi_sync_master_wait_point';
+---------------------------------+------------+
| Variable_name | Value |
+---------------------------------+------------+
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+---------------------------------+------------+
Copy the codeAlthough semi-synchronous replication can avoid data loss to the greatest extent, it is recommended to use it in a low-latency network environment, for example, between hosts in the same equipment room, because network communication may cause extra waiting time.
High availability architecture
Regardless of the master master replication structure or the one master and multiple slave replication structure, single-storage replication can only solve the problem of data reliability, but cannot solve the problem of high availability of the system. To ensure high availability, the system must be able to automatically failover, that is, when the master database is down, actively upgrade other standby databases to the master database. The following two solutions are commonly used:
5.1 MMM
The Master-Master Replication Manager for MySQL (MMM) is a set of third-party software developed in Perl that supports dual-master failover and dual-master routine management. It contains two types of roles: Writer and Reader, corresponding to read/write nodes and read-only nodes respectively. When the writer node breaks down (for example, Master1), the program automatically removes the read and write VIP from the node and switches to Master2. Set read_only of Master2 to 0, that is, disable the read-only limit and redirect all Slave nodes to Master2.
In addition to managing the two master nodes, MMM is also responsible for managing all Slave nodes. In case of downtime, replication delay, or replication errors, MMM removes the VIP of the node until the node is restored. An example of MMM’s highly available architecture is shown below:
The disadvantage of MMM architecture is that although it can implement automatic switching, it does not actively replace lost data, so there is a risk of data inconsistency. In addition, MMM was released earlier, so it does not support MySQL’s latest GTID-based replication. If you are using GTID-based replication, you can only use MHA.
5.2 MHA
MHA is a High Availability program implemented by Perl. Compared with MMM, MHA can avoid data inconsistency as much as possible. It monitors a master and slave replication architecture, as shown below:
If the Master node is down, the process is as follows:
- Tried to save binary logs from the outage Master;
- Find the Slave that contains the latest relay log;
- Apply the latest relay logs to other instances to ensure the consistency of data among instances.
- Apply binary log events saved from Master;
- Promoted a Slave to Master.
- Other slaves synchronize with the new Master.
Following the above process, MHA can minimize data inconsistency problems. But if the server on which the Master resides also goes down, the first step in the process will fail. With MySQL 5.5, this problem can be avoided by enabling semi-synchronous replication to ensure data consistency and almost no loss. Of course, MHA clustering also has some disadvantages:
- The SSH service must be enabled between all nodes in the cluster. Therefore, security may be affected.
- High availability of Slave is not implemented.
- For example, you need to configure the virtual IP address by running commands or using third-party software.
- You need to manually clear trunk logs.
The above is a brief introduction to MMM and MHA architecture. For details on how to set up MySQL cluster (3)-MMM High availability architecture and MySQL cluster (5)-MHA high availability architecture, please refer to the following two blogs.
The resources
- Chapter 17 Replication
- GTID Format and Storage
- MySQL semi-synchronous replication
- MySQL cluster construction (3)-MMM high availability architecture
- MySQL Cluster setup (5)-MHA high availability architecture
For more articles, please visit the full stack Engineer manual at GitHub.Github.com/heibaiying/…
