mysql xtrabackup mysqlbinlog Point-In-Time recovery

恢复到特定的时间点，可以使用innobackupex 和数据库binlog日志

首先，我们需要一个数据库快照，使用innobackupex 生成一个全库备份

# innobackupex --defaults-file=/etc/my.cnf --user root --password XXXX --no-timestamp /home/ssd/ali_backup/full_xtra_3306_20160825/

模拟测试数据

mysql> create database miles;
Query OK, 1 row affected (0.00 sec)

mysql> use miles;
Database changed
mysql> create table t (
    -> id int,
    -> name varchar(30));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values (1,'m1'),(2,'m2'),(3,'m3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t;
+------+------+
| id   | name |
+------+------+
|    1 | m1   |
|    2 | m2   |
|    3 | m3   |
+------+------+
3 rows in set (0.00 sec)

mysql> update t set name='c2' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t;
+------+------+
| id   | name |
+------+------+
|    1 | m1   |
|    2 | c2   |
|    3 | m3   |
+------+------+
3 rows in set (0.00 sec)

mysql> delete from t where id=1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+------+------+
| id   | name |
+------+------+
|    2 | c2   |
|    3 | m3   |
+------+------+
2 rows in set (0.00 sec)

#误操作删除数据库
mysql> drop database miles;
Query OK, 1 row affected (0.01 sec)

查看当前binlog文件

mysql> show binary logs;
+-----------------------+-----------+
| Log_name              | File_size |
+-----------------------+-----------+
| 3306-mysql-bin.000001 |      6622 |
| 3306-mysql-bin.000002 |      4533 |
+-----------------------+-----------+
2 rows in set (0.00 sec)

查看当前的binlog，及日志的Position

mysql> show master status\G;
*************************** 1. row ***************************
             File: 3306-mysql-bin.000002
         Position: 4533
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 87fd24be-683d-11e6-ba97-1418774c98d8:1-54
1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

关库

# mysqladmin --defaults-file=/home/ssd/ali_data/my.cnf  shutdown -uroot -p --socket=/home/ssd/ali_data/my3306.sock

保护误删除数据库目录结构

# mv ali_data ali_data_bak

应用日志到快照

# innobackupex --defaults-file=/etc/my.cnf --apply-log /home/ssd/ali_backup/full_xtra_3306_20160825/

在备份路径下，通过xtrabackup_binlog_info文件查看快照的Position

# more xtrabackup_binlog_info
3306-mysql-bin.000002   3325    87fd24be-683d-11e6-ba97-1418774c98d8:1-48

将快照拷贝回datadir路径，并更改目录属性

# innobackupex --defaults-file=/etc/my.cnf --copy-back /home/ssd/ali_backup/full_xtra_3306_20160825/

# chown -R mysql:mysql ali_data

查看binlog日志，确定开始的Position和drop操作的Position

# mysqlbinlog -vv --base64-output=decode-rows 3306-mysql-bin.000002
...
# at 3325
#160825 10:59:10 server id 201983306  end_log_pos 3373 CRC32 0xf555e0a5         GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '87fd24be-683d-11e6-ba97-1418774c98d8:49'/*!*/;
# at 3373
#160825 10:59:10 server id 201983306  end_log_pos 3470 CRC32 0x594a3bbd         Query   thread_id=51    exec_time=0     error_code=0
SET TIMESTAMP=1472093950/*!*/;
create database miles
/*!*/;
...
# at 4147
#160825 11:02:10 server id 201983306  end_log_pos 4195 CRC32 0x453242f4         GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '87fd24be-683d-11e6-ba97-1418774c98d8:53'/*!*/;
# at 4195
#160825 11:02:10 server id 201983306  end_log_pos 4268 CRC32 0xca99e1e1         Query   thread_id=51    exec_time=0     error_code=0
SET TIMESTAMP=1472094130/*!*/;
BEGIN
/*!*/;
# at 4268
#160825 11:02:10 server id 201983306  end_log_pos 4316 CRC32 0x3ea968f2         Table_map: `miles`.`t` mapped to number 284
# at 4316
#160825 11:02:10 server id 201983306  end_log_pos 4359 CRC32 0x6e6e79a5         Delete_rows: table id 284 flags: STMT_END_F
### DELETE FROM `miles`.`t`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='m1' /* VARSTRING(90) meta=90 nullable=1 is_null=0 */
# at 4359
#160825 11:02:10 server id 201983306  end_log_pos 4390 CRC32 0x8a88bf8a         Xid = 1371
COMMIT/*!*/;
# at 4390
#160825 11:02:42 server id 201983306  end_log_pos 4438 CRC32 0xe4a75c72         GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '87fd24be-683d-11e6-ba97-1418774c98d8:54'/*!*/;
# at 4438
#160825 11:02:42 server id 201983306  end_log_pos 4533 CRC32 0x5f2c1fe0         Query   thread_id=51    exec_time=0     error_code=0
SET TIMESTAMP=1472094162/*!*/;
drop database miles

应用日志

# mysqlbinlog --start-position=3325 --stop-position=4390 3306-mysql-bin.000002 | mysql -uroot --socket=/home/ssd/ali_data/my3306.sock -p

登录数据库，确认数据恢复情况

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| miles              |
| mysql              |
| performance_schema |
| tmp                |
| ywcf               |
+--------------------+
6 rows in set (0.00 sec)

mysql> use miles;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from t;
+------+------+
| id   | name |
+------+------+
|    2 | c2   |
|    3 | m3   |
+------+------+
2 rows in set (0.00 sec)

确定数据无误后，重新做一份快照

# innobackupex --defaults-file=/etc/my.cnf --user root --password beijing --no-timestamp /home/ssd/ali_backup/full_xtra_3306_20160825_1