mysql 主主同步失败_Mysql主主同步失败后的恢复

基础信息

主库:

数据库2 10.126.4.2

数据库3 10.126.4.3

1. 停止数据库3对外服务

防止同步过程中服务通过数据库3写入数据

$ firewall-cmd --remove-port=3306/tcp

$ firewall-cmd --add-rich-rule="rule f amily="ipv4" source address="10.126.4.2" port protocol="tcp" port="3306" accept"

$ firewall-cmd --reload

2. 备份主库

$ mysqldump -uroot -p --single-transaction --master-data=2 --no-autocommit -A >alldatas-190708.sql

记住 MASTER_LOG_FILE 和 MASTER_LOG_POS

$ head -n 30 alldatas-190708.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000016', MASTER_LOG_POS=801595396;

3. 切断数据库2、3的同步

两台主机分别执行

mysql>change master to master_host=''

mysql>stop slave;

mysql>reset slave;

4. 清空数据库3

清空数据文件

$ rm /var/lib/mysql

$ mv /db/mysql /db/mysql-190708

$ mkdir /db/mysql

$ ln -s /db/mysql /var/lib/mysql

chown mysql:mysql /var/lib/mysql

chown -h mysql:mysql /var/lib/mysql

初始化

mysqld --initialize --user=mysql

grep password /var/log/mysqld.log

重置 master 和 slave

mysql> reset master;

mysql> show master status\G

mysql> stop slave;

mysql> reset slave;

mysql> show slave status\G

File: mysql-bin.000001

Position: 154

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

ERROR:

No query specified

5. 导入主库信息，重新指向主库

$ mysql -uroot -p < alldatas.sql

mysql > change master to

-> master_host='10.126.4.2',

-> master_user='repl',

-> master_password='********',

-> master_log_file='binlog.???',

-> master_log_pos=???;

mysql > start slave;

mysql > show slave status\G

6. 数据库2配置主库信息

查询数据库3的 master_log_file 和 master_log_pos

10.126.4.3

show master status;

10.126.4.2

mysql > change master to

-> master_host='10.126.4.3',

-> master_user='repl',

-> master_password='********',

-> master_log_file='binlog.???',

-> master_log_pos=???;

mysql > show slave status\G

7. 放开数据库3对外服务

$ firewall-cmd --reload