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

This article will explain in detail how to restore MySQL database data, 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.

I. recovery plan

1. The amount of data is not very large, so you can restore the data backed up by mysqldump command using mysql client command or source command.

2. Use Xtrabackup to complete the physical backup and restore of the database, during which you need to restart the database service.

3. Use the LVM snapshot volume to complete the database physical backup and restore, during which you need to restart the database service.

Second, use mysqlbinlog for point-in-time recovery

1. Introduction

Mysqlbinlog is a tool that reads statements from binary logs and comes with it after the mysql installation is complete.

2. The principle of binary log recovery

When using mysqldump to back up the database, the backup file generated contains the time point of the database DML operation and the binary log location information at the time of backup. If you have a single database, you can start from a certain point in time and perform a point-in-time recovery; if it is a master-slave architecture, you can complete the recovery according to the point in time or location point according to the-- master-data=2 and-- single-transaction at the time of backup.

3. Binary log recovery example

(1) example of single database 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 full backup, scroll the log during backup, and remember the name of the binary log file and the location of the log

[root@WB-BLOG] # mysqldump-uroot-proot-h227.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

At this point, view 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 it for a period of time, I accidentally misoperated and executed the following statement to modify all the data in the database.

Mysql > UPDATE STUDENT SET name = 'admin'

After a period of time, maybe a few minutes, maybe a few hours, someone reported that there was a problem with the login of the website, checked and found that a lot of data had been mistakenly modified, and during this period, there have been write operations all the time, such as adding the following records

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

At this point, you need to recover the data. To prevent the data from continuing to be written, you can lock the table, suspend the writing business, notify the user of system maintenance, and then perform the following actions:

# Log in to the database and lock the table. At this time, the table can only be read, cannot write mysql > USE test_db;mysql > LOCK TABLE student READ;#, and then reopen a session window, otherwise the lock will be released after the session is released. Then compress the backup existing data and 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/*# to import the last full backup data [root@WB-BLOG ~] # mysql-uroot-proot-h227.0.0.1-P3306

< /tmp/test_db.sql #查看全备时的二进制日志文件和日志点[root@WB-BLOG ~]# cat bin_pos_2018-06-24.out Log_name File_size mysql-bin.000001 1497 mysql-bin.000002 397#将861这个点之后的二进制日志文件转换为一个sql文件[root@WB-BLOG bin]# ./mysqlbinlog /mysql_logs/mysql-bin.000002 --start-position=397 >

/ tmp/tmp.sql# uses the vim editor to edit the sql file, find the unconditional UPDATE statement in it, delete it, and then import the contents of the sql script after deleting the UPDATE statement into the database [root@WB-BLOG bin] # vim / tmp/tmp.sql use `test_ DB` / *! *; SET timestamp 1522088753 Update student set name = 'admin' # delete this sentence [root@WB-BLOG bin] # mysql-uroot-proot-h227.0.0.1-P3306

< /tmp/tmp.sql#登录数据库查询数据是否恢复，可以查看被误修改的数据是否还原，然后对表执行解锁，再次全备数据mysql>

UNLOCK TABLES

(2) Master-slave architecture data recovery example

Environment

Main library: 192.168.199.10 (node01)

Slave Library: 192.168.199.11 (node02)

First stop the SQL thread from the library, then complete the data from the library, and enter the "SHOW SLAVE STATUS" information into the backup file. The output information of "SHOW SLAVE STATUS" records the information of which location is currently applied to the main library.

# Log in to the slave library, and then close the SQL thread mysql > STOP SLAVE SQL_THREAD Query OK, 0 rows affected (0.01log) # then record the binary log file information of the main library currently applied from the slave library [root@node02 mysql_data] # mysql-e "SHOW SLAVE STATUS\ G" > slave_ `date +% F`.info [root@node02 mysql_data] # mysqldump-uroot-proot-h227.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 slave library

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

After starting the DML thread, the SQL operations on the master library during the backup period are resynchronized to the slave library. If a misoperation occurs on the master database, and all the data in the student table is updated without conditions, all the data in the table is modified, and the slave database is also modified because of the synchronization operation.

# Log in to the main database, modify the external users of the database so that they do not provide services for the time being, and then scroll mysql > UPDATE mysql.user SET Host = '127.0.0.1' WHERE User='tomcat';Query OK, 1 rows affected (0.00 sec) # refresh permissions table mysql > FLUSH PRIVILEGES;Query OK, 0 rows affected (0.00 sec) # scroll log mysql > FLUSH LOGS Query OK, 0 rows affected (0.01 sec) # transfer the data backed up from the slave database and the slave slave information at the backup time to the master database [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 from the library

[root@node01 mysql_logs] # mysql-uroot-proot-h227.0.0.1-P3306

< /root/mysql_test_db_2018-03-26.sql #注意：上述的操作不能锁主库的表，否则全备数据无法导入。 查看备份时刻的从库中应用到的主库二进制日志文件名称及位置点 [root@node01 mysql_logs]# cat /root/slave_2018-03-26.info Master_Log_File: master-bin.000002 #备份时所应用的主库二进制日志文件名称 Read_Master_Log_Pos: 395 #备份时所应用的主库二进制日志文件的位置 从该日志文件及日志点开始，将395日志点之后的日志文件转换为sql脚本，如果有多个二进制日志文件可以同时转换为sql脚本，如下所示 [root@node01 mysql_logs]# mysqlbinlog /mysql_logs/master-bin.000002 --start-position=395 >

/ tmp/tmp.sql# merges master-bin.000003,master-bin.000004,master-bin.000005 into / tmp.sql file [root@node01 mysql_logs] # mysqlbinlog / mysql_logs/master-bin.00000 {3meme4je 5}-- start-position=395 > / tmp/tmp.sql

Find the misoperated update statement, delete the statement, 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 the sentence [root@node01 mysql_logs] # mysql-uroot-proot-h227.0.0.1-P3306

< /tmp/tmp.sql 登录数据库，查看数据是否正常，被误修改的数据是否已经恢复，如果恢复，则在主库上全备数据，然后传到从库，完成从库恢复 [root@node01 mysql_data]# mysqldump -uroot -proot -h227.0.0.1 -P3306 --databases test_db --routines --triggers --single-transaction --master-date=1 >

/ tmp/master_test_db_ `date +% F`.sql [root@node01 mysql_data] # scp / tmp/master_test_db_2018-06-24.sql node01:/root/# if read-only is set from the library, you need to first remove the read-only restriction mysql > SET GLOBAL read_only = OFF;Query OK, 0 rows affected (0.00 sec) # import data into the slave library [root@node02 mysql_logs] # mysql-uroot-proot-h227.0.0.1-P3306

< /root/master_test_db_2018-06-24.sql#开启从库的只读mysql>

SET GLOBAL read_only = ON;Query OK, 0 rows affected (0.00 sec)

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

Log in to the slave database to check whether the SHOW SLAVE STATUS information is normal. If so, log in to the master library, modify the authorization table again, and then provide services to the outside world.

Mysql > UPDATE mysql.user set Host = '192.168.0%' WHERE User = 'tomcat';mysql > FLUSH PRIVILEGES;Query OK, 0 rows affected (0.00 sec) about how to restore MySQL database data is shared here. I hope the above content can be helpful to you and learn more. If you think the article is good, you can share it for more people to see.

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.