mysql 传统数据恢复_mysql数据安全一之数据恢复案例

mysql数据安全一之数据恢复案例

--chenjianwen

应用场景：适宜开启binlog 日志功能，定时备份并使用--master-data参数备份，在某个时间点丢失数据，用于数据恢复

开篇总结：

(1)本案例适用于人为SQL语句造成的误操作或者没有主从复制等的热备情况宕机时的修复

(2)恢复条件为mysql要开启binlog日志功能，并且要全备和增量的所有数据

(3)恢复时建议对外停止更新，即禁止更新数据库

(4)先恢复全量，然后把全备时刻点以后的增量日志，按顺序恢复成SQL文件，然后把文件中有问题的SQL语句删除(也可通过时间和位置点)，再恢复到数据库。

1.确保 MySQL 开启了 binlog 日志功能，并做好备份计划任务

2.mysql备份语句：mysqldump -uroot -pxxx -B -F -R -x --master-data=2 chenjianwen01|gzip >./chenjianwen01_$(date +%F).sql.gz

参数说明：-B：指定数据库-F：刷新日志-R：备份存储过程等-x：锁表

–master-data：在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息

3.解压保留源文件命令：gunzip -c chenjianwen01_2017-08-30.sql.gz > chenjianwen01_2017-08-30.sql

4.查看表中的数据：

mysql> select * fromuserinfo;+----------+---------------+-----------+

| fuser_id | fuser_name | fuser_age |

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

| 2 | chenjianwen02 | 22 |

| 3 | 陈建文 | 22 |

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

2 rows in set (0.00 sec)

5.再次向数据表中插入数据：

mysql>show tables;+-------------------------+

| Tables_in_chenjianwen01 |

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

| classinfo |

| userinfo |

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

2 rows in set (0.00sec)

mysql>desc userinfo;+------------+-------------+------+-----+---------+----------------+

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

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

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

| fuser_name | varchar(64) | NO | | NULL | |

| fuser_age | int(11) | NO | | NULL | |

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

3 rows in set (0.00sec)

mysql> insert into userinfo (fuser_name,fuser_age) values ("chenjianwen03",22);

Query OK,1 row affected (0.00sec)

mysql> insert into userinfo (fuser_name,fuser_age) values ("chenjianwen04",23);

Query OK,1 row affected (0.00sec)

mysql> insert into userinfo (fuser_name,fuser_age) values ("chenjianwen05",21);

Query OK,1 row affected (0.00sec)

mysql> select * fromuserinfo;+----------+---------------+-----------+

| fuser_id | fuser_name | fuser_age |

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

| 2 | chenjianwen02 | 22 |

| 3 | 陈建文 | 22 |

| 4 | chenjianwen03 | 22 |

| 5 | chenjianwen04 | 23 |

| 6 | chenjianwen05 | 21 |

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

5 rows in set (0.00 sec)

6.此时误操作，删除了数据库

mysql>drop database chenjianwen01;

Query OK,2 rows affected (0.02 sec)

此时，全备之后到误操作时刻之间，用户写入的数据在 binlog 中，需要恢复出来！

7.查看备份之后新增的 binlog 文件

[root@cjw_jdy backup]# cd /root/backup/[root@cjw_jdy backup]#gunzip -c chenjianwen01_2017-08-30.sql.gz > chenjianwen01_2017-08-30.sql

[root@cjw_jdy backup]#grep CHANGE chenjianwen01_2017-08-30.sql-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000022', MASTER_LOG_POS=120;

这是备份时刻的 binlog 文件位置，即 mysql-bin.000022 的 120 行，因此在该文件之前的 binlog 文件中的数据都已经包含在这个备份的 SQL 文件中了。

8.拷贝binlog 文件，并将mysql-bin.000022导出为sql文件，并删除里面的drop语句

[root@cjw_jdy backup]# cp /home/mysql/data/mysql-bin.000022.

[root@cjw_jdy backup]#ls

20170802 chenjianwen01_2017-08-30.sql chenjianwen01_2017-08-30.sql.gz mysql-bin.000022[root@cjw_jdy backup]# mysqlbinlog-d chenjianwen01 mysql-bin.000022 > mysql-bin.000022.sql

[root@cjw_jdy backup]#ls

20170802 chenjianwen01_2017-08-30.sql chenjianwen01_2017-08-30.sql.gz mysql-bin.000022 mysql-bin.000022.sql

[root@cjw_jdy backup]#cat mysql-bin.000022.sql | grep -i drop

[root@cjw_jdy backup]# rm -rf /home/mysql/data/mysql-bin.000022

注意：在恢复全备数据之前必须将该binlog文件移出，否则恢复过程中，会继续写入语句到binlog，最终导致增量恢复数据部分变得比较混乱。

9.恢复数据，导入前需要先创建数据库，查看表信息，没有备份后插入的数据

mysql>create database chenjianwen01 charset utf8;

Query OK,1 row affected (0.00sec)

[root@cjw_jdy backup]# mysql-uroot -p chenjianwen01 < chenjianwen01_2017-08-30.sql

mysql> select *from userinfo;+----------+---------------+-----------+

| fuser_id | fuser_name | fuser_age |

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

| 2 | chenjianwen02 | 22 |

| 3 | 陈建文 | 22 |

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

2 rows in set (0.00 sec)

10.使用mysql-bin.000022.sql文件恢复备份时刻到删除数据库之间新增的数据

[root@cjw_jdy backup]# mysql -uroot -p chenjianwen01 < mysql-bin.000022.sql

11.检查恢复后的数据

mysql> select *from userinfo;+----------+---------------+-----------+

| fuser_id | fuser_name | fuser_age |

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

| 2 | chenjianwen02 | 22 |

| 3 | 陈建文 | 22 |

| 4 | chenjianwen03 | 22 |

| 5 | chenjianwen04 | 23 |

| 6 | chenjianwen05 | 21 |

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

5 rows in set (0.00 sec)

mysql数据库增量数据恢复的实例过程【Good job】