[MySQL] Master / Slave – MySQL Replica 指南

引言

MySQL Replica 相關指令及指南


安裝

MySQL官方文件:Chapter 16 Replication

安裝筆記可以參考:https://blog.longwin.com.tw/2008/03/mysql_replication_master_slave_set_2008/


Slave 狀態檢查

狀態查詢SQL

SHOW slave STATUS;

建議使用command直式較好查看:SHOW slave STATUS \G;

忽略錯誤

SET GLOBAL SQL_SLAVE_SKIP_COUNTER =1;

可一次略過大量SQL數,建議逐筆Error Skip才不會漏

重新跟Master (錯誤後會停止)

START SLAVE;

狀態識別

Key Description
Master_Log_File IO Thread正在讀取Master的binlog
Slave_IO_Running 是否在同步,正常為Yes
Slave_SQL_Running 是否在同步,正常為Yes
Read_Master_Log_Pos Master Log I/O線程已經讀取的位置
Relay_Log_Pos 目前Slave Log I/O線程已經讀取的位置
Last_Error 錯誤SQL訊息
Skip_Counter 接下來跳過SQL count
Exec_Master_Log_Pos 自Master的binlog上次執行時間位置
Seconds_Behind_Master 主從之間的秒差

Master狀態檢查

SHOW BINARY LOGS Syntax

SHOW BINARY LOGS
SHOW MASTER LOGS

實作

Master to Slave

從Master做mysqldump時可以加入--master-data=1參數以匯出當前MASTER_LOG_FILEMASTER_LOG_POS資訊至sql:

mysqldump --databases [DATABASE_NAME1, DATABASE_NAME2, ...] -h [INSTANCE_IP] -u [USERNAME] -p \
--master-data=1 --flush-privileges --hex-blob --skip-triggers --ignore-table [VIEW_NAME1] [...] \
--default-character-set=utf8 > [SQL_FILE].sql

Slave的CHANGE MASTER SQL設定為:

CHANGE MASTER TO MASTER_HOST='[MASTER_IP_ADDRESS]', MASTER_USER='[REPLICATION_USER]',
MASTER_PASSWORD='[REPLICATION_PASSWORD]', MASTER_AUTO_POSITION=1;

MASTER_AUTO_POSITION option to tell the slave that transactions will be identified by GTIDs.

動態變更Slave位置

CHANGE MASTER TO MASTER_LOG_FILE='log-bin.{number}', MASTER_LOG_POS={number};

Slave災難救援

例如Slave機斷電等等造成Slave無法跟上Master時,最快的方法還是mysqldump來源再導入重跟。

From Master

從Master從新倒出導入較容易,同於上面所述--master-data=1就可以dump出log位置:

  1. 對Master做mysqldump(含master-data)
  2. Slave stop slave;
  3. 導入Master dump sql > start slave;

From Slave

例如Master不想Lock table做mysqldump時,且有另一台正常運作的Slave,那就來對該台Slave抄寫:

  1. 對正常Slave做mysqldump(不支援master-data)
  2. 在Lock期間查看Relay_Master_Log_File&Exec_Master_Log_Pos
  3. Slave stop slave;
  4. 導入Slave dump sql
  5. 修改起始位置,依照第二步資料填入:CHANGE MASTER TO MASTER_LOG_FILE='{Relay_Master_Log_File}', MASTER_LOG_POS={Exec_Master_Log_Pos};
  6. start slave;

此法理論上是資料一致,但也可能會重複。若出現位置錯誤例如duplicate key,可以先Skip掉。

若使用GTID,則先關掉,Skip完在開:

/* Step: OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON. */
SET @@GLOBAL.GTID_MODE = OFF;

GCP Remote Replica

GCP提供遠端Replica Soluction: Configuring External Replicas

官方文件中大致流程:

  1. GCP Master開立Replica用的帳號及權限

  2. 從GCP Master做全項mysqldump(Creating a SQL dump file),排除核心資料庫

  3. 匯入至Remote Replica後進行Replica設定(第二代使用gtid_mode),在進入mysql設定CHANGE MASTER,最後START SLAVE;


Bin Log 處理與設定

查看檔案 – mysqlbinlog

使用mysqlbinlog Tool開啟回傳bin log語句內容,可以令存檔案出來:

$ mysqlbinlog mysqld-bin.000001
$ mysqlbinlog mysqld-bin.000562 > ~/my-bin-sql-562.txt

expire_logs_days

Bin Log保留的天數。可以全域設定於/etc/mysql/my.cnf:

expire_logs_days = 3

Bin Log 存放目錄

指定Bin Log存放路徑,可以設定log-binmy.cnf:

log-bin=/path/to/new/directory/mysql-bin

mysql-bin 為檔名格式,將變成mysql-bin.000001

Leave a Reply

Your email address will not be published. Required fields are marked *