mysql双主重制主从关系

主机信息

序号	ip
1	192.168.198.100
–	–
2	192.168.198.200

关闭slave

mysql>stop slave; #两台都执行

锁住100的所有表

mysql> flush tables with read lock;

200的库全删除

drop database XXX

关闭mysql

清空binlog日志和master.info以及reley.log
启动200上的mysql

全量备份100上库

mysqldump xxxxxxxxx

将备份的数据库到200上恢复

恢复完成后记得执行下， flush privileges;

配置从库信息

100上执行 
mysql > reset slave all;
mysql > show master status\G;
+----------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+------------------------------------------+
| mysql33-bin.000001 | 1094 | | | 11fee432-c693-11ea-a6c7-005052a822ed:1-2 |
+----------------------+----------+--------------+------------------+------------------------------------------+
200上执行
mysql> reset master;
mysql> change master to master_host='192.168.1.100',master_port=xxxx,master_user='xxl',master_password='xxxxx',master_log_file='mysql33-bin.000001',master_log_pos=1094;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

此时200是100的从

100是200的从，操作倒过来执行下，记得将100的表锁打开执行mysql> unlock tables;