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

Question

我使用adityasatrio批处理文件备份本地MySQL数据库，并希望只保留30个最新的备份文件。在本例中使用root: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个最新文件保存在文件夹中的批处理文件

Windows批处理文件仅保留最后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 /o-d *.sql') do @del "%%F"

在批处理文件的末尾，就在

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

才能做到这一点？

我以前从来没有这样做过，但是我为MySQL dbs搜索了自动本地备份应用程序/php脚本/mysqldump命令/etc，甚至使用了Workbench，结果发现在社区版本中无法设置任何调度(谢谢Oracle)。

所有其他应用程序要么需要有人打开应用程序，然后点击“立即运行”，要么希望你为设置时间表付费(不，谢谢)。

我认为这可以通过Windows 7和更高版本的机器上的工具来完成。请帮我把这个功能添加到脚本中，这将是很棒的，谢谢。

edit1:

当添加引号命令时，什么都不会发生。此外，创建的备份目录只显示时间，而不显示年份、月和日。做进一步的研究，找出原因。有什么想法吗？

这个注释删除除X以外的所有最新文件夹谈到删除最新的5个文件夹，不过当我这样使用它时

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

错误如下。

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

下面的edit2:是使用@foxidrive设置文件夹名的代码，但是最后一段，尝试只保留最新的3个文件夹(出于测试目的，只删除3个文件夹)和删除backupDir中的其他文件夹似乎没有效果。

谢谢你的帮助。

     @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 /o-n "%backupDir%\%dirName%\*"')  do @echo rd /s /q "%backupDir%\%dirName%\%%~A"

pause

Answer 1

在上面@foxidrive的帮助下，我设法得到了我想要的文件夹的日期，它们是YYYY DD HH。

在这些文件夹中是由于使用.sql而存储的gzipped adityasatrio的MySQL备份批处理脚本数据库。

在这个答案@Magoo的帮助下，https://stackoverflow.com/a/17521693/1010918成功地将所有文件夹(nameDir)删除了，同时保留了最新的N个文件夹(nameDir)和not，这些文件可能位于目录(backupDir)中。

这里是完整的工作脚本.

可以随意删除pause和和echo的任何出现到而不是中，查看命令提示符内发生了什么。

此外，将其添加到中，您就可以为本地开发环境提供一个可靠的备份解决方案，该方案可以利用MySQL数据库。

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

(..and为了我们的下一个技巧，我们将在备份.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

Answer 2

这对文件夹名中的空格更有弹性，日期和时间例程已被更改。

• 运行它，并首先检查"dirName"=文件夹的格式是否正确
• 最后一行应该回显del命令，以保持最近的3次备份。

测试归档例程，然后

如果您认为所有的echo都是对的，请在del关键字之前删除它。

 @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 /o-d /a-d') do echo del "%backupDir%\%dirName%\%%a"
pause