許多團隊僅做定時備份,若發生操作失誤,資料恢復極其困難。本文詳述如何構建 MySQL 主從同步實現實時備份,並結合 Windows 批次腳本與排程任務對主庫進行定時全量備份,打造雙重保障的資料安全體系。
| 角色 | IP 位址 | 說明 |
|---|---|---|
| Master MySQL | 192.168.1.253 | 主庫,開啟 Binlog,提供寫入服務 |
| Slave MySQL | 192.168.1.254 | 從庫,同步複制主庫資料,作為實時熱備 |
前置作業:
log-bin)並設定唯一 server-idserver-idREPLICATION SLAVE 權限的同步帳號MASTER_LOG_FILE, MASTER_LOG_POS)mysqldump 完整備份主庫並恢復到從庫)mysqldump -u root -p --all-databases --lock-all-tables > G:/dbdump.sql
停止服務,編輯 my.ini(或 my.cnf)在 [mysqld] 區段添加:
[mysqld]
log-bin=mysql-bin
server-id=1
重啟服務:
net stop mysql
net start mysql
mysql -u root -p
CREATE USER 'slave'@'%' IDENTIFIED BY 'slaveAdmin';
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
FLUSH PRIVILEGES;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 9876212 | | |
+------------------+----------+--------------+------------------+
記下 File 與 Position,從庫配置時需用到。
[mysqld]
server-id=2
重啟服務。從庫不強制開啟 Binlog,但開啟可用於自身備份恢復。
mysql -u root -p < D:/dbdump.sql
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.1.253',
-> MASTER_USER='slave',
-> MASTER_PASSWORD='slaveAdmin',
-> MASTER_LOG_FILE='mysql-bin.000004',
-> MASTER_LOG_POS=9876212;
Query OK, 0 rows affected (0.00 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
關鍵指標:Slave_IO_Running 和 Slave_SQL_Running 必須均為 Yes 才表示同步正常。若 IO 為 No,檢查密碼、網路或重新配置。
在主庫建立資料庫 CREATE DATABASE vforbox;,從庫執行 SHOW DATABASES; 即可看到同步過來的新庫。
為防主庫故障或邏輯錯誤,輔以定時全量備份。新建 mysql_auto_bak.bat,配合 Windows 排程任務定時執行(如每天凌晨)。
@echo off
cls
color 3E
title %date% %time:~,5% 備份MySQL數據庫 By:vforbox
:: 以下為需自訂參數
SET MySQL_pw=123456 :: root 密碼,特殊符號前加雙 ^
SET BAK_dir=D:\mysqlbak :: 備份根目錄
SET BAK_db_myisam=myisam_db :: 需備份的 MyISAM 庫(多個用空格分隔)
SET BAK_db_innodb=innodb :: 需備份的 InnoDB 庫
SET RAR_dir="C:\Program Files (x86)\WinRAR\WinRAR.exe" :: WinRAR 路徑
SET BAK_dir2=%date:~0,4%-%date:~5,2%-%date:~8,2% :: 以日期為子目錄
SET BAK_file=%%i_%BAK_dir2%.sql :: 備份 SQL 檔名
SET BAK_file2=%%i_%BAK_dir2% :: 日誌中引用的 rar 路徑
SET LOG_file=%BAK_dir%\%BAK_dir2%\Mysql_bak.log :: 日誌檔
mysqldump 導出(支援 --single-transaction 確保 InnoDB 一致性).rar.sql 檔| 參數 | 用途 |
|---|---|
--single-transaction | InnoDB 熱備份關鍵,啟動事務確保一致性視圖,不阻塞寫入 |
--flush-logs | 刷新 Binlog,便於增量恢復定位 |
--triggers -R | 包含觸發器、存儲過程/函數 |
--opt | 快捷選項組合(含 --quick、--extended-insert 等),加速導出導入 |
--hex-blob | 二進位欄位用十六進位導出,避免編碼問題 |
MAILX_OPEN_CLOSE=1 並配置 contacts_list.txt(每行一個郵箱),備份失敗/成功發送通知OPEN_TRANS_DATA=1 配置 SSH 目標伺服器資訊,備份完成自動 SCP 傳輸並檢查遠端磁碟空間backup_list.txt 支援指定庫、指定表、排除表(--ignore-table)本方案結合主從同步(實時熱備)+ 定時全量備份(歷史快照)+ 異地傳輸(災難恢復),構建了完整的 MySQL 資料保護體系。主從同步保障秒級 RPO,定時備份提供任意時間點恢復能力,腳本自動化降低運維負擔。生產環境建議再加上 Binlog 定期歸檔與定期恢復演練,確保備份真正可用。