Shulou(Shulou.com)05/31 Report--

This article is about how to achieve logical data recovery in MySQL. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article. Let's take a look at it.

Support capability of backup system

Granularity

Backup Typ

Backup mode

Example

Full backup

Physical backup

Logical backup

Example

Incremental backup

Physical backup

Example

Log backup

Independent service

Database

Data sheet

Table structure

Object backup

Physical backup

Logical backup

In fact, data resilience at the object level is very important, and there are many ways to use it on the business side. The overall data recovery process is as follows:

How to plan and design the logical backup and recovery system, after some discussion, I made the following first version design.

1. Data backup

Logical backup recovery is mainly oriented to two dimensions:

Instance level: implement table structure, table structure + data backup

Library / table level: implement table structure, table structure + data backup

The scope of support for current logical backup restores:

1) an example of the master-slave topological relationship of the database. The logical backup operation is performed on the corresponding slave database according to the topological relationship.

2) single instance node. Logical backup operations are performed on single instance node, and backup capacity and load need to be evaluated.

Currently, nodes based on middleware MyCAT are not supported.

For the information entered as a domain name or Master IP, it needs to be transformed into the corresponding IP information from the library or single instance through the topological relationship.

For the backup of library table files, you can set the following rules:

1) tables with less than 50 million data per table or less than 10 gigabytes of table capacity can support logical backup. In addition, appropriate tips are needed to avoid such operations as much as possible.

2) choose a backup database with a capacity of less than 30g, and you need to be prompted to avoid such operations.

The backend needs to provide the corresponding library, and the table stores the metadata information related to capacity / data volume (estimated capacity is fine, no exact value is required). At present, it can be supported by database baseline and data table base line metadata in lifecycle management.

For the assessment of backup duration, the following incremental ranges are currently available:

1) backup capacity is less than 500m, indicating that the estimated completion time is less than 5 minutes.

2) the backup capacity is less than 2G, and the completion time is less than 10 minutes.

3) the backup capacity is less than 10G and the completion time is less than 20 minutes.

4) the backup capacity is less than 30g, and the completion time is less than 30 minutes.

After the backup is completed, you can provide the corresponding instant messaging prompt to inform the business side that the backup operation has been completed.

The files generated by the backup need to be stored on the designated backup machine and stored in accordance with the following directory rules

Backup BASE directory: / data/logical_backup/

Backup directory of the instance in the backup machine:

[Slave_IP] _ [port] / [YYYYmmdd] /

Corresponding backup file naming rules:

[Slave_IP] _ [port] _ [db_name] _ [YYYYmmdd] _ [hhmiss] _ [username]. Sql

After the data backup, the corresponding configuration file needs to be generated, and the data format is JSON. The corresponding data format can be parsed and displayed when the data is restored.

The retention period of backup files is tentatively set at 7 days, and you need to be prompted at the time of backup.

two。 Data recovery

Data recovery is initiated by business self-service, and the relevant data recovery resources have a usage time limit, which is tentatively scheduled for 2 days. After 2 days, the corresponding data and permissions will be reclaimed accordingly, and there will be corresponding resource recovery prompts. At the same time, relevant prompts need to be made in use.

The data recovery granularity is based on the object (database, table) granularity specified in the backup data file. If business classmate A backs up table db1.table1,db1.table2, it cannot only restore db1.table1. The recovery work will restore db1.table1,db1.table2 directly.

The selection of data recovery files needs to consider the relevant permissions. If business classmate A backs up table db1.table1,db1.table2 and business classmate B backs up table db2.table3,db2.table4, they should not be visible to each other in selecting backup files.

If multiple data recovery is needed for the same database, it can be dynamically provisioned according to the resource ratio of the server. For example, there are 4 instances in a data recovery server with ports of 4306-4309. Business students can recover 4 times in use, and in actual use, resources need to be expanded according to the frequency and situation used. A data recovery server group should be followed.

In data recovery, which instance to restore to, the backend provides the corresponding scheduling logic for allocation.

For the use of data recovery, you can provide integrated services. The business side uses client tools such as workbench to connect and use. For the part where the permissions are activated, you need to obtain the IP address of the office computer according to the user's domain name information, and activate the relevant database permissions. After activation, there are corresponding instant messaging prompts.

The permissions in the database layer are enabled. If the database user already exists, the corresponding permissions are supplemented. If the database user does not exist, you need to create a user in the specified instance and assign the corresponding permissions.

The use of the whole backup data needs to take into account the convenience and security, and can be docked with the Ministry of Security to evaluate and consider part of the work.

Backup and recovery related operations and history need to be stored and managed in a unified manner

For the log related to data recovery, in order to facilitate subsequent management and tracking, it is necessary to record the path of the backup file in the recovery record.

The above is how to achieve logical data recovery in MySQL. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.