Point-In-Time recovery（时间点的恢复innobackupex）

使用工具：innobackupex

第一步：在自己的数据创建添加一些数据

create database xtra_test default charset utf8;

use xtra_test;

create table M(id int,name varchar(10))engine=myisam;

create table I(id int,name varchar(10))engine=innodb;

insert into I values(11,'A'),(22,'B'),(33,'C'),(44,'D'),(55,'E');

insert into M values(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');

 

第二步：进行全量备份

innobackupex --defaults-file=/etc/my.cnf --user=root --password=@126.com /home/backup/xtrabackup/

 

第三步：prepare备份文件

innobackupex --apply-log /home/backup/xtrabackup/2018-07-31_23-32-17/

 

第四步：登录数据库，做些插入删除操作，使备份的position和最新的position有一定的举例，为下一步做Point-In-Time recovery做准备

update M set name = upper(name);

create table X(name varchar(20))default charset utf8;

insert into X values('zjy');

 

第五步：查询数据库当前的binary logs 和最新的位置，及在备份文件中，查看备份到的位置

mysql> SHOW BINARY LOGS;

+------------------+-----------+

| Log_name         | File_size |

+------------------+-----------+

| mysql-bin.000001 |       177 |

| mysql-bin.000002 |      2586 |

+------------------+-----------+

2 rows in set (0.00 sec)

 

mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000002 |     2586 |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

 

备份到的Position：

[root@localhost 2018-07-31_23-32-17]# cat xtrabackup_binlog_info

mysql-bin.000002    1743

 

第六步：清空mysql数据库，进行全备份的copy-back。注意若你的binlog文件在数据目录下，需要将binlog备份一下，我是讲整个数据目录，cp到同目录下的backMySQL文件下。

service mysqld stop        #关掉服务器

rm -rf /var/lib/mysql/*    #删空数据文件

innobackupex --copy-back /home/backup/xtrabackup/2018-07-31_23-32-17/        #数据恢复

sudo chown mysql:mysql /var/lib/mysql -R        #修改权限

service mysqld start      #启动服务器

 

第七步：登录数据库，查看是否恢复到完全备份的时间点

OK

 

第八步：使用二进制文件，数据恢复到最新的位置

mysqlbinlog /var/lib/backupMySQL/mysql-bin.000002 --start-position=1743 | mysql -uroot -p

第九步：登录数据库，检查是否恢复到最新位置

OK

 

补充：

如果有多个二进制文件需要进行恢复的话：

第八步可写成：

mysqlbinlog /var/lib/backupMySQL/mysql-bin.000002 /var/lib/backupMySQL/mysql-bin.000003 --start-position=1743 | mysql -u root -p

 

如果想要恢复到确定的时间，假设为：18-10-13 01:00:00:可使用一下命令：

mysqlbinlog /path/to/datadir/mysql-bin.000003 /path/to/datadir/mysql-bin.000004 \

--start-position=57 --stop-datetime="11-12-25 01:00:00" | mysql -u root -p

 

数据进行时间点的恢复时还可下边这种方法：（从第八步开始可换成一下方法）

1、从二进制文件中提取SQL语句，并存放到mybinlog.sql文件

mysqlbinlog /var/lib/backupMySQL/mysql-bin.000002 /var/lib/backupMySQL/mysql-bin.000003

--start-position=1743 > mybinlog.sql

2、登录mysql，执行mybinlog.sql

source  /data/backup/mybinlog.sql

数据库恢复完成