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 * from userinfo; +----------+---------------+-----------+ | 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.00 sec) 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.00 sec) mysql> insert into userinfo (fuser_name,fuser_age) values ("chenjianwen03",22); Query OK, 1 row affected (0.00 sec) mysql> insert into userinfo (fuser_name,fuser_age) values ("chenjianwen04",23); Query OK, 1 row affected (0.00 sec) mysql> insert into userinfo (fuser_name,fuser_age) values ("chenjianwen05",21); Query OK, 1 row affected (0.00 sec) 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)
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.00 sec) [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】