Backuping up and restoring

MySQL Cloud Database supports database backup, automatic Binlog backup, recovery from backup, backup file download, and database rollback creation functions. Open backup management console like this:

Backup Strategy

MySQL backups are divided into automatic backups and manual backups. Automatic backups are performed once a day (the default backup time is at a whole point between 3:00 and 6:00, and backups can be kept for 7 days), manual backups are free for 3 copies, and automatic backups are free for 7 copies. The backup source can be selected from the master or replication database; it supports whether to enable forced backup. After enabling forced backup, if there are some SQL errors in the library table, the forced backup will ignore and skip this table. To ensure that the backup task is not interrupted, it will continue to back up other tables. In this case, the backup file may not have data for tables with SQL errors. If there are SQL errors, it is recommended to repair them in time to ensure the completeness of the backup; users can set and change the automatic backup strategy according to business needs.

The backup method of MySQL provides two types: physical backup and logical backup, with physical backup as the default. It supports setting "Save to US3" to automatically upload the backup file to your selected US3 storage bucket, and you can manage your backup files in the US3 storage bucket, which can be kept for a longer time according to actual business conditions; during the retention policy has turned on "Save to US3", please ensure that the token of the selected US3 product and the corresponding storage bucket exist and the token authorization is valid, otherwise there is a risk of backup upload failure.

MySQL provides automatic Binlog backup function, supports automatically dumping local Binlog to US3, and supports configuring the validity period of Binlog dumping. In the functions of database rollback, creating Read-Only instances, etc., the dumped backup and Binlog will be used preferentially for data recovery, reducing the IO pressure on the master database.

Manual Backup

MySQL instances support manual backup. Users can save important data backups at certain key time points. The current number of manual backups allowed to be kept is 3. If it exceeds 3, the earliest manual backup will be automatically deleted. In manual backups, MySQL offers two types of backups: physical backup and logical backup, with physical backup as the default method. When creating a manual backup, users only need to enter the backup name and choose the backup method, and the backup work will start immediately in the background.

After clicking on manual backup, a pop-up dialog box allows you to enter the backup name, backup method, whether to force backup, and using physical backup by default to backup all databases and tables. If you need not to backup certain databases and tables, set the backup object to filter out unnecessary databases and tables.

Note:

When adding databases and tables that do not participate in the backup, if you enter non-existent databases and tables, the backup will execute normally without error.

Backup Download

Users can download the backup files generated by automatic backup and manual backup.

To view all backup files of MySQL instances, click "Backup Management" in the left navigation bar.

To view the backup files of the corresponding MySQL instance, select the corresponding MySQL instance and click "Backup Management" on the details page. MD5 information is provided for the backup files, which can be used for data integrity verification.

Click the download icon to pop up the download box for download.

Note: If the backup file exceeds 1G, it is recommended to download it to the cloud host (UHost) via wget or curl. For larger backup files, when the download time exceeds two hours, it will return 206. Tools like wget will automatically continue to download when they encounter 206 until they finally succeed. This is a normal phenomenon.

If you want to verify the integrity of the downloaded file, you can calculate the MD5 of the downloaded backup file and compare it with the MD5 in the backup list. If they are consistent, it means that the downloaded file is complete.

The MD5 of the file is calculated using the md5sum command

If the user has enabled the Binlog auto-dump function, you can also download the Binlog backup file from the list.

Local Binlog Management

Local Binlog cleanup will affect the earliest rollback time of the database rollback function, the creation of replication databases, etc. It is recommended to use it in conjunction with the Binlog automatic backup function.

In the Binlog backup list menu under the "Backup Management" menu in the instance details, you can configure the Binlog local management strategy, which can be configured to retain the maximum duration locally (1-168 hours) or the local disk usage percentage (5%-100%). Once configured, local logs will be automatically cleaned up according to the strategy.

Create MySQL Instance from Backup

Users can choose to create a MySQL instance from a backup file by clicking "Create from Backup". The creation process can refer to the Quick Start Guide.

Note:

The compression ratio of the backup file is 5:1, so it is recommended that the disk space size during creation is more than 5 times the size of the backup file. Initialization time depends on the size of the data.

Database Recovery

MySQL supports database rollback. If data is deleted or lost due to an operation error, you can restore the data through "Database Recovery". Just ensure that both the data backup file and binlog exist, and you can restore to any second within 7 days. Select the corresponding instance in the console, and click on Database Recovery in the operation items.

MySQL supports database recovery to new instance or current instance, and supports recovery at the library table level.

a. Database recovery to the original instance, specifying specific libraries and tables:

The rollback will not overwrite the original data, it will create a database with a timestamp. The naming format is "database name_[specified rollback time]_[operation execution time]". Users can perform the necessary operations after confirming that the data is correct.

For example, specify the table nash.test for rollback, after the rollback, the table nash_1662104405_1662104655.test will be created in the database.

b. Database recovery to new instance, specifying specific libraries and tables:

For the scenario of rolling back to a new instance: After the rollback is completed, the event_scheduler will be turned off by default. Users can restart it through the command line, or restart the instance in the control panel to turn it on.

c. Whole database recovery to a new instance:

For the scenario of rolling back to a new instance: After the rollback is completed, the event_scheduler will be turned off by default. Users can restart it through the command line, or restart the instance in the control panel to turn it on.