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

Batch-file for mysqldump to backup each database into a separate file

提问by Alex G

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) 文件以将每个数据库备份到一个单独的文件中。数据库经常被创建/删除，所以批处理文件需要在每次运行时获取当前的数据库名称并备份每个数据库名称。

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.

请帮忙。谢谢。

回答by newtover

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.

在批处理文件中，您需要使用额外的 % 来转义 %，即 use %%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

回答by RolandoMySQLDBA

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

回答by Vag Mor

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

嘿 rolando 我将您的代码与互联网上的其他一些代码结合起来，将所有数据库转储到不同的文件中，并将其压缩在一个带有日期时间戳的文件中，最后删除超过 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-Zipz.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%!

回答by HasanG

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%

回答by Master DJon

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.

这个脚本更“专业”一点，因为它会在一个数据库转储失败和哪个失败时通知某人。虽然，我没有备份所有数据库，而是只备份选定的数据库。这可以SET DBS=通过获取所有数据库的命令更改内容来轻松解决。

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 [email protected]
SET [email protected]

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

回答by rud3y

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.

我已经完成了第一部分，但我仍在努力备份数据库。

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 的实际机器上，对吗？如果是这种情况，它应该默认连接 SQL 的 IP 为 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.

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

回答by AndersFlodell

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%

回答by Oluwajoba

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.bat
2. 将目录 (cd) 更改为您保存批处理文件的 d 位置，从您的命令提示符
3. 键入您的名称批处理文件并点击输入，例如 mybatch.bat
4. 检查您的数据库架构的位置，即路径