MySQL数据库主从同步错误的解决办法

主：

先停止mysql服务

systemctl stop mysqld

登陆数据库

mysql-uroot-p

停止slave

stop slave;

重置中继日志

reset slave；

查看主状态

show master status；

假设

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File                      | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000101 |      110 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

 

登陆到从

stop slave;

reset slave;

change master to
master_host='主库IP',
master_port=3306,
master_user='root',
master_password='主库密码',
master_log_file='mysql-bin.000101',
master_log_pos=110;

启动

start slave;

查看状态

show slave status\G;

 

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

两个都为Yes则成功，反过来也一样

验证

主机create database xxx;

登陆备机查看show databases;

如果有xxx则正常

备机删除drop database xxx;

登陆主机，show databases;

xxx消失则为互备正常

如果出现报错

show slave status\G;

 Last_Error:可能会打印出错误

这时可以查也可以直接跳过事务

 stop slave;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

start slave;

即可