mysql point in time_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