Point-In-Time recovery
来源:互联网 发布:matlab矩阵运算源代码 编辑:程序博客网 时间:2024/06/06 08:27
本文转载自:http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/pit_recovery_ibk.html
Point-In-Time recovery
Recovering up to particular moment in database’s history can be done with innobackupex and the binary logs of the server.
Note that the binary log contains the operations that modified the database from a point in the past. You need a fulldatadir as a base, and then you can apply a series of operations from the binary log to make the data match what it was at the point in time you want.
For taking the snapshot, we will use innobackupex for a full backup:
$ innobackupex /path/to/backup --no-timestamp
(the --no-timestamp option is for convenience in this example) and we will prepare it to be ready for restoration:
$ innobackupex --apply-log /path/to/backup
For more details on these procedures, see Creating a Backup with innobackupex and Preparing a Full Backup with innobackupex.
Now, suppose that time has passed, and you want to restore the database to a certain point in the past, having in mind that there is the constraint of the point where the snapshot was taken.
To find out what is the situation of binary logging in the server, execute the following queries:
mysql> SHOW BINARY LOGS;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 | 126 || mysql-bin.000002 | 1306 || mysql-bin.000003 | 126 || mysql-bin.000004 | 497 |+------------------+-----------+
and
mysql> SHOW MASTER STATUS;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000004 | 497 | | |+------------------+----------+--------------+------------------+
The first query will tell you which files contain the binary log and the second one which file is currently being used to record changes, and the current position within it. Those files are stored usually in the datadir (unless other location is specified when the server is started with the --log-bin= option).
To find out the position of the snapshot taken, see the xtrabackup_binlog_info at the backup’s directory:
$ cat /path/to/backup/xtrabackup_binlog_infomysql-bin.000003 57
This will tell you which file was used at moment of the backup for the binary log and its position. That position will be the effective one when you restore the backup:
$ innobackupex --copy-back /path/to/backup
As the restoration will not affect the binary log files (you may need to adjust file permissions, see Restoring a Full Backup with innobackupex), the next step is extracting the queries from the binary log with mysqlbinlog starting from the position of the snapshot and redirecting it to a file
$ mysqlbinlog /path/to/datadir/mysql-bin.000003 /path/to/datadir/mysql-bin.000004 \ --start-position=57 > mybinlog.sql
Note that if you have multiple files for the binary log, as in the example, you have to extract the queries with one process, as shown above.
Inspect the file with the queries to determine which position or date corresponds to the point-in-time wanted. Once determined, pipe it to the server. Assuming the point is 11-12-25 01:00:00:
$ mysqlbinlog /path/to/datadir/mysql-bin.000003 /path/to/datadir/mysql-bin.000004 \ --start-position=57 --stop-datetime="11-12-25 01:00:00" | mysql -u root -p
and the database will be rolled forward up to that Point-In-Time.
注:近期参加MySQL运维学习,老师推荐该文章作为学习和技术提高的扩展阅读,先记录到自己的博客中,随后慢慢消化、学习、提高。本文章与“日志系统”主题相关。
- Point-In-Time recovery
- Point-In-Time recovery
- RMAN Tablespace Point-in-Time Recovery (TSPITR)
- Oracle Tablespace Point-in-Time Recovery (TSPITR)
- TSPITR:Tablespace Point-in-Time Recovery实例
- mysql xtrabackup mysqlbinlog Point-In-Time recovery
- RMAN Table Point In Time Recovery (PITR) in PDBs
- PostgreSQL的PITR技术(Point-In-Time-Recovery)
- TSPITR:Tablespace Point-in-Time Recovery表空间不完全恢复
- Point-in-Time (Incremental) Recovery Using the Binary Log
- MySQL backup and point in time recovery with binary logs
- Point-in-Time (Incremental) Recovery Using the Binary Log
- 7.5.1 Point-in-Time Recovery Using Event Times
- 7.5.2 Point-in-Time Recovery Using Event Positions
- tspitr(tablespace point in time recovery)实验
- 每日MySQL之014:Point-in-Time recovery
- Complete, Incomplete and Point-In-Time Recovery【每日一译】--2012-11-09
- Tablespace Point-in-Time Recovery--关于表空间即时点恢复
- memchached与redis相比过时?
- 出现错误instance method 'xxxmethod' not found (return type defaults to 'id')的问题修正
- 百度地图IOS版学习开发流程
- BeanUtils.populate的作用
- Spring3.5 jdbc template API 总结
- Point-In-Time recovery
- phoneGap中android程序自动更新代码(适合Android4.0之后版本)
- Android Wear 在可穿戴设备中使用语音进行交互
- C语言函数参数传递原理
- groovy入门 第04章 列表、映射和范围
- Parcel和Parcelable
- 压力测试
- HNU 12844 Arrangement of Contest
- 前台技术--通过javaScript提交表单