最精簡的 MySQL 主從搭建步驟:雙節點版本一致、主庫開 Binlog + server-id、從庫開 Relay Log + server-id、建立復制賬號、CHANGE MASTER TO 定位坐標、START SLAVE、雙 Yes 驗證、測試同步。附監控腳本思路。
Relay_Log_Pos、Exec_Master_Log_Pos 實現斷點續傳/etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=222 # 唯一,建議 IP 尾段
systemctl restart mysqld
/etc/my.cnf
[mysqld]
log-bin=mysql-bin # 可選,便於級聯從庫
server-id=226
relay-log=relay-bin # 啟用中繼日誌
systemctl restart mysqld
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl_pass';
FLUSH PRIVILEGES;
生產建議將 %' 改為從庫具體 IP。
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 308 | | |
+------------------+----------+--------------+------------------+
記住 File 與 Position,後續不可在主庫寫入新數據,否則坐標變化。
CHANGE MASTER TO
MASTER_HOST='192.168.145.222',
MASTER_USER='repl',
MASTER_PASSWORD='repl_pass',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=308;
START SLAVE;
SHOW SLAVE STATUS \G;
必須同時為 Yes:
Slave_IO_Running: YesSlave_SQL_Running: Yes若任一為 No,查看 Last_IO_Error/Last_SQL_Error 排查。
-- 主庫
CREATE DATABASE hi_db;
USE hi_db;
CREATE TABLE hi_tb(id INT, name CHAR(10));
INSERT INTO hi_tb VALUES(1, 'bobu');
-- 從庫
SHOW DATABASES; -- 見 hi_db
USE hi_db;
SELECT * FROM hi_tb; -- 見 (1, 'bobu')
#!/bin/bash
# check_slave.sh 每分鐘跑一次
STATUS=$(mysql -urepl -prepl_pass -e "SHOW SLAVE STATUS\G" | grep -E "Slave_IO_Running|Slave_SQL_Running" | awk '{print $2}')
if [[ "$STATUS" != "Yes Yes" ]]; then
echo "MySQL Slave 異常" | mail -s "告警" dba@company.com
# 或發送鉀釘/企微/短信
fi
納入 Nagios/Zabbix/Prometheus 監控體系,實現秒級發現主從中斷。