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

Question

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.

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.

Solution 1

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

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.

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

Solution 2

You are going to love this one

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

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

Make sure you have the correct username and password to connect to 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

Solution 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

@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%!

Comments

[Alex G]

It does not matter which version of MySQL you use. mysqldump works same way on all versions. If I'm running a batch file - oh yes, I'm on the local machine. 127.0.0.1 - is a localhost, you are right on this one.

[rud3y]

@Radio - You voted me down for asking if you were using a different version of SQL? Your commands dont work on my version of SQL... mysqldump does not work with MSSQL, which I am using, though the commands should be very similar. I'm running MSSQL, the most common version of SQL.

[Alex G]

You are not reading my question. It's not related to MSSQL or any other database. It's related only to MySQL. Just read please.

[Alex G]

This is an interesting one. But I get an error message: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server versi on for the right syntax to use near '.sql') FROM information_schema.schemata WHERE schema_name NOT IN ('information_s' at line 1

[Alex G]

Good one! There are couple of things I needed to change like \\ instead of \, also there is no need to "start mysqldump" in a separate window for each command... I will post my answer and will accept yours. Thanks a lot.

[Alex G]

Great! Where have you been before? =)

[rud3y]

You should learn to express yourself verbally better... Noone on here is a mind reader. Please see to it that you adhear to the proper format of questions and seek some guidance from the FAQ. [ stackoverflow.com/faq ]

[Alex G]

First of all, we are chatting online, this got nothing to do with VERBAL term. Secondly my original post clearly shows: mysql tag and mysqldump in the topic. What is your problem?

[Angel S. Moreno]

@RolandoMySQLDBA you sir, are a total bad ass.

[Alex G]

This is not what the question is about

[TheFrost]

By "not be wrapped" @newtover means that all the lines have to be in one line, otherwise you will get syntax error.

[Jubin Patel]

some how but this batch file code is not working for me. it throws The syntax of the command is incorrect. error every time

[XandrGuard]

@JubinPatel just use this script in one line (without new lines)

[Suman EStatic]

for mysqldump the syntax you mentioned will not work as we have to follow the sequence. mysqldump -h {hostname} -u {usernamne} -p{password} {dbName} > path/to/store/sql/file.

[HasanG]

@SumanEStatic The script is working fine right now and dumps dbs perfectly... MySQL Server 5.5

[gramgram]

I'm in love with you. :)

[newtover]

@gramgram, to be honest, this is the answer I am really proud of. I am glad you like it too :)

[Alex G]

@newtover: no good answer can take place without a good question asked first. =))