read-only=1。/etc/my.cnf[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
expire_logs_days = 7
max_binlog_size = 100M
binlog-do-db = wordpress # 可選:只同步指定庫
binlog-ignore-db = mysql # 忽略系統庫
sync_binlog = 1 # 每次提交刷盤,最安全
innodb_flush_log_at_trx_commit = 1
/etc/my.cnf[mysqld]
server-id = 2
log-bin = mysql-bin # 從庫也開 binlog,支援級聯/備份
relay-log = relay-bin
gtid_mode = ON
enforce_gtid_consistency = ON
read-only = 1
replicate-do-db = wordpress # 可選,與 Master 對應
relay_log_purge = 1
relay_log_recovery = 1 # 啟動時自動修復損壞 relay log
CREATE USER 'repl_user'@'192.168.1.%' IDENTIFIED BY 'StrongPass123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.%';
FLUSH PRIVILEGES;
-- Slave 執行
CHANGE MASTER TO
MASTER_HOST='192.168.1.1',
MASTER_USER='repl_user',
MASTER_PASSWORD='StrongPass123!',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1; -- GTID 核心:自動定位
START SLAVE;
SHOW SLAVE STATUS\G
关键指标:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Master_Server_Id: 1
Master_UUID: xxxx
Retrieved_Gtid_Set: xxxx:1-100
Executed_Gtid_Set: xxxx:1-100
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='new_ip'; -- 或 MASTER_LOG_FILE/POS (非 GTID)
START SLAVE;
-- 跳過 1 個事件
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;
-- GTID 模式跳過
SET GTID_NEXT='aaaa-bbbb-cccc:101';
BEGIN; COMMIT;
SET GTID_NEXT='AUTOMATIC';
import pymysql, os, smtplib
conn = pymysql.connect(host='192.168.1.2', user='monitor', password='xxx')
with conn.cursor() as cur:
cur.execute("SHOW SLAVE STATUS")
row = cur.fetchone()
if row['Slave_IO_Running'] != 'Yes' or row['Slave_SQL_Running'] != 'Yes' or row['Seconds_Behind_Master'] > 300:
# 發送告警郵件/釘釘/企業微信
pass
主從複制**標準化四步走**:配置 → 帳號 → GTID 啟動 → 監控告警。生產環境**必須**開啟 GTID、ROW 格式、read-only、半同步 (rpl_semi_sync_master=ON)、監控腳本。進階架構請評估 MGR 或 Orchestrator 實現自動故障轉移。