背景：生产环境数据采用xtrabackup全量备份数据库，在某些场景下需要进行单个数据库恢复。
以下为模拟测试用例：
创建测试用表：
[root@localhost][(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) --创建测试用数据库test01: [root@localhost][(none)]> create database test01; Query OK, 1 row affected (0.12 sec) [root@localhost][(none)]> use test01; Database changed [root@localhost][test01]> create table test01(id int primary key,name varchar(32)); Query OK, 0 rows affected (0.37 sec) [root@localhost][test01]> insert into test01 values(1,'a'); Query OK, 1 row affected (0.11 sec) [root@localhost][test01]> insert into test01 values(2,'b'); Query OK, 1 row affected (0.00 sec) [root@localhost][test01]> insert into test01 values(3,'c'); Query OK, 1 row affected (0.01 sec) [root@localhost][test01]> select * from test01; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | +----+------+ 3 rows in set (0.00 sec) --创建用测试数据库test02: [root@localhost][test01]> create table test02(id int primary key,name varchar(32)); Query OK, 0 rows affected (0.21 sec) [root@localhost][test01]> insert into test02 values(1,'a'); Query OK, 1 row affected (0.01 sec) [root@localhost][test01]> drop table test02; Query OK, 0 rows affected (0.04 sec) [root@localhost][test01]> create database test02; Query OK, 1 row affected (0.12 sec) [root@localhost][test01]> use test02; Database changed [root@localhost][test02]> create table test02(id int primary key,name varchar(32)); Query OK, 0 rows affected (0.16 sec) [root@localhost][test02]> insert into test02 values(1,'a'); Query OK, 1 row affected (0.02 sec) [root@localhost][test02]> select * from test02; +----+------+ | id | name | +----+------+ | 1 | a | +----+------+ 1 row in set (0.00 sec)
进行数据库加密备份操作：
数据库加密分为密钥和密钥文件两种方式进行，笔者以密钥文件作为演示进行：
生成密钥：
[root@node01 backup]# echo "123456" |md5sum f447b20a7fcbf53a5d5be013ea0b15af - [root@node01 backup]# --将密钥写入密钥文件中： echo -n "f447b20a7fcbf53a5d5be013ea0b15af" > /tmp/crypt.key 注意：此处不要使用vi编辑文件输入，否则后期会提示密钥长度错误的问题。
备份数据库
备份脚本： xtrabackup --parallel=4 --host=127.0.0.1 --user=backup --password=backup --port=3306 --encrypt=AES256 --encrypt-key-file='/tmp/crypt.key' --stream=xbstream --compress --backup --target-dir=/data/mysqldata/backup >> /data/mysqldata/backup/test01.xbstream --备份开始日志信息： xtrabackup: [Warning] option 'innodb_write_io_threads': signed value 2 adjusted to 4. xtrabackup: [Warning] option 'innodb_read_io_threads': signed value 2 adjusted to 4. 2024-08-22T15:16:19.877832+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/data/mysqldata/data --server-id=319 --log_bin=mysqld-bin --innodb_buffer_pool_size=4G --innodb_io_capacity=20000 --innodb_flush_method=O_DIRECT --innodb_log_file_size=1073741824 --innodb_log_files_in_group=2 --innodb_log_buffer_size=16777216 --innodb_write_io_threads=2 --innodb_read_io_threads=2 --innodb_file_per_table=1 --innodb_open_files=4096 2024-08-22T15:16:19.878014+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --host=localhost --port=3306 --user=root --password=* --parallel=4 --host=127.0.0.1 --user=backup --password=* --port=3306 --encrypt=AES256 --encrypt-key-file=/tmp/crypt.key --stream=xbstream --compress --backup=1 --target-dir=/data/mysqldata/backup xtrabackup version 8.0.35-31 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 55ec21d7) 240822 15:16:19 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=127.0.0.1;port=3306' as 'backup' (using password: YES). 240822 15:16:19 version_check Connected to MySQL server 240822 15:16:19 version_check Executing a version check against the server... 240822 15:16:19 version_check Done. 2024-08-22T15:16:19.986257+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: 127.0.0.1, user: backup, password: set, port: 3306, socket: not set 2024-08-22T15:16:20.000180+08:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.28 2024-08-22T15:16:20.003295+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ... 2024-08-22T15:16:20.108266+08:00 0 [Note] [MY-011825] [Xtrabackup] uses posix_fadvise(). 2024-08-22T15:16:20.108309+08:00 0 [Note] [MY-011825] [Xtrabackup] cd to /data/mysqldata/data 2024-08-22T15:16:20.108325+08:00 0 [Note] [MY-011825] [Xtrabackup] open files limit requested 0, set to 1024 2024-08-22T15:16:20.109398+08:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration: 2024-08-22T15:16:20.109432+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = . 2024-08-22T15:16:20.109440+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend 2024-08-22T15:16:20.109474+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = ./ 2024-08-22T15:16:20.109481+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 2 2024-08-22T15:16:20.109492+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 1073741824 2024-08-22T15:16:20.109517+08:00 0 [Note] [MY-011825] [Xtrabackup] using O_DIRECT 2024-08-22T15:16:20.112172+08:00 0 [Note] [MY-011825] [Xtrabackup] inititialize_service_handles suceeded 2024-08-22T15:16:20.234808+08:00 0 [Note] [MY-012529] [InnoDB] Redo log format is v4. The redo log was created before MySQL 8.0.30. 2024-08-22T15:16:20.237211+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: 127.0.0.1, user: backup, password: set, port: 3306, socket: not set 2024-08-22T15:16:20.247751+08:00 0 [Note] [MY-011825] [Xtrabackup] Redo Log Archiving is not set up. 2024-08-22T15:16:20.352702+08:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (21457863) 2024-08-22T15:16:20.355356+08:00 0 [Note] [MY-012953] [InnoDB] Disabling background ibuf IO read threads. 2024-08-22T15:16:20.716221+08:00 0 [Note] [MY-011825] [Xtrabackup] Generating a list of tablespaces 2024-08-22T15:16:20.716312+08:00 0 [Note] [MY-012204] [InnoDB] Scanning './' 2024-08-22T15:16:20.748131+08:00 0 [Note] [MY-012208] [InnoDB] Completed space ID check of 2 files. 2024-08-22T15:16:20.786124+08:00 0 [Warning] [MY-012091] [InnoDB] Allocated tablespace ID 52 for test02/test02, old maximum was 0 2024-08-22T15:16:20.819897+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_001'. 2024-08-22T15:16:20.823721+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_002'. 2024-08-22T15:16:20.826693+08:00 0 [Note] [MY-012910] [InnoDB] Opened 2 existing undo tablespaces. 2024-08-22T15:16:21.262920+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Compressing, encrypting and streaming ./sys/sys_config.ibd 2024-08-22T15:16:21.356715+08:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (21457863) 2024-08-22T15:16:21.806330+08:00 3 [Note] [MY-011825] [Xtrabackup] Compressing, encrypting and streaming ./undo_001 2024-08-22T15:16:22.026112+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Compressing, encrypting and streaming ./ibdata1 2024-08-22T15:16:22.361174+08:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (21457863) 省略部分中间日志信息...... 2024-08-22T15:16:23.647859+08:00 0 [Note] [MY-011825] [Xtrabackup] Finished backing up non-InnoDB tables and files 2024-08-22T15:16:23.647903+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS 2024-08-22T15:16:24.768901+08:00 0 [Note] [MY-011825] [Xtrabackup] Transaction log of lsn (21457863) to (21457873) was copied. 2024-08-22T15:16:24.993429+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
备份后文件信息
[root@node01 backup]# ls -rtl total 1772 -rw-r--r-- 1 root root 1814085 Aug 22 15:16 test01.xbstream [root@node01 backup]# du -sh * 1.8M test01.xbstream
设定场景为，需要特定恢复test01数据库数据，后进行导出。
流复制解压 xbstream
xbstream -x < test01.xbstream -C /data/mysqldata/backup/ 解压后信息： [root@node01 backup]# xbstream -x < test01.xbstream -C /data/mysqldata/backup/ [root@node01 backup]# [root@node01 backup]# [root@node01 backup]# ls -rtl total 3424 -rw-r--r-- 1 root root 1814085 Aug 22 15:16 test01.xbstream drwxr-x--- 2 root root 36 Aug 22 15:20 test01 drwxr-x--- 2 root root 36 Aug 22 15:20 test02 drwxr-x--- 2 root root 40 Aug 22 15:20 sys -rw-r----- 1 root root 4052 Aug 22 15:20 ibdata1.zst.xbcrypt -rw-r----- 1 root root 128378 Aug 22 15:20 undo_002.zst.xbcrypt -rw-r----- 1 root root 1368443 Aug 22 15:20 mysql.ibd.zst.xbcrypt -rw-r----- 1 root root 132516 Aug 22 15:20 undo_001.zst.xbcrypt drwxr-x--- 2 root root 32 Aug 22 15:20 #innodb_redo drwxr-x--- 2 root root 215 Aug 22 15:20 mysql drwxr-x--- 2 root root 8192 Aug 22 15:20 performance_schema -rw-r----- 1 root root 205 Aug 22 15:20 mysqld-bin.000025.zst.xbcrypt -rw-r----- 1 root root 125 Aug 22 15:20 mysqld-bin.index.zst.xbcrypt -rw-r----- 1 root root 127 Aug 22 15:20 xtrabackup_binlog_info.zst.xbcrypt -rw-r----- 1 root root 427 Aug 22 15:20 xtrabackup_logfile.zst.xbcrypt -rw-r----- 1 root root 134 Aug 22 15:20 xtrabackup_checkpoints -rw-r----- 1 root root 666 Aug 22 15:20 ib_buffer_pool.zst.xbcrypt -rw-r----- 1 root root 397 Aug 22 15:20 backup-my.cnf.zst.xbcrypt -rw-r----- 1 root root 472 Aug 22 15:20 xtrabackup_info.zst.xbcrypt -rw-r----- 1 root root 144 Aug 22 15:20 xtrabackup_tablespaces.zst.xbcrypt
zstd解压缩
xtrabackup --defaults-file=/etc/my.cnf --decompress --parallel=4 --decrypt=AES256 --encrypt-key="f447b20a7fcbf53a5d5be013ea0b15af" --target-dir=/data/mysqldata/backup/ --remove-original 日志信息： [root@node01 tmp]# xtrabackup --defaults-file=/etc/my.cnf --decompress --parallel=4 --decrypt=AES256 --encrypt-key="f447b20a7fcbf53a5d5be013ea0b15af" --target-dir=/data/mysqldata/backup/ --remove-original xtrabackup: [Warning] option 'innodb_write_io_threads': signed value 2 adjusted to 4. xtrabackup: [Warning] option 'innodb_read_io_threads': signed value 2 adjusted to 4. 2024-08-22T15:47:07.057987+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/data/mysqldata/data --server-id=319 --log_bin=mysqld-bin --innodb_buffer_pool_size=4G --innodb_io_capacity=20000 --innodb_flush_method=O_DIRECT --innodb_log_file_size=1073741824 --innodb_log_files_in_group=2 --innodb_log_buffer_size=16777216 --innodb_write_io_threads=2 --innodb_read_io_threads=2 --innodb_file_per_table=1 --innodb_open_files=4096 2024-08-22T15:47:07.058401+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --host=localhost --port=3306 --user=root --password=* --decompress=1 --parallel=4 --decrypt=AES256 --encrypt-key=* --target-dir=/data/mysqldata/backup/ --remove-original=1 xtrabackup version 8.0.35-31 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 55ec21d7) 2024-08-22T15:47:07.062155+08:00 0 [Note] [MY-011825] [Xtrabackup] decrypting and decompressing ./test01/test01.ibd.zst.xbcrypt 2024-08-22T15:47:07.065218+08:00 0 [Note] [MY-011825] [Xtrabackup] decrypting and decompressing ./test02/test02.ibd.zst.xbcrypt 2024-08-22T15:47:07.066570+08:00 0 [Note] [MY-011825] [Xtrabackup] decrypting and decompressing ./sys/sys_config.ibd.zst.xbcrypt 2024-08-22T15:47:07.068210+08:00 0 [Note] [MY-011825] [Xtrabackup] decrypting and decompressing ./ibdata1.zst.xbcrypt 2024-08-22T15:47:07.175552+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./test01/test01.ibd.zst.xbcrypt 2024-08-22T15:47:07.191849+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./sys/sys_config.ibd.zst.xbcrypt 2024-08-22T15:47:07.200585+08:00 0 [Note] [MY-011825] [Xtrabackup] decrypting and decompressing ./undo_002.zst.xbcrypt 2024-08-22T15:47:07.203302+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./test02/test02.ibd.zst.xbcrypt 2024-08-22T15:47:07.206364+08:00 0 [Note] [MY-011825] [Xtrabackup] decrypting and decompressing ./undo_001.zst.xbcrypt 2024-08-22T15:47:07.220600+08:00 0 [Note] [MY-011825] [Xtrabackup] decrypting and decompressing ./mysql.ibd.zst.xbcrypt 2024-08-22T15:47:07.381059+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./ibdata1.zst.xbcrypt .....省略部分日志 2024-08-22T15:47:09.541395+08:00 0 [Note] [MY-011825] [Xtrabackup] decrypting and decompressing ./backup-my.cnf.zst.xbcrypt 2024-08-22T15:47:09.549446+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./xtrabackup_binlog_info.zst.xbcrypt 2024-08-22T15:47:09.551907+08:00 0 [Note] [MY-011825] [Xtrabackup] decrypting and decompressing ./xtrabackup_info.zst.xbcrypt 2024-08-22T15:47:09.577967+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./xtrabackup_logfile.zst.xbcrypt 2024-08-22T15:47:09.578263+08:00 0 [Note] [MY-011825] [Xtrabackup] decrypting and decompressing ./xtrabackup_tablespaces.zst.xbcrypt 2024-08-22T15:47:09.594816+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./ib_buffer_pool.zst.xbcrypt 2024-08-22T15:47:09.607733+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./backup-my.cnf.zst.xbcrypt 2024-08-22T15:47:09.609938+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./xtrabackup_info.zst.xbcrypt 2024-08-22T15:47:09.619784+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./xtrabackup_tablespaces.zst.xbcrypt 2024-08-22T15:47:09.675816+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK! 解压后文件信息： [root@node01 tmp]# ls -rtl total 16 drwxrwxr-x. 4 1001 1001 39 Jul 24 16:42 backup drwxrwxr-x 21 1001 1001 326 Aug 3 15:09 base drwxrwxr-x 2 1001 1001 6 Aug 3 15:09 pg_wal drwxrwxr-x 6 postgres postgres 57 Aug 5 09:09 postgresql-14.11 -rw-r--r-- 1 root root 57 Aug 22 15:04 percona-version-check -rw------- 1 mysql mysql 6 Aug 22 15:33 mysqlx.sock.lock srwxrwxrwx 1 mysql mysql 0 Aug 22 15:33 mysqlx.sock -rw------- 1 mysql mysql 5 Aug 22 15:33 mysql.sock.lock srwxrwxrwx 1 mysql mysql 0 Aug 22 15:33 mysql.sock -rw-r--r-- 1 root root 33 Aug 22 15:45 crypt.key
因属于不完全恢复，将不需要恢复的库信息，移出备份目录
现只需要恢复test01,将test02移出恢复目录： [root@node01 backup]# ls backup-my.cnf ibtmp1 mysqld-bin.000027 performance_schema test01.xbstream undo_002 xtrabackup_info ib_buffer_pool #innodb_redo mysqld-bin.index sys test02 xtrabackup_binlog_info xtrabackup_logfile ibdata1 mysql mysql.ibd test01 undo_001 xtrabackup_checkpoints xtrabackup_tablespaces [root@node01 backup]# [root@node01 backup]# [root@node01 backup]# [root@node01 backup]# [root@node01 backup]# mv test02/ ../ [root@node01 backup]# [root@node01 backup]# ls -rtl total 91936 -rw-r--r-- 1 root root 1815983 Aug 22 15:37 test01.xbstream drwxr-x--- 2 root root 24 Aug 22 15:47 test01 drwxr-x--- 2 root root 28 Aug 22 15:47 sys -rw-r--r-- 1 root root 16777216 Aug 22 15:47 undo_001 -rw-r--r-- 1 root root 16777216 Aug 22 15:47 undo_002 -rw-r--r-- 1 root root 25165824 Aug 22 15:47 mysql.ibd drwxr-x--- 2 root root 143 Aug 22 15:47 mysql -rw-r--r-- 1 root root 20 Aug 22 15:47 mysqld-bin.index drwxr-x--- 2 root root 8192 Aug 22 15:47 performance_schema -rw-r--r-- 1 root root 157 Aug 22 15:47 mysqld-bin.000027 -rw-r--r-- 1 root root 22 Aug 22 15:47 xtrabackup_binlog_info -rw-r--r-- 1 root root 4776 Aug 22 15:47 ib_buffer_pool -rw-r--r-- 1 root root 451 Aug 22 15:47 backup-my.cnf -rw-r--r-- 1 root root 613 Aug 22 15:47 xtrabackup_info -rw-r----- 1 root root 12582912 Aug 22 15:49 ibtmp1 -rw-r--r-- 1 root root 12582912 Aug 22 15:49 ibdata1 -rw-r--r-- 1 root root 8388608 Aug 22 15:49 xtrabackup_logfile -rw-r----- 1 root root 132 Aug 22 15:49 xtrabackup_checkpoints drwxr-x--- 2 root root 6 Aug 22 15:49 #innodb_redo -rw-r--r-- 1 root root 39 Aug 22 15:49 xtrabackup_tablespaces
进行准备恢复：(不使用--apply-log-only)
xtrabackup --prepare --target-dir=/data/mysqldata/backup/ [root@node01 backup]# xtrabackup --prepare --target-dir=/data/mysqldata/backup/ 2024-08-22T15:53:42.195359+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_file_size=1073741824 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=319 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0 2024-08-22T15:53:42.195866+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --prepare=1 --target-dir=/data/mysqldata/backup/ xtrabackup version 8.0.35-31 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 55ec21d7) 2024-08-22T15:53:42.195938+08:00 0 [Note] [MY-011825] [Xtrabackup] cd to /data/mysqldata/backup/ 2024-08-22T15:53:42.196048+08:00 0 [Note] [MY-011825] [Xtrabackup] This target seems to be already prepared with --apply-log-only. 2024-08-22T15:53:42.198282+08:00 0 [Note] [MY-011825] [Xtrabackup] xtrabackup_logfile was already used to '--prepare'. 2024-08-22T15:53:42.198799+08:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration for recovery: 2024-08-22T15:53:42.198834+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = . 2024-08-22T15:53:42.198883+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend 2024-08-22T15:53:42.198951+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = . 2024-08-22T15:53:42.198960+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 2 2024-08-22T15:53:42.198972+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 1073741824 ......省略部分日志 2024-08-22T15:53:47.261778+08:00 0 [Warning] [MY-012091] [InnoDB] Allocated tablespace ID 1 for sys/sys_config, old maximum was 0 2024-08-22T15:53:47.279968+08:00 0 [Note] [MY-011825] [Xtrabackup] Completed loading of 3 tablespaces into cache in 0.0359138 seconds 2024-08-22T15:53:47.302074+08:00 0 [Note] [MY-011825] [Xtrabackup] Time taken to build dictionary: 0.0219715 seconds 2024-08-22T15:53:47.305402+08:00 0 [Note] [MY-011825] [Xtrabackup] starting shutdown with innodb_fast_shutdown = 1 2024-08-22T15:53:47.306473+08:00 0 [Note] [MY-012330] [InnoDB] FTS optimize thread exiting. 2024-08-22T15:53:48.313066+08:00 0 [Note] [MY-013072] [InnoDB] Starting shutdown... 2024-08-22T15:53:48.421314+08:00 0 [Note] [MY-013084] [InnoDB] Log background threads are being closed... 2024-08-22T15:53:48.460307+08:00 0 [Note] [MY-012980] [InnoDB] Shutdown completed; log sequence number 21486102 2024-08-22T15:53:48.466564+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
在一台新的mysql实例上进行恢复（已安装mysql同版本软件，且配置my.cnf文件）
xtrabackup --defaults-file=/etc/my.cnf --copy-back --user=backup --password='backup' --port=3306 --databases="test01" --datadir=/data/mysqldata/data --target-dir=/data/mysqldata/backup 日志信息： [root@node01 backup]# xtrabackup: [Warning] option 'innodb_write_io_threads': signed value 2 adjusted to 4. xtrabackup: [Warning] option 'innodb_read_io_threads': signed value 2 adjusted to 4. 2024-08-22T15:55:53.904097+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/data/mysqldata/data --server-id=319 --log_bin=mysqld-bin --innodb_buffer_pool_size=4G --innodb_io_capacity=20000 --innodb_flush_method=O_DIRECT --innodb_log_file_size=1073741824 --innodb_log_files_in_group=2 --innodb_log_buffer_size=16777216 --innodb_write_io_threads=2 --innodb_read_io_threads=2 --innodb_file_per_table=1 --innodb_open_files=4096 --datadir=/data/mysqldata/data 2024-08-22T15:55:53.904368+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --host=localhost --port=3306 --user=root --password=* --copy-back=1 --user=backup --password=* --port=3306 --databases=test01 --target-dir=/data/mysqldata/backup xtrabackup version 8.0.35-31 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 55ec21d7) 2024-08-22T15:55:53.904429+08:00 0 [Note] [MY-011825] [Xtrabackup] cd to /data/mysqldata/backup/ 2024-08-22T15:55:53.908378+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying undo_001 to /data/mysqldata/data/undo_001 2024-08-22T15:55:53.946709+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying undo_001 to /data/mysqldata/data/undo_001 2024-08-22T15:55:54.060343+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying undo_002 to /data/mysqldata/data/undo_002 2024-08-22T15:55:54.134460+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying undo_002 to /data/mysqldata/data/undo_002 2024-08-22T15:55:54.222376+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying ibdata1 to /data/mysqldata/data/ibdata1 ......省略部分日志 2024-08-22T15:55:54.871171+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./mysql.ibd to /data/mysqldata/data/mysql.ibd 2024-08-22T15:55:55.024958+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./mysql.ibd to /data/mysqldata/data/mysql.ibd 2024-08-22T15:55:55.091260+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./ib_buffer_pool to /data/mysqldata/data/ib_buffer_pool 2024-08-22T15:55:55.092071+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./ib_buffer_pool to /data/mysqldata/data/ib_buffer_pool 2024-08-22T15:55:55.096384+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./xtrabackup_info to /data/mysqldata/data/xtrabackup_info 2024-08-22T15:55:55.097860+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./xtrabackup_info to /data/mysqldata/data/xtrabackup_info 2024-08-22T15:55:55.103161+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./ibtmp1 to /data/mysqldata/data/ibtmp1 2024-08-22T15:55:55.147418+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./ibtmp1 to /data/mysqldata/data/ibtmp1 2024-08-22T15:55:55.201507+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
新实例数据目录信息
[root@node01 data]# ls -rtl total 83728 -rw-r----- 1 root root 16777216 Aug 22 15:55 undo_001 -rw-r----- 1 root root 16777216 Aug 22 15:55 undo_002 drwxr-x--- 2 root root 6 Aug 22 15:55 #innodb_redo -rw-r----- 1 root root 12582912 Aug 22 15:55 ibdata1 -rw-r----- 1 root root 157 Aug 22 15:55 mysqld-bin.000027 -rw-r----- 1 root root 18 Aug 22 15:55 mysqld-bin.index -rw-r----- 1 root root 1815983 Aug 22 15:55 test01.xbstream drwxr-x--- 2 root root 24 Aug 22 15:55 test01 drwxr-x--- 2 root root 28 Aug 22 15:55 sys drwxr-x--- 2 root root 143 Aug 22 15:55 mysql drwxr-x--- 2 root root 8192 Aug 22 15:55 performance_schema -rw-r----- 1 root root 25165824 Aug 22 15:55 mysql.ibd -rw-r----- 1 root root 4776 Aug 22 15:55 ib_buffer_pool -rw-r----- 1 root root 613 Aug 22 15:55 xtrabackup_info -rw-r----- 1 root root 12582912 Aug 22 15:55 ibtmp1 修改文件权限： [root@node01 mysqldata]# chown -R mysql.mysql data [root@node01 data]# ls -rtl total 83736 -rw-r----- 1 mysql mysql 16777216 Aug 22 15:55 undo_001 -rw-r----- 1 mysql mysql 16777216 Aug 22 15:55 undo_002 drwxr-x--- 2 mysql mysql 6 Aug 22 15:55 #innodb_redo -rw-r----- 1 mysql mysql 12582912 Aug 22 15:55 ibdata1 -rw-r----- 1 mysql mysql 157 Aug 22 15:55 mysqld-bin.000027 -rw-r----- 1 mysql mysql 18 Aug 22 15:55 mysqld-bin.index -rw-r----- 1 mysql mysql 1815983 Aug 22 15:55 test01.xbstream drwxr-x--- 2 mysql mysql 24 Aug 22 15:55 test01 drwxr-x--- 2 mysql mysql 28 Aug 22 15:55 sys drwxr-x--- 2 mysql mysql 143 Aug 22 15:55 mysql drwxr-x--- 2 mysql mysql 8192 Aug 22 15:55 performance_schema -rw-r----- 1 mysql mysql 25165824 Aug 22 15:55 mysql.ibd -rw-r----- 1 mysql mysql 4776 Aug 22 15:55 ib_buffer_pool -rw-r----- 1 mysql mysql 613 Aug 22 15:55 xtrabackup_info -rw-r----- 1 mysql mysql 12582912 Aug 22 15:55 ibtmp1 启动数据库
启动后查看
[root@localhost][(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test01 | | test02 | +--------------------+ 6 rows in set (0.00 sec) [root@localhost][(none)]> --删除数据库提示不存在： [root@localhost][(none)]> drop database test02; ERROR 3679 (HY000): Schema directory './test02/' does not exist [root@localhost][(none)]> [root@localhost][(none)]> exit Bye --创建需要删除的数据库目录： [root@node01 data]# mkdir test02 --修改权限： [root@node01 data]# chown mysql.mysql test02 [root@node01 data]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.28 MySQL Community Server - GPL Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. [root@localhost][(none)]> drop database test02; Query OK, 1 row affected (0.28 sec) --删除成功 [root@localhost][(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test01 | +--------------------+ 5 rows in set (0.00 sec) [root@localhost][(none)]> use test01; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed [root@localhost][test01]> show tables; +------------------+ | Tables_in_test01 | +------------------+ | test01 | +------------------+ 1 row in set (0.00 sec) [root@localhost][test01]> select * from test01; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | +----+------+ 3 rows in set (0.00 sec) 数据已恢复。
标签：--,MY,数据库,2024,Xtrabackup,08,22T15,root,全备 From： https://www.cnblogs.com/elanjie/p/18374188