[转帖]mysqldump全备份及结合binlog日志恢复的全过程_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方技术论坛-国内领先的Weblogic、Tuxedo中间件技术论坛
联动北方-国内领先的云技术服务提供商
»  游客             当前位置：  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 1822 | 回复: 0   主题： [转帖]mysqldump全备份及结合binlog日志恢复的全过程        下一篇 
yypypyy
注册用户
等级：新兵
经验：76
发帖：1
精华：0
注册：2016-9-26
状态：离线
发送短消息息给yypypyy 加好友    发送短消息息给yypypyy 发消息
发表于： IP：您无权察看 2018-8-28 15:48:19 | [全部帖] [楼主帖] 楼主

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
5 rows in set (0.00 sec)
mysql> use testdb;
Database changed
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| student_t |
+------------------+
1 row in set (0.00 sec)
mysql> select * from testdb.student_t;
+--------+----------+------------+
| id | name | time |
+--------+----------+------------+
| 12 | anzhen | 2017-07-25 |
| 1 | dsanzhen | 2017-07-25 |
| 18 | sfsdf | 2017-07-25 |
| 18 | s899 | 2017-07-25 |
| 134 | s899 | 2017-07-25 |
| 111 | 111 | 2017-07-25 |
| 444223 | 111 | 2017-07-25 |
| 3333 | 333 | 2017-07-25 |
+--------+----------+------------+
8 rows in set (0.00 sec)
#全备份时间为2017 Jul 25 20:46
[root@localhost104 mysql]#/usr/local/mysql/bin/mysqldump -uroot --dump-date --port=3306 --lock-all-tables --events --routines --triggers --master-data=2 --flush-logs --all-databases -p >/tmp/full.sql
[root@localhost104 mysql]# ls -all /tmp/mysqlfull_20170725.sql
-rw-r--r-- 1 root root 778018 Jul 25 20:46 /tmp/mysqlfull_20170725.sql
#全备份之后，新增的数据并且刷新了日志文件
mysql> create table testdb.add_data1(id int);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into testdb.add_data1 values(1),(2),(3);
Query OK, 3 rows affected (0.09 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from testdb.add_data1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql_bin.000402 | 1124 |
| mysql_bin.000403 | 1132 |
| mysql_bin.000404 | 1628 |
| mysql_bin.000405 | 486 |
| mysql_bin.000406 | 769 |
| mysql_bin.000407 | 1241 |
| mysql_bin.000408 | 201 |
| mysql_bin.000409 | 655 |
| mysql_bin.000410 | 154 |
+------------------+-----------+
41 rows in set (0.00 sec)


继续插入数据：

mysql> insert into testdb.add_data1 values(8585),(22463),(242);
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql_bin.000402 | 1124 |
| mysql_bin.000403 | 1132 |
| mysql_bin.000404 | 1628 |
| mysql_bin.000405 | 486 |
| mysql_bin.000406 | 769 |
| mysql_bin.000407 | 1241 |
| mysql_bin.000408 | 201 |
| mysql_bin.000409 | 655 |
| mysql_bin.000410 | 428 |
+------------------+-----------+
41 rows in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.04 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql_bin.000402 | 1124 |
| mysql_bin.000403 | 1132 |
| mysql_bin.000404 | 1628 |
| mysql_bin.000405 | 486 |
| mysql_bin.000406 | 769 |
| mysql_bin.000407 | 1241 |
| mysql_bin.000408 | 201 |
| mysql_bin.000409 | 655 |
| mysql_bin.000410 | 475 |
| mysql_bin.000411 | 154 |
+------------------+-----------+
42 rows in set (0.00 sec)


日志文件增加一个。

误操作：update忘记带where条件了。误操作的时间大概是:2017-07-25 21:02:00

mysql> select * from testdb.student_t;
+--------+----------+------------+
| id | name | time |
+--------+----------+------------+
| 12 | anzhen | 2017-07-25 |
| 1 | dsanzhen | 2017-07-25 |
| 18 | sfsdf | 2017-07-25 |
| 18 | s899 | 2017-07-25 |
| 134 | s899 | 2017-07-25 |
| 111 | 111 | 2017-07-25 |
| 444223 | 111 | 2017-07-25 |
| 3333 | 333 | 2017-07-25 |
+--------+----------+------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql> update testdb.student_t set name='m1';
Query OK, 8 rows affected (0.01 sec)
Rows matched: 8 Changed: 8 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from testdb.student_t;
+--------+------+------------+
| id | name | time |
+--------+------+------------+
| 12 | m1 | 2017-07-25 |
| 1 | m1 | 2017-07-25 |
| 18 | m1 | 2017-07-25 |
| 18 | m1 | 2017-07-25 |
| 134 | m1 | 2017-07-25 |
| 111 | m1 | 2017-07-25 |
| 444223 | m1 | 2017-07-25 |
| 3333 | m1 | 2017-07-25 |
+--------+------+------------+
8 rows in set (0.00 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2017-07-25 21:02:00 |
+---------------------+
1 row in set (0.00 sec)


其他同事新增正常的数据。

mysql> create table testdb.add_data2(id int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into testdb.add_data2 values(234),(345),(1231),(235),(241);
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from testdb.add_data2;
+------+
| id |
+------+
| 234 |
| 345 |
| 1231 |
| 235 |
| 241 |
+------+
5 rows in set (0.00 sec)


【原正向恢复方式】

选择数据出问题时间之前最近的备份集恢复在一个新实例（restorebak.pl恢复工具支持，操作方便）

将备份集POS与数据出问题POS之间的二进制日志拷贝到新实例上（拷贝二进制日志操作繁琐）

恢复的过程：采用全备份+binlog的增量日志文件恢复到最新的的数据。

（注意：如果是生产环境不能直接就覆盖生产环境的数据。）

（1）利用最近全备份恢复数据库,最近的备份集恢复在一个新实例。新建一个MySQL实列。（注意：如果是生产环境不能直接就覆盖生产环境的数据。）

查看全备份的生成时间：也就是说全备份可以恢复大概到2017 Jul 25 20:46的数据

[root@localhost104 mysql]# ls -all /tmp/mysqlfull_20170725.sql
-rw-r--r-- 1 root root 778018 Jul 25 20:46 /tmp/mysqlfull_20170725.sql
#恢复最近的全备份（一定要最近的，如果用很旧的全备份，这样binlog的日志可以不全，达不到恢复最新的恢复）
[root@localhost104 mysql]# /usr/local/mysql/bin/mysql -uroot -p < /tmp/mysqlfull_20170725.sql
Enter password:


（2）查看全备份文件的恢复情况，是否把以前2017 Jul 25 20:46的数据的数据恢复好了。

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
5 rows in set (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| student_t |
+------------------+
1 row in set (0.00 sec)


只有恢复全备份的数据

mysql> select * from testdb.student_t;
+--------+----------+------------+
| id | name | time |
+--------+----------+------------+
| 12 | anzhen | 2017-07-25 |
| 1 | dsanzhen | 2017-07-25 |
| 18 | sfsdf | 2017-07-25 |
| 18 | s899 | 2017-07-25 |
| 134 | s899 | 2017-07-25 |
| 111 | 111 | 2017-07-25 |
| 444223 | 111 | 2017-07-25 |
| 3333 | 333 | 2017-07-25 |
+--------+----------+------------+
8 rows in set (0.01 sec)


（3）现在要恢复全备份之后到误操作之前的数据。怎么样确定开始恢复的位置和停止的正确位置呢？

这个是恢复关键的所在。

[root@localhost104 mysql]# vi /tmp/mysqlfull_20170725.sql


全备份文件前面几行有以下的信息。因为全备份的时候用了这个参数--master-data，告诉我们从这个二进制文件开始恢复全备份之后的

的数据，位置为154

- CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin.000409', MASTER_LOG_POS=154;


因此开始位置为154。

怎么样确定误操作的命令的开始和结束位置号呢。依据大概误操作的时间2017-07-25 21:02:00，可以大概判断二进制文件。

在二进制目录参看：

-rw-r----- 1 mysql mysql 769 Jul 25 20:27 mysql_bin.000406
-rw-r----- 1 mysql mysql 1241 Jul 25 20:45 mysql_bin.000407
-rw-r----- 1 mysql mysql 201 Jul 25 20:46 mysql_bin.000408
-rw-r----- 1 mysql mysql 655 Jul 25 20:52 mysql_bin.000409
-rw-r----- 1 mysql mysql 475 Jul 25 20:53 mysql_bin.000410
-rw-r----- 1 mysql mysql 2288112 Jul 25 21:53 mysql_bin.000411
-rw-r----- 1 mysql mysql 154 Jul 25 21:53 mysql_bin.000412
-rw-r----- 1 mysql mysql 1677 Jul 25 21:53 mysql_bin.index


大概判断误操作是在mysql_bin.000410或者mysql_bin.000410这个二进制文件里面。只看错误大概前后5分钟左右的操作，看有没有误操作的记录。找到为止

[root@localhost104 mysql]# bin/mysqlbinlog -uroot --start-datetime='2017-07-25 20:58:00' --stop-datetime='2017-07-25 21:07:00' /usr/local/mysql/data/mysql_bin.000411 -p > /tmp/411binlog.sql
Enter password:
[root@localhost104 mysql]#
[root@localhost104 mysql]#
[root@localhost104 mysql]# vi /tmp/411binlog.sql
# at 293
#170725 21:01:25 server id 10 end_log_pos 351 CRC32 0x73dd9604 Table_map: `testdb`.`student_t` mapped to number 340
# at 351
#170725 21:01:25 server id 10 end_log_pos 583 CRC32 0x30b8f6c2 Update_rows: table id 340 flags: STMT_END_F
BINLOG '
JUF3WRMKAAAAOgAAAF8BAAAAAFQBAAAAAAEABnRlc3RkYgAJc3R1ZGVudF90AAMDDwoCCgAHBJbd
cw==
JUF3WR8KAAAA6AAAAEcCAAAAAFQBAAAAAAEAAgAD///4DAAAAAZhbnpoZW75wg/4DAAAAAJtMfnC
D/gBAAAACGRzYW56aGVu+cIP+AEAAAACbTH5wg/4EgAAAAVzZnNkZvnCD/gSAAAAAm0x+cIP+BIA
AAAEczg5OfnCD/gSAAAAAm0x+cIP+IYAAAAEczg5OfnCD/iGAAAAAm0x+cIP+G8AAAADMTEx+cIP
+G8AAAACbTH5wg/4P8cGAAMxMTH5wg/4P8cGAAJtMfnCD/gFDQAAAzMzM/nCD/gFDQAAAm0x+cIP
wva4MA==
'/*!*/;
# at 583
#170725 21:01:25 server id 10 end_log_pos 614 CRC32 0x4002b392 Xid = 1789
COMMIT/*!*/;
# at 614


可以错误命令执行之前的位置为# at 293，误操作执行完成之后的位置为583.因此要跳过位置293-583的范围。

#start-position为全备份文件里面的位置和mysql_bin.000409也是全备份里面的二进制文件开始恢复，mysql_bin.000411为包含有误操作的文件，--stop-position为误操作命

令之前的的位置。

[root@localhost104 mysql]# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql_bin.000409 /usr/local/mysql/data/mysql_bin.000410 /usr/local/mysql/data/mysql_bin.000411 --start-position=154 --stop-position=293 | /usr/local/mysql/bin/mysql -uroot -p
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| add_data1 |
| student_t |
+------------------+
2 rows in set (0.00 sec)
#表testdb.add_data1为全备份到误操作之间的数据，已经恢复。该误操作的mysql> update testdb.student_t set name='m1';数据也恢


复了。如下表testdb.student_t 的数据name不全部m1的错误数据了。

mysql> select * from testdb.add_data1;
+-------+
| id |
+-------+
| 1 |
| 2 |
| 3 |
| 8585 |
| 22463 |
| 242 |
+-------+
6 rows in set (0.00 sec)
mysql> select * from student_t;
+--------+----------+------------+
| id | name | time |
+--------+----------+------------+
| 12 | anzhen | 2017-07-25 |
| 1 | dsanzhen | 2017-07-25 |
| 18 | sfsdf | 2017-07-25 |
| 18 | s899 | 2017-07-25 |
| 134 | s899 | 2017-07-25 |
| 111 | 111 | 2017-07-25 |
| 444223 | 111 | 2017-07-25 |
| 3333 | 333 | 2017-07-25 |
+--------+----------+------------+
8 rows in set (0.00 sec)


（4）需要跳过误操作的错误数据，恢复误操作之后的正确数据。只需要mysql_bin.000411和以后的二进制文件。

查看当前的二进制文件

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000412 | 2289326 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


刷新二进制文件，把最新的数据刷新到mysql_bin.000412文件里面，然后我们一起把mysql_bin.000412最新的数据恢复出来。

mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000413 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[root@localhost104 mysql]# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql_bin.000411 /usr/local/mysql/data/mysql_bin.000412 --start-position=583 --stop-position=1071 | /usr/local/mysql/bin/mysql -uroot -p
Enter password:
mysql> use testdb;
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


误操作之后新增的正确数据add_data2也恢复完成

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| add_data1 |
| add_data2 |
| student_t |
+------------------+
3 rows in set (0.01 sec)
mysql> select * from testdb.add_data1;
+-------+
| id |
+-------+
| 1 |
| 2 |
| 3 |
| 8585 |
| 22463 |
| 242 |
+-------+
6 rows in set (0.01 sec)
mysql> select * from testdb.add_data2;
+------+
| id |
+------+
| 234 |
| 345 |
| 1231 |
| 235 |
| 241 |
+------+
5 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> select * from testdb.student_t;
+--------+----------+------------+
| id | name | time |
+--------+----------+------------+
| 12 | anzhen | 2017-07-25 |
| 1 | dsanzhen | 2017-07-25 |
| 18 | sfsdf | 2017-07-25 |
| 18 | s899 | 2017-07-25 |
| 134 | s899 | 2017-07-25 |
| 111 | 111 | 2017-07-25 |
| 444223 | 111 | 2017-07-25 |
| 3333 | 333 | 2017-07-25 |
+--------+----------+------------+
8 rows in set (0.00 sec)


到现在为止数据已经恢复到最后一次flush logs时候的最新的状态了。


赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码： 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论

Weblogic中间件技术论坛

 |

Tuxedo中间件技术论坛

 |

数据库论坛

 |

Java论坛

 |

Linux/unix论坛

 |

网站地图


Powered by LandingBJ     @ 2008-2018     联动北方   
京ICP证号090597    京ICP备案09017624号    京公网安备110112000093号    
400-810-2327     Email：service@landingbj.com   

Powered by landingbj @ 2008-2015 联动北方
京ICP证号090597 京ICP备案09017624号 京公网安备110112000093号
86-27-87056338 Email：service@landingbj.com