如何用Percona XtraBackup進行MySQL從庫的單表備份和恢復
環境說明：
主庫:192.168.0.1
從庫1:192.168.0.2
從庫2:192.168.0.3
備份工具 : Percona xtrabackup version 2.4.8 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 97330f7)
在主庫上建立chenfeng庫:
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,'duansf');
Query OK, 1 row affected (0.01 sec)
mysql> insert into duansf values(2,'duansf');
Query OK, 1 row affected (0.01 sec)
只備份chenfeng庫的duansf表:
[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_20-33-07/'
MySQL binlog position: filename 'mysql-bin.000008', position '154'
MySQL slave binlog position:
m
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]#
innobackup部分引數解釋:
--slave-info會將master的binary log檔名和偏移量儲存到xtrabackup_slave_info檔案中
--slave-info,備份從庫, 加上 --slave-info 備份目錄下會多生成一個 xtrabackup_slave_info 檔案,
這裡會儲存主日誌檔案以及偏移, 檔案內容類似於:
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=826270;
--safe-slave-backup會暫停slave的sql執行緒，待備份結束後再啟動
--include=REGEXP
對xtrabackup引數--tables的封裝，也支援ibbackup。備份包含的庫表，例如：--include="test.*"，意思是要備份test庫中所有的表。
如果需要全備份，則省略這個引數；如果需要備份test庫下的2個表：test1和test2,則寫成：--include="test.test1|test.test2"。也可以使用萬用字元，如：--include="test.test*"。
本例中只備份chenfeng庫下的duansf表，可以這麼寫--include=chenfeng.duansf
由於只備份了chenfeng庫的duansf表，所以我們在生成的時間目錄裡2017-10-15_20-33-07只看到了chenfeng資料夾.
[root@localhost 2017-10-15_20-33-07]# ll /data/backup
總用量 12316
-rw-r-----. 1 root root 424 10月 15 20:33 backup-my.cnf
-rw-r-----. 1 root root 593 10月 15 20:33 ib_buffer_pool
-rw-r-----. 1 root root 12582912 10月 15 20:33 ibdata1
drwxr-x---. 2 root root 42 10月 15 20:33 chenfeng
-rw-r-----. 1 root root 21 10月 15 20:33 xtrabackup_binlog_info
-rw-r-----. 1 root root 117 10月 15 20:33 xtrabackup_checkpoints
-rw-r-----. 1 root root 573 10月 15 20:33 xtrabackup_info
-rw-r-----. 1 root root 2560 10月 15 20:33 xtrabackup_logfile
-rw-r-----. 1 root root 76 10月 15 20:33 xtrabackup_slave_info
[root@localhost 2017-10-15_20-33-07]# cd chenfeng
[root@localhost chenfeng]# ll
總用量 108
-rw-r-----. 1 root root 8586 10月 15 20:33 duansf.frm
-rw-r-----. 1 root root 98304 10月 15 20:33 duansf.ibd
把chenfeng目錄打包放到/data/backup/bak目錄下:
[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/
解壓縮做恢復用:
恢復資料的時候，要經過prepare（recovery）和restore兩個步驟，
prepare匯出表步驟:
[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 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 233975916 (0%)
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 log file ./ib_logfile101 size to 48 MB
InnoDB: Setting log file ./ib_logfile1 size to 48 MB
InnoDB: Renaming log file ./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 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 233976341 (0%)
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"
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: 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.
InnoDB: 5.7.13 started; log sequence number 233976341
xtrabackup: starting shutdown with innodb_fast_shutdown = 0
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 233976360
171015 20:49:37 completed OK!
[root@localhost backup]#
在從庫2上刪除duansf表:
mysql> use chenfeng
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> show tables;
+-----------------+
| Tables_in_chenfeng |
+-----------------+
| duansf |
+-----------------+
1 row in set (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_chenfeng |
+-----------------+
| duansf |
+-----------------+
1 row in set (0.00 sec)
mysql> delete from duansf;
Query OK, 2 rows affected (0.06 sec)
mysql> select * from duansf;
Empty set (0.00 sec)
從xtrabackup備份裡恢復出duansf表資料:
刪除表：
mysql> drop table duansf;
Query OK, 0 rows affected (0.06 sec)
重建表結構:
mysql> CREATE TABLE `duansf` (
-> `id` int(11) DEFAULT NULL,
-> `name` varchar(10) DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.05 sec)
解除安裝表空間:
mysql> ALTER TABLE chenfeng.duansf DISCARD TABLESPACE;
Query OK, 0 rows affected (0.01 sec)
從備份裡恢復:
[root@localhost chenfeng]# cp /data/backup/2017-10-15_20-33-07/chenfeng/{duansf.ibd,duansf.cfg,duansf.frm} /usr/local/mysql/data/chenfeng
[root@localhost chenfeng]# ll
總用量 116
-rw-r-----. 1 mysql mysql 65 10月 15 19:00 db.opt
-rw-r--r--. 1 root root 426 10月 15 21:13 duansf.cfg
-rw-r-----. 1 mysql mysql 8586 10月 15 21:06 duansf.frm
-rw-r-----. 1 root root 98304 10月 15 21:13 duansf.ibd
root@localhost data]# chown -R mysql:mysql /usr/local/mysql/data/chenfeng
裝載表空間：
mysql> ALTER TABLE chenfeng.duansf import TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.20 sec)
檢視duansf表資料:
mysql> select * from duansf;
+------+--------+
| id | name |
+------+--------+
| 1 | duansf |
| 2 | duansf |
+------+--------+
2 rows in set (0.00 sec)
資料已恢復.
主庫:192.168.0.1
從庫1:192.168.0.2
從庫2:192.168.0.3
備份工具 : Percona xtrabackup version 2.4.8 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 97330f7)
在主庫上建立chenfeng庫:
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,'duansf');
Query OK, 1 row affected (0.01 sec)
mysql> insert into duansf values(2,'duansf');
Query OK, 1 row affected (0.01 sec)
只備份chenfeng庫的duansf表:
[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_20-33-07/'
MySQL binlog position: filename 'mysql-bin.000008', position '154'
MySQL slave binlog position:
m
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]#
innobackup部分引數解釋:
--slave-info會將master的binary log檔名和偏移量儲存到xtrabackup_slave_info檔案中
--slave-info,備份從庫, 加上 --slave-info 備份目錄下會多生成一個 xtrabackup_slave_info 檔案,
這裡會儲存主日誌檔案以及偏移, 檔案內容類似於:
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=826270;
--safe-slave-backup會暫停slave的sql執行緒，待備份結束後再啟動
--include=REGEXP
對xtrabackup引數--tables的封裝，也支援ibbackup。備份包含的庫表，例如：--include="test.*"，意思是要備份test庫中所有的表。
如果需要全備份，則省略這個引數；如果需要備份test庫下的2個表：test1和test2,則寫成：--include="test.test1|test.test2"。也可以使用萬用字元，如：--include="test.test*"。
本例中只備份chenfeng庫下的duansf表，可以這麼寫--include=chenfeng.duansf
由於只備份了chenfeng庫的duansf表，所以我們在生成的時間目錄裡2017-10-15_20-33-07只看到了chenfeng資料夾.
[root@localhost 2017-10-15_20-33-07]# ll /data/backup
總用量 12316
-rw-r-----. 1 root root 424 10月 15 20:33 backup-my.cnf
-rw-r-----. 1 root root 593 10月 15 20:33 ib_buffer_pool
-rw-r-----. 1 root root 12582912 10月 15 20:33 ibdata1
drwxr-x---. 2 root root 42 10月 15 20:33 chenfeng
-rw-r-----. 1 root root 21 10月 15 20:33 xtrabackup_binlog_info
-rw-r-----. 1 root root 117 10月 15 20:33 xtrabackup_checkpoints
-rw-r-----. 1 root root 573 10月 15 20:33 xtrabackup_info
-rw-r-----. 1 root root 2560 10月 15 20:33 xtrabackup_logfile
-rw-r-----. 1 root root 76 10月 15 20:33 xtrabackup_slave_info
[root@localhost 2017-10-15_20-33-07]# cd chenfeng
[root@localhost chenfeng]# ll
總用量 108
-rw-r-----. 1 root root 8586 10月 15 20:33 duansf.frm
-rw-r-----. 1 root root 98304 10月 15 20:33 duansf.ibd
把chenfeng目錄打包放到/data/backup/bak目錄下:
[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/
解壓縮做恢復用:
恢復資料的時候，要經過prepare（recovery）和restore兩個步驟，
prepare匯出表步驟:
[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 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 233975916 (0%)
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 log file ./ib_logfile101 size to 48 MB
InnoDB: Setting log file ./ib_logfile1 size to 48 MB
InnoDB: Renaming log file ./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 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 233976341 (0%)
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"
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: 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.
InnoDB: 5.7.13 started; log sequence number 233976341
xtrabackup: starting shutdown with innodb_fast_shutdown = 0
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 233976360
171015 20:49:37 completed OK!
[root@localhost backup]#
在從庫2上刪除duansf表:
mysql> use chenfeng
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> show tables;
+-----------------+
| Tables_in_chenfeng |
+-----------------+
| duansf |
+-----------------+
1 row in set (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_chenfeng |
+-----------------+
| duansf |
+-----------------+
1 row in set (0.00 sec)
mysql> delete from duansf;
Query OK, 2 rows affected (0.06 sec)
mysql> select * from duansf;
Empty set (0.00 sec)
從xtrabackup備份裡恢復出duansf表資料:
刪除表：
mysql> drop table duansf;
Query OK, 0 rows affected (0.06 sec)
重建表結構:
mysql> CREATE TABLE `duansf` (
-> `id` int(11) DEFAULT NULL,
-> `name` varchar(10) DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.05 sec)
解除安裝表空間:
mysql> ALTER TABLE chenfeng.duansf DISCARD TABLESPACE;
Query OK, 0 rows affected (0.01 sec)
從備份裡恢復:
[root@localhost chenfeng]# cp /data/backup/2017-10-15_20-33-07/chenfeng/{duansf.ibd,duansf.cfg,duansf.frm} /usr/local/mysql/data/chenfeng
[root@localhost chenfeng]# ll
總用量 116
-rw-r-----. 1 mysql mysql 65 10月 15 19:00 db.opt
-rw-r--r--. 1 root root 426 10月 15 21:13 duansf.cfg
-rw-r-----. 1 mysql mysql 8586 10月 15 21:06 duansf.frm
-rw-r-----. 1 root root 98304 10月 15 21:13 duansf.ibd
root@localhost data]# chown -R mysql:mysql /usr/local/mysql/data/chenfeng
裝載表空間：
mysql> ALTER TABLE chenfeng.duansf import TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.20 sec)
檢視duansf表資料:
mysql> select * from duansf;
+------+--------+
| id | name |
+------+--------+
| 1 | duansf |
| 2 | duansf |
+------+--------+
2 rows in set (0.00 sec)
資料已恢復.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15498/viewspace-2146003/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用xtrabackup對mysql進行備份和恢復MySql
- Percona XtraBackup 2.4 xtrabackup全量、增量備份恢復流程
- 【Mysql】xtrabackup 備份和恢復測試MySql
- Percona XtraBackup 實現全備&增量備份與恢復
- mysql備份和恢復測試(一）--xtrabackupMySql
- MySQL備份與恢復——基於Xtrabackup物理備份恢復MySql
- Percona Xtrabackup 快速備份 MySQLMySql
- MySQL · 物理備份 · Percona XtraBackup 備份原理MySql
- 基於percona xtrabackup 2.4.14的增量備份恢復還原mysql 5.6MySql
- MySQL 5.6 xtrabackup 全量和增量的備份和恢復MySql
- Percona XtraBackup 2.4 innobackupex全量、增量備份恢復流程
- 【MySQL】Xtrabackup備份及恢復指令碼MySql指令碼
- Xtrabackup備份恢復原理
- 從MySQL全庫備份中恢復某個庫和某張表MySql
- 使用mysqldump對mysql進行備份和恢復MySql
- 使用 xtrabackup 進行MySQL資料庫物理備份MySql資料庫
- 【Xtrabackup】Xtrabackup全備、增量備份及恢復示例
- 【備份恢復】從備份恢復資料庫資料庫
- MySQL運維實戰之備份和恢復（8.1）xtrabackup全量備份MySql運維
- Xtrabackup實現資料庫備份和災難恢復資料庫
- mysqldump 備份單張表和恢復MySql
- MySQL備份和恢復資料表的方法MySql
- 【MySQL】MySQL備份和恢復MySql
- Mysql備份和恢復MySql
- xtrabackup和innobackuppex的安裝和備份(含增量備份)已經恢復
- mysql通過percona xtrabackup全備和mysql binlog實現基於時間點的資料庫恢復MySql資料庫
- 如何從MySQL全庫備份的dump檔案中恢復某張表和表結構MySql
- windwos server 路由備份和恢復 路由表備份和恢復Server路由
- MySQL 備份和恢復 一MySql
- Percona xtrabackup2.4版本遠端備份和搭建從庫的方法介紹
- 對二所ATC的MySQL進行 備份與恢復MySql
- 使用Mysqldump備份和恢復MySQL資料庫MySql資料庫
- 第5章：從開啟的資料庫備份與恢復之從開啟的資料庫備份中進行不完全恢復資料庫
- 使用NetBackup進行oracle備份和恢復Oracle
- Xtrabackup之innobackupex備份恢復詳解薦
- Mysql備份恢復MySql
- mysql 備份恢復MySql
- MySQL：xtrabackup備份MySql