恢复到特定的时间点，可以使用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