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-uroot-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.
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.
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.
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.
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.
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:
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:
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.