編寫msyql8.0.21 數(shù)據(jù)庫批量備份腳本

一：編寫mysql數(shù)據(jù)庫備份my.cnf文件

二、編寫數(shù)據(jù)庫導(dǎo)出腳本

czywxt_nacos.bat

@echo off
chcp 65001 > nul
title MySQL Backup for czywxt_nacos
setlocal disabledelayedexpansion

:: 配置項(xiàng)（絕對(duì)路徑）
set "MYSQL_BIN=H:\DataBaseSystem\mysql-8.0.21-winx64-s\bin"
set "DATABASE=czywxt_nacos"
set "BACKUP_DIR=G:\MySQLBackups"
set "PASSWORD_FILE=H:\DataBaseSystem\mysql-8.0.21-winx64-s\bin\my.cnf"
set "7ZIP_PATH=C:\Program Files\7-Zip\7z.exe"
set "CLEAN_PS_SCRIPT=H:\DataBaseSystem\mysql-8.0.21-winx64-s\DatabaseBackUpBat\CleanOldBackups.ps1"


:: 步驟1：檢查MySQL連接
echo Checking MySQL server connection status...
"%MYSQL_BIN%\mysqladmin.exe" --defaults-file="%PASSWORD_FILE%" ping > nul 2>&1
if %errorlevel% neq 0 (
    echo Error: MySQL server is not running or connection failed.
    endlocal
    exit /b 1
)
echo MySQL server is connectable.


:: 步驟2：生成時(shí)間戳（用PowerShell避免批處理語法沖突）
for /f "delims=" %%a in ('powershell -Command "(Get-Date).ToString('yyyy-MM-dd_HHmmss')"') do set "TIMESTAMP=%%a"


:: 步驟3：創(chuàng)建備份目錄
if not exist "%BACKUP_DIR%" (
    mkdir "%BACKUP_DIR%"
    echo Backup directory created: %BACKUP_DIR%
) else (
    echo Using existing backup directory: %BACKUP_DIR%
)


:: 步驟4：執(zhí)行備份
echo Starting backup for database: %DATABASE%
set "SQL_FILE=%BACKUP_DIR%\%DATABASE%_%TIMESTAMP%.sql"
"%MYSQL_BIN%\mysqldump.exe" --defaults-file="%PASSWORD_FILE%" --databases "%DATABASE%" --result-file="%SQL_FILE%"
if %errorlevel% neq 0 (
    echo Error: mysqldump failed.
    endlocal
    exit /b 1
)
echo SQL backup created: %SQL_FILE%


:: 步驟5：壓縮備份
set "ZIP_FILE=%BACKUP_DIR%\%DATABASE%_%TIMESTAMP%.zip"
:: 嘗試PowerShell壓縮
powershell Compress-Archive -Path "%SQL_FILE%" -DestinationPath "%ZIP_FILE%" -Force 2> nul
if %errorlevel% equ 0 (
    echo Compressed via PowerShell: %ZIP_FILE%
    del "%SQL_FILE%"
    echo Deleted original SQL file: %SQL_FILE%
) else (
    :: 嘗試7-Zip
    if exist "%7ZIP_PATH%" (
        "%7ZIP_PATH%" a -tzip "%ZIP_FILE%" "%SQL_FILE%" -y > nul
        if %errorlevel% equ 0 (
            echo Compressed via 7-Zip: %ZIP_FILE%
            del "%SQL_FILE%"
            echo Deleted original SQL file: %SQL_FILE%
        ) else (
            echo Warning: Compression failed. SQL file retained.
            goto CLEANUP
        )
    ) else (
        echo Warning: 7-Zip not found. SQL file retained.
        goto CLEANUP
    )
)


:: 步驟6：清理舊備份
:CLEANUP
echo Cleaning up backup files older than 10 days...
powershell -ExecutionPolicy Bypass -File "H:\DataBaseSystem\mysql-8.0.21-winx64-s\DatabaseBackUpBat\CleanOldBackups.ps1" "G:\MySQLBackups" "czywxt_nacos"


:: 步驟7：完成提示（刪除pause，自動(dòng)退出）
echo --------------------------------------------------
if exist "%ZIP_FILE%" (
    echo Backup completed successfully.
    echo Current backup: %ZIP_FILE%
) else (
    echo Backup completed with warnings.
    echo SQL file retained: %SQL_FILE%
)
echo Operation time: %TIMESTAMP%

endlocal

CleanOldBackups.ps1

# 接收批處理傳遞的2個(gè)參數(shù)：1.備份目錄 2.數(shù)據(jù)庫名
param(
    [string]$BackupDir,
    [string]$DbName
)

# 清理10天前的指定數(shù)據(jù)庫ZIP備份
$oldDate = (Get-Date).AddDays(-10)
Get-ChildItem -Path $BackupDir -Filter "$DbName_*.zip" -File | Where-Object { $_.CreationTime -lt $oldDate } | ForEach-Object {
    Remove-Item $_ -Force
    Write-Host "Deleted old backup: $($_.FullName)"
}

Main_backUp.bat

@echo off
:: 設(shè)置控制臺(tái)為UTF-8編碼，避免中文亂碼
:: Set console to UTF-8 encoding to avoid garbled characters
chcp 65001 > nul
:: echo 開始批量執(zhí)行數(shù)據(jù)庫備份腳本...
echo Starting batch execution of database backup scripts...

:: 定義待執(zhí)行腳本所在的子文件夾（相對(duì)路徑）
:: Define the subfolder where the scripts to be executed are stored (relative path)
set "TARGET_FOLDER=newBak"

:: 遍歷子文件夾中的所有 .bat 文件
:: Traverse all .bat files in the subfolder
for %%f in ("%TARGET_FOLDER%\*.bat") do (
::  echo 正在執(zhí)行腳本：%%f
    echo Executing script: %%f
    :: Call the sub-script (wait for it to complete before executing the next one)
	:: 調(diào)用子腳本（等待其執(zhí)行完畢后再執(zhí)行下一個(gè)）
    call "%%f"
	:: echo 腳本 %%f 執(zhí)行完畢，等待 5 秒繼續(xù)下一個(gè)...
    echo Script %%f execution completed, waiting 5 seconds for the next one...
    :: Pause for 5 seconds to reduce server/database load
	:: 暫停5秒，減輕服務(wù)器/數(shù)據(jù)庫壓力
    timeout /t 5 /nobreak > nul
)

:: echo 所有備份腳本執(zhí)行完畢！
echo All backup scripts have been executed!

三、用java diam批量生成數(shù)據(jù)庫bat腳本

package Test;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.List;
/**
 * 編寫mysql 數(shù)據(jù)庫 備份的bat腳本
 */
public class GenerateBatFiles {
    public static void main(String[] args) {
        // ==================== 1. 配置路徑與參數(shù) ====================
        String templateFilePath = "H:\\DataBaseSystem\\mysql-8.0.21-winx64-s\\DatabaseBackUpBat\\czywxt_nacos.bat";
        String outputDirectory = "H:\\DataBaseSystem\\mysql-8.0.21-winx64-s\\DatabaseBackUpBat\\newBak";
        String originalDbName = "czywxt_nacos"; // 模板中要替換的`set DATABASE=`原始值
        String originalTitleDb = "czywxt_nacos"; // 模板中`title`里的原始數(shù)據(jù)庫名
        // 固定備份目錄（與模板中一致，無需修改）
        String fixedBackupDir = "G:\\MySQLBackups";
        // 固定PS清理腳本路徑（與模板中一致，無需修改）
        String fixedCleanPsPath = "H:\\DataBaseSystem\\mysql-8.0.21-winx64-s\\DatabaseBackUpBat\\CleanOldBackups.ps1";


        // ==================== 2. 讀取模板文件內(nèi)容 ====================
        List<String> templateLines = new ArrayList<>();
        try (BufferedReader reader = Files.newBufferedReader(Paths.get(templateFilePath))) {
            String line;
            while ((line = reader.readLine()) != null) {
                templateLines.add(line);
            }
        } catch (IOException e) {
            System.err.println("Failed to read template file: " + e.getMessage());
            return;
        }


        // ==================== 3. 定義要遍歷的數(shù)據(jù)庫名列表 ====================
        List<String> databaseNames = new ArrayList<>();
        // 此處需完整復(fù)制 DatabaseListExample 中的數(shù)據(jù)庫名列表 ↓↓↓
//         databaseNames.add("zoo");
         databaseNames.add("預(yù)算2007");

        // ==================== 4. 遍歷數(shù)據(jù)庫名，生成對(duì)應(yīng)BAT文件 ====================
        for (String targetDbName : databaseNames) {
            Path outputFilePath = Paths.get(outputDirectory, targetDbName + ".bat");
            try (BufferedWriter writer = Files.newBufferedWriter(outputFilePath)) {
                for (String line : templateLines) {
                    // ---- 替換邏輯1：處理title行，動(dòng)態(tài)替換數(shù)據(jù)庫名 ----
                    if (line.trim().startsWith("title") && line.contains(originalTitleDb)) {
                        line = "title MySQL Backup for " + targetDbName + "";
                    }
                    // ---- 替換邏輯2：處理set DATABASE行，動(dòng)態(tài)替換數(shù)據(jù)庫名 ----
                    else if (line.trim().startsWith("set \"DATABASE=" + originalDbName + "\"")) {
                        line = "set \"DATABASE=" + targetDbName + "\"";
                    }

                    // 替換3：清理步驟的PowerShell命令（替換最后一個(gè)數(shù)據(jù)庫名參數(shù)）
                    else if (line.trim().startsWith("powershell -ExecutionPolicy Bypass -File")
                             && line.contains(fixedCleanPsPath)
                             && line.contains(originalDbName)) {
                        // 原命令格式：powershell -ExecutionPolicy Bypass -File "PS路徑" "備份目錄" "原數(shù)據(jù)庫名"
                        // 替換為：powershell -ExecutionPolicy Bypass -File "PS路徑" "備份目錄" "目標(biāo)數(shù)據(jù)庫名"
                        line = String.format(
                            "powershell -ExecutionPolicy Bypass -File \"%s\" \"%s\" \"%s\"",
                            fixedCleanPsPath,
                            fixedBackupDir,
                            targetDbName
                        );
                    }
                    // （`chcp 65001` 會(huì)被自動(dòng)保留，因?yàn)槟０灏@一行，直接寫入新文件）
                    writer.write(line);
                    writer.newLine();
                }
                System.out.println("Successfully generated: " + outputFilePath);
            } catch (IOException e) {
                System.err.println("Failed to generate file " + outputFilePath + ": " + e.getMessage());
            }
        }
    }
}

四、運(yùn)行bat文件

數(shù)據(jù)備份情況

@echo off
setlocal enabledelayedexpansion

:: 配置項(xiàng)（修正路徑拼寫）
set "MYSQL_BIN=H:\DataBaseSystem\mysql-8.0.21-winx64-s\bin"
set "BACKUP_DIR=G:\Document\Mysql_BACKUP_DIR\MySQL_Backups\"  :: 修正 Decument → Document
set "PASSWORD_FILE=%MYSQL_BIN%\my.cnf"

:: 生成時(shí)間戳
for /f "tokens=2-4 delims=/ " %%a in ('date /t') do set "DATE=%%c-%%a-%%b"
set "TIMESTAMP=%DATE%_%time:~0,2%%time:~3,2%%time:~6,2%"

:: 創(chuàng)建備份目錄（若不存在）
if not exist "%BACKUP_DIR%" mkdir "%BACKUP_DIR%"

:: 獲取所有數(shù)據(jù)庫列表（排除系統(tǒng)庫）
"%MYSQL_BIN%\mysql.exe" --defaults-file="%PASSWORD_FILE%" --execute="SHOW DATABASES;" --skip-column-names > "%BACKUP_DIR%\databases.txt"

:: 循環(huán)備份每個(gè)數(shù)據(jù)庫
for /F "tokens=*" %%A in (%BACKUP_DIR%\databases.txt) do (
  if not "%%A"=="information_schema" (
    if not "%%A"=="performance_schema" (
      if not "%%A"=="mysql" (
        if not "%%A"=="sys" (
          echo Backing up %%A...
          :: 執(zhí)行備份（帶密碼文件）
          "%MYSQL_BIN%\mysqldump.exe" --defaults-file="%PASSWORD_FILE%" --databases %%A --result-file="%BACKUP_DIR%\%%A_%TIMESTAMP%.sql"

          :: 檢查備份文件是否存在，再壓縮
          if exist "%BACKUP_DIR%\%%A_%TIMESTAMP%.sql" (
              powershell Compress-Archive -Path "%BACKUP_DIR%\%%A_%TIMESTAMP%.sql" -DestinationPath "%BACKUP_DIR%\%%A_%TIMESTAMP%.zip" -Force
          ) else (
              echo Warning: 備份文件 %%A_%TIMESTAMP%.sql 未找到，跳過壓縮！
          )
        )
      )
    )
  )
)

:: 刪除臨時(shí)文件
del "%BACKUP_DIR%\databases.txt"
echo All backups completed at %TIMESTAMP%
endlocal

BACKUPAll.bat

@echo off
:: 設(shè)置UTF-8編碼，解決中文亂碼問題
chcp 936 >nul 2>&1

setlocal enabledelayedexpansion

:: 配置項(xiàng)（請(qǐng)根據(jù)實(shí)際情況修改）
set "MYSQL_BIN=H:\DataBaseSystem\mysql-8.0.21-winx64-s\bin"
set "BACKUP_DIR=G:\Document\Mysql_BACKUP_DIR\MySQL8.0.21_Backups\"
:: 確保該文件包含正確的登錄信息
set "PASSWORD_FILE=%MYSQL_BIN%\my.cnf"

:: 檢查MySQL是否可連接（替代服務(wù)檢查，支持命令行啟動(dòng)的情況）
echo 正在檢查MySQL服務(wù)器連接狀態(tài)...
"%MYSQL_BIN%\mysqladmin.exe" --defaults-file="%PASSWORD_FILE%" ping >nul 2>&1
if %errorlevel% equ 0 (
    echo MySQL服務(wù)器可正常連接，將進(jìn)行備份操作...
) else (
    echo 錯(cuò)誤：MySQL服務(wù)器未啟動(dòng)或無法連接，請(qǐng)檢查服務(wù)狀態(tài)。
    endlocal
    exit /b 1
)

:: 生成時(shí)間戳（處理上午9點(diǎn)前的顯示問題）
for /f "tokens=2-4 delims=/ " %%a in ('date /t') do set "DATE=%%c-%%a-%%b"
set "HOUR=%time:~0,2%"
:: 將空格替換為0，解決0-9點(diǎn)顯示問題
set "HOUR=!HOUR: =0!"
set "TIMESTAMP=%DATE%_%HOUR%%time:~3,2%%time:~6,2%"

:: 創(chuàng)建備份目錄（若不存在）
if not exist "%BACKUP_DIR%" (
    mkdir "%BACKUP_DIR%"
    echo 已創(chuàng)建備份目錄：%BACKUP_DIR%
)

:: 獲取所有數(shù)據(jù)庫列表（排除系統(tǒng)庫）
"%MYSQL_BIN%\mysql.exe" --defaults-file="%PASSWORD_FILE%" --execute="SHOW DATABASES;" --skip-column-names > "%BACKUP_DIR%\databases.txt"

:: 循環(huán)備份每個(gè)數(shù)據(jù)庫
for /F "tokens=*" %%A in (%BACKUP_DIR%\databases.txt) do (
    :: 排除系統(tǒng)數(shù)據(jù)庫
    if not "%%A"=="information_schema" if not "%%A"=="performance_schema" if not "%%A"=="mysql" if not "%%A"=="sys" (
        echo 正在備份數(shù)據(jù)庫：%%A...
        :: 執(zhí)行備份
        "%MYSQL_BIN%\mysqldump.exe" --defaults-file="%PASSWORD_FILE%" --databases %%A --result-file="%BACKUP_DIR%\%%A_%TIMESTAMP%.sql"

        :: 檢查備份文件并壓縮
        if exist "%BACKUP_DIR%\%%A_%TIMESTAMP%.sql" (
            powershell Compress-Archive -Path "%BACKUP_DIR%\%%A_%TIMESTAMP%.sql" -DestinationPath "%BACKUP_DIR%\%%A_%TIMESTAMP%.zip" -Force
            if !errorlevel! equ 0 (
			:: 壓縮成功后刪除原始SQL文件
                del "%BACKUP_DIR%\%%A_%TIMESTAMP%.sql"
                echo 數(shù)據(jù)庫 %%A 備份并壓縮完成
            ) else (
                echo 警告：數(shù)據(jù)庫 %%A 壓縮失敗，保留原始SQL文件
            )
        ) else (
            echo 錯(cuò)誤：數(shù)據(jù)庫 %%A 備份失敗，未生成備份文件
        )
    )
)

:: 清理臨時(shí)文件
if exist "%BACKUP_DIR%\databases.txt" del "%BACKUP_DIR%\databases.txt"

:: 刪除10天前的備份文件
echo 正在清理10天前的備份文件...
forfiles /p "%BACKUP_DIR%" /s /m *.zip /d -10 /c "cmd /c echo 刪除舊備份：@path && del @path"

echo 所有操作完成，備份時(shí)間：%TIMESTAMP%
endlocal