Shulou(Shulou.com)06/01 Report--

Brief description of MySQL Database:

Before writing this article, let's take a brief look at the MySQL database:

MySQL is an open source relational database management system (RDBMS). The MySQL database system uses the most commonly used database management language-structured query language (SQL) for database management. MySQL has attracted much attention because of its speed, reliability and adaptability. Most people agree that MySQL is the best choice for managing content without the need for transactional processing.

The cause of the failure of the MySQL database:

After receiving the contact from the user, through the communication between the engineer and the user, we know the general fault information. The user's local server operating system is windows2008 R2, a single instance of mysql5.6 is installed in the current environment, the engine type is innodb, and the tablespace used for in-table data storage is independent tablespace. No database backup was performed and binlog was not turned on. Due to the human misoperation, the where clause was not added for filtering when the Delete command was used to delete the data, resulting in the deletion of the whole table data, and no action was taken on the table after deletion.

MySQL database failure recovery scheme:

In response to the above problems, the technical department has drawn up a recovery plan as follows:

1. Classification of fault types: in this failure, the database cannot be directly restored because the production environment is not backed up and the binlog log is not opened, which belongs to the erroneous deletion of mysql-delete data in a typical table.

2. Fault analysis and feasibility scheme formulation: there are three recovery schemes for the loss of records caused by mysql innodb erroneous deletion, which are backup restore, binlog restore and record depth analysis. The first two schemes are not applicable to this failure, and can only be recovered by means of record depth analysis. The recovery principle of this recovery scheme is to simulate the record management mode of the innodb engine and parse the binary file into character records according to the table structure information.

Recovery process for MySQL database:

1. Get the data file: the table structure file and the table data file (.ibd) are compressed and encrypted by the user and transmitted to us through the network.

2. Use the recovery tool to restore:

Based on the table structure script provided by the user, you can use the 5room3 function in this tool to restore.

First, read the table structure information:

Start parsing the record:

By default, this tool extracts the records to SQL backup format, and then restores the records to the database to view the results after parsing:

Acceptance results of MySQL database data:

Randomly select the data in the table to be verified by the user, and count the total number of recovery records. After verification, the user indicates that the sample data is correct, the total number of records in the original table is in line with the number of records in the original table, and the recovery is successful, and the data recovery work is completed.

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.