Mysql 通过全量备份和binlog恢复整体数据

某天工作时间,一个二货犯晕登错生产当测试环境了,直接drop了一个数据库,需要紧急恢复!可利用备份的数据文件以及增量的 binlog 文件进行数据恢复。

具体思路归纳几点:

1、恢复条件为 MySQL 要开启 binlog 日志功能,并且要全备和增量的所有数据。

2、恢复时建议对外停止更新,即禁止更新数据库。(这点很重要)

3、先恢复全量,然后把全备时刻点以后的增量日志,按顺序恢复成 SQL 文件,

4、然后把文件中有问题的SQL语句删除(也可通过时间和位置点),再恢复到数据库。

具体实例演示:

1、首先要确保MySQL开启了binlog日志功能,检查如下结果

mysql> show variables like ‘%log_bin%‘;
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /mysql_data/mysql-bin       |
| log_bin_index                   | /mysql_data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+
6 rows in set (0.01 sec)

2、查看当前测试表里面数据信息后面好做对比

mysql> select * from Student;
+-----------+-----------+------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept |
+-----------+-----------+------+------+-------+
| 200215121 | 李勇      | 男   |   20 | CS    |
| 200215122 | 刘晨      | 女   |   19 | CS    |
| 200215123 | 王敏      | 女   |   18 | MA    |
| 200215125 | 张立      | 女   |   19 | IS    |
| 200215126 | 虎威      | 男   |   25 | CS    |
| 200215127 | 魏大师    | 男   |   35 | IS    |
| 200215128 | 老谢      | 男   |   33 | MA    |
| 200215129 | 小贾      | 男   |   30 | CS    |
| 200215130 | 会民      | 男   |   23 | CS    |
| 200215131 | 陈兴      | 男   |   33 | MA    |
| 200215132 | 阿帆      | 男   |   36 | IS    |
| 200215133 | 国良      | 男   |   40 | IS    |
| 200215134 | 老宋      | 男   |   40 | IS    |
| 200215135 | 光光      | 男   |   35 | IS    |
| 200215136 | 王老板    | 女   |   27 | IS    |
+-----------+-----------+------+------+-------+
15 rows in set (0.00 sec)

3、现在进行全备份

mysqldump -u root -p -B -F -R -x  student|gzip > /mysql_backup/student_$(date +%Y%m%d_%H%M%S).sql.gz

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

4、再次插入新数据

INSERT INTO Student VALUES (‘200215137‘,‘程程‘,‘女‘,30,‘IS‘);
INSERT INTO Student VALUES (‘200215138‘,‘琪琪‘,‘男‘,29,‘MA‘);
INSERT INTO Student VALUES (‘200215139‘,‘龙龙‘,‘男‘,27,‘IS‘);

5、检查是否插入成功,如下可以看出已经插入成功

mysql> select * from Student;
+-----------+-----------+------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept |
+-----------+-----------+------+------+-------+
| 200215121 | 李勇      | 男   |   20 | CS    |
| 200215122 | 刘晨      | 女   |   19 | CS    |
| 200215123 | 王敏      | 女   |   18 | MA    |
| 200215125 | 张立      | 女   |   19 | IS    |
| 200215126 | 虎威      | 男   |   25 | CS    |
| 200215127 | 魏大师    | 男   |   35 | IS    |
| 200215128 | 老谢      | 男   |   33 | MA    |
| 200215129 | 小贾      | 男   |   30 | CS    |
| 200215130 | 会民      | 男   |   23 | CS    |
| 200215131 | 陈兴      | 男   |   33 | MA    |
| 200215132 | 阿帆      | 男   |   36 | IS    |
| 200215133 | 国良      | 男   |   40 | IS    |
| 200215134 | 老宋      | 男   |   40 | IS    |
| 200215135 | 光光      | 男   |   35 | IS    |
| 200215136 | 王老板    | 女   |   27 | IS    |
| 200215137 | 程程      | 女   |   30 | IS    |
| 200215138 | 琪琪      | 男   |   29 | MA    |
| 200215139 | 龙龙      | 男   |   27 | IS    |
+-----------+-----------+------+------+-------+
18 rows in set (0.00 sec)

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

mysql> drop database student;
Query OK, 3 rows affected (0.11 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

7.查看全备之后备份文件

[root@ocbsdb01 mysql_backup]# cd  /mysql_backup
[root@ocbsdb01 mysql_backup]# gzip -d student_20170829_090319.sql.gz 
[root@ocbsdb01 mysql_backup]# ls
student_20170829_090319.sql
[root@ocbsdb01 mysql_backup]# vim student_20170829_090319.sql

8.检查并移动binlog文件,并导出为 SQL 文件剔除其中的 drop 语句,查看 MySQL 的数据存放目录,

由下面可知是在/mysql_data下,将 binlog 文件导出SQL文件,并vim编辑它删除其中的 drop 语句。

/home/mysql/mysql5/bin/mysqlbinlog   --no-defaults  /tmp/mysql-bin.000004 > /tmp/04.sql

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

在转换sql的时候可能会报错,具体信息如下:

[[email protected] tmp]# /home/mysql/mysql5/bin/mysqlbinlog   /tmp/mysql-bin.000004 > /tmp/04.sql

mysqlbinlog: [ERROR] unknown variable ‘default-character-set=utf8‘

原因是mysqlbinlog这个工具无法识别binlog中的配置中的default-character-set=utf8这个指令。

两个方法可以解决这个问题

在MySQL的配置/etc/my.cnf中将default-character-set=utf8 修改为 character-set-server = utf8,但是这需要重启MySQL服务,如果你的MySQL服务正在忙,那这样的代价会比较大。
用mysqlbinlog --no-defaults mysql-bin.000004 命令打开

9、开始恢复全备数据

[root@ocbsdb01 tmp]# mysql -u root -p < /mysql_backup/student_20170829_090319.sql
Enter password:

查看数据看看,student在不在,可以看到已经在了

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

11、开始恢复增量数据

使用04.sql文件恢复全备时刻到删除数据库之间新增的数据,编辑04bin.sql

#删除里面的drop语句

[root@ocbsdb01 tmp]# vim 04.sql

将drop 操作下面的内容删除

drop database student
/*!*/;
SET @@SESSION.GTID_NEXT= ‘AUTOMATIC‘ /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
/*!50003 SET [email protected]_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

不然会报如下错误:

ERROR 1790 (HY000) at line 96: @@SESSION.GTID_NEXT cannot be changed by a client that owns a GTID. 

The client owns ANONYMOUS. Ownership is released on COMMIT or ROLLBACK.

我的binlog文件具体内容如下:

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @[email protected]@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170829  9:03:22 server id 201609  end_log_pos 123 CRC32 0x669b3a18 Start: binlog v 4, server v 5.7.18-log created 170829  9:03:22
# Warning: this binlog is either in use or was not closed properly.
BINLOG ‘
Wr2kWQ+JEwMAdwAAAHsAAAABAAQANS43LjE4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ARg6m2Y=
‘/*!*/;
# at 123
#170829  9:03:22 server id 201609  end_log_pos 154 CRC32 0xf909764e Previous-GTIDs
# [empty]
# at 154
#170829  9:04:42 server id 201609  end_log_pos 219 CRC32 0x754f68f2 Anonymous_GTIDlast_committed=0sequence_number=1
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS‘/*!*/;
# at 219
#170829  9:04:42 server id 201609  end_log_pos 294 CRC32 0xdef39415 Querythread_id=7exec_time=0error_code=0
SET TIMESTAMP=1503968682/*!*/;
SET @@session.pseudo_thread_id=7/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1344274432/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 294
#170829  9:04:42 server id 201609  end_log_pos 359 CRC32 0xad757652 Table_map: `student`.`Student` mapped to number 225
# at 359
#170829  9:04:42 server id 201609  end_log_pos 421 CRC32 0x5da9edf3 Write_rows: table id 225 flags: STMT_END_F
BINLOG ‘
qr2kWROJEwMAQQAAAGcBAAAAAOEAAAAAAAEAB3N0dWRlbnQAB1N0dWRlbnQABf7+/gL+CP4h/jz+
Bv48HlJ2da0=
qr2kWR6JEwMAPgAAAKUBAAAAAOEAAAAAAAEAAgAF/+AJMjAwMjE1MTM3Bueoi+eoiwPlpbMeAAJJ
U/PtqV0=
‘/*!*/;
# at 421
#170829  9:04:42 server id 201609  end_log_pos 452 CRC32 0xfcd87186 Xid = 168
COMMIT/*!*/;
# at 452
#170829  9:04:42 server id 201609  end_log_pos 517 CRC32 0xe47a26ba Anonymous_GTIDlast_committed=1sequence_number=2
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS‘/*!*/;
# at 517
#170829  9:04:42 server id 201609  end_log_pos 592 CRC32 0x0d3e44d1 Querythread_id=7exec_time=0error_code=0
SET TIMESTAMP=1503968682/*!*/;
BEGIN
/*!*/;
# at 592
#170829  9:04:42 server id 201609  end_log_pos 657 CRC32 0x98d94728 Table_map: `student`.`Student` mapped to number 225
# at 657
#170829  9:04:42 server id 201609  end_log_pos 719 CRC32 0x32c7750e Write_rows: table id 225 flags: STMT_END_F
BINLOG ‘
qr2kWROJEwMAQQAAAJECAAAAAOEAAAAAAAEAB3N0dWRlbnQAB1N0dWRlbnQABf7+/gL+CP4h/jz+
Bv48HihH2Zg=
qr2kWR6JEwMAPgAAAM8CAAAAAOEAAAAAAAEAAgAF/+AJMjAwMjE1MTM4BueQqueQqgPnlLcdAAJN
QQ51xzI=
‘/*!*/;
# at 719
#170829  9:04:42 server id 201609  end_log_pos 750 CRC32 0x92ebbf95 Xid = 169
COMMIT/*!*/;
# at 750
#170829  9:04:42 server id 201609  end_log_pos 815 CRC32 0xfe05a22e Anonymous_GTIDlast_committed=2sequence_number=3
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS‘/*!*/;
# at 815
#170829  9:04:42 server id 201609  end_log_pos 890 CRC32 0x551c26a4 Querythread_id=7exec_time=0error_code=0
SET TIMESTAMP=1503968682/*!*/;
BEGIN
/*!*/;
# at 890
#170829  9:04:42 server id 201609  end_log_pos 955 CRC32 0x67c477d0 Table_map: `student`.`Student` mapped to number 225
# at 955
#170829  9:04:42 server id 201609  end_log_pos 1017 CRC32 0x5d3f2503 Write_rows: table id 225 flags: STMT_END_F
BINLOG ‘
qr2kWROJEwMAQQAAALsDAAAAAOEAAAAAAAEAB3N0dWRlbnQAB1N0dWRlbnQABf7+/gL+CP4h/jz+
Bv48HtB3xGc=
qr2kWR6JEwMAPgAAAPkDAAAAAOEAAAAAAAEAAgAF/+AJMjAwMjE1MTM5Bum+mem+mQPnlLcbAAJJ
UwMlP10=
‘/*!*/;
# at 1017
#170829  9:04:42 server id 201609  end_log_pos 1048 CRC32 0x2d5f70ba Xid = 170
COMMIT/*!*/;
# at 1048
#170829  9:06:30 server id 201609  end_log_pos 1113 CRC32 0xb8cdf9b6 Anonymous_GTIDlast_committed=3sequence_number=4
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS‘/*!*/;
# at 1113
#170829  9:06:30 server id 201609  end_log_pos 1214 CRC32 0x69d17a84 Querythread_id=7exec_time=0error_code=0
SET TIMESTAMP=1503968790/*!*/;
drop database student
/*!*/;
SET @@SESSION.GTID_NEXT= ‘AUTOMATIC‘ /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
/*!50003 SET [email protected]_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

调整好后开始恢复增量数据

[root@ocbsdb01 tmp]# mysql -u root -p  < 04.sql

Enter password:

再次查看数据库,发现全备份到删除数据库之间的那三条数据也恢复了!!

mysql> select * from Student;
+-----------+-----------+------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept |
+-----------+-----------+------+------+-------+
| 200215121 | 李勇      | 男   |   20 | CS    |
| 200215122 | 刘晨      | 女   |   19 | CS    |
| 200215123 | 王敏      | 女   |   18 | MA    |
| 200215125 | 张立      | 女   |   19 | IS    |
| 200215126 | 虎威      | 男   |   25 | CS    |
| 200215127 | 魏大师    | 男   |   35 | IS    |
| 200215128 | 老谢      | 男   |   33 | MA    |
| 200215129 | 小贾      | 男   |   30 | CS    |
| 200215130 | 会民      | 男   |   23 | CS    |
| 200215131 | 陈兴      | 男   |   33 | MA    |
| 200215132 | 阿帆      | 男   |   36 | IS    |
| 200215133 | 国良      | 男   |   40 | IS    |
| 200215134 | 老宋      | 男   |   40 | IS    |
| 200215135 | 光光      | 男   |   35 | IS    |
| 200215136 | 王老板    | 女   |   27 | IS    |
| 200215137 | 程程      | 女   |   30 | IS    |
| 200215138 | 琪琪      | 男   |   29 | MA    |
| 200215139 | 龙龙      | 男   |   27 | IS    |
+-----------+-----------+------+------+-------+
18 rows in set (0.00 sec)

以上就是MySQL 数据库增量数据恢复的实例过程!

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Jerry的SAP技术分享

在ABAP里取得一个数据库表记录数的两种方法

这个函数使用起来很简单,只需要将想查询的数据库表名称维护进输入参数IT_TABLES:

1565
来自专栏数据库

oracle常用命令大全

一、Oracle数据库实例、用户、目录及session会话查看: 1、ORACLE SID查看设置 查看SID、用户名 $ env|grep SID 、sele...

4187
来自专栏漫漫全栈路

Oracle 学习笔记

前言 本贴内容纪录Oracle课程中的学习笔记,和Oracle的课后作业,以及数据库相关课程的学习笔记,笔记部分使用实例代码记录,不记详细语法。 用户管理...

3405
来自专栏杨建荣的学习笔记

MySQL中GTID和自增列的数据测试(r12笔记第38天)

昨天的一篇文章MySQL自增列主从不一致的测试(r12笔记第37天),今天有不少网友向我确认一些细节,我想最近正好在看GTID的东西,可以揉在一起来说说。 ...

38211
来自专栏idba

死锁案例之一

一 前言 死锁,其实是一个很有意思也很有挑战的技术问题,大概每个DBA和部分开发同学都会在工作过程中遇见 。关于死锁我会持续写一个系列的案例分析,希望能够对想...

733
来自专栏ytkah

dedecms清空所有文章怎么操作?sql语句如何写?

  小C新建了一个站,确切的说是复制,出于seo考虑,决定清空所有文章,那么dedecms清空所有文章怎么操作?sql语句如何写呢?特别提醒:修改之前一定要先做...

2959
来自专栏杨建荣的学习笔记

手工创建/删除数据库的步骤

今天和大家分享下数据库的创建和删除的步骤,里面有很多细节需要大家考虑。创建数据库不只是一个create database语句。删除数据库 drop databa...

3186
来自专栏杨建荣的学习笔记

物化视图刷新的问题及分析(61天)

最近现场需要搭建一套全新的环境,对于数据字典的管理采用了物化视图,因为数据量不大,采用了全量刷新的方式。因为有好几套环境,有几套环境是通过db link和主节点...

3667
来自专栏IT大咖说

2018年,让你的数据库变更快的十个建议

摘要 大多数网站的内容都存在数据库里,用户通过请求来访问内容。数据库非常的快,有许多技巧能让你优化数据库的速度,使你不浪费服务器的资源。在这篇文章中,我收录了十...

4167
来自专栏杨建荣的学习笔记

MySQL Online DDL(二)(r11笔记第88天)

对于Online DDL,之前简单分析了一些场景MySQL中的Online DDL(第一篇)(r11笔记第3天),其实有一个很关键的点没提到,那就是online...

3619

扫码关注云+社区