1. 首页
  2. IT资讯

利用binlog进行数据库的还原


前言:在学习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 backupdir=/backup backupandapplylog //运行数据库的完整备份

3.2 创建数据库、表并插入数据

点击(此处)折叠或打开

  1. mysql> SELECT CURRENT_TIMESTAMP;
  2. ++
  3. | CURRENT_TIMESTAMP |
  4. ++
  5. | 20141126 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> CtrlC
  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.36log 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. | mysqlbin.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. | 20141126 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. | 20141126 17:55:53 |
  69. ++
  70. 1 row in set (0.00 sec)
  71. mysql> show master status;
  72. +++++
  73. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  74. +++++
  75. | mysqlbin.000001 | 1204 | | | //当前binlog的pos位置
  76. +++++
  77. 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. | mysqlbin.000001 | 2125 | | |
  16. +++++
  17. 1 row in set (0.00 sec)
  18. mysql> SELECT CURRENT_TIMESTAMP;
  19. ++
  20. | CURRENT_TIMESTAMP |
  21. ++
  22. | 20141126 17:58:08 |
  23. ++
  24. 1 row in set (0.00 sec)

3.4 以上的操作完成之后,便可以执行数据库的恢复测试

点击(此处)折叠或打开

  1. [root@mysql02 data]# mysqlbackup defaultsfile=/backup/servermy.cnf datadir=/data/mysql backupdir=/backup/ copyback
  2. MySQL Enterprise Backup version 3.11.0 Linux3.8.1316.2.1.el6uek.x86_64x86_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 defaultsfile=/backup/servermy.cnf datadir=/data/mysql
  6. backupdir=/backup/ copyback
  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_20141126.175958_copy_back.log
  12. Server Repository Options:
  13. datadir = /data/mysql
  14. innodb_data_home_dir = /data/mysql
  15. innodb_data_file_path = ibdata1:10M:autoextend
  16. innodb_log_group_home_dir = /data/mysql/
  17. innodb_log_files_in_group = 2
  18. innodb_log_file_size = 5242880
  19. innodb_page_size = Null
  20. innodb_checksum_algorithm = none
  21. Backup Config Options:
  22. datadir = /backup/datadir
  23. innodb_data_home_dir = /backup/datadir
  24. innodb_data_file_path = ibdata1:10M:autoextend
  25. innodb_log_group_home_dir = /backup/datadir
  26. innodb_log_files_in_group = 2
  27. innodb_log_file_size = 5242880
  28. innodb_page_size = 16384
  29. innodb_checksum_algorithm = none
  30. mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
  31. 141126 17:59:58 mysqlbackup: INFO: Copyback operation starts with following threads
  32. 1 readthreads 1 writethreads
  33. mysqlbackup: INFO: Could not find binlog index file. If this is online backup then server may not have started with logbin.
  34. Hence, binlogs will not be copied for this backup. PointInTimeRecovery will not be possible.
  35. 141126 17:59:58 mysqlbackup: INFO: Copying /backup/datadir/ibdata1.
  36. mysqlbackup: Progress in MB: 200 400 600
  37. 141126 18:00:22 mysqlbackup: INFO: Copying the database directory ‘john’
  38. 141126 18:00:23 mysqlbackup: INFO: Copying the database directory ‘mysql’
  39. 141126 18:00:23 mysqlbackup: INFO: Copying the database directory ‘performance_schema’
  40. 141126 18:00:23 mysqlbackup: INFO: Completing the copy of all noninnodb files.
  41. 141126 18:00:23 mysqlbackup: INFO: Copying the log file ‘ib_logfile0’
  42. 141126 18:00:23 mysqlbackup: INFO: Copying the log file ‘ib_logfile1’
  43. 141126 18:00:24 mysqlbackup: INFO: Creating server config files servermy.cnf and serverall.cnf in /data/mysql
  44. 141126 18:00:24 mysqlbackup: INFO: Copyback operation completed successfully.
  45. 141126 18:00:24 mysqlbackup: INFO: Finished copying backup files to ‘/data/mysql’
  46. 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 serverall.cnf
  13. rwxrwxrwx. 1 root root 1815 11月 26 18:00 servermy.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 startposition=107 stopposition=1203 mysqlbin.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.36log 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 startdate=“2014-11-27 09:21:56” stopdate=“2014-11-27 09:22:33” mysqlbin.000001| mysql uroot p123456 //本次通过基于时间点的恢复,恢复到时间点C
  2. Warning: Using unique option prefix startdate instead of startdatetime is deprecated and will be removed in a future release. Please use the full name instead.
  3. Warning: Using unique option prefix stopdate instead of stopdatetime 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.36log 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 mysqlbin.000001 //还原BKT数据库的信息 参数的组合使用:

点击(此处)折叠或打开

  1. [root@mysql02 mysql2]# mysqlbinlog startdate=“2014-11-27 09:21:56” stopdate=“2014-11-27 09:22:33” d BKT h 127.0.0.1 /var/lib/mysql/mysqlbin.000001 |mysql u root p
  2. #如果有多个binlog文件,用逗号隔开;

4.4 恢复是一件很重要的事情,如果不知道具体要恢复的时间点,请把binlog文件先转换成文本文件,详细查看完相应的内容再进行恢复;
[root@mysql02 mysql2]# mysqlbinlog mysqlbin.000001 > /tmp/00001.sql
总结:备份有时候永远都用不上,但是你永远也不知道什么时候会用上,正所谓养兵千日用兵一时,作为一个合格的DBA有个可用的备份,就可以做到胸有成竹;

主题测试文章,只做测试使用。发布者:布吉卡,转转请注明出处:http://www.cxybcw.com/195280.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code