How to Backup MySQL Databases with Batch File – mysqldump.exe

There are lots of tools to backup MySQL databases but the tool comes bundle with MySQL will just do a good job. Mysqldump.exe tool is used to backup (dump) all or selective databases.

According to MySQL.com

“The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format.”

Here is an example of mysqldump.exe to backup all MySQL databases:

@REM *** PARAMETERS/VARIABLES ***
SET BackupDir=c:\TEMP
SET mysqldir=C:\Program Files\MySQL Server\DWDatabase\bin
SET mysqlpassword=admin
SET mysqluser=admin
@REM *** EXECUTION ***
@REM Change to mysqldir
CD %mysqldir%
@REM dump/backup ALL database, this is all in one line
mysqldump -u %mysqluser% -p%mysqlpassword% --all-databases >%BackupDir%\DWSystemFullBackup_%random%.sql
@REM - YOU ARE DONE!

Here is an example to back selective databases:

@REM *** PARAMETERS/VARIABLES ***
SET BackupDir=c:\TEMP
SET mysqldir=C:\Program Files\MySQL Server\DWDatabase\bin
SET mysqlpassword=admin
SET mysqluser=admin
@REM *** EXECUTION ***
@REM Change to mysqldir
CD %mysqldir%
@REM dump/backup ALL database, this is all in one line
mysqldump -u %mysqluser% -p%mysqlpassword% --databases dwdata dwsystem dwlogging >%BackupDir%\DWSystemFullBackup_%random%.sql
@REM - YOU ARE DONE!

I am using a random number to generate a backup file.

mysqldump

mysqldump