本人Ubuntu16.04系统,mysql5.5,进行到这一步时Slave_IO_Running状态为connection,很明显从服务器没有连接上主服务器的数据库。出现这种情况有三种可能性:
1,网络不通
2,密码不对
3,pos不对,
1、直接排除,本人用的vm虚拟机网络都是配置好的,以防万一可以ping一下。
PING 192.168.1.50 (192.168.1.50) 56(84) bytes of data.
64 bytes from 192.168.1.50: icmp_seq=1 ttl=64 time=0.177 ms
64 bytes from 192.168.1.50: icmp_seq=2 ttl=64 time=0.257 ms
64 bytes from 192.168.1.50: icmp_seq=3 ttl=64 time=0.202 ms
2、找到原change master命令:
change master to master_host='192.168.1.50',master_user='******',master_password='123456',master_log_file='mysql-bin.000009',master_log_pos=326;
仔细看了一下 相关信息均没有错误,此处若找不到命令可以去查看mysql error日志:vim /usr/local/mysql/var/******-server-4.err
翻到最下面日志开始的地方
171012 0:04:46 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port='3306', master_log_file='', master_log_pos='4'. New state master_host='192.168.1.50', master_port='3306', master_log_file='mysql-bin.000009', master_log_pos='326'.
171012 0:04:56 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000009' at position 326, relay log './******-server-4-relay-bin.000001' position: 4
171012 0:07:03 [ERROR] Slave I/O: error connecting to master '******@192.168.1.50:3306' - retry-time: 60 retries: 86400, Error_code: 2003
此处显示比较详细,包括change master 配置信息 和连接错误信息(以上*号为mysql用户名,请自行替换)。
3、找到原主数据库show master status命令结果(这个找不到就真找不到了):
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000009 | 326 | | |
最后再试一下,cmd连接主服务器mysql,
C:\Users\Administrator>mysql -h 192.168.1.50 -P 3306 -u ****** -p
Enter password: ******
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.50' (10060 "Unknown error")
不出所料,基本可以断定问题在哪了,查看防火墙 iptables -L -n
DROP tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:3306
外部连接3306端口被直接丢弃....好变态!
查看防火墙规则编号 :iptables -L -n --line-number
6 DROP tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:3306
现在我们删掉这条规则:iptables -D INPUT 6
再重新添加一条规则:iptables -A INPUT -p tcp --dport 3306 -j ACCEPT
查看状态:
ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:3306
修改完毕,重启从服务器mysql、查看数据库
之前:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
之后:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
同步完成!