分類: mysql

最容易、最安全的「完整重同步」步驟（排除指定 DB）

適用情境

• Slave 一直出現 1032 / 1146 錯誤
• 資料已嚴重不同步
• 非 GTID 複製
• 需要保留 Replicate_Ignore_DB 設定

---

🛑 Step 1：在 SLAVE 停止並重置複製

sql

STOP SLAVE;
RESET SLAVE ALL;

目的：
清除舊的 relay log，避免殘留錯誤事件。

---

📌 Step 2：在 MASTER 鎖定資料並取得 binlog 位置

sql

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

請記下輸出中的：

text

File: mysql-bin.xxxxxx
Position: xxxxxxxx

⚠️ 請保持此連線不要中斷

---

📦 Step 3：在 MASTER 匯出所有資料庫（排除 ignore 的 DB）

bash

mysqldump -u root -p \
  --all-databases \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --master-data=2 \
  --ignore-database=dbispconfig \
  --ignore-database=dbispconfig2 \
  --ignore-database=mysql \
  --ignore-database=phpmyadmin \
  --ignore-database=sys \
  --ignore-database=information_schema \
  --ignore-database=performance_schema \
  > fulldump.sql

說明：

• --master-data=2：自動記錄 binlog 位置
• --single-transaction：InnoDB 無鎖匯出
• ignore DB 與 Replicate_Ignore_DB 完全一致

---

🔓 Step 4：解除 MASTER 鎖定

sql

UNLOCK TABLES;

---

🧹 Step 5：在 SLAVE 清空舊資料（非常重要）

✅ 方法一（推薦，安全）

sql

SET sql_log_bin = 0;

DROP DATABASE IF EXISTS c4web113;
DROP DATABASE IF EXISTS c7_5guvv_web116;
-- 其他客戶 DB 請一併 DROP

SET sql_log_bin = 1;

⚠️ 只 DROP「需要複製的 DB」，ignore 的 DB 不要動

---

📥 Step 6：在 SLAVE 匯入完整資料

bash

mysql -u root -p < fulldump.sql

⏳ 視資料量大小，可能需要數分鐘～數十分鐘

---

🔗 Step 7：在 SLAVE 設定 Master binlog 位置

（使用 Step 2 記下來的值）

sql

CHANGE MASTER TO
  MASTER_HOST='MasterSQLServer_IPAddress',
  MASTER_USER='slaveuser2',
  MASTER_PASSWORD='你的密碼',
  MASTER_LOG_FILE='mysql-bin.xxxxxx',
  MASTER_LOG_POS=xxxxxxxx;

---

▶️ Step 8：在 SLAVE 啟動複製

sql

START SLAVE;

---

✅ Step 9：確認複製狀態

sql

SHOW SLAVE STATUS\G

確認以下欄位：

text

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_SQL_Error:
Seconds_Behind_Master: (數字，並會逐漸下降)

---

✅ 完成後預期結果

• ✅ 不再出現 1032 / 1146 錯誤
• ✅ WordPress（含 Wordfence）資料正常
• ✅ Slave 與 Master 完整一致
• ✅ Replicate_Ignore_DB 持續生效

---

⚠️ 注意事項（請務必遵守）

• ❌ 不要再使用 SQL_SLAVE_SKIP_COUNTER
• ❌ 不要手動補單一 table
• ❌ 不要分開 dump 不同 DB

mysqldump -u root -p \
 --single-transaction \
 --routines \
 --triggers \
 --databases mydb > mydb.sql

mysql> SELECT INET_ATON('209.207.224.40');
-> 3520061480

mysql> SELECT INET_NTOA(3520061480);
-> '209.207.224.40'

e.g.
SELECT `ipaddress` FROM `blockip` ORDERBY INET_ATON(`ipaddress`)

source: (user9385340)(2022), https://stackoverflow.com/questions/8557172/mysql-order-by-sorting-alphanumeric-correctly

example:

SELECT `alphanumericCol` FROM `tableName` ORDER BY 
  SUBSTR(`alphanumericCol` FROM 1 FOR 1), 
  LPAD(lower(`alphanumericCol`), 10,0) ASC

output:

1
2
11
21
100
101
102
104
S-104A
S-105
S-107
S-111

Source: https://stackoverflow.com/questions/2366018/how-to-re-sync-the-mysql-db-if-master-and-slave-have-different-database-incase-o, David Espart 2010

This is the full step-by-step procedure to resync a master-slave replication from scratch:

At the master:

RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

And copy the values of the result of the last command somewhere.

Without closing the connection to the client (because it would release the read lock) issue the command to get a dump of the master:

mysqldump -u root -p --all-databases > /a/path/mysqldump.sql

Now you can release the lock, even if the dump hasn’t ended yet. To do it, perform the following command in the MySQL client:

UNLOCK TABLES;

Now copy the dump file to the slave using scp or your preferred tool.

At the slave:

Open a connection to mysql and type:

STOP SLAVE;

Load master’s data dump with this console command:

mysql -uroot -p < mysqldump.sql

Sync slave and master logs:

RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;

Where the values of the above fields are the ones you copied before.

Finally, type:

START SLAVE;

To check that everything is working again, after typing:

SHOW SLAVE STATUS;

you should see:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

That’s it!

master Server

show master status;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000177 | 31692464 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

File = mysql-bin.000177, Position = 31692464

slave Server

stop slave;
CHANGE MASTER TO master_log_file = 'mysql-bin.000177', master_log_pos =31692464;
start slave;
show slave status \G;

master

show master status;

slave

show slave status \G;

USE information_schema;
SELECT
        o.TABLE_SCHEMA,
        o.UPDATE_TIME 
    FROM
        `TABLES` o 
    LEFT JOIN
        `TABLES` b 
            ON o.TABLE_SCHEMA = b.TABLE_SCHEMA 
            AND o.UPDATE_TIME < b.UPDATE_TIME 
    WHERE
        b.UPDATE_TIME IS NULL
        AND o.UPDATE_TIME IS NOT NULL
    GROUP BY 
        o.TABLE_SCHEMA;