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

This article introduces the relevant knowledge of "how to incrementally recover data from MySQL database". In the operation process of actual cases, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

I. Overview of Scenes

One morning at 10:00, Xiaoming dropped a database inexplicably.

Use the CHANGE MASTER statement recorded in the complete sql file, binlog file and its location point information to find out the incremental part of binlog file

Use mysqlbinlog command to export the binlog file as sql file, and remove the drop statement from it.

Complete data can be recovered by exporting sql files from full backup files and incremental binlog files

III. Schematic diagram of process

IV. Operation process

1. analog data

CREATE TABLE `student` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` char(20) NOT NULL,

`age` tinyint(2) NOT NULL DEFAULT '0',

PRIMARY KEY (`id`),

KEY `index_name` (`name`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

mysql> insert student values(1,'zhangsan',20);

mysql> insert student values(2,'lisi',21);

mysql> insert student values(3,'wangwu',22);

2. full command

# mysqldump -uroot -p -B -F -R -x --master-data=2 test|gzip >/server/backup/test_$(date +%F).sql.gz

Parameter Description:

-B Specify database

-F Refresh log

-R Backup storage procedures, etc.

-x lock table

--master-data Add CHANGE MASTER statement and binlog file and location point information to backup statement

3. Continue inserting data

mysql> insert student values(6,'xiaoming',20);

mysql> insert student values(6,'xiaohong',20);

At this time, the test database was deleted by mistake.

mysql> drop database test;

At this time, the data written by the user between the time of full backup and the time of error operation needs to be restored in the binlog.

4. View binlog files added after full backup

# cd /server/backup/

# ls

test_2016-08-02.sql.gz

# gzip -d test_2016-08-02.sql.gz

# grep CHANGE test_2016-08-02.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=107;

This is the location of the binlog file at the time of full backup, i.e. line 107 of mysql-bin.000003, so the data in the binlog file before this file is already contained in this fully prepared sql file.

5. Move the binlog file and read sql, removing the drop statement

# cp /data/3306/mysql-bin.000003 /server/backup/

# mysqlbinlog -d test mysql-bin.000003 >003bin.sql

#Edit files with vim, eliminate drop statements

The binlog file must be removed before full backup data is recovered, otherwise statements will continue to be written to binlog during recovery, resulting in confusion in incremental recovery data.

6. recover data

# mysql -uroot -p

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.