Batch-file for mysqldump to backup each database i

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.

Answer 1

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:

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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.

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.

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.

I'll update this when I get it.

Answer 6

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.

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