How to implement MySQL asynchronous replication and semi-synchronous replication

Asynchronous replication

In asynchronous replication (async replication), the Master does not need to care whether the Slave receives the binary log, so the Master There is no dependency on Slave. Master and Slave can be regarded as two servers that work independently. With the help of binary logs, they can ultimately maintain data consistency.

Asynchronous replication has the best performance because it has almost no overhead on the database itself, unless the master-slave delay is very large and the Dump Thread needs to read a large number of binary log files.

If the business does not have high requirements for data consistency and can tolerate data loss, or even a large amount of loss, when a failure occurs, it is recommended to use asynchronous replication, which has the best performance (for example, like Although businesses like Weibo have extremely high performance requirements, data loss can usually be tolerated). But often core business systems are most concerned about data security, such as monitoring services and alarm systems.

Planning of asynchronous replication environment:

• master (docker), port 3310

• ##slave (docker), port 3311

master's configuration

Configuration file my.cnf

$ sudo cat /home/mysql/docker-data/3311/conf/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#datadir=/home/mysql/docker-data/3307/data
#socket=/home/mysql/docker-data/3307/mysql.sock
character_set_server=utf8
init_connect='SET NAMES utf8'
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#log-error=/home/mysql/docker-data/3307/logs/mysqld.log
#pid-file=/home/mysql/docker-data/3307/mysqld.pid
lower_case_table_names=1 # 表名是否小写
server-id=1403311
log-bin=mysql-bin # 开启binlog
binlog-format=ROW # binlog的格式
auto_increment_increment=1 # 自增的步长，适用于主主复制，为了避免id冲突，步长设置为master的个数
auto_increment_offset=1 # 自增的偏移，主主复制每个master的偏移需要不一致
# binlog-do-db=mstest      # 要同步的数据库
# binlog-ignore-db=mysql  # 要忽略的数据库
#rpl_semi_sync_master_enabled=1
#rpl_semi_sync_master_timeout=10000

Start mysql:

$ docker run --name mysql3310 -p 3310:3306 --privileged=true -ti -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=order -e MYSQL_USER=user -e MYSQL_PASSWORD=pass -v /home/mysql/docker-data/3310/conf:/etc/mysql/conf.d -v /home/mysql/docker-data/3310/data/:/var/lib/mysql -v /home/mysql/docker-data/3310/logs/:/var/log/mysql -d mysql:5.7

Create a user for synchronization:

mysql> GRANT REPLICATION SLAVE,FILE,REPLICATION CLIENT ON *.* TO 'repluser'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

View the master's binary log on the master:

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      1147 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

View the process list on the master:

mysql> show processlist;
+----+----------+------------------+-------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User     | Host             | db    | Command     | Time | State                                                         | Info             |
+----+----------+------------------+-------+-------------+------+---------------------------------------------------------------+------------------+
|  2 | root     | localhost        | order | Query       |    0 | starting                                                      | show processlist |
|  6 | repluser | 172.17.0.1:48450 | NULL  | Binlog Dump |  448 | Master has sent all binlog to slave; waiting for more updates | NULL             |
+----+----------+------------------+-------+-------------+------+---------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

slave's configuration

Configuration file my. The configuration of cnf is the same as that of master, except that the server-id field needs to be unique.

Start mysql:

$ docker run --name mysql3311 -p 3311:3306 --privileged=true -ti -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=order -e MYSQL_USER=user -e MYSQL_PASSWORD=pass -v /home/mysql/docker-data/3311/conf:/etc/mysql/conf.d -v /home/mysql/docker-data/3311/data/:/var/lib/mysql -v /home/mysql/docker-data/3311/logs/:/var/log/mysql -d mysql:5.7

Set the master information in the slave:

# master_log_file和master_log_pos取上面show master status显示的值mysql> change master to master_host='172.23.252.98',master_port=3310,master_user='repluser',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=1147;

Open the slave, start SQL and IO threads:

mysql> start slave;Query OK, 0 rows affected (0.00 sec)

View the slave Status:

mysql> show slave status\G;*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.54.214
                  Master_User: repluser
                  Master_Port: 3310
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 1147
               Relay_Log_File: 2da789531bf3-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003
             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: 1147
              Relay_Log_Space: 534
              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: 0Master_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: 1403311
                  Master_UUID: cd2eaa0a-7a59-11ec-b3b4-0242ac110002
             Master_Info_File: /var/lib/mysql/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:1 row in set (0.00 sec)

Only newly added data will be copied, and existing data needs to be synchronized manually using tools (such as mysqldump).

View the process list on the slave:

mysql> show processlist;
+----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db    | Command | Time | State                                                  | Info             |
+----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+
|  4 | root        | localhost | order | Query   |    0 | starting                                               | show processlist |
|  7 | system user |           | NULL  | Connect |  533 | Waiting for master to send event                       | NULL             |
|  8 | system user |           | NULL  | Connect |  127 | Slave has read all relay log; waiting for more updates | NULL             |
+----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

Semi-synchronous replication

Semi-synchronous replication is the semi-synchronous replication before MySQL 5.7 version mechanism.

Semi-synchronous replication requires that during the Master transaction submission process, at least N Slaves receive binary logs. This ensures that when the Master goes down, there are at least N Slaves. The data in the server is complete.

Semi-synchronous replication is not a built-in function of MySQL. Instead, you need to install the semi-synchronous plug-in, enable the semi-synchronous replication function, and set up N slaves to accept binary logs successfully.

Disadvantages: Suppose user1 inserts a piece of data in the main database and is waiting for the data to be returned. User2 can query this data at this time. If the master hangs up at this time, user2 cannot find this data. , resulting in a phenomenon similar to phantom reading.

Enhanced semi-synchronous replication

Enhanced semi-synchronous replication solves the shortcomings of semi-synchronous replication. WAIT and ACK occur before the transaction is committed, so that even if the Slave does not receive the binary Log, but the Master is down. Since the last transaction has not been submitted, the data itself is not visible to the outside world, and there is no problem of loss.

Therefore, for any business that has data consistency requirements, such as the core order business of e-commerce, banking, insurance, securities and other businesses closely related to funds, be sure to use Enhanced semi-synchronous replication. Even if there is an outage, the slave machine will have a complete data backup, so the data will be safe and reliable.

The enhanced semi-synchronous replication environment is based on asynchronous replication.

Install the plug-in. It is recommended to install it on both the master and slave, because there will be a master-slave switching situation:

# master
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
# slave
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

Make sure the plug-in is installed successfully:

mysql> show plugins;
... ...
| rpl_semi_sync_master       | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |
| rpl_semi_sync_slave        | ACTIVE   | REPLICATION        | semisync_slave.so  | GPL     |
+----------------------------+----------+--------------------+--------------------+---------+
46 rows in set (0.00 sec)

First start the half on the slave Synchronization:

mysql> set global rpl_semi_sync_slave_enabled = {0|1};  # 1：启用，0：禁止

Restart semi-synchronization on the master:

mysql> set global rpl_semi_sync_master_enabled = {0|1};  # 1：启用，0：禁止
# mysql> set global rpl_semi_sync_master_timeout = 10000;    # 单位为ms，默认为10s

Restart io_thread from the library:

mysql> stop slave io_thread;
mysql> start slave io_thread;

Why is it not recommended to write the parameters of semi-synchronization into the configuration file

If the parameters are written into the configuration file, the instance will enter semi-synchronous mode immediately after startup. If an instance that has been disconnected for a long time is re-started, it may cause the main database to be brought down.

After reconnecting the slave database that has been disconnected for a long time, you must wait for all transactions to be traced and then manually turn on the semi-synchronous mode instead of switching directly after startup to prevent impact on the main database.

Query master database status information

mysql> show global status like "%semi%";
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
| Rpl_semi_sync_slave_status                 | ON    |
+--------------------------------------------+-------+
15 rows in set (0.00 sec)

Important parameter description:

• Rpl_semi_sync_master_clients: Number of connected Slaves that support and register semi-synchronous replication

• Rpl_semi_sync_master_no_times: The number of times the master has closed semi-synchronous replication

• Rpl_semi_sync_master_no_tx: The number of times the master has submitted without receiving a reply from the slave, which can be understood as the master waiting timeout The number of times, that is, the number of unsuccessful submissions in semi-synchronous mode

• Rpl_semi_sync_master_status：ON是活动状态（半同步），OFF是非活动状态（异步），用于表示主服务器使用的是异步复制模式，还是半同步复制模式

• Rpl_semi_sync_master_tx_avg_wait_time：master花在每个事务上的平均等待时间

• Rpl_semi_sync_master_tx_waits：master等待成功的次数，即master没有等待超时的次数，也就是成功提交的次数

• Rpl_semi_sync_master_yes_tx：master成功接收到slave的回复的次数，即半同步模式成功提交数量。

查询主库参数信息

mysql> show global variables like '%sync%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| binlog_group_commit_sync_delay            | 0          |
| binlog_group_commit_sync_no_delay_count   | 0          |
| innodb_flush_sync                         | ON         |
| innodb_sync_array_size                    | 1          |
| innodb_sync_spin_loops                    | 30         |
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_semi_sync_slave_enabled               | ON         |
| rpl_semi_sync_slave_trace_level           | 32         |
| sync_binlog                               | 1          |
| sync_frm                                  | ON         |
| sync_master_info                          | 10000      |
| sync_relay_log                            | 10000      |
| sync_relay_log_info                       | 10000      |
+-------------------------------------------+------------+
18 rows in set (0.01 sec)

重要参数说明：

• rpl_semi_sync_master_enabled：（主库）是否启动半同步

• rpl_semi_sync_master_timeout：等待多时毫秒后变成异步复制，默认是10000ms

• rpl_semi_sync_master_wait_point：5.7默认AFTER_SYNC（增强版半同步复制，无损复制模式），在得到slave的应答后再commit，可选值AFTER_COMMIT，在master提交后同步数据给slave，然后master等待slave应答，应答成功返回客户端。

可以在slave端执行stop slave，测试master端会发生什么情况？

在master上执行下面的sql：

mysql> insert into t_order values(3,"C");Query OK, 1 row affected (10.05 sec)

会发现这条语句会阻塞10s（对应上面的参数rpl_semi_sync_master_timeout），然后执行成功，最后看看看master的日志描述：

2022-01-25T02:31:57.016430Z 4 [Note] Start binlog_dump to master_thread_id(4) slave_server(1403312), pos(mysql-bin.000005, 154)
2022-01-25T02:31:57.016515Z 4 [Note] Start asynchronous binlog_dump to slave (server_id: 1403312), pos(mysql-bin.000005, 154)
2022-01-25T02:33:32.183819Z 2 [Note] Semi-sync replication initialized for transactions.
2022-01-25T02:33:32.183865Z 2 [Note] Semi-sync replication enabled on the master.
2022-01-25T02:33:32.184004Z 0 [Note] Starting ack receiver thread
2022-01-25T02:33:59.644444Z 5 [Note] While initializing dump thread for slave with UUID <aba2eb12-7cbc-11ec-9c1d-0242ac110003>, found a zombie dump thread with the same UUID. Master is killing the zombie dump thread(4).
2022-01-25T02:33:59.644612Z 5 [Note] Start binlog_dump to master_thread_id(5) slave_server(1403312), pos(mysql-bin.000005, 154)
2022-01-25T02:33:59.644632Z 4 [Note] Stop asynchronous binlog_dump to slave (server_id: 1403312)
2022-01-25T02:33:59.644727Z 5 [Note] Start semi-sync binlog_dump to slave (server_id: 1403312), pos(mysql-bin.000005, 154)
2022-01-25T02:38:02.847879Z 0 [ERROR] mysqld: Got an error reading communication packets
2022-01-25T02:38:27.228952Z 2 [Warning] Timeout waiting for reply of binlog (file: mysql-bin.000005, pos: 684), semi-sync up to file mysql-bin.000005, position 419.
2022-01-25T02:38:27.229063Z 2 [Note] Semi-sync replication switched OFF.
2022-01-25T02:39:47.230189Z 5 [Note] Stop semi-sync binlog_dump to slave (server_id: 1403312)

可以发现半同步关闭了，变成异步模式。

The above is the detailed content of How to implement MySQL asynchronous replication and semi-synchronous replication. For more information, please follow other related articles on the PHP Chinese website!