mysql> select * from mysql.gtid_executed;<br>+--------------------------------------+----------------+--------------+<br>| source_uuid | interval_start | interval_end |<br>+--------------------------------------+----------------+--------------+<br>| 2cbdc21a-db11-11ec-83bf-020017003dc4 | 1 | 2124 |<br>+--------------------------------------+----------------+--------------+<br>1 row in set (0.00 sec)<br><br>mysql> show global variables where variable_name in ('gtid_executed', 'gtid_purged');<br>+---------------+---------------------------------------------+<br>| Variable_name | Value |<br>+---------------+---------------------------------------------+<br>| gtid_executed | 2cbdc21a-db11-11ec-83bf-020017003dc4:1-2124 |<br>| gtid_purged | 2cbdc21a-db11-11ec-83bf-020017003dc4:1-2124 |<br>+---------------+---------------------------------------------+<br>2 rows in set (0.00 sec)<br>
mysql> set global gtid_executed='411693c9-d512-11ec-9e11-525400d51a16:1-10369';<br>ERROR 1238 (HY000): Variable 'gtid_executed' is a read only variable<br>
mysql> set global gtid_purged='411693c9-d512-11ec-9e11-525400d51a16:1-10369';<br>ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.<br>
复制代码
而要 GTID_EXECUTED 为空,只能执行 RESET MASTER 操作。
mysql> reset master;<br>Query OK, 0 rows affected (0.02 sec)<br><br>mysql> show global variables where variable_name in ('gtid_executed', 'gtid_purged');<br>+---------------+-------+<br>| Variable_name | Value |<br>+---------------+-------+<br>| gtid_executed | |<br>| gtid_purged | |<br>+---------------+-------+<br>2 rows in set (0.00 sec)<br><br>mysql> set global gtid_purged='411693c9-d512-11ec-9e11-525400d51a16:1-10369';<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> show global variables where variable_name in ('gtid_executed', 'gtid_purged');<br>+---------------+----------------------------------------------+<br>| Variable_name | Value |<br>+---------------+----------------------------------------------+<br>| gtid_executed | 411693c9-d512-11ec-9e11-525400d51a16:1-10369 |<br>| gtid_purged | 411693c9-d512-11ec-9e11-525400d51a16:1-10369 |<br>+---------------+----------------------------------------------+<br>2 rows in set (0.00 sec)<br>
mysql> show global variables where variable_name in ('gtid_executed', 'gtid_purged');<br>+---------------+------------------------------------------+<br>| Variable_name | Value |<br>+---------------+------------------------------------------+<br>| gtid_executed | a028d418-ccce-11ec-bf07-525400d51a16:1-8 |<br>| gtid_purged | a028d418-ccce-11ec-bf07-525400d51a16:1-4 |<br>+---------------+------------------------------------------+<br>2 rows in set (0.00 sec)<br><br>mysql> select gtid_subtract(@@gtid_executed, @@gtid_purged);<br>+-----------------------------------------------+<br>| gtid_subtract(@@gtid_executed, @@gtid_purged) |<br>+-----------------------------------------------+<br>| a028d418-ccce-11ec-bf07-525400d51a16:5-8 |<br>+-----------------------------------------------+<br>1 row in set (0.00 sec)<br><br>mysql> set global gtid_purged='a028d418-ccce-11ec-bf07-525400d51a16:1-5';<br>ERROR 3546 (HY000): @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED<br>
复制代码
2. 设置的 GTID_PURGED 必须是当前 GTID_PURGED 的超集。
mysql> show global variables where variable_name in ('gtid_executed', 'gtid_purged');<br>+---------------+------------------------------------------+<br>| Variable_name | Value |<br>+---------------+------------------------------------------+<br>| gtid_executed | a028d418-ccce-11ec-bf07-525400d51a16:1-8 |<br>| gtid_purged | a028d418-ccce-11ec-bf07-525400d51a16:1-4 |<br>+---------------+------------------------------------------+<br>2 rows in set (0.00 sec)<br><br>mysql> set global gtid_purged='a028d418-ccce-11ec-bf07-525400d51a16:1-3';<br>ERROR 3546 (HY000): @@GLOBAL.GTID_PURGED cannot be changed: the new value must be a superset of the old value<br><br>mysql> set global gtid_purged='a028d418-ccce-11ec-bf07-525400d51a16:1-4,9b481834-de85-11ec-9045-020017003dc4:1-10';<br>Query OK, 0 rows affected (0.01 sec)<br><br>mysql> show global variables where variable_name in ('gtid_executed', 'gtid_purged')\G<br>*************************** 1. row ***************************<br>Variable_name: gtid_executed<br> Value: 9b481834-de85-11ec-9045-020017003dc4:1-10,<br>a028d418-ccce-11ec-bf07-525400d51a16:1-8<br>*************************** 2. row ***************************<br>Variable_name: gtid_purged<br> Value: 9b481834-de85-11ec-9045-020017003dc4:1-10,<br>a028d418-ccce-11ec-bf07-525400d51a16:1-4<br>2 rows in set (0.00 sec)<br>
# mysqlbinlog -v --base64-output=decode-rows --stop-position=507 mysql-bin.000024<br># The proper term is pseudo_replica_mode, but we use this compatibility alias<br># to make the statement usable on server versions 8.0.24 and older.<br>/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;<br>/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;<br>DELIMITER /*!*/;<br># at 4<br>#220529 11:19:07 server id 1 end_log_pos 126 CRC32 0xdcc54ec7 Start: binlog v 4, server v 8.0.28 created 220529 11:19:07<br># at 126<br>#220529 11:19:07 server id 1 end_log_pos 197 CRC32 0x5d440f7c Previous-GTIDs<br># d310871c-db0c-11ec-a557-020017003dc4:1-388482<br># at 197<br>#220529 11:19:07 server id 1 end_log_pos 276 CRC32 0x0dd893b5 GTID last_committed=0 sequence_number=1 rbr_only=yes original_committed_timestamp=1653823147539722 immediate_commit_timestamp=1653823147539722 transaction_length=310<br>/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;<br># original_commit_timestamp=1653823147539722 (2022-05-29 11:19:07.539722 GMT)<br># immediate_commit_timestamp=1653823147539722 (2022-05-29 11:19:07.539722 GMT)<br>/*!80001 SET @@session.original_commit_timestamp=1653823147539722*//*!*/;<br>/*!80014 SET @@session.original_server_version=80028*//*!*/;<br>/*!80014 SET @@session.immediate_server_version=80028*//*!*/;<br>SET @@SESSION.GTID_NEXT= 'd310871c-db0c-11ec-a557-020017003dc4:388483'/*!*/;<br># at 276<br>#220529 11:19:07 server id 1 end_log_pos 365 CRC32 0xa49dc290 Query thread_id=262 exec_time=0 error_code=0<br>...<br>BEGIN<br>/*!*/;<br># at 365<br>#220529 11:19:07 server id 1 end_log_pos 425 CRC32 0x824f6309 Table_map: `slowtech`.`t1` mapped to number 157<br># at 425<br>#220529 11:19:07 server id 1 end_log_pos 476 CRC32 0x5a6fe6ec Write_rows: table id 157 flags: STMT_END_F<br>### INSERT INTO `slowtech`.`t1`<br>### SET<br>### @1=1483132<br>### @2='aaaaaaaaaa'<br># at 476<br>#220529 11:19:07 server id 1 end_log_pos 507 CRC32 0x66a401f6 Xid = 4108904<br>COMMIT/*!*/;<br>SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;<br>DELIMITER ;<br># End of log file<br>/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;<br>/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;<br>
mysql> show global variables where variable_name in ('gtid_executed', 'gtid_purged');<br>+---------------+-----------------------------------------------+<br>| Variable_name | Value |<br>+---------------+-----------------------------------------------+<br>| gtid_executed | d310871c-db0c-11ec-a557-020017003dc4:1-388483 |<br>| gtid_purged | d310871c-db0c-11ec-a557-020017003dc4:1-388482 |<br>+---------------+-----------------------------------------------+<br>2 rows in set (0.00 sec)<br>
复制代码
所以,实例起来后,我们看到的 GTID_EXECUTED 就已经是正确值,就已经能正确反映备份结束时的一致性位置点信息了。
这个时候,直接执行 CHANGE MASTER TO 操作就可以了。
The log_status table provides information that enables an online backup tool to copy the required log files without locking those resources for the duration of the copy process.<br><br>When the log_status table is queried, the server blocks logging and related administrative changes for just long enough to populate the table, then releases the resources. <br><br>The log_status table informs the online backup which point it should copy up to in the source's binary log and gtid_executed record, and the relay log for each replication channel. <br><br>It also provides relevant information for individual storage engines, such as the last log sequence number (LSN) and the LSN of the last checkpoint taken for the InnoDB storage engine.<br>
复制代码
XtraBackup 8.0 中哪些场景会加全局读锁
下面两种场景,XtraBackup 8.0 会加全局读锁:
备份实例中存在 MyISAM 表。
备份从库,且命令行中指定了 --slave-info,且从库 SHOW SLAVE STATUS 中的 Auto_Position 不为 1。
Auto_Position 不为 1 意味着从库没有开启 GTID 复制,或者开启了 GTID 复制,但未将 MASTER_AUTO_POSITION 设置为 1。