MySQL 8.0 drop table恢复
关于MySQL的drop table可能有很多恢复的方法，下面说一种，基于全备，复制，然后让sql_thread停在一个指定位置的方法来恢复。下面进行模拟恢复
环境信息
MySQL版本信息：Server version: 8.0.27 MySQL Community Server - GPL
os：
[root@test28 ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.9 (Maipo)
场景描述：
1、利用sysbench 造10张表，每个表1000行记录
2、压测中利用xtrabackup做一个全备(full backup)
3、drop tables sbtest9
需求：
恢复sbtest9
思路梳理
主要步骤思路如下：
1、sysbench prepare后，然后run进行压测；
2、做一个全备份（模拟日常业务正常运行，每天或者每周做次热备份）
3、drop table（运行过程中，有人删了一张表）
4、flush logs；找到Drop对应得GTID
5、拿全备恢复一个从库 start slave io_thread；
6、start slave until sql_before_gtids=
7、dump或者表空间传输得方法，把主从都恢复一下。
恢复后的架构如下，3308实例为我们要新创建的从库，3306实例为主库，3310实例为原本复制正常的从库。
恢复过程
sysbench 造数据，压测
首先创建一个库
root@localhost 11:23: [(none)]> create database sbtest;
Query OK, 1 row affected (0.00 sec)
然后利用lua脚本造数：
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_read_write.lua --mysql-socket=/tmp/mysql3306.sock --mysql-user=root --mysql-password=’
mysql’ --mysql-db=sbtest --db-driver=mysql --tables=10 --table-size=1000 --report-interval=10 --threads=10 --time=120 prepare
[root@test28 sysbench]# /usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_read_write.lua --mysql-socket=/tmp/mysql3306.sock --mysql-user=root --mysql-password='
mysql' --mysql-db=sbtest --db-driver=mysql --tables=10 --table-size=1000 --report-interval=10 --threads=10 --time=120 prepare
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
Initializing worker threads...
Creating table 'sbtest6'...
Creating table 'sbtest10'...
Creating table 'sbtest2'...
Creating table 'sbtest9'...
Creating table 'sbtest4'...
Creating table 'sbtest7'...
Creating table 'sbtest1'...
Creating table 'sbtest5'...
Creating table 'sbtest3'...
Creating table 'sbtest8'...
Inserting 1000 records into 'sbtest4'
Inserting 1000 records into 'sbtest8'
Inserting 1000 records into 'sbtest1'
Inserting 1000 records into 'sbtest10'
Inserting 1000 records into 'sbtest5'
Inserting 1000 records into 'sbtest7'
Inserting 1000 records into 'sbtest3'
Inserting 1000 records into 'sbtest9'
Inserting 1000 records into 'sbtest2'
Inserting 1000 records into 'sbtest6'
Creating a secondary index on 'sbtest8'...
Creating a secondary index on 'sbtest4'...
Creating a secondary index on 'sbtest7'...
Creating a secondary index on 'sbtest1'...
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest6'...
Creating a secondary index on 'sbtest3'...
Creating a secondary index on 'sbtest9'...
Creating a secondary index on 'sbtest5'...
Creating a secondary index on 'sbtest10'...
然后run进行压测，跑出压力
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_read_write.lua --mysql-socket=/tmp/mysql3306.sock --mysql-user=root --mysql-password=’
mysql’ --mysql-db=sbtest --db-driver=mysql --tables=10 --table-size=1000 --report-interval=10 --threads=120 --time=3600 run
[root@test28 sysbench]# /usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_read_write.lua --mysql-socket=/tmp/mysql3306.sock --mysql-user=root --mysql-password='
mysql' --mysql-db=sbtest --db-driver=mysql --tables=10 --table-size=1000 --report-interval=10 --threads=120 --time=3600 run
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 120
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 120 tps: 1472.92 qps: 29569.55 (r/w/o: 20713.97/5897.77/2957.81) lat (ms,95%): 164.45 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 120 tps: 1622.06 qps: 32471.51 (r/w/o: 22731.58/6495.92/3244.01) lat (ms,95%): 150.29 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 120 tps: 1600.80 qps: 32038.31 (r/w/o: 22425.30/6411.50/3201.50) lat (ms,95%): 150.29 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 120 tps: 1595.50 qps: 31920.58 (r/w/o: 22343.06/6386.52/3191.01) lat (ms,95%): 147.61 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 120 tps: 1631.80 qps: 32618.22 (r/w/o: 22835.35/6519.18/3263.69) lat (ms,95%): 144.97 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 120 tps: 1587.26 qps: 31795.28 (r/w/o: 22252.99/6367.76/3174.53) lat (ms,95%): 147.61 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 120 tps: 1619.28 qps: 32354.57 (r/w/o: 22650.50/6465.41/3238.66) lat (ms,95%): 144.97 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 120 tps: 1647.55 qps: 32936.01 (r/w/o: 23057.71/6583.40/3294.90) lat (ms,95%): 139.85 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 120 tps: 1599.41 qps: 31989.87 (r/w/o: 22393.42/6397.43/3199.02) lat (ms,95%): 144.97 err/s: 0.00 reconn/s: 0.00
。。。。。。。
查看连接：
root@localhost 15:12: [(none)]> show processlist\G
*************************** 1. row ***************************
Id: 5
User: event_scheduler
Host: localhost
db: NULL
Command: Daemon
Time: 181618
State: Waiting on empty queue
Info: NULL
*************************** 2. row ***************************
Id: 10
User: repl
Host: test28:38018
db: NULL
Command: Binlog Dump GTID
Time: 181559
State: Sending binlog event to replica
Info: NULL
*************************** 3. row ***************************
Id: 160
User: root
Host: localhost
db: sbtest
Command: Execute
Time: 0
State: Opening tables
Info: SELECT c FROM sbtest6 WHERE id BETWEEN 371 AND 470
*************************** 4. row ***************************
Id: 161
User: root
Host: localhost
db: sbtest
Command: Execute
Time: 0
State: closing tables
Info: SELECT c FROM sbtest9 WHERE id=864
。。。。
*************************** 123. row ***************************
Id: 284
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: init
Info: show processlist
123 rows in set (0.00 sec)
xtrabackup全备数据库
xtrabackup --defaults-file=/data/mysql/mysql3306/my3306.cnf -S /tmp/mysql3306.sock -uroot -pmysql --backup --target-dir=/data/backup/db3306_full --no-server-version-check
[root@test28 backup]# xtrabackup --defaults-file=/data/mysql/mysql3306/my3306.cnf -S /tmp/mysql3306.sock -uroot -pmysql --backup --target-dir=/data/backup/db3306_full --no-server-versio
n-check
xtrabackup: recognized server arguments: --server-id=3306 --datadir=/data/mysql/mysql3306/data --open_files_limit=65535 --log_bin=/data/mysql/mysql3306/logs/mybinlog --innodb_buffer_pool_size=200M --innodb_data_file_path=ibdata1:12M:autoextend --innodb_flush_log_at_trx_commit=1 --innodb_log_buffer_size=32M --innodb_log_file_size=200M --innodb_log_files_in_group=3 --innodb_io_capacity=4000 --innodb_open_files=65535 --innodb_flush_method=O_DIRECT --innodb_adaptive_hash_index=0
xtrabackup: recognized client arguments: --port=3306 --socket=/tmp/mysql3306.sock --socket=/tmp/mysql3306.sock --user=root --password=* --backup=1 --target-dir=/data/backup/db3306_full --no-server-version-check=1
xtrabackup version 8.0.26-18 based on MySQL server 8.0.26 Linux (x86_64) (revision id: 4aecf82)
220115 14:57:52 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/tmp/mysql3306.sock' as 'root' (using password: YES).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/tmp/mysql3306.sock','root',...) failed: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at - line 1535.
220115 14:57:52 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /tmp/mysql3306.sock
Using server version 8.0.27
220115 14:57:52 Executing LOCK INSTANCE FOR BACKUP...
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysql/mysql3306/data
xtrabackup: open files limit requested 65535, set to 1000000
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 209715200
xtrabackup: using O_DIRECT
Number of pools: 1
xtrabackup: inititialize_service_handles suceeded
220115 14:57:54 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /tmp/mysql3306.sock
xtrabackup: Redo Log Archiving is not set up.
Starting to parse redo log at lsn = 1544060604
Recovery parsing buffer extended to 4194304.
Recovery parsing buffer extended to 8388608.
Recovery parsing buffer extended to 16777216.
220115 14:57:59 >> log scanned up to (1616220312)
xtrabackup: Generating a list of tablespaces
xtrabackup: Generating a list of tablespaces
Scanning './'
Completed space ID check of 2 files.
Allocated tablespace ID 50 for sbtest/sbtest8, old maximum was 0
Using undo tablespace './undo_001'.
Using undo tablespace './undo_002'.
Opened 2 existing undo tablespaces.
220115 14:57:59 [01] Copying ./ibdata1 to /data/backup/db3306_full/ibdata1
220115 14:57:59 [01] ...done
220115 14:57:59 [01] Copying ./sys/sys_config.ibd to /data/backup/db3306_full/sys/sys_config.ibd
220115 14:57:59 [01] ...done
220115 14:57:59 [01] Copying ./zhuo/t1.ibd to /data/backup/db3306_full/zhuo/t1.ibd
220115 14:57:59 [01] ...done
220115 14:57:59 [01] Copying ./sbtest/sbtest4.ibd to /data/backup/db3306_full/sbtest/sbtest4.ibd
220115 14:57:59 [01] ...done
220115 14:57:59 [01] Copying ./sbtest/sbtest3.ibd to /data/backup/db3306_full/sbtest/sbtest3.ibd
220115 14:57:59 [01] ...done
220115 14:57:59 [01] Copying ./sbtest/sbtest8.ibd to /data/backup/db3306_full/sbtest/sbtest8.ibd
220115 14:57:59 [01] ...done
220115 14:57:59 [01] Copying ./sbtest/sbtest10.ibd to /data/backup/db3306_full/sbtest/sbtest10.ibd
220115 14:57:59 [01] ...done
220115 14:57:59 [01] Copying ./sbtest/sbtest9.ibd to /data/backup/db3306_full/sbtest/sbtest9.ibd
220115 14:57:59 [01] ...done
220115 14:57:59 [01] Copying ./sbtest/sbtest5.ibd to /data/backup/db3306_full/sbtest/sbtest5.ibd
220115 14:57:59 [01] ...done
220115 14:57:59 [01] Copying ./sbtest/sbtest2.ibd to /data/backup/db3306_full/sbtest/sbtest2.ibd
220115 14:57:59 [01] ...done
220115 14:57:59 [01] Copying ./sbtest/sbtest7.ibd to /data/backup/db3306_full/sbtest/sbtest7.ibd
220115 14:57:59 [01] ...done
220115 14:57:59 [01] Copying ./sbtest/sbtest6.ibd to /data/backup/db3306_full/sbtest/sbtest6.ibd
220115 14:57:59 [01] ...done
220115 14:57:59 [01] Copying ./sbtest/sbtest1.ibd to /data/backup/db3306_full/sbtest/sbtest1.ibd
220115 14:57:59 [01] ...done
220115 14:57:59 [01] Copying ./mysql.ibd to /data/backup/db3306_full/mysql.ibd
220115 14:58:00 [01] ...done
220115 14:58:00 [01] Copying ./undo_002 to /data/backup/db3306_full/undo_002
220115 14:58:00 [01] ...done
220115 14:58:00 [01] Copying ./undo_001 to /data/backup/db3306_full/undo_001
220115 14:58:00 >> log scanned up to (1619279817)
220115 14:58:00 [01] ...done
220115 14:58:01 Starting to backup non-InnoDB tables and files
220115 14:58:01 [01] Copying mysql/general_log_213.sdi to /data/backup/db3306_full/mysql/general_log_213.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying mysql/general_log.CSM to /data/backup/db3306_full/mysql/general_log.CSM
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying mysql/general_log.CSV to /data/backup/db3306_full/mysql/general_log.CSV
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying mysql/slow_log_214.sdi to /data/backup/db3306_full/mysql/slow_log_214.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying mysql/slow_log.CSM to /data/backup/db3306_full/mysql/slow_log.CSM
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying mysql/slow_log.CSV to /data/backup/db3306_full/mysql/slow_log.CSV
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/cond_instances_82.sdi to /data/backup/db3306_full/performance_schema/cond_instances_82.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/error_log_83.sdi to /data/backup/db3306_full/performance_schema/error_log_83.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_waits_cur_84.sdi to /data/backup/db3306_full/performance_schema/events_waits_cur_84.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_waits_his_85.sdi to /data/backup/db3306_full/performance_schema/events_waits_his_85.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_waits_his_86.sdi to /data/backup/db3306_full/performance_schema/events_waits_his_86.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_waits_sum_87.sdi to /data/backup/db3306_full/performance_schema/events_waits_sum_87.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_waits_sum_88.sdi to /data/backup/db3306_full/performance_schema/events_waits_sum_88.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_waits_sum_89.sdi to /data/backup/db3306_full/performance_schema/events_waits_sum_89.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_waits_sum_90.sdi to /data/backup/db3306_full/performance_schema/events_waits_sum_90.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_waits_sum_91.sdi to /data/backup/db3306_full/performance_schema/events_waits_sum_91.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_waits_sum_92.sdi to /data/backup/db3306_full/performance_schema/events_waits_sum_92.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 >> log scanned up to (1622210888)
220115 14:58:01 [01] Copying performance_schema/file_instances_93.sdi to /data/backup/db3306_full/performance_schema/file_instances_93.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/file_summary_by__94.sdi to /data/backup/db3306_full/performance_schema/file_summary_by__94.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/file_summary_by__95.sdi to /data/backup/db3306_full/performance_schema/file_summary_by__95.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/host_cache_96.sdi to /data/backup/db3306_full/performance_schema/host_cache_96.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/mutex_instances_97.sdi to /data/backup/db3306_full/performance_schema/mutex_instances_97.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/objects_summary__98.sdi to /data/backup/db3306_full/performance_schema/objects_summary__98.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/performance_time_99.sdi to /data/backup/db3306_full/performance_schema/performance_time_99.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/processlist_100.sdi to /data/backup/db3306_full/performance_schema/processlist_100.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/rwlock_instances_101.sdi to /data/backup/db3306_full/performance_schema/rwlock_instances_101.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/setup_actors_102.sdi to /data/backup/db3306_full/performance_schema/setup_actors_102.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/setup_consumers_103.sdi to /data/backup/db3306_full/performance_schema/setup_consumers_103.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/setup_instrument_104.sdi to /data/backup/db3306_full/performance_schema/setup_instrument_104.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/setup_objects_105.sdi to /data/backup/db3306_full/performance_schema/setup_objects_105.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/setup_threads_106.sdi to /data/backup/db3306_full/performance_schema/setup_threads_106.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/table_io_waits_s_107.sdi to /data/backup/db3306_full/performance_schema/table_io_waits_s_107.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/table_io_waits_s_108.sdi to /data/backup/db3306_full/performance_schema/table_io_waits_s_108.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/table_lock_waits_109.sdi to /data/backup/db3306_full/performance_schema/table_lock_waits_109.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/threads_110.sdi to /data/backup/db3306_full/performance_schema/threads_110.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_stages_cu_111.sdi to /data/backup/db3306_full/performance_schema/events_stages_cu_111.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_stages_hi_112.sdi to /data/backup/db3306_full/performance_schema/events_stages_hi_112.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_stages_hi_113.sdi to /data/backup/db3306_full/performance_schema/events_stages_hi_113.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_stages_su_114.sdi to /data/backup/db3306_full/performance_schema/events_stages_su_114.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_stages_su_115.sdi to /data/backup/db3306_full/performance_schema/events_stages_su_115.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_stages_su_116.sdi to /data/backup/db3306_full/performance_schema/events_stages_su_116.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_stages_su_117.sdi to /data/backup/db3306_full/performance_schema/events_stages_su_117.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_stages_su_118.sdi to /data/backup/db3306_full/performance_schema/events_stages_su_118.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_statement_119.sdi to /data/backup/db3306_full/performance_schema/events_statement_119.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_statement_120.sdi to /data/backup/db3306_full/performance_schema/events_statement_120.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_statement_121.sdi to /data/backup/db3306_full/performance_schema/events_statement_121.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_statement_122.sdi to /data/backup/db3306_full/performance_schema/events_statement_122.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_statement_123.sdi to /data/backup/db3306_full/performance_schema/events_statement_123.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_statement_124.sdi to /data/backup/db3306_full/performance_schema/events_statement_124.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_statement_125.sdi to /data/backup/db3306_full/performance_schema/events_statement_125.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_statement_126.sdi to /data/backup/db3306_full/performance_schema/events_statement_126.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_statement_127.sdi to /data/backup/db3306_full/performance_schema/events_statement_127.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_statement_128.sdi to /data/backup/db3306_full/performance_schema/events_statement_128.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_statement_129.sdi to /data/backup/db3306_full/performance_schema/events_statement_129.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_statement_130.sdi to /data/backup/db3306_full/performance_schema/events_statement_130.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_transacti_131.sdi to /data/backup/db3306_full/performance_schema/events_transacti_131.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_transacti_132.sdi to /data/backup/db3306_full/performance_schema/events_transacti_132.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_transacti_133.sdi to /data/backup/db3306_full/performance_schema/events_transacti_133.sdi
220115 14:58:01 [01] ...done
220115 14:58:01 [01] Copying performance_schema/events_transacti_134.sdi to /data/backup/db3306_full/performance_schema/events_transacti_134.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/events_transacti_135.sdi to /data/backup/db3306_full/performance_schema/events_transacti_135.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/events_transacti_136.sdi to /data/backup/db3306_full/performance_schema/events_transacti_136.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/events_transacti_137.sdi to /data/backup/db3306_full/performance_schema/events_transacti_137.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/events_transacti_138.sdi to /data/backup/db3306_full/performance_schema/events_transacti_138.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/events_errors_su_139.sdi to /data/backup/db3306_full/performance_schema/events_errors_su_139.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/events_errors_su_140.sdi to /data/backup/db3306_full/performance_schema/events_errors_su_140.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/events_errors_su_141.sdi to /data/backup/db3306_full/performance_schema/events_errors_su_141.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/events_errors_su_142.sdi to /data/backup/db3306_full/performance_schema/events_errors_su_142.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/events_errors_su_143.sdi to /data/backup/db3306_full/performance_schema/events_errors_su_143.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/users_144.sdi to /data/backup/db3306_full/performance_schema/users_144.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/accounts_145.sdi to /data/backup/db3306_full/performance_schema/accounts_145.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/hosts_146.sdi to /data/backup/db3306_full/performance_schema/hosts_146.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/socket_instances_147.sdi to /data/backup/db3306_full/performance_schema/socket_instances_147.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/socket_summary_b_148.sdi to /data/backup/db3306_full/performance_schema/socket_summary_b_148.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/socket_summary_b_149.sdi to /data/backup/db3306_full/performance_schema/socket_summary_b_149.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/session_connect__150.sdi to /data/backup/db3306_full/performance_schema/session_connect__150.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/session_account__151.sdi to /data/backup/db3306_full/performance_schema/session_account__151.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/keyring_keys_152.sdi to /data/backup/db3306_full/performance_schema/keyring_keys_152.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/memory_summary_g_153.sdi to /data/backup/db3306_full/performance_schema/memory_summary_g_153.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/memory_summary_b_154.sdi to /data/backup/db3306_full/performance_schema/memory_summary_b_154.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/memory_summary_b_155.sdi to /data/backup/db3306_full/performance_schema/memory_summary_b_155.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/memory_summary_b_156.sdi to /data/backup/db3306_full/performance_schema/memory_summary_b_156.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/memory_summary_b_157.sdi to /data/backup/db3306_full/performance_schema/memory_summary_b_157.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/table_handles_158.sdi to /data/backup/db3306_full/performance_schema/table_handles_158.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/metadata_locks_159.sdi to /data/backup/db3306_full/performance_schema/metadata_locks_159.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/data_locks_160.sdi to /data/backup/db3306_full/performance_schema/data_locks_160.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/data_lock_waits_161.sdi to /data/backup/db3306_full/performance_schema/data_lock_waits_161.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/replication_conn_162.sdi to /data/backup/db3306_full/performance_schema/replication_conn_162.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/replication_grou_163.sdi to /data/backup/db3306_full/performance_schema/replication_grou_163.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/replication_conn_164.sdi to /data/backup/db3306_full/performance_schema/replication_conn_164.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/replication_appl_165.sdi to /data/backup/db3306_full/performance_schema/replication_appl_165.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/replication_appl_166.sdi to /data/backup/db3306_full/performance_schema/replication_appl_166.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/replication_appl_167.sdi to /data/backup/db3306_full/performance_schema/replication_appl_167.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/replication_appl_168.sdi to /data/backup/db3306_full/performance_schema/replication_appl_168.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/replication_grou_169.sdi to /data/backup/db3306_full/performance_schema/replication_grou_169.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/replication_appl_170.sdi to /data/backup/db3306_full/performance_schema/replication_appl_170.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/replication_appl_171.sdi to /data/backup/db3306_full/performance_schema/replication_appl_171.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/replication_asyn_172.sdi to /data/backup/db3306_full/performance_schema/replication_asyn_172.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/replication_asyn_173.sdi to /data/backup/db3306_full/performance_schema/replication_asyn_173.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/log_status_174.sdi to /data/backup/db3306_full/performance_schema/log_status_174.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/prepared_stateme_175.sdi to /data/backup/db3306_full/performance_schema/prepared_stateme_175.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/user_variables_b_176.sdi to /data/backup/db3306_full/performance_schema/user_variables_b_176.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/status_by_accoun_177.sdi to /data/backup/db3306_full/performance_schema/status_by_accoun_177.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/status_by_host_178.sdi to /data/backup/db3306_full/performance_schema/status_by_host_178.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/status_by_thread_179.sdi to /data/backup/db3306_full/performance_schema/status_by_thread_179.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/status_by_user_180.sdi to /data/backup/db3306_full/performance_schema/status_by_user_180.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/global_status_181.sdi to /data/backup/db3306_full/performance_schema/global_status_181.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/session_status_182.sdi to /data/backup/db3306_full/performance_schema/session_status_182.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/variables_by_thr_183.sdi to /data/backup/db3306_full/performance_schema/variables_by_thr_183.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/global_variables_184.sdi to /data/backup/db3306_full/performance_schema/global_variables_184.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/session_variable_185.sdi to /data/backup/db3306_full/performance_schema/session_variable_185.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/variables_info_186.sdi to /data/backup/db3306_full/performance_schema/variables_info_186.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/persisted_variab_187.sdi to /data/backup/db3306_full/performance_schema/persisted_variab_187.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/user_defined_fun_188.sdi to /data/backup/db3306_full/performance_schema/user_defined_fun_188.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/binary_log_trans_189.sdi to /data/backup/db3306_full/performance_schema/binary_log_trans_189.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/tls_channel_stat_190.sdi to /data/backup/db3306_full/performance_schema/tls_channel_stat_190.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/keyring_componen_191.sdi to /data/backup/db3306_full/performance_schema/keyring_componen_191.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/clone_status_413.sdi to /data/backup/db3306_full/performance_schema/clone_status_413.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 [01] Copying performance_schema/clone_progress_414.sdi to /data/backup/db3306_full/performance_schema/clone_progress_414.sdi
220115 14:58:02 [01] ...done
220115 14:58:02 Finished backing up non-InnoDB tables and files
220115 14:58:02 Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS
220115 14:58:02 Selecting LSN and binary log position from p_s.log_status
220115 14:58:02 [00] Copying /data/mysql/mysql3306/logs/mybinlog.000028 to /data/backup/db3306_full/mybinlog.000028 up to position 168484
220115 14:58:02 [00] ...done
220115 14:58:02 [00] Writing /data/backup/db3306_full/mybinlog.index
220115 14:58:02 [00] ...done
220115 14:58:02 [00] Writing /data/backup/db3306_full/xtrabackup_binlog_info
220115 14:58:02 [00] ...done
220115 14:58:02 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1556926439'
xtrabackup: Stopping log copying thread at LSN 1624355771.
xtrabackup: warning: Log block checksum mismatch (block no 0 at lsn 1624455680):
expected 0, calculated checksum 3965168067
xtrabackup: warning: this is possible when the log block has not been fully written by the server, will retry later.
220115 14:58:02 >> log scanned up to (1624455680)
220115 14:58:03 Executing UNLOCK INSTANCE
220115 14:58:03 All tables unlocked
220115 14:58:03 [00] Copying ib_buffer_pool to /data/backup/db3306_full/ib_buffer_pool
220115 14:58:03 [00] ...done
220115 14:58:03 Backup created in directory '/data/backup/db3306_full/'
MySQL binlog position: filename 'mybinlog.000028', position '168484', GTID of the last change '5c3ab878-72ce-11ec-93ce-0050569e5ca3:1-968024'
220115 14:58:03 [00] Writing /data/backup/db3306_full/backup-my.cnf
220115 14:58:03 [00] ...done
220115 14:58:03 [00] Writing /data/backup/db3306_full/xtrabackup_info
220115 14:58:03 [00] ...done
xtrabackup: Transaction log of lsn (1544060643) to (1627346469) was copied.
220115 14:58:04 completed OK!
查看备份集
[root@test28 backup]# cd db3306_full/
[root@test28 db3306_full]# ll
total 151180
-rw-r-----. 1 root root 479 Jan 15 14:58 backup-my.cnf
-rw-r-----. 1 root root 3684 Jan 15 14:58 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Jan 15 14:57 ibdata1
-rw-r-----. 1 root root 168484 Jan 15 14:58 mybinlog.000028
-rw-r-----. 1 root root 43 Jan 15 14:58 mybinlog.index
drwxr-x---. 2 root root 143 Jan 15 14:58 mysql
-rw-r-----. 1 root root 25165824 Jan 15 14:58 mysql.ibd
drwxr-x---. 2 root root 8192 Jan 15 14:58 performance_schema
drwxr-x---. 2 root root 197 Jan 15 14:57 sbtest
drwxr-x---. 2 root root 28 Jan 15 14:57 sys
-rw-r-----. 1 root root 16777216 Jan 15 14:58 undo_001
-rw-r-----. 1 root root 16777216 Jan 15 14:58 undo_002
-rw-r-----. 1 root root 69 Jan 15 14:58 xtrabackup_binlog_info
-rw-r-----. 1 root root 108 Jan 15 14:58 xtrabackup_checkpoints
-rw-r-----. 1 root root 656 Jan 15 14:58 xtrabackup_info
-rw-r-----. 1 root root 83288576 Jan 15 14:58 xtrabackup_logfile
-rw-r-----. 1 root root 39 Jan 15 14:58 xtrabackup_tablespaces
drwxr-x---. 2 root root 20 Jan 15 14:57 zhuo
[root@test28 db3306_full]# cat xtrabackup_info
uuid = 7c1e4249-75d0-11ec-bc6e-0050569e5ca3
name =
tool_name = xtrabackup
tool_command = --defaults-file=/data/mysql/mysql3306/my3306.cnf -S /tmp/mysql3306.sock -uroot -pmysql --backup --target-dir=/data/backup/db3306_full --no-server-version-check
tool_version = 8.0.26-18
ibbackup_version = 8.0.26-18
server_version = 8.0.27
start_time = 2022-01-15 14:57:52
end_time = 2022-01-15 14:58:03
lock_time = 2
binlog_pos = filename 'mybinlog.000028', position '168484', GTID of the last change '5c3ab878-72ce-11ec-93ce-0050569e5ca3:1-968024'
innodb_from_lsn = 0
innodb_to_lsn = 1556926439
partial = N
incremental = N
format = file
compressed = N
encrypted = N
[root@test28 db3306_full]# cat xtrabackup_binlog_info
mybinlog.000028 168484 5c3ab878-72ce-11ec-93ce-0050569e5ca3:1-968024
模拟drop table
主库执行：
root@localhost 15:50: [sbtest]> drop table sbtest9;
Query OK, 0 rows affected (0.10 sec)
root@localhost 15:50: [sbtest]> show master status;
+-----------------+----------+--------------+------------------+------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+------------------------------------------------+
| mybinlog.000085 | 12087079 | | | 5c3ab878-72ce-11ec-93ce-0050569e5ca3:1-3550493 |
+-----------------+----------+--------------+------------------+------------------------------------------------+
1 row in set (0.00 sec)
root@localhost 15:50: [sbtest]> flush logs;
Query OK, 0 rows affected (0.01 sec)
root@localhost 15:50: [sbtest]> show master status;
+-----------------+----------+--------------+------------------+------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+------------------------------------------------+
| mybinlog.000086 | 2501 | | | 5c3ab878-72ce-11ec-93ce-0050569e5ca3:1-3550494 |
+-----------------+----------+--------------+------------------+------------------------------------------------+
1 row in set (0.00 sec)
drop完，我们应该上线后，立即做一次切换日志。为了后面解析日志好解析。如上，drop的事务就在mybinlog.000085 里面。
mysqlbinlog解析次binlog，查找删除前的GTID。
此时我们的sysbench也就异常退出了，和因为缺表，和正常线上业务相同，可能业务此时就会报错退出了。
查找drop前的GTID
解析mybinlog.000085 日志。
[root@test28 logs]# /usr/local/mysql/bin/mysqlbinlog -v --base64-output=decode-rows mybinlog.000085 > 85.sql
vi打开文件，查找关键字DROP，如下：
即drop table对应的GTID为：
SET @@SESSION.GTID_NEXT= ‘5c3ab878-72ce-11ec-93ce-0050569e5ca3:3550485’/!/;
记住这个GTID，后面恢复要用。
下面我们根据全备搭建从库
创建必要的数据文件目录和日志文件目录和参数文件
cd /data/mysql
mkdir -p mysql3308/{data,logs,tmp}
cp mysql3306/my3306.cnf mysql3308/
cd mysql3308/
mv my3306.cnf my3308.cnf
chown -R mysql:mysql /data/mysql/mysql3308/
vi my3308.cnf
:%s/3306/3308/g
server_id = 283308
恢复数据库
1、prepare
xtrabackup --prepare --target-dir=/data/backup/db3306_full
[root@test28 db3306_full]# xtrabackup --prepare --target-dir=/data/backup/db3306_full
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=209715200 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=3306 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0
xtrabackup: recognized client arguments: --prepare=1 --target-dir=/data/backup/db3306_full
xtrabackup version 8.0.26-18 based on MySQL server 8.0.26 Linux (x86_64) (revision id: 4aecf82)
xtrabackup: cd to /data/backup/db3306_full/
xtrabackup: This target seems to be not prepared yet.
Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=93650944, start_lsn=(1544060643)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 93650944
xtrabackup: inititialize_service_handles suceeded
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 93650944
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
PUNCH HOLE support available
Uses event mutexes
GCC builtin __atomic_thread_fence() is used for memory barrier
Compressed tables use zlib 1.2.11
Number of pools: 1
Using CPU crc32 instructions
Directories to scan './'
Scanning './'
Completed space ID check of 15 files.
Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M
Completed initialization of buffer pool
page_cleaner coordinator priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
The log sequence number 307018972 in the system tablespace does not match the log sequence number 1544060643 in the ib_logfiles!
Database was not shutdown normally!
Starting crash recovery.
Starting to parse redo log at lsn = 1544060604, whereas checkpoint_lsn = 1544060643 and start_lsn = 1544060416
Doing recovery: scanned up to log sequence number 1549303296
Doing recovery: scanned up to log sequence number 1554546176
Doing recovery: scanned up to log sequence number 1559789056
Doing recovery: scanned up to log sequence number 1565031936
Doing recovery: scanned up to log sequence number 1570274816
Doing recovery: scanned up to log sequence number 1575517696
Doing recovery: scanned up to log sequence number 1580760576
Doing recovery: scanned up to log sequence number 1586003456
Applying a batch of 1180 redo log records ...
10%
20%
30%
40%
50%
60%
70%
80%
90%
100%
Apply batch completed!
Doing recovery: scanned up to log sequence number 1591246336
Doing recovery: scanned up to log sequence number 1596489216
Doing recovery: scanned up to log sequence number 1601732096
Doing recovery: scanned up to log sequence number 1606974976
Doing recovery: scanned up to log sequence number 1612217856
Doing recovery: scanned up to log sequence number 1617460736
Doing recovery: scanned up to log sequence number 1622703616
Doing recovery: scanned up to log sequence number 1624355771
Log background threads are being started...
Applying a batch of 1166 redo log records ...
10%
20%
30%
40%
50%
60%
70%
80%
90%
100%
Apply batch completed!
Using undo tablespace './undo_001'.
Using undo tablespace './undo_002'.
Opened 2 existing undo tablespaces.
GTID recovery trx_no: 2320827
Parallel initialization of rseg complete
Time taken to initialize rseg using 4 thread: 98029 ms.
Transaction 2320588 was in the XA prepared state.
Transaction 2320618 was in the XA prepared state.
Transaction 2320626 was in the XA prepared state.
Transaction 2320664 was in the XA prepared state.
Transaction 2320684 was in the XA prepared state.
Transaction 2320692 was in the XA prepared state.
Transaction 2320700 was in the XA prepared state.
Transaction 2320729 was in the XA prepared state.
Transaction 2320731 was in the XA prepared state.
Transaction 2320733 was in the XA prepared state.
Transaction 2320737 was in the XA prepared state.
Transaction 2320766 was in the XA prepared state.
Transaction 2320770 was in the XA prepared state.
Transaction 2320811 was in the XA prepared state.
Transaction 2320813 was in the XA prepared state.
Transaction 2320815 was in the XA prepared state.
Transaction 2320817 was in the XA prepared state.
Transaction 2320819 was in the XA prepared state.
Transaction 2320821 was in the XA prepared state.
Transaction 2320823 was in the XA prepared state.
Transaction 2320825 was in the XA prepared state.
Transaction 2320830 was in the XA prepared state.
Transaction 2320838 was in the XA prepared state.
Transaction 2320840 was in the XA prepared state.
Transaction 2320842 was in the XA prepared state.
Transaction 2320844 was in the XA prepared state.
Transaction 2320846 was in the XA prepared state.
Transaction 2320848 was in the XA prepared state.
Transaction 2320850 was in the XA prepared state.
Transaction 2320852 was in the XA prepared state.
Transaction 2320854 was in the XA prepared state.
Transaction 2320856 was in the XA prepared state.
Transaction 2320860 was in the XA prepared state.
Transaction 2320862 was in the XA prepared state.
Transaction 2320864 was in the XA prepared state.
Transaction 2320866 was in the XA prepared state.
Transaction 2320868 was in the XA prepared state.
Transaction 2320870 was in the XA prepared state.
Transaction 2320872 was in the XA prepared state.
Transaction 2320874 was in the XA prepared state.
Transaction 2320876 was in the XA prepared state.
Transaction 2320878 was in the XA prepared state.
Transaction 2320882 was in the XA prepared state.
Transaction 2320615 was in the XA prepared state.
Transaction 2320679 was in the XA prepared state.
Transaction 2320681 was in the XA prepared state.
Transaction 2320689 was in the XA prepared state.
Transaction 2320695 was in the XA prepared state.
Transaction 2320724 was in the XA prepared state.
Transaction 2320726 was in the XA prepared state.
Transaction 2320732 was in the XA prepared state.
Transaction 2320734 was in the XA prepared state.
Transaction 2320736 was in the XA prepared state.
Transaction 2320765 was in the XA prepared state.
Transaction 2320775 was in the XA prepared state.
Transaction 2320798 was in the XA prepared state.
Transaction 2320810 was in the XA prepared state.
Transaction 2320818 was in the XA prepared state.
Transaction 2320820 was in the XA prepared state.
Transaction 2320822 was in the XA prepared state.
Transaction 2320831 was in the XA prepared state.
Transaction 2320833 was in the XA prepared state.
Transaction 2320839 was in the XA prepared state.
Transaction 2320841 was in the XA prepared state.
Transaction 2320845 was in the XA prepared state.
Transaction 2320847 was in the XA prepared state.
Transaction 2320849 was in the XA prepared state.
Transaction 2320851 was in the XA prepared state.
Transaction 2320853 was in the XA prepared state.
Transaction 2320857 was in the XA prepared state.
Transaction 2320861 was in the XA prepared state.
Transaction 2320863 was in the XA prepared state.
Transaction 2320865 was in the XA prepared state.
Transaction 2320867 was in the XA prepared state.
Transaction 2320869 was in the XA prepared state.
Transaction 2320871 was in the XA prepared state.
Transaction 2320873 was in the XA prepared state.
Transaction 2320875 was in the XA prepared state.
Transaction 2320877 was in the XA prepared state.
94 transaction(s) which must be rolled back or cleaned up in total 28 row operations to undo
Trx id counter is 2321153
Creating shared tablespace for temporary tables
Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
File './ibtmp1' size is now 12 MB.
Scanning temp tablespace dir:'./#innodb_temp/'
Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active.
Page cleaner took 13097ms to flush 0 and evict 0 pages
8.0.26 started; log sequence number 1624355781
Allocated tablespace ID 1 for sys/sys_config, old maximum was 0
Starting in background the rollback of uncommitted transactions
Rolling back trx with id 2320883, 1 rows to undo
Rollback of trx with id 2320883 completed
Rolling back trx with id 2320881, 2 rows to undo
Rollback of trx with id 2320881 completed
Rolling back trx with id 2320880, 3 rows to undo
Rollback of trx with id 2320880 completed
Rolling back trx with id 2320879, 4 rows to undo
Rollback of trx with id 2320879 completed
Rolling back trx with id 2320859, 1 rows to undo
Rollback of trx with id 2320859 completed
Rolling back trx with id 2320858, 3 rows to undo
Rollback of trx with id 2320858 completed
Rolling back trx with id 2320855, 1 rows to undo
Rollback of trx with id 2320855 completed
Rolling back trx with id 2320843, 1 rows to undo
Rollback of trx with id 2320843 completed
Rolling back trx with id 2320837, 1 rows to undo
Rollback of trx with id 2320837 completed
Rolling back trx with id 2320816, 1 rows to undo
Rollback of trx with id 2320816 completed
Rolling back trx with id 2320768, 2 rows to undo
Rollback of trx with id 2320768 completed
Rolling back trx with id 2320685, 1 rows to undo
Rollback of trx with id 2320685 completed
Rolling back trx with id 2320623, 3 rows to undo
Rollback of trx with id 2320623 completed
Rolling back trx with id 2320619, 2 rows to undo
Rollback of trx with id 2320619 completed
Rolling back trx with id 2320483, 2 rows to undo
Rollback of trx with id 2320483 completed
Rollback of non-prepared transactions completed
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
FTS optimize thread exiting.
Starting shutdown...
Log background threads are being closed...
Shutdown completed; log sequence number 1624360905
Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 209715200
PUNCH HOLE support available
Uses event mutexes
GCC builtin __atomic_thread_fence() is used for memory barrier
Compressed tables use zlib 1.2.11
Number of pools: 1
Using CPU crc32 instructions
Directories to scan './'
Scanning './'
Completed space ID check of 15 files.
Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M
Completed initialization of buffer pool
page_cleaner coordinator priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
Creating log file ./ib_logfile101
Creating log file ./ib_logfile1
Creating log file ./ib_logfile2
page_cleaner worker priority: -20
Renaming log file ./ib_logfile101 to ./ib_logfile0
New log files created, LSN=1624360972
Starting to parse redo log at lsn = 1624360972, whereas checkpoint_lsn = 1624360972 and start_lsn = 1624360960
Log background threads are being started...
Applying a batch of 0 redo log records ...
Apply batch completed!
Using undo tablespace './undo_001'.
Using undo tablespace './undo_002'.
Opened 2 existing undo tablespaces.
GTID recovery trx_no: 2320827
Parallel initialization of rseg complete
Time taken to initialize rseg using 4 thread: 17581 ms.
Transaction 2320588 was in the XA prepared state.
Transaction 2320618 was in the XA prepared state.
Transaction 2320626 was in the XA prepared state.
Transaction 2320664 was in the XA prepared state.
Transaction 2320684 was in the XA prepared state.
Transaction 2320692 was in the XA prepared state.
Transaction 2320700 was in the XA prepared state.
Transaction 2320729 was in the XA prepared state.
Transaction 2320731 was in the XA prepared state.
Transaction 2320733 was in the XA prepared state.
Transaction 2320737 was in the XA prepared state.
Transaction 2320766 was in the XA prepared state.
Transaction 2320770 was in the XA prepared state.
Transaction 2320811 was in the XA prepared state.
Transaction 2320813 was in the XA prepared state.
Transaction 2320815 was in the XA prepared state.
Transaction 2320817 was in the XA prepared state.
Transaction 2320819 was in the XA prepared state.
Transaction 2320821 was in the XA prepared state.
Transaction 2320823 was in the XA prepared state.
Transaction 2320825 was in the XA prepared state.
Transaction 2320830 was in the XA prepared state.
Transaction 2320838 was in the XA prepared state.
Transaction 2320840 was in the XA prepared state.
Transaction 2320842 was in the XA prepared state.
Transaction 2320844 was in the XA prepared state.
Transaction 2320846 was in the XA prepared state.
Transaction 2320848 was in the XA prepared state.
Transaction 2320850 was in the XA prepared state.
Transaction 2320852 was in the XA prepared state.
Transaction 2320854 was in the XA prepared state.
Transaction 2320856 was in the XA prepared state.
Transaction 2320860 was in the XA prepared state.
Transaction 2320862 was in the XA prepared state.
Transaction 2320864 was in the XA prepared state.
Transaction 2320866 was in the XA prepared state.
Transaction 2320868 was in the XA prepared state.
Transaction 2320870 was in the XA prepared state.
Transaction 2320872 was in the XA prepared state.
Transaction 2320874 was in the XA prepared state.
Transaction 2320876 was in the XA prepared state.
Transaction 2320878 was in the XA prepared state.
Transaction 2320882 was in the XA prepared state.
Transaction 2320615 was in the XA prepared state.
Transaction 2320679 was in the XA prepared state.
Transaction 2320681 was in the XA prepared state.
Transaction 2320689 was in the XA prepared state.
Transaction 2320695 was in the XA prepared state.
Transaction 2320724 was in the XA prepared state.
Transaction 2320726 was in the XA prepared state.
Transaction 2320732 was in the XA prepared state.
Transaction 2320734 was in the XA prepared state.
Transaction 2320736 was in the XA prepared state.
Transaction 2320765 was in the XA prepared state.
Transaction 2320775 was in the XA prepared state.
Transaction 2320798 was in the XA prepared state.
Transaction 2320810 was in the XA prepared state.
Transaction 2320818 was in the XA prepared state.
Transaction 2320820 was in the XA prepared state.
Transaction 2320822 was in the XA prepared state.
Transaction 2320831 was in the XA prepared state.
Transaction 2320833 was in the XA prepared state.
Transaction 2320839 was in the XA prepared state.
Transaction 2320841 was in the XA prepared state.
Transaction 2320845 was in the XA prepared state.
Transaction 2320847 was in the XA prepared state.
Transaction 2320849 was in the XA prepared state.
Transaction 2320851 was in the XA prepared state.
Transaction 2320853 was in the XA prepared state.
Transaction 2320857 was in the XA prepared state.
Transaction 2320861 was in the XA prepared state.
Transaction 2320863 was in the XA prepared state.
Transaction 2320865 was in the XA prepared state.
Transaction 2320867 was in the XA prepared state.
Transaction 2320869 was in the XA prepared state.
Transaction 2320871 was in the XA prepared state.
Transaction 2320873 was in the XA prepared state.
Transaction 2320875 was in the XA prepared state.
Transaction 2320877 was in the XA prepared state.
79 transaction(s) which must be rolled back or cleaned up in total 0 row operations to undo
Trx id counter is 2321665
Removed temporary tablespace data file: "ibtmp1"
Creating shared tablespace for temporary tables
Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
File './ibtmp1' size is now 12 MB.
Scanning temp tablespace dir:'./#innodb_temp/'
Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active.
8.0.26 started; log sequence number 1624360982
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
FTS optimize thread exiting.
Trying to access missing tablespace 4294967294
Starting shutdown...
Log background threads are being closed...
Shutdown completed; log sequence number 1624360982
220115 15:02:19 completed OK!
2、copy-back
xtrabackup --defaults-file=/data/mysql/mysql3308/my3308.cnf --copy-back --target-dir=/data/backup/db3306_full
[root@test28 mysql3308]# xtrabackup --defaults-file=/data/mysql/mysql3308/my3308.cnf --copy-back --target-dir=/data/backup/db3306_full
xtrabackup: recognized server arguments: --server-id=283308 --datadir=/data/mysql/mysql3308/data --open_files_limit=65535 --log_bin=/data/mysql/mysql3308/logs/mybinlog --innodb_buffer_pool_size=200M --innodb_data_file_path=ibdata1:12M:autoextend --innodb_flush_log_at_trx_commit=1 --innodb_log_buffer_size=32M --innodb_log_file_size=200M --innodb_log_files_in_group=3 --innodb_io_capacity=4000 --innodb_open_files=65535 --innodb_flush_method=O_DIRECT --innodb_adaptive_hash_index=0
xtrabackup: recognized client arguments: --port=3308 --socket=/tmp/mysql3308.sock --copy-back=1 --target-dir=/data/backup/db3306_full
xtrabackup version 8.0.26-18 based on MySQL server 8.0.26 Linux (x86_64) (revision id: 4aecf82)
220115 15:03:55 [01] Copying undo_001 to /data/mysql/mysql3308/data/undo_001
220115 15:03:55 [01] ...done
220115 15:03:55 [01] Copying undo_002 to /data/mysql/mysql3308/data/undo_002
220115 15:03:55 [01] ...done
220115 15:03:55 [01] Copying ib_logfile0 to /data/mysql/mysql3308/data/ib_logfile0
220115 15:03:57 [01] ...done
220115 15:03:57 [01] Copying ib_logfile1 to /data/mysql/mysql3308/data/ib_logfile1
220115 15:03:58 [01] ...done
220115 15:03:58 [01] Copying ib_logfile2 to /data/mysql/mysql3308/data/ib_logfile2
220115 15:03:59 [01] ...done
220115 15:03:59 [01] Copying ibdata1 to /data/mysql/mysql3308/data/ibdata1
220115 15:03:59 [01] ...done
220115 15:03:59 [01] Copying mybinlog.000028 to /data/mysql/mysql3308/logs/mybinlog.000028
220115 15:03:59 [01] ...done
220115 15:03:59 [01] Copying mybinlog.index to /data/mysql/mysql3308/logs/mybinlog.index
220115 15:03:59 [01] ...done
220115 15:03:59 [01] Copying ./sys/sys_config.ibd to /data/mysql/mysql3308/data/sys/sys_config.ibd
220115 15:03:59 [01] ...done
220115 15:03:59 [01] Copying ./zhuo/t1.ibd to /data/mysql/mysql3308/data/zhuo/t1.ibd
220115 15:03:59 [01] ...done
220115 15:03:59 [01] Copying ./sbtest/sbtest4.ibd to /data/mysql/mysql3308/data/sbtest/sbtest4.ibd
220115 15:03:59 [01] ...done
220115 15:03:59 [01] Copying ./sbtest/sbtest3.ibd to /data/mysql/mysql3308/data/sbtest/sbtest3.ibd
220115 15:03:59 [01] ...done
220115 15:03:59 [01] Copying ./sbtest/sbtest8.ibd to /data/mysql/mysql3308/data/sbtest/sbtest8.ibd
220115 15:03:59 [01] ...done
220115 15:03:59 [01] Copying ./sbtest/sbtest10.ibd to /data/mysql/mysql3308/data/sbtest/sbtest10.ibd
220115 15:03:59 [01] ...done
220115 15:03:59 [01] Copying ./sbtest/sbtest9.ibd to /data/mysql/mysql3308/data/sbtest/sbtest9.ibd
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./sbtest/sbtest5.ibd to /data/mysql/mysql3308/data/sbtest/sbtest5.ibd
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./sbtest/sbtest2.ibd to /data/mysql/mysql3308/data/sbtest/sbtest2.ibd
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./sbtest/sbtest7.ibd to /data/mysql/mysql3308/data/sbtest/sbtest7.ibd
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./sbtest/sbtest6.ibd to /data/mysql/mysql3308/data/sbtest/sbtest6.ibd
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./sbtest/sbtest1.ibd to /data/mysql/mysql3308/data/sbtest/sbtest1.ibd
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./mysql.ibd to /data/mysql/mysql3308/data/mysql.ibd
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./mysql/general_log_213.sdi to /data/mysql/mysql3308/data/mysql/general_log_213.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./mysql/general_log.CSM to /data/mysql/mysql3308/data/mysql/general_log.CSM
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./mysql/general_log.CSV to /data/mysql/mysql3308/data/mysql/general_log.CSV
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./mysql/slow_log_214.sdi to /data/mysql/mysql3308/data/mysql/slow_log_214.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./mysql/slow_log.CSM to /data/mysql/mysql3308/data/mysql/slow_log.CSM
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./mysql/slow_log.CSV to /data/mysql/mysql3308/data/mysql/slow_log.CSV
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/cond_instances_82.sdi to /data/mysql/mysql3308/data/performance_schema/cond_instances_82.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/error_log_83.sdi to /data/mysql/mysql3308/data/performance_schema/error_log_83.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_waits_cur_84.sdi to /data/mysql/mysql3308/data/performance_schema/events_waits_cur_84.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_waits_his_85.sdi to /data/mysql/mysql3308/data/performance_schema/events_waits_his_85.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_waits_his_86.sdi to /data/mysql/mysql3308/data/performance_schema/events_waits_his_86.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_waits_sum_87.sdi to /data/mysql/mysql3308/data/performance_schema/events_waits_sum_87.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_waits_sum_88.sdi to /data/mysql/mysql3308/data/performance_schema/events_waits_sum_88.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_waits_sum_89.sdi to /data/mysql/mysql3308/data/performance_schema/events_waits_sum_89.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_waits_sum_90.sdi to /data/mysql/mysql3308/data/performance_schema/events_waits_sum_90.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_waits_sum_91.sdi to /data/mysql/mysql3308/data/performance_schema/events_waits_sum_91.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_waits_sum_92.sdi to /data/mysql/mysql3308/data/performance_schema/events_waits_sum_92.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/file_instances_93.sdi to /data/mysql/mysql3308/data/performance_schema/file_instances_93.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/file_summary_by__94.sdi to /data/mysql/mysql3308/data/performance_schema/file_summary_by__94.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/file_summary_by__95.sdi to /data/mysql/mysql3308/data/performance_schema/file_summary_by__95.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/host_cache_96.sdi to /data/mysql/mysql3308/data/performance_schema/host_cache_96.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/mutex_instances_97.sdi to /data/mysql/mysql3308/data/performance_schema/mutex_instances_97.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/objects_summary__98.sdi to /data/mysql/mysql3308/data/performance_schema/objects_summary__98.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/performance_time_99.sdi to /data/mysql/mysql3308/data/performance_schema/performance_time_99.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/processlist_100.sdi to /data/mysql/mysql3308/data/performance_schema/processlist_100.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/rwlock_instances_101.sdi to /data/mysql/mysql3308/data/performance_schema/rwlock_instances_101.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/setup_actors_102.sdi to /data/mysql/mysql3308/data/performance_schema/setup_actors_102.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/setup_consumers_103.sdi to /data/mysql/mysql3308/data/performance_schema/setup_consumers_103.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/setup_instrument_104.sdi to /data/mysql/mysql3308/data/performance_schema/setup_instrument_104.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/setup_objects_105.sdi to /data/mysql/mysql3308/data/performance_schema/setup_objects_105.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/setup_threads_106.sdi to /data/mysql/mysql3308/data/performance_schema/setup_threads_106.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/table_io_waits_s_107.sdi to /data/mysql/mysql3308/data/performance_schema/table_io_waits_s_107.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/table_io_waits_s_108.sdi to /data/mysql/mysql3308/data/performance_schema/table_io_waits_s_108.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/table_lock_waits_109.sdi to /data/mysql/mysql3308/data/performance_schema/table_lock_waits_109.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/threads_110.sdi to /data/mysql/mysql3308/data/performance_schema/threads_110.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_stages_cu_111.sdi to /data/mysql/mysql3308/data/performance_schema/events_stages_cu_111.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_stages_hi_112.sdi to /data/mysql/mysql3308/data/performance_schema/events_stages_hi_112.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_stages_hi_113.sdi to /data/mysql/mysql3308/data/performance_schema/events_stages_hi_113.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_stages_su_114.sdi to /data/mysql/mysql3308/data/performance_schema/events_stages_su_114.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_stages_su_115.sdi to /data/mysql/mysql3308/data/performance_schema/events_stages_su_115.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_stages_su_116.sdi to /data/mysql/mysql3308/data/performance_schema/events_stages_su_116.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_stages_su_117.sdi to /data/mysql/mysql3308/data/performance_schema/events_stages_su_117.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_stages_su_118.sdi to /data/mysql/mysql3308/data/performance_schema/events_stages_su_118.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_statement_119.sdi to /data/mysql/mysql3308/data/performance_schema/events_statement_119.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_statement_120.sdi to /data/mysql/mysql3308/data/performance_schema/events_statement_120.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_statement_121.sdi to /data/mysql/mysql3308/data/performance_schema/events_statement_121.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_statement_122.sdi to /data/mysql/mysql3308/data/performance_schema/events_statement_122.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_statement_123.sdi to /data/mysql/mysql3308/data/performance_schema/events_statement_123.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_statement_124.sdi to /data/mysql/mysql3308/data/performance_schema/events_statement_124.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_statement_125.sdi to /data/mysql/mysql3308/data/performance_schema/events_statement_125.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_statement_126.sdi to /data/mysql/mysql3308/data/performance_schema/events_statement_126.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_statement_127.sdi to /data/mysql/mysql3308/data/performance_schema/events_statement_127.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_statement_128.sdi to /data/mysql/mysql3308/data/performance_schema/events_statement_128.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_statement_129.sdi to /data/mysql/mysql3308/data/performance_schema/events_statement_129.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_statement_130.sdi to /data/mysql/mysql3308/data/performance_schema/events_statement_130.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_transacti_131.sdi to /data/mysql/mysql3308/data/performance_schema/events_transacti_131.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_transacti_132.sdi to /data/mysql/mysql3308/data/performance_schema/events_transacti_132.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_transacti_133.sdi to /data/mysql/mysql3308/data/performance_schema/events_transacti_133.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_transacti_134.sdi to /data/mysql/mysql3308/data/performance_schema/events_transacti_134.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_transacti_135.sdi to /data/mysql/mysql3308/data/performance_schema/events_transacti_135.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_transacti_136.sdi to /data/mysql/mysql3308/data/performance_schema/events_transacti_136.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_transacti_137.sdi to /data/mysql/mysql3308/data/performance_schema/events_transacti_137.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_transacti_138.sdi to /data/mysql/mysql3308/data/performance_schema/events_transacti_138.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_errors_su_139.sdi to /data/mysql/mysql3308/data/performance_schema/events_errors_su_139.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_errors_su_140.sdi to /data/mysql/mysql3308/data/performance_schema/events_errors_su_140.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_errors_su_141.sdi to /data/mysql/mysql3308/data/performance_schema/events_errors_su_141.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_errors_su_142.sdi to /data/mysql/mysql3308/data/performance_schema/events_errors_su_142.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/events_errors_su_143.sdi to /data/mysql/mysql3308/data/performance_schema/events_errors_su_143.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/users_144.sdi to /data/mysql/mysql3308/data/performance_schema/users_144.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/accounts_145.sdi to /data/mysql/mysql3308/data/performance_schema/accounts_145.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/hosts_146.sdi to /data/mysql/mysql3308/data/performance_schema/hosts_146.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/socket_instances_147.sdi to /data/mysql/mysql3308/data/performance_schema/socket_instances_147.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/socket_summary_b_148.sdi to /data/mysql/mysql3308/data/performance_schema/socket_summary_b_148.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/socket_summary_b_149.sdi to /data/mysql/mysql3308/data/performance_schema/socket_summary_b_149.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/session_connect__150.sdi to /data/mysql/mysql3308/data/performance_schema/session_connect__150.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/session_account__151.sdi to /data/mysql/mysql3308/data/performance_schema/session_account__151.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/keyring_keys_152.sdi to /data/mysql/mysql3308/data/performance_schema/keyring_keys_152.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/memory_summary_g_153.sdi to /data/mysql/mysql3308/data/performance_schema/memory_summary_g_153.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/memory_summary_b_154.sdi to /data/mysql/mysql3308/data/performance_schema/memory_summary_b_154.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/memory_summary_b_155.sdi to /data/mysql/mysql3308/data/performance_schema/memory_summary_b_155.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/memory_summary_b_156.sdi to /data/mysql/mysql3308/data/performance_schema/memory_summary_b_156.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/memory_summary_b_157.sdi to /data/mysql/mysql3308/data/performance_schema/memory_summary_b_157.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/table_handles_158.sdi to /data/mysql/mysql3308/data/performance_schema/table_handles_158.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/metadata_locks_159.sdi to /data/mysql/mysql3308/data/performance_schema/metadata_locks_159.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/data_locks_160.sdi to /data/mysql/mysql3308/data/performance_schema/data_locks_160.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/data_lock_waits_161.sdi to /data/mysql/mysql3308/data/performance_schema/data_lock_waits_161.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/replication_conn_162.sdi to /data/mysql/mysql3308/data/performance_schema/replication_conn_162.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/replication_grou_163.sdi to /data/mysql/mysql3308/data/performance_schema/replication_grou_163.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/replication_conn_164.sdi to /data/mysql/mysql3308/data/performance_schema/replication_conn_164.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/replication_appl_165.sdi to /data/mysql/mysql3308/data/performance_schema/replication_appl_165.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/replication_appl_166.sdi to /data/mysql/mysql3308/data/performance_schema/replication_appl_166.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/replication_appl_167.sdi to /data/mysql/mysql3308/data/performance_schema/replication_appl_167.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/replication_appl_168.sdi to /data/mysql/mysql3308/data/performance_schema/replication_appl_168.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/replication_grou_169.sdi to /data/mysql/mysql3308/data/performance_schema/replication_grou_169.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/replication_appl_170.sdi to /data/mysql/mysql3308/data/performance_schema/replication_appl_170.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/replication_appl_171.sdi to /data/mysql/mysql3308/data/performance_schema/replication_appl_171.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/replication_asyn_172.sdi to /data/mysql/mysql3308/data/performance_schema/replication_asyn_172.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/replication_asyn_173.sdi to /data/mysql/mysql3308/data/performance_schema/replication_asyn_173.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/log_status_174.sdi to /data/mysql/mysql3308/data/performance_schema/log_status_174.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/prepared_stateme_175.sdi to /data/mysql/mysql3308/data/performance_schema/prepared_stateme_175.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/user_variables_b_176.sdi to /data/mysql/mysql3308/data/performance_schema/user_variables_b_176.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/status_by_accoun_177.sdi to /data/mysql/mysql3308/data/performance_schema/status_by_accoun_177.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/status_by_host_178.sdi to /data/mysql/mysql3308/data/performance_schema/status_by_host_178.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/status_by_thread_179.sdi to /data/mysql/mysql3308/data/performance_schema/status_by_thread_179.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/status_by_user_180.sdi to /data/mysql/mysql3308/data/performance_schema/status_by_user_180.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/global_status_181.sdi to /data/mysql/mysql3308/data/performance_schema/global_status_181.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/session_status_182.sdi to /data/mysql/mysql3308/data/performance_schema/session_status_182.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/variables_by_thr_183.sdi to /data/mysql/mysql3308/data/performance_schema/variables_by_thr_183.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/global_variables_184.sdi to /data/mysql/mysql3308/data/performance_schema/global_variables_184.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/session_variable_185.sdi to /data/mysql/mysql3308/data/performance_schema/session_variable_185.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/variables_info_186.sdi to /data/mysql/mysql3308/data/performance_schema/variables_info_186.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/persisted_variab_187.sdi to /data/mysql/mysql3308/data/performance_schema/persisted_variab_187.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/user_defined_fun_188.sdi to /data/mysql/mysql3308/data/performance_schema/user_defined_fun_188.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/binary_log_trans_189.sdi to /data/mysql/mysql3308/data/performance_schema/binary_log_trans_189.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/tls_channel_stat_190.sdi to /data/mysql/mysql3308/data/performance_schema/tls_channel_stat_190.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/keyring_componen_191.sdi to /data/mysql/mysql3308/data/performance_schema/keyring_componen_191.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/clone_status_413.sdi to /data/mysql/mysql3308/data/performance_schema/clone_status_413.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./performance_schema/clone_progress_414.sdi to /data/mysql/mysql3308/data/performance_schema/clone_progress_414.sdi
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./ib_buffer_pool to /data/mysql/mysql3308/data/ib_buffer_pool
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./xtrabackup_info to /data/mysql/mysql3308/data/xtrabackup_info
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./xtrabackup_master_key_id to /data/mysql/mysql3308/data/xtrabackup_master_key_id
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Copying ./ibtmp1 to /data/mysql/mysql3308/data/ibtmp1
220115 15:04:00 [01] ...done
220115 15:04:00 [01] Creating directory ./#innodb_temp
220115 15:04:00 [01] ...done.
220115 15:04:00 completed OK!
修改权限，启动3308实例
[root@test28 logs]# chown -R mysql:mysql /data/mysql/mysql3308/
[root@test28 logs]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql3308/my3308.cnf &
[1] 27883
error.log
2022-01-15T15:15:48.894684+08:00 0 [Note] [MY-011243] [Server] Plugin mysqlx reported: 'Using OpenSSL for TLS connections'
2022-01-15T15:15:48.894827+08:00 0 [System] [MY-010931] [Server] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.0.27' socket: '/tmp/mysql3308.sock' port: 3308 MySQL Community Server - GPL.
2022-01-15T15:15:48.894848+08:00 0 [System] [MY-013292] [Server] Admin interface ready for connections, address: '127.0.0.1' port: 33082
ready for connections
配置为从库
root@localhost 16:24: [(none)]> change master to master_host=‘192.168.33.28’,master_port=3306,master_user=‘repl’,master_password=‘repl’,master_auto_position=1,master_ssl=1;
Query OK, 0 rows affected, 9 warnings (0.02 sec)
root@localhost 16:24: [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.33.28
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: test28-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No
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: 0
Relay_Log_Space: 156
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
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: 0
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 5c3ab878-72ce-11ec-93ce-0050569e5ca3:1-3135461
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.01 sec)
root@localhost 16:24: [(none)]> start slave io_thread
-> ;
Query OK, 0 rows affected, 1 warning (0.00 sec)
root@localhost 16:24: [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Queueing source event to the relay log
Master_Host: 192.168.33.28
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mybinlog.000077
Read_Master_Log_Pos: 6485141
Relay_Log_File: test28-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: Yes
Slave_SQL_Running: No
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: 0
Relay_Log_Space: 111521512
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
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: 3306
Master_UUID: 5c3ab878-72ce-11ec-93ce-0050569e5ca3
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 5c3ab878-72ce-11ec-93ce-0050569e5ca3:3135462-3183794
Executed_Gtid_Set: 5c3ab878-72ce-11ec-93ce-0050569e5ca3:1-3135461
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.01 sec)
root@localhost 16:25: [(none)]> CHANGE REPLICATION FILTER REPLICATE_DO_TABLE=(sbtest.sbtest9);
Query OK, 0 rows affected (0.01 sec)
root@localhost 16:25: [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.33.28
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mybinlog.000086
Read_Master_Log_Pos: 2501
Relay_Log_File: test28-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table: sbtest.sbtest9
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 956772074
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
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: 3306
Master_UUID: 5c3ab878-72ce-11ec-93ce-0050569e5ca3
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 5c3ab878-72ce-11ec-93ce-0050569e5ca3:3135462-3550494
Executed_Gtid_Set: 5c3ab878-72ce-11ec-93ce-0050569e5ca3:1-3135461
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.01 sec)
root@localhost 16:25: [(none)]> start slave sql_thread until SQL_BEFORE_GTIDS=‘5c3ab878-72ce-11ec-93ce-0050569e5ca3:3550485’
-> ;
Query OK, 0 rows affected, 1 warning (0.02 sec)
root@localhost 16:27: [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.33.28
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mybinlog.000086
Read_Master_Log_Pos: 2501
Relay_Log_File: test28-relay-bin.000002
Relay_Log_Pos: 13147298
Relay_Master_Log_File: mybinlog.000076
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table: sbtest.sbtest9
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 13147125
Relay_Log_Space: 956772074
Until_Condition: SQL_BEFORE_GTIDS
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 2442
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: 3306
Master_UUID: 5c3ab878-72ce-11ec-93ce-0050569e5ca3
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Waiting for replica workers to process their queues
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 5c3ab878-72ce-11ec-93ce-0050569e5ca3:3135462-3550494
Executed_Gtid_Set: 5c3ab878-72ce-11ec-93ce-0050569e5ca3:1-3141331
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)
root@localhost 16:27: [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.33.28
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mybinlog.000086
Read_Master_Log_Pos: 2501
Relay_Log_File: test28-relay-bin.000002
Relay_Log_Pos: 60020155
Relay_Master_Log_File: mybinlog.000076
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table: sbtest.sbtest9
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 60019982
Relay_Log_Space: 956772074
Until_Condition: SQL_BEFORE_GTIDS
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 2438
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: 3306
Master_UUID: 5c3ab878-72ce-11ec-93ce-0050569e5ca3
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Waiting for replica workers to process their queues
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 5c3ab878-72ce-11ec-93ce-0050569e5ca3:3135462-3550494
Executed_Gtid_Set: 5c3ab878-72ce-11ec-93ce-0050569e5ca3:1-3161971
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)
上面用到了2个知识点：
1、复制过滤
2、让sql_thread应用到某一个位置。
最终结果：
root@localhost 16:30: [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.33.28
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mybinlog.000086
Read_Master_Log_Pos: 2501
Relay_Log_File: test28-relay-bin.000029
Relay_Log_Pos: 12068634
Relay_Master_Log_File: mybinlog.000085
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table: sbtest.sbtest9
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 12068421
Relay_Log_Space: 12090652
Until_Condition: SQL_BEFORE_GTIDS
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
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: 3306
Master_UUID: 5c3ab878-72ce-11ec-93ce-0050569e5ca3
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 5c3ab878-72ce-11ec-93ce-0050569e5ca3:3135462-3550494
Executed_Gtid_Set: 5c3ab878-72ce-11ec-93ce-0050569e5ca3:1-3550484
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)
因为我们开启了并行复制，也有复制过滤，所以应用会很快。
Syntax:
START {SLAVE | REPLICA} [thread_types] [until_option] [connection_options] [channel_option]
thread_types:
[thread_type [, thread_type] ... ]
thread_type:
IO_THREAD | SQL_THREAD
until_option:
UNTIL { {SQL_BEFORE_GTIDS | SQL_AFTER_GTIDS} = gtid_set
| MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos
| SOURCE_LOG_FILE = 'log_name', SOURCE_LOG_POS = log_pos
| RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos
| SQL_AFTER_MTS_GAPS }
connection_options:
[USER='user_name'] [PASSWORD='user_pass'] [DEFAULT_AUTH='plugin_name'] [PLUGIN_DIR='plugin_dir']
root@localhost 16:31: [(none)]> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sbtest |
| sys |
| zhuo |
±-------------------+
6 rows in set (0.01 sec)
root@localhost 16:31: [(none)]> use sbtest;
Database changed
root@localhost 16:31: [sbtest]> show tables;
±-----------------+
| Tables_in_sbtest |
±-----------------+
| sbtest1 |
| sbtest10 |
| sbtest2 |
| sbtest3 |
| sbtest4 |
| sbtest5 |
| sbtest6 |
| sbtest7 |
| sbtest8 |
| sbtest9 |
±-----------------+
10 rows in set (0.01 sec)
root@localhost 16:32: [sbtest]> select count() from sbtest9;
±---------+
| count() |
±---------+
| 1000 |
±---------+
1 row in set (0.00 sec)
表已经回来了，恢复到了删除前的状态。
恢复表
恢复表有两种方，表空间传输或者mysqldump。在master端和所有的slave端恢复下。
mysqldump恢复：
3308实例备份：
[root@test28 backup]# /usr/local/mysql/bin/mysqldump --single-transaction -S /tmp/mysql3308.sock -uroot -pmysql sbtest sbtest9 > /data/backup/sbtest91.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[root@test28 backup]# /usr/local/mysql/bin/mysqldump --single-transaction -S /tmp/mysql3308.sock -uroot -pmysql --set-gtid-purged=OFF sbtest sbtest9 > /data/backup/sbtest92.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
一个包含GTID的信息，一个不包含GTID的信息。
3306实例导入：
[root@test28 backup]# /usr/local/mysql/bin/mysql -S /tmp/mysql3306.sock -uroot -pmysql sbtest < /data/backup/sbtest91.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3546 (HY000) at line 24: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED
[root@test28 backup]# /usr/local/mysql/bin/mysql -S /tmp/mysql3306.sock -uroot -pmysql sbtest < /data/backup/sbtest92.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
包含GTID的导入会报错。原因未知。
数据已恢复：
root@localhost 17:43: [sbtest]> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| sbtest1 |
| sbtest10 |
| sbtest2 |
| sbtest3 |
| sbtest4 |
| sbtest5 |
| sbtest6 |
| sbtest7 |
| sbtest8 |
| sbtest9 |
+------------------+
10 rows in set (0.00 sec)
root@localhost 17:45: [sbtest]> select count(*) from sbtest9;
+----------+
| count(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.00 sec)
那么导出导入过程如下：
# /usr/local/mysql/bin/mysqldump --single-transaction -S /tmp/mysql3308.sock -uroot -pmysql --set-gtid-purged=OFF sbtest sbtest9 > /data/backup/sbtest92.sql
# /usr/local/mysql/bin/mysql -S /tmp/mysql3306.sock -uroot -pmysql sbtest < /data/backup/sbtest92.sql
表空间传输：
1、3308 实例查询sbtest9的建表语句
root@localhost 16:40: [sbtest]> show create table sbtest9\G
*************************** 1. row ***************************
Table: sbtest9
Create Table: CREATE TABLE sbtest9 (
id int NOT NULL AUTO_INCREMENT,
k int NOT NULL DEFAULT ‘0’,
c char(120) NOT NULL DEFAULT ‘’,
pad char(60) NOT NULL DEFAULT ‘’,
PRIMARY KEY (id),
KEY k_9 (k)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
2、3306 实例建表：
root@localhost 16:41: [sbtest]> CREATE TABLE sbtest9 (
-> id int NOT NULL AUTO_INCREMENT,
-> k int NOT NULL DEFAULT ‘0’,
-> c char(120) NOT NULL DEFAULT ‘’,
-> pad char(60) NOT NULL DEFAULT ‘’,
-> PRIMARY KEY (id),
-> KEY k_9 (k)
-> ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.03 sec)
3、3308实例对标添加读锁
root@localhost 16:41: [sbtest]> flush tables sbtest9 for export;
Query OK, 0 rows affected (0.00 sec)
[root@test28 sbtest]# ll
total 3716
-rw-r-----. 1 mysql mysql 376832 Jan 15 16:24 sbtest10.ibd
-rw-r-----. 1 mysql mysql 376832 Jan 15 16:24 sbtest1.ibd
-rw-r-----. 1 mysql mysql 376832 Jan 15 16:24 sbtest2.ibd
-rw-r-----. 1 mysql mysql 376832 Jan 15 16:24 sbtest3.ibd
-rw-r-----. 1 mysql mysql 376832 Jan 15 16:24 sbtest4.ibd
-rw-r-----. 1 mysql mysql 376832 Jan 15 16:24 sbtest5.ibd
-rw-r-----. 1 mysql mysql 376832 Jan 15 16:24 sbtest6.ibd
-rw-r-----. 1 mysql mysql 376832 Jan 15 16:24 sbtest7.ibd
-rw-r-----. 1 mysql mysql 376832 Jan 15 16:24 sbtest8.ibd
-rw-r-----. 1 mysql mysql 844 Jan 15 16:42 sbtest9.cfg
-rw-r-----. 1 mysql mysql 409600 Jan 15 16:29 sbtest9.ibd
此时会产生一个cfg文件。
4、3306实例discard表空间
root@localhost 16:41: [sbtest]> alter table sbtest.sbtest9 discard tablespace;
Query OK, 0 rows affected (0.02 sec)
[root@test28 sbtest]# ll
total 3540
-rw-r-----. 1 mysql mysql 409600 Jan 15 15:51 sbtest10.ibd
-rw-r-----. 1 mysql mysql 376832 Jan 15 15:51 sbtest1.ibd
-rw-r-----. 1 mysql mysql 376832 Jan 15 15:51 sbtest2.ibd
-rw-r-----. 1 mysql mysql 409600 Jan 15 15:51 sbtest3.ibd
-rw-r-----. 1 mysql mysql 376832 Jan 15 15:51 sbtest4.ibd
-rw-r-----. 1 mysql mysql 376832 Jan 15 15:51 sbtest5.ibd
-rw-r-----. 1 mysql mysql 376832 Jan 15 15:51 sbtest6.ibd
-rw-r-----. 1 mysql mysql 409600 Jan 15 15:51 sbtest7.ibd
-rw-r-----. 1 mysql mysql 376832 Jan 15 15:51 sbtest8.ibd
-rw-r-----. 1 root root 844 Jan 15 16:44 sbtest9.cfg
-rw-r-----. 1 mysql mysql 131072 Jan 15 16:41 sbtest9.ibd
[root@test28 sbtest]# ll
total 3408
-rw-r-----. 1 mysql mysql 409600 Jan 15 15:51 sbtest10.ibd
-rw-r-----. 1 mysql mysql 376832 Jan 15 15:51 sbtest1.ibd
-rw-r-----. 1 mysql mysql 376832 Jan 15 15:51 sbtest2.ibd
-rw-r-----. 1 mysql mysql 409600 Jan 15 15:51 sbtest3.ibd
-rw-r-----. 1 mysql mysql 376832 Jan 15 15:51 sbtest4.ibd
-rw-r-----. 1 mysql mysql 376832 Jan 15 15:51 sbtest5.ibd
-rw-r-----. 1 mysql mysql 376832 Jan 15 15:51 sbtest6.ibd
-rw-r-----. 1 mysql mysql 409600 Jan 15 15:51 sbtest7.ibd
-rw-r-----. 1 mysql mysql 376832 Jan 15 15:51 sbtest8.ibd
sbtest9对应的表空间已经没了。
5、拷贝ibd和cfg文件到目标端数据文件位置
[root@test28 sbtest]# cp sbtest9.* /data/mysql/mysql3306/data/sbtest/
6、3308实例释放锁
root@localhost 16:49: [sbtest]> unlock tables;
Query OK, 0 rows affected (0.00 sec)
7、修改3306实例的数据文件权限。
[root@test28 sbtest]# chown mysql:mysql sbtest9.*
8、导入3306实例
root@localhost 16:46: [sbtest]> alter table sbtest.sbtest9 import tablespace;
Query OK, 0 rows affected (0.04 sec)
9、验证
3306实例
root@localhost 16:50: [sbtest]> select count() from sbtest.sbtest9;
±---------+
| count() |
±---------+
| 1000 |
±---------+
1 row in set (0.00 sec)
不推荐使用，因为此刻，主从复制关系已经破坏了。最好使用mysqldump，就不会破坏主从复制关系。
表空间传输在复制结构中，不推荐使用，因为他要在主从当中都操作，他不会把它复制到从库。
而mysqldump只用在主库操作，从胡就会复制过来。
从库表空间传输，会报错：
root@localhost 16:58: [sbtest]> alter table sbtest.sbtest9 discard tablespace;
Query OK, 0 rows affected (0.01 sec)
root@localhost 17:00: [sbtest]> alter table sbtest.sbtest9 import tablespace;
ERROR 1815 (HY000): Internal error: Cannot reset LSNs in table sbtest.sbtest9 : Data structure corruption
后面在研究。
总结
1、主要就是拿全备来恢复出一个从库。
2、关键要找出drop table之前的GTID。所以上线后第一条命令是切换日志。
3、对于日志量特别大的，我们在构建主从复制关系的时候，可以用复制过滤，只复制这一张表。
4、sql_thread不是全部应用，而是应用到drop table之前的那个GTID。
主要技术点：
change master to master_host='192.168.33.28',master_port=3306,master_user='repl',master_password='repl',master_auto_position=1,master_ssl=1;
start slave io_thread;
CHANGE REPLICATION FILTER REPLICATE_DO_TABLE=(sbtest.sbtest9);
start slave sql_thread until SQL_BEFORE_GTIDS='5c3ab878-72ce-11ec-93ce-0050569e5ca3:3550485'
以上4条命令。
2021
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
