mysql 基于时间点恢复

分享一下我老师大神的人工智能教程！零基础，通俗易懂！http://blog.csdn.net/jiangjunshow

也欢迎大家转载本篇文章。分享知识，造福人民，实现我们中华民族伟大复兴！

               

         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数据中有表t1mysql> use frank;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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.sqlEnter password: 在时间点b 创建表frank.t2mysql> 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删除表t1mysql> 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.sql3,重执行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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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=0SET 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=0use `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=0SET 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

           

给我老师的人工智能教程打call！http://blog.csdn.net/jiangjunshow