Summarize and organize MySQL semi-synchronous replication configuration-Mysql Tutorial-php.cn

This article brings you relevant knowledge about mysql, which mainly sorts out the issues related to semi-synchronous replication configuration. In semi-synchronous mode, the main database transaction must ensure that at least one slave database receives The submission cannot be completed until the log. Therefore, it can be guaranteed that when the main database goes down, the submitted transactions have been transferred to the slave database to ensure that the data is not lost. Let's take a look at it together. I hope it will be helpful to everyone.

Summarize and organize MySQL semi-synchronous replication configuration

Recommended learning: mysql video tutorial

Foreword:

MySQL default Master-slave replication adopts an asynchronous mode. The master database does not need to confirm whether the transaction submitted has been transmitted to the slave database. When the master database fails and switches between master and backup, the transactions that have been submitted by the master database may be lost in the slave database.

In MySQL 5.7, the semi-synchronous replication mode is supported through the plug-in. In the semi-synchronous mode, the main database transaction must ensure that at least one slave database receives the log before it can be completed. Therefore, it can be guaranteed that when the main database goes down, , the submitted transaction has been transferred to the slave database to ensure that the data is not lost.

Semi-synchronous:

The performance impact of semi-synchronous replication compared to asynchronous replication is mainly the trade-off of data integrity, which adds the need to send submissions to the slave database and confirm The received TCP/IP round-trip time, therefore, semi-synchronous replication requires a high-transmission network and a close distance between the master and slave. If the network transmission is slow or the distance between the master and slave is too far, it may cause the performance of the master database to decrease and Semi-synchronous replication timed out.

The main library and slave library of semi-synchronous replication will perform the following operations:

When connecting to the main library, the replication system will check whether the main library has semi-synchronous replication turned on;

# After the transaction has been completed and written to the relay log and flushed to disk (there is no need to complete this transaction), you can send confirmation to the main library that the transaction has been received;

If no slave library sends confirmation information within the specified time To the main library, the main library's semi-synchronous replication will time out and be downgraded to asynchronous replication. When at least one semi-synchronous slave library can send confirmation information to the main library, the main library will automatically upgrade to semi-synchronous mode;

## Semi-synchronous replication needs to be turned on in both the master library and the slave library at the same time. If the master library does not turn on the semi-synchronous mode or the master library turns on the semi-synchronous mode but none of the slave libraries turns on the semi-synchronous mode, the master-slave replication will still use the asynchronous mode ;

When the main database transaction submission is blocked due to waiting for slave database confirmation, the session that submitted the transaction will not return until the blockage is over, and the session will not be returned. The submission of the main database transaction needs to wait for at least one slave database confirmation. accept. The rpl_semi_sync_master_wait_for_slave_count parameter can be used to control the number of slaves that need to be confirmed in the master library. The default is 1.

Covering will also occur in the non -transactional table Rollback. Because the non -transaction table is not rolled back, the Rollback of the non -transaction table still has to be written into the log to the Congki.

RPL_SEMI_SYNC_MATER_WAIT_POINT Parameters are used in the half -synchronization mode to control the main library transaction. to the binlog and synchronize the slave library and sync binlog to disk. After the master library synchronizes, it waits for the slave library to receive confirmation of the transaction. After waiting for the slave library's confirmation, the master library submits the transaction to the storage engine and returns to the client.

after_commit: The master library writes the transaction to the binlog and synchronizes the slave library, sync binlog, and submits the transaction to the storage engine. After submission, the master library waits for the slave library to receive confirmation of the transaction. After receiving the confirmation, The origin returns the submission completion to the client.

The two synchronization time points mainly have the following differences

Under after_sync, all clients see the submitted transaction at the same time. Because the storage engine is submitted after confirming that the transaction has been received from the database, all clients see the data of the submitted transaction at the same time. ; In addition, when a failover occurs, all submitted transactions in the master database have been synchronized to the relay log of the slave database, and the data in the slave database is lossless.

client-->MySQLSQL Parse-->Storage Involve-->write binary log-->wait ACK-->storagecommit-->client(OK)

Under after_commit, the client that submitted the transaction needs to wait for confirmation that the slave library has received the transaction before returning. However, since the submission to the storage engine is completed before confirming the slave library, other clients will be earlier than the client that submitted the transaction. When a failover occurs, other clients may experience data loss related to the data they see on the source for transactions that have been committed to the storage engine but have not yet been confirmed to have been committed by the slave database. .

client-->MySQLSQL Parse-->Storage Involve-->write binary log-->storagecommit-->wait ACK-->client(OK)

In summary, it is recommended to use the default after_sync.

Semi-synchronous installation steps:

Environment information:

MySQL version: 5.7.32

Architecture: one master (192.168.1.110:3306) two slaves (192.168.1.111:3306,192.168.1.111:3307)

binlog:on

binlog_row_image:full

binlog_format:row

binlog_format: row

## Master-slave asynchronous replication (steps omitted here).

Semi-synchronous replication does not support multi-source replication (multiple channels). Semi-synchronous replication can only use the default channel.

Configuration management uses commands INSTALLPLUGIN, SET GLOBAL, STOP SLAVE, START SLAVE and super privilege SUPER privilege.

installation plug -in requires MySQLSERVER support dynamic loading. You need to confirm HAVE_DYNAMIC_LOADING to yes

show global variables like'%have_dynamic_loading%';

Copy after login

to confirm that the plug -in path is configured correctly, and the default is in Install_home/Lib/Plugin.

show global variables like '%plugin_dir%';

Copy after login

Confirm that the semi-synchronous replication plug-ins semisync_master and semisync_slave exist under the plug-in path plugin_dir. The file name suffix varies depending on the platform (for example, .so is used for Unix and Unix-like systems, .dll is used for Windows).

Configure semi-synchronization:

Install both master and slave to prevent master-slave switching.

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

Copy after login

Note: If the installation fails as follows, you need to install libimf first.

mysql> INSTALLPLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
ERROR 1126(HY000): Can't open shared library
'/usr/local/mysql/lib/plugin/semisync_master.so'
(errno: 22libimf.so: cannot open shared object file:
No such fileor directory)

Copy after login

Check whether the component is loaded.

show plugins;
+----------------------------+----------+--------------------+--------------------+---------+
| Name                       | Status   | Type               | Library            | License |
+----------------------------+----------+--------------------+--------------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL               | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL               | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_TEMP_TABLE_INFO     | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_VIRTUAL         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL               | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| ngram                      | ACTIVE   | FTPARSER           | NULL               | GPL     |
| rpl_semi_sync_master       | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |
| rpl_semi_sync_slave        | ACTIVE   | REPLICATION        | semisync_slave.so  | GPL     |
+----------------------------+----------+--------------------+--------------------+---------+
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 after login

After installing the plug-in, you can view the parameters related to semi-synchronization.

show global variables like 'rpl_semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | OFF        |
| 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               | OFF        |
| rpl_semi_sync_slave_trace_level           | 32         |
+-------------------------------------------+------------+

Copy after login

Detailed explanation of parameters:

rpl_semi_sync_master_enabled: Whether the master library turns on semi-synchronization.

rpl_semi_sync_master_wait_point: Synchronization time point after_commit, after_sync.

rpl_semi_sync_master_wait_for_slave_count: The number of confirmations required from the slave database after the master database transaction is submitted.

rpl_semi_sync_master_timeout: The master library waits for the confirmation timeout from the slave library (ms), the default is 10000ms, if the wait exceeds, semi-sync will be downgraded to asynchronous mode.

rpl_semi_sync_master_wait_no_slave:

When it is OFF, as long as the main library finds that (Rpl_semi_sync_master_clients) is less than (rpl_semi_sync_master_wait_for_slave_count), the semi-sync will immediately switch to asynchronous mode;

When is ON, in During the idle time when no transaction is committed, even if the main library finds that (Rpl_semi_sync_master_clients) is less than (rpl_semi_sync_master_wait_for_slave_count), no adjustment will be made;

As long as it is guaranteed that the main library receives a value greater than or equal to (rpl_semi_sync_master_wait_for_slave_count) before the transaction times out If the number of ACK responses is exceeded, the main library will remain in semi-synchronous mode. If the transaction submission phase (main library waits for ACK) times out, semi-synchronous mode will be converted to asynchronous mode;

无论(rpl_semi_sync_master_wait_no_slave)为ON还是OFF，当slave上线到(rpl_semi_sync_master_wait_for_slave_count)值时，master都会自动由异步模式转为半同步模式。

主库开启半同步功能

SET GLOBAL rpl_semi_sync_master_enabled = 1; 
SET GLOBAL rpl_semi_sync_master_timeout=10000;

Copy after login

从库开启半同步功能

SET GLOBAL rpl_semi_sync_slave_enabled = 1;

Copy after login

从库对于正在进行的异步复制，需要重启sql_io_thread 线程,才能生效半同步复制

STOP SLAVE IO_THREAD; 
START SLAVE IO_THREAD;

Copy after login

主库添加参数到/etc/my.cnf文件

[mysqld] 
rpl_semi_sync_master_enabled=1 
rpl_semi_sync_master_timeout=10000

Copy after login

从库添加参数到/etc/my.cnf文件

[mysqld] 
rpl_semi_sync_slave_enabled=1

Copy after login

查看主从是否启用了半同步复制:

主库查询半同步状态Rpl_semi_sync_master_status为on表示主库开启了半同步复制，Rpl_semi_sync_master_clients表示目前有2个半同步的从库。

show global status like 'rpl%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 2     |
| 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                 | OFF   |
+--------------------------------------------+-------+

Copy after login

从库查询半同步状态Rpl_semi_sync_slave_status 为on表示从库开启了半同步复制。

show global status like 'rpl%';
+--------------------------------------------+-------+
| 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                | OFF   |
| 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    |
+--------------------------------------------+-------+

Copy after login

半同步状态检查:

show global status like 'rpl%';
+--------------------------------------------+---------+
| Variable_name                              | Value   |
+--------------------------------------------+---------+
| Rpl_semi_sync_master_clients               | 2       |
| Rpl_semi_sync_master_net_avg_wait_time     | 0       |
| Rpl_semi_sync_master_net_wait_time         | 0       |
| Rpl_semi_sync_master_net_waits             | 12463   |
| Rpl_semi_sync_master_no_times              | 1       |
| Rpl_semi_sync_master_no_tx                 | 2739    |
| Rpl_semi_sync_master_status                | ON      |
| Rpl_semi_sync_master_timefunc_failures     | 0       |
| Rpl_semi_sync_master_tx_avg_wait_time      | 1030    |
| Rpl_semi_sync_master_tx_wait_time          | 5075616 |
| Rpl_semi_sync_master_tx_waits              | 4926    |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0       |
| Rpl_semi_sync_master_wait_sessions         | 0       |
| Rpl_semi_sync_master_yes_tx                | 5595    |
| Rpl_semi_sync_slave_status                 | OFF     |
+--------------------------------------------+---------+

Copy after login

Rpl_semi_sync_master_net_avg_wait_time:变量已经废弃，总是等于0。

Rpl_semi_sync_master_net_wait_time:变量已经废弃，总是等于0。

Rpl_semi_sync_master_net_waits:主库等待从库的总次数。

Rpl_semi_sync_master_no_times:主库关闭半同步的总次数，可以用来分析半同步降级为异步的频率次数。

Rpl_semi_sync_master_no_tx:表示半同步事务提交没有接受到从库确认接受的总次数，可以用来分析半同步事务的提交是否存在超时的情况。

Rpl_semi_sync_master_yes_tx: 表示半同步事务提交接受到从库确认接受的总次数。

Rpl_semi_sync_master_timefunc_failures::调用 gettimeofday() 等时间函数失败的次数。

Rpl_semi_sync_master_tx_avg_wait_time:每个事务提交平均等待时间(单位: microseconds ),可以用来分析事务提交性能。

Rpl_semi_sync_master_tx_wait_time:事务总的提交等待时间(单位: microseconds ),可以用来分析事务提交性能。

Rpl_semi_sync_master_tx_waits:事务总的提交等待次数,可以用来分析事务提交性能。

Rpl_semi_sync_master_wait_pos_backtraverse:主库event binary位置低于之前等待event的总次数，当事务开始等待回复的顺序与其二进制日志事件的写入顺序不同时，就会发生这种情况。

Rpl_semi_sync_master_wait_sessions:当前会话等待从库确认接受的个数，可以用来确认分析会话是否存在由于等待从库确认而堵塞的情况。

推荐学习：mysql视频教程

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