[root@MiWiFi-R1CL-srv backup]# mysql -uroot -pbgx -e "source /root/backup/db-2018-08-24-all.sql"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1840 (HY000) at line 24 in file: '/root/backup/db-2018-08-24-all.sql': @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
mysqldump -uroot -pbgx --all-databases --single-transaction --master-data=1 --flush-logs > /root/backup/db-$(date +%F)-all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
查看结果:
总用量 788
-rw-r--r--. 1 root root 804118 8月 24 11:55 db-2018-08-24-all.sql
3、参照第一点的第5步。
4、开始执行第 二点的第4步。
以上错误还是存在。。。为什么?
ERROR 1776 (HY000) at line 30 in file: '/root/backup/db-2018-08-24-all.sql': Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active.
[root@MiWiFi-R1CL-srv mysql]# mysql -uroot -pbgx -e "source /root/backup/db-2018-08-24-all.sql"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1840 (HY000) at line 24 in file: '/root/backup/db-2018-08-24-all.sql': @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
检查错误:
查看master1中的数据库master 状态
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
并么有reset master成功。
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
再次执行一次reset master 后再导出数据。
mysqldump -uroot -pbgx --all-databases --single-transaction --master-data=1 --flush-logs > /root/backup/db-$(date +%F)-all.sql
scp db-2018-08-24-all.sql root@slave:/root/backup/
在master2的服务器上面,查看master状态:
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
导入数据;
mysql -uroot -pbgx -e "source /root/backup/db-2018-08-24-all.sql"
[root@MiWiFi-R1CL-srv data]# mysql -uroot -pbgx -e "source /root/backup/db-2018-08-24-all.sql"
mysql: [Warning] Using a password on the command line interface can be insecure.
此步骤执行成功。。
--------------- 5、清理从主库的二进制日志
mysql> reset master;
Query OK, 0 rows affected (0.00 sec)