mysql8.0.31使用percona-xtrabackup 8.0.30的备份恢复
安装参考：
https://docs.percona.com/percona-xtrabackup/8.0/installation/yum_repo.html
备份恢复参考：
https://docs.percona.com/percona-xtrabackup/8.0/backup_scenarios/incremental_backup.html
https://blog.csdn.net/m0_49190412/article/details/110203370
Incremental backup¶
xtrabackup supports incremental backups, which means that they can copy only all the data that has changed since the last backup.
Note
Incremental backups on the MyRocks storage engine do not determine if an earlier full backup or incremental backup contains the same files. Percona XtraBackup copies all the MyRocks files each time it takes a backup.
You can perform many incremental backups between each full backup, so you can set up a backup process such as a full backup once a week and an incremental backup every day, or full backups every day and incremental backups every hour.
Incremental backups work because each InnoDB page contains a log sequence number, or LSN. The LSN is the system version number for the entire database. Each page’s LSN shows how recently it was changed.
An incremental backup copies each page whose LSN is newer than the previous incremental or full backup’s LSN. An algorithm finds the pages that match the criteria. The algorithm reads the data pages and checks the page LSN.
Use the changed page tracking algorithm¶
Percona XtraBackup 8.0.30 removes the algorithm that used the changed page tracking feature in Percona Server for MySQL. Percona Server for MySQL 8.0.27 deprecated the changed page tracking feature.
With PXB 8.0.27 or earlier, another algorithm enabled the Percona Server for MySQL changed page tracking feature. The algorithm generates a bitmap file. The xtrabackup binary uses that bitmap file to read only those pages needed for the incremental backup. This method potentially saves resources. The backup enables the algorithm by default if the xtrabackup binary discovers the bitmap file. You can override the algorithm with --incremental-force-scan which forces a read of all pages even if the bitmap file is available.
Create an incremental backup¶
To make an incremental backup, begin with a full backup as usual. The xtrabackup binary writes a file called xtrabackup_checkpoints into the backup’s target directory. This file contains a line showing the to_lsn, which is the database’s LSN at the end of the backup. Create the full backup with a following command:
$ xtrabackup --backup --target-dir=/data/backups/base
If you look at the xtrabackup_checkpoints file, you should see similar content depending on your LSN nuber:
Expected output
Now that you have a full backup, you can make an incremental backup based on it. Use the following command:
$ xtrabackup --backup --target-dir=/data/backups/inc1 \
--incremental-basedir=/data/backups/base
The /data/backups/inc1/ directory should now contain delta files, such as ibdata1.delta and test/table1.ibd.delta. These represent the changes since the LSN 1626007. If you examine the xtrabackup_checkpoints file in this directory, you should see similar content to the following:
Expected output
from_lsn is the starting LSN of the backup and for incremental it has to be the same as to_lsn (if it is the last checkpoint) of the previous/base backup.
It’s now possible to use this directory as the base for yet another incremental backup:
$ xtrabackup --backup --target-dir=/data/backups/inc2 \
--incremental-basedir=/data/backups/inc1
This folder also contains the xtrabackup_checkpoints:
Expected output
Note
In this case you can see that there is a difference between the to_lsn (last checkpoint LSN) and last_lsn (last copied LSN), this means that there was some traffic on the server during the backup process.
Prepare the incremental backups¶
The --prepare step for incremental backups is not the same as for full backups. In full backups, two types of operations are performed to make the database consistent: committed transactions are replayed from the log file against the data files, and uncommitted transactions are rolled back. You must skip the rollback of uncommitted transactions when preparing an incremental backup, because transactions that were uncommitted at the time of your backup may be in progress, and it’s likely that they will be committed in the next incremental backup. You should use the --apply-log-only option to prevent the rollback phase.
Warning
If you do not use the --apply-log-only option to prevent the rollback phase, then your incremental backups will be useless. After transactions have been rolled back, further incremental backups cannot be applied.
Beginning with the full backup you created, you can prepare it, and then apply the incremental differences to it. Recall that you have the following backups:
/data/backups/base
/data/backups/inc1
/data/backups/inc2
To prepare the base backup, you need to run --prepare as usual, but prevent the rollback phase:
$ xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base
The output should end with text similar to the following:
Expected output
The log sequence number should match the to_lsn of the base backup, which you saw previously.
Warning
This backup is actually safe to restore as-is now, even though the rollback phase has been skipped. If you restore it and start MySQL, InnoDB will detect that the rollback phase was not performed, and it will do that in the background, as it usually does for a crash recovery upon start. It will notify you that the database was not shut down normally.
To apply the first incremental backup to the full backup, run the following command:
$ xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base \
--incremental-dir=/data/backups/inc1
This applies the delta files to the files in /data/backups/base, which rolls them forward in time to the time of the incremental backup. It then applies the redo log as usual to the result. The final data is in /data/backups/base, not in the incremental directory. You should see an output similar to:
Expected output
Again, the LSN should match what you saw from your earlier inspection of the first incremental backup. If you restore the files from /data/backups/base, you should see the state of the database as of the first incremental backup.
Warning
Percona XtraBackup does not support using the same incremental backup directory to prepare two copies of backup. Do not run --prepare with the same incremental backup directory (the value of –incremental-dir) more than once.
Preparing the second incremental backup is a similar process: apply the deltas to the (modified) base backup, and you will roll its data forward in time to the point of the second incremental backup:
$ xtrabackup --prepare --target-dir=/data/backups/base \
--incremental-dir=/data/backups/inc2
Note
--apply-log-only should be used when merging the incremental backups except the last one. That’s why the previous line does not contain the --apply-log-only option. Even if the --apply-log-only was used on the last step, backup would still be consistent but in that case server would perform the rollback phase.
Once prepared incremental backups are the same as the full backups, and they can be restored in the same way.
测试结果：
主库做备份和恢复，从库自动恢复同步。
主库端：
*****[2022-12-27 13:48:07]*****
Last login: Mon Dec 26 11:07:34 2022 from 10.168.20.66
[root@mysql2 ~]# yum install \
> https://repo.percona.com/yum/percona-release-latest.\
> noarch.rpm
......
[root@mysql2 ~]# yum install qpress zstd -y
......
Complete!
[root@mysql2 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 3.9G 0 3.9G 0% /dev
tmpfs 3.9G 0 3.9G 0% /dev/shm
tmpfs 3.9G 8.9M 3.9G 1% /run
tmpfs 3.9G 0 3.9G 0% /sys/fs/cgroup
/dev/mapper/centos-root 28G 6.4G 22G 23% /
/dev/sda1 1014M 150M 865M 15% /boot
tmpfs 783M 0 783M 0% /run/user/0
/dev/loop0 50M 50M 0 100% /var/lib/snapd/snap/snapd/17883
/dev/loop1 64M 64M 0 100% /var/lib/snapd/snap/core20/1738
/dev/loop2 45M 45M 0 100% /var/lib/snapd/snap/certbot/2618
/dev/loop3 64M 64M 0 100% /var/lib/snapd/snap/core20/1778
[root@mysql2 ~]# mkdir /backup
[root@mysql2 ~]# xtrabackup --help
2022-12-27T13:58:46.903053+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/mysql/data --innodb_buffer_pool_size=2G --innodb_flush_log_at_trx_commit=2 --log_bin=/mysql/data/mysql-bin --server-id=2
2022-12-27T13:58:46.903474+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --port=3306 --socket=/mysql/mysql.sock
xtrabackup version 8.0.30-23 based on MySQL server 8.0.30 Linux (x86_64) (revision id: 873b467185c)
Open source backup tool for InnoDB and XtraDB
。。。。。。
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- ----------------------------------------
datadir /mysql/data
tmpdir /tmp
log (No default value)
log-bin /mysql/data/mysql-bin
log-bin-index (No default value)
innodb (No default value)
innodb-adaptive-hash-index TRUE
innodb-autoextend-increment 8
innodb-buffer-pool-size 2147483648
innodb-checksums TRUE
innodb-data-file-path (No default value)
innodb-data-home-dir (No default value)
innodb-io-capacity 200
innodb-file-io-threads 4
innodb-read-io-threads 4
innodb-write-io-threads 4
innodb-file-per-table FALSE
innodb-flush-log-at-trx-commit 2
innodb-flush-method littlesync
innodb-force-recovery 0
innodb-log-buffer-size 16777216
innodb-log-file-size 50331648
innodb-log-files-in-group 2
innodb-log-group-home-dir (No default value)
innodb-max-dirty-pages-pct 75
innodb-open-files 300
innodb-use-native-aio FALSE
innodb-page-size 16384
innodb-log-block-size 512
innodb-buffer-pool-filename (No default value)
debug-sync (No default value)
innodb-checksum-algorithm crc32
innodb-log-checksums TRUE
innodb-undo-directory (No default value)
innodb-directories (No default value)
temp-tablespaces-dir (No default value)
innodb-undo-tablespaces 2
innodb-redo-log-encrypt FALSE
innodb-undo-log-encrypt FALSE
defaults-group mysqld
open-files-limit 0
server-id 2
rocksdb-datadir (No default value)
rocksdb-wal-dir (No default value)
register-redo-log-consumer FALSE
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
----------------------------------------- --------------------------------
version FALSE
target-dir /root/xtrabackup_backupfiles/
backup FALSE
stats FALSE
prepare FALSE
export FALSE
apply-log-only FALSE
print-param FALSE
use-memory 104857600
use-free-memory-pct 0
throttle 0
log (No default value)
log-copy-interval 1000
extra-lsndir (No default value)
incremental-lsn (No default value)
incremental-basedir (No default value)
incremental-dir (No default value)
to-archived-lsn 0
tables (No default value)
tables-file (No default value)
databases (No default value)
databases-file (No default value)
tables-exclude (No default value)
databases-exclude (No default value)
create-ib-logfile FALSE
stream (No default value)
compress (No default value)
compress-threads 1
compress-chunk-size 65536
compress-zstd-level 1
encrypt NONE
encrypt-key-file (No default value)
encrypt-threads 1
encrypt-chunk-size 65536
rebuild-threads 1
incremental-force-scan FALSE
close-files FALSE
copy-back FALSE
move-back FALSE
galera-info FALSE
slave-info FALSE
page-tracking FALSE
no-lock FALSE
lock-ddl TRUE
lock-ddl-timeout 31536000
lock-ddl-per-table FALSE
backup-lock-timeout 31536000
backup-lock-retry-count 0
dump-innodb-buffer-pool FALSE
dump-innodb-buffer-pool-timeout 10
dump-innodb-buffer-pool-pct 0
safe-slave-backup FALSE
rsync FALSE
force-non-empty-directories FALSE
no-server-version-check FALSE
no-version-check FALSE
tables-compatibility-check TRUE
no-backup-locks FALSE
rollback-prepared-trx FALSE
decompress FALSE
user (No default value)
host (No default value)
port 3306
socket /mysql/mysql.sock
incremental-history-name (No default value)
incremental-history-uuid (No default value)
decrypt NONE
remove-original FALSE
ftwrl-wait-query-type ALL
kill-long-query-type SELECT
kill-long-queries-timeout 0
ftwrl-wait-timeout 0
ftwrl-wait-threshold 60
debug-sleep-before-unlock 0
safe-slave-backup-timeout 300
check-privileges FALSE
read-buffer-size 10485760
server-public-key-path (No default value)
get-server-public-key FALSE
ssl-ca (No default value)
ssl-capath (No default value)
ssl-cert (No default value)
ssl-cipher (No default value)
ssl-key (No default value)
ssl-crl (No default value)
ssl-crlpath (No default value)
tls-version (No default value)
tls-ciphersuites (No default value)
ssl-session-data (No default value)
ssl-session-data-continue-on-failed-reuse FALSE
server-public-key-path (No default value)
xtrabackup-plugin-dir (No default value)
plugin-load (No default value)
generate-new-master-key FALSE
generate-transition-key FALSE
keyring-file-data (No default value)
component-keyring-config (No default value)
component-keyring-file-config (No default value)
parallel 1
strict TRUE
rocksdb-checkpoint-max-age 0
rocksdb-checkpoint-max-count 0
[root@mysql2 ~]# xtrabackup --version
2022-12-27T13:58:54.156610+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/mysql/data --innodb_buffer_pool_size=2G --innodb_flush_log_at_trx_commit=2 --log_bin=/mysql/data/mysql-bin --server-id=2
xtrabackup version 8.0.30-23 based on MySQL server 8.0.30 Linux (x86_64) (revision id: 873b467185c)
[root@mysql2 ~]# xtrabackup --backup --target-dir=/backup
2022-12-27T14:00:10.516801+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/mysql/data --innodb_buffer_pool_size=2G --innodb_flush_log_at_trx_commit=2 --log_bin=/mysql/data/mysql-bin --server-id=2
2022-12-27T14:00:10.517326+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --port=3306 --socket=/mysql/mysql.sock --backup=1 --target-dir=/backup
xtrabackup version 8.0.30-23 based on MySQL server 8.0.30 Linux (x86_64) (revision id: 873b467185c)
221227 14:00:10 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/mysql/mysql.sock' (using password: NO).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/mysql/mysql.sock','',...) failed: Access denied for user 'root'@'localhost' (using password: NO) at - line 1535.
2022-12-27T14:00:10.639565+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: not set, password: not set, port: 3306, socket: /mysql/mysql.sock
2022-12-27T14:00:10.641207+08:00 0 [ERROR] [MY-011825] [Xtrabackup] Failed to connect to MySQL server: Access denied for user 'root'@'localhost' (using password: NO)
[root@mysql2 ~]# xtrabackup --backup --target-dir=/backup --user=root
2022-12-27T14:05:26.301042+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/mysql/data --innodb_buffer_pool_size=2G --innodb_flush_log_at_trx_commit=2 --log_bin=/mysql/data/mysql-bin --server-id=2
2022-12-27T14:05:26.301430+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --port=3306 --socket=/mysql/mysql.sock --backup=1 --target-dir=/backup --user=root
xtrabackup version 8.0.30-23 based on MySQL server 8.0.30 Linux (x86_64) (revision id: 873b467185c)
221227 14:05:26 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/mysql/mysql.sock' as 'root' (using password: NO).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/mysql/mysql.sock','root',...) failed: Access denied for user 'root'@'localhost' (using password: NO) at - line 1535.
2022-12-27T14:05:26.411601+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: not set, port: 3306, socket: /mysql/mysql.sock
2022-12-27T14:05:26.413026+08:00 0 [ERROR] [MY-011825] [Xtrabackup] Failed to connect to MySQL server: Access denied for user 'root'@'localhost' (using password: NO)
[root@mysql2 ~]# xtrabackup --backup --target-dir=/backup --user=root --password=abcd1234
2022-12-27T14:05:35.550516+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/mysql/data --innodb_buffer_pool_size=2G --innodb_flush_log_at_trx_commit=2 --log_bin=/mysql/data/mysql-bin --server-id=2
2022-12-27T14:05:35.550884+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --port=3306 --socket=/mysql/mysql.sock --backup=1 --target-dir=/backup --user=root --password=*
xtrabackup version 8.0.30-23 based on MySQL server 8.0.30 Linux (x86_64) (revision id: 873b467185c)
221227 14:05:35 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/mysql/mysql.sock' as 'root' (using password: YES).
221227 14:05:35 version_check Connected to MySQL server
221227 14:05:35 version_check Executing a version check against the server...
221227 14:05:35 version_check Done.
2022-12-27T14:05:35.662178+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /mysql/mysql.sock
2022-12-27T14:05:35.681295+08:00 0 [ERROR] [MY-011825] [Xtrabackup] Unsupported server version 8.0.31
2022-12-27T14:05:35.681360+08:00 0 [ERROR] [MY-011825] [Xtrabackup] Please upgrade PXB, if a new version is available. To continue with risk, use the option --no-server-version-check.
[root@mysql2 ~]# xtrabackup --backup --target-dir=/backup --user=root --password=abcd1234 --no-server-version-check
2022-12-27T14:05:56.030808+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/mysql/data --innodb_buffer_pool_size=2G --innodb_flush_log_at_trx_commit=2 --log_bin=/mysql/data/mysql-bin --server-id=2
2022-12-27T14:05:56.031349+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --port=3306 --socket=/mysql/mysql.sock --backup=1 --target-dir=/backup --user=root --password=* --no-server-version-check=1
xtrabackup version 8.0.30-23 based on MySQL server 8.0.30 Linux (x86_64) (revision id: 873b467185c)
221227 14:05:56 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/mysql/mysql.sock' as 'root' (using password: YES).
221227 14:05:56 version_check Connected to MySQL server
221227 14:05:56 version_check Executing a version check against the server...
221227 14:05:56 version_check Done.
2022-12-27T14:05:56.140625+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /mysql/mysql.sock
2022-12-27T14:05:56.148868+08:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.31
2022-12-27T14:05:56.153875+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ...
2022-12-27T14:05:56.154157+08:00 0 [ERROR] [MY-011825] [Xtrabackup] failed to execute query 'LOCK INSTANCE FOR BACKUP' : 1227 (42000) Access denied; you need (at least one of) the BACKUP_ADMIN privilege(s) for this operation
[root@mysql2 ~]# xtrabackup --backup --target-dir=/backup --user=clone_user --password=123456 --no-server-version-check
2022-12-27T14:07:42.755563+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/mysql/data --innodb_buffer_pool_size=2G --innodb_flush_log_at_trx_commit=2 --log_bin=/mysql/data/mysql-bin --server-id=2
2022-12-27T14:07:42.755950+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --port=3306 --socket=/mysql/mysql.sock --backup=1 --target-dir=/backup --user=clone_user --password=* --no-server-version-check=1
xtrabackup version 8.0.30-23 based on MySQL server 8.0.30 Linux (x86_64) (revision id: 873b467185c)
221227 14:07:42 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/mysql/mysql.sock' as 'clone_user' (using password: YES).
221227 14:07:42 version_check Connected to MySQL server
DBD::mysql::db do failed: Aborted connection 62 to db: 'unconnected' user: 'clone_user' host: 'localhost' (init_connect command failed) at - line 1559.
DBD::mysql::db do failed: Aborted connection 62 to db: 'unconnected' user: 'clone_user' host: 'localhost' (init_connect command failed) at - line 1559.
2022-12-27T14:07:42.865427+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: clone_user, password: set, port: 3306, socket: /mysql/mysql.sock
2022-12-27T14:07:42.867089+08:00 0 [ERROR] [MY-011825] [Xtrabackup] failed to execute query 'SET SESSION wait_timeout=2147483' : 1184 (08S01) Aborted connection 63 to db: 'unconnected' user: 'clone_user' host: 'localhost' (init_connect command failed)
[root@mysql2 ~]# mysql -h 192.168.207.132 -P 3306 -u root -pabcd1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 64
Server version: 8.0.31 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.
mysql> grant backup_admin on *.* to root;
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@mysql2 ~]# xtrabackup --backup --target-dir=/backup --user=root --password=abcd1234 --no-server-version-check
2022-12-27T14:09:39.577502+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/mysql/data --innodb_buffer_pool_size=2G --innodb_flush_log_at_trx_commit=2 --log_bin=/mysql/data/mysql-bin --server-id=2
2022-12-27T14:09:39.577868+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --port=3306 --socket=/mysql/mysql.sock --backup=1 --target-dir=/backup --user=root --password=* --no-server-version-check=1
xtrabackup version 8.0.30-23 based on MySQL server 8.0.30 Linux (x86_64) (revision id: 873b467185c)
221227 14:09:39 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/mysql/mysql.sock' as 'root' (using password: YES).
221227 14:09:39 version_check Connected to MySQL server
221227 14:09:39 version_check Executing a version check against the server...
221227 14:09:39 version_check Done.
2022-12-27T14:09:39.689983+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /mysql/mysql.sock
2022-12-27T14:09:39.699171+08:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.31
2022-12-27T14:09:39.701679+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ...
2022-12-27T14:09:39.722695+08:00 0 [Note] [MY-011825] [Xtrabackup] uses posix_fadvise().
2022-12-27T14:09:39.722771+08:00 0 [Note] [MY-011825] [Xtrabackup] cd to /mysql/data
2022-12-27T14:09:39.722824+08:00 0 [Note] [MY-011825] [Xtrabackup] open files limit requested 0, set to 1024
2022-12-27T14:09:39.722946+08:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration:
2022-12-27T14:09:39.722967+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2022-12-27T14:09:39.722982+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend
2022-12-27T14:09:39.723115+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = ./
2022-12-27T14:09:39.723136+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 2
2022-12-27T14:09:39.723157+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 50331648
2022-12-27T14:09:39.724238+08:00 0 [Note] [MY-013251] [InnoDB] Number of pools: 1
2022-12-27T14:09:39.727411+08:00 0 [Note] [MY-011825] [Xtrabackup] inititialize_service_handles suceeded
2022-12-27T14:09:39.901169+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /mysql/mysql.sock
2022-12-27T14:09:39.913329+08:00 0 [Note] [MY-011825] [Xtrabackup] Redo Log Archiving is not set up.
2022-12-27T14:09:40.012846+08:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (19947423)
2022-12-27T14:09:40.218228+08:00 0 [Note] [MY-011825] [Xtrabackup] Generating a list of tablespaces
2022-12-27T14:09:40.218376+08:00 0 [Note] [MY-011825] [Xtrabackup] Generating a list of tablespaces
2022-12-27T14:09:40.218414+08:00 0 [Note] [MY-012204] [InnoDB] Scanning './'
2022-12-27T14:09:40.220034+08:00 0 [Note] [MY-012208] [InnoDB] Completed space ID check of 2 files.
2022-12-27T14:09:40.220552+08:00 0 [Warning] [MY-012091] [InnoDB] Allocated tablespace ID 3 for t/t, old maximum was 0
2022-12-27T14:09:40.221443+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_001'.
2022-12-27T14:09:40.222293+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_002'.
2022-12-27T14:09:40.223931+08:00 0 [Note] [MY-012910] [InnoDB] Opened 2 existing undo tablespaces.
2022-12-27T14:09:40.224581+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./ibdata1 to /backup/ibdata1
2022-12-27T14:09:40.298649+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./ibdata1 to /backup/ibdata1
2022-12-27T14:09:40.314450+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./sys/sys_config.ibd to /backup/sys/sys_config.ibd
2022-12-27T14:09:40.315258+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./sys/sys_config.ibd to /backup/sys/sys_config.ibd
2022-12-27T14:09:40.316784+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./jycdb/test.ibd to /backup/jycdb/test.ibd
2022-12-27T14:09:40.317616+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./jycdb/test.ibd to /backup/jycdb/test.ibd
2022-12-27T14:09:40.320939+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./t/t.ibd to /backup/t/t.ibd
2022-12-27T14:09:40.321691+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./t/t.ibd to /backup/t/t.ibd
2022-12-27T14:09:40.324442+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./jyc/t.ibd to /backup/jyc/t.ibd
2022-12-27T14:09:40.325224+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./jyc/t.ibd to /backup/jyc/t.ibd
2022-12-27T14:09:40.327664+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./j/t.ibd to /backup/j/t.ibd
2022-12-27T14:09:40.328106+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./j/t.ibd to /backup/j/t.ibd
2022-12-27T14:09:40.330401+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./mysql.ibd to /backup/mysql.ibd
2022-12-27T14:09:40.463265+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./mysql.ibd to /backup/mysql.ibd
2022-12-27T14:09:40.477859+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./undo_002 to /backup/undo_002
2022-12-27T14:09:40.534698+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./undo_002 to /backup/undo_002
2022-12-27T14:09:40.551038+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./undo_001 to /backup/undo_001
2022-12-27T14:09:40.628082+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./undo_001 to /backup/undo_001
2022-12-27T14:09:41.013107+08:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (19947423)
2022-12-27T14:09:41.224315+08:00 0 [Note] [MY-011825] [Xtrabackup] Starting to backup non-InnoDB tables and files
2022-12-27T14:09:41.224974+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying mysql/slow_log.CSM to /backup/mysql/slow_log.CSM
2022-12-27T14:09:41.225562+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying mysql/slow_log.CSM to /backup/mysql/slow_log.CSM
2022-12-27T14:09:41.226440+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying mysql/slow_log.CSV to /backup/mysql/slow_log.CSV
2022-12-27T14:09:41.226481+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying mysql/slow_log.CSV to /backup/mysql/slow_log.CSV
2022-12-27T14:09:41.227200+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying mysql/general_log.CSV to /backup/mysql/general_log.CSV
2022-12-27T14:09:41.227247+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying mysql/general_log.CSV to /backup/mysql/general_log.CSV
2022-12-27T14:09:41.230421+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying mysql/general_log.CSM to /backup/mysql/general_log.CSM
2022-12-27T14:09:41.230614+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying mysql/general_log.CSM to /backup/mysql/general_log.CSM
2022-12-27T14:09:41.231495+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying mysql/general_log_496.sdi to /backup/mysql/general_log_496.sdi
2022-12-27T14:09:41.231677+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying mysql/general_log_496.sdi to /backup/mysql/general_log_496.sdi
2022-12-27T14:09:41.234812+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying mysql/slow_log_498.sdi to /backup/mysql/slow_log_498.sdi
2022-12-27T14:09:41.235040+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying mysql/slow_log_498.sdi to /backup/mysql/slow_log_498.sdi
2022-12-27T14:09:41.238196+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying performance_schema/clone_status_376.sdi to /backup/performance_schema/clone_status_376.sdi
2022-12-27T14:09:41.238396+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying performance_schema/clone_status_376.sdi to /backup/performance_schema/clone_status_376.sdi
2022-12-27T14:09:41.241425+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying performance_schema/clone_progress_377.sdi to /backup/performance_schema/clone_progress_377.sdi
2022-12-27T14:09:41.241621+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying performance_schema/clone_progress_377.sdi to /backup/performance_schema/clone_progress_377.sdi
2022-12-27T14:09:41.325561+08:00 0 [Note] [MY-011825] [Xtrabackup] Finished backing up non-InnoDB tables and files
2022-12-27T14:09:41.325634+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS
2022-12-27T14:09:41.346370+08:00 0 [Note] [MY-011825] [Xtrabackup] Selecting LSN and binary log position from p_s.log_status
2022-12-27T14:09:41.352505+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying /mysql/data/mysql-bin.000002 to /backup/mysql-bin.000002 up to position 197
2022-12-27T14:09:41.352775+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying /mysql/data/mysql-bin.000002 to /backup/mysql-bin.000002
2022-12-27T14:09:41.353960+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /backup/mysql-bin.index
2022-12-27T14:09:41.354088+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /backup/mysql-bin.index
2022-12-27T14:09:41.359644+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /backup/xtrabackup_binlog_info
2022-12-27T14:09:41.359762+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /backup/xtrabackup_binlog_info
2022-12-27T14:09:41.362887+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
2022-12-27T14:09:41.366593+08:00 0 [Note] [MY-011825] [Xtrabackup] The latest check point (for incremental): '19947423'
2022-12-27T14:09:41.366634+08:00 0 [Note] [MY-011825] [Xtrabackup] Stopping log copying thread at LSN 19949267
2022-12-27T14:09:41.366766+08:00 1 [Note] [MY-011825] [Xtrabackup] Starting to parse redo log at lsn = 19947204
2022-12-27T14:09:41.367100+08:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (19949369)
2022-12-27T14:09:42.374026+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing UNLOCK INSTANCE
2022-12-27T14:09:42.374452+08:00 0 [Note] [MY-011825] [Xtrabackup] All tables unlocked
2022-12-27T14:09:42.374605+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying ib_buffer_pool to /backup/ib_buffer_pool
2022-12-27T14:09:42.374747+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying ib_buffer_pool to /backup/ib_buffer_pool
2022-12-27T14:09:42.377729+08:00 0 [Note] [MY-011825] [Xtrabackup] Backup created in directory '/backup/'
2022-12-27T14:09:42.377774+08:00 0 [Note] [MY-011825] [Xtrabackup] MySQL binlog position: filename 'mysql-bin.000002', position '197', GTID of the last change 'd0271cef-7782-11ed-828a-d67f35a9de29:1-5'
2022-12-27T14:09:42.377930+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /backup/backup-my.cnf
2022-12-27T14:09:42.378037+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /backup/backup-my.cnf
2022-12-27T14:09:42.381587+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /backup/xtrabackup_info
2022-12-27T14:09:42.381699+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /backup/xtrabackup_info
2022-12-27T14:09:43.387982+08:00 0 [Note] [MY-011825] [Xtrabackup] Transaction log of lsn (19947423) to (19949379) was copied.
2022-12-27T14:09:43.598152+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
[root@mysql2 ~]# ll /backup/
total 69676
-rw-r-----. 1 root root 447 Dec 27 14:09 backup-my.cnf
-rw-r-----. 1 root root 4138 Dec 27 14:09 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Dec 27 14:09 ibdata1
drwxr-x---. 2 root root 19 Dec 27 14:09 j
drwxr-x---. 2 root root 19 Dec 27 14:09 jyc
drwxr-x---. 2 root root 22 Dec 27 14:09 jycdb
drwxr-x---. 2 root root 143 Dec 27 14:09 mysql
-rw-r-----. 1 root root 197 Dec 27 14:09 mysql-bin.000002
-rw-r-----. 1 root root 29 Dec 27 14:09 mysql-bin.index
-rw-r-----. 1 root root 25165824 Dec 27 14:09 mysql.ibd
drwxr-x---. 2 root root 64 Dec 27 14:09 performance_schema
drwxr-x---. 2 root root 28 Dec 27 14:09 sys
drwxr-x---. 2 root root 19 Dec 27 14:09 t
-rw-r-----. 1 root root 16777216 Dec 27 14:09 undo_001
-rw-r-----. 1 root root 16777216 Dec 27 14:09 undo_002
-rw-r-----. 1 root root 62 Dec 27 14:09 xtrabackup_binlog_info
-rw-r-----. 1 root root 138 Dec 27 14:09 xtrabackup_checkpoints
-rw-r-----. 1 root root 570 Dec 27 14:09 xtrabackup_info
-rw-r-----. 1 root root 4608 Dec 27 14:09 xtrabackup_logfile
-rw-r-----. 1 root root 39 Dec 27 14:09 xtrabackup_tablespaces
[root@mysql2 ~]# date
Tue Dec 27 14:10:02 CST 2022
[root@mysql2 ~]#
[root@mysql2 ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup
2022-12-27T14:11:45.357516+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=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=2 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0
2022-12-27T14:11:45.357840+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --prepare=1 --apply-log-only=1 --target-dir=/backup
xtrabackup version 8.0.30-23 based on MySQL server 8.0.30 Linux (x86_64) (revision id: 873b467185c)
2022-12-27T14:11:45.357925+08:00 0 [Note] [MY-011825] [Xtrabackup] cd to /backup/
2022-12-27T14:11:45.358118+08:00 0 [Note] [MY-011825] [Xtrabackup] This target seems to be not prepared yet.
2022-12-27T14:11:45.358952+08:00 0 [Note] [MY-013251] [InnoDB] Number of pools: 1
2022-12-27T14:11:45.367894+08:00 0 [Note] [MY-011825] [Xtrabackup] xtrabackup_logfile detected: size=8388608, start_lsn=(19947423)
2022-12-27T14:11:45.368992+08:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration for recovery:
2022-12-27T14:11:45.369028+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2022-12-27T14:11:45.369046+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend
2022-12-27T14:11:45.369149+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = .
2022-12-27T14:11:45.369168+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 1
2022-12-27T14:11:45.369190+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 8388608
2022-12-27T14:11:45.369768+08:00 0 [Note] [MY-011825] [Xtrabackup] inititialize_service_handles suceeded
2022-12-27T14:11:45.370151+08:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration for recovery:
2022-12-27T14:11:45.370179+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2022-12-27T14:11:45.370195+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend
2022-12-27T14:11:45.370222+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = .
2022-12-27T14:11:45.370239+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 1
2022-12-27T14:11:45.370254+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 8388608
2022-12-27T14:11:45.370279+08:00 0 [Note] [MY-011825] [Xtrabackup] Starting InnoDB instance for recovery.
2022-12-27T14:11:45.370297+08:00 0 [Note] [MY-011825] [Xtrabackup] Using 104857600 bytes for buffer pool (set by --use-memory parameter)
2022-12-27T14:11:45.370379+08:00 0 [Note] [MY-012932] [InnoDB] PUNCH HOLE support available
2022-12-27T14:11:45.370423+08:00 0 [Note] [MY-012944] [InnoDB] Uses event mutexes
2022-12-27T14:11:45.370441+08:00 0 [Note] [MY-012945] [InnoDB] GCC builtin __atomic_thread_fence() is used for memory barrier
2022-12-27T14:11:45.370467+08:00 0 [Note] [MY-012948] [InnoDB] Compressed tables use zlib 1.2.12
2022-12-27T14:11:45.370691+08:00 0 [Note] [MY-013251] [InnoDB] Number of pools: 1
2022-12-27T14:11:45.370885+08:00 0 [Note] [MY-012951] [InnoDB] Using software crc32.
2022-12-27T14:11:45.371637+08:00 0 [Note] [MY-012203] [InnoDB] Directories to scan './'
2022-12-27T14:11:45.371722+08:00 0 [Note] [MY-012204] [InnoDB] Scanning './'
2022-12-27T14:11:45.374183+08:00 0 [Note] [MY-012208] [InnoDB] Completed space ID check of 8 files.
2022-12-27T14:11:45.375832+08:00 0 [Note] [MY-012955] [InnoDB] Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M
2022-12-27T14:11:45.390357+08:00 0 [Note] [MY-012957] [InnoDB] Completed initialization of buffer pool
2022-12-27T14:11:45.401570+08:00 0 [Note] [MY-011951] [InnoDB] page_cleaner coordinator priority: -20
2022-12-27T14:11:45.402371+08:00 0 [Note] [MY-011954] [InnoDB] page_cleaner worker priority: -20
2022-12-27T14:11:45.418196+08:00 0 [Note] [MY-011954] [InnoDB] page_cleaner worker priority: -20
2022-12-27T14:11:45.419124+08:00 0 [Note] [MY-011954] [InnoDB] page_cleaner worker priority: -20
2022-12-27T14:11:45.539384+08:00 0 [Note] [MY-013883] [InnoDB] The latest found checkpoint is at lsn = 19947423 in redo log file ./#innodb_redo/#ib_redo0.
2022-12-27T14:11:45.539528+08:00 0 [Note] [MY-012560] [InnoDB] The log sequence number 19779292 in the system tablespace does not match the log sequence number 19947423 in the redo log files!
2022-12-27T14:11:45.539563+08:00 0 [Note] [MY-012551] [InnoDB] Database was not shutdown normally!
2022-12-27T14:11:45.539581+08:00 0 [Note] [MY-012552] [InnoDB] Starting crash recovery.
2022-12-27T14:11:45.539763+08:00 0 [Note] [MY-013086] [InnoDB] Starting to parse redo log at lsn = 19947204, whereas checkpoint_lsn = 19947423 and start_lsn = 19947008
2022-12-27T14:11:45.539809+08:00 0 [Note] [MY-012550] [InnoDB] Doing recovery: scanned up to log sequence number 19949267
2022-12-27T14:11:45.570417+08:00 0 [Note] [MY-013083] [InnoDB] Log background threads are being started...
2022-12-27T14:11:45.624669+08:00 0 [Note] [MY-012532] [InnoDB] Applying a batch of 6 redo log records ...
2022-12-27T14:11:45.631471+08:00 0 [Note] [MY-012533] [InnoDB] 100%
2022-12-27T14:11:46.131670+08:00 0 [Note] [MY-012535] [InnoDB] Apply batch completed!
2022-12-27T14:11:46.235277+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_001'.
2022-12-27T14:11:46.235536+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_002'.
2022-12-27T14:11:46.236928+08:00 0 [Note] [MY-012910] [InnoDB] Opened 2 existing undo tablespaces.
2022-12-27T14:11:46.237096+08:00 0 [Note] [MY-011980] [InnoDB] GTID recovery trx_no: 4024
2022-12-27T14:11:46.293152+08:00 0 [Note] [MY-013776] [InnoDB] Parallel initialization of rseg complete
2022-12-27T14:11:46.293252+08:00 0 [Note] [MY-013777] [InnoDB] Time taken to initialize rseg using 2 thread: 56195 ms.
2022-12-27T14:11:46.293435+08:00 0 [Note] [MY-012923] [InnoDB] Creating shared tablespace for temporary tables
2022-12-27T14:11:46.293632+08:00 0 [Note] [MY-012265] [InnoDB] Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2022-12-27T14:11:46.345726+08:00 0 [Note] [MY-012266] [InnoDB] File './ibtmp1' size is now 12 MB.
2022-12-27T14:11:46.346168+08:00 0 [Note] [MY-013627] [InnoDB] Scanning temp tablespace dir:'./#innodb_temp/'
2022-12-27T14:11:46.368809+08:00 0 [Note] [MY-013018] [InnoDB] Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active.
2022-12-27T14:11:46.369482+08:00 0 [Note] [MY-012976] [InnoDB] 8.0.30 started; log sequence number 19949277
2022-12-27T14:11:46.370145+08:00 0 [Warning] [MY-012091] [InnoDB] Allocated tablespace ID 1 for sys/sys_config, old maximum was 0
2022-12-27T14:11:46.413438+08:00 0 [Note] [MY-011825] [Xtrabackup] starting shutdown with innodb_fast_shutdown = 1
2022-12-27T14:11:46.413559+08:00 0 [Note] [MY-012330] [InnoDB] FTS optimize thread exiting.
2022-12-27T14:11:47.413197+08:00 0 [Note] [MY-013072] [InnoDB] Starting shutdown...
2022-12-27T14:11:47.516216+08:00 0 [Note] [MY-013084] [InnoDB] Log background threads are being closed...
2022-12-27T14:11:47.541807+08:00 0 [Note] [MY-012980] [InnoDB] Shutdown completed; log sequence number 19949277
2022-12-27T14:11:47.542534+08:00 0 [Note] [MY-013251] [InnoDB] Number of pools: 1
2022-12-27T14:11:47.544154+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
[root@mysql2 ~]# xtrabackup --prepare --target-dir=/backup
2022-12-27T14:12:50.405857+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=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=2 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0
2022-12-27T14:12:50.406318+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --prepare=1 --target-dir=/backup
xtrabackup version 8.0.30-23 based on MySQL server 8.0.30 Linux (x86_64) (revision id: 873b467185c)
2022-12-27T14:12:50.406399+08:00 0 [Note] [MY-011825] [Xtrabackup] cd to /backup/
2022-12-27T14:12:50.406511+08:00 0 [Note] [MY-011825] [Xtrabackup] This target seems to be already prepared with --apply-log-only.
2022-12-27T14:12:50.407320+08:00 0 [Note] [MY-013251] [InnoDB] Number of pools: 1
2022-12-27T14:12:50.407559+08:00 0 [Note] [MY-011825] [Xtrabackup] xtrabackup_logfile was already used to '--prepare'.
2022-12-27T14:12:50.408204+08:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration for recovery:
2022-12-27T14:12:50.408231+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2022-12-27T14:12:50.408247+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend
2022-12-27T14:12:50.408317+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = .
2022-12-27T14:12:50.408335+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 2
2022-12-27T14:12:50.408354+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 50331648
2022-12-27T14:12:50.408821+08:00 0 [Note] [MY-011825] [Xtrabackup] inititialize_service_handles suceeded
2022-12-27T14:12:50.409156+08:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration for recovery:
2022-12-27T14:12:50.409185+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2022-12-27T14:12:50.409202+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend
2022-12-27T14:12:50.409228+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = .
2022-12-27T14:12:50.409244+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 2
2022-12-27T14:12:50.409259+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 50331648
2022-12-27T14:12:50.409284+08:00 0 [Note] [MY-011825] [Xtrabackup] Starting InnoDB instance for recovery.
2022-12-27T14:12:50.409301+08:00 0 [Note] [MY-011825] [Xtrabackup] Using 104857600 bytes for buffer pool (set by --use-memory parameter)
2022-12-27T14:12:50.409371+08:00 0 [Note] [MY-012932] [InnoDB] PUNCH HOLE support available
2022-12-27T14:12:50.409412+08:00 0 [Note] [MY-012944] [InnoDB] Uses event mutexes
2022-12-27T14:12:50.409429+08:00 0 [Note] [MY-012945] [InnoDB] GCC builtin __atomic_thread_fence() is used for memory barrier
2022-12-27T14:12:50.409454+08:00 0 [Note] [MY-012948] [InnoDB] Compressed tables use zlib 1.2.12
2022-12-27T14:12:50.409690+08:00 0 [Note] [MY-013251] [InnoDB] Number of pools: 1
2022-12-27T14:12:50.409870+08:00 0 [Note] [MY-012951] [InnoDB] Using software crc32.
2022-12-27T14:12:50.410640+08:00 0 [Note] [MY-012203] [InnoDB] Directories to scan './'
2022-12-27T14:12:50.410723+08:00 0 [Note] [MY-012204] [InnoDB] Scanning './'
2022-12-27T14:12:50.411662+08:00 0 [Note] [MY-012208] [InnoDB] Completed space ID check of 8 files.
2022-12-27T14:12:50.412740+08:00 0 [Note] [MY-012955] [InnoDB] Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M
2022-12-27T14:12:50.427006+08:00 0 [Note] [MY-012957] [InnoDB] Completed initialization of buffer pool
2022-12-27T14:12:50.435547+08:00 0 [Note] [MY-011951] [InnoDB] page_cleaner coordinator priority: -20
2022-12-27T14:12:50.436349+08:00 0 [Note] [MY-011954] [InnoDB] page_cleaner worker priority: -20
2022-12-27T14:12:50.443131+08:00 0 [Note] [MY-011954] [InnoDB] page_cleaner worker priority: -20
2022-12-27T14:12:50.443610+08:00 0 [Note] [MY-011954] [InnoDB] page_cleaner worker priority: -20
2022-12-27T14:12:50.554178+08:00 0 [Note] [MY-011825] [InnoDB] Creating redo log file at ./#innodb_redo/#ib_redo0_tmp with file_id 0 with size 33554432 bytes
2022-12-27T14:12:50.559198+08:00 0 [Note] [MY-011825] [InnoDB] Renaming redo log file from ./#innodb_redo/#ib_redo0_tmp to ./#innodb_redo/#ib_redo0
2022-12-27T14:12:50.561362+08:00 0 [Note] [MY-012893] [InnoDB] New redo log files created, LSN=19949277
2022-12-27T14:12:50.563225+08:00 0 [Note] [MY-013883] [InnoDB] The latest found checkpoint is at lsn = 19949580 in redo log file ./#innodb_redo/#ib_redo0.
2022-12-27T14:12:50.563914+08:00 0 [Note] [MY-013086] [InnoDB] Starting to parse redo log at lsn = 19949580, whereas checkpoint_lsn = 19949580 and start_lsn = 19949568
2022-12-27T14:12:50.567009+08:00 0 [Note] [MY-013083] [InnoDB] Log background threads are being started...
2022-12-27T14:12:50.629715+08:00 0 [Note] [MY-012532] [InnoDB] Applying a batch of 0 redo log records ...
2022-12-27T14:12:50.629767+08:00 0 [Note] [MY-012535] [InnoDB] Apply batch completed!
2022-12-27T14:12:50.630093+08:00 0 [Note] [MY-013084] [InnoDB] Log background threads are being closed...
2022-12-27T14:12:50.636912+08:00 0 [Note] [MY-013041] [InnoDB] Resizing redo log from 32M to 1024M (LSN=19949580) synchronously. If this takes too long, consider starting the server with large --innodb_redo_log_capacity, and resizing the redo log online using SET.
2022-12-27T14:12:50.638982+08:00 0 [Note] [MY-012968] [InnoDB] Starting to delete and rewrite redo log files.
2022-12-27T14:12:50.639099+08:00 0 [Note] [MY-011825] [InnoDB] Removing redo log file: ./#innodb_redo/#ib_redo0
2022-12-27T14:12:50.759951+08:00 0 [Note] [MY-011825] [InnoDB] Creating redo log file at ./#innodb_redo/#ib_redo0_tmp with file_id 0 with size 33554432 bytes
2022-12-27T14:12:50.763093+08:00 0 [Note] [MY-011825] [InnoDB] Renaming redo log file from ./#innodb_redo/#ib_redo0_tmp to ./#innodb_redo/#ib_redo0
2022-12-27T14:12:50.764946+08:00 0 [Note] [MY-012893] [InnoDB] New redo log files created, LSN=19950092
2022-12-27T14:12:50.765026+08:00 0 [Note] [MY-013083] [InnoDB] Log background threads are being started...
2022-12-27T14:12:50.819815+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_001'.
2022-12-27T14:12:50.820524+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_002'.
2022-12-27T14:12:50.821907+08:00 0 [Note] [MY-012910] [InnoDB] Opened 2 existing undo tablespaces.
2022-12-27T14:12:50.822000+08:00 0 [Note] [MY-011980] [InnoDB] GTID recovery trx_no: 4024
2022-12-27T14:12:50.840725+08:00 0 [Note] [MY-013776] [InnoDB] Parallel initialization of rseg complete
2022-12-27T14:12:50.840795+08:00 0 [Note] [MY-013777] [InnoDB] Time taken to initialize rseg using 2 thread: 18813 ms.
2022-12-27T14:12:50.843739+08:00 0 [Note] [MY-012255] [InnoDB] Removed temporary tablespace data file: "ibtmp1"
2022-12-27T14:12:50.843772+08:00 0 [Note] [MY-012923] [InnoDB] Creating shared tablespace for temporary tables
2022-12-27T14:12:50.843908+08:00 0 [Note] [MY-012265] [InnoDB] Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2022-12-27T14:12:50.887275+08:00 0 [Note] [MY-012266] [InnoDB] File './ibtmp1' size is now 12 MB.
2022-12-27T14:12:50.887629+08:00 0 [Note] [MY-013627] [InnoDB] Scanning temp tablespace dir:'./#innodb_temp/'
2022-12-27T14:12:50.922688+08:00 0 [Note] [MY-013018] [InnoDB] Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active.
2022-12-27T14:12:50.923127+08:00 0 [Note] [MY-012976] [InnoDB] 8.0.30 started; log sequence number 19950102
2022-12-27T14:12:50.923693+08:00 0 [Warning] [MY-012091] [InnoDB] Allocated tablespace ID 1 for sys/sys_config, old maximum was 0
2022-12-27T14:12:50.963362+08:00 0 [Note] [MY-011825] [Xtrabackup] starting shutdown with innodb_fast_shutdown = 1
2022-12-27T14:12:50.963476+08:00 0 [Note] [MY-012330] [InnoDB] FTS optimize thread exiting.
2022-12-27T14:12:51.963385+08:00 0 [Note] [MY-013072] [InnoDB] Starting shutdown...
2022-12-27T14:12:51.977190+08:00 0 [Note] [MY-013084] [InnoDB] Log background threads are being closed...
2022-12-27T14:12:52.001674+08:00 0 [Note] [MY-012980] [InnoDB] Shutdown completed; log sequence number 19950102
2022-12-27T14:12:52.002402+08:00 0 [Note] [MY-013251] [InnoDB] Number of pools: 1
2022-12-27T14:12:52.003848+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
[root@mysql2 ~]#
[root@mysql2 ~]#
[root@mysql2 ~]# ll /mysql/data
total 91360
-rw-r-----. 1 mysql mysql 56 Dec 9 13:32 auto.cnf
-rw-------. 1 mysql mysql 1680 Dec 9 13:32 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 Dec 9 13:32 ca.pem
-rw-r--r--. 1 mysql mysql 1112 Dec 9 13:32 client-cert.pem
-rw-------. 1 mysql mysql 1680 Dec 9 13:32 client-key.pem
drwxr-x---. 2 mysql mysql 48 Dec 16 14:07 #clone
-rw-r-----. 1 mysql mysql 589824 Dec 27 14:10 #ib_16384_0.dblwr
-rw-r-----. 1 mysql mysql 8978432 Dec 15 15:29 #ib_16384_1.dblwr
-rw-r-----. 1 mysql mysql 4138 Dec 16 14:28 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Dec 27 14:09 ibdata1
-rw-r-----. 1 mysql mysql 12582912 Dec 16 14:28 ibtmp1
drwxr-x---. 2 mysql mysql 4096 Dec 16 14:28 #innodb_redo
drwxr-x---. 2 mysql mysql 187 Dec 16 14:28 #innodb_temp
drwxr-x---. 2 mysql mysql 19 Dec 24 15:10 j
drwxr-x---. 2 mysql mysql 19 Dec 16 14:28 jyc
drwxr-x---. 2 mysql mysql 22 Dec 16 14:05 jycdb
drwxr-x---. 2 mysql mysql 143 Dec 15 15:29 mysql
-rw-r-----. 1 mysql mysql 14942 Dec 27 14:09 mysql2.err
-rw-r-----. 1 mysql mysql 313 Dec 15 15:47 mysql2.log
-rw-r-----. 1 mysql mysql 6 Dec 16 14:28 mysql2.pid
-rw-r-----. 1 mysql mysql 180 Dec 24 15:07 mysql2-relay-bin.000001
-rw-r-----. 1 mysql mysql 26 Dec 24 15:07 mysql2-relay-bin.index
-rw-r-----. 1 mysql mysql 1292 Dec 27 14:09 mysql-bin.000001
-rw-r-----. 1 mysql mysql 197 Dec 27 14:09 mysql-bin.000002
-rw-r-----. 1 mysql mysql 58 Dec 27 14:09 mysql-bin.index
-rw-r-----. 1 mysql mysql 25165824 Dec 27 14:09 mysql.ibd
-rw-r-----. 1 mysql mysql 6 Dec 15 15:29 mysql_upgrade_info
drwxr-x---. 2 mysql mysql 64 Dec 16 14:28 performance_schema
-rw-------. 1 mysql mysql 1680 Dec 9 13:32 private_key.pem
-rw-r--r--. 1 mysql mysql 452 Dec 9 13:32 public_key.pem
-rw-r--r--. 1 mysql mysql 1112 Dec 9 13:32 server-cert.pem
-rw-------. 1 mysql mysql 1676 Dec 9 13:32 server-key.pem
drwxr-x---. 2 mysql mysql 28 Dec 16 14:05 sys
drwxr-x---. 2 mysql mysql 19 Dec 16 14:05 t
-rw-r-----. 1 mysql mysql 16777216 Dec 27 14:10 undo_001
-rw-r-----. 1 mysql mysql 16777216 Dec 27 14:10 undo_002
[root@mysql2 ~]# service mysql stop
Shutting down MySQL.......... SUCCESS!
[root@mysql2 ~]# ll /mysql
total 304
drwxr-xr-x. 2 mysql mysql 4096 Sep 14 04:08 bin
drwxr-x---. 12 mysql mysql 4096 Dec 27 14:14 data
drwxr-xr-x. 2 mysql mysql 55 Sep 14 04:08 docs
drwxr-xr-x. 3 mysql mysql 4096 Sep 14 04:08 include
drwxr-xr-x. 6 mysql mysql 201 Sep 14 04:08 lib
-rw-r--r--. 1 mysql mysql 287627 Sep 14 00:15 LICENSE
drwxrwxr-x. 2 mysql mysql 22 Dec 15 15:36 log
drwxr-xr-x. 4 mysql mysql 30 Sep 14 04:08 man
-rw-r--r--. 1 mysql mysql 666 Sep 14 00:15 README
drwxr-xr-x. 28 mysql mysql 4096 Sep 14 04:08 share
drwxr-xr-x. 2 mysql mysql 77 Sep 14 04:08 support-files
[root@mysql2 ~]# mv /mysql/data /mysql/data.bak
[root@mysql2 ~]# mkdir -p /mysql/data
[root@mysql2 ~]# ll /mysql
total 304
drwxr-xr-x. 2 mysql mysql 4096 Sep 14 04:08 bin
drwxr-xr-x. 2 root root 6 Dec 27 14:15 data
drwxr-x---. 12 mysql mysql 4096 Dec 27 14:14 data.bak
drwxr-xr-x. 2 mysql mysql 55 Sep 14 04:08 docs
drwxr-xr-x. 3 mysql mysql 4096 Sep 14 04:08 include
drwxr-xr-x. 6 mysql mysql 201 Sep 14 04:08 lib
-rw-r--r--. 1 mysql mysql 287627 Sep 14 00:15 LICENSE
drwxrwxr-x. 2 mysql mysql 22 Dec 15 15:36 log
drwxr-xr-x. 4 mysql mysql 30 Sep 14 04:08 man
-rw-r--r--. 1 mysql mysql 666 Sep 14 00:15 README
drwxr-xr-x. 28 mysql mysql 4096 Sep 14 04:08 share
drwxr-xr-x. 2 mysql mysql 77 Sep 14 04:08 support-files
[root@mysql2 ~]# chown -R mysql.mysql /mysql/data
[root@mysql2 ~]# ll /mysql
total 304
drwxr-xr-x. 2 mysql mysql 4096 Sep 14 04:08 bin
drwxr-xr-x. 2 mysql mysql 6 Dec 27 14:15 data
drwxr-x---. 12 mysql mysql 4096 Dec 27 14:14 data.bak
drwxr-xr-x. 2 mysql mysql 55 Sep 14 04:08 docs
drwxr-xr-x. 3 mysql mysql 4096 Sep 14 04:08 include
drwxr-xr-x. 6 mysql mysql 201 Sep 14 04:08 lib
-rw-r--r--. 1 mysql mysql 287627 Sep 14 00:15 LICENSE
drwxrwxr-x. 2 mysql mysql 22 Dec 15 15:36 log
drwxr-xr-x. 4 mysql mysql 30 Sep 14 04:08 man
-rw-r--r--. 1 mysql mysql 666 Sep 14 00:15 README
drwxr-xr-x. 28 mysql mysql 4096 Sep 14 04:08 share
drwxr-xr-x. 2 mysql mysql 77 Sep 14 04:08 support-files
[root@mysql2 ~]# xtrabackup --copy-back --target-dir=/backup
2022-12-27T14:15:59.929918+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/mysql/data --innodb_buffer_pool_size=2G --innodb_flush_log_at_trx_commit=2 --log_bin=/mysql/data/mysql-bin --server-id=2
2022-12-27T14:15:59.930303+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --port=3306 --socket=/mysql/mysql.sock --copy-back=1 --target-dir=/backup
xtrabackup version 8.0.30-23 based on MySQL server 8.0.30 Linux (x86_64) (revision id: 873b467185c)
2022-12-27T14:15:59.930390+08:00 0 [Note] [MY-011825] [Xtrabackup] cd to /backup/
2022-12-27T14:15:59.931983+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying undo_001 to /mysql/data/undo_001
2022-12-27T14:15:59.967224+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying undo_001 to /mysql/data/undo_001
2022-12-27T14:16:00.000010+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying undo_002 to /mysql/data/undo_002
2022-12-27T14:16:00.036479+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying undo_002 to /mysql/data/undo_002
2022-12-27T14:16:00.071270+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying ibdata1 to /mysql/data/ibdata1
2022-12-27T14:16:00.089230+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying ibdata1 to /mysql/data/ibdata1
2022-12-27T14:16:00.119649+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying mysql-bin.000002 to /mysql/data/mysql-bin.000002
2022-12-27T14:16:00.119802+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying mysql-bin.000002 to /mysql/data/mysql-bin.000002
2022-12-27T14:16:00.124862+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying mysql-bin.index to /mysql/data/mysql-bin.index
2022-12-27T14:16:00.125121+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying mysql-bin.index to /mysql/data/mysql-bin.index
2022-12-27T14:16:00.128984+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./sys/sys_config.ibd to /mysql/data/sys/sys_config.ibd
2022-12-27T14:16:00.129350+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./sys/sys_config.ibd to /mysql/data/sys/sys_config.ibd
2022-12-27T14:16:00.132860+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./jycdb/test.ibd to /mysql/data/jycdb/test.ibd
2022-12-27T14:16:00.133126+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./jycdb/test.ibd to /mysql/data/jycdb/test.ibd
2022-12-27T14:16:00.136535+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./t/t.ibd to /mysql/data/t/t.ibd
2022-12-27T14:16:00.136790+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./t/t.ibd to /mysql/data/t/t.ibd
2022-12-27T14:16:00.139905+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./jyc/t.ibd to /mysql/data/jyc/t.ibd
2022-12-27T14:16:00.140276+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./jyc/t.ibd to /mysql/data/jyc/t.ibd
2022-12-27T14:16:00.141445+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./j/t.ibd to /mysql/data/j/t.ibd
2022-12-27T14:16:00.141693+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./j/t.ibd to /mysql/data/j/t.ibd
2022-12-27T14:16:00.143667+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./mysql.ibd to /mysql/data/mysql.ibd
2022-12-27T14:16:00.204798+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./mysql.ibd to /mysql/data/mysql.ibd
2022-12-27T14:16:00.243248+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./mysql/slow_log.CSM to /mysql/data/mysql/slow_log.CSM
2022-12-27T14:16:00.243416+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./mysql/slow_log.CSM to /mysql/data/mysql/slow_log.CSM
2022-12-27T14:16:00.245546+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./mysql/slow_log.CSV to /mysql/data/mysql/slow_log.CSV
2022-12-27T14:16:00.245592+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./mysql/slow_log.CSV to /mysql/data/mysql/slow_log.CSV
2022-12-27T14:16:00.247369+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./mysql/general_log.CSV to /mysql/data/mysql/general_log.CSV
2022-12-27T14:16:00.247425+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./mysql/general_log.CSV to /mysql/data/mysql/general_log.CSV
2022-12-27T14:16:00.249403+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./mysql/general_log.CSM to /mysql/data/mysql/general_log.CSM
2022-12-27T14:16:00.249540+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./mysql/general_log.CSM to /mysql/data/mysql/general_log.CSM
2022-12-27T14:16:00.251324+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./mysql/general_log_496.sdi to /mysql/data/mysql/general_log_496.sdi
2022-12-27T14:16:00.251470+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./mysql/general_log_496.sdi to /mysql/data/mysql/general_log_496.sdi
2022-12-27T14:16:00.253357+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./mysql/slow_log_498.sdi to /mysql/data/mysql/slow_log_498.sdi
2022-12-27T14:16:00.253511+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./mysql/slow_log_498.sdi to /mysql/data/mysql/slow_log_498.sdi
2022-12-27T14:16:00.255582+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./performance_schema/clone_status_376.sdi to /mysql/data/performance_schema/clone_status_376.sdi
2022-12-27T14:16:00.255714+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./performance_schema/clone_status_376.sdi to /mysql/data/performance_schema/clone_status_376.sdi
2022-12-27T14:16:00.258628+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./performance_schema/clone_progress_377.sdi to /mysql/data/performance_schema/clone_progress_377.sdi
2022-12-27T14:16:00.258790+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./performance_schema/clone_progress_377.sdi to /mysql/data/performance_schema/clone_progress_377.sdi
2022-12-27T14:16:00.261990+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./ib_buffer_pool to /mysql/data/ib_buffer_pool
2022-12-27T14:16:00.262157+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./ib_buffer_pool to /mysql/data/ib_buffer_pool
2022-12-27T14:16:00.265353+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./xtrabackup_info to /mysql/data/xtrabackup_info
2022-12-27T14:16:00.265487+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./xtrabackup_info to /mysql/data/xtrabackup_info
2022-12-27T14:16:00.268507+08:00 1 [Note] [MY-011825] [Xtrabackup] Creating directory ./#innodb_redo
2022-12-27T14:16:00.268558+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: creating directory ./#innodb_redo
2022-12-27T14:16:00.268737+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./ibtmp1 to /mysql/data/ibtmp1
2022-12-27T14:16:00.292850+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./ibtmp1 to /mysql/data/ibtmp1
2022-12-27T14:16:00.329425+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
[root@mysql2 ~]# ll /mysql/data
total 81940
-rw-r-----. 1 root root 4138 Dec 27 14:16 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Dec 27 14:16 ibdata1
-rw-r-----. 1 root root 12582912 Dec 27 14:16 ibtmp1
drwxr-x---. 2 root root 6 Dec 27 14:16 #innodb_redo
drwxr-x---. 2 root root 19 Dec 27 14:16 j
drwxr-x---. 2 root root 19 Dec 27 14:16 jyc
drwxr-x---. 2 root root 22 Dec 27 14:16 jycdb
drwxr-x---. 2 root root 143 Dec 27 14:16 mysql
-rw-r-----. 1 root root 197 Dec 27 14:16 mysql-bin.000002
-rw-r-----. 1 root root 17 Dec 27 14:16 mysql-bin.index
-rw-r-----. 1 root root 25165824 Dec 27 14:16 mysql.ibd
drwxr-x---. 2 root root 64 Dec 27 14:16 performance_schema
drwxr-x---. 2 root root 28 Dec 27 14:16 sys
drwxr-x---. 2 root root 19 Dec 27 14:16 t
-rw-r-----. 1 root root 16777216 Dec 27 14:15 undo_001
-rw-r-----. 1 root root 16777216 Dec 27 14:16 undo_002
-rw-r-----. 1 root root 570 Dec 27 14:16 xtrabackup_info
[root@mysql2 ~]# chown -R mysql.mysql /mysql/data
[root@mysql2 ~]# ll /mysql/data
total 81940
-rw-r-----. 1 mysql mysql 4138 Dec 27 14:16 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Dec 27 14:16 ibdata1
-rw-r-----. 1 mysql mysql 12582912 Dec 27 14:16 ibtmp1
drwxr-x---. 2 mysql mysql 6 Dec 27 14:16 #innodb_redo
drwxr-x---. 2 mysql mysql 19 Dec 27 14:16 j
drwxr-x---. 2 mysql mysql 19 Dec 27 14:16 jyc
drwxr-x---. 2 mysql mysql 22 Dec 27 14:16 jycdb
drwxr-x---. 2 mysql mysql 143 Dec 27 14:16 mysql
-rw-r-----. 1 mysql mysql 197 Dec 27 14:16 mysql-bin.000002
-rw-r-----. 1 mysql mysql 17 Dec 27 14:16 mysql-bin.index
-rw-r-----. 1 mysql mysql 25165824 Dec 27 14:16 mysql.ibd
drwxr-x---. 2 mysql mysql 64 Dec 27 14:16 performance_schema
drwxr-x---. 2 mysql mysql 28 Dec 27 14:16 sys
drwxr-x---. 2 mysql mysql 19 Dec 27 14:16 t
-rw-r-----. 1 mysql mysql 16777216 Dec 27 14:15 undo_001
-rw-r-----. 1 mysql mysql 16777216 Dec 27 14:16 undo_002
-rw-r-----. 1 mysql mysql 570 Dec 27 14:16 xtrabackup_info
[root@mysql2 ~]# service mysql start
Starting MySQL.Logging to '/mysql/data/mysql2.err'.
... SUCCESS!
[root@mysql2 ~]# mysql -h 192.168.207.132 -P 3306 -u root -pabcd1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.31 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.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| j |
| jyc |
| jycdb |
| mysql |
| performance_schema |
| sys |
| t |
+--------------------+
8 rows in set (0.01 sec)
mysql> show processlist;
+----+-----------------+-----------------------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------------+------+---------+------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 28 | Waiting on empty queue | NULL |
| 8 | root | 192.168.207.132:57472 | NULL | Query | 0 | init | show processlist |
+----+-----------------+-----------------------+------+---------+------+------------------------+------------------+
2 rows in set (0.00 sec)
mysql> SELECT trx.trx_id, trx.trx_started, trx.trx_mysql_thread_id FROM INFORMATION_SCHEMA.INNODB_TRX trx JOIN INFORMATION_SCHEMA.PROCESSLIST ps ON trmysql> AND ps.user != 'system_user';
mysql> SELECT (unix_timestamp(now()) - unix_timestamp(trx.trx_started))/3600 as hours, trx.trx_mysql_thread_id, pt.processlist_user, pt.processlist_host, pt.processlist_command, ps.info FROM INFORMATION_SCHEMA.INNODB_TRX trx INNER JOIN INFORMATION_SCHEMA.PROCESSLIST ps ON (ps.id = trx.trx_mysql_thread_id) INNER JOIN performance_schema.threads pt ON (pt.processlist_id = trx.trx_mysql_thread_id) WHERE trx.trx_started < CURRENT_TIMESTAMP - INTERVAL 1 HOUR AND ps.usermysql> AND ps.user != 'system_user';
mysql> SELECT (unix_timestamp(now()) - unix_timestamp(trx.trx_started))/3600 as hours, trx.trx_mysql_thread_id, pt.processlist_user, pt.processlist_host, pt.processlist_command, ps.info FROM INFORMATION_SCHEMA.INNODB_TRX trx INNER JOIN INFORMATION_SCHEMA.PROCESSLIST ps ON (ps.id = trx.trx_mysql_thread_id) INNER JOIN performance_schema.threads pt ON (pt.processlist_id = trx.trx_mysql_thread_id) WHERE trx.trx_started < CURRENT_TIMESTAMP - INTERVAL 1 HOUR AND ps.usermysql> AND ps.user != 'system_user';```
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 197
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: d0271cef-7782-11ed-828a-d67f35a9de29:1-5
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show slave status\G;
Empty set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql> show processlist;
+----+-----------------+-----------------------+------+------------------+------+-----------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------------+------+------------------+------+-----------------------------------------------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 165 | Waiting on empty queue | NULL |
| 8 | root | 192.168.207.132:57472 | NULL | Query | 0 | init | show processlist |
| 9 | root | 192.168.207.131:35112 | NULL | Binlog Dump GTID | 124 | Source has sent all binlog to replica; waiting for more updates | NULL |
+----+-----------------+-----------------------+------+------------------+------+-----------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
mysql> drop database t;
Query OK, 1 row affected (0.03 sec)
mysql> drop database j;
Query OK, 1 row affected (0.01 sec)
mysql> show processlist;
+----+-----------------+-----------------------+------+------------------+------+-----------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------------+------+------------------+------+-----------------------------------------------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 210 | Waiting on empty queue | NULL |
| 8 | root | 192.168.207.132:57472 | NULL | Query | 0 | init | show processlist |
| 9 | root | 192.168.207.131:35112 | NULL | Binlog Dump GTID | 169 | Source has sent all binlog to replica; waiting for more updates | NULL |
+----+-----------------+-----------------------+------+------------------+------+-----------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
mysql> show slave status\G;
Empty set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 541
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 0489844a-85ae-11ed-97ee-d67f35a9de29:1-2,
d0271cef-7782-11ed-828a-d67f35a9de29:1-5
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 541
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 0489844a-85ae-11ed-97ee-d67f35a9de29:1-2,
d0271cef-7782-11ed-828a-d67f35a9de29:1-5
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
mysql> use test;
Database changed
mysql> create table t(id int);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t values(1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test.t;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> exit
Bye
从库端：
*****[2022-12-27 14:18:05]*****
Last login: Mon Dec 26 12:48:57 2022 from 10.168.20.66
[root@mysql1 ~]# mysql -u root -pabcd1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 33
Server version: 8.0.31 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.
mysql> show processlist;
+----+-----------------+-----------------+------+---------+--------+----------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+------+---------+--------+----------------------------------------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 419196 | Waiting on empty queue | NULL |
| 20 | system user | connecting host | NULL | Connect | 256132 | Waiting for source to send event | NULL |
| 21 | system user | | NULL | Query | 62 | Replica has read all relay log; waiting for more updates | NULL |
| 22 | system user | | NULL | Query | 532 | Waiting for an event from Coordinator | NULL |
| 23 | system user | | NULL | Connect | 256132 | Waiting for an event from Coordinator | NULL |
| 24 | system user | | NULL | Connect | 256132 | Waiting for an event from Coordinator | NULL |
| 25 | system user | | NULL | Connect | 256132 | Waiting for an event from Coordinator | NULL |
| 33 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-----------------+-----------------+------+---------+--------+----------------------------------------------------------+------------------+
8 rows in set (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.207.132
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 197
Relay_Log_File: mysql1-relay-bin.000009
Relay_Log_Pos: 413
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 197
Relay_Log_Space: 880
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: 0489844a-85ae-11ed-97ee-d67f35a9de29
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: d0271cef-7782-11ed-828a-d67f35a9de29:1-5
Executed_Gtid_Set: 740dc65b-7ced-11ed-9e87-82b000a4e5ee:1-9,
d0271cef-7782-11ed-828a-d67f35a9de29:1-5
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000006
Position: 1486
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 740dc65b-7ced-11ed-9e87-82b000a4e5ee:1-9,
d0271cef-7782-11ed-828a-d67f35a9de29:1-5
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| j |
| jyc |
| jycdb |
| mysql |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| jyc |
| jycdb |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> show processlist;
+----+-----------------+-----------------+------+---------+--------+----------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+------+---------+--------+----------------------------------------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 419310 | Waiting on empty queue | NULL |
| 20 | system user | connecting host | NULL | Connect | 256246 | Waiting for source to send event | NULL |
| 21 | system user | | NULL | Query | 18 | Replica has read all relay log; waiting for more updates | NULL |
| 22 | system user | | NULL | Query | 20 | Waiting for an event from Coordinator | NULL |
| 23 | system user | | NULL | Connect | 256246 | Waiting for an event from Coordinator | NULL |
| 24 | system user | | NULL | Connect | 256246 | Waiting for an event from Coordinator | NULL |
| 25 | system user | | NULL | Connect | 256246 | Waiting for an event from Coordinator | NULL |
| 33 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-----------------+-----------------+------+---------+--------+----------------------------------------------------------+------------------+
8 rows in set (0.00 sec)
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000006
Position: 1844
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 0489844a-85ae-11ed-97ee-d67f35a9de29:1-2,
740dc65b-7ced-11ed-9e87-82b000a4e5ee:1-9,
d0271cef-7782-11ed-828a-d67f35a9de29:1-5
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.207.132
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 541
Relay_Log_File: mysql1-relay-bin.000009
Relay_Log_Pos: 757
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 541
Relay_Log_Space: 1224
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: 0489844a-85ae-11ed-97ee-d67f35a9de29
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 0489844a-85ae-11ed-97ee-d67f35a9de29:1-2,
d0271cef-7782-11ed-828a-d67f35a9de29:1-5
Executed_Gtid_Set: 0489844a-85ae-11ed-97ee-d67f35a9de29:1-2,
740dc65b-7ced-11ed-9e87-82b000a4e5ee:1-9,
d0271cef-7782-11ed-828a-d67f35a9de29:1-5
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql> select * from test.t;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| jyc |
| jycdb |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
7 rows in set (0.01 sec)
最后修改时间：2022-12-27 19:03:06
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
