mysql主从不复制排错_Mysql主从复制排错案例一

MYSQL主从复制排错案例一：

问题：主从无法同步

现象：MASTER: mysql> show master status;

Empty set (0.00 sec)

SLAVE:  mysql> show slave status \G;

Slave_IO_Running:Connecting              Slave_SQL_Running: Yes

Seconds_Behind_Master: NULL

Last_IO_Errno: 1045

Last_IO_Error: error connecting to master [email protected]:3306‘

- retry-time: 60  retries: 86400

问题排查过程：

一 、 MASTER :[[email protected] ~]# egrep "log-bin|server" /etc/my.cnf

# The MySQL server

server-id       = 1

#log-bin=mysql-bin    //没有开启            SLAVE: [[email protected] ~]# egrep "log-bin|server" /etc/my.cnf

# The MySQL server

server-id       = 3

#log-bin=mysql-bin

二、.开启MASTART log-bin日志，重新启动mysql数据库

MASTER: mysql> show master status;

mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 |     107 |              |                  |

+------------------+----------+--------------+------------------+

SLAVE:    mysql> show slave status \G;

Slave_IO_Running: Connecting

Slave_SQL_Running: Yes

三、查看master.info 和relay-log.info信息

SLAVE:  [[email protected] data]# cat master.info

18

mysql-bin.000001

348                            192.168.254.253

rep

oldboy123

3306

60

0

[[email protected] data]# cat relay-log.info

./localhost-relay-bin.000005

4

mysql-bin.000001

348                              8

MASTER上的log-bin的POS点107，SLAVE上的log-bin的POS点348,两边不一致。 四、. 在master.info中用户名为rep,在MASTER端没有找到该用户注册的信息。

mysql> select host,user from mysql.user;

五、.重新注册用于同步的用户

grant replication slave on *.* to [email protected] identified by ‘oldboy123‘;

六、 在SLAVE重新注册CHANGE MASTER TO

mysql> CHANGE MASTER TO

-> MASTER_HOST=‘192.168.254.253‘,

-> MASTER_PORT=3306,

-> MASTER_USER=‘rep‘,

-> MASTER_PASSWORD=‘oldboy123‘,

-> MASTER_LOG_FILE=‘mysql-bin.000002‘,

-> MASTER_LOG_POS=107;

mysql> start slave;

七、.mysql> show slave status \G;

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

八、.测试：MASTER:  mysql> create database mama;

Query OK, 1 row affected (0.00 sec)

SLAVE: mysql> show databases;

mama

问题：由于虚拟机测试环境，没有把MASTER数据库变为只读，备份主库。直接同步到从库，这样后果丢失数据。在出现问题之前创建新的数据库没有同步过来。 生产环境下要先备份主库。

原文：http://xin521long.blog.51cto.com/11884590/1832143