MySQL 8.0 Reference Manual（讀書筆記93節--Semisynchronous Replication）

In addition to the built-in asynchronous replication, MySQL 8.0 supports an interface to semisynchronous replication that is implemented by plugins. This section discusses what semisynchronous replication is and how it works. The following sections cover the administrative interface to semisynchronous replication and how to install, configure, and monitor it.

1.非同步replication（預設）的風險

MySQL replication by default is asynchronous. The source writes events to its binary log and replicas request them when they are ready. The source does not know whether or when a replica has retrieved and processed the transactions, and there is no guarantee that any event ever reaches any replica. With asynchronous replication, if the source crashes, transactions that it has committed might not have been transmitted to any replica. Failover from source to replica in this case might result in failover to a server that is missing transactions relative to the source.

2.全同步replication的壞處

With fully synchronous replication, when a source commits a transaction, all replicas have also committed the transaction before the source returns to the session that performed the transaction. Fully synchronous replication means failover from the source to any replica is possible at any time. The drawback of fully synchronous replication is that there might be a lot of delay to complete a transaction.

3.半同步

什麼是半同步

Semisynchronous replication falls between asynchronous and fully synchronous replication. The source waits until at least one replica has received and logged the events (the required number of replicas is configurable), and then commits the transaction. The source does not wait for all replicas to acknowledge receipt, and it requires only an acknowledgement from the replicas, not that the events have been fully executed and committed on the replica side. Semisynchronous replication therefore guarantees that if the source crashes, all the transactions that it has committed have been transmitted to at least one replica.

好處

Compared to asynchronous replication, semisynchronous replication provides improved data integrity, because when a commit returns successfully, it is known that the data exists in at least two places. Until a semisynchronous source receives acknowledgment from the required number of replicas, the transaction is on hold and not committed.

Compared to fully synchronous replication, semisynchronous replication is faster, because it can be configured to balance your requirements for data integrity (the number of replicas acknowledging receipt of the transaction) with the speed of commits, which are slower due to the need to wait for replicas.

【With semisynchronous replication, if the source crashes and a failover to a replica is carried out, the failed source should not be reused as the replication source, and should be discarded【dɪˈskɑːrdɪd 丟棄;拋棄;打出(無用的牌);墊(牌);】. It could have transactions that were not acknowledged【əkˈnɑːlɪdʒd 承認(權威、地位);告知收悉;】 by any replica, which were therefore not committed before the failover.

If your goal is to implement a fault-tolerant replication topology where all the servers receive the same transactions in the same order, and a server that crashes can rejoin the group and be brought up to date automatically, you can use Group Replication to achieve this.】

4.半同步的性能（損耗）如何？

The performance impact of semisynchronous replication compared to asynchronous replication is the tradeoff for increased data integrity. The amount of slowdown is at least the TCP/IP roundtrip time to send the commit to the replica and wait for the acknowledgment of receipt by the replica. This means that semisynchronous replication works best for close servers communicating over fast networks, and worst for distant servers communicating over slow networks. Semisynchronous replication also places a rate limit on busy sessions by constraining the speed at which binary log events can be sent from source to replica. When one user is too busy, this slows it down, which can be useful in some deployment situations.

5.半同步的operates

Semisynchronous replication between a source and its replicas operates as follows:

• A replica indicates whether it is semisynchronous-capable when it connects to the source.

• If semisynchronous replication is enabled on the source side and there is at least one semisynchronous replica, a thread that performs a transaction commit on the source blocks and waits until at least one semisynchronous replica acknowledges that it has received all events for the transaction, or until a timeout occurs.

• The replica acknowledges receipt【rɪˈsiːt 收據;接收;收到;(企業、銀行、政府等)收到的款項，收入;收條;】 of a transaction's events only after the events have been written to its relay log and flushed to disk.

• If a timeout occurs without any replica having acknowledged the transaction, the source reverts【rɪˈvɜːrts 恢復，回覆(到以前的狀態、制度或行為);回到，重提，重新考慮(某個話題);(財產、權力、金錢等)歸還，歸屬;】 to asynchronous replication. When at least one semisynchronous replica catches up, the source returns to semisynchronous replication.

• Semisynchronous replication must be enabled on both the source and replica sides. If semisynchronous replication is disabled on the source, or enabled on the source but on no replicas, the source uses asynchronous replication.

6.半同步的block機制

While the source is blocking (waiting for acknowledgment from a replica), it does not return to the session that performed the transaction. When the block ends, the source returns to the session, which then can proceed to execute other statements. At this point, the transaction has committed on the source side, and receipt of its events has been acknowledged by at least one replica. The number of replica acknowledgments the source must receive per transaction before returning to the session is configurable, and defaults to one acknowledgement.

Blocking also occurs after rollbacks that are written to the binary log, which occurs when a transaction that modifies nontransactional tables is rolled back. The rolled-back transaction is logged even though it has no effect for transactional tables because the modifications to the nontransactional tables cannot be rolled back and must be sent to replicas.

For statements that do not occur in transactional context (that is, when no transaction has been started with START TRANSACTION or SET autocommit = 0), autocommit is enabled and each statement commits implicitly. With semisynchronous replication, the source blocks for each such statement, just as it does for explicit transaction commits.

By default, the source waits for replica acknowledgment of the transaction receipt after syncing the binary log to disk, but before committing the transaction to the storage engine. As an alternative【[ɔːlˈtɜːrnətɪv 可供選擇的事物;】, you can configure the source so that the source waits for replica acknowledgment after committing the transaction to the storage engine, using the rpl_semi_sync_source_wait_point or rpl_semi_sync_master_wait_point system variable. This setting affects the replication characteristics【ˌkɛrəktəˈrɪstɪks 特征;特點;品質;】 and the data that clients can see on the source.

From MySQL 8.0.23, you can improve the performance of semisynchronous replication by enabling the system variables replication_sender_observe_commit_only, which limits callbacks, and replication_optimize_for_static_plugin_config, which adds shared locks and avoids unnecessary lock acquisitions. These settings help as the number of replicas increases, because contention for locks can slow down performance. Semisynchronous replication source servers can also get performance benefits from enabling these system variables, because they use the same locking mechanisms as the replicas.

7.Installing Semisynchronous Replication

Semisynchronous replication is implemented using plugins, which must be installed on the source and on the replicas to make semisynchronous replication available on the instances. There are different plugins for a source and for a replica. After a plugin has been installed, you control it by means of the system variables associated with it. These system variables are available only when the associated plugin has been installed.

7.1 半同步的環境要求

To use semisynchronous replication, the following requirements must be satisfied:

• The capability of installing plugins requires a MySQL server that supports dynamic loading. To verify this, check that the value of the have_dynamic_loading system variable is YES. Binary distributions should support dynamic loading.

• Replication must already be working.--沒有同步，何談半同步，這句話是這個意思。

• There must not be multiple replication channels configured. Semisynchronous replication is only compatible with the default replication channel.

7.2 半同步需要的plugins

From MySQL 8.0.26, new versions of the plugins that implement semisynchronous replication, one for the source server and one for the replica, are supplied. The new plugins replace the terms “master” and “slave” with “source” and “replica” in system variables and status variables, and you can install these versions instead of the old ones. You cannot have both the new and the old version of the relevant plugin installed on an instance. If you use the new version of the plugins, the new system variables and status variables are available but the old ones are not. If you use the old version of the plugins, the old system variables and status variables are available but the new ones are not.

The file name suffix for the plugin library files differs per platform (for example, .so for Unix and Unix-like systems, and .dll for Windows). The plugin and library file names are as follows:

• Source server, old terminology: rpl_semi_sync_master plugin (semisync_master.so or semisync_master.dll library)

• Source server, new terminology (from MySQL 8.0.26): rpl_semi_sync_source plugin (semisync_source.so or semisync_source.dll library)

• Replica, old terminology: rpl_semi_sync_slave plugin (semisync_slave.so or semisync_slave.dll library)

• Replica, new terminology (from MySQL 8.0.26): rpl_semi_sync_replica plugin (semisync_replica.so or semisync_replica.dll library)

To be usable by a source or replica server, the appropriate plugin library file must be located in the MySQL plugin directory (the directory named by the plugin_dir system variable). If necessary, configure the plugin directory location by setting the value of plugin_dir at server startup. The source plugin library file must be present in the plugin directory of the source server. The replica plugin library file must be present in the plugin directory of each replica server.

7.3 set up

To set up semisynchronous replication, use the following instructions. The INSTALL PLUGIN, SET GLOBAL, STOP REPLICA, and START REPLICA statements mentioned here require the REPLICATION_SLAVE_ADMIN privilege (or the deprecated SUPER privilege).

To load the plugins, use the INSTALL PLUGIN statement on the source and on each replica that is to be semisynchronous, adjusting the .so suffix for your platform as necessary.

On the source:

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Or from MySQL 8.0.26:
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';

On each replica:

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Or from MySQL 8.0.26:
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';

If an attempt to install a plugin results in an error on Linux similar to that shown here, you must install libimf:

mysql> INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
ERROR 1126 (HY000): Can't open shared library
'/usr/local/mysql/lib/plugin/semisync_source.so'
(errno: 22 libimf.so: cannot open shared object file:
No such file or directory)

安裝確認

To verify plugin installation, examine the Information Schema PLUGINS table or use the SHOW PLUGINS statement.For example:

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
 FROM INFORMATION_SCHEMA.PLUGINS
 WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_source | ACTIVE        |
+----------------------+---------------+

If a plugin fails to initialize, check the server error log for diagnostic messages.

7.4 enabled plugins

After a semisynchronous replication plugin has been installed, it is disabled by default. The plugins must be enabled both on the source side and the replica side to enable semisynchronous replication. If only one side is enabled, replication is asynchronous. To enable the plugins, set the appropriate system variable either at runtime using SET GLOBAL, or at server startup on the command line or in an option file. For example:

On the source:

SET GLOBAL rpl_semi_sync_master_enabled = 1;
Or from MySQL 8.0.26 with the rpl_semi_sync_source plugin:
SET GLOBAL rpl_semi_sync_source_enabled = 1;

On each replica:

SET GLOBAL rpl_semi_sync_slave_enabled = 1;
Or from MySQL 8.0.26 with the rpl_semi_sync_replica plugin:
SET GLOBAL rpl_semi_sync_replica_enabled = 1;

7.5 enable semisynchronous replication on a replica at runtime

If you enable semisynchronous replication on a replica at runtime, you must also start the replication I/O (receiver) thread (stopping it first if it is already running) to cause the replica to connect to the source and register as a semisynchronous replica:

STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;
Or from MySQL 8.0.22:
STOP REPLICA IO_THREAD;
START REPLICA IO_THREAD;

If the replication I/O (receiver) thread is already running and you do not restart it, the replica continues to use asynchronous replication. --特別強調 要重啟

7.6 listed in an option file

A setting listed in an option file takes effect each time the server starts. For example, you can set the variables in my.cnf files on the source and replica servers as follows:

On the source:

[mysqld]
rpl_semi_sync_master_enabled=1
Or from MySQL 8.0.26 with the rpl_semi_sync_source plugin:
rpl_semi_sync_source_enabled=1

On each replica:

[mysqld]
rpl_semi_sync_slave_enabled=1
Or from MySQL 8.0.26 with the rpl_semi_sync_source plugin:
rpl_semi_sync_replica_enabled=1

You can configure the behavior of the semisynchronous replication plugins using the system variables that become available when you install the plugins.

8. Configuring Semisynchronous Replication

When you install the source and replica plugins for semisynchronous replication, system variables become available to control plugin behavior.

To check the current values of the status variables for semisynchronous replication, use SHOW VARIABLES:

mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';

From MySQL 8.0.26, new versions of the source and replica plugins are supplied, which replace the terms “master” and “slave” with “source” and “replica” in system variables and status variables. If you install the new rpl_semi_sync_source and rpl_semi_sync_replica plugins, the new system variables and status variables are available but the old ones are not. If you install the old rpl_semi_sync_master and rpl_semi_sync_slave plugins, the old system variables and status variables are available but the new ones are not. You cannot have both the new and the old version of the relevant plugin installed on an instance.

Some key system variables are:

The rpl_semi_sync_source_wait_point or rpl_semi_sync_master_wait_point system variable controls the point at which a semisynchronous source server waits for replica acknowledgment of transaction receipt before returning a status to the client that committed the transaction. These values are permitted:

• AFTER_SYNC (the default): The source writes each transaction to its binary log and the replica, and syncs the binary log to disk. The source waits for replica acknowledgment of transaction receipt after the sync. Upon receiving acknowledgment, the source commits the transaction to the storage engine and returns a result to the client, which then can proceed.

• AFTER_COMMIT: The source writes each transaction to its binary log and the replica, syncs the binary log, and commits the transaction to the storage engine. The source waits for replica acknowledgment of transaction receipt after the commit. Upon receiving acknowledgment, the source returns a result to the client, which then can proceed.

The replication characteristics of these settings differ as follows:

• With AFTER_SYNC, all clients see the committed transaction at the same time, which is after it has been acknowledged by the replica and committed to the storage engine on the source. Thus, all clients see the same data on the source.

In the event of source failure, all transactions committed on the source have been replicated to the replica (saved to its relay log). An unexpected exit of the source and failover to the replica is lossless because the replica is up to date. As noted above, the source should not be reused after the failover.

• With AFTER_COMMIT, the client issuing the transaction gets a return status only after the server commits to the storage engine and receives replica acknowledgment. After the commit and before replica acknowledgment, other clients can see the committed transaction before the committing client.

If something goes wrong such that the replica does not process the transaction, then in the event of an unexpected source exit and failover to the replica, it is possible for such clients to see a loss of data relative to what they saw on the source.

From MySQL 8.0.23, you can improve the performance of semisynchronous replication by enabling the system variables replication_sender_observe_commit_only, which limits callbacks, and replication_optimize_for_static_plugin_config, which adds shared locks and avoids unnecessary lock acquisitions. These settings help as the number of replicas increases, because contention for locks can slow down performance. Semisynchronous replication source servers can also get performance benefits from enabling these system variables, because they use the same locking mechanisms as the replicas.

9.Semisynchronous Replication Monitoring

 The plugins for semisynchronous replication expose【ɪkˈspoʊz 暴露;露出;使接觸;曝光;揭露;使面臨，使遭受(危險或不快);顯露;揭穿;】 a number of status variables that enable you to monitor their operation. To check the current values of the status variables, use SHOW STATUS:

mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';

Some examples are:

• Rpl_semi_sync_source_clients or Rpl_semi_sync_master_clients

The number of semisynchronous replicas that are connected to the source server.

• Rpl_semi_sync_source_status or Rpl_semi_sync_master_status

Whether semisynchronous replication currently is operational【ˌɑːpəˈreɪʃənl 操作的;業務的;運營的;運轉的;可使用;】 on the source server. The value is 1 if the plugin has been enabled and a commit acknowledgment has not occurred. It is 0 if the plugin is not enabled or the source has fallen back to asynchronous replication due to commit acknowledgment timeout.

• Rpl_semi_sync_source_no_tx or Rpl_semi_sync_master_no_tx

The number of commits that were not acknowledged successfully by a replica.

• Rpl_semi_sync_source_yes_tx or Rpl_semi_sync_master_yes_tx

The number of commits that were acknowledged successfully by a replica.

• Rpl_semi_sync_replica_status or Rpl_semi_sync_slave_status

Whether semisynchronous replication currently is operational on the replica. This is 1 if the plugin has been enabled and the replication I/O (receiver) thread is running, 0 otherwise.

When the source switches between asynchronous or semisynchronous replication due to commit-blocking timeout or a replica catching up, it sets the value of the Rpl_semi_sync_source_status or Rpl_semi_sync_master_status status variable appropriately【ə'proprɪrtlɪ 合適的；適當地；相稱地;】. Automatic fallback from semisynchronous to asynchronous replication on the source means that it is possible for the rpl_semi_sync_source_enabled or rpl_semi_sync_master_enabled system variable to have a value of 1 on the source side even when semisynchronous replication is in fact not operational at the moment. You can monitor the Rpl_semi_sync_source_status or Rpl_semi_sync_master_status status variable to determine whether the source currently is using asynchronous or semisynchronous replication.

 10.MySQL 8.0 初始化時，my.cnf 不可有plugin_load_add

MySQL在部署安裝時，my.cnf 不可有plugin_load_add，否則初始化不完整，MySQL服務無法正常啟動。

例如，為了實現半同步，在my.cnf 配置中添加了以下參數

plugin_load_add                        = semisync_source.so           
plugin_load_add                        = semisync_replica.so
rpl_semi_sync_source_enabled           = 1
rpl_semi_sync_replica_enabled          = 1

初始化

cd /usr/local/mysql
./bin/mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data --initialize-insecure

啟動服務時報錯，主要的報錯信息如下：

XXXXXX 0 [Warning] [MY-013501] [Server] Ignoring --plugin-load[_add] list as the server is running with --initialize(-insecure).
XXXXXX 0 [ERROR] [MY-000067] [Server] unknown variable 'rpl_semi_sync_source_enabled=1'.
mysqld: Table 'mysql.plugin' doesn't exist
XXXXXX 0 [ERROR] [MY-010735] [Server] Could not open the mysql.plugin table. Please perform the MySQL upgrade procedure.

 XXXXXX 0 [ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001146 - Table 'mysql.component' doesn't exist
 XXXXXX 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-003543 - The mysql.component table is missing or has an incorrect definition.
 XXXXXX 0 [ERROR] [MY-010326] [Server] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
 XXXXXX 0 [ERROR] [MY-010952] [Server] The privilege system failed to initialize correctly. For complete instructions on how to upgrade MySQL to a new version please see the 'Upgrading MySQL' section from the MySQL manual.

所以，應該先初始化，然後install plugin，最後考慮enable。