There may be many recovery methods for MySQL drop table. The following one is based on full backup, replication, and then sql_thread stops at a specified location to recover. Let's simulate recovery
environmental information
MySQL version information: 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)
Scenario Description:
1. Use sysbench to create 10 tables with 1000 rows of records in each table
2. Use xtrabackup to make a full backup during pressure measurement
3,drop tables sbtest9
Requirements:
Restore sbtest9
Train of thought combing
The main steps are as follows:
1. After sysbench prepare, run for pressure measurement;
2. Make a full backup (simulate the normal operation of daily business, and make a hot backup every day or every week)
3. drop table (someone deleted a table during operation)
4,flush logs； Find the GTID corresponding to the Drop
5. Take the full backup and restore a start slave IO from the library_ thread；
6,start slave until sql_before_gtids=
7. dump or table space transfer method to restore the master and slave.
The restored architecture is as follows: 3308 instance is the newly created slave library, 3306 instance is the master library, and 3310 instance is the slave library that was copied normally.
Recovery process
sysbench data, pressure measurement
First create a library
root@localhost 11:23: [(none)]> create database sbtest; Query OK, 1 row affected (0.00 sec)
Then use lua script to create numbers:
/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'...
Then run for pressure measurement and run out of pressure
/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 . . . . . . .
View connections:
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 full backup database
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!
Viewing backup sets
[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
Simulate drop table
Main library execution:
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)
After the drop, we should switch logs immediately after we go online. In order to parse the log later. As mentioned above, the drop transaction is in mybinlog 000085 inside.
mysqlbinlog parses the binlog twice to find the GTID before deletion.
At this time, our sysbench exits abnormally. Due to the lack of tables, it is the same as the normal online business. Maybe the business will exit with an error.
Find GTID before drop
Parse mybinlog 000085 log.
[root@test28 logs]# /usr/local/mysql/bin/mysqlbinlog -v --base64-output=decode-rows mybinlog.000085 > 85.sql
vi open the file and find the keyword DROP as follows:
That is, the GTID corresponding to drop table is:
SET @@SESSION.GTID_NEXT= '5c3ab878-72ce-11ec-93ce-0050569e5ca3:3550485'/!/;
Remember this GTID, which will be used for later recovery.
Next, we will build the slave library according to the full set
Create the necessary data file directories and log file directories and parameter files
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
Restore database
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!
Modify permissions and start 3308 instances
[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
Configure as slave Library
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)
Two knowledge points are used above:
1. Copy filtering
2. Let sql_thread is applied to a location.
Final result:
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)
Because we have enabled parallel replication and replication filtering, the application will be fast.
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)
The table has returned to its state before deletion.
Recovery table
There are two ways to recover tables, tablespace transfer or mysqldump. Restore on the master side and all slave sides.
mysqldump recovery:
3308 instance backup:
[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.
One contains GTID information and the other does not contain GTID information.
3306 instance import:
[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.
The imported GTID will contain an error. The reason is unknown.
Data recovered:
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)
The export and import process is as follows:
# /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
Tablespace transfer:
1. Table creation statement of 3308 instance query 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 example table:
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 add read lock for instance benchmarking
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
A cfg file is generated.
4. 3306 instance discard tablespace
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
The table space corresponding to sbtest9 is gone.
5. Copy ibd and cfg files to the target data file location
[root@test28 sbtest]# cp sbtest9.* /data/mysql/mysql3306/data/sbtest/
6. 3308 instance release lock
root@localhost 16:49: [sbtest]> unlock tables;
Query OK, 0 rows affected (0.00 sec)
7. Modify the data file permissions of 3306 instances.
[root@test28 sbtest]# chown mysql:mysql sbtest9.*
8. Import 3306 instances
root@localhost 16:46: [sbtest]> alter table sbtest.sbtest9 import tablespace;
Query OK, 0 rows affected (0.04 sec)
9. Verify
3306 examples
root@localhost 16:50: [sbtest]> select count() from sbtest.sbtest9;
±---------+
| count() |
±---------+
| 1000 |
±---------+
1 row in set (0.00 sec)
It is not recommended because the master-slave replication relationship has been destroyed. It is best to use mysqldump so that the master-slave replication relationship will not be destroyed.
Table space transfer is not recommended in the copy structure, because it needs to operate in both master and slave, and it will not copy it to the slave library.
mysqldump is only used in the main database operation, and it will be copied from Hu.
When transferring from the library table space, an error will be reported:
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
Later in the study.
summary
1. The main thing is to restore a slave library with all the backup.
2. The key is to find out the GTID before drop table. Therefore, the first command after online is to switch logs.
3. For a large number of logs, we can use replication filter to copy only this table when building the master-slave replication relationship.
4,sql_ The thread is not all applied, but the GTID before it is applied to the drop table.
Main technical points:
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'
The above four commands.