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

1.

The following options also have an impact on the source:

• For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should use innodb_flush_log_at_trx_commit=1 and sync_binlog=1 in the source's my.cnf file.

• Ensure that the skip_networking system variable is not enabled on the source. If networking has been disabled, the replica cannot communicate with the source and replication fails.

2.The default server_id value is 1.

The default server_id value from MySQL 8.0 is 1.Note that if a value of 0 (which was the default in earlier releases) was set previously for the server ID, you must restart the server to initialize the source with your new nonzero server ID.

Note that a value of 0 for the server ID prevents a replica from connecting to a source. If that server ID value (which was the default in earlier releases) was set previously, you must restart the server to initialize the replica with your new nonzero server ID. Otherwise【ˈʌðərwaɪz 否則;另;不然;在其他方面;亦;除此以外;】, a server restart is not needed when you change the server ID, unless you make other configuration changes that require it. For example, if binary logging was disabled on the server and you want it enabled for your replica, a server restart is required to enable this.

3.級聯複製

Binary logging is enabled by default on all servers. A replica is not required to have binary logging enabled for replication to take place. However, binary logging on a replica means that the replica's binary log can be used for data backups and crash recovery. Replicas that have binary logging enabled can also be used as part of a more complex replication topology. For example, you might want to set up replication servers using this chained arrangement:

A -> B -> C

Here, A serves as the source for the replica B, and B serves as the source for the replica C. For this to work, B must be both a source and a replica. Updates received from A must be logged by B to its binary log, in order to be passed on to C. In addition to binary logging, this replication topology requires the system variable log_replica_updates (from MySQL 8.0.26) or log_slave_updates (before MySQL 8.0.26) to be enabled. With replica updates enabled, the replica writes updates that are received from a source and performed by the replica's SQL thread to the replica's own binary log. The log_replica_updates or log_slave_updates system variable is enabled by default.

If you need to disable binary logging or replica update logging on a replica, you can do this by specifying the --skip-log-bin and --log-replica-updates=OFF or --log-slave-updates=OFF options for the replica. If you decide to re-enable these features on the replica, remove the relevant options and restart the server.

4.Creating a Data Snapshot Using mysqldump

By default, if GTIDs are in use on the source (gtid_mode=ON), mysqldump includes the GTIDs from the gtid_executed set on the source in the dump output to add them to the gtid_purged set on the replica. If you are dumping only specific databases or tables, it is important to note that the value that is included by mysqldump includes the GTIDs of all transactions in the gtid_executed set on the source, even those that changed suppressed【səˈprest 被抑制的；(病)癥狀不顯明的；被刪去的;】 parts of the database, or other databases on the server that were not included in the partial【ˈpɑːrʃl 部分的，不完全的;<數>偏的;偏向一方的，偏袒的，不公平的;偏愛的，癖好的;】 dump. Check the description for mysqldump's --set-gtid-purged option to find the outcome of the default behavior for the MySQL Server versions you are using, and how to change the behavior if this outcome is not suitable for your situation.

/usr/local/mysql/bin/mysqldump --master-data=2 -u用戶 -p密碼 --databases 指定資料庫 --set-gtid-purged=off --single-transaction -R --triggers > /指定路徑/tmp_xxxxx.sql

 否則，會遇到如下錯誤

ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

 5.註意scheduled events

If the replication source server or existing replica that you are copying to create the new replica has any scheduled events, ensure that these are disabled on the new replica before you start it. If an event runs on the new replica that has already run on the source, the duplicated operation causes an error. The Event Scheduler is controlled by the event_scheduler system variable, which defaults to ON from MySQL 8.0, so events that are active on the original server run by default when the new replica starts up. To stop all events from running on the new replica, set the event_scheduler system variable to OFF or DISABLED on the new replica. Alternatively, you can use the ALTER EVENT statement to set individual events to DISABLE or DISABLE ON SLAVE to prevent them from running on the new replica. You can list the events on a server using the SHOW statement or the Information Schema EVENTS table.

6.

GTID assignment distinguishes between client transactions, which are committed on the source, and replicated transactions, which are reproduced on a replica. When a client transaction is committed on the source, it is assigned a new GTID, provided that the transaction was written to the binary log. Client transactions are guaranteed【ɡærənˈtiːd 保證;保障;擔保;確保;使必然發生;提供(產品)保修單(免費掉換或修理有問題的產品);】 to have monotonically【mɒnə'tɒnɪklɪ 單調地;單調地，無變化地;】 increasing GTIDs without gaps between the generated numbers. If a client transaction is not written to the binary log (for example, because the transaction was filtered out, or the transaction was read-only), it is not assigned a GTID on the server of origin.

Replicated transactions retain the same GTID that was assigned to the transaction on the server of origin. The GTID is present before the replicated transaction begins to execute, and is persisted【pərˈsɪstɪd 持續存在；保持；維持；頑強地堅持；執著地做】 even if the replicated transaction is not written to the binary log on the replica, or is filtered out on the replica. The MySQL system table mysql.gtid_executed is used to preserve【prɪˈzɜːrv 保存;保護;保留;維護;保鮮;保養;貯存;維持…的原狀;使繼續存活;】 the assigned GTIDs of all the transactions applied on a MySQL server, except those that are stored in a currently active binary log file.

只會執行一次

The auto-skip function for GTIDs means that a transaction committed on the source can be applied no more than once on the replica, which helps to guarantee consistency. Once a transaction with a given GTID has been committed on a given server, any attempt to execute a subsequent【ˈsʌbsɪkwənt 隨後的;之後的;後來的;接後的;】 transaction with the same GTID is ignored by that server. No error is raised, and no statement in the transaction is executed.

7.

If a transaction with a given GTID has started to execute on a server, but has not yet committed or rolled back, any attempt to start a concurrent【kənˈkɜːrənt 同時發生的;同意的，一致的;<律>有相等權力的，同時(實施)的;合作的;協調的;並存的;<數>共點的，會合的;共同(或同時)起作用的;】 transaction on the server with the same GTID blocks. The server neither begins to execute the concurrent transaction nor returns control to the client. Once the first attempt at the transaction commits or rolls back, concurrent sessions that were blocking on the same GTID may proceed. If the first attempt rolled back, one concurrent session proceeds【proʊˈsiːdz , ˈproʊsiːdz 行進;前往;繼續做(或從事、進行);接著做;繼而做;】 to attempt the transaction,and any other concurrent sessions that were blocking on the same GTID remain blocked. If the first attempt committed, all the concurrent sessions stop being blocked, and auto-skip all the statements of the transaction.

 8.GTID Sets

A GTID set is a set comprising【kəmˈpraɪzɪŋ 包括;包含;組成;構成;由…組成;是(某事物的)組成部分;】 one or more single GTIDs or ranges of GTIDs. GTID sets are used in a MySQL server in several ways. For example, the values stored by the gtid_executed and gtid_purged system variables are GTID sets. The START REPLICA (or before MySQL 8.0.22, START SLAVE) clauses UNTIL SQL_BEFORE_GTIDS and UNTIL SQL_AFTER_GTIDS can be used to make a replica process transactions only up to the first GTID in a GTID set, or stop after the last GTID in a GTID set. The built-in functions GTID_SUBSET() and GTID_SUBTRACT() require GTID sets as input.

A range of GTIDs originating from the same server can be collapsed into a single expression, as shown here:

3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5

The above example represents the first through fifth transactions originating【əˈrɪdʒɪneɪtɪŋ 起源;創建;發明;髮端於;發源;創立;】 on the MySQL server whose server_uuid is 3E11FA47-71CA-11E1-9E33-C80AA9429562. Multiple single GTIDs or ranges of GTIDs originating from the same server can also be included in a single expression, with the GTIDs or ranges separated by colons, as in the following example:

3E11FA47-71CA-11E1-9E33-C80AA9429562:1-3:11:47-49

A GTID set can include any combination of single GTIDs and ranges of GTIDs, and it can include GTIDs originating from different servers. This example shows the GTID set stored in the gtid_executed system variable (@@GLOBAL.gtid_executed) of a replica that has applied transactions from more than one source:

2174B383-5441-11E8-B90A-C80AA9429562:1-3, 24DA167-0C0C-11E8-8442-00059A3C7B00:1-19

When GTID sets are returned from server variables, UUIDs are in alphabetical【ˌælfəˈbetɪkl 按字母順序排列的;按字母(表)順序的;】 order, and numeric intervals are merged and in ascending order.