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

This article introduces the knowledge about "what is the method of incremental data recovery of 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. Work scene

MySQL database is backed up automatically every night at 12:00.

(2) One morning at work, at 9 o'clock, a colleague made a mistake and dropped a database!

(3) Emergency recovery is required! Backup data files as well as incremental binlog files can be utilized for data recovery.

II. Data Recovery Ideas

(1) Use the CHANGE MASTER statement recorded in the fully prepared sql file, the binlog file and its location point information to find out the incremental part of the binlog file.

(2) Export the binlog file to sql file with mysqlbinlog command, and remove the drop statement from it.

(3) Complete data can be restored by exporting sql files from full backup files and incremental binlog files.

III. Examples

----------------------------------------

First, make sure mysql has binlog enabled.

Add the following to the [mysqld] block in/etc/my.cnf:

log-bin=mysql-bin

Then restart the mysql service

----------------------------------------

(1) Create a table customers under ops library

mysql> use ops;

mysql> create table customers(

-> id int not null auto_increment,

-> name char(20) not null,

-> age int not null,

-> primary key(id)

-> )engine=InnoDB;

Query OK, 0 rows affected (0.09 sec)

mysql> show tables;

+---------------+

| Tables_in_ops |

+---------------+

| customers |

+---------------+

1 row in set (0.00 sec)

mysql> desc customers;

+-------+----------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| name | char(20) | NO | | NULL | |

| age | int(11) | NO | | NULL | |

+-------+----------+------+-----+---------+----------------+

3 rows in set (0.02 sec)

mysql> customers values(1,"wangbo","24");

Query OK, 1 row affected (0.06 sec)

mysql> customers values(2,"guohui","22");

Query OK, 1 row affected (0.06 sec)

mysql> customers values(3,"zhangheng","27");

Query OK, 1 row affected (0.09 sec)

mysql> select * from customers;

+----+-----------+-----+

| id | name | age |

+----+-----------+-----+

| 1 | wangbo | 24 |

| 2 | guohui | 22 |

| 3 | zhangheng | 27 |

+----+-----------+-----+

3 rows in set (0.00 sec)

(2) Full backup now

[root@vm-002 ~]# mysqldump -uroot -p -B -F -R -x --master-data=2 ops|gzip >/opt/backup/ops_$(date +%F).sql.gz

Enter password:

[root@vm-002 ~]# ls /opt/backup/

ops_2016-09-25.sql.gz

-----------------

Parameter Description:

-B: Specify database

-F: Refresh log

-R: backup storage process, etc.

-x: lock table

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

-----------------

(3) Insert data again

mysql> customers values(4,"liupeng","21");

Query OK, 1 row affected (0.06 sec)

mysql> customers values(5,"xiaoda","31");

Query OK, 1 row affected (0.07 sec)

mysql> customers values(6,"fuaiai","26");

Query OK, 1 row affected (0.06 sec)

mysql> select * from customers;

+----+-----------+-----+

| id | name | age |

+----+-----------+-----+

| 1 | wangbo | 24 |

| 2 | guohui | 22 |

| 3 | zhangheng | 27 |

| 4 | liupeng | 21 |

| 5 | xiaoda | 31 |

| 6 | fuaiai | 26 |

+----+-----------+-----+

6 rows in set (0.00 sec)

(4) Mistake operation at this time, deleting the test database

mysql> drop database ops;

Query OK, 1 row affected (0.04 sec)

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

(5)View binlog files added after full backup

[root@vm-002 ~]# cd /opt/backup/

[root@vm-002 backup]# ls

ops_2016-09-25.sql.gz

[root@vm-002 backup]# gzip -d ops_2016-09-25.sql.gz

[root@vm-002 backup]# ls

ops_2016-09-25.sql

[root@vm-002 backup]# grep CHANGE ops_2016-09-25.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=106;

This is the binlog file location at the time of full preparation.

That is, line 106 of mysql-bin.000002, so the data in the binlog file before this file is already contained in this fully prepared sql file.

(6) Move binlog file and export it to sql file, eliminate drop statement in it

Check the mysql data storage directory, there are the following can be seen under/var/lib/mysql

[root@vm-002 backup]# ps -ef|grep mysql

root 9272 1 0 01:43 pts/1 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql

mysql 9377 9272 0 01:43 pts/1 00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock

[root@vm-002 backup]# cd /var/lib/mysql/

[root@vm-002 mysql]# ls

ibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.index mysql.sock test

[root@vm-002 mysql]# cp mysql-bin.000002 /opt/backup/

Export binlog file to sql file and vim edit it to delete drop statement

[root@vm-002 backup]# mysqlbinlog -d ops mysql-bin.000002 >002bin.sql

[root@vm-002 backup]# ls

002bin.sql mysql-bin.000002 ops_2016-09-25.sql

[root@vm-002 backup]# vim 002bin.sql #delete the drop statement inside

Note:

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.

(7) Recovery of data

[root@vm-002 backup]# mysql -uroot -p

< ops_2016-09-25.sql Enter password: [root@vm-002 backup]# 查看数据库，看看ops库在不在 mysql>

show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| ops |

| test |

+--------------------+

4 rows in set (0.00 sec)

mysql> use ops;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from customers;

+----+-----------+-----+

| id | name | age |

+----+-----------+-----+

| 1 | wangbo | 0 |

| 2 | guohui | 0 |

| 3 | zhangheng | 0 |

+----+-----------+-----+

3 rows in set (0.00 sec)

At this time, the data of the full standby time is restored

Then, use the 002bin.sql file to restore the new data between the time of full backup and the deletion of the database.

[root@vm-002 backup]# mysql -uroot -p ops select * from customers;

+----+-----------+-----+

| id | name | age |

+----+-----------+-----+

| 1 | wangbo | 24 |

| 2 | guohui | 22 |

| 3 | zhangheng | 27 |

| 4 | liupeng | 21 |

| 5 | xiaoda | 31 |

| 6 | fuaiai | 26 |

+----+-----------+-----+

6 rows in set (0.00 sec)

"MySQL database incremental data recovery method is what" the content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

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.