MySQL: Create Database Backup Using a Windows Batch File

Creating a complete solution in MySQL can sometimes be challenging, especially when you are looking for a specific administration or backup requirement.

I faced this situation several times while working with my development team. One of my colleagues asked me how to create MySQL database dumps using a batch file so that the backup could be executed easily from Windows.

I had already written about creating MySQL dumps using the command line, but this time I needed to automate the process using a Windows batch file.

After preparing the batch file and testing it successfully, I thought it would be useful to share the approach here.

Create the Batch File

Create a new file with a .bat extension, for example:

mysql_backup.bat

Add the following commands to the file:

cd "C:\Program Files\MySQL\MySQL Server\bin"

mysqldump -hlocalhost -uroot -p testDB1 > D:\mybackupdumps.sql

exit

Replace the following values according to your environment:

• localhost — MySQL server host
• root — MySQL username
• testDB1 — database name
• D:\mybackupdumps.sql — location and name of the backup file

When the batch file runs, mysqldump will prompt for the MySQL password.

Note: Avoid putting the MySQL password directly in the batch file because the file may be accessible to other users or applications.

Backup a Database on a Remote Server

You can also specify the MySQL server hostname or IP address using the -h option:

cd "C:\Program Files\MySQL\MySQL Server\bin"

mysqldump -h192.168.1.100 -umyuser -p mydatabase > D:\mybackup.sql

exit

Replace the host, username, database name, and output path with your actual values.

Backup All Databases

If you want to create a dump containing all databases accessible to the MySQL user, use the --all-databases option:

cd "C:\Program Files\MySQL\MySQL Server\bin"

mysqldump -hlocalhost -umyuser -p --all-databases > D:\myalldatabases.sql

exit

Useful mysqldump Options

mysqldump provides many options that can be used to control how the dump is created. Some commonly used options include:

--add-locks
Adds LOCK TABLES and UNLOCK TABLES statements around table dumps.

--all-databases
Dumps all databases.

--comments
Includes comments in the dump file.

--compact
Produces a more compact dump output by reducing some additional statements and comments.

--ignore-table=db_name.table_name
Excludes the specified table from the dump.

 

Final Note

A Windows batch file makes it easier to repeat a database backup command without manually typing it each time. It can also be used as part of a scheduled backup process using Windows Task Scheduler.

The exact MySQL installation path may differ depending on the MySQL version and how it was installed, so update the cd path accordingly.