MySQL: Schedule Automatic Database Backup on Windows

Quite simple. Since you have already read my older post about creating a backup (dump) file, here we will see how to automatically take a backup of a MySQL database at a particular time on your local Windows system.

This can be done using a batch file and Windows Task Scheduler.

Steps to create a scheduled backup:

Step 1: Create a batch file with the mysqldump command and the required options, as mentioned in the old post.

Step 2: Open Windows Task Scheduler.

You can search for Task Scheduler from the Windows Start menu.

Step 3: Create a new task or basic task and configure it to run the batch file at the required date and time.

After completing the configuration, wait for the scheduled time and check the destination directory. If everything is configured correctly, the MySQL backup file should be created automatically.

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.