MYSQL 增量恢复
祖仙教小凡仙 海鲨数据库架构师
增量备份两种方式 假设周一是基于base的备份，周二是基于base的incr1备份，周三是基于incr1的incr2备份，在恢复数据库的时候，需要使用base，incr1，incr2三个备份都存在时，才能进行完整的恢复，每个备份的from_lsn都是基于上一个备份的to_lsn，所以缺一不可。
基于incr1的incr2备份
xtrabackup --defaults-file=/home/mysql8.0.16/mysql/my.cnf --socket=/tmp/mysql.sock --host='192.168.11.253' --user='aa' --password='aa' --port=3307 --backup --target-dir=/home/zqdba/20200324backup/incr2 --incremental-basedir=/home/zqdba/20200324backup/incr1
假设周一是基于base的备份，周二是基于base的incr1备份，周三是基于base的incr2备份，在恢复数据库的时候，需要使用base和incr1，incr2两个备份中的其中一个，才能进行完整的恢复，因为incr1和incr2的from_lsn都是基于base备份中的to_lsn，所以恢复数据库时，只需要base和任意一个基于base的增量备份。
基于base的incr2备份
xtrabackup --defaults-file=/home/mysql8.0.16/mysql/my.cnf --socket=/tmp/mysql.sock --host='192.168.11.253' --user='aa' --password='aa' --port=3307 --backup --target-dir=/home/zqdba/20200324backup/incr2 --incremental-basedir=/home/zqdba/20200324backup/incr1
很显然在ORACLE里面第一种叫做差异备份， 第二种叫做覆盖备份
基于方式一的恢复： 3.1准备好备份片 MYSQL xtrabackup 增量备份 差异
xtrabackup --defaults-file=/u01/mysql/mysql8020debug/my.cnf --user=root --password=123456 --backup --target-dir=/u01/mysql/mysql8020debug/backup_increment --incremental-basedir=/u01/mysql/mysql8020debug/backup
xtrabackup --defaults-file=/u01/mysql/mysql8020debug/my.cnf --user=root --password=123456 --backup --target-dir=/u01/mysql/mysql8020debug/backup_incr2 --incremental-basedir=/u01/mysql/mysql8020debug/backup_increment
3.2执行恢复命令 1 先对全备应用日志
xtrabackup --prepare --apply-log-only --target-dir=/u01/mysql/mysql8020debug/backup
第一次备份目录下不能包含其他备份文件
xtrabackup --prepare --apply-log-only --target-dir=/u01/mysql/mysql8020debug/backup Multiple files found for the same tablespace ID: Tablespace ID: 1 = ['Full_mysql8020_2021-01-28-23-51-49/sys/sys_config.ibd', 'sys/sys_config.ibd'] Tablespace ID: 3 = ['Full_mysql8020_2021-01-28-23-51-49/bookstore/books.ibd', 'bookstore/books.ibd'] Tablespace ID: 4 = ['Full_mysql8020_2021-01-28-23-51-49/bookstore/books2.ibd', 'bookstore/books2.ibd'] Tablespace ID: 4294967278 = ['Full_mysql8020_2021-01-28-23-51-49/undo_002', 'undo_002'] Tablespace ID: 4294967279 = ['Full_mysql8020_2021-01-28-23-51-49/undo_001', 'undo_001'] Tablespace ID: 4294967294 = ['Full_mysql8020_2021-01-28-23-51-49/mysql.ibd', 'mysql.ibd'] Plugin initialization aborted with error Failed, retry may succeed. xtrabackup: innodb_init(): Error occured.
移走就OK！ [root@localhost backup]# mv Full_mysql8020_2021-01-28-23-51-49/
Apply batch completed! Using undo tablespace './undo_001'. Using undo tablespace './undo_002'. Opened 2 existing undo tablespaces. GTID recovery trx_no: 10769 Creating shared tablespace for temporary tables Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... File './ibtmp1' size is now 12 MB. Scanning temp tablespace dir:'./#innodb_temp/' Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active. 8.0.21 started; log sequence number 18216088 Allocated tablespace ID 3 for bookstore/books, old maximum was 0 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 FTS optimize thread exiting. Starting shutdown... Log background threads are being closed... Shutdown completed; log sequence number 18216088 Number of pools: 1 210209 04:24:50 completed OK!
2 对增量1 应用日志
xtrabackup --prepare --apply-log-only --target-dir=/u01/mysql/mysql8020debug/backup/ --incremental-dir=/u01/mysql/mysql8020debug/backup_increment
进入主备份目录执行
[root@localhost backup]# xtrabackup --prepare --apply-log-only --target-dir=/u01/mysql/mysql8020debug/backup/ --incremental-dir=/u01/mysql/mysql8020debug/backup_increment xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0 xtrabackup: recognized client arguments: --prepare=1 --apply-log-only=1 --target-dir=/u01/mysql/mysql8020debug/backup/ --incremental-dir=/u01/mysql/mysql8020debug/backup_increment xtrabackup version 8.0.14 based on MySQL server 8.0.21 Linux (x86_64) (revision id: 113f3d7) incremental backup from 18216058 is enabled. xtrabackup: cd to /u01/mysql/mysql8020debug/backup/ xtrabackup: This target seems to be already prepared with --apply-log-only. Number of pools: 1 xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(18264119) xtrabackup: using the following InnoDB configuration for recovery: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend xtrabackup: innodb_log_group_home_dir = /u01/mysql/mysql8020debug/backup_increment/ xtrabackup: innodb_log_files_in_group = 1 xtrabackup: innodb_log_file_size = 8388608 xtrabackup: Generating a list of tablespaces xtrabackup: Generating a list of tablespaces Scanning './' Completed space ID check of 2 files. Allocated tablespace ID 1 for sys/sys_config, old maximum was 0 Using undo tablespace './undo_001'. Using undo tablespace './undo_002'. Opened 2 existing undo tablespaces. xtrabackup: page size for /u01/mysql/mysql8020debug/backup_increment//mysql.ibd.delta is 16384 bytes Applying /u01/mysql/mysql8020debug/backup_increment//mysql.ibd.delta to ./mysql.ibd... xtrabackup: page size for /u01/mysql/mysql8020debug/backup_increment//bookstore/books.ibd.delta is 16384 bytes Applying /u01/mysql/mysql8020debug/backup_increment//bookstore/books.ibd.delta to ./bookstore/books.ibd... xtrabackup: page size for /u01/mysql/mysql8020debug/backup_increment//bookstore/books3.ibd.delta is 16384 bytes Applying /u01/mysql/mysql8020debug/backup_increment//bookstore/books3.ibd.delta to ./bookstore/books3.ibd... xtrabackup: page size for /u01/mysql/mysql8020debug/backup_increment//bookstore/books2.ibd.delta is 16384 bytes Applying /u01/mysql/mysql8020debug/backup_increment//bookstore/books2.ibd.delta to ./bookstore/books2.ibd... xtrabackup: page size for /u01/mysql/mysql8020debug/backup_increment//undo_001.delta is 16384 bytes Applying /u01/mysql/mysql8020debug/backup_increment//undo_001.delta to ./undo_001... xtrabackup: page size for /u01/mysql/mysql8020debug/backup_increment//sys/sys_config.ibd.delta is 16384 bytes Applying /u01/mysql/mysql8020debug/backup_increment//sys/sys_config.ibd.delta to ./sys/sys_config.ibd... xtrabackup: page size for /u01/mysql/mysql8020debug/backup_increment//undo_002.delta is 16384 bytes Applying /u01/mysql/mysql8020debug/backup_increment//undo_002.delta to ./undo_002... xtrabackup: page size for /u01/mysql/mysql8020debug/backup_increment//ibdata1.delta is 16384 bytes Applying /u01/mysql/mysql8020debug/backup_increment//ibdata1.delta to ./ibdata1... xtrabackup: using the following InnoDB configuration for recovery: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend xtrabackup: innodb_log_group_home_dir = /u01/mysql/mysql8020debug/backup_increment/ xtrabackup: innodb_log_files_in_group = 1 xtrabackup: innodb_log_file_size = 8388608 xtrabackup: Starting InnoDB instance for recovery. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) PUNCH HOLE support available Mutexes and rw_locks use GCC atomic builtins Uses event mutexes GCC builtin __atomic_thread_fence() is used for memory barrier Compressed tables use zlib 1.2.7 Number of pools: 1 Not using CPU crc32 instructions Directories to scan './' Scanning './' Completed space ID check of 7 files. Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M Completed initialization of buffer pool page_cleaner coordinator priority: -20 page_cleaner worker priority: -20 page_cleaner worker priority: -20 page_cleaner worker priority: -20 The log sequence number 18216088 in the system tablespace does not match the log sequence number 18264119 in the ib_logfiles! Database was not shutdown normally! Starting crash recovery. Starting to parse redo log at lsn = 18264109, whereas checkpoint_lsn = 18264119 Doing recovery: scanned up to log sequence number 18264149 Log background threads are being started... Applying a batch of 1 redo log records ... 100% Apply batch completed! Using undo tablespace './undo_001'. Using undo tablespace './undo_002'. Opened 2 existing undo tablespaces. GTID recovery trx_no: 11294 Removed temporary tablespace data file: "ibtmp1" Creating shared tablespace for temporary tables Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... File './ibtmp1' size is now 12 MB. Scanning temp tablespace dir:'./#innodb_temp/' Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active. 8.0.21 started; log sequence number 18264149 Allocated tablespace ID 5 for bookstore/books3, old maximum was 0 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 FTS optimize thread exiting. Starting shutdown... Log background threads are being closed... Shutdown completed; log sequence number 18264149 Number of pools: 1 210209 04:29:57 [01] Copying /u01/mysql/mysql8020debug/backup_increment//mysql/slow_log_203.sdi to ./mysql/slow_log_203.sdi 210209 04:29:57 [01] ...done 210209 04:29:57 [01] Copying /u01/mysql/mysql8020debug/backup_increment//mysql/slow_log.CSM to ./mysql/slow_log.CSM 210209 04:29:57 [01] ...done 210209 04:29:57 [01] Copying /u01/mysql/mysql8020debug/backup_increment//mysql/general_log_202.sdi to ./mysql/general_log_202.sdi 210209 04:29:57 [01] ...done 21020//performance_schema/persisted_variab_178.sdi to ./performance_schema/persisted_variab_178.sdi 210209 04:29:58 [01] ...done 21 210209 04:29:58 [01] Copying /u01/mysql/mysql8020debug/backup_increment//performance_schema/events_statement_115.sdi to ./performance_schema/events_statement_115.sdi 210209 04:29:58 [01] ...done 210209 04:29:58 [01] Copying /u01/mysql/mysql8020debug/backup_increment//performance_schema/table_handles_151.sdi to ./performance_schema/table_handles_151.sdi 210209 04:29:58 [01] ...done 210209 04:29:58 [01] Copying /u01/mysql/mysql8020debug/backup_increment//performance_schema/prepared_stateme_166.sdi to ./performance_schema/prepared_stateme_166.sdi 2ent//performance_schema/file_summary_by__88.sdi to ./performance_schema/file_summary_by__88.sdi 210209 04:29:58 [01] ...done 210209 04:29:58 [01] Copying /u01/mysql/mysql8020debug/backup_increment//performance_schema/events_statement_116.sdi to ./performance_schema/events_statement_116.sdi 210209 04:29:58 [01] ...done 210209 04:29:58 [01] Copying /u01/mysql/mysql8020debug/backup_increment//performance_schema/metadata_locks_152.sdi to ./performance_schema/metadata_locks_152.sdi 210 2102 210209 04:29:58 [00] Copying /u01/mysql/mysql8020debug/backup_increment/binlog.index to ./binlog.index 210209 04:29:58 [00] ...done 210209 04:29:58 completed OK!
3 对增量2 应用日志
xtrabackup --prepare --apply-log-only --target-dir=/u01/mysql/mysql8020debug/backup/ --incremental-dir=/u01/mysql/mysql8020debug/backup_incr2
[root@localhost backup]# xtrabackup --prepare --apply-log-only --target-dir=/u01/mysql/mysql8020debug/backup/ --incremental-dir=/u01/mysql/mysql8020debug/backup_incr2 xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0 xtrabackup: recognized client arguments: --prepare=1 --apply-log-only=1 --target-dir=/u01/mysql/mysql8020debug/backup/ --incremental-dir=/u01/mysql/mysql8020debug/backup_incr2 xtrabackup version 8.0.14 based on MySQL server 8.0.21 Linux (x86_64) (revision id: 113f3d7) incremental backup from 18264119 is enabled. xtrabackup: cd to /u01/mysql/mysql8020debug/backup/ xtrabackup: This target seems to be already prepared with --apply-log-only. Number of pools: 1 xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(18284362) xtrabackup: using the following InnoDB configuration for recovery: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend xtrabackup: innodb_log_group_home_dir = /u01/mysql/mysql8020debug/backup_incr2/ xtrabackup: innodb_log_files_in_group = 1 xtrabackup: innodb_log_file_size = 8388608 xtrabackup: Generating a list of tablespaces xtrabackup: Generating a list of tablespaces Scanning './' Completed space ID check of 2 files. Allocated tablespace ID 1 for sys/sys_config, old maximum was 0 Using undo tablespace './undo_001'. Using undo tablespace './undo_002'. Opened 2 existing undo tablespaces. xtrabackup: page size for /u01/mysql/mysql8020debug/backup_incr2//mysql.ibd.delta is 16384 bytes Applying /u01/mysql/mysql8020debug/backup_incr2//mysql.ibd.delta to ./mysql.ibd... xtrabackup: page size for /u01/mysql/mysql8020debug/backup_incr2//bookstore/books.ibd.delta is 16384 bytes Applying /u01/mysql/mysql8020debug/backup_incr2//bookstore/books.ibd.delta to ./bookstore/books.ibd... xtrabackup: page size for /u01/mysql/mysql8020debug/backup_incr2//bookstore/books3.ibd.delta is 16384 bytes Applying /u01/mysql/mysql8020debug/backup_incr2//bookstore/books3.ibd.delta to ./bookstore/books3.ibd... xtrabackup: page size for /u01/mysql/mysql8020debug/backup_incr2//bookstore/books2.ibd.delta is 16384 bytes Applying /u01/mysql/mysql8020debug/backup_incr2//bookstore/books2.ibd.delta to ./bookstore/books2.ibd... xtrabackup: page size for /u01/mysql/mysql8020debug/backup_incr2//bookstore/books4.ibd.delta is 16384 bytes Applying /u01/mysql/mysql8020debug/backup_incr2//bookstore/books4.ibd.delta to ./bookstore/books4.ibd... xtrabackup: page size for /u01/mysql/mysql8020debug/backup_incr2//undo_001.delta is 16384 bytes Applying /u01/mysql/mysql8020debug/backup_incr2//undo_001.delta to ./undo_001... xtrabackup: page size for /u01/mysql/mysql8020debug/backup_incr2//sys/sys_config.ibd.delta is 16384 bytes Applying /u01/mysql/mysql8020debug/backup_incr2//sys/sys_config.ibd.delta to ./sys/sys_config.ibd... xtrabackup: page size for /u01/mysql/mysql8020debug/backup_incr2//undo_002.delta is 16384 bytes Applying /u01/mysql/mysql8020debug/backup_incr2//undo_002.delta to ./undo_002... xtrabackup: page size for /u01/mysql/mysql8020debug/backup_incr2//ibdata1.delta is 16384 bytes Applying /u01/mysql/mysql8020debug/backup_incr2//ibdata1.delta to ./ibdata1... xtrabackup: using the following InnoDB configuration for recovery: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend xtrabackup: innodb_log_group_home_dir = /u01/mysql/mysql8020debug/backup_incr2/ xtrabackup: innodb_log_files_in_group = 1 xtrabackup: innodb_log_file_size = 8388608 xtrabackup: Starting InnoDB instance for recovery. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) PUNCH HOLE support available Mutexes and rw_locks use GCC atomic builtins Uses event mutexes GCC builtin __atomic_thread_fence() is used for memory barrier Compressed tables use zlib 1.2.7 Number of pools: 1 Not using CPU crc32 instructions Directories to scan './' Scanning './' Completed space ID check of 8 files. Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M Completed initialization of buffer pool page_cleaner coordinator priority: -20 page_cleaner worker priority: -20 page_cleaner worker priority: -20 page_cleaner worker priority: -20 The log sequence number 18264149 in the system tablespace does not match the log sequence number 18284362 in the ib_logfiles! Database was not shutdown normally! Starting crash recovery. Starting to parse redo log at lsn = 18284082, whereas checkpoint_lsn = 18284362 Doing recovery: scanned up to log sequence number 18284392 Log background threads are being started... Applying a batch of 1 redo log records ... 100% Apply batch completed! Using undo tablespace './undo_001'. Using undo tablespace './undo_002'. Opened 2 existing undo tablespaces. GTID recovery trx_no: 11313 Removed temporary tablespace data file: "ibtmp1" Creating shared tablespace for temporary tables Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... File './ibtmp1' size is now 12 MB. Scanning temp tablespace dir:'./#innodb_temp/' Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active. 8.0.21 started; log sequence number 18284392 Allocated tablespace ID 5 for bookstore/books3, old maximum was 0 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 FTS optimize thread exiting. Starting shutdown... Log background threads are being closed... Shutdown completed; log sequence number 18284392 Number of pools: 1 210209 04:33:40 [01] Copying /u01/mysql/mysql8020debug/backup_incr2//mysql/slow_log_203.sdi to ./mysql/slow_log_203.sdi 210209 04:33:40 [01] ...done 210209 04:33:40 [01] Copying /u01/mysql/mysql8020debug/backup_incr2//mysql/slow_log.CSM to ./mysql/slow_log.CSM 210209 04:33:40 [00] Copying /u01/mysql/mysql8020debug/backup_incr2//xtrabackup_tablespaces to ./xtrabackup_tablespaces 210209 04:33:40 [00] ...done 210209 04:33:40 [00] Copying /u01/mysql/mysql8020debug/backup_incr2/binlog.000031 to ./binlog.000031 210209 04:33:40 [00] ...done 210209 04:33:40 [00] Copying /u01/mysql/mysql8020debug/backup_incr2/binlog.index to ./binlog.index 210209 04:33:40 [00] ...done 210209 04:33:40 completed OK!
4 拷贝文件（前提目录为空）
[root@localhost ~]# xtrabackup --defaults-file=/u01/mysql/mysql8020debug/my.cnf --host=localhost --user=root --password=123456 --port=3306 --copy-back --target-dir=/u01/mysql/mysql8020debug/backup/
root@localhost ~]# xtrabackup --defaults-file=/u01/mysql/mysql8020debug/my.cnf --host=localhost --user=root --password=123456 --port=3306 --copy-back --target-dir=/u01/mysql/mysql8020debug/backup/ xtrabackup: recognized server arguments: --datadir=/u01/mysql/mysql8020debug/data xtrabackup: recognized client arguments: --port=3306 --socket=/tmp/mysql.sock --host=localhost --user=root --password=* --port=3306 --copy-back=1 --target-dir=/u01/mysql/mysql8020debug/backup/ xtrabackup version 8.0.14 based on MySQL server 8.0.21 Linux (x86_64) (revision id: 113f3d7) 210209 04:43:47 [01] Copying undo_001 to /u01/mysql/mysql8020debug/data/undo_001 210209 04:43:47 [01] ...done 210209 04:43:47 [01] Copying undo_002 to /u01/mysql/mysql8020debug/data/undo_002 210209 04:43:47 [01] ...done 210209 04:43:47 [01] Copying ibdata1 to /u01/mysql/mysql8020debug/data/ibdata1 210209 04:43:48 [01] ...done 210209 04:43:48 [01] Copying binlog.000031 to /u01/mysql/mysql8020debug/data/binlog.000031 210209 04:43:48 [01] ...done 210209 04:43:48 [01] Copying binlog.index to /u01/mysql/mysql8020debug/data/binlog.index 210209 04:43:48 [01] ...done 210209 04:43:48 [01] Copying ./xtrabackup_info to /u01/mysql/mysql8020debug/data/xtrabackup_info 210209 04:43:48 [01] ...done 210209 04:43:48 [01] Copying ./mysql/slow_log_203.sdi to /u01/mysql/mysql8020debug/data/mysql/slow_log_203.sdi 210209 04:43:48 [01] ...done 210209 04:43:48 [01] Copying ./mysql/slow_log.CSM to /u01/mysql/mysql8020debug/data/mysql/slow_log.CSM 210209 04:43:49 completed OK! [root@localhost ~]#
收尾修改目录权限 启动服务
[root@localhost mysql8020debug]# chown -R mysql:mysql /u01/mysql/mysql8020debug/data [root@localhost mysql8020debug]# chmod -R 755 /u01/mysql/mysql8020debug/data [root@localhost mysql8020debug]# service mysqld start Starting MySQL..... SUCCESS! [root@localhost ~]#
基于方式二的恢复 覆盖方式 不实验 1 也是先应用全备的日制 xtrabackup --prepare --apply-log-only --target-dir=/FULL_BACKUP
2 选择覆盖最后的增量应用日志 xtrabackup --prepare --target-dir=/FULL_BACKUP --incremental-dir=/incr2
3 拷贝文件 xtrabackup --defaults-file=/u01/mysql/mysql8020debug/my.cnf --host=localhost --user=root --password=123456 --port=3306 --copy-back --target-dir=/u01/mysql/mysql8020debug/backup/
3.3对data目录授权 chown -R mysql.mysql data/
Tips: --apply-log-only命令应该用在所有增量备份（除最后一次增量备份） ，这就是为什么恢复脚本中，最后一次的命令不包含--apply-log-only。即使--apply-log-only在最后一次增量备份时被使用，备份仍将是一致的，但在这种情况下，数据库会执行回滚的操作
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did163176