签到成功

知道了

CNDBA社区CNDBA社区

MariaDB 基于binlog 的数据恢复

2018-02-10 02:04 4007 0 原创 MySQL
作者： dave
本文链接：http://www.cndba.cn/dave/article/2654

之前我们看了MariaDB的逻辑备份，物理备份操作，他们都需要利用二进制日志来推进数据。 这篇我们专门看一下二进制日志的恢复。 http://www.cndba.cn/dave/article/2654http://www.cndba.cn/dave/article/2654http://www.cndba.cn/dave/article/2654

MariaDB 的逻辑备份与恢复
http://www.cndba.cn/dave/article/2650

http://www.cndba.cn/dave/article/2654

MariaDB 使用 xtrabackup 工具进行备份与恢复
http://www.cndba.cn/dave/article/2652http://www.cndba.cn/dave/article/2654

1 基于时间点恢复

1）测试数据
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status
    -> ;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      385 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> create table dave(id int,name char(100));
Query OK, 0 rows affected (0.01 sec)

mysql>  insert into dave values(1,'www.cndba.cn/dave');
Query OK, 1 row affected (0.01 sec)

mysql> select * from dave;
+------+-------------------+
| id   | name              |
+------+-------------------+
|    1 | www.cndba.cn/dave |
+------+-------------------+
1 row in set (0.00 sec)

mysql> drop table dave;
Query OK, 0 rows affected (0.00 sec)
2）通过binlog 日志查看操作记录
[root@www.cndba.cn/dave ~]# mysqlbinlog --no-defaults /mysql/data/mysql-bin.000002
# at 427
#180209  4:12:56 server id 1  end_log_pos 535 CRC32 0xfa19ae04     Query    thread_id=29    exec_time=0    error_code=0
use `cndba`/*!*/;
SET TIMESTAMP=1518120776/*!*/;
SET @@session.pseudo_thread_id=29/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;
SET @@session.sql_mode=1411383296/*!*/;
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/*!*/;
create table dave(id int,name char(100))
/*!*/;
# at 535
#180209  4:13:05 server id 1  end_log_pos 577 CRC32 0xf2c3df2e     GTID 0-1-6 trans
/*!100001 SET @@session.gtid_seq_no=6*//*!*/;
BEGIN
/*!*/;
# at 577
#180209  4:13:05 server id 1  end_log_pos 691 CRC32 0x118f096f     Query    thread_id=29    exec_time=0    error_code=0
SET TIMESTAMP=1518120785/*!*/;
insert into dave values(1,'www.cndba.cn/dave')
/*!*/;
# at 691
#180209  4:13:05 server id 1  end_log_pos 722 CRC32 0x9be4325a     Xid = 315
COMMIT/*!*/;
# at 722
#180209  4:13:11 server id 1  end_log_pos 764 CRC32 0x145a58c0     GTID 0-1-7 ddl
/*!100001 SET @@session.gtid_seq_no=7*//*!*/;
# at 764
#180209  4:13:11 server id 1  end_log_pos 875 CRC32 0xd6032931     Query    thread_id=29    exec_time=0    error_code=0
SET TIMESTAMP=1518120791/*!*/;
DROP TABLE `dave` /* generated by server */
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@www.cndba.cn/dave ~]#

--利用binlog 基于时间，进行恢复，这里关键是根据日志查找操作的时间点：
[root@www.cndba.cn/dave ~]# mysqlbinlog --no-defaults --stop-datetime='18-02-09  4:12:56'   --stop-datetime='18-02-09  4:13:11' /mysql/data/mysql-bin.000002|mysql -u root -p
Enter password: 
[root@www.cndba.cn/dave ~]#
3）数据被还原回来
mysql> select * from dave;
+------+-------------------+
| id   | name              |
+------+-------------------+
|    1 | www.cndba.cn/dave |
+------+-------------------+
1 row in set (0.00 sec)

2 基于位置恢复

1）创建表并插入数据
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> create table dave2(id int,name char(100));
Query OK, 0 rows affected (0.03 sec)

mysql>  insert into dave2 values(1,'www.cndba.cn/dave');
Query OK, 1 row affected (0.00 sec)

mysql> drop table dave2;
Query OK, 0 rows affected (0.00 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1018 |
| mysql-bin.000002 |      1259 |
| mysql-bin.000003 |       878 |
+------------------+-----------+
3 rows in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      878 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
2）通过binlog 日志查看操作记录

可以使用mysqlbinlog 命令查看，也可以直接在mysql里show binlog。http://www.cndba.cn/dave/article/2654

mysql> show binlog events in 'mysql-bin.000003';
+------------------+-----+-------------------+-----------+-------------+--------------------------------------------------------------+
| Log_name         | Pos | Event_type        | Server_id | End_log_pos | Info                                                         |
+------------------+-----+-------------------+-----------+-------------+--------------------------------------------------------------+
| mysql-bin.000003 |   4 | Format_desc       |         1 |         256 | Server ver: 10.2.12-MariaDB-log, Binlog ver: 4               |
| mysql-bin.000003 | 256 | Gtid_list         |         1 |         299 | [0-1-6]                                                      |
| mysql-bin.000003 | 299 | Binlog_checkpoint |         1 |         342 | mysql-bin.000002                                             |
| mysql-bin.000003 | 342 | Binlog_checkpoint |         1 |         385 | mysql-bin.000003                                             |
| mysql-bin.000003 | 385 | Gtid              |         1 |         427 | GTID 0-1-8                                                   |
| mysql-bin.000003 | 427 | Query             |         1 |         536 | use `cndba`; create table dave2(id int,name char(100))       |
| mysql-bin.000003 | 536 | Gtid              |         1 |         578 | BEGIN GTID 0-1-9                                             |
| mysql-bin.000003 | 578 | Query             |         1 |         693 | use `cndba`; insert into dave2 values(1,'www.cndba.cn/dave') |
| mysql-bin.000003 | 693 | Xid               |         1 |         724 | COMMIT /* xid=376 */                                         |
| mysql-bin.000003 | 724 | Gtid              |         1 |         766 | GTID 0-1-10                                                  |
| mysql-bin.000003 | 766 | Query             |         1 |         878 | use `cndba`; DROP TABLE `dave2` /* generated by server */    |
+------------------+-----+-------------------+-----------+-------------+--------------------------------------------------------------+
11 rows in set (0.00 sec)

mysql>
3）基于位置的恢复

注意这里的位置，写的数字是小于，不等于。

http://www.cndba.cn/dave/article/2654 

[root@www.cndba.cn/dave ~]# mysqlbinlog --no-defaults --start-position 427 --stop-position 724 /mysql/data/mysql-bin.000003|mysql -u root -p
Enter password: 
[root@www.cndba.cn/dave ~]#
4）数据被还原回来
mysql> select * from dave2;
+------+-------------------+
| id   | name              |
+------+-------------------+
|    1 | www.cndba.cn/dave |
+------+-------------------+
1 row in set (0.00 sec)

3 不完全恢复

不完全恢复是利用物理备份或者逻辑备份的基础上，在结合binlog的一种恢复操作。

3.1 物理全备数据库
[root@www.cndba.cn/dave ~]# innobackupex --user=root --password='' /tmp/

通过xtrabackup_binlog_info文件查看备份结束时刻binlog 的名称和位置

[root@www.cndba.cn/dave 2018-02-09_04-36-56]# pwd
/tmp/2018-02-09_04-36-56
[root@www.cndba.cn/dave 2018-02-09_04-36-56]# cat xtrabackup_info
binlog_pos = filename 'mysql-bin.000003', position '1522', GTID of the last change '0-1-9'
3.2 创建测试数据
mysql> create table dave3(id int,name char(100));
Query OK, 0 rows affected (0.03 sec)

mysql>  insert into dave3 values(1,'www.cndba.cn/dave');
Query OK, 1 row affected (0.00 sec)

mysql> drop table dave3;
Query OK, 0 rows affected (0.00 sec)
3.3 查看当前数据库的binlog 文件和位置
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1018 |
| mysql-bin.000002 |      1259 |
| mysql-bin.000003 |      2015 |
+------------------+-----------+
3 rows in set (0.00 sec)

mysql> show master status ;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |     2015 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
3.4 从全备中恢复数据库
[root@www.cndba.cn/dave 2018-02-09_04-36-56]# innobackupex --apply-log /tmp/2018-02-09_04-36-56
[root@www.cndba.cn/dave 2018-02-09_04-36-56]# innobackupex --copy-back /tmp/2018-02-09_04-36-56
[root@www.cndba.cn/dave data]# chown mysql:mysql /mysql/ -R
3.5 利用binlog 追加数据
mysql> show binlog events in 'mysql-bin.000003';
+------------------+------+-------------------+-----------+-------------+--------------------------------------------------------------+
| Log_name         | Pos  | Event_type        | Server_id | End_log_pos | Info                                                         |
+------------------+------+-------------------+-----------+-------------+--------------------------------------------------------------+
| mysql-bin.000003 |    4 | Format_desc       |         1 |         256 | Server ver: 10.2.12-MariaDB-log, Binlog ver: 4               |
| mysql-bin.000003 |  256 | Gtid_list         |         1 |         299 | [0-1-6]                                                      |
| mysql-bin.000003 |  299 | Binlog_checkpoint |         1 |         342 | mysql-bin.000002                                             |
| mysql-bin.000003 |  342 | Binlog_checkpoint |         1 |         385 | mysql-bin.000003                                             |
| mysql-bin.000003 |  385 | Gtid              |         1 |         427 | GTID 0-1-8                                                   |
| mysql-bin.000003 |  427 | Query             |         1 |         536 | use `cndba`; create table dave2(id int,name char(100))       |
| mysql-bin.000003 |  536 | Gtid              |         1 |         578 | BEGIN GTID 0-1-9                                             |
| mysql-bin.000003 |  578 | Query             |         1 |         693 | use `cndba`; insert into dave2 values(1,'www.cndba.cn/dave') |
| mysql-bin.000003 |  693 | Xid               |         1 |         724 | COMMIT /* xid=376 */                                         |
| mysql-bin.000003 |  724 | Gtid              |         1 |         766 | GTID 0-1-10                                                  |
| mysql-bin.000003 |  766 | Query             |         1 |         878 | use `cndba`; DROP TABLE `dave2` /* generated by server */    |
| mysql-bin.000003 |  878 | Gtid              |         1 |         920 | GTID 0-1-11                                                  |
| mysql-bin.000003 |  920 | Query             |         1 |        1029 | use `cndba`; create table dave2(id int,name char(100))       |
| mysql-bin.000003 | 1029 | Gtid              |         1 |        1071 | GTID 0-1-12                                                  |
| mysql-bin.000003 | 1071 | Query             |         1 |        1183 | use `cndba`; DROP TABLE `dave2` /* generated by server */    |
| mysql-bin.000003 | 1183 | Gtid              |         1 |        1225 | GTID 0-1-13                                                  |
| mysql-bin.000003 | 1225 | Query             |         1 |        1334 | use `cndba`; create table dave2(id int,name char(100))       |
| mysql-bin.000003 | 1334 | Gtid              |         1 |        1376 | BEGIN GTID 0-1-9                                             |
| mysql-bin.000003 | 1376 | Query             |         1 |        1491 | use `cndba`; insert into dave2 values(1,'www.cndba.cn/dave') |
| mysql-bin.000003 | 1491 | Xid               |         1 |        1522 | COMMIT /* xid=455 */                                         |
| mysql-bin.000003 | 1522 | Gtid              |         1 |        1564 | GTID 0-1-14                                                  |
| mysql-bin.000003 | 1564 | Query             |         1 |        1673 | use `cndba`; create table dave3(id int,name char(100))       |
| mysql-bin.000003 | 1673 | Gtid              |         1 |        1715 | BEGIN GTID 0-1-15                                            |
| mysql-bin.000003 | 1715 | Query             |         1 |        1830 | use `cndba`; insert into dave3 values(1,'www.cndba.cn/dave') |
| mysql-bin.000003 | 1830 | Xid               |         1 |        1861 | COMMIT /* xid=482 */                                         |
| mysql-bin.000003 | 1861 | Gtid              |         1 |        1903 | GTID 0-1-16                                                  |
| mysql-bin.000003 | 1903 | Query             |         1 |        2015 | use `cndba`; DROP TABLE `dave3` /* generated by server */    |
+------------------+------+-------------------+-----------+-------------+--------------------------------------------------------------+
27 rows in set (0.00 sec)

这里的1522 是从备份的信息里查看到的，1861是从binlog里发现的：http://www.cndba.cn/dave/article/2654

[root@www.cndba.cn/dave ~]# mysqlbinlog --no-defaults --start-position 1522 --stop-position 1861 /mysql/data/mysql-bin.000003|mysql -u root -p

mysql> select * from dave3;
+------+-------------------+
| id   | name              |
+------+-------------------+
|    1 | www.cndba.cn/dave |
+------+-------------------+
1 row in set (0.00 sec)

数据追加成功。 我们成功的利用了备份和binlog 追加到了我们drop dave3 之前的状态。 进行了不完全恢复。 这里最主要的点就是首先从备份中查看备份时候binlog的位置，然后根据日志分析我们要恢复到的日志的位置。

http://www.cndba.cn/dave/article/2654
http://www.cndba.cn/dave/article/2654

版权声明：本文为博主原创文章，未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的：当他回首往事的时候，他不会因为虚度年华而悔恨，也不会因为碌碌无为而羞耻；这样，在临死的时候，他就能够说：“我的整个生命和全部精力，都已经献给世界上最壮丽的事业....."

  • 2262
    原创
  • 3
    翻译
  • 579
    转载
  • 192
    评论
  • 访问：8090004次
  • 积分：4350
  • 等级：核心会员
  • 排名：第1名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ