Xtrabackup Incremental backup solution, full Backup Recovery record

mysql> select * from mm;+------+| a    |+------+|    1 ||    2 ||    3 ||    4 |+------+4 rows in set (0.00 sec)mysql> show tables;+----------------+| Tables_in_inno |+----------------+| mm             || t              |+----------------+2 rows in set (0.01 sec)mysql> insert into mm select 9999;Query OK, 1 row affected (0.01 sec)Records: 1  Duplicates: 0  Warnings: 0mysql> insert into t select 9999;Query OK, 1 row affected (0.16 sec)Records: 1  Duplicates: 0  Warnings: 0mysql>mysql> select * from t;+------+| a    |+------+|    1 ||    2 ||    3 |+------+3 rows in set (0.00 sec)mysql> select * from mm;+------+| a    |+------+|    1 ||    2 ||    3 ||    4 || 9999 |+------+5 rows in set (0.00 sec)mysql> select * from t;+------+| a    |+------+|    1 ||    2 ||    3 || 9999 |+------+4 rows in set (0.00 sec)mysql> 

The above is the preparation of the relevant environment, the following is the start of Backup recovery related records;

 

Incremental backup and simulated recovery: perform a full backup first, incremental backup needs to be based on this [root @ slave02 Inno] # mkdir-P/data/back_up/Delta --- create an incremental backup folder [root @ slave02 Inno] # cd/data/back_up/ [root @ slave02 back_up] # lsdelta ibdata1 Inno using xtrabackup_logfile [root @ slave02 back_up] # cd Delta/[root @ slave02 Delta] # ls [root @ slave02 Delta] # [root @ slave02 Delta] # ls-latotal 16drwxr-xr-x 2 root Root 4096 Mar 27. drwxr-XR-x 4 Root 40 96 Mar 27 .. [root @ slave02 Delta] # [root @ slave02 Delta] # xtrabackup_55 -- defaults-file =/etc/My. CNF -- backup -- target-Dir =/data/back_up/Delta -- incremental-basedir =/data/back_up xtrabackup_55 version 1.6.2 for MySQL Server 5.5.10 Linux (i686) (Revision ID: Undefined) incremental backup from 893330211 is enabled. xtrabackup: Uses posix_fadvise (). xtrabackup: CD to/usr/local/MySQL/dataxtrabackup: Target instance is assumed as followings. xtrabackup: innodb_data_home_dir =. /xtrabackup: innodb_data_file_path = ibdata1: 10 m: autoextendxtrabackup: innodb_log_group_home_dir =. /xtrabackup: groups = 2 xtrabackup: innodb_log_file_size = 5242880130327 14:38:04 InnoDB: Using Linux native aio130327 14:38:04 InnoDB: Warning: allocated tablespace 2, old maximum was 0> log scanned up t O (893330821) [01] copying. /ibdata1 to/data/back_up/Delta/ibdata1.delta [01]... done [01] copying. /Inno/mm. IBD to/data/back_up/Delta/Inno/mm. IBD. delta [01]... done [01] copying. /Inno/t. IBD to/data/back_up/Delta/Inno/t. IBD. delta [01]... done> log scanned up to (893330821) xtrabackup: The latest check point (for incremental): '000000'> log scanned up to (893330821) xtrabackup: Stopping log copyin G thread. xtrabackup: transaction log of lsn (893330821) to (893330821) was copied. [root @ slave02 Delta] # [root @ slave02 Delta] # ls/data/back_up/Delta/-generated Incremental Backup Data File ibdata1.delta ibdata1.meta Inno xtrabackup_checkpoints xtrabackup_logfile [root @ slave02 Delta] # [root @ slave02 Delta] # cd Inno/[root @ slave02 Inno] # lsmm. IBD. delta mm. IBD. meta T. IBD. delta T. IBD. meta [root @ slave02 Inno] # lltotal 88-rw-r -- r -- 1 Root 32768 Mar 27mm. IBD. delta-RW-r -- 1 Root 18 Mar 27mm. IBD. meta-RW-r -- 1 Root 32768 Mar 27 T. IBD. delta-RW-r -- 1 Root 18 Mar 27 T. IBD. meta [root @ slave02 Inno] # Note: In the Incremental Backup Directory, all data files are. at the end of delta. Incremental Backup only backs up the page that was modified after the last full backup. Therefore, Incremental Backup only has a few blank spaces. Incremental Backup can be incremental Based on Incremental backup. The Incremental Backup Directory needs to be modified each time. For example, if the second increment is changed to/data/back_up/delta2/) backup and recovery, the incremental and full data can be prepare once; full prepare; [root @ slave02 Delta] # xtrabackup_55 -- defaults-file =/etc/My. CNF -- prepare -- target-Dir =/data/back_upxtrabackup_55 version 1.6.2 for MySQL Server 5.5.10 Linux (i686) (Revision ID: Undefined) xtrabackup: CD to/data/back_upxtrabackup: this target seems to be not prepared yet. xtrabackup: xtrabackup_logfile detected: size = 2097152, St Art_lsn = (893330211) xtrabackup: Temporary instance for recovery is set as followings. xtrabackup: innodb_data_home_dir =. /xtrabackup: innodb_data_file_path = ibdata1: 10 m: autoextendxtrabackup: innodb_log_group_home_dir =. /xtrabackup: innodb_log_files_in_group = 1 xtrabackup: innodb_log_file_size = 2097152130327 14:40:53 InnoDB: Using Linux native aioxtrabackup: Starting InnoDB instance for recovery. Xtrabackup: Using 104857600 bytes for Buffer Pool (set by -- use-memory parameter) 130327 14:40:53 InnoDB: the InnoDB memory heap is disabled130327 14:40:53 InnoDB: mutexes and rw_locks use InnoDB's own implementation130327 14:40:53 InnoDB: Compressed tables use zlib 1.2.3130327 14:40:53 InnoDB: Using Linux native aio130327 14:40:53 InnoDB: Warning is deprecated. please use I Parameters and innodb_write_io_threads instead130327 14:40:53 InnoDB: initializing buffer pool, size = 10020.m130327 14:40:53 InnoDB: Completed initialization of buffer pool130327 14:40:53 InnoDB: highest supported file format is barracuda. innoDB: the log sequence number in ibdata files does not matchinnodb: the log sequence number in the ib_logfiles! 130327 14:40:53 InnoDB: database was not shut down normally! InnoDB: Starting crash recovery. innoDB: Reading tablespace information from. IBD files... innoDB: Last MySQL BINLOG file position 0 1747, file name. /mysql-bin.000004130327 14:40:54 InnoDB: waiting for the background threads to start130327 14:40:55 InnoDB: 1.1.5 started; log sequence number 893330211 [Notice (again)] if you use binary log and don't use any hack of group commit, the binary log POS Ition seems to be: InnoDB: Last MySQL BINLOG file position 0 1747, file name. /mysql-bin.000004xtrabackup: Starting shutdown with innodb_fast_shutdown = 1130327 14:40:55 InnoDB: Starting shutdown... 130327 14:40:56 InnoDB: shutdown completed; log sequence number 893330449 [root @ slave02 Delta] # incremental prepare; [root @ slave02 Delta] # xtrabackup_55 -- defaults-file =/etc/My. CNF -- prepare -- target-Dir =/data/back _ Up -- incremental-Dir =/data/back_up/Delta/xtrabackup_55 version 1.6.2 for MySQL Server 5.5.10 Linux (i686) (Revision ID: Undefined) Incremental backup from 893330211 is enabled. xtrabackup: CD to/data/back_upxtrabackup: This target seems to be already prepared. xtrabackup: xtrabackup_logfile detected: size = 2097152, start_lsn = (893330821) xtrabackup: page size for/data/back_up/Delta // ibdata1.delta is 16384 bytesapplying/data/back_up/Delta // ibdata1.delta... xtrabackup: page size for/data/back_up/Delta // Inno/mm. IBD. delta is 16384 bytesapplying/data/back_up/Delta // Inno/mm. IBD. delta... xtrabackup: page size for/data/back_up/Delta // Inno/t. IBD. delta is 16384 bytesapplying/data/back_up/Delta // Inno/t. IBD. delta... xtrabackup: Temporary instance for recovery is set as followings. xtrabackup: InnoDB _ Data_home_dir =. /xtrabackup: Rule = ibdata1: 10 m: rule: Rule =/data/back_up/Delta/xtrabackup: Rule = 1 xtrabackup: Rule = 2097152130327 14:42:49 InnoDB: Using Linux native aioxtrabackup: starting InnoDB instance for recovery. xtrabackup: Using 104857600 bytes for Buffer Pool (set by -- use-memory parameter) 1303 27 14:42:49 InnoDB: the InnoDB memory heap is busy 14:42:49 InnoDB: mutexes and rw_locks use InnoDB's own restart 14:42:49 InnoDB: Compressed tables use zlib restart 14:42:49 InnoDB: Using Linux native aio130327 14:42:49 InnoDB: warning: innodb_file_io_threads is deprecated. please use innodb_read_io_threads and innodb_write_io_threads instead130327 14:42:49 InnoDB: init Ializing buffer pool, size = 100w.m130327 14:42:49 InnoDB: Completed initialization of buffer pool130327 14:42:49 InnoDB: highest supported file format is barracuda. innoDB: the log sequence number in ibdata files does not matchinnodb: the log sequence number in the ib_logfiles! 130327 14:42:49 InnoDB: database was not shut down normally! InnoDB: Starting crash recovery. innoDB: Reading tablespace information from. IBD files... innoDB: Last MySQL BINLOG file position 0 474, file name. /mysql-bin.000005130327 14:42:49 InnoDB: waiting for the background threads to start130327 14:42:50 InnoDB: 1.1.5 started; log sequence number 893330821 [Notice (again)] if you use binary log and don't use any hack of group commit, the binary log posi Tion seems to be: InnoDB: Last MySQL BINLOG file position 0 474, file name. /mysql-bin.000005xtrabackup: Starting shutdown with innodb_fast_shutdown = 1130327 14:42:50 InnoDB: Starting shutdown... 130327 14:42:51 InnoDB: shutdown completed; log sequence number 893330821 [root @ slave02 Delta] # [root @ slave02 data] # rm-RF Inno/---- delete all files in the Inno database in the data directory [root @ slave02 data] # lsbook MySQL mysql-bin.000005 Prod Ibdata1 mysql-bin.000001 mysql-bin.index mysql-bin.000002 slave02.errib _ logfile0 mysql-bin.000003 mysqld_multi.log slave02.pidib _ logfile1 MySQL. PID slow. loglog.1 mysql-bin.000004 performance_schema test [root @ slave02 data] # cp-r/data/back_up/Inno // usr/local/MySQL/data/--- copy full backup files from the Backup Directory go to the database directory to [root @ slave02 data] # cd Inno/[root @ slave02 Inno] # lltotal 2096-rw-r ----- 1 Root 8554 Mar 27 Mm. FRM-RW-r -- 1 Root 1048576 Mar 27mm. IBD-RW-r ----- 1 Root 8554 Mar 27 T. FRM-RW-r -- 1 Root 1048576 Mar 27 T. IBD [root @ slave02 Inno] # chown-r MYSQL: mySQL/usr/local/MySQL/data/Inno/---- the process for modifying permissions is the same as that for full backup [root @ slave02 Inno] # ls-latotal 2112drwx ------ 2 MySQL 4096 Mar 27. drwxr-XR-x 8 MySQL 4096 Mar 27 .. -RW-r ----- 1 MySQL 8554 MAR 27mm. FRM-RW-r -- 1 MySQL 1048576 Mar 27mm. IBD-RW-r ----- 1 MySQL 8554 Mar 27 T. FRM-RW-r -- 1 MySQL 1048576 Mar 27 T. IBD [root @ slave02 Inno] # service mysqld restart ----- restart the database and shutting down MySQL... [OK] Starting MySQL .. [OK] [root @ slave02 Inno] # mysql-uroot-prootwelcome to the MySQL monitor. commands end with; or \ G. your MySQL connection ID is 1 server version: 5.5.13-log source distributioncopyright (c) 2000,201 0, Oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. other names may be trademarks of their respectiveowners. type 'help; 'or' \ H' for help. type '\ C' to clear the current input statement. mysql> Use innodatabase changedmysql> show tables; --- whether serious Incremental backup data exists Effect + ---------------- + | tables_in_inno | + ---------------- + | mm | T | + ---------------- + 2 rows in SET (0.00 Sec) mysql> select * From mm; + ------ + | A | + ------ + | 1 | 2 | 3 | 4 | 9999 | + ------ + 5 rows in SET (0.01 Sec) mysql> select * from t; + ------ + | A | + ------ + | 1 | 2 | 3 | 9999 | + ------ + 4 rows in SET (0.00 Sec) mysql> ------- after verification, Incremental backup is successful. Incremental Backup advantages: 1. The database is too large and there is not enough space for full backup. This effectively saves space and is efficient. 2. Supports hot backup. The table is not locked during the backup process and is not subject to time restrictions. 3. Daily backup only generates a small amount of data, making remote backup and transmission easier. Saves space at the same time. 4. Backup recovery is based on file operations to reduce the risks of direct database operations. 5. Higher backup efficiency and higher recovery efficiency. 6. The current Incremental Backup can be performed through a channel much faster than the previous version. You can use it. Currently, we have tested that 12 channels can be activated in production, over GB of data files can be quickly added for backup