mysql主从同步从库上Slave_IO_Running: Connecting问题

绝地灬酷狼 2023-10-18 19:05 10阅读 0赞

mysql主从同步从库上Slave\_IO\_Running: Connecting问题

一．在做主从同步时遇到的问题

数据库主从问题从库上Slave\_IO\_Running: Connecting

在做mysql主从同步的时候有时候发现在从库上Slave\_IO\_Running: Connecting

![901ceadbb653bfbab5e7fc5eabc9ccfe2f4a6f56.jpg][]

二．解决办法通常是5个问题：

1.网络不通 \#互ping机器ip，无丢包率正常访问，排除

2.密码不对 \#MySQL重新授权远程用户登陆账号，重新设置密码，排除

3.pos不正确 \#主服务器,登陆数据库重新查看起始偏移量show master status,排除

4.ID问题

\#ID的问题，在安装完mysql数据库的时候默认他们的server-id=1 但是在做主从同步的时候需要将ID号码设置不一样才行,查看数据库配置文件cat /etc/my.cnf，文件写的不同，排除

5.防火墙策略

查看防火墙策略，是否放通双方的服务端口 iptables -nL,最后发现 防火墙策略写了多端口防火墙策略的端口不生效，解决防火墙策略单独开放端口，暂停从服务的io读取stop slave;重新在从的数据库写入以下语句：

mysql> change master to

\-> master\_host='192.168.137.36',

\-> master\_user='root',

\-> master\_password='abc123',

\-> master\_port=3307,

\-> master\_log\_file='mysql-bin.000001',

\-> master\_log\_pos=848;

Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> start slave;

Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Slave\_IO\_State: Waiting for master to send event

Master\_Host: 192.168.137.36

Master\_User: root

Master\_Port: 3307

Connect\_Retry: 60

Master\_Log\_File: mysql-bin.000001

Read\_Master\_Log\_Pos: 1298

Relay\_Log\_File: localhost-relay-bin.000002

Relay\_Log\_Pos: 770

Relay\_Master\_Log\_File: mysql-bin.000001

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

Replicate\_Do\_DB:

Replicate\_Ignore\_DB:

Replicate\_Do\_Table:

Replicate\_Ignore\_Table:

Replicate\_Wild\_Do\_Table:

Replicate\_Wild\_Ignore\_Table:

Last\_Errno: 0

Last\_Error:

Skip\_Counter: 0

Exec\_Master\_Log\_Pos: 1298

Relay\_Log\_Space: 981

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: 0

Master\_SSL\_Verify\_Server\_Cert: No

Last\_IO\_Errno: 0

Last\_IO\_Error:

Last\_SQL\_Errno: 0

Last\_SQL\_Error:

Replicate\_Ignore\_Server\_Ids:

Master\_Server\_Id: 9

Master\_UUID: ad899234-9c81-11e7-99bd-000c29af2bef

Master\_Info\_File: /data/mysql\_data1/master.info

SQL\_Delay: 0

SQL\_Remaining\_Delay: NULL

Slave\_SQL\_Running\_State: Slave has read all relay log; waiting for more updates

Master\_Retry\_Count: 86400

Master\_Bind:

Last\_IO\_Error\_Timestamp:

Last\_SQL\_Error\_Timestamp:

Master\_SSL\_Crl:

Master\_SSL\_Crlpath:

Retrieved\_Gtid\_Set:

Executed\_Gtid\_Set:

Auto\_Position: 0

Replicate\_Rewrite\_DB:

Channel\_Name:

Master\_TLS\_Version:

1 row in set (0.00 sec)

注意：标注的俩个红色yes为正常。

本文原作者去轻狂书生，原文链接http://blog.51cto.com/8999a/1966548

标签： [IO][] [Running][] [Slave][]

声明：本文内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：4051308@qq.com 进行举报，并提供相关证据，工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

来源：[https://cloud.tencent.com/info/3ce6fb6c2d65bd337b37e591b46a3557.html][https_cloud.tencent.com_info_3ce6fb6c2d65bd337b37e591b46a3557.html]

[901ceadbb653bfbab5e7fc5eabc9ccfe2f4a6f56.jpg]: http://seo-1255598498.file.myqcloud.com/full/901ceadbb653bfbab5e7fc5eabc9ccfe2f4a6f56.jpg
[IO]: https://cloud.tencent.com/info/tag/255703
[Running]: https://cloud.tencent.com/info/tag/273283
[Slave]: https://cloud.tencent.com/info/tag/273282
[https_cloud.tencent.com_info_3ce6fb6c2d65bd337b37e591b46a3557.html]: https://cloud.tencent.com/info/3ce6fb6c2d65bd337b37e591b46a3557.html