- 导出cidev表结构
mysqldump -uroot -p123456 -d cidev> cidev.sql
- 导出cidev表结构及数据并压缩
mysqldump --single-transaction -q --max_allowed_packet=1073741824 --lock-tables=false -uroot -p123456 cidev|gzip > cidev.sql.gz
- 还原
//解压gz文件
gunzip -c cidev.sql.gz > cidev.sql
//重建数据库cidev
drop database cidev;
create database cidev DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
grant ALL PRIVILEGES on cidev.* to cidev@"%" identified by 'cidevfast' ;
create database confluence DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
grant ALL PRIVILEGES on confluence.* to confluence@"%" identified by '123456' ;
flush tables;
- ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty
今天在做库迁移的时候,因为涉及从集群迁移到单实例,遇到一些问题,记录了解决过程
ERROR 1840 (HY000) at line 24 in file: ‘sql/cidev.sql’: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
当前GTID_EXECUTED参数已经有值,而从集群倒出来的dump文件中包含了SET @@GLOBAL.GTID_PURGED的操作
解决方法:
方法一:reset master
这个操作可以将当前库的GTID_EXECUTED值置空
方法二:–set-gtid-purged=off
在dump导出时,添加–set-gtid-purged=off参数,避免将gtid信息导出
mysqldump -uroot -p --set-gtid-purged=off -d cidev> cidev.sql
评论区