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

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

具体思路归纳几点：

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

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

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

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

具体实例演示：

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

<code>mysql> show variables like </code><code>'%log_bin%'</code><code>;</code>

<code>| Variable_name | Value |</code>

<code>| log_bin_basename | </code><code>/mysql_data/mysql-bin</code> <code>|</code>

<code>| log_bin_index | </code><code>/mysql_data/mysql-bin</code><code>.index |</code>

<code>| log_bin_trust_function_creators | OFF |</code>

<code>| log_bin_use_v1_row_events | OFF |</code>

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

<code>mysql> </code><code>select</code> <code>* from Student;</code>

3、现在进行全备份

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

<code>-B：指定数据库</code>

<code>-R：备份存储过程等</code>

4、再次插入新数据

<code>INSERT INTO Student VALUES (</code><code>'200215137'</code><code>,</code><code>'程程'</code><code>,</code><code>'女'</code><code>,30,</code><code>'IS'</code><code>);</code>

<code>INSERT INTO Student VALUES (</code><code>'200215138'</code><code>,</code><code>'琪琪'</code><code>,</code><code>'男'</code><code>,29,</code><code>'MA'</code><code>);</code>

<code>INSERT INTO Student VALUES (</code><code>'200215139'</code><code>,</code><code>'龙龙'</code><code>,</code><code>'男'</code><code>,27,</code><code>'IS'</code><code>);</code>

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

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

<code>mysql> drop database student;</code>

<code>Query OK, 3 rows affected (0.11 sec)</code>

<code>mysql> show databases;</code>

<code>| Database |</code>

<code>| information_schema |</code>

<code>| mysql |</code>

<code>| performance_schema |</code>

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

<code>[root@ocbsdb01 mysql_backup]</code><code># cd /mysql_backup</code>

<code>[root@ocbsdb01 mysql_backup]</code><code># gzip -d student_20170829_090319.sql.gz </code>

<code>[root@ocbsdb01 mysql_backup]</code><code># ls</code>

<code>student_20170829_090319.sql</code>

<code>[root@ocbsdb01 mysql_backup]</code><code># vim student_20170829_090319.sql</code>

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的时候可能会报错，具体信息如下：

[root@ocbsdb01 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、开始恢复全备数据

<code>[root@ocbsdb01 tmp]</code><code># mysql -u root -p < /mysql_backup/student_20170829_090319.sql</code>

<code>Enter password:</code>

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

<code>| student |</code>

11、开始恢复增量数据

使用04.sql文件恢复全备时刻到删除数据库之间新增的数据，编辑04bin.sql #删除里面的drop语句

[root@ocbsdb01 tmp]# vim 04.sql

<code>将drop 操作下面的内容删除</code>

<code>drop database student</code>

<code>SET @@SESSION.GTID_NEXT= </code><code>'AUTOMATIC'</code> <code>/* added by mysqlbinlog */ /*!*/;</code>

<code>DELIMITER ;</code>

<code>/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;</code>

<code>/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;</code>

不然会报如下错误：

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文件具体内容如下：

<code>/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;</code>

<code>/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;</code>

<code>DELIMITER /*!*/;</code>

<code>#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</code>

<code># Warning: this binlog is either in use or was not closed properly.</code>

<code>BINLOG '</code>

<code>Wr2kWQ+JEwMAdwAAAHsAAAABAAQANS43LjE4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</code>

<code>AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA</code>

<code>#170829 9:03:22 server id 201609 end_log_pos 154 CRC32 0xf909764e Previous-GTIDs</code>

<code># [empty]</code>

<code>#170829 9:04:42 server id 201609 end_log_pos 219 CRC32 0x754f68f2 Anonymous_GTIDlast_committed=0sequence_number=1</code>

<code>SET @@SESSION.GTID_NEXT= </code><code>'ANONYMOUS'</code><code>/*!*/;</code>

<code>#170829 9:04:42 server id 201609 end_log_pos 294 CRC32 0xdef39415 Querythread_id=7exec_time=0error_code=0</code>

<code>SET TIMESTAMP=1503968682/*!*/;</code>

<code>SET @@session.pseudo_thread_id=7/*!*/;</code>

<code>SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;</code>

<code>SET @@session.sql_mode=1344274432/*!*/;</code>

<code>SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;</code>

<code>SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;</code>

<code>SET @@session.lc_time_names=0/*!*/;</code>

<code>SET @@session.collation_database=DEFAULT/*!*/;</code>

<code>BEGIN</code>

<code>#170829 9:04:42 server id 201609 end_log_pos 359 CRC32 0xad757652 Table_map: `student`.`Student` mapped to number 225</code>

<code>#170829 9:04:42 server id 201609 end_log_pos 421 CRC32 0x5da9edf3 Write_rows: table id 225 flags: STMT_END_F</code>

<code>qr2kWROJEwMAQQAAAGcBAAAAAOEAAAAAAAEAB3N0dWRlbnQAB1N0dWRlbnQABf7+</code><code>/gL</code><code>+CP4h</code><code>/jz</code><code>+</code>

<code>qr2kWR6JEwMAPgAAAKUBAAAAAOEAAAAAAAEAAgAF/+AJMjAwMjE1MTM3Bueoi+eoiwPlpbMeAAJJ</code>

<code>#170829 9:04:42 server id 201609 end_log_pos 452 CRC32 0xfcd87186 Xid = 168</code>

<code>COMMIT/*!*/;</code>

<code>#170829 9:04:42 server id 201609 end_log_pos 517 CRC32 0xe47a26ba Anonymous_GTIDlast_committed=1sequence_number=2</code>

<code>#170829 9:04:42 server id 201609 end_log_pos 592 CRC32 0x0d3e44d1 Querythread_id=7exec_time=0error_code=0</code>

<code>#170829 9:04:42 server id 201609 end_log_pos 657 CRC32 0x98d94728 Table_map: `student`.`Student` mapped to number 225</code>

<code>#170829 9:04:42 server id 201609 end_log_pos 719 CRC32 0x32c7750e Write_rows: table id 225 flags: STMT_END_F</code>

<code>qr2kWROJEwMAQQAAAJECAAAAAOEAAAAAAAEAB3N0dWRlbnQAB1N0dWRlbnQABf7+</code><code>/gL</code><code>+CP4h</code><code>/jz</code><code>+</code>

<code>qr2kWR6JEwMAPgAAAM8CAAAAAOEAAAAAAAEAAgAF/+AJMjAwMjE1MTM4BueQqueQqgPnlLcdAAJN</code>

<code>#170829 9:04:42 server id 201609 end_log_pos 750 CRC32 0x92ebbf95 Xid = 169</code>

<code>#170829 9:04:42 server id 201609 end_log_pos 815 CRC32 0xfe05a22e Anonymous_GTIDlast_committed=2sequence_number=3</code>

<code>#170829 9:04:42 server id 201609 end_log_pos 890 CRC32 0x551c26a4 Querythread_id=7exec_time=0error_code=0</code>

<code>#170829 9:04:42 server id 201609 end_log_pos 955 CRC32 0x67c477d0 Table_map: `student`.`Student` mapped to number 225</code>

<code>#170829 9:04:42 server id 201609 end_log_pos 1017 CRC32 0x5d3f2503 Write_rows: table id 225 flags: STMT_END_F</code>

<code>qr2kWROJEwMAQQAAALsDAAAAAOEAAAAAAAEAB3N0dWRlbnQAB1N0dWRlbnQABf7+</code><code>/gL</code><code>+CP4h</code><code>/jz</code><code>+</code>

<code>qr2kWR6JEwMAPgAAAPkDAAAAAOEAAAAAAAEAAgAF/+AJMjAwMjE1MTM5Bum+mem+mQPnlLcbAAJJ</code>

<code>UwMlP10=</code>

<code>#170829 9:04:42 server id 201609 end_log_pos 1048 CRC32 0x2d5f70ba Xid = 170</code>

<code>#170829 9:06:30 server id 201609 end_log_pos 1113 CRC32 0xb8cdf9b6 Anonymous_GTIDlast_committed=3sequence_number=4</code>

<code>#170829 9:06:30 server id 201609 end_log_pos 1214 CRC32 0x69d17a84 Querythread_id=7exec_time=0error_code=0</code>

<code>SET TIMESTAMP=1503968790/*!*/;</code>

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

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

Enter password:

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

以上就是MySQL 数据库增量数据恢复的实例过程!如有不足还请指正

本文转自 yuri_cto 51CTO博客，原文链接：http://blog.51cto.com/laobaiv1/1960846，如需转载请自行联系原作者

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

继续阅读

Ubuntu16.04安装Apache+MySQL+PHP1. 安装Apache2. 安装MySQL3. 安装PHP4. 安装phpMyAdmin

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

mysql使用source命令导入.sql文件

登录plsql 报错 the account is locked --用户被锁

sqlServer根据经纬查距离

SequoiaDB巨杉数据库C++驱动概述