xtrabackup2.4 增量备份
创建备份用户
mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cret';
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
mysql> FLUSH PRIVILEGES;创建测试数据
创建数据
mysql> CREATE DATABASE test;
mysql> CREATE TABLE test.messages (message VARCHAR(250));
mysql> INSERT INTO test.messages VALUES ('hello');查询数据
mysql> select * from messages;
+---------+
| message |
+---------+
| hello |
+---------+
1 row in set (0.00 sec)mysql数据库目录
# ls -l /data/mysql/data/
total 110668
-rw-r----- 1 mysql mysql 56 Jan 12 13:40 auto.cnf
-rw------- 1 mysql mysql 1680 Jan 12 13:40 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 Jan 12 13:40 ca.pem
-rw-r--r-- 1 mysql mysql 1112 Jan 12 13:40 client-cert.pem
-rw------- 1 mysql mysql 1680 Jan 12 13:40 client-key.pem
-rw-r----- 1 mysql mysql 436 Jan 13 16:37 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Jan 13 16:37 ibdata1
-rw-r----- 1 mysql mysql 50331648 Jan 13 16:36 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Jan 12 19:22 ib_logfile1
drwxr-x--- 2 mysql mysql 4096 Jan 13 16:37 mysql
drwxr-x--- 2 mysql mysql 4096 Jan 13 16:37 performance_schema
-rw------- 1 mysql mysql 1680 Jan 12 13:40 private_key.pem
-rw-r--r-- 1 mysql mysql 452 Jan 12 13:40 public_key.pem
-rw-r--r-- 1 mysql mysql 1112 Jan 12 13:40 server-cert.pem
-rw------- 1 mysql mysql 1680 Jan 12 13:40 server-key.pem
drwxr-x--- 2 mysql mysql 12288 Jan 13 16:37 sys
drwxr-x--- 2 mysql mysql 4096 Jan 13 16:37 test全量备份数据
备份数据
# xtrabackup --backup --slave-info --host="localhost" --port=3306 --user="bkpuser" --password="s3cret" --socket="/data/mysql/mysql.sock" --parallel=2 --target-dir="/data/xtrabackup/base"查看备份信息
# ls -l /data/xtrabackup/base
total 12336
-rw-r----- 1 root root 487 Jan 12 18:29 backup-my.cnf
-rw-r----- 1 root root 436 Jan 12 18:29 ib_buffer_pool
-rw-r----- 1 root root 12582912 Jan 12 18:29 ibdata1
drwxr-x--- 2 root root 4096 Jan 12 18:29 mysql
drwxr-x--- 2 root root 4096 Jan 12 18:29 performance_schema
drwxr-x--- 2 root root 12288 Jan 12 18:29 sys
drwxr-x--- 2 root root 4096 Jan 12 18:29 test
-rw-r----- 1 root root 21 Jan 12 18:29 xtrabackup_binlog_info
-rw-r----- 1 root root 135 Jan 12 18:29 xtrabackup_checkpoints
-rw-r----- 1 root root 589 Jan 12 18:29 xtrabackup_info
-rw-r----- 1 root root 2560 Jan 12 18:29 xtrabackup_logfile
-rw-r----- 1 root root 0 Jan 12 18:29 xtrabackup_slave_info查看xtrabackup_checkpoints
# cat /data/xtrabackup/base/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 2768955
last_lsn = 2768964
compact = 0
recover_binlog_info = 0
flushed_lsn = 2768964增量备份数据
新增数据inc1
mysql> INSERT INTO test.messages VALUES ('inc1');
Query OK, 1 row affected (0.00 sec)
mysql> select * from messages;
+---------+
| message |
+---------+
| hello |
| inc1 |
+---------+
2 rows in set (0.00 sec)增量备份一
备份数据inc1
# xtrabackup --backup --slave-info --host="localhost" --port=3306 --user="bkpuser" --password="s3cret" --socket="/data/mysql/mysql.sock" --parallel=2 --target-dir=/data/xtrabackup/inc1 --incremental-basedir=/data/xtrabackup/base
xtrabackup: recognized server arguments: --parallel=2
xtrabackup: recognized client arguments: --backup=1 --slave-info=1 --host=localhost --port=3306 --user=bkpuser --password=* --socket=/data/mysql/mysql.sock --target-dir=/data/xtrabackup/inc1 --incremental-basedir=/data/xtrabackup/base
230112 18:36:00 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=localhost;port=3306;mysql_socket=/data/mysql/mysql.sock' as 'bkpuser' (using password: YES).
Failed to connect to MySQL server as DBD::mysql module is not installed at - line 1327.
230112 18:36:00 Connecting to MySQL server host: localhost, user: bkpuser, password: set, port: 3306, socket: /data/mysql/mysql.sock
Using server version 5.7.40-log
xtrabackup version 2.4.27 based on MySQL server 5.7.40 Linux (x86_64) (revision id: aae8e82)
incremental backup from 2768955 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysql/data/
xtrabackup: open files limit requested 0, set to 100000
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
230112 18:36:00 >> log scanned up to (2769355)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 24 for test/messages, old maximum was 0
xtrabackup: using the full scan for incremental backup
xtrabackup: Starting 2 threads for parallel data files transfer
230112 18:36:00 [02] Copying ./test/messages.ibd to /data/xtrabackup/inc1/test/messages.ibd.delta
230112 18:36:00 [02] ...done
230112 18:36:00 [01] Copying ./ibdata1 to /data/xtrabackup/inc1/ibdata1.delta
230112 18:36:00 [01] ...done
230112 18:36:00 [02] Copying ./mysql/time_zone_transition_type.ibd to /data/xtrabackup/inc1/mysql/time_zone_transition_type.ibd.delta
230112 18:36:00 [02] ...done
....
230112 18:36:01 [01] Copying ./sys/x@0024ps_digest_95th_percentile_by_avg_us.frm to /data/xtrabackup/inc1/sys/x@0024ps_digest_95th_percentile_by_avg_us.frm
230112 18:36:01 [01] ...done
230112 18:36:01 Finished backing up non-InnoDB tables and files
230112 18:36:01 [00] Writing /data/xtrabackup/inc1/xtrabackup_slave_info
230112 18:36:01 [00] ...done
230112 18:36:01 [00] Writing /data/xtrabackup/inc1/xtrabackup_binlog_info
230112 18:36:01 [00] ...done
230112 18:36:01 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '2769346'
xtrabackup: Stopping log copying thread.
.230112 18:36:01 >> log scanned up to (2769355)
230112 18:36:01 Executing UNLOCK TABLES
230112 18:36:01 All tables unlocked
230112 18:36:01 [00] Copying ib_buffer_pool to /data/xtrabackup/inc1/ib_buffer_pool
230112 18:36:01 [00] ...done
230112 18:36:01 Backup created in directory '/data/xtrabackup/inc1/'
MySQL binlog position: filename 'mysql-bin.000005', position '416'
230112 18:36:01 [00] Writing /data/xtrabackup/inc1/backup-my.cnf
230112 18:36:01 [00] ...done
230112 18:36:01 [00] Writing /data/xtrabackup/inc1/xtrabackup_info
230112 18:36:01 [00] ...done
xtrabackup: Transaction log of lsn (2769346) to (2769355) was copied.
230112 18:36:02 completed OK!查看inc1 xtrabackup_checkpoints
# cat /data/xtrabackup/inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2768955
to_lsn = 2769346
last_lsn = 2769355
compact = 0
recover_binlog_info = 0
flushed_lsn = 2769355新增数据inc2
mysql> INSERT INTO test.messages VALUES ('inc2');
Query OK, 1 row affected (0.00 sec)
mysql> select * from messages;
+---------+
| message |
+---------+
| hello |
| inc1 |
| inc2 |
+---------+
3 rows in set (0.00 sec)增量备份二
备份数据inc2
# xtrabackup --backup --slave-info --host="localhost" --port=3306 --user="bkpuser" --password="s3cret" --socket="/data/mysql/mysql.sock" --parallel=2 --target-dir=/data/xtrabackup/inc2 --incremental-basedir=/data/xtrabackup/inc1
xtrabackup: recognized server arguments: --parallel=2
xtrabackup: recognized client arguments: --backup=1 --slave-info=1 --host=localhost --port=3306 --user=bkpuser --password=* --socket=/data/mysql/mysql.sock --target-dir=/data/xtrabackup/inc2 --incremental-basedir=/data/xtrabackup/inc1
230112 18:39:39 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=localhost;port=3306;mysql_socket=/data/mysql/mysql.sock' as 'bkpuser' (using password: YES).
Failed to connect to MySQL server as DBD::mysql module is not installed at - line 1327.
230112 18:39:39 Connecting to MySQL server host: localhost, user: bkpuser, password: set, port: 3306, socket: /data/mysql/mysql.sock
Using server version 5.7.40-log
xtrabackup version 2.4.27 based on MySQL server 5.7.40 Linux (x86_64) (revision id: aae8e82)
incremental backup from 2769346 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysql/data/
xtrabackup: open files limit requested 0, set to 100000
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
230112 18:39:39 >> log scanned up to (2769713)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 24 for test/messages, old maximum was 0
xtrabackup: using the full scan for incremental backup
xtrabackup: Starting 2 threads for parallel data files transfer
230112 18:39:40 [01] Copying ./ibdata1 to /data/xtrabackup/inc2/ibdata1.delta
230112 18:39:40 [02] Copying ./test/messages.ibd to /data/xtrabackup/inc2/test/messages.ibd.delta
230112 18:39:40 [02] ...done
230112 18:39:40 [01] ...done
....
230112 18:39:41 [01] Copying ./sys/x@0024ps_digest_95th_percentile_by_avg_us.frm to /data/xtrabackup/inc2/sys/x@0024ps_digest_95th_percentile_by_avg_us.frm
230112 18:39:41 [01] ...done
230112 18:39:41 Finished backing up non-InnoDB tables and files
230112 18:39:41 [00] Writing /data/xtrabackup/inc2/xtrabackup_slave_info
230112 18:39:41 [00] ...done
230112 18:39:41 [00] Writing /data/xtrabackup/inc2/xtrabackup_binlog_info
230112 18:39:41 [00] ...done
230112 18:39:41 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '2769704'
xtrabackup: Stopping log copying thread.
.230112 18:39:41 >> log scanned up to (2769713)
230112 18:39:41 Executing UNLOCK TABLES
230112 18:39:41 All tables unlocked
230112 18:39:41 [00] Copying ib_buffer_pool to /data/xtrabackup/inc2/ib_buffer_pool
230112 18:39:41 [00] ...done
230112 18:39:41 Backup created in directory '/data/xtrabackup/inc2/'
MySQL binlog position: filename 'mysql-bin.000005', position '678'
230112 18:39:41 [00] Writing /data/xtrabackup/inc2/backup-my.cnf
230112 18:39:41 [00] ...done
230112 18:39:41 [00] Writing /data/xtrabackup/inc2/xtrabackup_info
230112 18:39:41 [00] ...done
xtrabackup: Transaction log of lsn (2769704) to (2769713) was copied.
230112 18:39:41 completed OK!查看inc2 xtrabackup_checkpoints
# cat /data/xtrabackup/inc2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2769346
to_lsn = 2769704
last_lsn = 2769713
compact = 0
recover_binlog_info = 0
flushed_lsn = 2769713清除测试数据
查看数据
mysql> select * from messages;
+---------+
| message |
+---------+
| hello |
| inc1 |
| inc2 |
+---------+
3 rows in set (0.00 sec)删除测试数据
mysql> drop table messages;
Query OK, 0 rows affected (0.00 sec)停止mysql服务
# systemctl stop mysqld还原全量数据
Preparing base
# xtrabackup --prepare --apply-log-only --target-dir=/data/xtrabackup/base/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=0 --redo-log-version=1
xtrabackup: recognized client arguments: --prepare=1 --apply-log-only=1 --target-dir=/data/xtrabackup/base/
xtrabackup version 2.4.27 based on MySQL server 5.7.40 Linux (x86_64) (revision id: aae8e82)
xtrabackup: cd to /data/xtrabackup/base/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2768955)
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 = .
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
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 = .
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)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.12
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 2768955
InnoDB: Doing recovery: scanned up to log sequence number 2768964 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 2875, file name mysql-bin.000003
InnoDB: xtrabackup: Last MySQL binlog file position 2875, file name mysql-bin.000003
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2768973
InnoDB: Number of pools: 1
230112 18:46:55 completed OK!copy-back base
# xtrabackup --copy-back --target-dir=/data/xtrabackup/base/ --datadir=/data/xtrabackup/base-copy-back
xtrabackup: recognized server arguments: --datadir=/data/xtrabackup/base-copy-back
xtrabackup: recognized client arguments: --copy-back=1 --target-dir=/data/xtrabackup/base/
xtrabackup version 2.4.27 based on MySQL server 5.7.40 Linux (x86_64) (revision id: aae8e82)
230112 18:58:21 [01] Copying ibdata1 to /data/xtrabackup/base-copy-back/ibdata1
230112 18:58:21 [01] ...done
230112 18:58:21 [01] Copying ./xtrabackup_info to /data/xtrabackup/base-copy-back/xtrabackup_info
230112 18:58:21 [01] ...done
230112 18:58:21 [01] Copying ./test/messages.ibd to /data/xtrabackup/base-copy-back/test/messages.ibd
230112 18:58:21 [01] ...done
230112 18:58:21 [01] Copying ./test/messages.frm to /data/xtrabackup/base-copy-back/test/messages.frm
230112 18:58:21 [01] ...done
230112 18:58:21 [01] Copying ./test/db.opt to /data/xtrabackup/base-copy-back/test/db.opt
230112 18:58:21 [01] ...done
230112 18:58:21 [01] Copying ./mysql/user.MYI to /data/xtrabackup/base-copy-back/mysql/user.MYI
230112 18:58:21 [01] ...done
230112 18:58:21 [01] Copying ./mysql/user.frm to /data/xtrabackup/base-copy-back/mysql/user.frm
230112 18:58:21 [01] ...done
.....
230112 18:58:21 [01] Copying ./sys/x@0024ps_digest_95th_percentile_by_avg_us.frm to /data/xtrabackup/base-copy-back/sys/x@0024ps_digest_95th_percentile_by_avg_us.frm
230112 18:58:21 [01] ...done
230112 18:58:21 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/xtrabackup/base-copy-back/xtrabackup_binlog_pos_innodb
230112 18:58:21 [01] ...done
230112 18:58:21 completed OK!同步数据
# rsync -avrP /data/xtrabackup/base-copy-back/ /data/mysql/data/mysql数据库目录
# ls -l /data/mysql/data/
total 110668
-rw-r----- 1 mysql mysql 56 Jan 12 13:40 auto.cnf
-rw------- 1 mysql mysql 1680 Jan 12 13:40 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 Jan 12 13:40 ca.pem
-rw-r--r-- 1 mysql mysql 1112 Jan 12 13:40 client-cert.pem
-rw------- 1 mysql mysql 1680 Jan 12 13:40 client-key.pem
-rw-r----- 1 mysql mysql 436 Jan 13 16:37 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Jan 13 16:37 ibdata1
-rw-r----- 1 mysql mysql 50331648 Jan 13 16:36 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Jan 12 19:22 ib_logfile1
drwxr-x--- 2 mysql mysql 4096 Jan 13 16:37 mysql
drwxr-x--- 2 mysql mysql 4096 Jan 13 16:37 performance_schema
-rw------- 1 mysql mysql 1680 Jan 12 13:40 private_key.pem
-rw-r--r-- 1 mysql mysql 452 Jan 12 13:40 public_key.pem
-rw-r--r-- 1 mysql mysql 1112 Jan 12 13:40 server-cert.pem
-rw------- 1 mysql mysql 1680 Jan 12 13:40 server-key.pem
drwxr-x--- 2 mysql mysql 12288 Jan 13 16:37 sys
drwxr-x--- 2 mysql mysql 4096 Jan 13 16:37 test修改属组
# chown -R mysql:mysql /data/mysql/data/启动mysql
# systemctl restart mysqld查询数据
mysql> select * from messages;
+---------+
| message |
+---------+
| hello |
+---------+
1 row in set (0.01 sec)还原增量1数据
Preparing 增量1
# xtrabackup --prepare --apply-log-only --target-dir=/data/xtrabackup/base --incremental-dir=/data/xtrabackup/inc1
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=0 --redo-log-version=1
xtrabackup: recognized client arguments: --prepare=1 --apply-log-only=1 --target-dir=/data/xtrabackup/base --incremental-dir=/data/xtrabackup/inc1
xtrabackup version 2.4.27 based on MySQL server 5.7.40 Linux (x86_64) (revision id: aae8e82)
incremental backup from 2768955 is enabled.
xtrabackup: cd to /data/xtrabackup/base/
xtrabackup: This target seems to be already prepared with --apply-log-only.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2769346)
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 = /data/xtrabackup/inc1/
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 24 for test/messages, old maximum was 0
xtrabackup: page size for /data/xtrabackup/inc1//ibdata1.delta is 16384 bytes space id is 0
Applying /data/xtrabackup/inc1//ibdata1.delta to ./ibdata1...
xtrabackup: page size for /data/xtrabackup/inc1//test/messages.ibd.delta is 16384 bytes space id is 24
Applying /data/xtrabackup/inc1//test/messages.ibd.delta to ./test/messages.ibd...
xtrabackup: page size for /data/xtrabackup/inc1//mysql/innodb_table_stats.ibd.delta is 16384 bytes space id is 13
Applying /data/xtrabackup/inc1//mysql/innodb_table_stats.ibd.delta to ./mysql/innodb_table_stats.ibd...
xtrabackup: page size for /data/xtrabackup/inc1//mysql/time_zone_name.ibd.delta is 16384 bytes space id is 8
Applying /data/xtrabackup/inc1//mysql/time_zone_name.ibd.delta to ./mysql/time_zone_name.ibd...
xtrabackup: page size for /data/xtrabackup/inc1//mysql/help_category.ibd.delta is 16384 bytes space id is 5
...
230112 18:49:24 [01] Copying /data/xtrabackup/inc1/sys/x@0024ps_digest_95th_percentile_by_avg_us.frm to ./sys/x@0024ps_digest_95th_percentile_by_avg_us.frm
230112 18:49:24 [01] ...done
230112 18:49:24 [00] Copying /data/xtrabackup/inc1//xtrabackup_binlog_info to ./xtrabackup_binlog_info
230112 18:49:24 [00] ...done
230112 18:49:24 [00] Copying /data/xtrabackup/inc1//xtrabackup_slave_info to ./xtrabackup_slave_info
230112 18:49:24 [00] ...done
230112 18:49:24 [00] Copying /data/xtrabackup/inc1//xtrabackup_info to ./xtrabackup_info
230112 18:49:24 [00] ...done
230112 18:49:24 completed OK!copy-back base-inc1
# xtrabackup --copy-back --target-dir=/data/xtrabackup/base/ --datadir=/data/xtrabackup/base-copy-back-inc1
xtrabackup: recognized server arguments: --datadir=/data/xtrabackup/base-copy-back-inc1
xtrabackup: recognized client arguments: --copy-back=1 --target-dir=/data/xtrabackup/base/
xtrabackup version 2.4.27 based on MySQL server 5.7.40 Linux (x86_64) (revision id: aae8e82)
230112 19:18:13 [01] Copying ibdata1 to /data/xtrabackup/base-copy-back-inc1/ibdata1
230112 19:18:13 [01] ...done
230112 19:18:13 [01] Copying ./xtrabackup_info to /data/xtrabackup/base-copy-back-inc1/xtrabackup_info
230112 19:18:13 [01] ...done
230112 19:18:13 [01] Copying ./test/messages.ibd to /data/xtrabackup/base-copy-back-inc1/test/messages.ibd
230112 19:18:13 [01] ...done
230112 19:18:13 [01] Copying ./test/messages.frm to /data/xtrabackup/base-copy-back-inc1/test/messages.frm
230112 19:18:13 [01] ...done
230112 19:18:13 [01] Copying ./test/db.opt to /data/xtrabackup/base-copy-back-inc1/test/db.opt
230112 19:18:13 [01] ...done
230112 19:18:13 [01] Copying ./mysql/user.MYI to /data/xtrabackup/base-copy-back-inc1/mysql/user.MYI
230112 19:18:13 [01] ...done
230112 19:18:13 [01] Copying ./mysql/user.frm to /data/xtrabackup/base-copy-back-inc1/mysql/user.frm
230112 19:18:13 [01] ...done
230112 19:18:13 [01] Copying ./mysql/time_zone_transition_type.ibd to /data/xtrabackup/base-copy-back-inc1/mysql/time_zone_transition_type.ibd
230112 19:18:13 [01] ...done
...
230112 19:18:13 [01] Copying ./sys/x@0024ps_digest_95th_percentile_by_avg_us.frm to /data/xtrabackup/base-copy-back-inc1/sys/x@0024ps_digest_95th_percentile_by_avg_us.frm
230112 19:18:13 [01] ...done
230112 19:18:13 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/xtrabackup/base-copy-back-inc1/xtrabackup_binlog_pos_innodb
230112 19:18:13 [01] ...done
230112 19:18:13 completed OK!停止mysql
# systemctl stop mysqld同步数据
# rsync -avrP /data/xtrabackup/base-copy-back-inc1/ /data/mysql/data/mysql数据库目录
# ls -l /data/mysql/data/
total 110668
-rw-r----- 1 mysql mysql 56 Jan 12 13:40 auto.cnf
-rw------- 1 mysql mysql 1680 Jan 12 13:40 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 Jan 12 13:40 ca.pem
-rw-r--r-- 1 mysql mysql 1112 Jan 12 13:40 client-cert.pem
-rw------- 1 mysql mysql 1680 Jan 12 13:40 client-key.pem
-rw-r----- 1 mysql mysql 436 Jan 13 16:37 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Jan 13 16:37 ibdata1
-rw-r----- 1 mysql mysql 50331648 Jan 13 16:36 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Jan 12 19:22 ib_logfile1
drwxr-x--- 2 mysql mysql 4096 Jan 13 16:37 mysql
drwxr-x--- 2 mysql mysql 4096 Jan 13 16:37 performance_schema
-rw------- 1 mysql mysql 1680 Jan 12 13:40 private_key.pem
-rw-r--r-- 1 mysql mysql 452 Jan 12 13:40 public_key.pem
-rw-r--r-- 1 mysql mysql 1112 Jan 12 13:40 server-cert.pem
-rw------- 1 mysql mysql 1680 Jan 12 13:40 server-key.pem
drwxr-x--- 2 mysql mysql 12288 Jan 13 16:37 sys
drwxr-x--- 2 mysql mysql 4096 Jan 13 16:37 test修改属组
# chown -R mysql:mysql /data/mysql/data/重启mysql
# systemctl restart mysqld查询数据
mysql> select * from messages;
+---------+
| message |
+---------+
| hello |
| inc1 |
+---------+
2 rows in set (0.00 sec)还原增量2数据
Preparing 增量2
注意： 最后一次增量preparing 不加--apply-log-only参数
# xtrabackup --prepare --target-dir=/data/xtrabackup/base --incremental-dir=/data/xtrabackup/inc2
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=0 --redo-log-version=1
xtrabackup: recognized client arguments: --prepare=1 --apply-log-only=1 --target-dir=/data/xtrabackup/base --incremental-dir=/data/xtrabackup/inc2
xtrabackup version 2.4.27 based on MySQL server 5.7.40 Linux (x86_64) (revision id: aae8e82)
incremental backup from 2769346 is enabled.
xtrabackup: cd to /data/xtrabackup/base/
xtrabackup: This target seems to be already prepared with --apply-log-only.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2769704)
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 = /data/xtrabackup/inc2/
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 24 for test/messages, old maximum was 0
xtrabackup: page size for /data/xtrabackup/inc2//ibdata1.delta is 16384 bytes space id is 0
Applying /data/xtrabackup/inc2//ibdata1.delta to ./ibdata1...
xtrabackup: page size for /data/xtrabackup/inc2//test/messages.ibd.delta is 16384 bytes space id is 24
.....
230112 18:52:35 [01] Copying /data/xtrabackup/inc2/sys/waits_by_host_by_latency.frm to ./sys/waits_by_host_by_latency.frm
230112 18:52:35 [01] ...done
230112 18:52:35 [01] Copying /data/xtrabackup/inc2/sys/x@0024ps_digest_95th_percentile_by_avg_us.frm to ./sys/x@0024ps_digest_95th_percentile_by_avg_us.frm
230112 18:52:35 [01] ...done
230112 18:52:35 [00] Copying /data/xtrabackup/inc2//xtrabackup_binlog_info to ./xtrabackup_binlog_info
230112 18:52:35 [00] ...done
230112 18:52:35 [00] Copying /data/xtrabackup/inc2//xtrabackup_slave_info to ./xtrabackup_slave_info
230112 18:52:35 [00] ...done
230112 18:52:35 [00] Copying /data/xtrabackup/inc2//xtrabackup_info to ./xtrabackup_info
230112 18:52:35 [00] ...done
230112 18:52:35 completed OK!copy-back base-inc2
# xtrabackup --copy-back --target-dir=/data/xtrabackup/base/ --datadir=/data/xtrabackup/base-copy-back-inc2
xtrabackup: recognized server arguments: --datadir=/data/xtrabackup/base-copy-back-inc2
xtrabackup: recognized client arguments: --copy-back=1 --target-dir=/data/xtrabackup/base/
xtrabackup version 2.4.27 based on MySQL server 5.7.40 Linux (x86_64) (revision id: aae8e82)
230112 19:22:47 [01] Copying ib_logfile0 to /data/xtrabackup/base-copy-back-inc2/ib_logfile0
230112 19:22:47 [01] ...done
230112 19:22:47 [01] Copying ib_logfile1 to /data/xtrabackup/base-copy-back-inc2/ib_logfile1
230112 19:22:47 [01] ...done
230112 19:22:47 [01] Copying ibdata1 to /data/xtrabackup/base-copy-back-inc2/ibdata1
230112 19:22:47 [01] ...done
230112 19:22:47 [01] Copying ./xtrabackup_info to /data/xtrabackup/base-copy-back-inc2/xtrabackup_info
230112 19:22:47 [01] ...done
230112 19:22:47 [01] Copying ./test/messages.ibd to /data/xtrabackup/base-copy-back-inc2/test/messages.ibd
230112 19:22:47 [01] ...done
230112 19:22:47 [01] Copying ./test/messages.frm to /data/xtrabackup/base-copy-back-inc2/test/messages.frm
230112 19:22:47 [01] ...done
230112 19:22:47 [01] Copying ./test/db.opt to /data/xtrabackup/base-copy-back-inc2/test/db.opt
230112 19:22:47 [01] ...done
230112 19:18:13 [01] Copying ./mysql/user.MYI to /data/xtrabackup/base-copy-back-inc1/mysql/user.MYI
230112 19:18:13 [01] ...done
230112 19:18:13 [01] Copying ./mysql/user.frm to /data/xtrabackup/base-copy-back-inc1/mysql/user.frm
230112 19:18:13 [01] ...done
230112 19:18:13 [01] Copying ./mysql/time_zone_transition_type.ibd to /data/xtrabackup/base-copy-back-inc1/mysql/time_zone_transition_type.ibd
230112 19:18:13 [01] ...done
...
230112 19:22:47 [01] Copying ./sys/x@0024io_global_by_file_by_bytes.frm to /data/xtrabackup/base-copy-back-inc2/sys/x@0024io_global_by_file_by_bytes.frm
230112 19:22:47 [01] ...done
230112 19:22:47 [01] Copying ./sys/statement_analysis.frm to /data/xtrabackup/base-copy-back-inc2/sys/statement_analysis.frm
230112 19:22:47 [01] ...done
230112 19:22:47 [01] Copying ./sys/waits_by_host_by_latency.frm to /data/xtrabackup/base-copy-back-inc2/sys/waits_by_host_by_latency.frm
230112 19:22:47 [01] ...done
230112 19:22:47 [01] Copying ./sys/x@0024ps_digest_95th_percentile_by_avg_us.frm to /data/xtrabackup/base-copy-back-inc2/sys/x@0024ps_digest_95th_percentile_by_avg_us.frm
230112 19:22:47 [01] ...done
230112 19:22:47 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/xtrabackup/base-copy-back-inc2/xtrabackup_binlog_pos_innodb
230112 19:22:47 [01] ...done
230112 19:22:47 completed OK!停止mysql
# systemctl stop mysqld同步数据
# rsync -avrP /data/xtrabackup/base-copy-back-inc2/ /data/mysql/data/查看mysql数据目录
# ls -l /data/mysql/data/
total 110668
-rw-r----- 1 mysql mysql 56 Jan 12 13:40 auto.cnf
-rw------- 1 mysql mysql 1680 Jan 12 13:40 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 Jan 12 13:40 ca.pem
-rw-r--r-- 1 mysql mysql 1112 Jan 12 13:40 client-cert.pem
-rw------- 1 mysql mysql 1680 Jan 12 13:40 client-key.pem
-rw-r----- 1 root root 436 Jan 13 16:37 ib_buffer_pool
-rw-r----- 1 root root 12582912 Jan 13 16:37 ibdata1
-rw-r----- 1 mysql mysql 50331648 Jan 13 16:36 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Jan 12 19:22 ib_logfile1
drwxr-x--- 2 root root 4096 Jan 13 16:37 mysql
drwxr-x--- 2 root root 4096 Jan 13 16:37 performance_schema
-rw------- 1 mysql mysql 1680 Jan 12 13:40 private_key.pem
-rw-r--r-- 1 mysql mysql 452 Jan 12 13:40 public_key.pem
-rw-r--r-- 1 mysql mysql 1112 Jan 12 13:40 server-cert.pem
-rw------- 1 mysql mysql 1680 Jan 12 13:40 server-key.pem
drwxr-x--- 2 root root 12288 Jan 13 16:37 sys
drwxr-x--- 2 root root 4096 Jan 13 16:37 test
-rw-r----- 1 mysql mysql 22 Jan 12 19:22 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 633 Jan 13 16:37 xtrabackup_info
-rw-r----- 1 mysql mysql 1 Jan 12 19:22 xtrabackup_master_key_id
-rw-r----- 1 root root 0 Jan 13 16:37 xtrabackup_slave_info修改属组
# chown -R mysql:mysql /data/mysql/data/重启mysql
# systemctl restart mysqld查询数据
mysql> select * from messages;
+---------+
| message |
+---------+
| hello |
| inc1 |
| inc2 |
+---------+
3 rows in set (0.00 sec)