创建备份用户
mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cret';
mysql> GRANT SELECT, 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/`date +%Y-%m-%d_%H-%M%S`"
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/2023-01-12_17-4302
230112 17:43: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.
230112 17:43: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
230112 17:43:02 >> log scanned up to (2768562)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 24 for test/messages, old maximum was 0
xtrabackup: Starting 2 threads for parallel data files transfer
230112 17:43:02 [01] Copying ./ibdata1 to /data/xtrabackup/2023-01-12_17-4302/ibdata1
230112 17:43:02 [02] Copying ./test/messages.ibd to /data/xtrabackup/2023-01-12_17-4302/test/messages.ibd
230112 17:43:02 [02] ...done
....
230112 17:43:04 [01] Copying ./sys/x@0024ps_digest_95th_percentile_by_avg_us.frm to /data/xtrabackup/2023-01-12_17-4302/sys/x@0024ps_digest_95th_percentile_by_avg_us.frm
230112 17:43:04 [01] ...done
230112 17:43:04 Finished backing up non-InnoDB tables and files
230112 17:43:04 [00] Writing /data/xtrabackup/2023-01-12_17-4302/xtrabackup_slave_info
230112 17:43:04 [00] ...done
230112 17:43:04 [00] Writing /data/xtrabackup/2023-01-12_17-4302/xtrabackup_binlog_info
230112 17:43:04 [00] ...done
230112 17:43:04 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '2768553'
xtrabackup: Stopping log copying thread.
.230112 17:43:04 >> log scanned up to (2768562)
230112 17:43:04 Executing UNLOCK TABLES
230112 17:43:04 All tables unlocked
230112 17:43:04 [00] Copying ib_buffer_pool to /data/xtrabackup/2023-01-12_17-4302/ib_buffer_pool
230112 17:43:04 [00] ...done
230112 17:43:04 Backup created in directory '/data/xtrabackup/2023-01-12_17-4302/'
MySQL binlog position: filename 'mysql-bin.000003', position '2875'
230112 17:43:04 [00] Writing /data/xtrabackup/2023-01-12_17-4302/backup-my.cnf
230112 17:43:04 [00] ...done
230112 17:43:04 [00] Writing /data/xtrabackup/2023-01-12_17-4302/xtrabackup_info
230112 17:43:04 [00] ...done
xtrabackup: Transaction log of lsn (2768553) to (2768562) was copied.
230112 17:43:04 completed OK!
查看备份目录
# ls -l 2023-01-12_17-4302/
total 12336
-rw-r----- 1 root root 487 Jan 12 17:45 backup-my.cnf
-rw-r----- 1 root root 436 Jan 12 17:45 ib_buffer_pool
-rw-r----- 1 root root 12582912 Jan 12 17:45 ibdata1
drwxr-x--- 2 root root 4096 Jan 12 17:45 mysql
drwxr-x--- 2 root root 4096 Jan 12 17:45 performance_schema
drwxr-x--- 2 root root 12288 Jan 12 17:45 sys
drwxr-x--- 2 root root 4096 Jan 12 17:45 test
-rw-r----- 1 root root 22 Jan 12 17:45 xtrabackup_binlog_info
-rw-r----- 1 root root 135 Jan 12 17:45 xtrabackup_checkpoints
-rw-r----- 1 root root 604 Jan 12 17:45 xtrabackup_info
-rw-r----- 1 root root 2560 Jan 12 17:45 xtrabackup_logfile
-rw-r----- 1 root root 0 Jan 12 17:45 xtrabackup_slave_info
查看备份内容
xtrabackup_binlog_info
# cat 2023-01-12_17-4302/xtrabackup_binlog_info
mysql-bin.000003 2875
xtrabackup_info
# cat 2023-01-12_17-4302/xtrabackup_info
uuid = ddb43fa7-925d-11ed-a379-52540045f9cf
name =
tool_name = xtrabackup
tool_command = --backup --slave-info --host=localhost --port=3306 --user=bkpuser --password=... --socket=/data/mysql/mysql.sock --parallel=2 --target-dir=/data/xtrabackup/2023-01-12_17-4535
tool_version = 2.4.27
ibbackup_version = 2.4.27
server_version = 5.7.40-log
start_time = 2023-01-12 17:45:35
end_time = 2023-01-12 17:45:36
lock_time = 0
binlog_pos = filename 'mysql-bin.000003', position '2875'
innodb_from_lsn = 0
innodb_to_lsn = 2768553
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N
xtrabackup_checkpoints
# cat 2023-01-12_17-4302/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 2768553
last_lsn = 2768562
compact = 0
recover_binlog_info = 0
flushed_lsn = 2768562
Prepare a backup
# xtrabackup --prepare --target-dir=/data/xtrabackup/2023-01-12_17-4302
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/xtrabackup/2023-01-12_17-4302
xtrabackup version 2.4.27 based on MySQL server 5.7.40 Linux (x86_64) (revision id: aae8e82)
xtrabackup: cd to /data/xtrabackup/2023-01-12_17-4302/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2768553)
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 2768553
InnoDB: Doing recovery: scanned up to log sequence number 2768562 (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: 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 2768562
InnoDB: xtrabackup: Last MySQL binlog file position 2875, file name mysql-bin.000003
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2768581
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=2768581
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 2768908
InnoDB: Doing recovery: scanned up to log sequence number 2768917 (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: 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 2768917
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2768936
230112 17:57:37 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/xtrabackup/2023-01-12_17-4302/ --datadir=/data/xtrabackup/backs
xtrabackup: recognized server arguments: --datadir=/data/xtrabackup/backs
xtrabackup: recognized client arguments: --copy-back=1 --target-dir=/data/xtrabackup/2023-01-12_17-4302/
xtrabackup version 2.4.27 based on MySQL server 5.7.40 Linux (x86_64) (revision id: aae8e82)
230112 18:08:28 [01] Copying ib_logfile0 to /data/xtrabackup/backs/ib_logfile0
230112 18:08:28 [01] ...done
230112 18:08:28 [01] Copying ib_logfile1 to /data/xtrabackup/backs/ib_logfile1
230112 18:08:28 [01] ...done
....
230112 18:08:29 [01] Copying ./sys/x@0024ps_digest_95th_percentile_by_avg_us.frm to /data/xtrabackup/backs/sys/x@0024ps_digest_95th_percentile_by_avg_us.frm
230112 18:08:29 [01] ...done
230112 18:08:29 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/xtrabackup/backs/xtrabackup_binlog_pos_innodb
230112 18:08:29 [01] ...done
230112 18:08:29 completed OK!
同步数据
# rsync -avrP /data/xtrabackup/backs/ /data/mysql/data/
sending incremental file list
./
ib_buffer_pool
436 100% 0.00kB/s 0:00:00 (xfr#1, to-chk=285/287)
ib_logfile0
....
查看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)
