mysql xtrabackup 全量复制
官网：https://www.percona.com/doc/percona-xtrabackup/LATEST/backup_scenarios/full_backup.html
********************
相关参数
--datadir：需要备份的数据目录
--target-dir：备份数据存放目录
If the target directory does not exist, xtrabackup creates it.
If the directory does exist and is empty, xtrabackup will succeed.
# xtrabackup不会覆盖文件，如果备份目录中要创建的文件存在，会报错退出
xtrabackup will not overwrite existing files, it will fail with operating system error 17, file exists
--log-bin：二进制日志基础名称，默认为/var/lib/mysql/binlog
--log-index：二进制日志索引文件，默认为/var/lib/mysql/binlog.index
********************
示例：mysql数据目录为dir，备份数据到目录dir2，还原数据到dir3；mysql2使用dir3做数据目录
docker创建 mysql
docker run -it -d --net fixed --ip 172.18.0.2 -p 3306:3306 \
-v /usr/mysql/single/data:/var/lib/mysql \
--privileged=true -e MYSQL_ROOT_PASSWORD=123456 --name mysql mysql
docker run -it -d --net fixed --ip 172.18.0.4 -p 3307:3306 \
-v /usr/mysql/single/data2:/var/lib/mysql \
--privileged=true -e MYSQL_ROOT_PASSWORD=123456 --name mysql2 mysql
、
数据备份到目录 /usr/mysql/data/backup
[root@centos single]# xtrabackup -u root --password=123456 -H 192.168.57.120 -P 3306 --backup --log-bin=/usr/mysql/single/data/binlog --log-bin-index=/usr/mysql/single/data/binlog.index --datadir=/usr/mysql/single/data --target-dir=/usr/mysql/single/backup
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --log_bin=/usr/mysql/single/data/binlog --log-bin-index=/usr/mysql/single/data/binlog.index --datadir=/usr/mysql/single/data
xtrabackup: recognized client arguments: --user=root --password=* --host=192.168.57.120 --port=3306 --backup=1 --target-dir=/usr/mysql/single/backup
xtrabackup version 8.0.25-17 based on MySQL server 8.0.25 Linux (x86_64) (revision id: d27028b)
210609 21:36:38 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=192.168.57.120;port=3306' as 'root' (using password: YES).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup;host=192.168.57.120;port=3306','root',...) failed: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: 无法打开共享对象文件: 没有那个文件或目录 at - line 1535.
210609 21:36:38 Connecting to MySQL server host: 192.168.57.120, user: root, password: set, port: 3306, socket: not set
Using server version 8.0.25
Warning: option 'datadir' has different values:
'/usr/mysql/single/data' in defaults file
'/var/lib/mysql/' in SHOW VARIABLES
210609 21:36:38 Executing LOCK INSTANCE FOR BACKUP...
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /usr/mysql/single/data
xtrabackup: open files limit requested 0, set to 1024
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 = 2
xtrabackup: innodb_log_file_size = 50331648
Number of pools: 1
xtrabackup: inititialize_service_handles suceeded
210609 21:36:38 Connecting to MySQL server host: 192.168.57.120, user: root, password: set, port: 3306, socket: not set
xtrabackup: Redo Log Archiving is not set up.
210609 21:36:39 >> log scanned up to (28649493)
xtrabackup: Generating a list of tablespaces
xtrabackup: Generating a list of tablespaces
Scanning './'
Completed space ID check of 2 files.
Allocated tablespace ID 1 for sys/sys_config, old maximum was 0
Using undo tablespace './undo_001'.
Using undo tablespace './undo_002'.
Opened 2 existing undo tablespaces.
210609 21:36:39 [01] Copying ./ibdata1 to /usr/mysql/single/backup/ibdata1
210609 21:36:39 [01] ...done
210609 21:36:39 [01] Copying ./sys/sys_config.ibd to /usr/mysql/single/backup/sys/sys_config.ibd
210609 21:36:39 [01] ...done
210609 21:36:39 [01] Copying ./mysql.ibd to /usr/mysql/single/backup/mysql.ibd
210609 21:36:39 [01] ...done
210609 21:36:39 [01] Copying ./undo_002 to /usr/mysql/single/backup/undo_002
210609 21:36:39 [01] ...done
210609 21:36:39 [01] Copying ./undo_001 to /usr/mysql/single/backup/undo_001
210609 21:36:39 [01] ...done
210609 21:36:40 >> log scanned up to (28649493)
210609 21:36:40 Starting to backup non-InnoDB tables and files
210609 21:36:40 [01] Copying mysql/general_log_213.sdi to /usr/mysql/single/backup/mysql/general_log_213.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying mysql/general_log.CSM to /usr/mysql/single/backup/mysql/general_log.CSM
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying mysql/general_log.CSV to /usr/mysql/single/backup/mysql/general_log.CSV
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying mysql/slow_log_214.sdi to /usr/mysql/single/backup/mysql/slow_log_214.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying mysql/slow_log.CSM to /usr/mysql/single/backup/mysql/slow_log.CSM
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying mysql/slow_log.CSV to /usr/mysql/single/backup/mysql/slow_log.CSV
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/cond_instances_82.sdi to /usr/mysql/single/backup/performance_schema/cond_instances_82.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/error_log_83.sdi to /usr/mysql/single/backup/performance_schema/error_log_83.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_waits_cur_84.sdi to /usr/mysql/single/backup/performance_schema/events_waits_cur_84.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_waits_his_85.sdi to /usr/mysql/single/backup/performance_schema/events_waits_his_85.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_waits_his_86.sdi to /usr/mysql/single/backup/performance_schema/events_waits_his_86.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_waits_sum_87.sdi to /usr/mysql/single/backup/performance_schema/events_waits_sum_87.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_waits_sum_88.sdi to /usr/mysql/single/backup/performance_schema/events_waits_sum_88.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_waits_sum_89.sdi to /usr/mysql/single/backup/performance_schema/events_waits_sum_89.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_waits_sum_90.sdi to /usr/mysql/single/backup/performance_schema/events_waits_sum_90.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_waits_sum_91.sdi to /usr/mysql/single/backup/performance_schema/events_waits_sum_91.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_waits_sum_92.sdi to /usr/mysql/single/backup/performance_schema/events_waits_sum_92.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/file_instances_93.sdi to /usr/mysql/single/backup/performance_schema/file_instances_93.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/file_summary_by__94.sdi to /usr/mysql/single/backup/performance_schema/file_summary_by__94.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/file_summary_by__95.sdi to /usr/mysql/single/backup/performance_schema/file_summary_by__95.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/host_cache_96.sdi to /usr/mysql/single/backup/performance_schema/host_cache_96.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/mutex_instances_97.sdi to /usr/mysql/single/backup/performance_schema/mutex_instances_97.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/objects_summary__98.sdi to /usr/mysql/single/backup/performance_schema/objects_summary__98.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/performance_time_99.sdi to /usr/mysql/single/backup/performance_schema/performance_time_99.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/processlist_100.sdi to /usr/mysql/single/backup/performance_schema/processlist_100.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/rwlock_instances_101.sdi to /usr/mysql/single/backup/performance_schema/rwlock_instances_101.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/setup_actors_102.sdi to /usr/mysql/single/backup/performance_schema/setup_actors_102.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/setup_consumers_103.sdi to /usr/mysql/single/backup/performance_schema/setup_consumers_103.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/setup_instrument_104.sdi to /usr/mysql/single/backup/performance_schema/setup_instrument_104.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/setup_objects_105.sdi to /usr/mysql/single/backup/performance_schema/setup_objects_105.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/setup_threads_106.sdi to /usr/mysql/single/backup/performance_schema/setup_threads_106.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/table_io_waits_s_107.sdi to /usr/mysql/single/backup/performance_schema/table_io_waits_s_107.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/table_io_waits_s_108.sdi to /usr/mysql/single/backup/performance_schema/table_io_waits_s_108.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/table_lock_waits_109.sdi to /usr/mysql/single/backup/performance_schema/table_lock_waits_109.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/threads_110.sdi to /usr/mysql/single/backup/performance_schema/threads_110.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_stages_cu_111.sdi to /usr/mysql/single/backup/performance_schema/events_stages_cu_111.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_stages_hi_112.sdi to /usr/mysql/single/backup/performance_schema/events_stages_hi_112.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_stages_hi_113.sdi to /usr/mysql/single/backup/performance_schema/events_stages_hi_113.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_stages_su_114.sdi to /usr/mysql/single/backup/performance_schema/events_stages_su_114.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_stages_su_115.sdi to /usr/mysql/single/backup/performance_schema/events_stages_su_115.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_stages_su_116.sdi to /usr/mysql/single/backup/performance_schema/events_stages_su_116.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_stages_su_117.sdi to /usr/mysql/single/backup/performance_schema/events_stages_su_117.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_stages_su_118.sdi to /usr/mysql/single/backup/performance_schema/events_stages_su_118.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_statement_119.sdi to /usr/mysql/single/backup/performance_schema/events_statement_119.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_statement_120.sdi to /usr/mysql/single/backup/performance_schema/events_statement_120.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_statement_121.sdi to /usr/mysql/single/backup/performance_schema/events_statement_121.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_statement_122.sdi to /usr/mysql/single/backup/performance_schema/events_statement_122.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_statement_123.sdi to /usr/mysql/single/backup/performance_schema/events_statement_123.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_statement_124.sdi to /usr/mysql/single/backup/performance_schema/events_statement_124.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_statement_125.sdi to /usr/mysql/single/backup/performance_schema/events_statement_125.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_statement_126.sdi to /usr/mysql/single/backup/performance_schema/events_statement_126.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_statement_127.sdi to /usr/mysql/single/backup/performance_schema/events_statement_127.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_statement_128.sdi to /usr/mysql/single/backup/performance_schema/events_statement_128.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_statement_129.sdi to /usr/mysql/single/backup/performance_schema/events_statement_129.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_statement_130.sdi to /usr/mysql/single/backup/performance_schema/events_statement_130.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_transacti_131.sdi to /usr/mysql/single/backup/performance_schema/events_transacti_131.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_transacti_132.sdi to /usr/mysql/single/backup/performance_schema/events_transacti_132.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_transacti_133.sdi to /usr/mysql/single/backup/performance_schema/events_transacti_133.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_transacti_134.sdi to /usr/mysql/single/backup/performance_schema/events_transacti_134.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_transacti_135.sdi to /usr/mysql/single/backup/performance_schema/events_transacti_135.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_transacti_136.sdi to /usr/mysql/single/backup/performance_schema/events_transacti_136.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_transacti_137.sdi to /usr/mysql/single/backup/performance_schema/events_transacti_137.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_transacti_138.sdi to /usr/mysql/single/backup/performance_schema/events_transacti_138.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_errors_su_139.sdi to /usr/mysql/single/backup/performance_schema/events_errors_su_139.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_errors_su_140.sdi to /usr/mysql/single/backup/performance_schema/events_errors_su_140.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_errors_su_141.sdi to /usr/mysql/single/backup/performance_schema/events_errors_su_141.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_errors_su_142.sdi to /usr/mysql/single/backup/performance_schema/events_errors_su_142.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/events_errors_su_143.sdi to /usr/mysql/single/backup/performance_schema/events_errors_su_143.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/users_144.sdi to /usr/mysql/single/backup/performance_schema/users_144.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/accounts_145.sdi to /usr/mysql/single/backup/performance_schema/accounts_145.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/hosts_146.sdi to /usr/mysql/single/backup/performance_schema/hosts_146.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/socket_instances_147.sdi to /usr/mysql/single/backup/performance_schema/socket_instances_147.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/socket_summary_b_148.sdi to /usr/mysql/single/backup/performance_schema/socket_summary_b_148.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/socket_summary_b_149.sdi to /usr/mysql/single/backup/performance_schema/socket_summary_b_149.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/session_connect__150.sdi to /usr/mysql/single/backup/performance_schema/session_connect__150.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/session_account__151.sdi to /usr/mysql/single/backup/performance_schema/session_account__151.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/keyring_keys_152.sdi to /usr/mysql/single/backup/performance_schema/keyring_keys_152.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/memory_summary_g_153.sdi to /usr/mysql/single/backup/performance_schema/memory_summary_g_153.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/memory_summary_b_154.sdi to /usr/mysql/single/backup/performance_schema/memory_summary_b_154.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/memory_summary_b_155.sdi to /usr/mysql/single/backup/performance_schema/memory_summary_b_155.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/memory_summary_b_156.sdi to /usr/mysql/single/backup/performance_schema/memory_summary_b_156.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/memory_summary_b_157.sdi to /usr/mysql/single/backup/performance_schema/memory_summary_b_157.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/table_handles_158.sdi to /usr/mysql/single/backup/performance_schema/table_handles_158.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/metadata_locks_159.sdi to /usr/mysql/single/backup/performance_schema/metadata_locks_159.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/data_locks_160.sdi to /usr/mysql/single/backup/performance_schema/data_locks_160.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/data_lock_waits_161.sdi to /usr/mysql/single/backup/performance_schema/data_lock_waits_161.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/replication_conn_162.sdi to /usr/mysql/single/backup/performance_schema/replication_conn_162.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/replication_grou_163.sdi to /usr/mysql/single/backup/performance_schema/replication_grou_163.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/replication_conn_164.sdi to /usr/mysql/single/backup/performance_schema/replication_conn_164.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/replication_appl_165.sdi to /usr/mysql/single/backup/performance_schema/replication_appl_165.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/replication_appl_166.sdi to /usr/mysql/single/backup/performance_schema/replication_appl_166.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/replication_appl_167.sdi to /usr/mysql/single/backup/performance_schema/replication_appl_167.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/replication_appl_168.sdi to /usr/mysql/single/backup/performance_schema/replication_appl_168.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/replication_grou_169.sdi to /usr/mysql/single/backup/performance_schema/replication_grou_169.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/replication_appl_170.sdi to /usr/mysql/single/backup/performance_schema/replication_appl_170.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/replication_appl_171.sdi to /usr/mysql/single/backup/performance_schema/replication_appl_171.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/replication_asyn_172.sdi to /usr/mysql/single/backup/performance_schema/replication_asyn_172.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/replication_asyn_173.sdi to /usr/mysql/single/backup/performance_schema/replication_asyn_173.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/log_status_174.sdi to /usr/mysql/single/backup/performance_schema/log_status_174.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/prepared_stateme_175.sdi to /usr/mysql/single/backup/performance_schema/prepared_stateme_175.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/user_variables_b_176.sdi to /usr/mysql/single/backup/performance_schema/user_variables_b_176.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/status_by_accoun_177.sdi to /usr/mysql/single/backup/performance_schema/status_by_accoun_177.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/status_by_host_178.sdi to /usr/mysql/single/backup/performance_schema/status_by_host_178.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/status_by_thread_179.sdi to /usr/mysql/single/backup/performance_schema/status_by_thread_179.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/status_by_user_180.sdi to /usr/mysql/single/backup/performance_schema/status_by_user_180.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/global_status_181.sdi to /usr/mysql/single/backup/performance_schema/global_status_181.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/session_status_182.sdi to /usr/mysql/single/backup/performance_schema/session_status_182.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/variables_by_thr_183.sdi to /usr/mysql/single/backup/performance_schema/variables_by_thr_183.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/global_variables_184.sdi to /usr/mysql/single/backup/performance_schema/global_variables_184.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/session_variable_185.sdi to /usr/mysql/single/backup/performance_schema/session_variable_185.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/variables_info_186.sdi to /usr/mysql/single/backup/performance_schema/variables_info_186.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/persisted_variab_187.sdi to /usr/mysql/single/backup/performance_schema/persisted_variab_187.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/user_defined_fun_188.sdi to /usr/mysql/single/backup/performance_schema/user_defined_fun_188.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/binary_log_trans_189.sdi to /usr/mysql/single/backup/performance_schema/binary_log_trans_189.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/tls_channel_stat_190.sdi to /usr/mysql/single/backup/performance_schema/tls_channel_stat_190.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 [01] Copying performance_schema/keyring_componen_191.sdi to /usr/mysql/single/backup/performance_schema/keyring_componen_191.sdi
210609 21:36:40 [01] ...done
210609 21:36:40 Finished backing up non-InnoDB tables and files
210609 21:36:40 Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS
210609 21:36:40 Selecting LSN and binary log position from p_s.log_status
210609 21:36:40 [00] Copying /usr/mysql/single/data/binlog.000006 to /usr/mysql/single/backup/binlog.000006 up to position 156
210609 21:36:40 [00] ...done
210609 21:36:40 [00] Writing /usr/mysql/single/backup/binlog.index
210609 21:36:40 [00] ...done
210609 21:36:40 [00] Writing /usr/mysql/single/backup/xtrabackup_binlog_info
210609 21:36:40 [00] ...done
210609 21:36:40 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '28649493'
xtrabackup: Stopping log copying thread at LSN 28649493.
Starting to parse redo log at lsn = 28649493
210609 21:36:40 Executing UNLOCK INSTANCE
210609 21:36:40 All tables unlocked
210609 21:36:40 [00] Copying ib_buffer_pool to /usr/mysql/single/backup/ib_buffer_pool
210609 21:36:40 [00] ...done
210609 21:36:40 Backup created in directory '/usr/mysql/single/backup/'
MySQL binlog position: filename 'binlog.000006', position '156'
210609 21:36:40 [00] Writing /usr/mysql/single/backup/backup-my.cnf
210609 21:36:40 [00] ...done
210609 21:36:40 [00] Writing /usr/mysql/single/backup/xtrabackup_info
210609 21:36:40 [00] ...done
xtrabackup: Transaction log of lsn (28649493) to (28649503) was copied.
210609 21:36:41 completed OK!
说明：transaction log即为 redo log
数据备份时，如果备份目录存在文件/usr/mysql/single/backup/xtrabackup_logfile，则会报错退出
[root@centos single]# xtrabackup -u root --password=123456 -H 192.168.57.120 -P 3306 --backup --log-bin=/usr/mysql/single/data/logbin --log-bin-index=/usr/mysql/single/data/logbin.index --datadir=/usr/mysql/single/data --target-dir=/usr/mysql/single/backup
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --log_bin=/usr/mysql/single/data/logbin --log-bin-index=/usr/mysql/single/data/logbin.index --datadir=/usr/mysql/single/data
xtrabackup: recognized client arguments: --user=root --password=* --host=192.168.57.120 --port=3306 --backup=1 --target-dir=/usr/mysql/single/backup
xtrabackup version 8.0.25-17 based on MySQL server 8.0.25 Linux (x86_64) (revision id: d27028b)
210609 21:29:26 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=192.168.57.120;port=3306' as 'root' (using password: YES).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup;host=192.168.57.120;port=3306','root',...) failed: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: 无法打开共享对象文件: 没有那个文件或目录 at - line 1535.
210609 21:29:26 Connecting to MySQL server host: 192.168.57.120, user: root, password: set, port: 3306, socket: not set
Using server version 8.0.25
Warning: option 'datadir' has different values:
'/usr/mysql/single/data' in defaults file
'/var/lib/mysql/' in SHOW VARIABLES
210609 21:29:26 Executing LOCK INSTANCE FOR BACKUP...
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /usr/mysql/single/data
xtrabackup: open files limit requested 0, set to 1024
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 = 2
xtrabackup: innodb_log_file_size = 50331648
Number of pools: 1
xtrabackup: inititialize_service_handles suceeded
210609 21:29:26 Connecting to MySQL server host: 192.168.57.120, user: root, password: set, port: 3306, socket: not set
xtrabackup: Redo Log Archiving is not set up.
xtrabackup: Can't create/write to file '/usr/mysql/single/backup/xtrabackup_logfile' (OS errno 17 - File exists)
xtrabackup: error: failed to open the target stream for 'xtrabackup_logfile'.
prepare 备份目录中的数据，回滚数据文件中事务没有提交的数据
[root@centos single]# xtrabackup --prepare --target-dir=/usr/mysql/single/backup
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0
xtrabackup: recognized client arguments: --prepare=1 --target-dir=/usr/mysql/single/backup
xtrabackup version 8.0.25-17 based on MySQL server 8.0.25 Linux (x86_64) (revision id: d27028b)
xtrabackup: cd to /usr/mysql/single/backup/
xtrabackup: This target seems to be not prepared yet.
Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(28649493)
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 = 8388608
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 = 8388608
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 4 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 28594717 in the system tablespace does not match the log sequence number 28649493 in the ib_logfiles!
Database was not shutdown normally!
Starting crash recovery.
Starting to parse redo log at lsn = 28649493, whereas checkpoint_lsn = 28649493 and start_lsn = 28649472
Doing recovery: scanned up to log sequence number 28649493
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: 2571
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.25 started; log sequence number 28649493
Allocated tablespace ID 1 for sys/sys_config, old maximum was 0
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 28649493
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 = 2
xtrabackup: innodb_log_file_size = 50331648
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 4 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
Creating log file ./ib_logfile101
Creating log file ./ib_logfile1
Renaming log file ./ib_logfile101 to ./ib_logfile0
New log files created, LSN=28649996
Starting to parse redo log at lsn = 28649996, whereas checkpoint_lsn = 28649996 and start_lsn = 28649984
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: 2571
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.25 started; log sequence number 28649996
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 28649996
210609 21:43:35 completed OK!
数据还原到目录 /usr/mysql/single/data2，data2目录须为空
[root@centos single]# xtrabackup --copy-back --datadir=/usr/mysql/single/data2 --target-dir=/usr/mysql/single/backup
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --datadir=/usr/mysql/single/data2
xtrabackup: recognized client arguments: --copy-back=1 --target-dir=/usr/mysql/single/backup
xtrabackup version 8.0.25-17 based on MySQL server 8.0.25 Linux (x86_64) (revision id: d27028b)
210609 21:46:14 [01] Copying undo_001 to /usr/mysql/single/data2/undo_001
210609 21:46:14 [01] ...done
210609 21:46:14 [01] Copying undo_002 to /usr/mysql/single/data2/undo_002
210609 21:46:14 [01] ...done
210609 21:46:15 [01] Copying ib_logfile0 to /usr/mysql/single/data2/ib_logfile0
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ib_logfile1 to /usr/mysql/single/data2/ib_logfile1
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ibdata1 to /usr/mysql/single/data2/ibdata1
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying binlog.000006 to /usr/mysql/single/data2/binlog.000006
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying binlog.index to /usr/mysql/single/data2/binlog.index
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./sys/sys_config.ibd to /usr/mysql/single/data2/sys/sys_config.ibd
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./mysql.ibd to /usr/mysql/single/data2/mysql.ibd
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./mysql/general_log_213.sdi to /usr/mysql/single/data2/mysql/general_log_213.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./mysql/general_log.CSM to /usr/mysql/single/data2/mysql/general_log.CSM
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./mysql/general_log.CSV to /usr/mysql/single/data2/mysql/general_log.CSV
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./mysql/slow_log_214.sdi to /usr/mysql/single/data2/mysql/slow_log_214.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./mysql/slow_log.CSM to /usr/mysql/single/data2/mysql/slow_log.CSM
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./mysql/slow_log.CSV to /usr/mysql/single/data2/mysql/slow_log.CSV
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/cond_instances_82.sdi to /usr/mysql/single/data2/performance_schema/cond_instances_82.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/error_log_83.sdi to /usr/mysql/single/data2/performance_schema/error_log_83.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_waits_cur_84.sdi to /usr/mysql/single/data2/performance_schema/events_waits_cur_84.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_waits_his_85.sdi to /usr/mysql/single/data2/performance_schema/events_waits_his_85.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_waits_his_86.sdi to /usr/mysql/single/data2/performance_schema/events_waits_his_86.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_waits_sum_87.sdi to /usr/mysql/single/data2/performance_schema/events_waits_sum_87.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_waits_sum_88.sdi to /usr/mysql/single/data2/performance_schema/events_waits_sum_88.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_waits_sum_89.sdi to /usr/mysql/single/data2/performance_schema/events_waits_sum_89.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_waits_sum_90.sdi to /usr/mysql/single/data2/performance_schema/events_waits_sum_90.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_waits_sum_91.sdi to /usr/mysql/single/data2/performance_schema/events_waits_sum_91.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_waits_sum_92.sdi to /usr/mysql/single/data2/performance_schema/events_waits_sum_92.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/file_instances_93.sdi to /usr/mysql/single/data2/performance_schema/file_instances_93.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/file_summary_by__94.sdi to /usr/mysql/single/data2/performance_schema/file_summary_by__94.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/file_summary_by__95.sdi to /usr/mysql/single/data2/performance_schema/file_summary_by__95.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/host_cache_96.sdi to /usr/mysql/single/data2/performance_schema/host_cache_96.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/mutex_instances_97.sdi to /usr/mysql/single/data2/performance_schema/mutex_instances_97.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/objects_summary__98.sdi to /usr/mysql/single/data2/performance_schema/objects_summary__98.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/performance_time_99.sdi to /usr/mysql/single/data2/performance_schema/performance_time_99.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/processlist_100.sdi to /usr/mysql/single/data2/performance_schema/processlist_100.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/rwlock_instances_101.sdi to /usr/mysql/single/data2/performance_schema/rwlock_instances_101.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/setup_actors_102.sdi to /usr/mysql/single/data2/performance_schema/setup_actors_102.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/setup_consumers_103.sdi to /usr/mysql/single/data2/performance_schema/setup_consumers_103.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/setup_instrument_104.sdi to /usr/mysql/single/data2/performance_schema/setup_instrument_104.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/setup_objects_105.sdi to /usr/mysql/single/data2/performance_schema/setup_objects_105.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/setup_threads_106.sdi to /usr/mysql/single/data2/performance_schema/setup_threads_106.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/table_io_waits_s_107.sdi to /usr/mysql/single/data2/performance_schema/table_io_waits_s_107.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/table_io_waits_s_108.sdi to /usr/mysql/single/data2/performance_schema/table_io_waits_s_108.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/table_lock_waits_109.sdi to /usr/mysql/single/data2/performance_schema/table_lock_waits_109.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/threads_110.sdi to /usr/mysql/single/data2/performance_schema/threads_110.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_stages_cu_111.sdi to /usr/mysql/single/data2/performance_schema/events_stages_cu_111.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_stages_hi_112.sdi to /usr/mysql/single/data2/performance_schema/events_stages_hi_112.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_stages_hi_113.sdi to /usr/mysql/single/data2/performance_schema/events_stages_hi_113.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_stages_su_114.sdi to /usr/mysql/single/data2/performance_schema/events_stages_su_114.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_stages_su_115.sdi to /usr/mysql/single/data2/performance_schema/events_stages_su_115.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_stages_su_116.sdi to /usr/mysql/single/data2/performance_schema/events_stages_su_116.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_stages_su_117.sdi to /usr/mysql/single/data2/performance_schema/events_stages_su_117.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_stages_su_118.sdi to /usr/mysql/single/data2/performance_schema/events_stages_su_118.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_statement_119.sdi to /usr/mysql/single/data2/performance_schema/events_statement_119.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_statement_120.sdi to /usr/mysql/single/data2/performance_schema/events_statement_120.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_statement_121.sdi to /usr/mysql/single/data2/performance_schema/events_statement_121.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_statement_122.sdi to /usr/mysql/single/data2/performance_schema/events_statement_122.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_statement_123.sdi to /usr/mysql/single/data2/performance_schema/events_statement_123.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_statement_124.sdi to /usr/mysql/single/data2/performance_schema/events_statement_124.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_statement_125.sdi to /usr/mysql/single/data2/performance_schema/events_statement_125.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_statement_126.sdi to /usr/mysql/single/data2/performance_schema/events_statement_126.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_statement_127.sdi to /usr/mysql/single/data2/performance_schema/events_statement_127.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_statement_128.sdi to /usr/mysql/single/data2/performance_schema/events_statement_128.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_statement_129.sdi to /usr/mysql/single/data2/performance_schema/events_statement_129.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_statement_130.sdi to /usr/mysql/single/data2/performance_schema/events_statement_130.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_transacti_131.sdi to /usr/mysql/single/data2/performance_schema/events_transacti_131.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_transacti_132.sdi to /usr/mysql/single/data2/performance_schema/events_transacti_132.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_transacti_133.sdi to /usr/mysql/single/data2/performance_schema/events_transacti_133.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_transacti_134.sdi to /usr/mysql/single/data2/performance_schema/events_transacti_134.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_transacti_135.sdi to /usr/mysql/single/data2/performance_schema/events_transacti_135.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_transacti_136.sdi to /usr/mysql/single/data2/performance_schema/events_transacti_136.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_transacti_137.sdi to /usr/mysql/single/data2/performance_schema/events_transacti_137.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_transacti_138.sdi to /usr/mysql/single/data2/performance_schema/events_transacti_138.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_errors_su_139.sdi to /usr/mysql/single/data2/performance_schema/events_errors_su_139.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_errors_su_140.sdi to /usr/mysql/single/data2/performance_schema/events_errors_su_140.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_errors_su_141.sdi to /usr/mysql/single/data2/performance_schema/events_errors_su_141.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_errors_su_142.sdi to /usr/mysql/single/data2/performance_schema/events_errors_su_142.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/events_errors_su_143.sdi to /usr/mysql/single/data2/performance_schema/events_errors_su_143.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/users_144.sdi to /usr/mysql/single/data2/performance_schema/users_144.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/accounts_145.sdi to /usr/mysql/single/data2/performance_schema/accounts_145.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/hosts_146.sdi to /usr/mysql/single/data2/performance_schema/hosts_146.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/socket_instances_147.sdi to /usr/mysql/single/data2/performance_schema/socket_instances_147.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/socket_summary_b_148.sdi to /usr/mysql/single/data2/performance_schema/socket_summary_b_148.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/socket_summary_b_149.sdi to /usr/mysql/single/data2/performance_schema/socket_summary_b_149.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/session_connect__150.sdi to /usr/mysql/single/data2/performance_schema/session_connect__150.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/session_account__151.sdi to /usr/mysql/single/data2/performance_schema/session_account__151.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/keyring_keys_152.sdi to /usr/mysql/single/data2/performance_schema/keyring_keys_152.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/memory_summary_g_153.sdi to /usr/mysql/single/data2/performance_schema/memory_summary_g_153.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/memory_summary_b_154.sdi to /usr/mysql/single/data2/performance_schema/memory_summary_b_154.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/memory_summary_b_155.sdi to /usr/mysql/single/data2/performance_schema/memory_summary_b_155.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/memory_summary_b_156.sdi to /usr/mysql/single/data2/performance_schema/memory_summary_b_156.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/memory_summary_b_157.sdi to /usr/mysql/single/data2/performance_schema/memory_summary_b_157.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/table_handles_158.sdi to /usr/mysql/single/data2/performance_schema/table_handles_158.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/metadata_locks_159.sdi to /usr/mysql/single/data2/performance_schema/metadata_locks_159.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/data_locks_160.sdi to /usr/mysql/single/data2/performance_schema/data_locks_160.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/data_lock_waits_161.sdi to /usr/mysql/single/data2/performance_schema/data_lock_waits_161.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/replication_conn_162.sdi to /usr/mysql/single/data2/performance_schema/replication_conn_162.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/replication_grou_163.sdi to /usr/mysql/single/data2/performance_schema/replication_grou_163.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/replication_conn_164.sdi to /usr/mysql/single/data2/performance_schema/replication_conn_164.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/replication_appl_165.sdi to /usr/mysql/single/data2/performance_schema/replication_appl_165.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/replication_appl_166.sdi to /usr/mysql/single/data2/performance_schema/replication_appl_166.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/replication_appl_167.sdi to /usr/mysql/single/data2/performance_schema/replication_appl_167.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/replication_appl_168.sdi to /usr/mysql/single/data2/performance_schema/replication_appl_168.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/replication_grou_169.sdi to /usr/mysql/single/data2/performance_schema/replication_grou_169.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/replication_appl_170.sdi to /usr/mysql/single/data2/performance_schema/replication_appl_170.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/replication_appl_171.sdi to /usr/mysql/single/data2/performance_schema/replication_appl_171.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/replication_asyn_172.sdi to /usr/mysql/single/data2/performance_schema/replication_asyn_172.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/replication_asyn_173.sdi to /usr/mysql/single/data2/performance_schema/replication_asyn_173.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/log_status_174.sdi to /usr/mysql/single/data2/performance_schema/log_status_174.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/prepared_stateme_175.sdi to /usr/mysql/single/data2/performance_schema/prepared_stateme_175.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/user_variables_b_176.sdi to /usr/mysql/single/data2/performance_schema/user_variables_b_176.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/status_by_accoun_177.sdi to /usr/mysql/single/data2/performance_schema/status_by_accoun_177.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/status_by_host_178.sdi to /usr/mysql/single/data2/performance_schema/status_by_host_178.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/status_by_thread_179.sdi to /usr/mysql/single/data2/performance_schema/status_by_thread_179.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/status_by_user_180.sdi to /usr/mysql/single/data2/performance_schema/status_by_user_180.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/global_status_181.sdi to /usr/mysql/single/data2/performance_schema/global_status_181.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/session_status_182.sdi to /usr/mysql/single/data2/performance_schema/session_status_182.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/variables_by_thr_183.sdi to /usr/mysql/single/data2/performance_schema/variables_by_thr_183.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/global_variables_184.sdi to /usr/mysql/single/data2/performance_schema/global_variables_184.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/session_variable_185.sdi to /usr/mysql/single/data2/performance_schema/session_variable_185.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/variables_info_186.sdi to /usr/mysql/single/data2/performance_schema/variables_info_186.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/persisted_variab_187.sdi to /usr/mysql/single/data2/performance_schema/persisted_variab_187.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/user_defined_fun_188.sdi to /usr/mysql/single/data2/performance_schema/user_defined_fun_188.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/binary_log_trans_189.sdi to /usr/mysql/single/data2/performance_schema/binary_log_trans_189.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/tls_channel_stat_190.sdi to /usr/mysql/single/data2/performance_schema/tls_channel_stat_190.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./performance_schema/keyring_componen_191.sdi to /usr/mysql/single/data2/performance_schema/keyring_componen_191.sdi
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./ib_buffer_pool to /usr/mysql/single/data2/ib_buffer_pool
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./xtrabackup_info to /usr/mysql/single/data2/xtrabackup_info
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./xtrabackup_master_key_id to /usr/mysql/single/data2/xtrabackup_master_key_id
210609 21:46:15 [01] ...done
210609 21:46:15 [01] Copying ./ibtmp1 to /usr/mysql/single/data2/ibtmp1
210609 21:46:16 [01] ...done
210609 21:46:16 [01] Creating directory ./#innodb_temp
210609 21:46:16 [01] ...done.
210609 21:46:16 completed OK!
如果还原目录不为空，则不能还原数据
[root@centos ~]# xtrabackup --copy-back --datadir=/usr/mysql/single/data2 --target-dir=/usr/mysql/single/backup
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --datadir=/usr/mysql/single/data2
xtrabackup: recognized client arguments: --copy-back=1 --target-dir=/usr/mysql/single/backup
xtrabackup version 8.0.25-17 based on MySQL server 8.0.25 Linux (x86_64) (revision id: d27028b)
Original data directory /usr/mysql/single/data2 is not empty!
mysql2 查询数据
root@6f71527b845f:/# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.25 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 4 | 5 |
| 5 | 6 |
| 6 | 8 |
| 7 | 9 |
| 8 | 10 |
| 9 | 10 |
+----+-------+
9 rows in set (0.00 sec)
mysql2可查询到mysql中插入的数据
2379
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
