MySQL基于时间点恢复(PITR)
MySQL的PITR主要是通过mysqldump来做全备，然后通过log-bin来恢复到某个时间点，达到PITR的目的
确认log_bin是否打开
mysql> show global variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
当前frank数据中有表t1
mysql> use frank;
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> show tables;
+-----------------+
| Tables_in_frank |
+-----------------+
| t1 |
+-----------------+
1 row in set (0.00 sec)
在时间点a做备份frank数据库
[root@c12 data]# mysqldump -u root -p --protocol tcp --host 127.0.0.1 --port 3306 --flush-logs --databases frank >frank.sql
Enter password:
在时间点b 创建表frank.t2
mysql> create table t2 (a int);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t2 values(10);
Query OK, 1 row affected (0.02 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t2;
+------+
| a |
+------+
| 10 |
+------+
1 row in set (0.00 sec)
在时间点c删除表t1
mysql> drop table t1;
Query OK, 0 rows affected (0.05 sec)
mysql> drop table t2;
Query OK, 0 rows affected (0.01 sec)
要求恢复到时间点b(存在表t1和t2)
1,先恢复全库.
[root@c12 ~]# mysql -u frank -p -h 127.0.0.1 --port 3306 </usr/local/mysql/data/frank.sql
Enter password:
mysql> show tables;
+-----------------+
| Tables_in_frank |
+-----------------+
| t1 |
+-----------------+
1 row in set (0.00 sec)
2,挖掘log-bin日志
[root@c12 data]# mysqlbinlog --start-datetime='2013-12-29 6:25:16' --stop-datetime='2013-12-29 6:34:01' abc.* >recovery.sql
3,重执行sql。
[root@c12 ~]# mysql -u root -p -h 127.0.0.1 --port 3306 </usr/local/mysql/data/recovery.sql
Enter password:
mysql> use frank;
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> show tables;
+-----------------+
| Tables_in_frank |
+-----------------+
| t1 |
| t2 |
+-----------------+
2 rows in set (0.00 sec)
mysql> select * from t2;
+------+
| a |
+------+
| 10 |
+------+
1 row in set (0.01 sec)
检查数据已经被恢复出来了.
start-datetime='2013-12-29 6:25:16' 可以过通sql dump文件中最后一行得到备份的结束时间
stop-datetime='2013-12-29 6:34:01' 通过mysqlbinlog发掘后，产生drop之前的时间点得到
主要的不足:mysqldump本身比较慢;PITR是恢复了整个库，影响比较大，通过查找也可以恢复单个表。
mysqlbinlog --start-datetime='2015-09-14 18:00:00' --stop-datetime='2015-09-24 18:00:01' mysql-bin.* >a1.sql
#150914 18:00:00 server id 135 end_log_pos 34205812 CRC32 0x4d4361a2 Query thread_id=235503 exec_time=0 error_code=0
SET TIMESTAMP=1442224800/*!*/;
SET @@session.pseudo_thread_id=235503/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 34205812
#150914 18:00:00 server id 135 end_log_pos 34206205 CRC32 0x79a69dd2 Query thread_id=235503 exec_time=0 error_code=0
use `quartz`/*!*/;
SET TIMESTAMP=1442224800/*!*/;
UPDATE QRTZ_FIRED_TRIGGERS SET INSTANCE_NAME = 'auto', FIRED_TIME = 1442224800003, SCHED_TIME = 1442224800000, STATE = 'EXECUTING', JOB_NAME = 'QueryRechargeJob', JOB_GROUP = 'zjzcOrderQuery',
IS_NONCONCURRENT = 0, REQUESTS_RECOVERY = 0 WHERE SCHED_NAME = 'ReportControlScheduler' AND ENTRY_ID = 'auto1442195950054'
/*!*/;
# at 494166947
#150924 18:00:00 server id 135 end_log_pos 494167135 CRC32 0x3ded5067 Query thread_id=410262 exec_time=0 error_code=0
SET TIMESTAMP=1443088800/*!*/;
DELETE FROM QRTZ_FIRED_TRIGGERS WHERE SCHED_NAME = 'ReportControlScheduler' AND ENTRY_ID = 'auto1442887716722'
/*!*/;
# at 494167135
#150924 18:00:00 server id 135 end_log_pos 494167166 CRC32 0xa44d66f4 Xid = 24146772