mysql主从同步Slave_IO_Running:NO/Connecting问题

第一次做mysql主从同步的时候，在从库上查看同步状态：

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.41.137
                  Master_User: xxx
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 1066
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 

同步正常：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

一、如果出现：

Slave_IO_Running: No
Slave_SQL_Running: Yes

原因一：
主从配置文件per.cnf 中server-id 相同
解决：
主从配置文件per.cnf 设置不同，
比如master：
server-id=1
slave：
server-id=2

其它原因：
master上查看binlog信息：

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |     1066 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.04 sec)

mysql> 

在slave上查看

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.41.137
                  Master_User: xxx
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 767

主从的Master_Log_File、Read_Master_Log_Pos 不同，
解决：
从更新和主一致

mysql>master_host='192.168.41.137',master_port=3306,master_user='xxx',master_password='xxx',master_log_file='mysql-bin.000005',master_log_pos=1066;

导致原因是主mysql重启过，binlog文件位置发生了改变

二、如果出现：

Slave_IO_Running: Connecting
Slave_SQL_Running: Yes

在从mysql查看

mysql> show slave status\G
Master_User: xxx
Master_Port: 3306

测试从主机是否能连接主mysql

[trf@localhost ~]$ mysql -h192.168.41.137 -P 3306 -uxxx -p
Enter password: 
ERROR 1045 (28000): Unknown error 1045
[trf@localhost ~]$ 

在主mysql上查看用户是否开启了远程访问

mysql> use mysql;
mysql> select Host,User from user;
+-----------+---------------+
| Host      | User          |
+-----------+---------------+
| localhost        | xxx        |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
4 rows in set (0.00 sec)

mysql> 

如果没有远程访问，将从主机地址添加到访问列表：

mysql> GRANT ALL PRIVILEGES ON *.* TO 'xxx'@'xxx.xxx.xxx.xxx' IDENTIFIED BY 'xxx' WITH GRANT OPTION;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> select Host,User from user;
+-------------+---------------+
| Host        | User          |
+-------------+---------------+
| xxx.xxx.xxx.xxx         | xxx          |
| localhost        | xxx        |
| localhost   | mysql.session |
| localhost   | mysql.sys     |
| localhost   | root          |
+-------------+---------------+
5 rows in set (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> 

从mysql可以访问主mysql之后，从mysql上查看同步正常：

mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes