MySQL database backup

Overview

The database backup process in Xopero ONE can be split into two different operations:

1. Creating a database dump using backup scripts,

2. Performing a backup of the database dump.

The first operation is triggered through Xopero ONE using the Backup scripts feature, and the second action is simply a file-level/image-level backup plan performance done directly by Xopero ONE. This article describes how to use Backup scripts in order to create a MySQL database dump, so it can be included in a backup plan.

See also:

pageDrive Image-Level Backup - Backup Plan Configuration, Limitations, Advantages, Common IssuespageFile-Level Backup - Setting Up A Backup Plan, Performance, Advantages

Thanks to the mysqldump tool usage Xopero ONE supports all MySQL versions that are supported by Oracle.

How to configure MySQL database backup

To configure the MySQL database environment open the Plans tab and click Add plan button and open Advanced settings.

Click on the Backup scripts section to expand the list of available options.

Toggle the pre-backup script switch to ON and set the correct script responsible for MySQL database environment backup.

Enter the database username in the Database user field.

It is highly recommended to create a dedicated user for the database backup operations.

Select the user password by opening the password manager, where you can add a new password or use one previously added.

See also how to add a new password to the Safe Password Manager:

pageAdd A New Password

Enter the arguments in the Script arguments field.

A scheme for MySQL script arguments is:

Windows: database output_dir mysqldump_dir port
Linux: database output_dir port

The mysqldump utility for Linux when pointing to a host by the name 'localhost' communicates via socket file. If you do not enter a port in the configuration, the tool will connect correctly even to a non-standard port. Specifying a non-standard port will be required if you are using a connection via 127.0.0.1.

where:

• database - database name. If you want to protect more than 1 database in one backup plan, type the names as [base1 base2 base3]. If you want to protect all databases use --all parameter instead of the database name.

• output_dir - the directory to which the database dump is to go and also a directory that should be selected in Data to protect a section during backup plan creation.

• mysqldump_dir - path to the mysqldump utility.

• port - this is an additional parameter, enter the port only if you are using a non-standard port. The standard port is 3306.

For example, if you want to back up all the databases from the MySQL environment, use the following arguments:

--all C:\MySQLBackup\ "C:\Program Files\MySQL\MySQL Server 8.0\bin"

If you want to protect the selected databases from the whole MySQL environment (for example two databases from seven), you can use the following arguments:

database1 database2 C:\MySQLBackup\ "C:\Program Files\MySQL\MySQL Server 8.0\bin"

Important! The mysqldump tool is the part of the MySQL server and should by installed by default. If, for some reason, it is not installed, you can download the MySQL Utilities, which contains the mysqldump tool.

MySQL :: Download MySQL Utilities (Archived Versions)

It's recommended to mark the checkbox next to the Fail the task if the script fails option, Using it will set the backup plan status to Failed, but only in case if MySQL dump script will finish with errors.

Click the Save button to apply your settings for MySQL environment backup. That's all that is needed in order to create the database dump. Remember, that this file should be now included in Data to protect section in case of file-level backup(if you want to backup only the database) or you can perform an image-level backup to protect whole drive.

See also:

pageFile-Level Backup - Setting Up A Backup Plan, Performance, AdvantagespageDrive Image-Level Backup - Backup Plan Configuration, Limitations, Advantages, Common IssuespageHow to restore MySQL database from backup?