Xtrabackup Incremental backup solution, full Backup Recovery record

Xtrabackup Incremental backup solution, recording the entire Backup Recovery Process [html] 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: 0 mysql> insert T select 9999; Query OK, 1 row affected (0.16 sec) Records: 1 Duplicates: 0 Warnings: 0 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 ro Ws 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; [html] Incremental backup and recovery simulation steps: do 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] # ls delta ibdata1 inno ready xtrabackup_logfile [root @ Slave02 back_up] # cd delta/[root @ Slave02 delta] # ls [root @ Slave02 delta] # [root @ Slave02 delta] # ls-la tota L 16 drwxr-xr-x 2 root 4096 Mar 27. drwxr-xr-x 4 root 4096 Mar 27 .. [root @ Slave02 delta] # [root @ Slave02 delta] # xtrabackup_55 -- defaults-file =/etc/my. cnf -- backup -- tar get-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. xtrabac Kup: uses posix_fadvise (). xtrabackup: cd to/usr/local/mysql/data xtrabackup: Target instance is assumed as followings. xtrabackup: innodb_data_home_dir =. /xtrabackup: innodb_data_file_path = ibdata1: 10 M: autoextend xtrabackup: innodb_log_group_home_dir =. /xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 5242880 130327 14:38:04 InnoDB: Using Linux native AIO 130327 14:38:04 InnoDB: Warning: allocated tablespace 2, old maximum was 0> log scanned up to (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 poin T (for incremental): '000000'> log scanned up to (893330821) xtrabackup: Stopping log copying 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 @ Slave 02 inno] # ls mm. ibd. delta mm. ibd. meta t. ibd. delta t. ibd. meta [root @ Slave02 inno] # ll total 88-rw-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_up xtrabackup_55 version 1.6.2 for MySQL server 5.5.10 Linux (i686) (revision id: undefined) xtrabackup: cd to/data/back_up xtrabackup: this target seems to be not prepared yet. xtrabackup: xtrabackup_logfile detected: size = 20971 52, start_lsn = (893330211) xtrabackup: Temporary instance for recovery is set as followings. xtrabackup: innodb_data_home_dir =. /xtrabackup: innodb_data_file_path = ibdata1: 10 M: autoextend xtrabackup: innodb_log_group_home_dir =. /xtrabackup: innodb_log_files_in_group = 1 xtrabackup: innodb_log_file_size = 2097152 130327 14:40:53 InnoDB: Using Linux native AIO xtrabackup: 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 disabled 130327 14:40:53 InnoDB: mutexes and rw_locks use InnoDB's own implementation 130327 14:40:53 InnoDB: Compressed tables use zlib 1.2.3 130327 14:40:53 InnoDB: Using Linux native AIO 130327 14:40:53 InnoDB: Warning: innodb_file_io_threads is depr Ecated. please use cases and innodb_write_io_threads instead 130327 14:40:53 InnoDB: Initializing buffer pool, size = 100.0 M 130327 14:40:53 InnoDB: Completed initialization of buffer pool 130327 14:40:53 InnoDB: highest supported file format is Barracuda. innoDB: The log sequence number in ibdata files does not match InnoDB: 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.000004 130327 14:40:54 InnoDB: Waiting for the background threads to start 130327 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 l Og position seems to be: InnoDB: Last MySQL binlog file position 0 1747, file name. /mysql-bin.000004 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 130327 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-di R =/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_up xtrabackup: 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 bytes Applying/data/back_up/delta // ibdata1.delta... xtrabackup: page size for/data/back_up/delta // inno/mm. ibd. delta is 16384 bytes Applying/data/back_up/delta // inno/mm. ibd. delta... xtrabackup: page size for/data/back_up/delta // inno/t. ibd. delta is 16384 bytes Applying/data/back_up/delta // inno/t. ibd. delta... xtrabackup: Temporary instance for recovery is set as follow Ings. xtrabackup: innodb_data_home_dir =. /xtrabackup: innodb_data_file_path = ibdata1: 10 M: autoextend xtrabackup: groups =/data/back_up/delta/xtrabackup: records = 1 xtrabackup: records = 2097152 130327 14:42:49 InnoDB: using Linux native AIO xtrabackup: Starting InnoDB instance for recovery. xtrabackup: Using 104857600 bytes for buffer pool (se T by -- use-memory parameter) 130327 14:42:49 InnoDB: The InnoDB memory heap is disabled 130327 14:42:49 InnoDB: Mutexes and rw_locks use InnoDB's own implementation 130327 14:42:49 InnoDB: compressed tables use zlib 1.2.3 130327 14:42:49 InnoDB: Using Linux native AIO 130327 14:42:49 InnoDB: Warning: innodb_file_io_threads is deprecated. please use innodb_read_io_threads and innodb_write_io_thread S instead 130327 14:42:49 InnoDB: Initializing buffer pool, size = 100.0 M 130327 14:42:49 InnoDB: Completed initialization of buffer pool 130327 14:42:49 InnoDB: highest supported file format is Barracuda. innoDB: The log sequence number in ibdata files does not match InnoDB: 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.000005 130327 14:42:49 InnoDB: Waiting for the background threads to start 130327 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 lo G position seems to be: InnoDB: Last MySQL binlog file position 0 474, file name. /mysql-bin.000005 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 130327 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] # ls book mysql-B In.20.05 prod ibdata1 mysql-bin.000001 mysql-bin.index Slave02.err ib_logfile0 mysql-bin.000002 mysqld_multi.log Slave02.pid ib_logfile1 mysql-bin.000003 mysql. pid slow. log log.1 mysql-bin.000004 performance_schema test [root @ Slave02 data] # cp-r/data/back_up/inno // usr/local/mysql/data/--- copy full backup from the Backup Directory file in the database directory to [root @ Slave02 data] # cd inno/[root @ Slave02 inno] # ll total 2096-rw-r ----- 1 root Root 8554 Mar 27mm. 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-la total 2112 drwx ------ 2 mysql 4096 mar 27. drwxr-xr-x 8 mysql 4096 Mar 27 .. -r W-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-proot Welcome to the MySQL monitor. commands end w Ith; or \ g. your MySQL connection id is 1 Server version: 5.5.13-log Source distribution Copyright (c) 2000,201 0, Oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of Oracle Corporation and/or its affiliates. other names may be trademarks of their respective owners. type 'help; 'or' \ H' for help. type '\ C' to clear the current input statement. mysql> use inno Da Tabase changed mysql> show tables; --- whether serious Incremental backup data is valid + ---------------- + | 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 to have enough space for full backup, which effectively saves space and improves efficiency. 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