【MySQL 8.0】部分备份与恢复验证

[mysql@node01 ~]$ xtrabackup --user=xtrabackup --password=xtrabackup --databases="tpcc10" --backup --target-dir=/home/mysql/backup/partial

[mysql@node01 ~]$ scp -r backup/partial/* node02:/home/mysql/backup/partial

[mysql@node01 ~]$ mysqldump -uroot -pabcd.1234 --no-data --databases tpcc10 > tpcc10.sql

[mysql@node01 ~]$ scp tpcc10.sql node02:/home/mysql/

[mysql@node02 ~]$  mysql -uroot -pabcd.1234 < tpcc10.sql

[mysql@node02 ~]$ mysql -uroot -pabcd.1234 -e " select concat('alter table ', table_name, ' discard tablespace;') as '#' from information_schema.tables where table_schema = 'tpcc10';" >discard_ts.sql

[mysql@node02 ~]$ cat discard_ts.sql
#
alter table customer discard tablespace;
alter table district discard tablespace;
alter table history discard tablespace;
alter table item discard tablespace;
alter table new_orders discard tablespace;
alter table order_line discard tablespace;
alter table orders discard tablespace;
alter table stock discard tablespace;
alter table warehouse discard tablespace;

[mysql@node02 ~]$ mysql -uroot -pabcd.1234 tpcc10 < discard_ts.sql

[mysql@node02 ~]$ xtrabackup --prepare --export --target-dir=/home/mysql/backup/partial

[mysql@node02 ~]$ cp /home/mysql/backup/partial/tpcc10/*.cfg /usr/local/mysql/data/tpcc10/
[mysql@node02 ~]$ cp /home/mysql/backup/partial/tpcc10/*.ibd /usr/local/mysql/data/tpcc10/

[mysql@node02 ~]$ mysql -uroot -pabcd.1234 -e " select concat('alter table ', table_name, ' import tablespace;') as '#' from information_schema.tables where table_schema = 'tpcc10';" >import_ts.sql

[mysql@node02 ~]$ cat import_ts.sql
#
alter table customer import tablespace;
alter table district import tablespace;
alter table history import tablespace;
alter table item import tablespace;
alter table new_orders import tablespace;
alter table order_line import tablespace;
alter table orders import tablespace;
alter table stock import tablespace;
alter table warehouse import tablespace;

[mysql@node02 ~]$ mysql -uroot -pabcd.1234 tpcc10 < import_ts.sql

[mysql@node02 ~]$ cat backup/partial/xtrabackup_binlog_info
mysql-bin.000018        1383

[mysql@node02 ~]$ cd binlog
[mysql@node02 binlog]$ mysqlbinlog --database=tpcc10 --start-position=1383 mysql-bin.000018  | mysql -uroot -pabcd.1234