mysql主从断掉后，恢复

古城微笑少年丶 2023-06-06 12:12 16阅读 0赞

主库：10.0.2.75；从库：10.0.2.230；

主库操作：

mysql> show slave status\\G;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMDc0NTI3_size_16_color_FFFFFF_t_70][]

mysql> flush tables with read lock\\G

![20191011172105963.png][]

mysql> show master status\\G

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMDc0NTI3_size_16_color_FFFFFF_t_70 1][]

mysql> change master to master\_host='10.0.2.230', master\_user='root', master\_password='123456', master\_log\_file='mysql-bin.000005', master\_log\_pos=625;

\#\#\#mater\_log\_file和master\_log\_pos的值是在从库上执行show master status\\G 的结果

出现报错：

![20210111181528392.png][]

mysql> unlock tables;

![20210111181705645.png][]

mysql> start slave;

![20191011172217949.png][]

出现报错；处理；

mysql> reset slave;

![20191011172301946.png][]

重新配置；

mysql> change master to master\_host='10.0.2.230', master\_user='root', master\_password='123456', master\_log\_file='mysql-bin.000005', master\_log\_pos=625;

mysql> start slave;

![2019101117233947.png][]

mysql> show slave status\\G;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMDc0NTI3_size_16_color_FFFFFF_t_70 2][]

主库成功；开始配置从库；

mysql> flush tables with read lock\\G

mysql> show master status\\G

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMDc0NTI3_size_16_color_FFFFFF_t_70 3][]

mysql> change master to master\_host='10.0.2.75', master\_user='root', master\_password='123456', master\_log\_file='mysql-bin.000008', master\_log\_pos=639208;

\#\#\#mater\_log\_file和master\_log\_pos的值是在主库上执行show master status的结果

mysql>  start slave;

![20191011172801881.png][]

mysql> reset slave;

![20191011172809354.png][]

mysql> change master to master\_host='10.0.2.75', master\_user='root', master\_password='123456', master\_log\_file='mysql-bin.000008', master\_log\_pos=639208;

mysql>  start slave;

![20191011172829179.png][]

mysql> show slave status\\G

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMDc0NTI3_size_16_color_FFFFFF_t_70 4][]

mysql> UNLOCK TABLES;

![20191011173005878.png][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMDc0NTI3_size_16_color_FFFFFF_t_70]: /images/20230601/3d25a43de31c4a688c839d3f273e228d.png
[20191011172105963.png]: /images/20230601/9251bdfd1bd749c38b7cddcaa98b73d9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMDc0NTI3_size_16_color_FFFFFF_t_70 1]: /images/20230601/5ef07664ded444dcae3eca129f1258e6.png
[20210111181528392.png]: /images/20230601/da85c276a9204c28b0a873df15f20321.png
[20210111181705645.png]: /images/20230601/ccb61bee11cd40e39e38e6b2529dded1.png
[20191011172217949.png]: /images/20230601/4cb57727ce0649cda26a05c6c0cd84c7.png
[20191011172301946.png]: /images/20230601/6eea3479e7f4434a807ea750a8585fed.png
[2019101117233947.png]: /images/20230601/4eb38c2905d440fe968011febcf37290.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMDc0NTI3_size_16_color_FFFFFF_t_70 2]: /images/20230601/f15d1e5cbacc4ee2bfcfd41bb0d24b0d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMDc0NTI3_size_16_color_FFFFFF_t_70 3]: /images/20230601/c9df35ec97a249c6b36b5ea7f5d85a19.png
[20191011172801881.png]: /images/20230601/468206a57bc74d5ba9ed1f7a0e26462e.png
[20191011172809354.png]: /images/20230601/099d6128ae8d4384b3537f6d7f1505c1.png
[20191011172829179.png]: /images/20230601/f900e6e05e0b488b8d6515f53f9a1693.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMDc0NTI3_size_16_color_FFFFFF_t_70 4]: /images/20230601/16ccf7d976df4d7f853b48f6cf1c1d79.png
[20191011173005878.png]: /images/20230601/ff8961fb2ce349e085341b95b4b91915.png