主從複製是 MySQL 高可用與讀寫分離的基石。通過 Binlog 異步/半同步傳輸,實現數據實時同步,具備:
| 角色 | 內網 IP | 公網 IP | 版本 | Server ID |
|---|---|---|---|---|
| Master | 10.4.14.168 | 101.69.178.208 | MySQL 5.7+ | 1 |
| Slave | 10.4.5.9 | 101.69.178.219 | MySQL 5.7+ | 2 |
# CentOS 7/8 使用官方 Yum 源
rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
yum install -y mysql-community-server
# 或 Ubuntu
apt update && apt install -y mysql-server
# 驗證安裝
mysql --version
# 輸出示例:mysql Ver 8.0.36 for Linux on x86_64 (MySQL Community Server - GPL)
systemctl start mysqld
systemctl enable mysqld
# 獲取臨時 root 密碼(MySQL 5.7+)
grep 'temporary password' /var/log/mysqld.log
# 登錄並修改密碼、刪除匿名用戶、禁止遠程 root
mysql -uroot -p'TempPass#123'
ALTER USER 'root'@'localhost' IDENTIFIED BY 'StrongPass@2024';
DELETE FROM mysql.user WHERE User='';
FLUSH PRIVILEGES;
/etc/my.cnf[mysqld]
# 基礎設置
server-id = 1
port = 3306
bind-address = 0.0.0.0 # 監聽所有網卡
datadir = /data/mysql
socket = /var/lib/mysql/mysql.sock
# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
# Binlog 核心配置
log-bin = /data/mysql/binlog/mysql-bin
binlog-format = ROW # 推薦 ROW 格式,數據一致性最強
expire_logs_days = 7 # 自動清理 7 天前 Binlog
max_binlog_size = 100M
binlog_rows_query_log_events = ON # 記錄原始 SQL,便於審計
# InnoDB 優化
innodb_buffer_pool_size = 2G # 物理內存 60-70%
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1 # 雙 1 配置,保證數據零丟失
# 連接優化
max_connections = 1000
max_connect_errors = 100000
skip-name-resolve # 跳過 DNS 解析,加速連接
CREATE USER 'repl_user'@'10.4.5.%' IDENTIFIED BY 'ReplPass@2024';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'10.4.5.%';
FLUSH PRIVILEGES;
# 查看主庫 Binlog 位置
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 412 | | |
+------------------+----------+--------------+------------------+
/etc/my.cnf[mysqld]
server-id = 2
port = 3306
bind-address = 0.0.0.0
datadir = /data/mysql
# Relay Log 配置
relay-log = /data/mysql/relay/relay-bin
relay-log-index = /data/mysql/relay/relay-bin.index
log-slave-updates = ON # 級聯從庫必開
read-only = ON # 防止誤寫入
# 複製優化(MySQL 5.7+)
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 4 # 並行複製線程數
master-info-repository = TABLE
relay-log-info-repository = TABLE
relay-log-recovery = ON # 防止 Relay Log 損壞
CHANGE MASTER TO
MASTER_HOST = '10.4.14.168',
MASTER_PORT = 3306,
MASTER_USER = 'repl_user',
MASTER_PASSWORD = 'ReplPass@2024',
MASTER_LOG_FILE = 'mysql-bin.000003',
MASTER_LOG_POS = 412,
MASTER_AUTO_POSITION = 0, # GTID 模式設為 1
GET_MASTER_PUBLIC_KEY = 1; # caching_sha2_password 必需
START SLAVE;
# 檢查狀態
SHOW SLAVE STATUS\G
# 關鍵指標:
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
# Seconds_Behind_Master: 0
# 主庫建表插數
CREATE DATABASE test_repl;
USE test_repl;
CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(20));
INSERT INTO t1 VALUES (1, 'master_data');
# 從庫查詢
USE test_repl;
SELECT * FROM t1;
# 應返回同樣數據
| 現象 | 可能原因 | 解決方案 |
|---|---|---|
| Slave_IO_Running: Connecting | 網絡不通/防火牆/密碼錯誤 | telnet 測試 3306、檢查 security group、驗證用戶權限 |
| Slave_SQL_Running: No | 數據衝突/主鍵重複/結構不一致 | SHOW SLAVE STATUS 看 Last_Error,跳過錯誤或重構從庫 |
| Seconds_Behind_Master 偏大 | 從庫硬件弱/大事務/並行複製未開 | 升級從庫配置、開啟並行複製、拆分大事務 |
| GTID 模式下報錯 | GTID 集合不匹配 | RESET MASTER/SLAVE 重新同步,或使用 mysqlbinlog 修補 |
在雙方 my.cnf 添加:
gtid_mode = ON
enforce_gtid_consistency = ON
log-slave-updates = ON
配置主從時簡化為:
CHANGE MASTER TO
MASTER_HOST = '10.4.14.168',
MASTER_USER = 'repl_user',
MASTER_PASSWORD = 'ReplPass@2024',
MASTER_AUTO_POSITION = 1;
GTID 實現自動定位,故障切換、多級從庫管理更簡單。
MASTER_SSL=1 配合憑證