Windows批处理脚本备份本地MySQL数据库和放大器;只有保持与备份文件ñ最新FOLDERS [英] Windows Batch Script to backup local MySQL databases & only keep N latest FOLDERS with backup files
问题描述
我使用 adityasatrio的批处理文件备份本地MySQL DB和希望能够只保持30最新的备份文件。使用根：根在这个例子
关闭@echo 设置DBUSER =根
设置DBPASSWORD =根
设置BACKUPDIR =D：\\ MySQLDumps \\ DBS \\
设置的mysqldump =C：\\ WAMP \\ BIN \\ mysql的\\ mysql5.6.17 \\ BIN \\ mysqldump.exe
设置mysqlDataDir =C：\\ WAMP \\ BIN \\ mysql的\\ mysql5.6.17 \\ DATA
集拉链=C：\\ Program Files文件\\的7-Zip \\ 7z.exe ::获取日期
FOR / F令牌= 2-4 delims = /%% i的（'日期/ T'）做（
一套YY = %%我
设置星期一= %%Ĵ
集DD = %%ķ
） ::获取时间
FOR / F的标记= 5-8 delims = :.我%%（回声^ |。时间^ |找到当前'）做（
集HH = %%我
设置分= %%Ĵ
） 回声DIRNAME =％YY %%周一%% DD％_％HH %%分钟％
设置目录名=％YY %%周一%% DD％_％HH %%分钟％ ::切换到数据文件夹中
PUSHD％mysqlDataDir％ ::迭代中的数据的文件夹结构文件夹来获取数据库
针对/ D %% f由于（*）做（ 如果不存在％BACKUPDIR％\\％DIRNAME％\\（
MKDIR％BACKUPDIR％\\％DIRNAME％
） ％的mysqldump％主机=localhost的用户=％DBUSER％--password =％DBPASSWORD％--single事务--add-drop-table在--databases %% F＆GT; ％BACKUPDIR％\\％DIRNAME％\\ %% f.sql 拉链％一％％-tgzip BACKUPDIR％\\％DIRNAME％\\ %% f.sql.gz％BACKUPDIR％\\％DIRNAME％\\ %% f.sql 德尔％BACKUPDIR％\\％DIRNAME％\\ %% f.sql ）
POPD
现在，我有幸在以下几个问题好好看看：
<一个href=\"https://serverfault.com/questions/49614/delete-files-older-than-x-days\">https://serverfault.com/questions/49614/delete-files-older-than-x-days
<一个href=\"https://stackoverflow.com/questions/13367746/batch-file-that-keeps-the-7-latest-files-in-a-folder\">Batch文件保存在一个文件夹 7个最新的文件
<一个href=\"https://stackoverflow.com/questions/14265984/windows-batch-file-only-keeping-the-last-30-files\">Windows批处理文件只保留最后的30个文件
，我现在想知道如果我可以简单地添加（<一个href=\"http://stackoverflow.com/a/14267137/1010918\">http://stackoverflow.com/a/14267137/1010918)
FOR / F跳过= 30 delims =%% A有（'DIR / A：-d / B / O：-d / T：C * .SQL ^ 2 ^＆GT; NUL'）做是否存在%%〜FA回声〜%% FA
（是的，我会在以后更改回声德尔但首先我想看看会发生什么）
或（ http://stackoverflow.com/a/13368077 ）
FOR / F跳过= 30 =停产：delims =%% f由于（'DIR / B / O-D * .SQL'）做@del%% F
在批处理文件的结尾，右侧下
德尔％BACKUPDIR％\\％DIRNAME％\\ %% f.sql
要做到这一点？
我从来没有这样做过，但搜索的自动本地备份应用程序/ PHP脚本/ mysqldump的命令在/ etc MySQL的星展银行，甚至有与工作台一去才发现，没有调度可以在社区版（设置谢谢你的Oracle）。
所有其他应用程序要么需要有人打开应用程序并点击立即运行，或要你支付设立一个时间表（不，谢谢）。
我认为这是可以用工具在手做了Windows 7和更高版本的机器上。请帮我添加此功能的脚本，那将是巨大的，谢谢你。
EDIT1：
在添加引用命令没有反应。
还创建的备份目录仅显示时间，但不年，月，日。做进一步的研究，以找出原因。任何想法？
本评论<一个href=\"https://stackoverflow.com/questions/18553694/delete-all-but-x-most-recent-folders#comment46957275_18554479\">delete所有将x最近的文件夹有关删除5最新的文件夹，尽管当我使用它像这样的会谈
FOR / F跳过= 2 delims =%%一个在（'DIR％BACKUPDIR％\\％DIRNAME％/ OD / B'）做RD / S / Q％BACKUPDIR％\\ ％DIRNAME％\\ %%一个
误差以下
系统找不到指定的文件。
该系统找不到指定的路径。
EDIT2：
下面是code与@foxidrive帮助设置文件夹的名称，因为我想拥有它，但最后一点，试图只保留3最新的文件夹（只有3测试目的），并删除这些文件夹的其余部分在BACKUPDIR似乎没有工作了。
感谢您的任何帮助。
关闭@echo 设置DBUSER =根
设置DBPASSWORD =根
设置BACKUPDIR = D：\\ MySQLDumps \\ DBS \\
设置的mysqldump = C：\\ WAMP \\ BIN \\ mysql的\\ mysql5.6.17 \\ BIN \\ mysqldump.exe
设置mysqlDataDir = C：\\ WAMP \\ BIN \\ mysql的\\ mysql5.6.17 \\ DATA
设置ZIP = C：\\ Program Files文件\\的7-Zip \\ 7z.exeREM下面的四行会给你可靠的YY DD在XP专业版及更高版本MM YYYY HH分秒MS变量。FOR / F令牌= 2 delims ==%%一个在（'WMIC OS获取localdatetime /值）并设置DT = %%一个
设置YY =％DT：〜2,2％与＆amp;设置YYYY =％DT：〜0.4％，与＆amp;设置MM =％DT：〜4.2％与＆amp;设置DD =％DT：6,2〜％
设置HH =％DT：〜8.2％＆放大器;设置最小=％DT：10,2〜％＆放大器;设置SEC =％DT：〜12.2％＆放大器;集MS =％DT：〜15.3％。 设置目录名=％YY％ - ％MM％ - ％DD％HH％％ - ％最小％ - ％二段％ 回声目录名=％DIRNAME％
暂停 ::切换到数据文件夹中
PUSHD％mysqlDataDir％ ::创建备份文件夹，如果它不存在
如果不存在％BACKUPDIR％\\％DIRNAME％\\的mkdir％BACKUPDIR％\\％DIRNAME％ ::迭代中的数据的文件夹结构文件夹来获取数据库
针对/ D %% f由于（*）做（
回波处理文件夹%% F ％的mysqldump％主机=localhost的用户=％DBUSER％--password =％DBPASSWORD％--single事务--add-drop-table在--databases %% F＆GT; ％BACKUPDIR％\\％DIRNAME％\\ %%〜nxf.sql 拉链％％一-tgzip％BACKUPDIR％\\％DIRNAME％\\ %%〜nxf.sql.gz％BACKUPDIR％\\％DIRNAME％\\ %%〜nxf.sql 德尔％BACKUPDIR％\\％DIRNAME％\\ %%〜nxf.sql ）
POPD ::删除所有但最新的3个文件夹 FOR / F跳过= 3 delims =%% A有（'DIR / B /广告/在％BACKUPDIR％\\％DIRNAME％\\ *'）做@echo RD / S / Q％BACKUPDIR％\\％ DIRNAME％\\ %%〜A暂停
通过上面@foxidrive我的帮助下设法文件夹的日期，因为我想他们是，他们是YYYY-MM-DD HH-MIN - 仲。
在这些文件夹存储的感谢 adityasatrio的MySQL的备份批处理脚本的gzip压缩的.sql databses
随着@Magoo从这个答案
下面是完整的工作脚本。
随意删除任何出现暂停和和回声为不可以看什么是命令提示符里面发生了。
此外它添加到Windows任务计划程序，你有你自己的本地开发环境，使得使用MySQL数据库的一个坚实的备份解决方案。
请感谢那些帮助我得到这个工作的人。如果没有你们，我就不得不在本地使用昂贵的Windows应用程序只保存MySQL数据库。
（..和我们的下一招，我们将是否有错误，而备份.sql文件通过电子邮件发送一个错误日志，以自己..但这是另一天另一个问题和故事..）
关闭@echo 设置DBUSER =根
设置DBPASSWORD =根
设置BACKUPDIR = D：\\ MySQLDumps
设置的mysqldump = C：\\ WAMP \\ BIN \\ mysql的\\ mysql5.6.17 \\ BIN \\ mysqldump.exe
设置mysqlDataDir = C：\\ WAMP \\ BIN \\ mysql的\\ mysql5.6.17 \\ DATA
设置ZIP = C：\\ Program Files文件\\的7-Zip \\ 7z.exe :: http://stackoverflow.com/a/31789045/1010918 foxidrive的回答帮助我得到的文件夹的日期和时间，我想REM下面的四行会给你可靠的YY DD在XP专业版及更高版本MM YYYY HH分秒MS变量。FOR / F令牌= 2 delims ==%%一个在（'WMIC OS获取localdatetime /值）并设置DT = %%一个
设置YY =％DT：〜2,2％与＆amp;设置YYYY =％DT：〜0.4％，与＆amp;设置MM =％DT：〜4.2％与＆amp;设置DD =％DT：6,2〜％
设置HH =％DT：〜8.2％＆放大器;设置最小=％DT：10,2〜％＆放大器;设置SEC =％DT：〜12.2％＆放大器;集MS =％DT：〜15.3％。 设置目录名=％YYYY％ - ％MM％ - ％DD％HH％％ - ％最小％ - ％二段％ ::消除回声在这里，如果你喜欢
回声目录名=％DIRNAME％ ::切换到数据文件夹中
PUSHD％mysqlDataDir％ ::创建备份文件夹，如果它不存在
如果不存在％BACKUPDIR％\\％DIRNAME％\\的mkdir％BACKUPDIR％\\％DIRNAME％ ::迭代中的数据的文件夹结构文件夹来获取数据库 针对/ D %% f由于（*）做（
::消除回声在这里，如果你喜欢
回波处理文件夹%% F ％的mysqldump％主机=localhost的用户=％DBUSER％--password =％DBPASSWORD％--single事务--add-drop-table在--databases %% F＆GT; ％BACKUPDIR％\\％DIRNAME％\\ %%〜nxf.sql 拉链％％一-tgzip％BACKUPDIR％\\％DIRNAME％\\ %%〜nxf.sql.gz％BACKUPDIR％\\％DIRNAME％\\ %%〜nxf.sql 德尔％BACKUPDIR％\\％DIRNAME％\\ %%〜nxf.sql ）
POPD ::删除所有文件夹，但最新的2
:: http://stackoverflow.com/a/17521693/1010918脱线的回答让我得到我想要的文件夹做
:: FOR / F跳过= 2 delims =在（'DIR / B / AD-H / O-D'）％G DO呼应要删除％G ::下面按照我的版本RD（删除目录）命令和/ s和/ Q
::消除回声RD之前，真正删除文件夹的问题！
::注意他们将在他们的内容被删除!! ::更改值跳过之后=要你喜欢什么，这是最新的文件夹，保持在你的备份目录中的金额
FOR / F跳过= 2 delims =%%一个在（'DIR％BACKUPDIR％\\/ B / AD-H / O-D'）做回声RD / S / Q％BACKUPDIR％\\ %%一::删除暂停在这里，如果你喜欢和文件添加到Windows任务管理器
暂停
I am using adityasatrio's batch file to backup local MySQL dbs and like to be able to only keep the 30 latest backup files. Using root:root for this example.
@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
Now I have had a good look at the following questions:
Batch file to delete files older than N days
https://serverfault.com/questions/49614/delete-files-older-than-x-days
Batch file that keeps the 7 latest files in a folder
Windows batch file only keeping the last 30 files
and am now wondering if I can simply add (http://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"
(yes I will later change echo to del but first I like to see what will happen)
or (http://stackoverflow.com/a/13368077)
for /f "skip=30 eol=: delims=" %%F in ('dir /b /o-d *.sql') do @del "%%F"
at the end of the batch file, right under
del %backupDir%\%dirName%\%%f.sql
to make this happen?
I have never done this before but have searched for automated local backup apps/php scripts/mysqldump commands/etc for MySQL dbs, even had a go with Workbench only to discover that no scheduling can be set in the community edition (thank you Oracle).
All the other apps either need to have someone open the app and hit "run now" or want you to pay for setting up a schedule (no thanks).
I think this can be done with the tools at hand on a Windows 7 and later versions machine. Please help me add this functionality to the script, that would be great, thank you.
edit1:
When adding the quote commands nothing happens. Also the created backup directory only displays the time but not the year, month and day. Doing further research to find out why. Any ideas?
This comment delete all but X most recent folders talks about deleting the 5 latest folders, though when I use it like this
for /f "skip=2 delims=" %%a in ('dir %backupDir%\%dirName% /o-d /b') do rd /S /Q "%backupDir%\%dirName%\%%a"
the error is the following.
The system cannot find the file specified.
The system cannot find the path specified.
edit2: Below is the code that with @foxidrive help sets the folder name as I like to have it, but the last bit, trying to only keep the 3 latest folders (for testing purposes only 3) and delete the rest of the folders in the backupDir does not seem to work out.
Thank you for any help.
@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
With the help of @foxidrive above I managed to get the date of the folders as I wanted them to be, them being YYYY-MM-DD HH-MIN-SEC.
In these folders are the the gzipped .sql databses stored thanks to adityasatrio's MySQL Backup Batch Script.
With the help of @Magoo from this answer http://stackoverflow.com/a/17521693/1010918 I managed to get all folders (nameDir) deleted while keeping the latest N folders (nameDir) and also not touching any files that might be in the directory (backupDir).
Here is the complete working script.
Feel free to remove any occurrence of pause and and echo to not see what is going on inside the command prompt.
Additionally add this to Windows Task Scheduler and you have yourself a solid backup solution for a local development environment that makes use of MySQL databases.
Please thank the people that helped me get this done. Without you guys I would have had to use a costly Windows app only to locally save MySQL databases.
(..and for our next trick we are going to email an error log to ourselves if there are errors while backing up the .sql files.. but that is another question and story for another day.. )
@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"
:: http://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
:: http://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
这篇关于Windows批处理脚本备份本地MySQL数据库和放大器;只有保持与备份文件ñ最新FOLDERS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
