萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 實時備份實戰:主從同步架構與 Windows 定時備份腳本完整指南

許多團隊僅做定時備份,若發生操作失誤,資料恢復極其困難。本文詳述如何構建 MySQL 主從同步實現實時備份,並結合 Windows 批次腳本與排程任務對主庫進行定時全量備份,打造雙重保障的資料安全體系。

一、環境規劃與前置條件

角色IP 位址說明
Master MySQL192.168.1.253主庫,開啟 Binlog,提供寫入服務
Slave MySQL192.168.1.254從庫,同步複制主庫資料,作為實時熱備

前置作業:

  • 兩台伺服器均安裝 MySQL(可用免安裝版)
  • 主庫開啟二進制日誌(log-bin)並設定唯一 server-id
  • 從庫設定唯一 server-id
  • 主庫建立具備 REPLICATION SLAVE 權限的同步帳號
  • 同步前記錄主庫 Binlog 文件名與位置(MASTER_LOG_FILE, MASTER_LOG_POS)
  • 同步前確保主從資料一致(可用 mysqldump 完整備份主庫並恢復到從庫)

二、主庫配置步驟

1. 完整備份主庫資料

mysqldump -u root -p --all-databases --lock-all-tables > G:/dbdump.sql

2. 修改配置檔啟用 Binlog

停止服務,編輯 my.ini(或 my.cnf)在 [mysqld] 區段添加:

[mysqld]
log-bin=mysql-bin
server-id=1

重啟服務:

net stop mysql
net start mysql

3. 建立同步複製帳號

mysql -u root -p
CREATE USER 'slave'@'%' IDENTIFIED BY 'slaveAdmin';
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
FLUSH PRIVILEGES;

4. 獲取主庫 Binlog 位置

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 9876212  |              |                  |
+------------------+----------+--------------+------------------+

記下 File 與 Position,從庫配置時需用到。

三、從庫配置步驟

1. 修改配置檔設定 Server ID

[mysqld]
server-id=2

重啟服務。從庫不強制開啟 Binlog,但開啟可用於自身備份恢復。

2. 恢復主庫備份至從庫

mysql -u root -p < D:/dbdump.sql

3. 配置從庫連接主庫並啟動同步

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,檢查密碼、網路或重新配置。

4. 驗證同步

在主庫建立資料庫 CREATE DATABASE vforbox;,從庫執行 SHOW DATABASES; 即可看到同步過來的新庫。

四、Windows 定時全量備份腳本

為防主庫故障或邏輯錯誤,輔以定時全量備份。新建 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  :: 日誌檔

備份核心邏輯

  1. 檢查必要參數是否定義
  2. 建立日期子目錄
  3. 遍歷指定資料庫:
  • 執行 mysqldump 導出(支援 --single-transaction 確保 InnoDB 一致性)
  • 呼叫 WinRAR 壓縮為 .rar
  • 刪除原始 .sql 檔
  • 記錄備份結果與大小到日誌
  • 可選:郵件通知、異地傳輸(SCP)

關鍵 mysqldump 參數說明

參數用途
--single-transactionInnoDB 熱備份關鍵,啟動事務確保一致性視圖,不阻塞寫入
--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 定期歸檔與定期恢復演練,確保備份真正可用。

copyright © 萬盛學電腦網 all rights reserved