此批处理可以备份mysql数据库并压缩,需要手动替换批处理中的mysqldump路径、帐号、密码及相应的库名称。适用于所有win主机。
@echo off set YE=%date:~2,2% set MO=%date:~5,2% set DA=%date:~8,2% set DG=1 set/a vY1=%YE% %% 400 set/a vY2=%YE% %% 4 set/a vY3=%YE% %% 100 if %vY1%==0 (set var=true) else (if %vY2%==0 (if %vY3%==0 (set var=false) else (set var=true)) else (set var=false)) set LY=%YE% set LM=%MO% if %MO:~0,1%==0 (set MO=%MO:~1,1%) if %DA:~0,1%==0 (set DA=%DA:~1,1%) if %DA% GTR %DG% (set/a LD=%DA%-%DG%) else ( if %MO%==1 (set/a LY=%YE%-1) & (set/a LM=12+%MO%-1) & (set/a LD=31+%DA%-%DG%) else ( set/a LM=%MO%-1 if %MO%==3 (if %var%==false (set/a LD=28+%DA%-%DG%) else (set/a LD=29+%DA%-%DG%)) for %%a in (2 4 6 8 9 11) do (if "%MO%"=="%%a" (set/a LD=31+%DA%-%DG%)) for %%b in (5 7 8 10 12) do (if "%MO%"=="%%b" (set/a LD=30+%DA%-%DG%)))) if %LM% LSS 10 set LM=0%LM:~-1% if %LD% LSS 10 set LD=0%LD:~-1% set Han= set Han=%LY%%LM%%LD% "D:/mysql/mysql5.6/bin/mysqldump" -uroot -pwhsir whsir > e:\mysqldump\whsir%Han%.sql "C:\Program Files\WinRAR\WinRAR.exe" a -k -s -m3 -df e:\mysqldump\whsir%Han%.rar e:\mysqldump\whsir%Han%.sql
|----------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | @echo off set YE=%date:~2,2% set MO=%date:~5,2% set DA=%date:~8,2% set DG=1 set/a vY1=%YE% %% 400 set/a vY2=%YE% %% 4 set/a vY3=%YE% %% 100 if %vY1%==0 (set var=true) else (if %vY2%==0 (if %vY3%==0 (set var=false) else (set var=true)) else (set var=false)) set LY=%YE% set LM=%MO% if %MO:~0,1%==0 (set MO=%MO:~1,1%) if %DA:~0,1%==0 (set DA=%DA:~1,1%) if %DA% GTR %DG% (set/a LD=%DA%-%DG%) else ( if %MO%==1 (set/a LY=%YE%-1) & (set/a LM=12+%MO%-1) & (set/a LD=31+%DA%-%DG%) else ( set/a LM=%MO%-1 if %MO%==3 (if %var%==false (set/a LD=28+%DA%-%DG%) else (set/a LD=29+%DA%-%DG%)) for %%a in (2 4 6 8 9 11) do (if "%MO%"=="%%a" (set/a LD=31+%DA%-%DG%)) for %%b in (5 7 8 10 12) do (if "%MO%"=="%%b" (set/a LD=30+%DA%-%DG%)))) if %LM% LSS 10 set LM=0%LM:~-1% if %LD% LSS 10 set LD=0%LD:~-1% set Han= set Han=%LY%%LM%%LD% "D:/mysql/mysql5.6/bin/mysqldump" -uroot -pwhsir whsir > e:\mysqldump\whsir%Han%.sql "C:\Program Files\WinRAR\WinRAR.exe" a -k -s -m3 -df e:\mysqldump\whsir%Han%.rar e:\mysqldump\whsir%Han%.sql |
注意当前系统是否装了winrar,能否找到C:\Program Files\WinRAR\WinRAR.exe,如果winrar装到了别的路径,自行修改路径。
D:/mysql/mysql5.6/bin/mysqldump是你当前mysql的路径,我这里是将whsir的数据库备份到了e盘下mysqldump文件夹。
数据库压缩后的格式为whsir170303.rar
防止文章出现乱码,附一份文件。
https://down.whsir.com/downloads/whsirmysqldump.bat
为了防止数据库的不断增长,也要做一个删除的批处理
@echo off set YE=%date:~2,2% set MO=%date:~5,2% set DA=%date:~8,2% set DG=7 set/a vY1=%YE% %% 400 set/a vY2=%YE% %% 4 set/a vY3=%YE% %% 100 if %vY1%==0 (set var=true) else (if %vY2%==0 (if %vY3%==0 (set var=false) else (set var=true)) else (set var=false)) set LY=%YE% set LM=%MO% if %MO:~0,1%==0 (set MO=%MO:~1,1%) if %DA:~0,1%==0 (set DA=%DA:~1,1%) if %DA% GTR %DG% (set/a LD=%DA%-%DG%) else ( if %MO%==1 (set/a LY=%YE%-1) & (set/a LM=12+%MO%-1) & (set/a LD=31+%DA%-%DG%) else ( set/a LM=%MO%-1 if %MO%==3 (if %var%==false (set/a LD=28+%DA%-%DG%) else (set/a LD=29+%DA%-%DG%)) for %%a in (2 4 6 8 9 11) do (if "%MO%"=="%%a" (set/a LD=31+%DA%-%DG%)) for %%b in (5 7 8 10 12) do (if "%MO%"=="%%b" (set/a LD=30+%DA%-%DG%)))) if %LM% LSS 10 set LM=0%LM:~-1% if %LD% LSS 10 set LD=0%LD:~-1% set Han= set Han=%LY%%LM%%LD% del e:\mysqldump\whsir%Han%.rar /Q /F
|-------------------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | @echo off set YE=%date:~2,2% set MO=%date:~5,2% set DA=%date:~8,2% set DG=7 set/a vY1=%YE% %% 400 set/a vY2=%YE% %% 4 set/a vY3=%YE% %% 100 if %vY1%==0 (set var=true) else (if %vY2%==0 (if %vY3%==0 (set var=false) else (set var=true)) else (set var=false)) set LY=%YE% set LM=%MO% if %MO:~0,1%==0 (set MO=%MO:~1,1%) if %DA:~0,1%==0 (set DA=%DA:~1,1%) if %DA% GTR %DG% (set/a LD=%DA%-%DG%) else ( if %MO%==1 (set/a LY=%YE%-1) & (set/a LM=12+%MO%-1) & (set/a LD=31+%DA%-%DG%) else ( set/a LM=%MO%-1 if %MO%==3 (if %var%==false (set/a LD=28+%DA%-%DG%) else (set/a LD=29+%DA%-%DG%)) for %%a in (2 4 6 8 9 11) do (if "%MO%"=="%%a" (set/a LD=31+%DA%-%DG%)) for %%b in (5 7 8 10 12) do (if "%MO%"=="%%b" (set/a LD=30+%DA%-%DG%)))) if %LM% LSS 10 set LM=0%LM:~-1% if %LD% LSS 10 set LD=0%LD:~-1% set Han= set Han=%LY%%LM%%LD% del e:\mysqldump\whsir%Han%.rar /Q /F |
附一份删除的批处理
https://down.whsir.com/downloads/delwhsirmysqldump.bat