mysqldump的批处理文件，用于将每个数据库备份到一个单独的文件中

Trying to create a batch (cmd) file for backing up each database into a separate file. Databases are created/deleted often, so batch file needs to grab current db names everytime it runs and backup each one of them.

尝试创建一个批处理(cmd)文件，以便将每个数据库备份到一个单独的文件中。数据库经常被创建/删除，所以批处理文件每次运行时都需要获取当前的db名称并备份它们。

Here is how I want it to be:

以下是我希望的:

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

Is it possible to do in a batch file?

是否可以在批处理文件中执行?

Please help. Thanks.

请帮助。谢谢。

8 个解决方案

#1

This can be run directly in cmd (I wrapped the line but it should not be wrapped):

这可以直接在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

In a batch file you will need to escape % with an additional %, that is use %%D.

在批处理文件中，您将需要用一个额外的%来转义%，即使用%% %D。

Batch File

批处理文件

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

#2

You are going to love this one

你会喜欢这个的

Have the information_schema database construct a DOS Batch File to perform the mysqldumps in parallel

是否有information_schema数据库构造一个DOS批处理文件来并行执行mysqldumps

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% 

Just run like any DOS Batch File

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

Make sure you have the correct username and password to connect to mysql

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

I just tried it out to make sure

我试了一下，确定一下

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

#3

hey rolando i combined your code with some other code from the internet to dump all databases to different files and compress it in one file with date-time stamp and finally delete files older than 60 days cheers

嘿，罗兰多，我把你的代码和互联网上的其他代码结合在一起，把所有的数据库转储到不同的文件中，然后压缩到一个文件中，并加上日期时间戳，最后删除超过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%!

#4

OK, First... It's possible that I'm using a different version of SQL than you, and im sorry if thats the case, you didn't list your version in your question so I'm just going to give you what works with mine.

好吧,第一次……有可能我使用的SQL版本与你不同，如果是这样的话，我很抱歉，你没有在你的问题中列出你的版本，所以我将给你我的版本。

I have the first part of this done, but I'm still working on the backing up of the DB's.

我已经完成了第一部分，但是我仍然在做DB的备份。

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

There are other triggers that can be used, but it sounds like you're going to be on the actual machine that has SQL installed, correct? If this is the case, it should default the IP to connect to SQL to 127.0.0.1 or localhost, etc.

还有其他可以使用的触发器，但是听起来您将在安装了SQL的实际机器上，对吗?如果是这样，它应该默认IP连接到SQL到127.0.0.1或localhost等。

What I'm thinking I'm going to have to do for this is create a file in the batch script that I will call further down that will send the commands line by line, similar to what is done in the FTP process with Batch Scripts.

我想我要做的是在批处理脚本中创建一个文件，我将进一步调用它，它将一行一行地发送命令，类似于在FTP进程中使用批处理脚本执行的操作。

I'll update this when I get it.

我收到后会更新。

#5

Im no DOS hacker,but I added one correction to my copy of the batch file to account for the whitespace character in the curtime variable if time is before 10 am. I added this line to my batch file after the for loops:

我不是DOS黑客，但是我在我的批处理文件副本中添加了一个修正，如果时间是在上午10点之前，那么我就在curtime变量中添加了一个空格字符。在for循环之后，我将这一行添加到我的批处理文件中:

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

#6

I've tried the answers but none of them worked as expected, here is my solution for backup but it will create a single file for mysql and other user databases.

我已经尝试了答案，但是没有一个能像预期的那样工作，这里是我的备份解决方案，但是它将为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%

#7

This script is a bit more "professional" in the sense that it notifies someone when one DB dump failed and which one failed. Though, I had it not backing up all databases but only chosen ones. This can be fixed easily by changing the SET DBS= content by a command getting all databases.

这个脚本更“专业”一点，当一个DB转储失败时，它会通知某人，而另一个则失败。但是，我没有备份所有的数据库，而是只备份选定的数据库。通过使用获取所有数据库的命令更改SET DBS= content，可以很容易地解决这个问题。

EDIT: New version remove the warning message

编辑:新版本删除警告消息。

@ECHO OFF

:: Configuration part
SET BACKUP_PATH=Backup-MySQL8
SET PHP=C:\Program Files (x86)\PHP\v5.6\php.exe
SET MAIL_TO=[e-mail]
SET MAIL_FROM=[e-mail]

SET MYSQL_PATH=C:\Program Files\MySQL\MySQL Server 5.6\bin
SET MYSQL_USER=root
SET MYSQL_PASS=mypassword
SET DBS=database_name1 database_name2


:: Software part
if not exist %BACKUP_PATH% md %BACKUP_PATH%

setlocal EnableDelayedExpansion
SET hasError=0
SET dbsInError=
SET CONFIG_FILE=backup-mysql.cnf

DEL /F /Q %BACKUP_PATH%\* 

echo [mysqldump] > %CONFIG_FILE%
echo user=%MYSQL_USER% >> %CONFIG_FILE%
echo password=%MYSQL_PASS% >> %CONFIG_FILE%

(for %%a in (%DBS%) do (
    "%MYSQL_PATH%\mysqldump.exe" --defaults-extra-file=%CONFIG_FILE% --routines --triggers %%a > %BACKUP_PATH%\%%a.sql
    IF NOT !ERRORLEVEL! == 0 (
        SET hasError=1
        DEL %BACKUP_PATH%\%%a.sql
        SET dbsInError=!dbsInError! %%a
    )
))

DEL %CONFIG_FILE%

IF !hasError! == 1 (
    echo Error... sending email
    "%PHP%" -r "echo (mail('%MAIL_TO%', 'Backup MySQL failed', 'The following database dump failed:!dbsInError!', 'From: %MAIL_FROM%') ? 'Sent' : 'Failed:' . print_r(error_get_last()));"
    echo.
)

echo Backup ended

#8

You can try this straight forward approach:

你可以尝试这种直截了当的方法:

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

steps:
1. type the above code in your text editor and save it as batch file e.g. mybatch.bat
2. change directory(cd) to d location you save the batch file to, from your command prompt
3. type the name of your batch file and hit enter e.g. mybatch.bat
4. check the location for your DB schema i.e. path

步骤:1。在文本编辑器中键入上述代码，并将其保存为批处理文件，例如mybatch。蝙蝠2。将目录(cd)从命令提示符3中更改为保存批处理文件的d位置。键入您的批处理文件的名称并单击enter，例如mybatch。蝙蝠4。检查DB模式(即路径)的位置