xtrabackup备份恢复过程
#全备innobackupex --user=root --password=123456 --no-timestamp /backup/full
增加数据
mysql> insert into oldboy.test values(2,'b');
Query OK, 1 row affected (0.00 sec)
mysql> select * from oldboy.test;
+------+------+
| id| name |
+------+------+
| 1 | a |
| 2 | b |
+------+------+
#第一次增备
innobackupex --user=root --password=123456 --incremental --no-timestamp --incremental-basedir=/backup/full//backup/inc1
增加数据
mysql> insert into oldboy.test values(3,'c');
Query OK, 1 row affected (0.01 sec)
mysql> select * from oldboy.test;
+------+------+
| id| name |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
+------+------+
# 第二次增备
innobackupex --user=root --password=123456 --incremental --no-timestamp --incremental-basedir=/backup/inc1//backup/inc2
增加数据
mysql> insert into oldboy.test values(4,'d');
Query OK, 1 row affected (0.00 sec)
mysql> select * from oldboy.test;
+------+------+
| id| name |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+------+------+
#破坏数据
drop table oldboy.test;
#恢复
##应用全备
innobackupex --apply-log --redo-only /backup/full/
#第一次增备合并到全备
innobackupex --apply-log --redo-only /backup/full/ --incremental-dir=/backup/inc1/
#第二次增备合并到全备
innobackupex --apply-log --redo-only /backup/full/ --incremental-dir=/backup/inc2/
# 最后应用全备
innobackupex --apply-log /backup/full/
#恢复
innobackupex --copy-back /backup/full/
#启动数据库
/etc/init.d/mysqld start
#binlog恢复
#查看最后一次增备的binlog文件及位置
cat /backup/inc2/xtrabackup_binlog_info
mysql-bin.000001 554
#其它数据需要binlog恢复，binlog日志片段
# at 554
#1711235:05:47 server id 1end_log_pos 631 CRC32 0x682f8893 Query thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1511413547/*!*/;
BEGIN
/*!*/;
# at 631
#1711235:05:47 server id 1end_log_pos 740 CRC32 0xc13589a3 Query thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1511413547/*!*/;
insert into oldboy.test values(4,'d')
/*!*/;
# at 740
#1711235:05:47 server id 1end_log_pos 771 CRC32 0x9fe9947a Xid = 62
COMMIT/*!*/;
# at 771
#1711235:07:01 server id 1end_log_pos 895 CRC32 0xc7671d4f Query thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1511413621/*!*/;
DROP TABLE `oldboy`.`test` /* generated by server */
/*!*/;
# at 895
#1711235:16:08 server id 1end_log_pos 918 CRC32 0x7fc7c108 Stop
DELIMITER ;
# End of log file
# 导出binlog
mysqlbinlog mysql-bin.000001 --start-position=554 --stop-position=771 > /backup/binlog.sql
# 导入binlog
set sql_log_bin=0;
source /backup/binlog.sql
#最终数据
mysql> select * from oldboy.test;
+------+------+
| id| name |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+------+------+
4 rows in set (0.00 sec)
页:
[1]