AI 助理
备案控制台
开发者社区 数据库 文章 正文

利用binlog进行数据库的还原

2017-07-03 1720
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：
前言：在学习 mysql备份的时候，深深的感受到mysql的备份还原功能没有 oracle强大；比如一个很常见的恢复场景：基于时间点的恢复，oracle通过rman工具就能够很快的实现 数据库的恢复，但是mysql在进行不完全恢复的时候很大的一部分要依赖于mysqlbinlog这个工具运行binlog语句来实现，本文档介绍通过mysqlbinlog实现各种场景的恢复；
　　 一、测试环境说明：使用mysqlbinlog工具的前提需要一个数据库的完整性备份，所以需要事先对数据库做一个完整的备份，本文档通过mysqlbackup进行数据库的全备
　 　二、测试步骤说明：
　　数据库的插入准备 工作
　　2.1 在时间点A进行一个数据库的完整备份；
　　2.2 在时间点B创建一个数据库BKT,并在BKT下面创建一个表JOHN，并插入5条数据；
　　2.3 在时间点C往表JOHN继续插入数据到10条；
　　数据库的恢复工作
　　2.4 恢复数据库到时间点A,然后检查数据库表的状态；
　　2.5 恢复数据库到时间点B,检查相应的系统状态；
　　2.6 恢复数据库到时间点C，并检查恢复的状态；
　　 三、场景模拟测试步骤（备份恢复是一件很重要的事情）
　　3.1 执行数据库的全备份；
　　[root@mysql01 backup]# mysqlbackup --user=root --password --backup-dir=/backup backup-and-apply-log //运行数据库的完整备份
　　3.2 创建数据库、表并插入数据
mysql> SELECT CURRENT_TIMESTAMP;
+---------------------+
| CURRENT_TIMESTAMP |
+---------------------+
| 2014-11-26 17:51:27 |
+---------------------+
1 row in set (0.01 sec)
mysql> show databases; //尚未创建数据库BKT
+--------------------+
| Database |
+--------------------+
| information_schema |
| john |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.03 sec)
mysql> Ctrl-C --
Aborted
[root@mysql02 data]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \\g.
Your MySQL connection id is 2
Server version: 5.5.36-log Source distribution
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type \'help;\' or \'\\h\' for help. Type \'\\c\' to clear the current input statement.
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107 | | | //当前数据库log的pos状态
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> SELECT CURRENT_TIMESTAMP; //当前的时间戳 当前时间点A
+---------------------+
| CURRENT_TIMESTAMP |
+---------------------+
| 2014-11-26 17:54:12 |
+---------------------+
1 row in set (0.00 sec)
mysql> create database BKT; //创建数据库BKT
Query OK, 1 row affected (0.01 sec)
mysql> create table john (id varchar(32));
ERROR 1046 (3D000): No database selected
mysql> use bkt;
ERROR 1049 (42000): Unknown database \'bkt\'
mysql> use BKT;
Database changed
mysql> create table john (id varchar(32));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into john values(\'1\');
Query OK, 1 row affected (0.01 sec)
mysql> insert into john values(\'2\');
Query OK, 1 row affected (0.01 sec)
mysql> insert into john values(\'3\');
Query OK, 1 row affected (0.00 sec)
mysql> insert into john values(\'4\');
Query OK, 1 row affected (0.01 sec)
mysql> insert into john values(\'5\');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT CURRENT_TIMESTAMP; //插入5条数据后数据库的时间点B，记录该点便于数据库的恢复
+---------------------+
| CURRENT_TIMESTAMP |
+---------------------+
| 2014-11-26 17:55:53 |
+---------------------+
1 row in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 1204 | | | //当前binlog的pos位置
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
　3.3 设置时间点C的测试
mysql> insert into john values(\'6\');
Query OK, 1 row affected (0.02 sec)
mysql> insert into john values(\'7\');
Query OK, 1 row affected (0.01 sec)
mysql> insert into john values(\'8\');
Query OK, 1 row affected (0.01 sec)
mysql> insert into john values(\'9\');
Query OK, 1 row affected (0.01 sec)
mysql> insert into john values(\'10\');
Query OK, 1 row affected (0.03 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 2125 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> SELECT CURRENT_TIMESTAMP;
+---------------------+
| CURRENT_TIMESTAMP |
+---------------------+
| 2014-11-26 17:58:08 |
+---------------------+
1 row in set (0.00 sec)
　　3.4 以上的操作完成之后，便可以执行数据库的恢复测试
[root@mysql02 data]# mysqlbackup --defaults-file=/backup/server-my.cnf --datadir=/data/mysql --backup-dir=/backup/ copy-back
MySQL Enterprise Backup version 3.11.0 Linux-3.8.13-16.2.1.el6uek.x86_64-x86_64 [2014/08/26]
Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved.
mysqlbackup: INFO: Starting with following command line ...
mysqlbackup --defaults-file=/backup/server-my.cnf --datadir=/data/mysql
--backup-dir=/backup/ copy-back
mysqlbackup: INFO:
IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful \'copy-back\' run mysqlbackup
prints \"mysqlbackup completed OK!\".
141126 17:59:58 mysqlbackup: INFO: MEB logfile created at /backup/meta/MEB_2014-11-26.17-59-58_copy_back.log
--------------------------------------------------------------------
Server Repository Options:
--------------------------------------------------------------------
datadir = /data/mysql
innodb_data_home_dir = /data/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /data/mysql/
innodb_log_files_in_group = 2
innodb_log_file_size = 5242880
innodb_page_size = Null
innodb_checksum_algorithm = none
--------------------------------------------------------------------
Backup Config Options:
--------------------------------------------------------------------
datadir = /backup/datadir
innodb_data_home_dir = /backup/datadir
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /backup/datadir
innodb_log_files_in_group = 2
innodb_log_file_size = 5242880
innodb_page_size = 16384
innodb_checksum_algorithm = none
mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
141126 17:59:58 mysqlbackup: INFO: Copy-back operation starts with following threads
1 read-threads 1 write-threads
mysqlbackup: INFO: Could not find binlog index file. If this is online backup then server may not have started with --log-bin.
Hence, binlogs will not be copied for this backup. Point-In-Time-Recovery will not be possible.
141126 17:59:58 mysqlbackup: INFO: Copying /backup/datadir/ibdata1.
mysqlbackup: Progress in MB: 200 400 600
141126 18:00:22 mysqlbackup: INFO: Copying the database directory \'john\'
141126 18:00:23 mysqlbackup: INFO: Copying the database directory \'mysql\'
141126 18:00:23 mysqlbackup: INFO: Copying the database directory \'performance_schema\'
141126 18:00:23 mysqlbackup: INFO: Completing the copy of all non-innodb files.
141126 18:00:23 mysqlbackup: INFO: Copying the log file \'ib_logfile0\'
141126 18:00:23 mysqlbackup: INFO: Copying the log file \'ib_logfile1\'
141126 18:00:24 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /data/mysql
141126 18:00:24 mysqlbackup: INFO: Copy-back operation completed successfully.
141126 18:00:24 mysqlbackup: INFO: Finished copying backup files to \'/data/mysql\'
mysqlbackup completed //数据库恢复完成
　　授权并打开数据库
　　[root@mysql02 data]# chmod -R 777 mysql //需要授权后才能打开
　　[root@mysql02 data]# cd mysql
　　[root@mysql02 mysql]# ll
　　总用量 733220
-rwxrwxrwx. 1 root root 305 11月 26 18:00 backup_variables.txt
-rwxrwxrwx. 1 root root 740294656 11月 26 18:00 ibdata1
-rwxrwxrwx. 1 root root 5242880 11月 26 18:00 ib_logfile0
-rwxrwxrwx. 1 root root 5242880 11月 26 18:00 ib_logfile1
drwxrwxrwx. 2 root root 4096 11月 26 18:00 john
drwxrwxrwx. 2 root root 4096 11月 26 18:00 mysql
drwxrwxrwx. 2 root root 4096 11月 26 18:00 performance_schema
-rwxrwxrwx. 1 root root 8488 11月 26 18:00 server-all.cnf
-rwxrwxrwx. 1 root root 1815 11月 26 18:00 server-my.cnf //没有BKT数据库
[root@mysql02 mysql]# service mysqld start //启动数据库
　　3.5 进行数据库的恢复到时间点B
　　[root@mysql02 mysql2]# pwd //备份的时候，需要备份binlog日志，之前的binlog目录为/data/mysql2
　　/data/mysql2
　　[root@mysql02 mysql2]# mysqlbinlog --start-position=107 --stop-position=1203 mysql-bin.000001| mysql -uroot -p //根据post的位置进行恢复，当前的pos位置为107，恢复到pos位置到1203
Enter password:
[root@mysql02 mysql2]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \\g.
Your MySQL connection id is 3
Server version: 5.5.36-log Source distribution
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type \'help;\' or \'\\h\' for help. Type \'\\c\' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| BKT |
| john |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.02 sec)
mysql> use BKT
Database changed
mysql> show tables;
+---------------+
| Tables_in_BKT |
+---------------+
| john |
+---------------+
1 row in set (0.00 sec)
mysql> select * from john;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.01 sec) //查看数据库恢复成功
　　3.6 恢复数据库到时间点C
[root@mysql02 mysql2]# mysqlbinlog --start-date=\"2014-11-27 09:21:56\" --stop-date=\"2014-11-27 09:22:33\" mysql-bin.000001| mysql -uroot -p123456 //本次通过基于时间点的恢复，恢复到时间点C
Warning: Using unique option prefix start-date instead of start-datetime is deprecated and will be removed in a future release. Please use the full name instead.
Warning: Using unique option prefix stop-date instead of stop-datetime is deprecated and will be removed in a future release. Please use the full name instead.
[root@mysql02 mysql2]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \\g.
Your MySQL connection id is 6
Server version: 5.5.36-log Source distribution
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type \'help;\' or \'\\h\' for help. Type \'\\c\' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| BKT |
| john |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)
mysql> use BKT
Database changed
mysql> select * from john;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+------+
10 rows in set (0.00 sec) //经过检查成功恢复到时间点C
　 　四、mysqlbinlog的其他总结：以上是利用binlog文件进行基于时间点和binlog的POS位置恢复的测试，mysqlbinlog的使用还有很多功能，运行mysqlbinlog --help可以查看相应参数；
　　4.1 查看binlog的内容：[root@mysql02 mysql2]# mysqlbinlog mysql-bin.000001
　　4.2 mysqlbinlog的其他常用参数：
　　-h  根据数据库的IP
　　-P  根据数据库所占用的端口来分
　　-server-id 根据数据库serverid来还原（在集群中很有用）
　　-d  根据数据库名称
　　例如： [root@mysql02 mysql2]# mysqlbinlog -d BKT mysql-bin.000001 //还原BKT数据库的信息
最新内容请见作者的GitHub页：http://qaseven.github.io/
文章标签：
云数据库 RDS MySQL 版
关系型数据库
数据库
MySQL
测试技术
Oracle
关键词：
数据库binlog
数据库还原
binlog还原
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发，帮助IT从业者们通过“阿里云Web应用上云解决方案”，了解一个企业级Web应用上云的常见架构，了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品：云数据库RDS MySQL 版 阿里云关系型数据库RDS（Relational Database Service）是一种稳定可靠、可弹性伸缩的在线数据库服务，提供容灾、备份、恢复、迁移等方面的全套解决方案，彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
晚来风急
目录
相关文章
土木林森
|
SQL 关系型数据库 MySQL
【揭秘】MySQL binlog日志与GTID：如何让数据库备份恢复变得轻松简单？
【8月更文挑战第22天】MySQL的binlog日志记录数据变更，用于恢复、复制和点恢复；GTID为每笔事务分配唯一ID，简化复制和恢复流程。开启binlog和GTID后，可通过`mysqldump`进行逻辑备份，包含binlog位置信息，或用`xtrabackup`做物理备份。恢复时，使用`mysql`命令执行备份文件，或通过`innobackupex`恢复物理备份。GTID模式下的主从复制配置更简便。
土木林森
1508 2
栈江湖
|
12月前
|
SQL 关系型数据库 MySQL
数据库灾难应对：MySQL误删除数据的救赎之道，技巧get起来！之binlog
《数据库灾难应对：MySQL误删除数据的救赎之道，技巧get起来！之binlog》介绍了如何利用MySQL的二进制日志（Binlog）恢复误删除的数据。主要内容包括： 1. **启用二进制日志**：在`my.cnf`中配置`log-bin`并重启MySQL服务。 2. **查看二进制日志文件**：使用`SHOW VARIABLES LIKE 'log_%';`和`SHOW MASTER STATUS;`命令获取当前日志文件及位置。 3. **创建数据备份**：确保在恢复前已有备份，以防意外。 4. **导出二进制日志为SQL语句**：使用`mysqlbinlog`
栈江湖
830 2
兀码
|
SQL 关系型数据库 MySQL
使用mysql数据库的binlog应对故障
【6月更文挑战第1天】本文介绍`mysql的 binlog`工具用于解析MySQL的二进制日志，转换为可执行的SQL语句，主要用于数据库主从复制和增量恢复。定期备份和binlog推送能实现故障时的数据恢复。
兀码
568 9
使用mysql数据库的binlog应对故障
以山向海
|
SQL 运维 关系型数据库
关系型数据库mysql二进制日志（Binary Log, Binlog）
【6月更文挑战第20天】
以山向海
261 3
三分钟热度的鱼
|
消息中间件 Oracle Kafka
实时计算 Flink版产品使用问题之启动多个job清洗会对原数据库的Binlog造成什么影响
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架，广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器，提供了一套全面的解决方案，以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点，使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
三分钟热度的鱼
137 1
软件求生
|
存储 缓存 NoSQL
Redis与数据库同步指南：订阅Binlog实现数据一致性
本文由开发者小米分享，探讨分布式系统中的一致性问题，尤其是数据库和Redis一致性。文章介绍了全量缓存策略的优势，如高效读取和稳定性，但也指出其一致性挑战。为解决此问题，提出了通过订阅数据库的Binlog实现数据同步的方法，详细解释了工作原理和步骤，并分析了优缺点。此外，还提到了异步校准方案作为补充，以进一步保证数据一致性。最后，提醒在实际线上环境中需注意日志记录、逐步优化和监控报警。
软件求生
1372 3
三分钟热度的鱼
|
Oracle 关系型数据库 Java
实时计算 Flink版产品使用问题之如何启动多个jar包来监听同一个数据库的binlog，并针对不同的业务进行处理
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架，广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器，提供了一套全面的解决方案，以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点，使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
三分钟热度的鱼
200 0
vohelon
|
消息中间件 Java Kafka
启动多个jar包来监听同一个数据库的binlog
【2月更文挑战第27天】启动多个jar包来监听同一个数据库的binlog
vohelon
193 8
一只牛博
|
监控 安全 数据库
Binlog vs. Redo Log：数据库日志的较劲【高级】
Binlog vs. Redo Log：数据库日志的较劲【高级】
一只牛博
288 0
一只牛博
|
存储 缓存 关系型数据库
Binlog vs. Redo Log：数据库日志的较劲【基础】
Binlog vs. Redo Log：数据库日志的较劲【基础】
一只牛博
603 0

热门文章

最新文章

  • 1
    云栖大会｜AI时代关系型数据库创新突破：软硬协同赋能企业数智化转型
  • 2
    云栖大会｜AI时代的数据库变革升级与实践：Data+AI驱动企业智能新范式
  • 3
    Python SQLAlchemy模块：从入门到实战的数据库操作指南
  • 4
    云栖大会｜AI浪潮下的NoSQL演进：下一代数据库的破局之道
  • 5
    除了JDBC，还有哪些常见的数据库访问技术？
  • 6
    【赵渝强老师】使用NetManager创建Oracle数据库的监听器
  • 7
    通过binlog恢复mysql数据库
  • 8
    手把手告诉你如何监听 MySQL binlog 实现数据变化后的实时通知！
  • 9
    为 MySQL/MariaDB 开启 Binlog 功能
  • 10
    MySQL · 引擎特性 · DROP TABLE之binlog解析
  • 1
    关系型数据库中对索引的数目
    278
  • 2
    济南sql数据库工程师
    196
  • 3
    数据库sql超市
    298
  • 4
    sql数据库清除工具
    472
  • 5
    sql数据库命令
    181
  • 6
    Mysql 数据库基本操作
    159
  • 7
    众安保险 CDP 平台：借助阿里云数据库 SelectDB 版内核 Apache Doris 打破数据孤岛，人群圈选提速4倍
    503
  • 8
    sql2005恢复数据库
    190
  • 9
    sql 数据库教程
    243
  • 10
    数据库更新sql
    146

    • 相关课程

    更多
  • 数据库的前世今生
  • 数据库核心概念
  • 从传统数据库到云数据库演进
  • 数据库常见问题排查
  • 数据库及SQL/MySQL基础
  • 高校精品课-西安交通大学 -数据库理论与技术

    • 相关电子书

    更多
  • 开源数据库工作室推广计划
  • 国产数据库研制人才培养实践
  • 阿里云&信通院《Serverless数据库技术研究报告》
    • 下一篇
    「直播预告」Streaming Lakehouse Meetup EP.2｜Paimon × StarRocks 共话实时湖仓