應用層:寫操作走 Master,讀操作走 Slave(可通過中間件如 ProxySQL、MyCat 實現自動路由)。
/etc/my.cnf[mysqld]
server-id=1
log-bin=mysql-bin
binlog_format=mixed
binlog-do-db=test
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
sync_binlog=1
expire_logs_days=7
/etc/my.cnf[mysqld]
server-id=2
log-bin=mysql-bin
binlog_format=mixed
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema
replicate-do-db=test
sync_binlog=1
expire_logs_days=7
read_only=1 # 防誤寫入(可選)
relay_log=relay-bin # 顯式指定中繼日志名稱
修改後雙方重啟 MySQL。
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.204.134' IDENTIFIED BY 'repl_pass';
FLUSH PRIVILEGES;
⚠️ 生產環境請用專用帳號、強密碼、限制來源 IP。
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+-------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+-------------------------------+
| mysql-bin.000026 | 302 | test | mysql,performance_schema |
+------------------+----------+--------------+-------------------------------+
記錄 File 與 Position。
CHANGE MASTER TO
MASTER_HOST='192.168.204.132',
MASTER_USER='repl',
MASTER_PASSWORD='repl_pass',
MASTER_LOG_FILE='mysql-bin.000026',
MASTER_LOG_POS=302;
STOP SLAVE;
START SLAVE;
mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.204.132
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000026
Read_Master_Log_Pos: 302
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000026
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test
Replicate_Ignore_DB: mysql,performance_schema
Seconds_Behind_Master: 0
關鍵指標:Slave_IO_Running: Yes、Slave_SQL_Running: Yes、Seconds_Behind_Master: 0。
UUID()、NOW())導致主從不一致。gtid_mode=ON,簡化故障轉移與拓撲變更。Seconds_Behind_Master、Slave_*_Running、Last_Error。mysqldump --single-transaction --master-data=2 不影響主庫。