SLTechnology News&Howtos › Database ›

How to use Percona XtraBackup to backup and restore MySQL slave database from a single table

Shulou Source: shulou.com Published: 2022-06-01 05:18:40 10月04日 Update

Environment description:

Main library: 192.168.0.1

From library 1, 192.168.0.2

From library 2, 192.168.0.3

Backup tool: Percona xtrabackup version 2.4.8 based on MySQL server 5.7.13 Linux (x86x64) (revision id: 97330f7)

Create a chenfeng library on the main library:

Mysql > create database chenfeng

Query OK, 1 row affected (0.08 sec)

Mysql > use chenfeng

Database changed

Mysql > create table duansf (id int (11), name varchar (10))

Query OK, 0 rows affected (0.14 sec)

Mysql > insert into duansf values (1)

Query OK, 1 row affected (0.01sec)

Mysql > insert into duansf values (2)

Query OK, 1 row affected (0.01sec)

Back up only the duansf table of the chenfeng library:

[root@localhost backup] # innobackupex-defaults-file=/etc/my.cnf-- user=root-- password=dsf0723-S / tmp/mysql.sock-- slave-info-- safe-slave-backup-- include=chenfeng.duansf / data/backup

171015 20:33:07 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.

At the end of a successful backup run innobackupex

Prints "completed OK!".

171015 20:33:08 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql.sock' as' root' (using password: YES).

171015 20:33:08 version_check Connected to MySQL server

171015 20:33:08 version_check Executing a version check against the server...

171015 20:33:08 version_check Done.

171015 20:33:08 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: / tmp/mysql.sock

Using server version 5.7.18-log

Innobackupex version 2.4.8 based on MySQL server 5.7.13 Linux (x86 / 64) (revision id: 97330f7)

Xtrabackup: uses posix_fadvise ().

Xtrabackup: cd to / usr/local/mysql/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

InnoDB: Number of pools: 1

171015 20:33:08 > > log scanned up to (233975916)

Xtrabackup: Generating a list of tablespaces

InnoDB: Allocated tablespace ID 79 for chenfeng/duansf, old maximum was 0

171015 20:33:09 [01] Copying. / ibdata1 to / data/backup/2017-10-15 20-33-07/ibdata1

171015 20:33:09 [01]... done

171015 20:33:09 [01] Copying. / chenfeng/duansf.ibd to / data/backup/2017-10-15 20-33-07/chenfeng/duansf.ibd

171015 20:33:09 [01]... done

171015 20:33:09 > > log scanned up to (233975916)

171015 20:33:10 Slave open temp tables: 0

171015 20:33:10 Slave is safe to backup

171015 20:33:10 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...

171015 20:33:10 Executing FLUSH TABLES WITH READ LOCK...

171015 20:33:10 Starting to backup non-InnoDB tables and files

171015 20:33:10 [01] Skipping. / ibdata1.

171015 20:33:10 [01] Skipping. / ib_logfile1.

171015 20:33:10 [01] Skipping. / ib_logfile0.

171015 20:33:10 [01] Skipping. / mysql/db.opt.

171015 20:33:10 [01] Skipping. / mysql/plugin.ibd.

171015 20:33:10 [01] Skipping. / mysql/tables_priv.MYI.

171015 20:33:10 [01] Skipping. / mysql/time_zone.frm.

171015 20:33:10 [01] Skipping. / mysql/time_zone_leap_second.frm.

171015 20:33:10 [01] Skipping. / mysql/innodb_index_stats.frm.

171015 20:33:10 [01] Skipping. / mysql/innodb_table_stats.frm.

171015 20:33:10 [01] Skipping. / mysql/ndb_binlog_index.MYD.

171015 20:33:10 [01] Skipping. / mysql/proc.frm.

171015 20:33:10 [01] Skipping. / mysql/proc.MYD.

171015 20:33:10 [01] Skipping. / mysql/procs_priv.MYI.

171015 20:33:10 [01] Skipping. / mysql/time_zone_name.frm.

171015 20:33:10 [01] Skipping. / mysql/time_zone_name.ibd.

171015 20:33:10 [01] Skipping. / mysql/time_zone_transition.frm.

171015 20:33:10 [01] Skipping. / mysql/time_zone_transition.ibd.

171015 20:33:10 [01] Skipping. / mysql/time_zone_transition_type.frm.

171015 20:33:10 [01] Skipping. / mysql/time_zone_transition_type.ibd.

171015 20:33:10 [01] Skipping. / mysql/user.frm.

171015 20:33:10 [01] Skipping. / mysql/user.MYI.

171015 20:33:10 [01] Skipping. / mysql/user.MYD.

171015 20:33:10 [01] Skipping. / mysql/columns_priv.MYD.

171015 20:33:10 [01] Skipping. / mysql/ndb_binlog_index.MYI.

171015 20:33:10 [01] Skipping. / mysql/proxies_priv.frm.

171015 20:33:10 [01] Skipping. / mysql/proxies_priv.MYI.

171015 20:33:10 [01] Skipping. / mysql/proxies_priv.MYD.

171015 20:33:10 [01] Skipping. / mysql/server_cost.frm.

171015 20:33:10 [01] Skipping. / mysql/slave_master_info.ibd.

171015 20:33:10 [01] Skipping. / mysql/general_log.frm.

171015 20:33:10 [01] Skipping. / mysql/general_log.CSM.

171015 20:33:10 [01] Skipping. / mysql/general_log.CSV.

171015 20:33:10 [01] Skipping. / mysql/slow_log.frm.

171015 20:33:10 [01] Skipping. / mysql/slow_log.CSM.

171015 20:33:10 [01] Skipping. / mysql/slow_log.CSV.

171015 20:33:10 [01] Skipping. / mysql/procs_priv.MYD.

171015 20:33:10 [01] Skipping. / mysql/slave_relay_log_info.frm.

171015 20:33:10 [01] Skipping. / mysql/slave_relay_log_info.ibd.

171015 20:33:10 [01] Skipping. / mysql/slave_worker_info.frm.

171015 20:33:10 [01] Skipping. / mysql/slave_worker_info.ibd.

171015 20:33:10 [01] Skipping. / mysql/tables_priv.frm.

171015 20:33:10 [01] Skipping. / mysql/tables_priv.MYD.

171015 20:33:10 [01] Skipping. / mysql/time_zone.ibd.

171015 20:33:10 [01] Skipping. / mysql/server_cost.ibd.

171015 20:33:10 [01] Skipping. / mysql/servers.frm.

171015 20:33:10 [01] Skipping. / mysql/servers.ibd.

171015 20:33:10 [01] Skipping. / mysql/slave_master_info.frm.

171015 20:33:10 [01] Skipping. / mysql/time_zone_leap_second.ibd.

171015 20:33:10 [01] Skipping. / mysql/columns_priv.frm.

171015 20:33:10 [01] Skipping. / mysql/columns_priv.MYI.

171015 20:33:10 [01] Skipping. / mysql/db.frm.

171015 20:33:10 [01] Skipping. / mysql/db.MYI.

171015 20:33:10 [01] Skipping. / mysql/db.MYD.

171015 20:33:10 [01] Skipping. / mysql/engine_cost.frm.

171015 20:33:10 [01] Skipping. / mysql/engine_cost.ibd.

171015 20:33:10 [01] Skipping. / mysql/event.frm.

171015 20:33:10 [01] Skipping. / mysql/event.MYI.

171015 20:33:10 [01] Skipping. / mysql/event.MYD.

171015 20:33:10 [01] Skipping. / mysql/func.frm.

171015 20:33:10 [01] Skipping. / mysql/func.MYI.

171015 20:33:10 [01] Skipping. / mysql/func.MYD.

171015 20:33:10 [01] Skipping. / mysql/gtid_executed.frm.

171015 20:33:10 [01] Skipping. / mysql/gtid_executed.ibd.

171015 20:33:10 [01] Skipping. / mysql/help_category.frm.

171015 20:33:10 [01] Skipping. / mysql/help_category.ibd.

171015 20:33:10 [01] Skipping. / mysql/help_keyword.frm.

171015 20:33:10 [01] Skipping. / mysql/help_keyword.ibd.

171015 20:33:10 [01] Skipping. / mysql/help_relation.frm.

171015 20:33:10 [01] Skipping. / mysql/help_relation.ibd.

171015 20:33:10 [01] Skipping. / mysql/help_topic.frm.

171015 20:33:10 [01] Skipping. / mysql/help_topic.ibd.

171015 20:33:10 [01] Skipping. / mysql/innodb_index_stats.ibd.

171015 20:33:10 [01] Skipping. / mysql/innodb_table_stats.ibd.

171015 20:33:10 [01] Skipping. / mysql/ndb_binlog_index.frm.

171015 20:33:10 [01] Skipping. / mysql/proc.MYI.

171015 20:33:10 [01] Skipping. / mysql/procs_priv.frm.

171015 20:33:10 [01] Skipping. / mysql/plugin.frm.

171015 20:33:10 [01] Skipping. / performance_schema/db.opt.

171015 20:33:10 [01] Skipping. / performance_schema/cond_instances.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_waits_current.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_waits_history.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_waits_history_long.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_waits_summary_by_instance.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_waits_summary_by_host_by_event_name.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_waits_summary_by_user_by_event_name.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_waits_summary_by_account_by_event_name.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_waits_summary_by_thread_by_event_name.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_waits_summary_global_by_event_name.frm.

171015 20:33:10 [01] Skipping. / performance_schema/file_instances.frm.

171015 20:33:10 [01] Skipping. / performance_schema/file_summary_by_event_name.frm.

171015 20:33:10 [01] Skipping. / performance_schema/file_summary_by_instance.frm.

171015 20:33:10 [01] Skipping. / performance_schema/socket_instances.frm.

171015 20:33:10 [01] Skipping. / performance_schema/socket_summary_by_instance.frm.

171015 20:33:10 [01] Skipping. / performance_schema/socket_summary_by_event_name.frm.

171015 20:33:10 [01] Skipping. / performance_schema/host_cache.frm.

171015 20:33:10 [01] Skipping. / performance_schema/mutex_instances.frm.

171015 20:33:10 [01] Skipping. / performance_schema/objects_summary_global_by_type.frm.

171015 20:33:10 [01] Skipping. / performance_schema/performance_timers.frm.

171015 20:33:10 [01] Skipping. / performance_schema/rwlock_instances.frm.

171015 20:33:10 [01] Skipping. / performance_schema/setup_actors.frm.

171015 20:33:10 [01] Skipping. / performance_schema/setup_consumers.frm.

171015 20:33:10 [01] Skipping. / performance_schema/setup_instruments.frm.

171015 20:33:10 [01] Skipping. / performance_schema/setup_objects.frm.

171015 20:33:10 [01] Skipping. / performance_schema/setup_timers.frm.

171015 20:33:10 [01] Skipping. / performance_schema/table_io_waits_summary_by_index_usage.frm.

171015 20:33:10 [01] Skipping. / performance_schema/table_io_waits_summary_by_table.frm.

171015 20:33:10 [01] Skipping. / performance_schema/table_lock_waits_summary_by_table.frm.

171015 20:33:10 [01] Skipping. / performance_schema/threads.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_stages_current.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_stages_history.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_stages_history_long.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_stages_summary_by_thread_by_event_name.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_stages_summary_by_host_by_event_name.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_stages_summary_by_user_by_event_name.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_stages_summary_by_account_by_event_name.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_stages_summary_global_by_event_name.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_statements_current.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_statements_history.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_statements_history_long.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_statements_summary_by_thread_by_event_name.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_statements_summary_by_host_by_event_name.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_statements_summary_by_user_by_event_name.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_statements_summary_by_account_by_event_name.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_statements_summary_global_by_event_name.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_transactions_current.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_transactions_history.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_transactions_history_long.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_transactions_summary_by_thread_by_event_name.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_transactions_summary_by_host_by_event_name.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_transactions_summary_by_user_by_event_name.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_transactions_summary_by_account_by_event_name.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_transactions_summary_global_by_event_name.frm.

171015 20:33:10 [01] Skipping. / performance_schema/hosts.frm.

171015 20:33:10 [01] Skipping. / performance_schema/users.frm.

171015 20:33:10 [01] Skipping. / performance_schema/accounts.frm.

171015 20:33:10 [01] Skipping. / performance_schema/memory_summary_global_by_event_name.frm.

171015 20:33:10 [01] Skipping. / performance_schema/memory_summary_by_thread_by_event_name.frm.

171015 20:33:10 [01] Skipping. / performance_schema/memory_summary_by_account_by_event_name.frm.

171015 20:33:10 [01] Skipping. / performance_schema/memory_summary_by_host_by_event_name.frm.

171015 20:33:10 [01] Skipping. / performance_schema/memory_summary_by_user_by_event_name.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_statements_summary_by_digest.frm.

171015 20:33:10 [01] Skipping. / performance_schema/events_statements_summary_by_program.frm.

171015 20:33:10 [01] Skipping. / performance_schema/prepared_statements_instances.frm.

171015 20:33:10 [01] Skipping. / performance_schema/replication_connection_configuration.frm.

171015 20:33:10 [01] Skipping. / performance_schema/replication_group_member_stats.frm.

171015 20:33:10 [01] Skipping. / performance_schema/replication_group_members.frm.

171015 20:33:10 [01] Skipping. / performance_schema/replication_connection_status.frm.

171015 20:33:10 [01] Skipping. / performance_schema/replication_applier_configuration.frm.

171015 20:33:10 [01] Skipping. / performance_schema/replication_applier_status.frm.

171015 20:33:10 [01] Skipping. / performance_schema/replication_applier_status_by_coordinator.frm.

171015 20:33:10 [01] Skipping. / performance_schema/replication_applier_status_by_worker.frm.

171015 20:33:10 [01] Skipping. / performance_schema/session_connect_attrs.frm.

171015 20:33:10 [01] Skipping. / performance_schema/session_account_connect_attrs.frm.

171015 20:33:10 [01] Skipping. / performance_schema/table_handles.frm.

171015 20:33:10 [01] Skipping. / performance_schema/metadata_locks.frm.

171015 20:33:10 [01] Skipping. / performance_schema/user_variables_by_thread.frm.

171015 20:33:10 [01] Skipping. / performance_schema/variables_by_thread.frm.

171015 20:33:10 [01] Skipping. / performance_schema/global_variables.frm.

171015 20:33:10 [01] Skipping. / performance_schema/session_variables.frm.

171015 20:33:10 [01] Skipping. / performance_schema/status_by_thread.frm.

171015 20:33:10 [01] Skipping. / performance_schema/status_by_user.frm.

171015 20:33:10 [01] Skipping. / performance_schema/status_by_host.frm.

171015 20:33:10 [01] Skipping. / performance_schema/status_by_account.frm.

171015 20:33:10 [01] Skipping. / performance_schema/global_status.frm.

171015 20:33:10 [01] Skipping. / performance_schema/session_status.frm.

171015 20:33:10 [01] Skipping. / sys/db.opt.

171015 20:33:10 [01] Skipping. / sys/version.frm.

171015 20:33:10 [01] Skipping. / sys/sys_config.frm.

171015 20:33:10 [01] Skipping. / sys/sys_config.ibd.

171015 20:33:10 [01] Skipping. / sys/statements_with_full_table_scans.frm.

171015 20:33:10 [01] Skipping. / sys/sys_config_insert_set_user.TRN.

171015 20:33:10 [01] Skipping. / sys/processlist.frm.

171015 20:33:10 [01] Skipping. / sys/sys_config.TRG.

171015 20:33:10 [01] Skipping. / sys/statements_with_sorting.frm.

171015 20:33:10 [01] Skipping. / sys/sys_config_update_set_user.TRN.

171015 20:33:10 [01] Skipping. / sys/x@0024statements_with_sorting.frm.

171015 20:33:10 [01] Skipping. / sys/innodb_buffer_stats_by_schema.frm.

171015 20:33:10 [01] Skipping. / sys/schema_index_statistics.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024innodb_buffer_stats_by_schema.frm.

171015 20:33:10 [01] Skipping. / sys/statements_with_temp_tables.frm.

171015 20:33:10 [01] Skipping. / sys/innodb_buffer_stats_by_table.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024ps_schema_table_statistics_io.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024innodb_buffer_stats_by_table.frm.

171015 20:33:10 [01] Skipping. / sys/host_summary.frm.

171015 20:33:10 [01] Skipping. / sys/innodb_lock_waits.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024host_summary.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024innodb_lock_waits.frm.

171015 20:33:10 [01] Skipping. / sys/waits_by_user_by_latency.frm.

171015 20:33:10 [01] Skipping. / sys/schema_object_overview.frm.

171015 20:33:10 [01] Skipping. / sys/user_summary_by_file_io_type.frm.

171015 20:33:10 [01] Skipping. / sys/schema_auto_increment_columns.frm.

171015 20:33:10 [01] Skipping. / sys/user_summary_by_file_io.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024schema_flattened_keys.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024user_summary_by_file_io.frm.

171015 20:33:10 [01] Skipping. / sys/schema_redundant_indexes.frm.

171015 20:33:10 [01] Skipping. / sys/user_summary_by_statement_type.frm.

171015 20:33:10 [01] Skipping. / sys/ps_check_lost_instrumentation.frm.

171015 20:33:10 [01] Skipping. / sys/latest_file_io.frm.

171015 20:33:10 [01] Skipping. / sys/waits_by_host_by_latency.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024latest_file_io.frm.

171015 20:33:10 [01] Skipping. / sys/waits_global_by_latency.frm.

171015 20:33:10 [01] Skipping. / sys/io_by_thread_by_latency.frm.

171015 20:33:10 [01] Skipping. / sys/user_summary_by_stages.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024io_by_thread_by_latency.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024user_summary_by_stages.frm.

171015 20:33:10 [01] Skipping. / sys/io_global_by_file_by_bytes.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024schema_index_statistics.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024io_global_by_file_by_bytes.frm.

171015 20:33:10 [01] Skipping. / sys/user_summary.frm.

171015 20:33:10 [01] Skipping. / sys/io_global_by_file_by_latency.frm.

171015 20:33:10 [01] Skipping. / sys/schema_table_statistics.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024io_global_by_file_by_latency.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024user_summary.frm.

171015 20:33:10 [01] Skipping. / sys/io_global_by_wait_by_bytes.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024schema_table_statistics.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024io_global_by_wait_by_bytes.frm.

171015 20:33:10 [01] Skipping. / sys/host_summary_by_file_io_type.frm.

171015 20:33:10 [01] Skipping. / sys/io_global_by_wait_by_latency.frm.

171015 20:33:10 [01] Skipping. / sys/schema_table_statistics_with_buffer.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024io_global_by_wait_by_latency.frm.

171015 20:33:10 [01] Skipping. / sys/host_summary_by_file_io.frm.

171015 20:33:10 [01] Skipping. / sys/memory_by_user_by_current_bytes.frm.

171015 20:33:10 [01] Skipping. / sys/schema_table_lock_waits.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024memory_by_user_by_current_bytes.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024host_summary_by_file_io.frm.

171015 20:33:10 [01] Skipping. / sys/memory_by_host_by_current_bytes.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024schema_table_lock_waits.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024memory_by_host_by_current_bytes.frm.

171015 20:33:10 [01] Skipping. / sys/statement_analysis.frm.

171015 20:33:10 [01] Skipping. / sys/memory_by_thread_by_current_bytes.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024statement_analysis.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024memory_by_thread_by_current_bytes.frm.

171015 20:33:10 [01] Skipping. / sys/host_summary_by_statement_type.frm.

171015 20:33:10 [01] Skipping. / sys/memory_global_by_current_bytes.frm.

171015 20:33:10 [01] Skipping. / sys/statements_with_errors_or_warnings.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024memory_global_by_current_bytes.frm.

171015 20:33:10 [01] Skipping. / sys/metrics.frm.

171015 20:33:10 [01] Skipping. / sys/memory_global_total.frm.

171015 20:33:10 [01] Skipping. / sys/host_summary_by_stages.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024memory_global_total.frm.

171015 20:33:10 [01] Skipping. / sys/session.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024schema_table_statistics_with_buffer.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024statements_with_errors_or_warnings.frm.

171015 20:33:10 [01] Skipping. / sys/schema_tables_with_full_table_scans.frm.

171015 20:33:10 [01] Skipping. / sys/schema_unused_indexes.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024schema_tables_with_full_table_scans.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024host_summary_by_stages.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024statements_with_full_table_scans.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024ps_digest_avg_latency_distribution.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024ps_digest_95th_percentile_by_avg_us.frm.

171015 20:33:10 [01] Skipping. / sys/statements_with_runtimes_in_95th_percentile.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024statements_with_runtimes_in_95th_percentile.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024statements_with_temp_tables.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024user_summary_by_file_io_type.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024user_summary_by_statement_type.frm.

171015 20:33:10 [01] Skipping. / sys/user_summary_by_statement_latency.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024user_summary_by_statement_latency.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024host_summary_by_file_io_type.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024host_summary_by_statement_type.frm.

171015 20:33:10 [01] Skipping. / sys/host_summary_by_statement_latency.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024host_summary_by_statement_latency.frm.

171015 20:33:10 [01] Skipping. / sys/wait_classes_global_by_avg_latency.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024wait_classes_global_by_avg_latency.frm.

171015 20:33:10 [01] Skipping. / sys/wait_classes_global_by_latency.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024wait_classes_global_by_latency.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024waits_by_user_by_latency.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024waits_by_host_by_latency.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024waits_global_by_latency.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024processlist.frm.

171015 20:33:10 [01] Skipping. / sys/x@0024session.frm.

171015 20:33:10 [01] Skipping. / sys/session_ssl_status.frm.

171015 20:33:10 [01] Skipping. / .rnd.

171015 20:33:10 [01] Skipping. / ca-key.pem.

171015 20:33:10 [01] Skipping. / ca-req.pem.

171015 20:33:10 [01] Skipping. / ca.pem.

171015 20:33:10 [01] Skipping. / server-key.pem.

171015 20:33:10 [01] Skipping. / server-req.pem.

171015 20:33:10 [01] Skipping. / server-cert.pem.

171015 20:33:10 [01] Skipping. / client-key.pem.

171015 20:33:10 [01] Skipping. / client-req.pem.

171015 20:33:10 [01] Skipping. / client-cert.pem.

171015 20:33:10 [01] Skipping. / mysql-bin.000004.

171015 20:33:10 [01] Skipping. / mysql-bin.000006.

171015 20:33:10 [01] Skipping. / mysql-bin.000007.

171015 20:33:10 [01] Skipping. / localhost-relay-bin.000018.

171015 20:33:10 [01] Skipping. / localhost-relay-bin.index.

171015 20:33:10 [01] Skipping. / mysql-bin.000001.

171015 20:33:10 [01] Skipping. / test/db.opt.

171015 20:33:10 [01] Skipping. / test/test.frm.

171015 20:33:10 [01] Skipping. / test/test.ibd.

171015 20:33:10 [01] Skipping. / test/chenfeng.frm.

171015 20:33:10 [01] Skipping. / test/chenfeng.ibd.

171015 20:33:10 [01] Skipping. / test/dsf.frm.

171015 20:33:10 [01] Skipping. / test/dsf.ibd.

171015 20:33:10 [01] Skipping. / ibtmp1.

171015 20:33:10 [01] Skipping. / mysql-bin.000008.

171015 20:33:10 [01] Skipping. / localhost.localdomain.pid.

171015 20:33:10 [01] Skipping. / localhost-relay-bin.000017.

171015 20:33:10 [01] Skipping. / mysql-bin.000002.

171015 20:33:10 [01] Skipping. / auto.cnf.

171015 20:33:10 [01] Skipping. / master.info.

171015 20:33:10 [01] Skipping. / relay-log.info.

171015 20:33:10 [01] Skipping. / mysql-bin.000003.

171015 20:33:10 [01] Skipping. / mysql-bin.000005.

171015 20:33:10 [01] Skipping. / chenfeng/db.opt.

171015 20:33:10 [01] Copying. / chenfeng/duansf.frm to / data/backup/2017-10-15 20-33-07/chenfeng/duansf.frm

171015 20:33:10 [01]... done

171015 20:33:10 [01] Skipping. / ib_buffer_pool.

171015 20:33:10 [01] Skipping. / mysql-bin.index.

171015 20:33:10 Finished backing up non-InnoDB tables and files

171015 20:33:10 [00] Writing / data/backup/2017-10-15 20-33-07/xtrabackup_slave_info

171015 20:33:10 [00]... done

171015 20:33:10 [00] Writing / data/backup/2017-10-15 20-33-07/xtrabackup_binlog_info

171015 20:33:10 [00]... done

171015 20:33:10 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...

Xtrabackup: The latest check point (for incremental): '233975907'

Xtrabackup: Stopping log copying thread.

.171015 20:33:10 > > log scanned up to (233975916)

171015 20:33:10 Executing UNLOCK TABLES

171015 20:33:10 All tables unlocked

Starting slave SQL thread

171015 20:33:10 [00] Copying ib_buffer_pool to / data/backup/2017-10-15 20-33-07/ib_buffer_pool

171015 20:33:10 [00]... done

171015 20:33:10 Backup created in directory'/ data/backup/2017-10-15 Backup created in directory'20-33-07

MySQL binlog position: filename 'mysql-bin.000008', position' 154'

MySQL slave binlog position:

171015 20:33:10 [00] Writing / data/backup/2017-10-15 20-33-07/backup-my.cnf

171015 20:33:10 [00]... done

171015 20:33:10 [00] Writing / data/backup/2017-10-15 20-33-07/xtrabackup_info

171015 20:33:10 [00]... done

Xtrabackup: Transaction log of lsn (233975907) to (233975916) was copied.

171015 20:33:10 completed OK!

[root@localhost backup] #

Some parameters of innobackup explain:

-- slave-info saves the binary log file name and offset of master to the xtrabackup_slave_info file

-- slave-info, backup from the library, plus-- slave-info backup directory will generate one more xtrabackup_slave_info file

The main log file and the offset are saved here, and the content of the file is similar to:

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=826270

-- safe-slave-backup pauses the SQL thread of slave and starts it after the backup ends.

-- include=REGEXP

The encapsulation of the xtrabackup parameter, tables, also supports ibbackup. Backup contains library tables, for example:-- include= "test.*", which means backing up all tables in the test library.

If you need a full backup, omit this parameter; if you need to back up the two tables under the test library: test1 and test2, write:-- include= "test.test1 | test.test2". You can also use wildcards, such as:-- include= "test.test*".

In this example, only the duansf table under the chenfeng library is backed up, which can be written as follows-- include=chenfeng.duansf

Since only the duansf table of the chenfeng library was backed up, we only saw the chenfeng folder in the generated time directory 2017-10-15, 20-33-07.

[root@localhost 2017-10-15 20-33-07] # ll / data/backup

The total dosage is 12316

-rw-r-. 1 root root 424 October 15 20:33 backup-my.cnf

-rw-r-. 1 root root 593 October 15 20:33 ib_buffer_pool

-rw-r-. 1 root root 12582912 October 15 20:33 ibdata1

Drwxr-x---. 2 root root 42 October 15 20:33 chenfeng

-rw-r-. 1 root root 21 October 15 20:33 xtrabackup_binlog_info

-rw-r-. 1 root root 117 October 15 20:33 xtrabackup_checkpoints

-rw-r-. 1 root root 573 October 15 20:33 xtrabackup_info

-rw-r-. 1 root root 2560 October 15 20:33 xtrabackup_logfile

-rw-r-. 1 root root 76 October 15 20:33 xtrabackup_slave_info

[root@localhost 2017-10-15 20-33-07] # cd chenfeng

[root@localhost chenfeng] # ll

Total dosage 108

-rw-r-. 1 root root 8586 October 15 20:33 duansf.frm

-rw-r-. 1 root root 98304 October 15 20:33 duansf.ibd

Package the chenfeng directory into the / data/backup/bak directory:

[root@localhost 2017-10-15 20-33-07] # tar czvf chenfeng.tar.gz chenfeng

[root@localhost 2017-10-15 20-33-07] # mv chenfeng.tar.gz / data/backup/bak/

Decompress for recovery:

When recovering data, you have to go through two steps: prepare (recovery) and restore.

Prepare Export Table steps:

[root@localhost backup] # innobackupex-- defaults-file=/etc/my.cnf-- user=root-- password=dsf0723-S / tmp/mysql.sock-- apply-log-- export / data/backup/2017-10-15 20-33-07

171015 20:49:32 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.

At the end of a successful apply-log run innobackupex

Prints "completed OK!".

Innobackupex version 2.4.8 based on MySQL server 5.7.13 Linux (x86 / 64) (revision id: 97330f7)

Xtrabackup: auto-enabling-innodb-file-per-table due to the-export option

Xtrabackup: cd to / data/backup/2017-10-15 20-33-07 /

Xtrabackup: This target seems to be not prepared yet.

InnoDB: Number of pools: 1

Xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn= (233975907)

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: 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)

InnoDB: PUNCH HOLE support available

InnoDB: Mutexes and rw_locks use GCC atomic builtins

InnoDB: Uses event mutexes

InnoDB: GCC builtin _ _ atomic_thread_fence () is used for memory barrier

InnoDB: Compressed tables use zlib 1.2.7

InnoDB: Number of pools: 1

InnoDB: Using CPU crc32 instructions

InnoDB: Initializing buffer pool, total size = 100m, instances = 1, chunk size = 100m

InnoDB: Completed initialization of buffer pool

InnoDB: page_cleaner coordinator priority:-20

InnoDB: Highest supported file format is Barracuda.

InnoDB: Log scan progressed past the checkpoint lsn 233975907

InnoDB: Doing recovery: scanned up to log sequence number 233975916

InnoDB: Database was not shutdown normally!

InnoDB: Starting crash recovery.

InnoDB: xtrabackup: Last MySQL binlog file position 68420531, file name mysql-bin.000002

InnoDB: Creating shared tablespace for temporary tables

InnoDB: Setting file'. / ibtmp1' size to 12 MB. Physically writing the file full; Please wait...

InnoDB: File'. / ibtmp1' size is now 12 MB.

InnoDB: 96 redo rollback segment (s) found. 1 redo rollback segment (s) are active.

InnoDB: 32 non-redo rollback segment (s) are active.

InnoDB: Waiting for purge to start

InnoDB: Failed to find tablespace for table `mysql`.`innodb _ index_ stats` in the cache. Attempting to load the tablespace with space id 56

InnoDB: Operating system error number 2 in a file operation.

InnoDB: The error means the system cannot find the path specified.

InnoDB: Cannot open datafile for read-only:'. / mysql/innodb_index_stats.ibd' OS error: 71

InnoDB: Operating system error number 2 in a file operation.

InnoDB: The error means the system cannot find the path specified.

InnoDB: 5.7.13 started; log sequence number 233975916

Xtrabackup: export option is specified.

InnoDB: Could not find a valid tablespace file for `mysql/innodb_index_ stats`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

InnoDB: Failed to find tablespace for table `mysql`.`innodb _ table_ stats` in the cache. Attempting to load the tablespace with space id 57

InnoDB: Operating system error number 2 in a file operation.

InnoDB: The error means the system cannot find the path specified.

InnoDB: Cannot open datafile for read-only:'. / mysql/innodb_table_stats.ibd' OS error: 71

InnoDB: Operating system error number 2 in a file operation.

InnoDB: The error means the system cannot find the path specified.

InnoDB: Could not find a valid tablespace file for `mysql/innodb_table_ stats`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

Xtrabackup: export metadata of table 'chenfeng/duansf' to file `. / chenfeng/ duansf.exp` (1 indexes)

Xtrabackup: name=GEN_CLUST_INDEX, id.low=91, page=3

InnoDB: xtrabackup: Last MySQL binlog file position 68420531, file name mysql-bin.000002

Xtrabackup: starting shutdown with innodb_fast_shutdown = 0

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 233975935

InnoDB: 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

InnoDB: PUNCH HOLE support available

InnoDB: Mutexes and rw_locks use GCC atomic builtins

InnoDB: Uses event mutexes

InnoDB: GCC builtin _ _ atomic_thread_fence () is used for memory barrier

InnoDB: Compressed tables use zlib 1.2.7

InnoDB: Number of pools: 1

InnoDB: Using CPU crc32 instructions

InnoDB: Initializing buffer pool, total size = 100m, instances = 1, chunk size = 100m

InnoDB: Completed initialization of buffer pool

InnoDB: page_cleaner coordinator priority:-20

InnoDB: Setting logfile. / ib_logfile101 size to 48 MB

InnoDB: Setting logfile. / ib_logfile1 size to 48 MB

InnoDB: Renaming logfile. / ib_logfile101 to. / ib_logfile0

InnoDB: New log files created, LSN=233975935

InnoDB: Highest supported file format is Barracuda.

InnoDB: Log scan progressed past the checkpoint lsn 233976332

InnoDB: Doing recovery: scanned up to log sequence number 233976341

InnoDB: Database was not shutdown normally!

InnoDB: Starting crash recovery.

InnoDB: xtrabackup: Last MySQL binlog file position 68420531, file name mysql-bin.000002

InnoDB: Removed temporary tablespace data file: "ibtmp1"