How to realize data recovery in Mysql Database

This article will explain in detail how to achieve data recovery in the Mysql database. The content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Online environment

The mysql database has an one-master and multi-slave architecture, in which the master and write are separated from the read and write, and the dedicated slave database is used for data backup, with one full backup at 0 o'clock every day and an incremental backup at 12:00. If the amount of data in the initial stage is very small, according to this scheme, when the subsequent data volume is large and read and write frequently, relevant adjustments will be made to increase the incremental backup frequency.

System environment

[root@mysql-1 ~] # cat / etc/redhat-release

CentOS release 6.8 (Final)

[root@mysql-1 ~] # uname-r

2.6.32-642.el6.x86_64

[root@mysql-1 ~] # mysql- v

Mysql Ver 14.14 Distrib 5.7.17, for linux-glibc2.5 (x86 / 64) using EditLine wrapper

Master-slave synchronization

3306Mutual talk-> 3307

3307 enable binlog log to be used as backup server, complete at 0: 00, incremental backup at 12:00

[root@mysql-1 ~] # netstat-lntup | grep 33

Tcp 0 0: 3306: * LISTEN 42473/mysqld

Tcp 0 0: 3307: * LISTEN 42769/mysqld

2 analog online data writing

Database synchronization completed, enable the binlog log function of 3307 slave library

View the current log file

Write data test synchronization

Note: check the modification time of the log file and find that data has been written.

Execute the full file at this time

Write data after it is complete

A misoperation occurred at this time and a data was deleted.

Misoperation is impossible to detect in the first place, so continue to write data

At this time, it is found that there is a problem with the database data. Some data is inaccessible and needs to be restored.

3 recover data

The specific operations of data recovery are as follows

1. Stop master-slave synchronization and apply read and write operations with the database to prevent data from being written again.

2. Refresh binlog and generate a new log file

3. Restore all the files to the main library

4. Merge binlog files to generate sql and delete misoperation statements

5. Perform incremental recovery

At this time, the data of the main database has been restored successfully.

4 testing master-slave synchronization

Restart synchronization to test whether the data is synchronized

On how to achieve data recovery in the Mysql database to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.