Protecting information in databases and the 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 backups can be solved with the help of the MySQL dump tool of dbForge Studio for MySQL. To use it, you should set up a backup in the wizard manually and schedule making backups.
To open the Database Backup wizard, on the Database menu, point to Backup and Restore, and then click Backup Database.
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 the 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.
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.
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 based on the project saved before.
Command line:
C:\Program Files\Devart\dbForge Studio for MySQL\dbforgemysql.com" /backup /projectfile:"D:\backup\sakila.backup
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 MySQLdbforgemysql.com" /backup /projectfile:"d:MyBackupssakila.backup"
%programfiles% – is an environment variable that returns the full path to the Program Files system folder.
- Open Control Panel -> Task Scheduler and select Create Basic Task
- Set the task schedule
- Select the created dailybackup.cmd file in the wizard
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 automating tedious work connected with making backups of MySQL databases regularly.
I am trying to set up an automated backup. I’m looking at your article: https://blog.devart.com/how-to-setup-daily-mysql-backup-on-windows.html, but I don’t have the option to “Auto delete old files” or “Save Project” buttons. I am running Express which has the Backup wizard in it. Please advise.