mysqldump的batch file将每个数据库备份到单独的文件中

尝试创build批处理(cmd)文件,将每个数据库备份到单独的文件中。 数据库经常被创build/删除,因此batch file需要在每次运行时获取当前的数据库名称,并对其中的每一个进行备份。

这是我想要的:

mysql -e "show databases" -u root --password=1234 mysqldump %dbname% -u root --password=1234 > S:\Backup\MySQL\%dbname%.sql 

可以在batch file中执行吗?

请帮忙。 谢谢。

Solutions Collecting From Web of "mysqldump的batch file将每个数据库备份到单独的文件中"

这可以直接在cmd中运行(我包装的行,但不应该被包装):

 mysql.exe -uroot -p1234 -s -N -e "SHOW DATABASES" | for /F "usebackq" %D in (`findstr /V "information_schema performance_schema"`) do mysqldump %D -uroot -p1234 > S:\Backup\MySQL\%D.sql 

在批处理文件中,您需要使用%%D来转义%。

批处理文件

 mysql.exe -uroot -p1234 -s -N -e "SHOW DATABASES" | for /F "usebackq" %%D in (`findstr /V "information_schema performance_schema"`) do mysqldump %%D -uroot -p1234 > S:\Backup\MySQL\%%D.sql 

你会喜欢这个

让information_schema数据库构造一个DOS批处理文件以并行执行mysqldump

 set MYSQLUSER=root set MYSQLPASS=1234 set BATCHFILE=S:\Backup\MySQL\Batch_mysqldump.bat set DUMPPATH=S:\Backup\MySQL echo @echo off > %BATCHFILE% echo cd %DUMPPATH% >> %BATCHFILE% mysql -u%MYSQLUSER% -p%MYSQLPASS% -AN -e"SELECT CONCAT('start mysqldump -u%MYSQLUSER% -p%MYSQLPASS% --routines --triggers ',schema_name,' > ',schema_name,'.sql') FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','performance_schema')" >> %BATCHFILE% type %BATCHFILE% 

就像任何DOS批处理文件一样运行

确保你有正确的用户名和密码连接到MySQL

我只是试图确定

 C:\>set MYSQLUSER=lwdba C:\>set MYSQLPASS=<hidden> C:\>set BATCHFILE=C:\LWDBA\Batch_mysqldump.bat C:\>set DUMPPATH=C:\LWDBA C:\>echo @echo off > %BATCHFILE% C:\>echo cd %DUMPPATH% >> %BATCHFILE% C:\>mysql -u%MYSQLUSER% -p%MYSQLPASS% -AN -Bse"SELECT CONCAT('start mysqldump -u%MYSQLUSER% -p%MYSQLPASS% --routines --triggers ',schema_nam e,' > ',schema_name,'.sql') FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','performance_schema')" >> %BATCH FILE% C:\>type %BATCHFILE% @echo off cd C:\LWDBA start mysqldump -ulwdba -phidden --routines --triggers a1ex07 > a1ex07.sql start mysqldump -ulwdba -phidden --routines --triggers annarbor > annarbor.sql start mysqldump -ulwdba -phidden --routines --triggers dilyan_kn > dilyan_kn.sql start mysqldump -ulwdba -phidden --routines --triggers dtest > dtest.sql start mysqldump -ulwdba -phidden --routines --triggers dude > dude.sql start mysqldump -ulwdba -phidden --routines --triggers example > example.sql start mysqldump -ulwdba -phidden --routines --triggers fed > fed.sql start mysqldump -ulwdba -phidden --routines --triggers friends > friends.sql start mysqldump -ulwdba -phidden --routines --triggers giannosfor > giannosfor.sql start mysqldump -ulwdba -phidden --routines --triggers javier > javier.sql start mysqldump -ulwdba -phidden --routines --triggers johnlocke > johnlocke.sql start mysqldump -ulwdba -phidden --routines --triggers junk > junk.sql start mysqldump -ulwdba -phidden --routines --triggers lovesh > lovesh.sql start mysqldump -ulwdba -phidden --routines --triggers mysql > mysql.sql start mysqldump -ulwdba -phidden --routines --triggers nwwatson > nwwatson.sql start mysqldump -ulwdba -phidden --routines --triggers part > part.sql start mysqldump -ulwdba -phidden --routines --triggers preeti > preeti.sql start mysqldump -ulwdba -phidden --routines --triggers prefixdb > prefixdb.sql start mysqldump -ulwdba -phidden --routines --triggers replagdb > replagdb.sql start mysqldump -ulwdba -phidden --routines --triggers rollup_test > rollup_test.sql start mysqldump -ulwdba -phidden --routines --triggers sample > sample.sql start mysqldump -ulwdba -phidden --routines --triggers stuff > stuff.sql start mysqldump -ulwdba -phidden --routines --triggers table_test > table_test.sql start mysqldump -ulwdba -phidden --routines --triggers tagmediatest > tagmediatest.sql start mysqldump -ulwdba -phidden --routines --triggers targetdb > targetdb.sql start mysqldump -ulwdba -phidden --routines --triggers test > test.sql start mysqldump -ulwdba -phidden --routines --triggers test_mysqldb > test_mysqldb.sql start mysqldump -ulwdba -phidden --routines --triggers tostinni > tostinni.sql start mysqldump -ulwdba -phidden --routines --triggers user1267617 > user1267617.sql start mysqldump -ulwdba -phidden --routines --triggers user391986 > user391986.sql start mysqldump -ulwdba -phidden --routines --triggers utility > utility.sql start mysqldump -ulwdba -phidden --routines --triggers veto > veto.sql start mysqldump -ulwdba -phidden --routines --triggers vito > vito.sql start mysqldump -ulwdba -phidden --routines --triggers zipcodes > zipcodes.sql 

嘿罗兰多我把你的代码与其他一些代码从互联网转储所有数据库到不同的文件,并压缩在一个文件与日期时间戳,最后删除超过60天的文件干杯

 @echo off CLS cd c:\temp set MYSQLUSER=root set MYSQLPASS=PassWord set BATCHFILE=c:\temp\Batch_mysqldump.bat set DUMPPATH=c:\temp SET backuptime=%DATE:~4,2%-%DATE:~7,2%-%DATE:~10,4%-%TIME:~0,2%-%TIME:~3,2% SET backuptimelog=%DATE:~4,2%-%DATE:~7,2%-%DATE:~10,4% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2% echo starting MySqlDump at %backuptime% echo ------ starting MySqlDump at %backuptimelog% ------ >> "Z:\-=macine backup=-\sqldump\sqldump.log" echo Running dump... set 7zip_path= mkdir "%backuptime%" >> "Z:\-=macine backup=-\sqldump\sqldump.log" cd "c:\Program Files\MySQL\MySQL server 5.6\bin" echo @echo off > %BATCHFILE% echo cd %DUMPPATH% >> %BATCHFILE% echo copy "C:\Program Files\MySQL\MySQL server 5.6\bin\mysqldump.exe" "c:\temp\%backuptime%" >> %BATCHFILE% echo cd "%backuptime%" >> %BATCHFILE% mysql -u%MYSQLUSER% -p%MYSQLPASS% -AN -e"SELECT CONCAT('mysqldump -u%MYSQLUSER% -p%MYSQLPASS% ' ,schema_name,' --result-file=',schema_name,'.sql') FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','performance_schema')" >> %BATCHFILE% echo exit >> %BATCHFILE% start /wait %BATCHFILE% echo Compressing bk_%backuptime%.sql... SET ziptime=%DATE:~4,2%-%DATE:~7,2%-%DATE:~10,4% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2% echo starting 7zip compression at %ziptime% echo starting 7zip compression at %ziptime% >> "Z:\-=macine backup=-\sqldump\sqldump.log" "C:\Program Files\7-Zip\7z.exe" a -t7z -m0=PPMd "Z:\-=macine backup=-\sqldump\bk_%backuptime%.7z" "c:\temp\%backuptime%" >> "Z:\-=macine backup=-\sqldump\sqldump.log" echo Deleting the SQL file ... rmdir /s /q "c:\temp\%backuptime%" >> "Z:\-=macine backup=-\sqldump\sqldump.log" echo deleting files older than 60 days echo deleting files older than 60 days >> "Z:\-=macine backup=-\sqldump\sqldump.log" forfiles -p "Z:\-=macine backup=-\sqldump" -s -m *.* /D -60 /C "cmd /c del @path" >> "Z:\-=macine backup=-\sqldump\sqldump.log" SET finishtime=%DATE:~4,2%-%DATE:~7,2%-%DATE:~10,4% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2% echo ------ Done at %finishtime%! ------ >> "Z:\-=macine backup=-\sqldump\sqldump.log" echo Done at %finishtime%! 

好的,首先…有可能我使用的是不同于你的SQL版本,不好意思,如果是这样的话,你没有在你的问题中列出你的版本,所以我只是想给你一些有用的东西与我的。

我已经完成了第一部分,但是我仍然在对数据库进行备份。

 sqlcmd -U %USER% -P %PASSWORD% -Q"SELECT name FROM sys.databases" > c:\JHA\Synergy\SQL_db_list.txt 

还有其他的触发器可以使用,但是这听起来像你将要在安装了SQL的实际机器上正确吗? 如果是这种情况,它应该默认IP连接到SQL到127.0.0.1或本地主机等

我在想我将不得不这样做是在批处理脚本中创建一个文件,我将进一步调用,将逐行发送命令,类似于在FTP过程中使用批处理脚本。

当我得到它时,我会更新它。

我没有DOS黑客,但我添加了一个更正到我的批处理文件的副本,以解释curtime变量中的空白字符,如果时间是早上10点。 我在for循环之后将这行添加到了我的批处理文件中:

 if "%curtime:~0,1%"==" " set curtime=0%curtime:~1,3% 

我已经尝试了答案,但没有一个按预期工作,这里是我的备份解决方案,但它会为mysql和其他用户数据库创建一个文件。

 set USERNAME=root set PASSWORD=1234 set TIMESTAMP=%DATE:~10,4%.%DATE:~4,2%.%DATE:~7,2%-%TIME:~0,2%.%TIME:~3,2%.%TIME:~6,2% set BACKUPPATH=D:\Backup\MySql\ if not exist %BACKUPPATH% md %BACKUPPATH% mysqldump --all-databases --result-file="%BACKUPPATH%%TIMESTAMP%.sql" --user=%USERNAME% --password=%PASSWORD% 

你可以尝试这个直接的方法:

 mysqldump databaseName -u root --password=rootPass > "path\myDBbackup.sql" 

脚步:
1.在你的文本编辑器中键入上面的代码,并将其保存为批处理文件,例如mybatch.bat
2.将目录(cd)更改为d位置,将批处理文件保存到命令提示符下
3.输入你的批处理文件的名称,然后按回车,例如mybatch.bat
4.检查数据库模式的位置,即路径