How to rebuild mysql replication once it disasters? 如何重建MYSQL資料庫複製?

由於我的SLAVE MYSQL是不經常開機的PC,偶爾未正常關閉SLAVE,就會有同步異常的現象發生

Due to my slave mysql located at my PC that’s not always persistent online, sometimes, I forget to shutdown slave normally, and then sync job will occur error.

當你show slave status G;時,沒看到兩個YES, 就是出問題了

when you execute show slave status G; on Slave, you did not see two lines “YES”, it should be gotten problems between master and slave.

下圖是正常的狀況,當出問題時,可能其中一個會變NO

Below image shows you a normal situation, you can see two lines “YES”, once one of it turns into “NO”, you should got any problem on your Mysql replication.

 

 

 

 

 

 

那發生問題時,怎麼辦呢? 以下是處理的步驟

And once happen, how can you do? below is some procedures for your reference.

在SLAVE機台上的mysql console,執行以下步驟:

on Slave Mysql console, please follow up the procedures as below.

1. STOP SLAVE;

2. RESET SLAVE;

3. 停止MYSQL服務

after stop and reset slave, please shutdown your Mysql service on your slave side.

在MASTER的機台上, DUMP一次資料庫,如下圖指令:

and on your Master, dump all of the databases you would like to replicate as below image.

然後把它LOAD回SLAVE裡,如下圖

and then load it into Slave as below.

在SLAVE上的MYSQL CONSOLE,再輸入以下指令:

and then on the Mysql Console of Slave, execute the command as below.

CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
MASTER_USER=<user>, MASTER_PASSWORD=<password> ;

where you replace <host>, <user>, <password> by quoted strings and
<port> by the master’s port number (3306 by default).

Example:

CHANGE MASTER TO MASTER_HOST=’125.564.12.1′, MASTER_PORT=3306, MASTER_USER=’joe’, MASTER_PASSWORD=’secret’;

然後, 重新啟動複製

and then, restart replication.

start slave;

一切即恢復正常

and finally, You should get your mysql replication back to normal.

another way is you use phpmyadmin to bypass the error, and reload your slave.

Print Friendly, PDF & Email

發表迴響