您可以捐助,支持我们的公益事业。

1元 10元 50元





认证码:  验证码,看不清楚?请点击刷新验证码 必填



  要资料 文章 文库 Lib 视频 Code iProcess 课程 认证 咨询 工具 火云堂 讲座吧   成长之路  
会员   
 
   
 
  
每天15篇文章
不仅获得谋生技能
更可以追随信仰
 
 
     
   
 订阅
  捐助
利用binlog进行数据库的还原
 
作者:wzq609 来源:51Testing 发布于:2015-01-06
1414 次浏览     评价:      
 

前言:在学习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数据库的信息

   
 订阅
  捐助
相关文章 相关文档 相关课程



我们该如何设计数据库
数据库设计经验谈
数据库设计过程
数据库编程总结
数据库性能调优技巧
数据库性能调整
数据库性能优化讲座
数据库系统性能调优系列
高性能数据库设计与优化
高级数据库架构师
数据仓库和数据挖掘技术
Hadoop原理、部署与性能调优
 

MySQL索引背后的数据结构
MySQL性能调优与架构设计
SQL Server数据库备份与恢复
让数据库飞起来 10大DB2优化
oracle的临时表空间写满磁盘
数据库的跨平台设计
更多...   


并发、大容量、高性能数据库
高级数据库架构设计师
Hadoop原理与实践
Oracle 数据仓库
数据仓库和数据挖掘
Oracle数据库开发与管理


领先IT公司 android开发平台最佳实践
北京 Android开发技术进阶
某新能源领域企业 Android开发技术
某航天公司 Android、IOS应用软件开发
阿尔卡特 Linux内核驱动
艾默生 嵌入式软件架构设计
西门子 嵌入式架构设计
更多...   
 
 
 
 
 
每天2个文档/视频
扫描微信二维码订阅
订阅技术月刊
获得每月300个技术资源
 
 

关于我们 | 联系我们 | 京ICP备10020922号 京公海网安备110108001071号