利用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 执行数据库的全备份；

点击(此处)折叠或打开

[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数据库的信息参数的组合使用：

点击(此处)折叠或打开

[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
#如果有多个binlog文件，用逗号隔开；