Misc An Ultimate Guide on MariaDB and MySQL Database Backup | dbForge Studio for MySQL

Backing up and restoration are crucial to maintaining a healthy and thriving database. If anything goes wrong, database restoration can serve as a time machine that takes you back to the point when everything was still in order and helps you prevent the disaster. In this article, you will find an illustrated practical guide on how to back up and restore one or multiple databases using a convenient MariaDB GUI - dbForge Studio for MySQL.

Database Backup and Restore in dbForge Studio for MySQL

Keeping database backups up-to-date is one of the most important things in the process of administering MySQL databases. On the occasion of some critical data loss, a backup can be the only thing to help you reverse it. dbForge Studio for MySQL offers a convenient and user-friendly interface for you to back up and restore databases but also allows you to go about it in two different ways:

  • Manual database backup and restoration
  • Scheduling backup creation

How to create a database backup manually

Without further ado, let us dive into the practical side of the matter.

Manual backup creation

To create a backup, click **Database**, point to **Backup and Restore**, and select **Backup Database**.

1. In the **Database Backup Wizard** that opens, configure general backup options:

  • Optional: Load a previously saved project if you have one.
  • Specify the connection and the database.
  • Specify the path where to save the backup.
  • Type in the output file name.
  • Optional: If you would like to append a timestamp to the output file, select the corresponding option.
  • Optional: Auto-delete old files and Use Compression (ZIP) are disabled by default. Enable those if needed.

2. On the **Backup Content** page, select database objects to backup. You can choose whether you would like to include either the database structure or the data, or both.

3. In the **Options** tab, specify detailed options of how backup should be performed. For convenience, the options are divided into the following groups:

  • Options affecting a database
  • General script options
  • Backup structure
  • Data backup
  • Users/roles

Click each option to get more information about each of them.

4. In the **Errors handling** tab, specify errors processing behavior and logging options.

5. Click **Backup**.

6. The process of backup creation begins. You can click **Cancel** in case you would like to abort the operation.

7. As soon as the backup process is over, you will see the **Backup complete** message along with the information about how many errors and warnings have been encountered and how many objects have been backed up in total.

Moreover, if you have chosen to write an error report to a log file earlier, you will be able to open it directly from the wizard at this step.

8. Once done, click **Finish**.

Manual backup restoration

We have already figured out how to create a MySQL and MariaDB backup and now, let us restore it.

1. Click **Database**, point to **Backup and Restore**, and click **Restore Database**.

2. In the **Database Restore Wizard** that opens, select a file with a database script created during the previous backup operation.

  • Specify the connection and the database. By default, the MariaDB IDE will use the database specified in the script.
  • Specify the path to the backup file.
  • Choose the SQL file encoding.

3. Click **Restore**.

4. The process of backup restoration begins. You can click **Cancel** in case you would like to abort the operation.

5. As soon as the process is over, you will see the **Database restore completed successfully** message.

How to schedule database backup creation

Having an up-to-date backup of a database is crucial when it comes to fault-tolerance measures. However, doing it regularly by yourself might be too time-consuming and tedious. To automate the process, you can schedule the MySQL/MariaDB database backup:

1. Once you finish the backup creation process, click **Save Project** on the final tab of the **Database Backup Wizard**.

2. Open **Task Scheduler for Windows**.

3. In the top menu, click **Action** and choose **Create Basic Task**.

4. In the **Create Basic Task Wizard** that opens, provide a name for the new task. Click **Next**.

5. Configure the schedule of the task. Click **Next**.

6. Select the time and frequency for running the task. Click **Next**.

7. Select **Start a Program** and click **Next**.

8. Specify a path to the previously saved project file.

9. Make sure to specify arguments in the **Add arguments** field. For example:

*/backup /connection:"User Id=root;Host=db;Port=3309;Database=sakila;Character Set=utf8;" /projectfile:"C:\Users\Documents\dbForge Studio for MySQL\Export\sakila_backup".*

10. Check all the specified settings once again and, if no changes are required, click **Finish**. Otherwise, click **Back** and make the needed changes.

Now, we have scheduled an automatic backup that will run every night at 3 AM. Thus, you will always have a fresh file to restore your MySQL & MariaDB database in case of emergency.

Conclusion

In this article, we described how to back up and restore a database using a convenient MySQL/MariaDB GUI - dbForge Studio. Use 5 levels of file compression and a wide set of options to fine-tune backups. Save your settings and connections as a project for the next time or to use in the command line. Download a fully-functional 30-day trial version of the IDE to try the solution for free.