创建备份用户
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
xbstream
备份数据
~# xtrabackup --backup --slave-info --host="localhost" --port=3306 --user="bkpuser" --password="s3cret" --stream=xbstream --socket="/data/mysql/mysql.sock" --parallel=2 --target-dir="/data/backups/" > /data/backups/`date +%Y-%m-%d_%H-%M%S`.xbstream
xtrabackup: recognized server arguments: --parallel=2
xtrabackup: recognized client arguments: --backup=1 --slave-info=1 --host=localhost --port=3306 --user=bkpuser --password=* --stream=xbstream --socket=/data/mysql/mysql.sock --target-dir=/data/backups/
230113 15:42:28 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.
230113 15:42:28 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)
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
230113 15:42:28 >> log scanned up to (2790262)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 33 for test/messages, old maximum was 0
xtrabackup: Starting 2 threads for parallel data files transfer
230113 15:42:28 [01] Streaming ./ibdata1
230113 15:42:28 [02] Streaming ./test/messages.ibd
230113 15:42:28 [02] ...done
...
230113 15:42:29 [01] Streaming ./sys/x@0024ps_digest_95th_percentile_by_avg_us.frm to <STDOUT>
230113 15:42:29 [01] ...done
230113 15:42:29 Finished backing up non-InnoDB tables and files
230113 15:42:29 [00] Streaming <STDOUT>
230113 15:42:29 [00] ...done
230113 15:42:29 [00] Streaming <STDOUT>
230113 15:42:29 [00] ...done
230113 15:42:29 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '2790253'
xtrabackup: Stopping log copying thread.
.230113 15:42:29 >> log scanned up to (2790262)
230113 15:42:29 Executing UNLOCK TABLES
230113 15:42:29 All tables unlocked
230113 15:42:29 [00] Streaming ib_buffer_pool to <STDOUT>
230113 15:42:29 [00] ...done
230113 15:42:29 Backup created in directory '/data/backups/'
MySQL binlog position: filename 'mysql-bin.000009', position '791'
230113 15:42:29 [00] Streaming <STDOUT>
230113 15:42:29 [00] ...done
230113 15:42:29 [00] Streaming <STDOUT>
230113 15:42:29 [00] ...done
xtrabackup: Transaction log of lsn (2790253) to (2790262) was copied.
230113 15:42:30 completed OK!
查看备份数据
# ls -l /data/backups/
total 25532
-rw-r--r-- 1 root root 26141719 Jan 13 15:42 2023-01-13_15-4228.xbstream
解压xbstream文件
创建解压目录
# mkdir /data/backups/xbs-data解压文件
# xbstream -x --parallel=2 -C /data/backups/xbs-data < 2023-01-13_15-4228.xbstream查看解压文件
# ls xbs-data/ -l
total 12336
-rw-r----- 1 root root 487 Jan 13 15:53 backup-my.cnf
-rw-r----- 1 root root 436 Jan 13 15:53 ib_buffer_pool
-rw-r----- 1 root root 12582912 Jan 13 15:53 ibdata1
drwxr-x--- 2 root root 4096 Jan 13 15:53 mysql
drwxr-x--- 2 root root 4096 Jan 13 15:53 performance_schema
drwxr-x--- 2 root root 12288 Jan 13 15:53 sys
drwxr-x--- 2 root root 4096 Jan 13 15:53 test
-rw-r----- 1 root root 21 Jan 13 15:53 xtrabackup_binlog_info
-rw-r----- 1 root root 135 Jan 13 15:53 xtrabackup_checkpoints
-rw-r----- 1 root root 604 Jan 13 15:53 xtrabackup_info
-rw-r----- 1 root root 2560 Jan 13 15:53 xtrabackup_logfile
-rw-r----- 1 root root 0 Jan 13 15:53 xtrabackup_slave_info
tar
备份数据
# xtrabackup --backup --slave-info --host="localhost" --port=3306 --user="bkpuser" --password="s3cret" --stream=tar --socket="/data/mysql/mysql.sock" --parallel=2 --target-dir="/data/backups/" > /data/backups/`date +%Y-%m-%d_%H-%M%S`.tar
xtrabackup: recognized server arguments: --parallel=2
xtrabackup: recognized client arguments: --backup=1 --slave-info=1 --host=localhost --port=3306 --user=bkpuser --password=* --stream=tar --socket=/data/mysql/mysql.sock --target-dir=/data/backups/
230113 16:00:35 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.
230113 16:00:35 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)
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
xtrabackup: warning: the --parallel option does not have any effect when streaming in the 'tar' format. You can use the 'xbstream' format instead.
230113 16:00:35 >> log scanned up to (2790388)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 33 for test/messages, old maximum was 0
230113 16:00:35 [01] Streaming ./ibdata1
230113 16:00:35 [01] ...done
...
230113 16:00:36 [01] Streaming ./sys/x@0024ps_digest_95th_percentile_by_avg_us.frm to <STDOUT>
230113 16:00:36 [01] ...done
230113 16:00:36 Finished backing up non-InnoDB tables and files
230113 16:00:36 [00] Streaming <STDOUT>
230113 16:00:36 [00] ...done
230113 16:00:36 [00] Streaming <STDOUT>
230113 16:00:36 [00] ...done
230113 16:00:36 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '2790379'
xtrabackup: Stopping log copying thread.
.230113 16:00:36 >> log scanned up to (2790388)
230113 16:00:36 Executing UNLOCK TABLES
230113 16:00:36 All tables unlocked
230113 16:00:36 [00] Streaming ib_buffer_pool to <STDOUT>
230113 16:00:36 [00] ...done
230113 16:00:36 Backup created in directory '/data/backups/'
MySQL binlog position: filename 'mysql-bin.000009', position '791'
230113 16:00:36 [00] Streaming <STDOUT>
230113 16:00:36 [00] ...done
230113 16:00:36 [00] Streaming <STDOUT>
230113 16:00:36 [00] ...done
xtrabackup: Transaction log of lsn (2790379) to (2790388) was copied.
230113 16:00:37 completed OK!
查看备份文件
# ls -l 2023-01-13_16-0035.tar
-rw-r--r-- 1 root root 26315776 Jan 13 16:00 2023-01-13_16-0035.tar
解压tar备份文件
创建数据目录
# mkdir /data/backups/tar-data解压文件
# tar xf 2023-01-13_16-0035.tar -C tar-data/查看解压文件
# ls -l tar-data/
total 12336
-rw-rw---- 1 root root 487 Jan 13 16:00 backup-my.cnf
-rw-rw---- 1 root root 436 Jan 12 19:22 ib_buffer_pool
-rw-rw---- 1 root root 12582912 Jan 13 15:43 ibdata1
drwxr-xr-x 2 root root 4096 Jan 13 16:04 mysql
drwxr-xr-x 2 root root 4096 Jan 13 16:04 performance_schema
drwxr-xr-x 2 root root 12288 Jan 13 16:04 sys
drwxr-xr-x 2 root root 4096 Jan 13 16:04 test
-rw-rw---- 1 root root 21 Jan 13 16:00 xtrabackup_binlog_info
-rw-rw---- 1 root root 135 Jan 13 16:00 xtrabackup_checkpoints
-rw-rw---- 1 root root 594 Jan 13 16:00 xtrabackup_info
-rw-rw---- 1 root root 2560 Jan 13 16:00 xtrabackup_logfile
-rw-rw---- 1 root root 0 Jan 13 16:00 xtrabackup_slave_info
qp文件
创建备份qp文件
# xtrabackup --backup --slave-info --host="localhost" --port=3306 --user="bkpuser" --password="s3cret" --compress --compress-threads=4 --socket="/data/mysql/mysql.sock" --parallel=2 --target-dir="/data/backups/compressed"
xtrabackup: recognized server arguments: --parallel=2
xtrabackup: recognized client arguments: --backup=1 --slave-info=1 --host=localhost --port=3306 --user=bkpuser --password=* --compress --compress-threads=4 --socket=/data/mysql/mysql.sock --target-dir=/data/backups/compressed
sh: 0: getcwd() failed: No such file or directory
sh: 0: getcwd() failed: No such file or directory
/bin/pwd: couldn't find directory entry in ‘..’ with matching i-node
230113 16:12:55 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.
230113 16:12:55 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)
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
230113 16:12:55 >> log scanned up to (2790388)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 33 for test/messages, old maximum was 0
xtrabackup: Starting 2 threads for parallel data files transfer
230113 16:12:55 [01] Compressing ./ibdata1 to /data/backups/compressed/ibdata1.qp
230113 16:12:55 [02] Compressing ./test/messages.ibd to /data/backups/compressed/test/messages.ibd.qp
230113 16:12:55 [02] ...done
...
230113 16:12:56 [01] Compressing ./sys/x@0024ps_digest_95th_percentile_by_avg_us.frm to /data/backups/compressed/sys/x@0024ps_digest_95th_percentile_by_avg_us.frm.qp
230113 16:12:56 [01] ...done
230113 16:12:56 Finished backing up non-InnoDB tables and files
230113 16:12:56 [00] Compressing /data/backups/compressed/xtrabackup_slave_info.qp
230113 16:12:56 [00] ...done
230113 16:12:56 [00] Compressing /data/backups/compressed/xtrabackup_binlog_info.qp
230113 16:12:56 [00] ...done
230113 16:12:56 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '2790379'
xtrabackup: Stopping log copying thread.
.230113 16:12:56 >> log scanned up to (2790388)
230113 16:12:56 Executing UNLOCK TABLES
230113 16:12:56 All tables unlocked
230113 16:12:56 [00] Compressing ib_buffer_pool to /data/backups/compressed/ib_buffer_pool.qp
230113 16:12:56 [00] ...done
230113 16:12:56 Backup created in directory '/data/backups/compressed/'
MySQL binlog position: filename 'mysql-bin.000009', position '791'
230113 16:12:56 [00] Compressing /data/backups/compressed/backup-my.cnf.qp
230113 16:12:56 [00] ...done
230113 16:12:56 [00] Compressing /data/backups/compressed/xtrabackup_info.qp
230113 16:12:56 [00] ...done
xtrabackup: Transaction log of lsn (2790379) to (2790388) was copied.
230113 16:12:56 completed OK!
查看备份文件
# ls -l compressed/
total 548
-rw-r----- 1 root root 475 Jan 13 16:12 backup-my.cnf.qp
-rw-r----- 1 root root 388 Jan 13 16:12 ib_buffer_pool.qp
-rw-r----- 1 root root 505310 Jan 13 16:12 ibdata1.qp
drwxr-x--- 2 root root 4096 Jan 13 16:12 mysql
drwxr-x--- 2 root root 4096 Jan 13 16:12 performance_schema
drwxr-x--- 2 root root 12288 Jan 13 16:12 sys
drwxr-x--- 2 root root 4096 Jan 13 16:12 test
-rw-r----- 1 root root 108 Jan 13 16:12 xtrabackup_binlog_info.qp
-rw-r----- 1 root root 135 Jan 13 16:12 xtrabackup_checkpoints
-rw-r----- 1 root root 584 Jan 13 16:12 xtrabackup_info.qp
-rw-r----- 1 root root 545 Jan 13 16:12 xtrabackup_logfile.qp
-rw-r----- 1 root root 59 Jan 13 16:12 xtrabackup_slave_info.qp
解压qp文件
# xtrabackup --decompress --remove-original --target-dir=/data/backups/compressed/
xtrabackup: recognized server arguments:
xtrabackup: recognized client arguments: --decompress=1 --remove-original=1 --target-dir=/data/backups/compressed/
xtrabackup version 2.4.27 based on MySQL server 5.7.40 Linux (x86_64) (revision id: aae8e82)
230113 16:30:35 [01] decompressing ./backup-my.cnf.qp
230113 16:30:35 [01] removing ./backup-my.cnf.qp
230113 16:30:35 [01] decompressing ./test/db.opt.qp
230113 16:30:35 [01] removing ./test/db.opt.qp
230113 16:30:35 [01] decompressing ./test/messages.frm.qp
230113 16:30:35 [01] removing ./test/messages.frm.qp
230113 16:30:35 [01] decompressing ./test/messages.ibd.qp
230113 16:30:35 [01] removing ./test/messages.ibd.qp
230113 16:30:35 [01] decompressing ./mysql/db.MYI.qp
230113 16:30:35 [01] removing ./mysql/db.MYI.qp
...
230113 16:30:36 [01] decompressing ./sys/waits_by_host_by_latency.frm.qp
230113 16:30:36 [01] removing ./sys/waits_by_host_by_latency.frm.qp
230113 16:30:36 [01] decompressing ./sys/user_summary_by_statement_latency.frm.qp
230113 16:30:36 [01] removing ./sys/user_summary_by_statement_latency.frm.qp
230113 16:30:36 [01] decompressing ./sys/host_summary_by_file_io.frm.qp
230113 16:30:36 [01] removing ./sys/host_summary_by_file_io.frm.qp
230113 16:30:36 completed OK!
# ls -l compressed/
total 12344
-rw-r--r-- 1 root root 487 Jan 13 16:30 backup-my.cnf
-rw-r--r-- 1 root root 436 Jan 13 16:30 ib_buffer_pool
-rw-r--r-- 1 root root 12582912 Jan 13 16:30 ibdata1
drwxr-x--- 2 root root 4096 Jan 13 16:30 mysql
drwxr-x--- 2 root root 12288 Jan 13 16:30 performance_schema
drwxr-x--- 2 root root 12288 Jan 13 16:30 sys
drwxr-x--- 2 root root 4096 Jan 13 16:30 test
-rw-r--r-- 1 root root 21 Jan 13 16:30 xtrabackup_binlog_info
-rw-r----- 1 root root 135 Jan 13 16:29 xtrabackup_checkpoints
-rw-r--r-- 1 root root 633 Jan 13 16:30 xtrabackup_info
-rw-r--r-- 1 root root 2560 Jan 13 16:30 xtrabackup_logfile
-rw-r--r-- 1 root root 0 Jan 13 16:30 xtrabackup_slave_info
Preparing the backup
# xtrabackup --prepare --target-dir=/data/backups/compressed/
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 --target-dir=/data/backups/compressed/
xtrabackup version 2.4.27 based on MySQL server 5.7.40 Linux (x86_64) (revision id: aae8e82)
xtrabackup: cd to /data/backups/compressed/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2790379)
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 2790379
InnoDB: Doing recovery: scanned up to log sequence number 2790388 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 791, file name mysql-bin.000009
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.40 started; log sequence number 2790388
InnoDB: xtrabackup: Last MySQL binlog file position 791, file name mysql-bin.000009
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2790423
InnoDB: Number of pools: 1
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 = 2
xtrabackup: innodb_log_file_size = 50331648
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: Setting log file ./ib_logfile101 size to 48 MB
InnoDB: Setting log file ./ib_logfile1 size to 48 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=2790423
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 2790924
InnoDB: Doing recovery: scanned up to log sequence number 2790933 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 791, file name mysql-bin.000009
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.40 started; log sequence number 2790933
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2790952
230113 16:24:52 completed OK!
还原数据
删除测试数据
mysql> drop table messages;
Query OK, 0 rows affected (0.00 sec)
停止mysql服务
# systemctl stop mysqldcopy-back
# xtrabackup --copy-back --target-dir=/data/backups/compressed/ --datadir=/data/backups/compressed-backs
xtrabackup: recognized server arguments: --datadir=/data/xtrabackup/backs
xtrabackup: recognized client arguments: --copy-back=1 --target-dir=/data/backups/compressed/
xtrabackup version 2.4.27 based on MySQL server 5.7.40 Linux (x86_64) (revision id: aae8e82)
230113 16:37:04 [01] Copying ibdata1 to /data/backups/compressed-backs/ibdata1
230113 16:37:04 [01] ...done
230113 16:37:04 [01] Copying ./xtrabackup_info to /data/backups/compressed-backs/xtrabackup_info
230113 16:37:04 [01] ...done
230113 16:37:04 [01] Copying ./test/messages.ibd to /data/backups/compressed-backs/test/messages.ibd
230113 16:37:04 [01] ...done
230113 16:37:04 [01] Copying ./test/messages.frm to /data/backups/compressed-backs/test/messages.frm
230113 16:37:04 [01] ...done
230113 16:37:04 [01] Copying ./test/db.opt to /data/backups/compressed-backs/test/db.opt
230113 16:37:04 [01] ...done
...
230113 16:37:04 [01] Copying ./sys/waits_by_host_by_latency.frm to /data/backups/compressed-backs/sys/waits_by_host_by_latency.frm
230113 16:37:04 [01] ...done
230113 16:37:04 [01] Copying ./sys/x@0024ps_digest_95th_percentile_by_avg_us.frm to /data/backups/compressed-backs/sys/x@0024ps_digest_95th_percentile_by_avg_us.frm
230113 16:37:04 [01] ...done
230113 16:37:05 completed OK!
同步数据
# rsync -avrP /data/backups/compressed-backs/ /data/mysql/data/
test/
test/db.opt
65 100% 0.61kB/s 0:00:00 (xfr#275, to-chk=2/282)
test/messages.frm
8,566 100% 80.43kB/s 0:00:00 (xfr#276, to-chk=1/282)
test/messages.ibd
98,304 100% 923.08kB/s 0:00:00 (xfr#277, to-chk=0/282)
sent 26,133,793 bytes received 5,318 bytes 17,426,074.00 bytes/sec
total size is 26,105,339 speedup is 1.00
查看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 |
+---------+
1 row in set (0.00 sec)
qp.xbstream
备份数据
# xtrabackup --backup --slave-info --host="localhost" --port=3306 --user="bkpuser" --password="s3cret" --compress --compress-threads=4 --stream=xbstream --socket="/data/mysql/mysql.sock" --parallel=2 --target-dir="/data/backups/" > /data/backups/xtra_full_`date +%Y-%m-%d_%H-%M%S`_qp.xbstream
xtrabackup: recognized server arguments: --parallel=2
xtrabackup: recognized client arguments: --backup=1 --slave-info=1 --host=localhost --port=3306 --user=bkpuser --password=* --compress --compress-threads=4 --stream=xbstream --socket=/data/mysql/mysql.sock --target-dir=/data/backups/
230113 16:57:28 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.
230113 16:57:28 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)
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
230113 16:57:28 >> log scanned up to (2795702)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 33 for test/messages, old maximum was 0
xtrabackup: Starting 2 threads for parallel data files transfer
230113 16:57:29 [01] Compressing and streaming ./ibdata1
230113 16:57:29 [02] Compressing and streaming ./test/messages.ibd
230113 16:57:29 [02] ...done
...
230113 16:57:30 [01] Compressing and streaming ./sys/x@0024ps_digest_95th_percentile_by_avg_us.frm to <STDOUT>
230113 16:57:30 [01] ...done
230113 16:57:30 Finished backing up non-InnoDB tables and files
230113 16:57:30 [00] Compressing and streaming <STDOUT>
230113 16:57:30 [00] ...done
230113 16:57:30 [00] Compressing and streaming <STDOUT>
230113 16:57:30 [00] ...done
230113 16:57:30 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '2795693'
xtrabackup: Stopping log copying thread.
.230113 16:57:30 >> log scanned up to (2795702)
230113 16:57:30 Executing UNLOCK TABLES
230113 16:57:30 All tables unlocked
230113 16:57:30 [00] Compressing and streaming ib_buffer_pool to <STDOUT>
230113 16:57:30 [00] ...done
230113 16:57:30 Backup created in directory '/data/backups/'
MySQL binlog position: filename 'mysql-bin.000010', position '154'
230113 16:57:30 [00] Compressing and streaming <STDOUT>
230113 16:57:30 [00] ...done
230113 16:57:30 [00] Compressing and streaming <STDOUT>
230113 16:57:30 [00] ...done
xtrabackup: Transaction log of lsn (2795693) to (2795702) was copied.
230113 16:57:30 completed OK!
查看备份数据
# ls -l xtra_full_2023-01-13_16-5728_qp.xbstream
-rw-r--r-- 1 root root 1350345 Jan 13 16:57 xtra_full_2023-01-13_16-5728_qp.xbstream
解压xbstream文件
创建解压目录
# mkdir /data/backups/qp-data解压xbstream文件
# cat xtra_full_2023-01-13_16-5728_qp.xbstream | xbstream -x --parallel=2 -C /data/backups/qp-data/查看解压文件
# ls -l qp-data/
total 548
-rw-r----- 1 root root 475 Jan 13 17:02 backup-my.cnf.qp
-rw-r----- 1 root root 388 Jan 13 17:02 ib_buffer_pool.qp
-rw-r----- 1 root root 505543 Jan 13 17:02 ibdata1.qp
drwxr-x--- 2 root root 4096 Jan 13 17:02 mysql
drwxr-x--- 2 root root 4096 Jan 13 17:02 performance_schema
drwxr-x--- 2 root root 12288 Jan 13 17:02 sys
drwxr-x--- 2 root root 4096 Jan 13 17:02 test
-rw-r----- 1 root root 108 Jan 13 17:02 xtrabackup_binlog_info.qp
-rw-r----- 1 root root 135 Jan 13 17:02 xtrabackup_checkpoints
-rw-r----- 1 root root 595 Jan 13 17:02 xtrabackup_info.qp
-rw-r----- 1 root root 612 Jan 13 17:02 xtrabackup_logfile.qp
-rw-r----- 1 root root 59 Jan 13 17:02 xtrabackup_slave_info.qp
解压qp文件
# xtrabackup --decompress --remove-original --target-dir=/data/backups/qp-data/
xtrabackup: recognized server arguments:
xtrabackup: recognized client arguments: --decompress=1 --remove-original=1 --target-dir=/data/backups/qp-data/
xtrabackup version 2.4.27 based on MySQL server 5.7.40 Linux (x86_64) (revision id: aae8e82)
230113 17:04:20 [01] decompressing ./backup-my.cnf.qp
230113 17:04:20 [01] removing ./backup-my.cnf.qp
230113 17:04:20 [01] decompressing ./test/db.opt.qp
230113 17:04:20 [01] removing ./test/db.opt.qp
230113 17:04:20 [01] decompressing ./test/messages.frm.qp
230113 17:04:20 [01] removing ./test/messages.frm.qp
230113 17:04:20 [01] decompressing ./test/messages.ibd.qp
230113 17:04:20 [01] removing ./test/messages.ibd.qp
230113 17:04:20 [01] decompressing ./mysql/db.MYI.qp
230113 17:04:20 [01] removing ./mysql/db.MYI.qp
...
230113 17:04:21 [01] removing ./sys/user_summary_by_statement_latency.frm.qp
230113 17:04:21 [01] decompressing ./sys/host_summary_by_file_io.frm.qp
230113 17:04:21 [01] removing ./sys/host_summary_by_file_io.frm.qp
230113 17:04:21 completed OK!
查看解压文件
# ls -l qp-data/
total 12344
-rw-r--r-- 1 root root 487 Jan 13 17:04 backup-my.cnf
-rw-r--r-- 1 root root 436 Jan 13 17:04 ib_buffer_pool
-rw-r--r-- 1 root root 12582912 Jan 13 17:04 ibdata1
drwxr-x--- 2 root root 4096 Jan 13 17:04 mysql
drwxr-x--- 2 root root 12288 Jan 13 17:04 performance_schema
drwxr-x--- 2 root root 12288 Jan 13 17:04 sys
drwxr-x--- 2 root root 4096 Jan 13 17:04 test
-rw-r--r-- 1 root root 21 Jan 13 17:04 xtrabackup_binlog_info
-rw-r----- 1 root root 135 Jan 13 17:02 xtrabackup_checkpoints
-rw-r--r-- 1 root root 645 Jan 13 17:04 xtrabackup_info
-rw-r--r-- 1 root root 2560 Jan 13 17:04 xtrabackup_logfile
-rw-r--r-- 1 root root 0 Jan 13 17:04 xtrabackup_slave_info
Preparing the backup
# xtrabackup --prepare --target-dir=/data/backups/qp-data/
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 --target-dir=/data/backups/qp-data/
xtrabackup version 2.4.27 based on MySQL server 5.7.40 Linux (x86_64) (revision id: aae8e82)
xtrabackup: cd to /data/backups/qp-data/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2795693)
xtrabackup: using the following InnoDB configuration for recovery:
...
InnoDB: 5.7.40 started; log sequence number 2796053
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2796072
230113 17:07:49 completed OK!
还原数据
删除测试数据
mysql> drop table messages;
Query OK, 0 rows affected (0.00 sec)
停止mysql服务
# systemctl stop mysqldcopy-back
# xtrabackup --copy-back --target-dir=/data/backups/qp-data/ --datadir=/data/backups/qp-data-backs
xtrabackup: recognized server arguments: --datadir=/data/backups/qp-data-backs
xtrabackup: recognized client arguments: --copy-back=1 --target-dir=/data/backups/qp-data/
xtrabackup version 2.4.27 based on MySQL server 5.7.40 Linux (x86_64) (revision id: aae8e82)
230113 17:10:55 [01] Copying ib_logfile0 to /data/backups/qp-data-backs/ib_logfile0
230113 17:10:55 [01] ...done
...
230113 17:10:56 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/backups/qp-data-backs/xtrabackup_binlog_pos_innodb
230113 17:10:56 [01] ...done
230113 17:10:56 completed OK!
同步数据
# rsync -avrP /data/backups/qp-data-backs/ /data/mysql/data/查看mysql数据目录
# ls -l /data/mysql/data/
total 122956
-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 17:10 ib_buffer_pool
-rw-r----- 1 root root 12582912 Jan 13 17:10 ibdata1
-rw-r----- 1 root root 50331648 Jan 13 17:10 ib_logfile0
-rw-r----- 1 root root 50331648 Jan 13 17:10 ib_logfile1
-rw-r----- 1 root root 12582912 Jan 13 17:10 ibtmp1
drwxr-x--- 2 root root 4096 Jan 13 17:10 mysql
drwxr-x--- 2 root root 4096 Jan 13 17:10 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 17:10 sys
drwxr-x--- 2 root root 4096 Jan 13 17:10 test
-rw-r----- 1 root root 21 Jan 13 17:10 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 645 Jan 13 17:10 xtrabackup_info
-rw-r----- 1 root root 1 Jan 13 17:10 xtrabackup_master_key_id
-rw-r----- 1 root root 0 Jan 13 17:10 xtrabackup_slave_info
修改属组
# chown -R mysql:mysql /data/mysql/data/验证数据
启动mysql
# systemctl restart mysqld查询数据
mysql> select * from messages;
+---------+
| message |
+---------+
| hello |
+---------+
1 row in set (0.00 sec)
tar.gz
备份数据
# xtrabackup --backup --slave-info --host="localhost" --port=3306 --user="bkpuser" --password="s3cret" --stream=tar --socket="/data/mysql/mysql.sock" --parallel=2 --target-dir="/data/backups/" |gzip - > /data/backups/xtra_full_`date +%Y%m%d%H%M%S`.tar.gz
xtrabackup: recognized server arguments: --parallel=2
xtrabackup: recognized client arguments: --backup=1 --slave-info=1 --host=localhost --port=3306 --user=bkpuser --password=* --stream=tar --socket=/data/mysql/mysql.sock --target-dir=/data/backups/
230113 17:25:34 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.
230113 17:25:34 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)
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
xtrabackup: warning: the --parallel option does not have any effect when streaming in the 'tar' format. You can use the 'xbstream' format instead.
230113 17:25:34 >> log scanned up to (2796100)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 33 for test/messages, old maximum was 0
230113 17:25:34 [01] Streaming ./ibdata1
230113 17:25:34 [01] ...done
...
230113 17:25:35 [01] Streaming ./sys/x@0024ps_digest_95th_percentile_by_avg_us.frm to <STDOUT>
230113 17:25:35 [01] ...done
230113 17:25:35 Finished backing up non-InnoDB tables and files
230113 17:25:35 [00] Streaming <STDOUT>
230113 17:25:35 [00] ...done
230113 17:25:35 [00] Streaming <STDOUT>
230113 17:25:35 [00] ...done
230113 17:25:35 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '2796091'
xtrabackup: Stopping log copying thread.
.230113 17:25:35 >> log scanned up to (2796100)
230113 17:25:35 Executing UNLOCK TABLES
230113 17:25:35 All tables unlocked
230113 17:25:35 [00] Streaming ib_buffer_pool to <STDOUT>
230113 17:25:35 [00] ...done
230113 17:25:35 Backup created in directory '/data/backups/'
MySQL binlog position: filename 'mysql-bin.000011', position '154'
230113 17:25:35 [00] Streaming <STDOUT>
230113 17:25:35 [00] ...done
230113 17:25:35 [00] Streaming <STDOUT>
230113 17:25:35 [00] ...done
xtrabackup: Transaction log of lsn (2796091) to (2796100) was copied.
230113 17:25:36 completed OK!
查看备份数据
# ls -lh xtra_full_20230113172534.tar.gz
-rw-r--r-- 1 root root 670K Jan 13 17:25 xtra_full_20230113172534.tar.gz
解压tar.gz文件
创建解压目录
# mkdir /data/backups/tar-data解压文件
# tar xf xtra_full_20230113172534.tar.gz -C tar-data/查看解压文件
# ls -l tar-data/
total 12336
-rw-rw---- 1 root root 487 Jan 13 17:25 backup-my.cnf
-rw-rw---- 1 root root 436 Jan 13 17:10 ib_buffer_pool
-rw-rw---- 1 root root 12582912 Jan 13 17:14 ibdata1
drwxr-xr-x 2 root root 4096 Jan 13 17:28 mysql
drwxr-xr-x 2 root root 4096 Jan 13 17:28 performance_schema
drwxr-xr-x 2 root root 12288 Jan 13 17:28 sys
drwxr-xr-x 2 root root 4096 Jan 13 17:28 test
-rw-rw---- 1 root root 21 Jan 13 17:25 xtrabackup_binlog_info
-rw-rw---- 1 root root 135 Jan 13 17:25 xtrabackup_checkpoints
-rw-rw---- 1 root root 594 Jan 13 17:25 xtrabackup_info
-rw-rw---- 1 root root 2560 Jan 13 17:25 xtrabackup_logfile
-rw-rw---- 1 root root 0 Jan 13 17:25 xtrabackup_slave_info
Preparing the backup
# xtrabackup --prepare --target-dir=/data/backups/tar-data/
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 --target-dir=/data/backups/tar-data/
xtrabackup version 2.4.27 based on MySQL server 5.7.40 Linux (x86_64) (revision id: aae8e82)
xtrabackup: cd to /data/backups/tar-data/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2796091)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
...
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 791, file name mysql-bin.000009
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.40 started; log sequence number 2796565
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2796584
230113 17:31:16 completed OK!
还原数据
删除测试数据
mysql> drop table messages;
Query OK, 0 rows affected (0.00 sec)
停止mysql服务
# systemctl stop mysqldcopy-back
# xtrabackup --copy-back --target-dir=/data/backups/tar-data/ --datadir=/data/backups/tar-data-backs
xtrabackup: recognized server arguments: --datadir=/data/backups/tar-data-backs
xtrabackup: recognized client arguments: --copy-back=1 --target-dir=/data/backups/tar-data/
xtrabackup version 2.4.27 based on MySQL server 5.7.40 Linux (x86_64) (revision id: aae8e82)
230113 17:34:08 [01] Copying ib_logfile0 to /data/backups/tar-data-backs/ib_logfile0
230113 17:34:08 [01] ...done
230113 17:34:08 [01] Copying ib_logfile1 to /data/backups/tar-data-backs/ib_logfile1
230113 17:34:08 [01] ...done
230113 17:34:08 [01] Copying ibdata1 to /data/backups/tar-data-backs/ibdata1
230113 17:34:08 [01] ...done
...
230113 17:34:09 [01] Copying ./sys/statement_analysis.frm to /data/backups/tar-data-backs/sys/statement_analysis.frm
230113 17:34:09 [01] ...done
230113 17:34:09 [01] Copying ./sys/waits_by_host_by_latency.frm to /data/backups/tar-data-backs/sys/waits_by_host_by_latency.frm
230113 17:34:09 [01] ...done
230113 17:34:09 [01] Copying ./sys/x@0024ps_digest_95th_percentile_by_avg_us.frm to /data/backups/tar-data-backs/sys/x@0024ps_digest_95th_percentile_by_avg_us.frm
230113 17:34:09 [01] ...done
230113 17:34:09 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/backups/tar-data-backs/xtrabackup_binlog_pos_innodb
230113 17:34:09 [01] ...done
230113 17:34:09 completed OK!
同步数据
# rsync -avrP /data/backups/tar-data-backs/ /data/mysql/data/查看mysql数据目录
# ls -l /data/mysql/data/
total 122956
-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 17:10 ib_buffer_pool
-rw-r----- 1 root root 12582912 Jan 13 17:10 ibdata1
-rw-r----- 1 root root 50331648 Jan 13 17:10 ib_logfile0
-rw-r----- 1 root root 50331648 Jan 13 17:10 ib_logfile1
-rw-r----- 1 root root 12582912 Jan 13 17:10 ibtmp1
drwxr-x--- 2 root root 4096 Jan 13 17:10 mysql
drwxr-x--- 2 root root 4096 Jan 13 17:10 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 17:10 sys
drwxr-x--- 2 root root 4096 Jan 13 17:10 test
-rw-r----- 1 root root 21 Jan 13 17:10 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 645 Jan 13 17:10 xtrabackup_info
-rw-r----- 1 root root 1 Jan 13 17:10 xtrabackup_master_key_id
-rw-r----- 1 root root 0 Jan 13 17:10 xtrabackup_slave_info
修改属组
# chown -R mysql:mysql /data/mysql/data/验证数据
启动mysql
# systemctl restart mysqld查询数据
mysql> select * from messages;
+---------+
| message |
+---------+
| hello |
+---------+
1 row in set (0.00 sec)
