![mysql GTID 复制](https://linux22.com/image/669846/mysql%20GTID%20%E5%A4%8D%E5%88%B6.png)
我尝试使用 GTID 和 backdump mysql -u root -h xx.xxx.xxx.xx -p < 2015-10-30_all_database.sql 输入密码:ERROR 1840 (HY000) 在第 24 行:仅当 @@GLOBAL.GTID_EXECUTED 为空时才能设置 @@GLOBAL.GTID_PURGED。
我检查了从服务器
显示全局变量如‘gtid_executed’;
| Variable_name | Value |
| gtid_executed | 19718ded-7eca-11e5-bece-00163e001216:1-2 |
显示全局变量如‘gtid_purged’;
| Variable_name | Value |
| gtid_purged | |
而主机显示像‘gtid_executed’这样的全局变量;
| Variable_name | Value |
| gtid_executed | |
显示全局变量如‘gtid_purged’->;
| Variable_name | Value |
| gtid_purged | |
我检查从属日志
#151030 16:12:52 server id 2 end_log_pos 219 CRC32 0xf7210a68 GTID last_committed=0 sequence_number=1
SET @@SESSION.GTID_NEXT= '19718ded-7eca-11e5-bece-00163e001216:1'/*!*/;
# at 219
#151030 16:12:52 server id 2 end_log_pos 366 CRC32 0xd6c11bfa Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1446192772/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE USER 'root'@'xxx.xxx.xxx.xxx' IDENTIFIED WITH 'mysql_native_password'
/*!*/;
# at 366
#151030 16:14:44 server id 2 end_log_pos 431 CRC32 0x0682aa9d GTID last_committed=1 sequence_number=2
SET @@SESSION.GTID_NEXT= '19718ded-7eca-11e5-bece-00163e001216:2'/*!*/;**
# at 431
#151030 16:14:44 server id 2 end_log_pos 564 CRC32 0x25289918 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1446192884/*!*/;
GRANT SUPER ON *.* TO 'root'@'xxx.xxx.xxx.xxx'
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
我创建用户并授予主主机根权限,这些生成了gtid_executed,但主服务器没有。如何对从属服务器执行2015-10-30_all_database.sql?然后使用GTID进行复制。
答案1
启用 GTID,然后在加载转储文件之前在从属上执行 RESET MASTER。