9.1 MySQL Backup

First, please refer to Chapter 4 Configuring Instances to configure the corresponding MySql instance information for the source Client. Just as a Client has multiple MySql instances, it can be configured multiple times.

You can also manually query Mysql-related parameters.

Login to MySql:

mysql -u root -p

Query:

SHOW VARIABLES LIKE 'port';
SHOW VARIABLES LIKE 'socket';
SHOW VARIABLES LIKE 'basedir';
SHOW VARIABLES LIKE 'datadir';

The result should be like:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+

+---------------+-----------------------------+
| Variable_name | Value                       |
+---------------+-----------------------------+
| socket        | /var/run/mysqld/mysqld.sock |
+---------------+-----------------------------+

+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| basedir       | /usr/            |
+---------------+------------------+

+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| datadir       | /var/lib/mysql/        |
+---------------+------------------------+

The above results can be compared with the automatic scanning results, and the query results should prevail.

After configuring the instance:

img

Start creating a new MySql plan from the left navigation bar [Plan] - [Database Backup] - [+], in 5 steps:

The first step is to select the data content.

img

Name the plan and select the data type MySql. After selecting Client and Instance, the system will automatically scan all databases under that instance (note that MySql's system databases such as MySQL/Sys have been hidden). The backup data list can be expanded later.

img

After selecting the database you need to backup, click [Next]:

Note that MySql data selection has two scenarios that will affect later backup plan options and recovery.

The first method is to directly click the checkbox in front of the instance name. At this time, all databases under this instance will be selected, and MySQL system libraries (mysql library and sys library) will also be included, that is, the entire instance-level backup will be performed. There are three results to note for instance-level backup.

1. Incremental backup can be performed because MySQL incremental backup is aimed at backing up binlog files, while MySQL's binglog is shared by all libraries.

2. It can only be restored to a blank MySql instance. Since the entire instance is backed up, full coverage at the instance level will be performed during recovery, which will delete the database and rebuild it, causing the update of the target machine account password (restoring to the same level as the source requires a restart). Therefore, it is not possible to recover from the original machine (data will be lost), and it is not recommended to recover in an environment with production data.

3. New libraries will be automatically included in the backup plan.

The second scenario is to select individual libraries for recovery, which is a backup at the library level. This situation requires attention.

1. It can only be done fully, not incrementally. The reason is that it is difficult to filter out logs that only target individual libraries from MySQL's shared binlog.

2. It can be restored to the original machine or a different machine, but special attention should be paid to the target instance cannot have the same name in the inventory.

Step two, select storage:

img

Please select the storage pre-configured in Chapter 5 Configuring Storage . Click [Next] to continue:

Step three, set a time plan.

img

Refer to Chapter 6, Section 1, Automatic Trigger of Backup Plan to configure the trigger time of the plan.

For MySql instance level backups , you can choose either "Full" only or "Full + Incremental";

For MySQL repository level backups , only full backups can be selected, please choose according to your needs.

Click [Next] to continue:

Fourth step, advanced options:

img

Please refer to Chapter 6, Section 4, Advanced options for backup plans and Section 5, before and after scripts to check.

There are some dedicated parameters for Mysql backup:

img

The --hex-blob, --opt, and --single-transaction parameters are enabled by default. --routines --events and --set-gtid-purged=OFF parameters are also provided for selection.

Pay special attention to whether the data source has enabled GTID for the backup of MySQL master and leader/follower clusters. If it is enabled, we recommend checking the --set-gtid-purged=OFF option. If not selected, the recovery may fail due to GTID issues. The method for querying GTID is as follows:

mysql -u username -p -e "
SHOW VARIABLES LIKE 'gtid_mode';
SHOW VARIABLES LIKE 'enforce_gtid_consistency';
SHOW STATUS LIKE 'Gtid%';
"

Result explanation:

• gtid_mode value should be ON or OFF , if it is ON , the GTID feature is enabled.

• enforce_gtid_consistency value should be ON , indicating MySQL forces GTID consistency.

• The output of SHOW STATUS Iike'Gtid% '; displays the GTID-related status variables to help confirm that GTID is running.

Mysqldump is a utility provided by MySQL for backing up databases. When using mysqldump , many parameters can be added to control the details of the backup. Here are some detailed descriptions of commonly used parameters:

--hex-blob

• Function : Dump BLOB fields (such as binary data, images, etc.) into hexadecimal format.

• Purpose : Ensure that binary data is not corrupted due to character set issues during dumping and recovery.

--opt

• Function : Enable multiple optimization options to improve backup and restore speed. Specifically include --add-drop-table , --add-locks , --create-options , --quick , --extended-insert , --lock-tables , --set-charset .

• Purpose : Enabled by default, suitable for efficient backup in most cases.

• Note : Starting from MySQL 5.6.6, --opt is enabled by default. You can use --skip-opt to disable it.

--single-transaction

• Function : Dumps data in a transaction to ensure data consistency.

• Purpose : Suitable for InnoDB tables, can perform consistent backups without locking the table. Especially useful for large-scale databases.

• Note : Not applicable to MyISAM tables that require table locking.

--routines

• Function : Routines including stored procedures and functions are in the dump.

• Purpose : Ensure that the backup contains all database logic, including stored procedures and functions.

--events

• Function : Events, including event scheduler events, are in the dump.

• Purpose : Ensure that the backup contains all scheduled tasks and events.

--set-gtid-purged=OFF

• Function : Controls whether to include GTID (Global Transaction Identifiers) information when exporting.

• Purpose : For GTID replication cluster, if OFF , it will not include the SET @@GLOBAL.GTID_PURGED statement; if ON or AUTO , it will be included or automatically judged according to the actual situation.

Click [Submit] to save the policy in the fifth step.

img

You can then refer to Chapter 6, Section 2, Manual Trigger of Backup Plan to run the plan manually. You can also wait for the scheduled automatic trigger of the plan.