Mysql主從複製排錯案例一

標籤：mysql   error   status   master   

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 ‘rep‘@‘192.168.254.250‘ 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

Mysql主從複製排錯案例一