For GTID-based replication, the recovery process needs the GTIDs of the transactions that were already received or committed by the replica. The missing transactions can be retrieved from the source using GTID auto-positioning, which automatically compares the source's transactions to the replica's transactions and identifies the missing transactions.

For file position based replication, the recovery process needs an accurate replication SQL thread (applier) position showing the last transaction that was applied on the replica. Based on that position, the replication I/O thread (receiver) retrieves from the source's binary log all of the transactions that should be applied on the replica from that point on.

When GTID-based replication is in use (gtid_mode=ON), setSOURCE_AUTO_POSITION=1|MASTER_AUTO_POSITION=1, which activates GTID auto-positioning for the connection to the source to automatically identify and retrieve missing transactions. This option is set using aCHANGE REPLICATION SOURCE TOstatement (from MySQL 8.0.23) orCHANGE MASTER TOstatement (before MySQL 8.0.23). If the replica has multiple replication channels, you need to set this option for each channel individually. For details of how GTID auto-positioning works, seeSection 17.1.3.3, “GTID Auto-Positioning”。When file position based replication is in use,SOURCE_AUTO_POSITION=1|MASTER_AUTO_POSITION=1is not used, and instead the binary log position or relay log position is used to control where replication starts.

From MySQL 8.0.27, when GTID-based replication is in use (gtid_mode=ON), setGTID_ONLY=1, which makes the replica use only GTIDs in the recovery process, and stop persisting binary log and relay log file names and file positions in the replication metadata repositories. This option is set using aCHANGE REPLICATION SOURCE TOstatement. If the replica has multiple replication channels, you need to set this option for each channel individually. WithGTID_ONLY=1, during recovery, the file position information is ignored and GTID auto-skip is used to skip transactions that have already been supplied, rather than identifying the correct file position. This strategy is more efficient provided that you purge relay logs using the default setting forrelay_log_purge, which means only one relay log file needs to be inspected.

Setsync_relay_log=1, which instructs the replication receiver thread to synchronize the relay log to disk after each received transaction is written to it. This means the replica's record of the current position read from the source's binary log (in the applier metadata repository) is never ahead of the record of transactions saved in the relay log. Note that although this setting is the safest, it is also the slowest due to the number of disk writes involved. Withsync_relay_log > 1, orsync_relay_log=0(where synchronization is handled by the operating system), in the event of an unexpected halt of a replica there might be committed transactions that have not been synchronized to disk. Such transactions can cause the recovery process to fail if the recovering replica, based on the information it has in the relay log as last synchronized to disk, tries to retrieve and apply the transactions again instead of skipping them. Settingsync_relay_log=1是尤其重要的,因为一个多线程代表lica, where the recovery process fails if gaps in the sequence of transactions cannot be filled using the information in the relay log. For a single-threaded replica, the recovery process only needs to use the relay log if the relevant information is not available in the applier metadata repository.

Setinnodb_flush_log_at_trx_commit=1, which synchronizes theInnoDBlogs to disk before each transaction is committed. This setting, which is the default, ensures thatInnoDBtables and theInnoDBlogs are saved on disk so that there is no longer a requirement for the information in the relay log regarding the transaction. Combined with the settingsync_relay_log=1, this setting further ensures that the content of theInnoDBtables and theInnoDBlogs is consistent with the content of the relay log at all times, so that purging the relay log files cannot cause unfillable gaps in the replica's history of transactions in the event of an unexpected halt.

Setrelay_log_info_repository = TABLE, which stores the replication SQL thread position in theInnoDBtablemysql.slave_relay_log_info, and updates it together with the transaction commit to ensure a record that is always accurate. This setting is the default from MySQL 8.0, and theFILEsetting is deprecated. From MySQL 8.0.23, the use of the system variable itself is deprecated, so omit it and allow it to default. If theFILEsetting is used, which was the default in earlier releases, the information is stored in a file in the data directory that is updated after the transaction has been applied. This creates a risk of losing synchrony with the source depending at which stage of processing a transaction the replica halts at, or even corruption of the file itself. With the settingrelay_log_info_repository = FILE, recovery is not guaranteed.

Setrelay_log_recovery = ON, which enables automatic relay log recovery immediately following server startup. This global variable defaults toOFFand is read-only at runtime, but you can set it toONwith the--relay-log-recoveryoption at replica startup following an unexpected halt of a replica. Note that this setting ignores the existing relay log files, in case they are corrupted or inconsistent. The relay log recovery process starts a new relay log file and fetches transactions from the source beginning at the replication SQL thread position recorded in the applier metadata repository. The previous relay log files are removed over time by the replica's normal purge mechanism.