您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 5.5 主從同步深度實戰：配置優化與故障處理全解析

編輯：MySQL 綜合教學

MySQL 5.5 主從同步深度實戰：配置優化與故障解析

MySQL 5.5 引入了多項複製增強特性，本文結合生產環境實踐，詳細解析 5.5 版本主從同步的配置細節、性能優化與常見故障處理方案。

一、主庫深度配置優化

[mysqld]
log-bin=mysql-bin
log-bin-index=mysql-bin.index
server-id = 1
sync_binlog=1
binlog_format=mixed

binlog-do-db = testdb
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema

[mysqldump]
max_allowed_packet = 32M

關鍵參數解析：

sync_binlog=1：每次提交刷盤，最大程度保證數據不丟失，性能略有影響
binlog_format=mixed：混合模式，兼顧基於語句和基於行的優缺點
max_allowed_packet：防止大事務導致備份/同步失敗，建議設置 100M 以上

二、從庫增強配置

[mysqld]
server-id=2
log-bin = mysql-bin
relay-log-index = slave-relay-bin.index
relay-log = slave-relay-bin
sync_master_info = 1
sync_relay_log = 1
sync_relay_log_info = 1

5.5 專屬參數： 最後三行參數僅 5.5+ 支持，分別同步主庫信息、中繼日誌、中繼日誌信息到磁盤，大幅提高從庫崩潰恢復能力。

三、同步賬號與啟動流程

-- 主庫創建同步用戶
CREATE USER 'funsion'@'%' IDENTIFIED BY 'ifunsion.com';
GRANT REPLICATION SLAVE ON *.* TO 'funsion'@'%';

-- 從庫建立連接
CHANGE MASTER TO 
    MASTER_HOST = '10.1.123.233',
    MASTER_USER = 'funsion',
    MASTER_PORT = 3306,
    MASTER_PASSWORD = 'ifunsion.com';
CHANGE MASTER TO MASTER_CONNECT_RETRY=30;
START SLAVE;

四、故障恢復標準流程

當同步報錯無法啟動時：

主庫查看坐標：SHOW MASTER STATUS\G;
從庫停止同步：STOP SLAVE;
重新指定位置：CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
重啟同步：START SLAVE;

五、時間同步的重要性

yum -y install ntpdate
ntpdate cn.pool.ntp.org
clock -w

原因： 主從時間差異會導致基於時間的事件（如 Event Scheduler）執行異常，影響數據一致性。

六、常用運維命令速查

操作	命令	說明
鎖表	FLUSH TABLES WITH READ LOCK;	構建一致性快照前必須操作
解鎖	UNLOCK TABLES;	釋放全局讀鎖
重置主庫	RESET MASTER;	清空所有二進制日誌，慎用！
重置從庫	RESET SLAVE;	清除同步元數據，重新開始

七、業務不停機擴展從庫實戰

場景：主庫 1 號、從庫 2 號運行中，需新增從庫 3 號，且不允許停服務。

-- 主庫導出帶坐標的備份
mysqldump -uroot -pxxxx -h127.0.0.1 --databases db1 db2 db3 --master-data=1 > bak.sql

-- 3 號從庫操作
SLAVE STOP;
SOURCE bak.sql;
SLAVE START;

核心原理： --master-data=1 會在 SQL 文件末尾自動添加 CHANGE MASTER TO 語句，包含導出瞬間的 Binlog 位置，從庫導入後自動從該位置開始同步。

適用限制： 僅適合數據量較小（如 6G 以內）的場景，大數據量建議使用 Percona XtraBackup 熱備方案。