利用binlog进行数据库的还原-阿里云开发者社区

开发者社区> 小麦苗> 正文

利用binlog进行数据库的还原

简介: 前言:在学习mysql备份的时候,深深的感受到mysql的备份还原功能没有oracle强大;比如一个很常见的恢复场景:基于时间点的恢复,oracle通过rman工具就能够很快的实现数据库的恢复,但是mysql在进行不完全恢复的时候很大的一部分要依赖于mysql...
+关注继续查看

前言:在学习mysql备份的时候,深深的感受到mysql的备份还原功能没有oracle强大;比如一个很常见的恢复场景:基于时间点的恢复,oracle通过rman工具就能够很快的实现数据库的恢复,但是mysql在进行不完全恢复的时候很大的一部分要依赖于mysqlbinlog这个工具运行binlog语句来实现,本文档介绍通过mysqlbinlog实现各种场景的恢复;

一、测试环境说明:使用mysqlbinlog工具的前提需要一个数据库的完整性备份,所以需要事先对数据库做一个完整的备份,本文档通过mysqlbackup进行数据库的全备(mysqlbackup的使用:http://blog.itpub.net/12679300/viewspace-1329578/);

二、测试步骤说明:
数据库的插入准备工作
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 执行数据库的全备份;

点击(此处)折叠或打开

  1. [root@mysql01 backup]# mysqlbackup --user=root --password --backup-dir=/backup backup-and-apply-log //运行数据库的完整备份
3.2 创建数据库、表并插入数据

点击(此处)折叠或打开

  1. mysql> SELECT CURRENT_TIMESTAMP;
  2. +---------------------+
  3. | CURRENT_TIMESTAMP |
  4. +---------------------+
  5. | 2014-11-26 17:51:27 |
  6. +---------------------+
  7. 1 row in set (0.01 sec)

  8. mysql> show databases; //尚未创建数据库BKT
  9. +--------------------+
  10. | Database |
  11. +--------------------+
  12. | information_schema |
  13. | john |
  14. | mysql |
  15. | performance_schema |
  16. +--------------------+
  17. 4 rows in set (0.03 sec)

  18. mysql> Ctrl-C --
  19. Aborted
  20. [root@mysql02 data]# mysql -uroot -p
  21. Enter password:
  22. Welcome to the MySQL monitor. Commands end with ; or \\g.
  23. Your MySQL connection id is 2
  24. Server version: 5.5.36-log Source distribution
  25. Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
  26. Oracle is a registered trademark of Oracle Corporation and/or its
  27. affiliates. Other names may be trademarks of their respective
  28. owners.
  29. Type \'help;\' or \'\\h\' for help. Type \'\\c\' to clear the current input statement.
  30. mysql> show master status;
  31. +------------------+----------+--------------+------------------+
  32. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  33. +------------------+----------+--------------+------------------+
  34. | mysql-bin.000001 | 107 | | | //当前数据库log的pos状态
  35. +------------------+----------+--------------+------------------+
  36. 1 row in set (0.00 sec)
  37. mysql> SELECT CURRENT_TIMESTAMP; //当前的时间戳 当前时间点A
  38. +---------------------+
  39. | CURRENT_TIMESTAMP |
  40. +---------------------+
  41. | 2014-11-26 17:54:12 |
  42. +---------------------+
  43. 1 row in set (0.00 sec)
  44. mysql> create database BKT; //创建数据库BKT
  45. Query OK, 1 row affected (0.01 sec)
  46. mysql> create table john (id varchar(32));
  47. ERROR 1046 (3D000): No database selected
  48. mysql> use bkt;
  49. ERROR 1049 (42000): Unknown database \'bkt\'
  50. mysql> use BKT;
  51. Database changed
  52. mysql> create table john (id varchar(32));
  53. Query OK, 0 rows affected (0.02 sec)
  54. mysql> insert into john values(\'1\');
  55. Query OK, 1 row affected (0.01 sec)
  56. mysql> insert into john values(\'2\');
  57. Query OK, 1 row affected (0.01 sec)
  58. mysql> insert into john values(\'3\');
  59. Query OK, 1 row affected (0.00 sec)
  60. mysql> insert into john values(\'4\');
  61. Query OK, 1 row affected (0.01 sec)
  62. mysql> insert into john values(\'5\');
  63. Query OK, 1 row affected (0.01 sec)
  64. mysql> SELECT CURRENT_TIMESTAMP; //插入5条数据后数据库的时间点B,记录该点便于数据库的恢复
  65. +---------------------+
  66. | CURRENT_TIMESTAMP |
  67. +---------------------+
  68. | 2014-11-26 17:55:53 |
  69. +---------------------+
  70. 1 row in set (0.00 sec)
  71.  
  72. mysql> show master status;
  73. +------------------+----------+--------------+------------------+
  74. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  75. +------------------+----------+--------------+------------------+
  76. | mysql-bin.000001 | 1204 | | | //当前binlog的pos位置
  77. +------------------+----------+--------------+------------------+
  78. 1 row in set (0.00 sec)
 3.3 设置时间点C的测试

点击(此处)折叠或打开

  1. mysql> insert into john values(\'6\');
  2. Query OK, 1 row affected (0.02 sec)
  3. mysql> insert into john values(\'7\');
  4. Query OK, 1 row affected (0.01 sec)
  5. mysql> insert into john values(\'8\');
  6. Query OK, 1 row affected (0.01 sec)
  7. mysql> insert into john values(\'9\');
  8. Query OK, 1 row affected (0.01 sec)
  9. mysql> insert into john values(\'10\');
  10. Query OK, 1 row affected (0.03 sec)
  11. mysql> show master status;
  12. +------------------+----------+--------------+------------------+
  13. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  14. +------------------+----------+--------------+------------------+
  15. | mysql-bin.000001 | 2125 | | |
  16. +------------------+----------+--------------+------------------+
  17. 1 row in set (0.00 sec)
  18. mysql> SELECT CURRENT_TIMESTAMP;
  19. +---------------------+
  20. | CURRENT_TIMESTAMP |
  21. +---------------------+
  22. | 2014-11-26 17:58:08 |
  23. +---------------------+
  24. 1 row in set (0.00 sec)
 3.4 以上的操作完成之后,便可以执行数据库的恢复测试

点击(此处)折叠或打开

  1. [root@mysql02 data]# mysqlbackup --defaults-file=/backup/server-my.cnf --datadir=/data/mysql --backup-dir=/backup/ copy-back
  2. MySQL Enterprise Backup version 3.11.0 Linux-3.8.13-16.2.1.el6uek.x86_64-x86_64 [2014/08/26]
  3. Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved.
  4.  mysqlbackup: INFO: Starting with following command line ...
  5.  mysqlbackup --defaults-file=/backup/server-my.cnf --datadir=/data/mysql
  6.         --backup-dir=/backup/ copy-back
  7.  mysqlbackup: INFO:
  8. IMPORTANT: Please check that mysqlbackup run completes successfully.
  9.            At the end of a successful \'copy-back\' run mysqlbackup
  10.            prints \"mysqlbackup completed OK!\".
  11. 141126 17:59:58 mysqlbackup: INFO: MEB logfile created at /backup/meta/MEB_2014-11-26.17-59-58_copy_back.log
  12. --------------------------------------------------------------------
  13.                        Server Repository Options:
  14. --------------------------------------------------------------------
  15.   datadir = /data/mysql
  16.   innodb_data_home_dir = /data/mysql
  17.   innodb_data_file_path = ibdata1:10M:autoextend
  18.   innodb_log_group_home_dir = /data/mysql/
  19.   innodb_log_files_in_group = 2
  20.   innodb_log_file_size = 5242880
  21.   innodb_page_size = Null
  22.   innodb_checksum_algorithm = none
  23. --------------------------------------------------------------------
  24.                        Backup Config Options:
  25. --------------------------------------------------------------------
  26.   datadir = /backup/datadir
  27.   innodb_data_home_dir = /backup/datadir
  28.   innodb_data_file_path = ibdata1:10M:autoextend
  29.   innodb_log_group_home_dir = /backup/datadir
  30.   innodb_log_files_in_group = 2
  31.   innodb_log_file_size = 5242880
  32.   innodb_page_size = 16384
  33.   innodb_checksum_algorithm = none
  34.  mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
  35. 141126 17:59:58 mysqlbackup: INFO: Copy-back operation starts with following threads
  36.         1 read-threads 1 write-threads
  37.  mysqlbackup: INFO: Could not find binlog index file. If this is online backup then server may not have started with --log-bin.
  38.         Hence, binlogs will not be copied for this backup. Point-In-Time-Recovery will not be possible.
  39. 141126 17:59:58 mysqlbackup: INFO: Copying /backup/datadir/ibdata1.
  40.  mysqlbackup: Progress in MB: 200 400 600
  41. 141126 18:00:22 mysqlbackup: INFO: Copying the database directory \'john\'
  42. 141126 18:00:23 mysqlbackup: INFO: Copying the database directory \'mysql\'
  43. 141126 18:00:23 mysqlbackup: INFO: Copying the database directory \'performance_schema\'
  44. 141126 18:00:23 mysqlbackup: INFO: Completing the copy of all non-innodb files.
  45. 141126 18:00:23 mysqlbackup: INFO: Copying the log file \'ib_logfile0\'
  46. 141126 18:00:23 mysqlbackup: INFO: Copying the log file \'ib_logfile1\'
  47. 141126 18:00:24 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /data/mysql
  48. 141126 18:00:24 mysqlbackup: INFO: Copy-back operation completed successfully.
  49. 141126 18:00:24 mysqlbackup: INFO: Finished copying backup files to \'/data/mysql\'
  50. mysqlbackup completed //数据库恢复完成
 授权并打开数据库

点击(此处)折叠或打开

  1. [root@mysql02 data]# chmod -R 777 mysql //需要授权后才能打开
  2. [root@mysql02 data]# cd mysql
  3. [root@mysql02 mysql]# ll
  4. 总用量 733220
  5. -rwxrwxrwx. 1 root root 305 11月 26 18:00 backup_variables.txt
  6. -rwxrwxrwx. 1 root root 740294656 11月 26 18:00 ibdata1
  7. -rwxrwxrwx. 1 root root 5242880 11月 26 18:00 ib_logfile0
  8. -rwxrwxrwx. 1 root root 5242880 11月 26 18:00 ib_logfile1
  9. drwxrwxrwx. 2 root root 4096 11月 26 18:00 john
  10. drwxrwxrwx. 2 root root 4096 11月 26 18:00 mysql
  11. drwxrwxrwx. 2 root root 4096 11月 26 18:00 performance_schema
  12. -rwxrwxrwx. 1 root root 8488 11月 26 18:00 server-all.cnf
  13. -rwxrwxrwx. 1 root root 1815 11月 26 18:00 server-my.cnf //没有BKT数据库
  14. [root@mysql02 mysql]# service mysqld start //启动数据库
 3.5 进行数据库的恢复到时间点B

点击(此处)折叠或打开

  1. [root@mysql02 mysql2]# pwd //备份的时候,需要备份binlog日志,之前的binlog目录为/data/mysql2
  2. /data/mysql2
  3. [root@mysql02 mysql2]# mysqlbinlog --start-position=107 --stop-position=1203 mysql-bin.000001| mysql -uroot -p //根据post的位置进行恢复,当前的pos位置为107,恢复到pos位置到1203
  4. Enter password:
  5. [root@mysql02 mysql2]# mysql -uroot -p
  6. Enter password:
  7. Welcome to the MySQL monitor. Commands end with ; or \\g.
  8. Your MySQL connection id is 3
  9. Server version: 5.5.36-log Source distribution
  10. Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
  11. Oracle is a registered trademark of Oracle Corporation and/or its
  12. affiliates. Other names may be trademarks of their respective
  13. owners.
  14. Type \'help;\' or \'\\h\' for help. Type \'\\c\' to clear the current input statement.
  15. mysql> show databases;
  16. +--------------------+
  17. | Database |
  18. +--------------------+
  19. | information_schema |
  20. | BKT |
  21. | john |
  22. | mysql |
  23. | performance_schema |
  24. +--------------------+
  25. 5 rows in set (0.02 sec)
  26. mysql> use BKT
  27. Database changed
  28. mysql> show tables;
  29. +---------------+
  30. | Tables_in_BKT |
  31. +---------------+
  32. | john |
  33. +---------------+
  34. 1 row in set (0.00 sec)
  35. mysql> select * from john;
  36. +------+
  37. | id |
  38. +------+
  39. | 1 |
  40. | 2 |
  41. | 3 |
  42. | 4 |
  43. | 5 |
  44. +------+
  45. 5 rows in set (0.01 sec) //查看数据库恢复成功
 3.6 恢复数据库到时间点C

点击(此处)折叠或打开

  1. [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
  2. 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.
  3. 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.
  4. [root@mysql02 mysql2]# mysql -uroot -p
  5. Enter password:
  6. Welcome to the MySQL monitor. Commands end with ; or \\g.
  7. Your MySQL connection id is 6
  8. Server version: 5.5.36-log Source distribution
  9. Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
  10. Oracle is a registered trademark of Oracle Corporation and/or its
  11. affiliates. Other names may be trademarks of their respective
  12. owners.
  13. Type \'help;\' or \'\\h\' for help. Type \'\\c\' to clear the current input statement.
  14. mysql> show databases;
  15. +--------------------+
  16. | Database |
  17. +--------------------+
  18. | information_schema |
  19. | BKT |
  20. | john |
  21. | mysql |
  22. | performance_schema |
  23. +--------------------+
  24. 5 rows in set (0.00 sec)
  25. mysql> use BKT
  26. Database changed
  27. mysql> select * from john;
  28. +------+
  29. | id |
  30. +------+
  31. | 1 |
  32. | 2 |
  33. | 3 |
  34. | 4 |
  35. | 5 |
  36. | 6 |
  37. | 7 |
  38. | 8 |
  39. | 9 |
  40. | 10 |
  41. +------+
  42. 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数据库的信息

参数的组合使用:

点击(此处)折叠或打开

  1. [root@mysql02 mysql2]# mysqlbinlog --start-date=\"2014-11-27 09:21:56\" --stop-date=\"2014-11-27 09:22:33\" -d BKT -h 127.0.0.1 /var/lib/mysql/mysql-bin.000001 |mysql -u root -p
  2. #如果有多个binlog文件,用逗号隔开;
 
4.4 恢复是一件很重要的事情,如果不知道具体要恢复的时间点,请把binlog文件先转换成文本文件,详细查看完相应的内容再进行恢复;
[root@mysql02 mysql2]# mysqlbinlog mysql-bin.000001 > /tmp/00001.sql

总结:备份有时候永远都用不上,但是你永远也不知道什么时候会用上,正所谓养兵千日用兵一时,作为一个合格的DBA有个可用的备份,就可以做到胸有成竹;




 




版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
十分钟教你了解阿里云数据库RDS
阿里云关系型数据库(Relational Database Service,简称RDS)是一种稳定可靠、可弹性伸缩的在线数据库服务。基于阿里云分布式文件系统和SSD盘高性能存储,RDS支持MySQL、SQL Server、PostgreSQL、PPAS(Postgre Plus Advanced Server,高度兼容Oracle数据库)和MariaDB TX引擎,并且提供了容灾、备份、恢复、监控、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。
535 0
PostgreSQL 数据库实例只读锁定(readonly) - 硬锁定,软锁定,解锁
PostgreSQL 数据库实例只读锁定(readonly) - 硬锁定,软锁定,解锁
555 0
SQL SERVER数据库删除LOG文件和清空日志的方案
原文:SQL SERVER数据库删除LOG文件和清空日志的方案 数据库在使用过程中会使日志文件不断增加,使得数据库的性能下降,并且占用大量的磁盘空间。SQL Server数据库都有log文件,log文件记录用户对数据库修改的操作。
4421 0
weblogic连接RAC数据库
对于负载均衡的机器,要连接多个实例的数据库的时候,使用这种策略目前是比较好的一种方案,当然也可以使用weblogic自带的解决方案。 直接使用了RAC的负载均衡策略。   在Oracle中找到tnsnames.ora这个文件。
658 0
邮件服务别名数据库设置指南
使用 Linux 服务器的时候,我们经常需要转发邮件,最简单的方案就是使用别名。以下简单介绍 sendmail 的别名数据库配置。这些配置也同样适用于兼容 sendmail 的其他邮件服务,例如 postfix。
78 0
重新定义数据库的时刻,阿里云数据库专家带你了解POLARDB
POLARDB是阿里云ApsaraDB数据库团队研发的基于云计算架构的下一代关系型数据库,其最大的特色是计算节点与存储节点分离,借助优秀的RDMA网络以及最新的块存储技术。POLARDB不但满足了公有云计算环境下用户业务快速弹性扩展的刚性需求,同时也满足了互联网环境下用户对数据库服务器高可用的需求。
3196 0
+关注
小麦苗
网名:小麦苗 | 微信公众号:DB宝 | 11g和12c OCM | 《数据库笔试面试宝典》作者,博客地址:http://blog.itpub.net/26736162/abstract/1/
889
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载