Data Recovery Method of MySQL Database Operation and Maintenance

The previous three articles introduced the common backup methods of MySQL database, including logical backup and physical backup. This article will summarize the data recovery related contents of MySQL database under 1. These data recovery schemes in the previous backup content introduction, here summarizes 1 under the recovery scheme, and combined with the database binary log to do the demonstration of data recovery!

1. Recovery programme

1. The amount of data is not particularly large, so the data backed up by mysqldump command can be restored by mysql client command or source command;
2. Use Xtrabackup to complete the physical backup and recovery of the database, during which the database service needs to be restarted;
3. Use LVM snapshot volume to complete the physical backup and recovery of the database, during which the database service needs to be restarted;

2. Point-in-time recovery using mysqlbinlog

1. Introduction

mysqlbinlog is a tool for reading statements from binary logs, which comes with mysql after installation.

Recovery principle of binary log

When using mysqldump to back up the database, the generated backup file contains the time point when the database DML is operated and the binary log position information when backing up. If the database is single, it can start from a certain time point and recover at a time point; If it is a master-slave architecture, the recovery can be completed according to the time point or location point according to the--master-data=2 and--single-transaction at the time of backup.

3, binary log recovery example

(1) Sample of single library recovery

Create a database and insert test data

mysql> SHOW CREATE DATABASE test_db;
mysql> CREATE TABLE `student` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) NOT NULL,
 `age` tinyint(4) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
mysql> INSERT INTO student (name,age) VALUES('Jack',23),('Tomcat',24),('XiaoHong',22),('ZhangFei',29);

Use mysqldump for a full backup, scrolling through the logs while remembering the binary log file name and the location of the logs

[root@WB-BLOG ~]# mysqldump -uroot -proot -h127.0.0.1 -P3306 --databases test_db --single-transaction --triggers --routines --flush-logs --events > /tmp/test_db.sql
[root@WB-BLOG ~]# mysql -e "show binary logs" > bin_pos_`date +%F`.out

Look at the binary log file name and log point location as follows

mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name     | File_size |
+------------------+-----------+
| mysql-bin.000001 |   1497 |
| mysql-bin.000002 |    397 |
+------------------+-----------+
2 rows in set (0.00 sec)

After using 1 period of time, I accidentally misoperated and executed the following statement, which modified all the data in the database

mysql> UPDATE STUDENT SET name = 'admin';

After a period of time, may be a few minutes, may also be a few hours, someone reflected that there was a problem with the website login, and found that many data were modified by mistake, and during this period of time, there was also a direct write operation, such as adding the following records

mysql> INSERT INTO student(name,age) VALUES('Hbase',23),('BlackHole',30);

At this time, you need to recover the data. First, in order to prevent the data from continuing to write, you can lock the table, suspend the writing business, notify the user of system maintenance, and then perform the following operations:

# Log in to the database and lock the table. At this time, the table can only be read, not written 
mysql> USE test_db;
mysql> LOCK TABLE student READ;
# Then reopen (note reopen) 1 A session Window, otherwise the lock will be released after the session exits. Then compress and back up the existing data and 2 Binary log file 
[root@WB-BLOG mysql_logs]# tar zcvf mysql_data.tar.gz /mysql_data/*
[root@WB-BLOG mysql_logs]# tar zcvf mysql_bin.tar.gz /mysql_logs/*
# Import the most recently backed up 1 Subtotal standby data 
[root@WB-BLOG ~]# mysql -uroot -proot -h127.0.0.1 -P3306 < /tmp/test_db.sql 

# View the when it is fully prepared 2 Binary log files and log points 
[root@WB-BLOG ~]# cat bin_pos_2018-06-24.out 
  Log_name    File_size
  mysql-bin.000001   1497
  mysql-bin.000002    397
# Will 861 After this point 2 The binary log file is converted to 1 A sql Documents 
[root@WB-BLOG bin]# ./mysqlbinlog /mysql_logs/mysql-bin.000002 --start-position=397 > /tmp/tmp.sql
# Use vim The editor edits this sql File, find the unconditional UPDATE Statement, and then delete it, and then delete it UPDATE Statement after the sql Script content is imported into the database 
[root@WB-BLOG bin]# vim /tmp/tmp.sql
  use `test_db`/*!*/;
  SET TIMESTAMP=1522088753/*!*/;
  update student set name = 'admin' # Delete this 1 Sentence 
[root@WB-BLOG bin]# mysql -uroot -proot -h127.0.0.1 -P3306 < /tmp/tmp.sql
# Log in to the database to query whether the data is restored. You can check whether the data that has been modified by mistake is restored, then unlock the table and prepare all the data again 
mysql> UNLOCK TABLES;

(2) Master-slave schema data recovery example

Environment

Main Library: 192.168. 199.10 (node01)
From Library: 192.168. 199.11 (node02)

First, stop the SQL thread of the slave library, then fully prepare the data on the slave library, and input the information of "SHOW SLAVE STATUS" into the backup file. The output information of "SHOW SLAVE STATUS" records the information of which position point is currently applied to the master library

# Log in to the slave library and close SQL Thread 
mysql> STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.01 sec)
# Then record the current application of the master library in the slave library 2 Binary log file information 
[root@node02 mysql_data]# mysql -e "SHOW SLAVE STATUS \G" > slave_`date +%F`.info
[root@node02 mysql_data]# mysqldump -uroot -proot -h127.0.0.1 -P3306 --databases test_db --routines --triggers --single-transaction > /tmp/mysql_test_db_`date +%F`.sql

After the backup from the library is complete, restart the SQL thread from the library

mysql> START SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.01 sec)

After the SQL thread is started, DML operations on the master library during the backup period are resynchronized to the slave library. If there is an error operation on the master library, updating all the data in the student table without adding conditions, resulting in all the data in the table being modified, then the slave library is also modified due to synchronous operation

# Log in to the main library, modify the external users of the database so that they do not provide services temporarily, and then scroll the log 
mysql> UPDATE mysql.user SET Host = '127.0.0.1' WHERE User='tomcat';
Query OK, 1 rows affected (0.00 sec)
# Refresh permission table 
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
# Rolling log 
mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.01 sec)
# The data to be backed up from the library and the slave library at the time of backup slave The information is transmitted to the main library 
[root@node02 mysql_data]# scp /tmp/mysql_test_db_2018-06-24.sql 192.168.199.10:/root/
[root@node02 mysql_data]# scp slave_2018-06-24.info node01:/root/

Back up the data directory and binary log file directory of the main library

[root@node01 mysql_logs]# tar zcvf mysql_master_data.tar.gz /mysql_data/*
[root@node01 mysql_logs]# tar zcvf mysql_logs.tar.gz /mysql_logs/*  

Import data from the last backup of the library

[root@WB-BLOG ~]# mysqldump -uroot -proot -h127.0.0.1 -P3306 --databases test_db --single-transaction --triggers --routines --flush-logs --events > /tmp/test_db.sql
[root@WB-BLOG ~]# mysql -e "show binary logs" > bin_pos_`date +%F`.out

0

View the name and location of the master library binary log file applied from the slave library at the time of backup

[root@WB-BLOG ~]# mysqldump -uroot -proot -h127.0.0.1 -P3306 --databases test_db --single-transaction --triggers --routines --flush-logs --events > /tmp/test_db.sql
[root@WB-BLOG ~]# mysql -e "show binary logs" > bin_pos_`date +%F`.out

1

Starting from this log file and log point, convert the log file after 395 log point to sql script. If there are multiple binary log files, it can be converted to sql script at the same time, as follows

[root@WB-BLOG ~]# mysqldump -uroot -proot -h127.0.0.1 -P3306 --databases test_db --single-transaction --triggers --routines --flush-logs --events > /tmp/test_db.sql
[root@WB-BLOG ~]# mysql -e "show binary logs" > bin_pos_`date +%F`.out

2

Find the incorrect update statement, delete it, and import the incremental sql script into the database

[root@node01 mysql_logs]# vim /tmp/tmp.sql
  use `test_db`/*!*/;
  update student set name = 'admin' # Delete this 1 Sentence 
[root@node01 mysql_logs]# mysql -uroot -proot -h127.0.0.1 -P3306 < /tmp/tmp.sql 

Log in to the database to check whether the data is normal or not, and whether the data modified by mistake has been recovered. If it is recovered, prepare all the data on the master library, and then transfer it to the slave library to complete the recovery of the slave library

[root@WB-BLOG ~]# mysqldump -uroot -proot -h127.0.0.1 -P3306 --databases test_db --single-transaction --triggers --routines --flush-logs --events > /tmp/test_db.sql
[root@WB-BLOG ~]# mysql -e "show binary logs" > bin_pos_`date +%F`.out

4

Since the --master-date=1 parameter was added when backing up on the primary library, there is no need to re-perform the change master operation after importing from the library.

Log in to the slave library to see if SHOW SLAVE STATUS information is normal. If it is normal, log in to the master library, modify the authorization table again, and then provide services to the outside world

[root@WB-BLOG ~]# mysqldump -uroot -proot -h127.0.0.1 -P3306 --databases test_db --single-transaction --triggers --routines --flush-logs --events > /tmp/test_db.sql
[root@WB-BLOG ~]# mysql -e "show binary logs" > bin_pos_`date +%F`.out

5

After the execution is completed, the master-slave data is recovered.