How To: Setup Daily MySQL Backup on Windows

Posted by on August 4th, 2010

Protecting information in databases and possibility to restore databases in case of need is the highest priority task in many companies. But not all DBMSs have built-in tools for data protection (tools to backup and restore databases). And MySQL is one of such DBMSs.

Making database backups is one of the most important things in the process of administrating MySQL databases, because some critical data loss can be irreplaceable.

The task of making daily mysql backup can be solved with the help of the backup database function of dbForge Studio for MySQL. To use it, you should setup backup in the wizard manually and schedule making backups.

To open the Database Backup wizard, you should choose Database Backup Database from the main menu.

menu "backup database" for opening mysql backup wizard

Making a MySQL database backup. Saving backup project.

On the first page of the wizard we should choose the database connection and the database we should backup. Also we should set the path where the backup files of MySQL databases will be saved.

When you are making daily database backups you should delete old irrelevant backups to optimize disk space usage and to store only a specified number of relevant backups of MySQL databases. It’s very easy to delete old backups automatically – you should only check the Auto delete old files option and set the number of backups that should be stored. In case when a scheduled backup was created successfully and the actual number of backups exceeds the set number, older backup files will be deleted.

To add the resulting file backup to an archive, check the Use compression (ZIP) option. In this case you can add comments to the archive.

General page of MySQL Backup wizard

On the Error Handling page of the wizard we should set the path and the name for a file into which the backup process of a MySQL database will be logged.

MySQL backup - "Error Handling" wizard page

After performing all necessary settings press the Backup button and wait until the application finishes export.

Press the Save Project button on the Finish page of the wizard, and as a result the backup project file in which options from all wizard pages are stored will be saved (for example, sakila.backup).

Creating a MySQL database backup via Command Line. Create a scheduled MySQL database backup using Windows Scheduled Tasks.

If you are using the Command Line mode and a backup project file, you can make a MySQL database backup easily and automate daily backup making using Windows Scheduled Tasks.

Below is an example of the command line in the result of executing which a MySQL database backup will be created basing on the project saved before.

Command line:

“D:Program FilesDevartdbForge Studio for” /backup /projectfile:”d:MyBackupssakila.backup”

MySQL backup using command line mode

To make backups of a MySQL database every day let’s create a scheduled task.

To make our example clear, let’s create a dailybackup.cmd file with the following content:

call “%programfiles%DevartdbForge Studio for” /backup /projectfile:”d:MyBackupssakila.backup”

%programfiles% – is an environment variable that returns full path to the Program Files system folder.

  • Open Control Panel -> Scheduled Tasks and select Add Scheduled Tasks
  • Select the created dailybackup.cmd file in the wizard

Scheduled Tasks wizard window

  • Set the task schedule

Scheduled Tasks wizard

Scheduled tasks wizard - set time

To avoid data loss you should make backups of all active databases regularly. If you have a backup, you will be able to restore the whole database or some of its objects easily.

dbForge Studio for MySQL allows to perform database backups of MySQL databases as SQL scenarios and supports making backups from Command Line, that allows to automate tedious work connected with making backups of MySQL databases regularly.

Leave a Reply