Windows批处理脚本来备份本地MySQL数据库，只保留N个最新的文件夹备份文件

我正在使用adityasatrio的batch file来备份本地MySQL数据库，并希望能够只保留30个最新的备份文件。使用root：这个例子的根。

@echo off set dbUser=root set dbPassword=root set backupDir="D:\MySQLDumps\dbs\" set mysqldump="C:\wamp\bin\mysql\mysql5.6.17\bin\mysqldump.exe" set mysqlDataDir="C:\wamp\bin\mysql\mysql5.6.17\data" set zip="C:\Program Files\7-Zip\7z.exe" :: get date for /F "tokens=2-4 delims=/ " %%i in ('date /t') do ( set yy=%%i set mon=%%j set dd=%%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

现在我已经看了下面的问题：

批文件删除N天以前的文件

https://serverfault.com/questions/49614/delete-files-older-than-x-days

将最新的7个文件保存在一个文件夹中的batch file

Windowsbatch file只保留最后的30个文件

我现在想知道如果我可以简单地添加（ https://stackoverflow.com/a/14267137/1010918 ）

 for /f "skip=30 delims=" %%A in ('dir /a:-d /b /o:-d /t:c *.sql ^2^>nul') do if exist "%%~fA" echo "%%~fA"

（是的，我会稍后改变回声del，但首先我想看看会发生什么）

或（ https://stackoverflow.com/a/13368077 ）

 for /f "skip=30 eol=: delims=" %%F in ('dir /b /od *.sql') do @del "%%F"

在batch file的最后，右边

  del %backupDir%\%dirName%\%%f.sql

要做到这一点？

我从来没有这样做过，但已经search了MySQL数据库的自动本地备份应用程序/ PHP脚本/ mysqldump命令/等，甚至有一个去与工作台只发现没有任何日程安排可以设置在社区版（谢谢你甲骨文）。

所有其他应用程序或者需要有人打开应用程序，并点击“立即运行”或要求您付费设置时间表（不，谢谢）。

我认为这可以通过Windows 7和更高版本的机器上的工具完成。请帮助我将这个function添加到脚本，这将是伟大的，谢谢。

EDIT1：

当添加quote命令时，什么都不会发生。另外，创build的备份目录仅显示时间，但不显示年，月和日。做进一步的研究，找出原因。有任何想法吗？

这个评论删除所有，但最近的文件夹谈到删除5个最新的文件夹，虽然当我这样使用它

for /f "skip=2 delims=" %%a in ('dir %backupDir%\%dirName% /od /b') do rd /S /Q "%backupDir%\%dirName%\%%a"

错误如下。

 The system cannot find the file specified. The system cannot find the path specified.

编辑2：下面是@foxidrive帮助设置文件夹名称的代码，因为我喜欢它，但最后一点，试图只保留3个最新文件夹（仅用于testing目的3），并删除其余的文件夹备份目录似乎没有解决。

感谢您的任何帮助。

  @echo off set dbUser=root set dbPassword=root set "backupDir=D:\MySQLDumps\dbs\" set "mysqldump=C:\wamp\bin\mysql\mysql5.6.17\bin\mysqldump.exe" set "mysqlDataDir=C:\wamp\bin\mysql\mysql5.6.17\data" set "zip=C:\Program Files\7-Zip\7z.exe" rem The four lines below will give you reliable YY DD MM YYYY HH Min Sec MS variables in XP Pro and higher. for /f "tokens=2 delims==" %%a in ('wmic OS Get localdatetime /value') do set "dt=%%a" set "YY=%dt:~2,2%" & set "YYYY=%dt:~0,4%" & set "MM=%dt:~4,2%" & set "DD=%dt:~6,2%" set "HH=%dt:~8,2%" & set "Min=%dt:~10,2%" & set "Sec=%dt:~12,2%" & set "MS=%dt:~15,3%" set "dirname=%YY%-%MM%-%DD% %HH%-%Min%-%Sec%" echo "dirName"="%dirName%" pause :: switch to the "data" folder pushd "%mysqlDataDir%" :: create backup folder if it doesn't exist if not exist "%backupDir%\%dirName%\" mkdir "%backupDir%\%dirName%" :: iterate over the folder structure in the "data" folder to get the databases for /d %%f in (*) do ( echo processing folder "%%f" "%mysqldump%" --host="localhost" --user=%dbUser% --password=%dbPassword% --single-transaction --add-drop-table --databases %%f > "%backupDir%\%dirName%\%%~nxf.sql" "%zip%" a -tgzip "%backupDir%\%dirName%\%%~nxf.sql.gz" "%backupDir%\%dirName%\%%~nxf.sql" del "%backupDir%\%dirName%\%%~nxf.sql" ) popd :: delete all but the latest 3 folders for /f "skip=3 delims=" %%A in ('dir /b /ad /on "%backupDir%\%dirName%\*"') do @echo rd /s /q "%backupDir%\%dirName%\%%~A" pause

在上面@foxidrive的帮助下，我设法得到了我想要的那些文件夹的日期，他们是YYYY-MM-DD HH-MIN-SEC。

在这些文件夹中是由adityasatrio的MySQL备份批处理脚本存储的gzip压缩的.sql数据库。

从这个答案的@Magoo的帮助https://stackoverflow.com/a/17521693/1010918我设法让所有的文件夹（nameDir）删除， 同时保持最新的N文件夹（nameDir），也没有触及任何文件，可能是在目录（backupDir）中。

这是完整的工作脚本。

随意删除任何pause和echo发生在命令提示符内部看不到。

另外，将其添加到Windows任务计划程序中，您可以为使用MySQL数据库的本地开发环境提供可靠的备份解决方案。

请感谢帮助我完成这件事的人。没有你们，我不得不使用昂贵的Windows应用程序来本地保存MySQL数据库。

（..和我们的下一个技巧，我们将电子邮件错误日志给自己，如果有错误，同时备份.sql文件..但这是另一个问题和故事另一天..）

  @echo off set dbUser=root set dbPassword=root set "backupDir=D:\MySQLDumps" set "mysqldump=C:\wamp\bin\mysql\mysql5.6.17\bin\mysqldump.exe" set "mysqlDataDir=C:\wamp\bin\mysql\mysql5.6.17\data" set "zip=C:\Program Files\7-Zip\7z.exe" :: https://stackoverflow.com/a/31789045/1010918 foxidrive's answer helped me get the folder with the date and time I wanted rem The four lines below will give you reliable YY DD MM YYYY HH Min Sec MS variables in XP Pro and higher. for /f "tokens=2 delims==" %%a in ('wmic OS Get localdatetime /value') do set "dt=%%a" set "YY=%dt:~2,2%" & set "YYYY=%dt:~0,4%" & set "MM=%dt:~4,2%" & set "DD=%dt:~6,2%" set "HH=%dt:~8,2%" & set "Min=%dt:~10,2%" & set "Sec=%dt:~12,2%" & set "MS=%dt:~15,3%" set "dirname=%YYYY%-%MM%-%DD% %HH%-%Min%-%Sec%" :: remove echo here if you like echo "dirName"="%dirName%" :: switch to the "data" folder pushd "%mysqlDataDir%" :: create backup folder if it doesn't exist if not exist "%backupDir%\%dirName%\" mkdir "%backupDir%\%dirName%" :: iterate over the folder structure in the "data" folder to get the databases for /d %%f in (*) do ( :: remove echo here if you like echo processing folder "%%f" "%mysqldump%" --host="localhost" --user=%dbUser% --password=%dbPassword% --single-transaction --add-drop-table --databases %%f > "%backupDir%\%dirName%\%%~nxf.sql" "%zip%" a -tgzip "%backupDir%\%dirName%\%%~nxf.sql.gz" "%backupDir%\%dirName%\%%~nxf.sql" del "%backupDir%\%dirName%\%%~nxf.sql" ) popd :: delete all folders but the latest 2 :: https://stackoverflow.com/a/17521693/1010918 Magoo's answer helped me get what I wanted to do with the folders :: for /f "skip=2 delims=" %G in ('dir /B /ad-h /o-d') DO echo going to delete %G :: below following my version with rd (remove dir) command and /s and /q :: remove echo before rd to really delete the folders in question!! :: attention they will be deleted with content in them!! :: change the value after skip= to what you like, this is the amount of latest folders to keep in your backup directory for /f "skip=2 delims=" %%a in (' dir "%backupDir%\" /b /ad-h /o-d') do echo rd /s /q "%backupDir%\%%a" :: remove pause here if you like and add the file to Windows Task Manager pause

这对文件夹名称中的空格稍有弹性，并且日期和时间例程已被更改
– 运行它，并首先检查"dirName"=文件夹是在正确的格式
– 最后一行应该回显del命令以保留最新的3个备份。

测试归档例程，然后
删除del关键字之前的echo ，如果一切看起来合适。

  @echo off set dbUser=root set dbPassword=root set "backupDir=D:\MySQLDumps\dbs\" set "mysqldump=C:\wamp\bin\mysql\mysql5.6.17\bin\mysqldump.exe" set "mysqlDataDir=C:\wamp\bin\mysql\mysql5.6.17\data" set "zip=C:\Program Files\7-Zip\7z.exe" rem The four lines below will give you reliable YY DD MM YYYY HH Min Sec MS variables in XP Pro and higher. for /f "tokens=2 delims==" %%a in ('wmic OS Get localdatetime /value') do set "dt=%%a" set "YY=%dt:~2,2%" & set "YYYY=%dt:~0,4%" & set "MM=%dt:~4,2%" & set "DD=%dt:~6,2%" set "HH=%dt:~8,2%" & set "Min=%dt:~10,2%" & set "Sec=%dt:~12,2%" & set "MS=%dt:~15,3%" set "dirname=%YY%%MM%%DD%_%HH%%Min%" echo "dirName"="%dirname%" pause :: switch to the "data" folder pushd "%mysqlDataDir%" :: create backup folder if it doesn't exist if not exist "%backupDir%\%dirName%\" mkdir "%backupDir%\%dirName%" :: iterate over the folder structure in the "data" folder to get the databases for /d %%f in (*) do ( echo processing folder "%%f" "%mysqldump%" --host="localhost" --user=%dbUser% --password=%dbPassword% --single-transaction --add-drop-table --databases %%f > "%backupDir%\%dirName%\%%~nxf.sql" "%zip%" a -tgzip "%backupDir%\%dirName%\%%~nxf.sql.gz" "%backupDir%\%dirName%\%%~nxf.sql" del "%backupDir%\%dirName%\%%~nxf.sql" ) popd ::keep 3 newest backup *.sql files for /f "skip=3 delims=" %%a in ('dir "%backupDir%\%dirName%\*.sql" /b /od /a-d') do echo del "%backupDir%\%dirName%\%%a" pause