step 1: 全备
# innobackupex --defaults-file=/etc/my.cnf --no-timestamp /home/ssd/ali_backup/full_xtra_3306_20160826 --user root --password beijing --socket=/home/ssd/ali_data/my3306.sock查看xtrabackup_checkpoints 文件
# more full_xtra_3306_20160826/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 12222652952
last_lsn = 12222652952
compact = 0
recover_binlog_info = 0测试数据
mysql> create database miles;
Query OK, 1 row affected (0.01 sec)
mysql> use miles;
Database changed
mysql> create table t( id int, name varchar(30));
Query OK, 0 rows affected (0.05 sec)step 2:1级备份
# innobackupex --defaults-file=/etc/my.cnf --no-timestamp --incremental /home/ssd/ali_backup/full_xtra_3306_20160826_i_01 --incremental-basedir=/home/ssd/ali_backup/full_xtra_3306_20160826/ --user root --password beijing --socket=/home/ssd/ali_data/my3306.sock查看xtrabackup_checkpoints 文件
# more full_xtra_3306_20160826_i_01/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 12222652952
to_lsn = 12222657366
last_lsn = 12222657366
compact = 0
recover_binlog_info = 0测试数据
mysql> insert into t values (1, 'i1'),(2,'i2'),(3,'i3');
mysql> select * from t;
+------+------+
| id | name |
+------+------+
| 1 | i1 |
| 2 | i2 |
| 3 | i3 |
+------+------+
3 rows in set (0.01 sec)step 3:2级备份
# innobackupex --defaults-file=/etc/my.cnf --no-timestamp --incremental /home/ssd/ali_backup/full_xtra_3306_20160826_i_02 --incremental-basedir=/home/ssd/ali_backup/full_xtra_3306_20160826_i_01/ --user root --password beijing --socket=/home/ssd/ali_data/my3306.sock查看xtrabackup_checkpoints 文件
# more full_xtra_3306_20160826_i_02/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 12222657366
to_lsn = 12222659058
last_lsn = 12222659058
compact = 0
recover_binlog_info = 0测试数据
mysql> delete from t where id=2;
Query OK, 1 row affected (0.02 sec)
mysql> select * from t;
+------+------+
| id | name |
+------+------+
| 1 | i1 |
| 3 | i3 |
+------+------+
2 rows in set (0.00 sec)
mysql> drop database miles;
Query OK, 1 row affected (0.02 sec)
step 4:查看当前日志
mysql> show master status\G;
*************************** 1. row ***************************
File: 3306-mysql-bin.000003
Position: 1319
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 87fd24be-683d-11e6-ba97-1418774c98d8:49-53,
cdf80832-6a74-11e6-890b-1418774c98d8:1-13
1 row in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)step 5:关库
# mysqladmin -uroot shutdown --socket=/data/mysql.sock -pstep 6:保留旧数据目录
# mv ali_data ali_data_bakstep 7:准备全备
# innobackupex --apply-log --redo-only /home/ssd/ali_backup/full_xtra_3306_20160826/step 8:准备1级备份
# innobackupex --apply-log --redo-only /home/ssd/ali_backup/full_xtra_3306_20160826/ --incremental-dir=/home/ssd/ali_backup/full_xtra_3306_20160826_i_01/step 9:准备2级备份
# innobackupex --apply-log --redo-only /home/ssd/ali_backup/full_xtra_3306_20160826/ --incremental-dir=/home/ssd/ali_backup/full_xtra_3306_20160826_i_02/step 10:再次准备全备
# innobackupex --apply-log /home/ssd/ali_backup/full_xtra_3306_20160826/step 11:恢复数据库
# innobackupex --defaults-file=/etc/my.cnf --copy-back /home/ssd/ali_backup/full_xtra_3306_20160826
# chown -R mysql:mysql ali_datastep 12:启动实例
# mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --datadir=/home/ssd/ali_data/ &mysql> select * from t;
+------+------+
| id | name |
+------+------+
| 1 | i1 |
| 2 | i2 |
| 3 | i3 |
+------+------+
3 rows in set (0.00 sec)step 13:查看备份Position
# more full_xtra_3306_20160826/xtrabackup_binlog_info
3306-mysql-bin.000003 933 87fd24be-683d-11e6-ba97-1418774c98d8:49-53,
cdf80832-6a74-11e6-890b-1418774c98d8:1-11step 14:查看binlog日志，确定start-position和stop-position
# mysqlbinlog -vv --base64-output=decode-rows 3306-mysql-bin.000003
...
SET @@SESSION.GTID_NEXT= 'cdf80832-6a74-11e6-890b-1418774c98d8:11'/*!*/;
# at 722
#160826 14:00:28 server id 201983306 end_log_pos 795 CRC32 0x98f6ac81 Query thread_id=27 exec_time=0 error_code=0
SET TIMESTAMP=1472191228/*!*/;
BEGIN
/*!*/;
# at 795
#160826 14:00:28 server id 201983306 end_log_pos 843 CRC32 0x98615829 Table_map: `miles`.`t` mapped to number 77
# at 843
#160826 14:00:28 server id 201983306 end_log_pos 902 CRC32 0x44246525 Write_rows: table id 77 flags: STMT_END_F
### INSERT INTO `miles`.`t`
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### @2='i1' /* VARSTRING(90) meta=90 nullable=1 is_null=0 */
### INSERT INTO `miles`.`t`
### SET
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### @2='i2' /* VARSTRING(90) meta=90 nullable=1 is_null=0 */
### INSERT INTO `miles`.`t`
### SET
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
### @2='i3' /* VARSTRING(90) meta=90 nullable=1 is_null=0 */
# at 902
#160826 14:00:28 server id 201983306 end_log_pos 933 CRC32 0x47fad9c0 Xid = 239
COMMIT/*!*/;
# at 933
#160826 14:08:31 server id 201983306 end_log_pos 981 CRC32 0xd5f3269b GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'cdf80832-6a74-11e6-890b-1418774c98d8:12'/*!*/;
# at 981
#160826 14:08:31 server id 201983306 end_log_pos 1054 CRC32 0x993def1d Query thread_id=32 exec_time=0 error_code=0
SET TIMESTAMP=1472191711/*!*/;
BEGIN
/*!*/;
# at 1054
#160826 14:08:31 server id 201983306 end_log_pos 1102 CRC32 0x5fe76bad Table_map: `miles`.`t` mapped to number 78
# at 1102
#160826 14:08:31 server id 201983306 end_log_pos 1145 CRC32 0x98128279 Delete_rows: table id 78 flags: STMT_END_F
### DELETE FROM `miles`.`t`
### WHERE
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### @2='i2' /* VARSTRING(90) meta=90 nullable=1 is_null=0 */
# at 1145
#160826 14:08:31 server id 201983306 end_log_pos 1176 CRC32 0xa2617fb2 Xid = 261
COMMIT/*!*/;
# at 1176
#160826 14:08:40 server id 201983306 end_log_pos 1224 CRC32 0x5cda357a GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'cdf80832-6a74-11e6-890b-1418774c98d8:13'/*!*/;
# at 1224
#160826 14:08:40 server id 201983306 end_log_pos 1319 CRC32 0x5de28a5d Query thread_id=32 exec_time=0 error_code=0
SET TIMESTAMP=1472191720/*!*/;
drop database miles
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
# at 1319
#160826 14:09:05 server id 201983306 end_log_pos 1371 CRC32 0xee498579 Rotate to 3306-mysql-bin.000004 pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;step 15:基于binlog日志恢复
# mysqlbinlog --start-position=933 --stop-position=1176 3306-mysql-bin.000003 | mysql -uroot -p --socket=/home/ssd/ali_data/my3306.sock查看恢复情况
mysql> select * from t;
+------+------+
| id | name |
+------+------+
| 1 | i1 |
| 3 | i3 |
+------+------+
2 rows in set (0.00 sec)step 16:确定数据无误后，重新做一份全备
# innobackupex --defaults-file=/etc/my.cnf --user root --password beijing --no-timestamp /home/ssd/ali_backup/full_xtra_3306_20160826_1时间： 2024-09-15 01:16:47