mysql主从，一方发生错误，恢复方式

梦里梦外; 2021-09-07 21:24 252阅读 0赞

今天收到客服反馈，有用户登录失败，提示账户不存在，进入mycat里查询发现确实查不出数据，于是进入物理库分别查询，发现俩个物理库数据不同步，检查主从配置发现错误，如下：  
mysql> show slave status\\G;  
Slave\_IO\_State: Waiting for master to send event  
Master\_Host: xx.xxx.xx.xx  
Master\_User: root  
Master\_Port: 3306  
Connect\_Retry: 60  
Master\_Log\_File: mysql-bin.000004  
Read\_Master\_Log\_Pos: 273639293  
Relay\_Log\_File: iZbp1cbpdn4wsuej4fhbfcZ-relay-bin.000002  
Relay\_Log\_Pos: 2017959  
Relay\_Master\_Log\_File: mysql-bin.000004  
Slave\_IO\_Running: Yes  
Slave\_SQL\_Running: No  
Replicate\_Do\_DB:textdbnew  
Replicate\_Ignore\_DB:  
Replicate\_Do\_Table:  
Replicate\_Ignore\_Table:  
Replicate\_Wild\_Do\_Table:  
Replicate\_Wild\_Ignore\_Table:  
Last\_Errno: 1594  
Last\_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master’s binary log is corrupted (you can check this by running ‘mysqlbinlog’ on the binary log), the slave’s relay log is corrupted (you can check this by running ‘mysqlbinlog’ on the relay log), a network problem, or a bug in the master’s or slave’s MySQL code. If you want to check the master’s binary log or slave’s relay log, you will be able to know their names by issuing ‘SHOW SLAVE STATUS’ on this slave.  
Skip\_Counter: 0  
Exec\_Master\_Log\_Pos: 94945936  
Relay\_Log\_Space: 111997714  
Until\_Condition: None  
Until\_Log\_File:  
Until\_Log\_Pos: 0  
Master\_SSL\_Allowed: No  
Master\_SSL\_CA\_File:  
Master\_SSL\_CA\_Path:  
Master\_SSL\_Cert:  
Master\_SSL\_Cipher:  
Master\_SSL\_Key:  
Seconds\_Behind\_Master: NULL  
Master\_SSL\_Verify\_Server\_Cert: No  
Last\_IO\_Errno: 0  
Last\_IO\_Error:  
Last\_SQL\_Errno: 1594  
Last\_SQL\_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master’s binary log is corrupted (you can check this by running ‘mysqlbinlog’ on the binary log), the slave’s relay log is corrupted (you can check this by running ‘mysqlbinlog’ on the relay log), a network problem, or a bug in the master’s or slave’s MySQL code. If you want to check the master’s binary log or slave’s relay log, you will be able to know their names by issuing ‘SHOW SLAVE STATUS’ on this slave.  
Replicate\_Ignore\_Server\_Ids:  
Master\_Server\_Id: 104  
Master\_UUID: c81b1259-82ac-11e9-8bbb-00163e024c1e  
Master\_Info\_File: /var/lib/mysql/master.info  
SQL\_Delay: 0  
SQL\_Remaining\_Delay: NULL  
Slave\_SQL\_Running\_State:  
Master\_Retry\_Count: 86400  
Master\_Bind:  
Last\_IO\_Error\_Timestamp:  
Last\_SQL\_Error\_Timestamp: 210423 11:28:53  
Master\_SSL\_Crl:  
Master\_SSL\_Crlpath:  
Retrieved\_Gtid\_Set:  
Executed\_Gtid\_Set:  
Auto\_Position: 0  
Replicate\_Rewrite\_DB:  
Channel\_Name:  
Master\_TLS\_Version:

解决方法，重新设置主从进程解决：  
mysql> stop slave;  
Query OK, 0 rows affected (0.02 sec)

mysql> reset slave;  
Query OK, 0 rows affected (0.57 sec)

mysql> change master to master\_host=‘xxx.xxx.xxx.xx’,  
\-> master\_user=‘replicat’,  
\-> master\_password=‘passw’,  
\-> master\_log\_file=‘mysql-bin.000004’,  
\-> master\_log\_pos=94945936;  
Query OK, 0 rows affected, 2 warnings (0.07 sec)

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

注意点：重新设置主机的日志同步位置是以出错日志里的position开始， Exec\_Master\_Log\_Pos: 94945936，还有日志文件也是：Relay\_Master\_Log\_File: mysql-bin.000004。

如果数据量比较小，用这种方式可以，如果数据量大，且主从机在外网，建议采取如下方式：  
注意先停止数据库访问，以免出现数据不同步  
1.停止主从配置  
stop slave;  
reset slave;  
2.拷贝主库数据到从库，表结构和数据一起导出  
mysqldump -u root --default-character-set=gbk -p\*\*\*\*\*\* databasename > /home/eeet/bak.sql  
3.进入从库的mysql  
mysql> source bak.sql  
导入数据到从库，这样比慢慢同步日志效率高很多  
4.重新配置主从，搞定  
主从配置不会的可以看前面的博客 [https://blog.csdn.net/qq\_33337927/article/details/104680912][https_blog.csdn.net_qq_33337927_article_details_104680912]

[https_blog.csdn.net_qq_33337927_article_details_104680912]: https://blog.csdn.net/qq_33337927/article/details/104680912