环境说明
主机说明
宿主机：192.168.1.219
备份机：192.168.2.235
备份要求
宿主机可以免密登录备份机
备份机器提前创建备份目录
# ssh [email protected] "mkdir -pv /data/mysql-backs"
mkdir: created directory '/data/mysql-backs'
创建备份用户
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
备份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/" | ssh [email protected] "gzip - > /data/mysql-backs/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/
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 18:07:02 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 18:07:02 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 18:07:02 >> log scanned up to (2796612)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 33 for test/messages, old maximum was 0
230113 18:07:02 [01] Streaming ./ibdata1
230113 18:07:02 [01] ...done
230113 18:07:02 [01] Streaming ./test/messages.ibd
230113 18:07:02 [01] ...done
...
230113 18:07:03 [01] Streaming ./sys/x@0024ps_digest_95th_percentile_by_avg_us.frm to <STDOUT>
230113 18:07:03 [01] ...done
230113 18:07:03 Finished backing up non-InnoDB tables and files
230113 18:07:03 [00] Streaming <STDOUT>
230113 18:07:03 [00] ...done
230113 18:07:03 [00] Streaming <STDOUT>
230113 18:07:03 [00] ...done
230113 18:07:03 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '2796603'
xtrabackup: Stopping log copying thread.
.230113 18:07:03 >> log scanned up to (2796612)
230113 18:07:03 Executing UNLOCK TABLES
230113 18:07:03 All tables unlocked
230113 18:07:03 [00] Streaming ib_buffer_pool to <STDOUT>
230113 18:07:03 [00] ...done
230113 18:07:03 Backup created in directory '/data/backups/'
MySQL binlog position: filename 'mysql-bin.000012', position '154'
230113 18:07:03 [00] Streaming <STDOUT>
230113 18:07:03 [00] ...done
230113 18:07:03 [00] Streaming <STDOUT>
230113 18:07:03 [00] ...done
xtrabackup: Transaction log of lsn (2796603) to (2796612) was copied.
230113 18:07:03 completed OK!
查看宿主机备份目录
# ls -l /data/backups/
total 0
查看备份机备份目录
# ssh [email protected] "ls -lh /data/mysql-backs"
total 672K
-rw-r--r-- 1 root root 670K Jan 13 18:07 xtra_full_20230113180702.tar.gz
验证备份包数据
复制备份包到宿主机
# scp [email protected]:/data/mysql-backs/xtra_full_20230113180702.tar.gz .
创建解压目录
# mkdir /data/backups/tar-data
解压备份包
# tar xf xtra_full_20230113180702.tar.gz -C tar-data/
查看解压文件
# ls -lh tar-data/
total 13M
-rw-rw---- 1 root root 487 Jan 13 18:07 backup-my.cnf
-rw-rw---- 1 root root 436 Jan 13 17:34 ib_buffer_pool
-rw-rw---- 1 root root 12M Jan 13 17:37 ibdata1
drwxr-xr-x 2 root root 4.0K Jan 13 18:16 mysql
drwxr-xr-x 2 root root 4.0K Jan 13 18:16 performance_schema
drwxr-xr-x 2 root root 12K Jan 13 18:16 sys
drwxr-xr-x 2 root root 4.0K Jan 13 18:16 test
-rw-rw---- 1 root root 21 Jan 13 18:07 xtrabackup_binlog_info
-rw-rw---- 1 root root 135 Jan 13 18:07 xtrabackup_checkpoints
-rw-rw---- 1 root root 594 Jan 13 18:07 xtrabackup_info
-rw-rw---- 1 root root 2.5K Jan 13 18:07 xtrabackup_logfile
-rw-rw---- 1 root root 0 Jan 13 18:07 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=(2796603)
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
...
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 2797077
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2797096
230113 18:17:57 completed OK!
删除测试数据
mysql> drop table messages;
Query OK, 0 rows affected (0.00 sec)
停止mysql服务
# systemctl stop mysqld
copy-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 18:20:00 [01] Copying ib_logfile0 to /data/backups/tar-data-backs/ib_logfile0
230113 18:20:00 [01] ...done
...
230113 18:20:01 [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 18:20:01 [01] ...done
230113 18:20:01 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/backups/tar-data-backs/xtrabackup_binlog_pos_innodb
230113 18:20:01 [01] ...done
230113 18:20:01 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)
备份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/" |ssh [email protected] "gzip - > /data/mysql-backs/xtra_full_`date +%Y%m%d%H%M%S`_qp.xb.gz"
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 18:47:05 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 18:47:05 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 18:47:05 >> log scanned up to (2797636)
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 18:47:05 [01] Compressing and streaming ./ibdata1
...
230113 18:47:06 [01] Compressing and streaming ./sys/x@0024ps_digest_95th_percentile_by_avg_us.frm to <STDOUT>
230113 18:47:06 [01] ...done
230113 18:47:06 Finished backing up non-InnoDB tables and files
230113 18:47:06 [00] Compressing and streaming <STDOUT>
230113 18:47:06 [00] ...done
230113 18:47:06 [00] Compressing and streaming <STDOUT>
230113 18:47:06 [00] ...done
230113 18:47:06 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '2797627'
xtrabackup: Stopping log copying thread.
.230113 18:47:06 >> log scanned up to (2797636)
230113 18:47:06 Executing UNLOCK TABLES
230113 18:47:06 All tables unlocked
230113 18:47:06 [00] Compressing and streaming ib_buffer_pool to <STDOUT>
230113 18:47:06 [00] ...done
230113 18:47:06 Backup created in directory '/data/backups/'
MySQL binlog position: filename 'mysql-bin.000017', position '154'
230113 18:47:06 [00] Compressing and streaming <STDOUT>
230113 18:47:06 [00] ...done
230113 18:47:06 [00] Compressing and streaming <STDOUT>
230113 18:47:06 [00] ...done
xtrabackup: Transaction log of lsn (2797627) to (2797636) was copied.
230113 18:47:06 completed OK!
查看宿主机备份目录
# ls -l /data/backups/
total 0
查看备份机备份目录
# ssh [email protected] "ls -lh /data/mysql-backs"
total 780K
-rw-r--r-- 1 root root 778K Jan 13 18:47 xtra_full_20230113184705_qp.xb.gz
验证备份包数据
复制备份包到宿主机
# scp [email protected]:/data/mysql-backs/xtra_full_20230113184705_qp.xb.gz .
创建解压目录
# mkdir /data/backups/qp-xbs
gz解压
# gzip -d xtra_full_20230113184705_qp.xb.gz
# ls -l
total 1320
-rw-r--r-- 1 root root 1350013 Jan 13 18:50 xtra_full_20230113184705_qp.xb
解压xbstream文件
# cat xtra_full_20230113184705_qp.xb | xbstream -x --parallel=2 -C /data/backups/qp-data/
查看解压文件
# ls -l qp-data/
total 548
-rw-r----- 1 root root 475 Jan 13 18:58 backup-my.cnf.qp
-rw-r----- 1 root root 388 Jan 13 18:58 ib_buffer_pool.qp
-rw-r----- 1 root root 505530 Jan 13 18:58 ibdata1.qp
drwxr-x--- 2 root root 4096 Jan 13 18:58 mysql
drwxr-x--- 2 root root 4096 Jan 13 18:58 performance_schema
drwxr-x--- 2 root root 12288 Jan 13 18:58 sys
drwxr-x--- 2 root root 4096 Jan 13 18:58 test
-rw-r----- 1 root root 108 Jan 13 18:58 xtrabackup_binlog_info.qp
-rw-r----- 1 root root 135 Jan 13 18:58 xtrabackup_checkpoints
-rw-r----- 1 root root 593 Jan 13 18:58 xtrabackup_info.qp
-rw-r----- 1 root root 295 Jan 13 18:58 xtrabackup_logfile.qp
-rw-r----- 1 root root 59 Jan 13 18:58 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 mysqld
copy-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)
备份xbstream并解压
备份节点需要安装xbstream
创建备份目录
# ssh [email protected] "mkdir -pv /data/mysql-backs/xtra_full_xbs"
mkdir: created directory '/data/mysql-backs/xtra_full_xbs'
备份数据
# 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/" | ssh [email protected] "/usr/local/xtrabackup/bin/xbstream -x -v -C /data/mysql-backs/xtra_full_xbs"
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 19:28:42 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 19:28:42 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 19:28:42 >> log scanned up to (2798148)
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 19:28:43 [01] Streaming ./ibdata1
...
sys/statement_analysis.frm
sys/waits_by_host_by_latency.frm
sys/x@0024ps_digest_95th_percentile_by_avg_us.frm
xtrabackup_slave_info
xtrabackup_binlog_info
230113 19:28:44 Executing UNLOCK TABLES
230113 19:28:44 All tables unlocked
230113 19:28:44 [00] Streaming ib_buffer_pool to <STDOUT>
230113 19:28:44 [00] ...done
230113 19:28:44 Backup created in directory '/data/backups/'
MySQL binlog position: filename 'mysql-bin.000018', position '154'
230113 19:28:44 [00] Streaming <STDOUT>
230113 19:28:44 [00] ...done
230113 19:28:44 [00] Streaming <STDOUT>
230113 19:28:44 [00] ...done
xtrabackup: Transaction log of lsn (2798139) to (2798148) was copied.
xtrabackup_logfile
xtrabackup_checkpoints
ib_buffer_pool
backup-my.cnf
xtrabackup_info
230113 19:28:44 completed OK!
查看宿主机目录
# ls -l /data/backups/
total 0
查看备份目录
# ssh [email protected] "ls -l /data/mysql-backs/xtra_full_xbs"
total 12340
-rw-r----- 1 root root 487 Jan 13 19:28 backup-my.cnf
-rw-r----- 1 root root 436 Jan 13 19:28 ib_buffer_pool
-rw-r----- 1 root root 12582912 Jan 13 19:28 ibdata1
drwxr-x--- 2 root root 4096 Jan 13 19:28 mysql
drwxr-x--- 2 root root 4096 Jan 13 19:28 performance_schema
drwxr-x--- 2 root root 12288 Jan 13 19:28 sys
drwxr-x--- 2 root root 4096 Jan 13 19:28 test
-rw-r----- 1 root root 21 Jan 13 19:28 xtrabackup_binlog_info
-rw-r----- 1 root root 135 Jan 13 19:28 xtrabackup_checkpoints
-rw-r----- 1 root root 604 Jan 13 19:28 xtrabackup_info
-rw-r----- 1 root root 2560 Jan 13 19:28 xtrabackup_logfile
-rw-r----- 1 root root 0 Jan 13 19:28 xtrabackup_slave_info
Preparing the backup
# xtrabackup --prepare --target-dir=/data/mysql-backs/xtra_full_xbs
copy-back
# xtrabackup --copy-back --target-dir=/data/mysql-backs/xtra_full_xbs --datadir=/data/mysql-backs/xtra_full_xbs-backs
删除测试数据
mysql> drop table messages;
Query OK, 0 rows affected (0.00 sec)
停止mysql服务
# systemctl stop mysqld
同步数据
# rsync -avrP /data/mysql-backs/xtra_full_xbs-backs /data/mysql/data/
修改属组
# chown -R mysql:mysql /data/mysql/data/
启动mysql
# systemctl restart mysqld
查询数据
mysql> select * from messages;
+---------+
| message |
+---------+
| hello |
+---------+
1 row in set (0.00 sec)