Batch script backup database mysql trên windows

Hướng dẫn tạo file batch backup mysql trên windows hỗ trợ cho việc đặt lịch backup database hàng ngày

Cách bạn tạo file backupmysql.bat theo mẫu bên dưới:

^{@echo off}

 ^{set dbUser=root
 set dbPassword=69mTm1EUd6XL
 set backupDir=”Z:\backup\backupmysql\”
 set mysqldump=”C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe”
 set mysqlDataDir=”C:\ProgramData\MySQL\MySQL Server 5.7\Data”
 set zip=”C:\Program Files\7-Zip\7z.exe”}

 ^{:: get date
 for /F “tokens=2-4 delims=/ ” %%i in (‘date /t’) do (
      set mon=%%i
      set dd=%%j
      set yy=%%k
 )}

 ^{:: get time
 for /F “tokens=5-8 delims=:. ” %%i in (‘echo.^| time ^| find “current” ‘) do (
      set hh=%%i
      set min=%%j
 )}

 ^{echo dirName=%yy%%mon%%dd%_%hh%%min%
 set dirName=%yy%%mon%%dd%_%hh%%min%
 
 
 :: switch to the “data” folder
 pushd %mysqlDataDir%}

 ^{:: iterate over the folder structure in the “data” folder to get the databases
 for /d %%f in (*) do (}

 ^{if not exist %backupDir%\%dirName%\ (
      mkdir %backupDir%\%dirName%
 )}

 ^{%mysqldump% –host=”localhost” –user=%dbUser% –password=%dbPassword% –single-transaction –add-drop-table –databases %%f > %backupDir%\%dirName%\%%f.sql}

 ^{%zip% a -tgzip %backupDir%\%dirName%\%%f.sql.gz %backupDir%\%dirName%\%%f.sql}

 ^{del %backupDir%\%dirName%\%%f.sql
 )
 popd
 
 for /F “skip=7 delims=” %%D in (‘dir “%backupDir%” /AD /B /O-D 2^>nul’) do (
    rd /Q /S “%backupDir%\%%D”
)}

Chúc các bạn thành công.