mysql_backup.bat
@echo off
set "MYSQL_BIN=C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldump.exe"
set "BACKUP_DIR=E:\backdata"
set "DB_USER=root"
set "DB_PASS=111111"
set "DATABASES=database1 database2"
:: 日期時間格式化 YYYY-MM-DD_HH_MM
for /f "tokens=2 delims==" %%I in ('wmic os get localdatetime /value') do set "DT=%%I"
set "FILENAME=%BACKUP_DIR%\backup_%DT:~0,4%-%DT:~4,2%-%DT:~6,2%_%DT:~8,2%_%DT:~10,2%.sql"
"%MYSQL_BIN%" -u%DB_USER% -p%DB_PASS% --single-transaction --routines --triggers --events --databases %DATABASES% > "%FILENAME%"
:: 可選:7z 壓縮(需安裝 7-Zip)
:: "C:\Program Files\7-Zip\7z.exe" a -tzip "%FILENAME%.zip" "%FILENAME%" && del "%FILENAME%"
cmd.exe,引數 /c E:\scripts\mysql_backup.bat。mysql_cold_backup.bat
@echo off
net stop mysql
xcopy "C:\ProgramData\MySQL\MySQL Server 8.0\Data\activecode\*.*" "D:\db_backup\%date:~0,4%%date:~5,2%%date:~8,2%\" /S /E /Y /I
net start mysql
%date:~0,4%%date:~5,2%%date:~8,2% 相容大多數地區格式(YYYYMMDD)。
[mysqld]
log-bin = D:/backdata/logs/mysql-bin
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 100M
log-error = D:/backdata/logs/error.log
slow_query_log = 1
long_query_time = 2
slow_query_log_file = D:/backdata/logs/slow.log
重啟服務生效。Binlog 記錄所有寫入操作,配合全量備份可恢復到任意時間點。
@echo off
set "BINLOG_DIR=D:\backdata\logs"
set "INCR_DIR=D:\backdata\incremental\%date:~0,4%%date:~5,2%%date:~8,2%"
mkdir "%INCR_DIR%"
xcopy "%BINLOG_DIR%\mysql-bin.*" "%INCR_DIR%\" /D /Y
/D 僅複製新增/變更檔案。
mysql -uroot -p < backup_20240101.sqlmysqlbinlog --start-datetime="2024-01-01 03:00:00" --stop-datetime="2024-01-01 10:00:00" D:\backdata\logs\mysql-bin.000001 | mysql -uroot -p--defaults-extra-file=my.cnf 避免明文/轉義問題。