Batch-file for mysqldump to backup each database into a separate file
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.sqlIn 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
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=rootset MYSQLPASS=1234set BATCHFILE=S:\Backup\MySQL\Batch_mysqldump.bat set DUMPPATH=S:\Backup\MySQLecho @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=lwdbaC:\>set MYSQLPASS=<hidden>C:\>set BATCHFILE=C:\LWDBA\Batch_mysqldump.batC:\>set DUMPPATH=C:\LWDBAC:\>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_name,' > ',schema_name,'.sql') FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','performance_schema')" >> %BATCHFILE%C:\>type %BATCHFILE%@echo offcd C:\LWDBAstart mysqldump -ulwdba -phidden --routines --triggers a1ex07 > a1ex07.sqlstart mysqldump -ulwdba -phidden --routines --triggers annarbor > annarbor.sqlstart mysqldump -ulwdba -phidden --routines --triggers dilyan_kn > dilyan_kn.sqlstart mysqldump -ulwdba -phidden --routines --triggers dtest > dtest.sqlstart mysqldump -ulwdba -phidden --routines --triggers dude > dude.sqlstart mysqldump -ulwdba -phidden --routines --triggers example > example.sqlstart mysqldump -ulwdba -phidden --routines --triggers fed > fed.sqlstart mysqldump -ulwdba -phidden --routines --triggers friends > friends.sqlstart mysqldump -ulwdba -phidden --routines --triggers giannosfor > giannosfor.sqlstart mysqldump -ulwdba -phidden --routines --triggers javier > javier.sqlstart mysqldump -ulwdba -phidden --routines --triggers johnlocke > johnlocke.sqlstart mysqldump -ulwdba -phidden --routines --triggers junk > junk.sqlstart mysqldump -ulwdba -phidden --routines --triggers lovesh > lovesh.sqlstart mysqldump -ulwdba -phidden --routines --triggers mysql > mysql.sqlstart mysqldump -ulwdba -phidden --routines --triggers nwwatson > nwwatson.sqlstart mysqldump -ulwdba -phidden --routines --triggers part > part.sqlstart mysqldump -ulwdba -phidden --routines --triggers preeti > preeti.sqlstart mysqldump -ulwdba -phidden --routines --triggers prefixdb > prefixdb.sqlstart mysqldump -ulwdba -phidden --routines --triggers replagdb > replagdb.sqlstart mysqldump -ulwdba -phidden --routines --triggers rollup_test > rollup_test.sqlstart mysqldump -ulwdba -phidden --routines --triggers sample > sample.sqlstart mysqldump -ulwdba -phidden --routines --triggers stuff > stuff.sqlstart mysqldump -ulwdba -phidden --routines --triggers table_test > table_test.sqlstart mysqldump -ulwdba -phidden --routines --triggers tagmediatest > tagmediatest.sqlstart mysqldump -ulwdba -phidden --routines --triggers targetdb > targetdb.sqlstart mysqldump -ulwdba -phidden --routines --triggers test > test.sqlstart mysqldump -ulwdba -phidden --routines --triggers test_mysqldb > test_mysqldb.sqlstart mysqldump -ulwdba -phidden --routines --triggers tostinni > tostinni.sqlstart mysqldump -ulwdba -phidden --routines --triggers user1267617 > user1267617.sqlstart mysqldump -ulwdba -phidden --routines --triggers user391986 > user391986.sqlstart mysqldump -ulwdba -phidden --routines --triggers utility > utility.sqlstart mysqldump -ulwdba -phidden --routines --triggers veto > veto.sqlstart mysqldump -ulwdba -phidden --routines --triggers vito > vito.sqlstart mysqldump -ulwdba -phidden --routines --triggers zipcodes > zipcodes.sql
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 dayscheers
@echo offCLScd c:\tempset MYSQLUSER=rootset MYSQLPASS=PassWordset BATCHFILE=c:\temp\Batch_mysqldump.bat set DUMPPATH=c:\tempSET 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%!