MySQL物理物理备份与还原工具xtraBackup

概述（一）xtraBackup简介 xtraBackup是Percona公司开发的一款MySQL数据库备份软件，在备份模式中属于物理备份。其显著特点是开源、免费、备份执行过程中不会阻塞事物、备份可压缩、支

（一）xtraBackup简介

xtraBackup是Percona公司开发的一款MysqL数据库备份软件，在备份模式中属于物理备份。其显著特点是开源、免费、备份执行过程中不会阻塞事物、备份可压缩、支持全备和增量备份。

 

（二）下载安装xtraBackup工具

xtraBackup并没有随着MysqL安装包一起下载，需要单独到percona官网下载。注意，最新版的Percona XtraBackup 8.0不适用于MysqL 8.0以前的版本，我的数据库是MysqL5.7，因此下载2.4版本。

（2.1）离线安装

大部分公司服务器都不连接外网，离线安装是使用最多的方式。xtraBackup rpm包下载地址为：https://www.percona.com/downloads/Percona-XtraBackup-2.4/LATEST/。以下过程是在断网情况下执行的。

STEP1：将rpm安装包传到服务器上

[root@MysqLserver ~]# ls -l |grep percona-rw-r--r--  1 root root   7935884 Feb 11 16:19 percona-xtrabackup-24-2.4.18-1.el7.x86_64.rpm

STEP2：安装rpm包，这里不要使用rpm -ivh去安装，因为该安装包需要其他依赖包，直接使用yum解决依赖问题

yum install -y percona-xtrabackup-STEP3：确认安装结果

which xtrabackup/usr/bin/xtrabackup[root@MysqLserver ~]# innobackupex/usr/bin/innobackupex

 

（2.2）在线安装

如果机器联网，可以直接使用yum源安装，这里以centos7服务器为例。

STEP1:安装xtraBackup的yum源

[root@MysqLserver /root]# install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm

STEP2：确认xtrabackup安装包（可跳过）

yum List | percona...percona-xtrabackup-22-deBUGinfo.x86_64 2.2.13-1.el7 percona-release-x86_64percona-xtrabackup-24.x86_64 24-deBUGinfo.x86_64 80.x86_64 8.0.9-1.el7 percona-release-80-deBUGinfo.x86_64 x86_64percona-xtrabackup-deBUGinfo.x86_64 2.3.10-x86_64percona-xtrabackup-test.x86_64 x86_64percona-xtrabackup-test-22.x86_64 24.x86_64 80.x86_64 x86_64percona-zabbix-templates.noarch 1.1.8-1 percona-release-noarch...

STEP3：安装xtraBackup

install -y percona-xtrabackup-24

STEP4：确认安装结果

xtrabackup[root@MysqLserver /root]# innobackupex/usr/bin/innobackupex

 

xtraBackup包含2个主要的工具：xtrabackup和innobackupex。两者区别如下：

xtrbackup只能备份innodb和xTradb引擎的表，对于其他引擎的表则无能为力，包括MYISAM。innobackupex是一个封装了xtrbackup的Perl脚本，除了支持xtrabackup工具可以备份的表以外，还支持MYISAM、CSV、memory(仅表结构)等，比xtrabackup强大。

因为innobackupex工具更为强大，所以后续直接使用innobackupex来执行备份和恢复。

 

（三）使用innobackupex备份数据库

innobackupex支持全备和增量备份，对于该工具的用法，可以直接使用“--help”命令查看

[root@MysqLserver ~]# innobackupex --help

 

（3.1）使用innobackupex来执行全备

执行全备的命令如下：

[root@MysqLserver ~]# innobackupex --user=root --password='123456' --default-file=/etc/my.cnf' /backup 

 参数信息：

--user                  ：连接数据库使用的用户名

--password          ：连接数据库使用的密码

--default-file         ：指出MysqL的参数配置文件路径

[backup_dir]        ：备份集存放地址

 

innobackupex备份执行过程如下：

[root@MysqLserver ~]# innobackupex --user=root --password=' /backup xtrabackup: recognized server arguments: --datadir=/usr/local/MysqL/data xtrabackup: recognized clIEnt arguments: 200211 42:47 innobackupex: Starting the backup operationimportant: Please check that the backup run completes successfully.           At the end of a successful backup run innobackupex           prints "completed OK!".47  version_check Connecting to MysqL server with DSN dbi:MysqL:;MysqL_read_default_group=xtrabackup;MysqL_socket=/tmp/MysqL.soc' as root'  (using password: YES).  version_check Connected to MysqL server  version_check Executing a version check against the server...  version_check Done.47 Connecting to MysqL server host: localhost,user: root,password: set,port: not set,socket: /tmp/MysqL.socUsing server version 5.7.27innobackupex version 18 based on MysqL server 26 linux (x86_64) (revision ID: 29b4ca5)xtrabackup: uses posix_fadvise().xtrabackup: cd to /usr/local/MysqL/dataxtrabackup: open files limit requested 0,set to 1024xtrabackup: using the following InnoDB configuration:xtrabackup:   innodb_data_home_dir = .xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextendxtrabackup:   innodb_log_group_home_dir = ./xtrabackup:   innodb_log_files_in_group = 2xtrabackup:   innodb_log_file_size = 50331648InnoDB: Number of pools: 147 >> log scanned up to (2625691)xtrabackup: Generating a List of tablespacesInnoDB: Allocated tablespace ID 2 for MysqL/plugin,old maximum was 047 [01] copying ./ibdata1 to /backup/2020-02-11_16-42-47/ibdata148 [01]        ...done01] copying ./MysqL/plugin.ibd to /backup/47/MysqL/plugin.ibd01] copying ./MysqL/servers.ibd to /backup/servers.ibd01] copying ./MysqL/help_topic.ibd to /backup/help_topic.ibd01] copying ./MysqL/help_category.ibd to /backup/help_category.ibd01] copying ./MysqL/help_relation.ibd to /backup/help_relation.ibd01] copying ./MysqL/help_keyword.ibd to /backup/help_keyword.ibd01] copying ./MysqL/time_zone_name.ibd to /backup/time_zone_name.ibd01] copying ./MysqL/time_zone.ibd to /backup/time_zone.ibd01] copying ./MysqL/time_zone_Transition.ibd to /backup/time_zone_Transition.ibd01] copying ./MysqL/time_zone_Transition_type.ibd to /backup/time_zone_Transition_type.ibd01] copying ./MysqL/time_zone_leap_second.ibd to /backup/time_zone_leap_second.ibd01] copying ./MysqL/innodb_table_stats.ibd to /backup/innodb_table_stats.ibd01] copying ./MysqL/innodb_index_stats.ibd to /backup/innodb_index_stats.ibd01] copying ./MysqL/slave_relay_log_info.ibd to /backup/slave_relay_log_info.ibd01] copying ./MysqL/slave_master_info.ibd to /backup/slave_master_info.ibd01] copying ./MysqL/slave_worker_info.ibd to /backup/slave_worker_info.ibd01] copying ./MysqL/gtID_executed.ibd to /backup/gtID_executed.ibd01] copying ./MysqL/server_cost.ibd to /backup/server_cost.ibd01] copying ./MysqL/engine_cost.ibd to /backup/engine_cost.ibd01] copying ./sys/sys_config.ibd to /backup/47/sys/sys_config.ibd01] copying ./test_database/test01.ibd to /backup/47/test_database/test01.ibd48 >> log scanned up to ()48 Executing FLUSH NO_WRITE_TO_binlog tableS... Executing FLUSH tableS WITH READ LOCK...48 Starting to backup non-InnoDB tables and files01] copying ./MysqL/db.opt to /backup/db.opt01] copying ./MysqL/db.frm to /backup/db.frm01] copying ./MysqL/db.MYI to /backup/db.MYI01] copying ./MysqL/db.MYD to /backup/db.MYD01] copying ./MysqL/user.frm to /backup/user.frm01] copying ./MysqL/user.MYI to /backup/user.MYI01] copying ./MysqL/user.MYD to /backup/user.MYD01] copying ./MysqL/func.frm to /backup/func.frm01] copying ./MysqL/func.MYI to /backup/func.MYI01] copying ./MysqL/func.MYD to /backup/func.MYD01] copying ./MysqL/plugin.frm to /backup/plugin.frm01] copying ./MysqL/servers.frm to /backup/servers.frm01] copying ./MysqL/tables_priv.frm to /backup/tables_priv.frm01] copying ./MysqL/tables_priv.MYI to /backup/tables_priv.MYI01] copying ./MysqL/tables_priv.MYD to /backup/tables_priv.MYD01] copying ./MysqL/columns_priv.frm to /backup/columns_priv.frm01] copying ./MysqL/columns_priv.MYI to /backup/columns_priv.MYI01] copying ./MysqL/columns_priv.MYD to /backup/columns_priv.MYD01] copying ./MysqL/help_topic.frm to /backup/help_topic.frm01] copying ./MysqL/help_category.frm to /backup/help_category.frm01] copying ./MysqL/help_relation.frm to /backup/help_relation.frm01] copying ./MysqL/help_keyword.frm to /backup/help_keyword.frm01] copying ./MysqL/time_zone_name.frm to /backup/time_zone_name.frm01] copying ./MysqL/time_zone.frm to /backup/time_zone.frm01] copying ./MysqL/time_zone_Transition.frm to /backup/time_zone_Transition.frm01] copying ./MysqL/time_zone_Transition_type.frm to /backup/time_zone_Transition_type.frm01] copying ./MysqL/time_zone_leap_second.frm to /backup/time_zone_leap_second.frm01] copying ./MysqL/proc.frm to /backup/proc.frm01] copying ./MysqL/proc.MYI to /backup/proc.MYI01] copying ./MysqL/proc.MYD to /backup/proc.MYD01] copying ./MysqL/procs_priv.frm to /backup/procs_priv.frm01] copying ./MysqL/procs_priv.MYI to /backup/procs_priv.MYI01] copying ./MysqL/procs_priv.MYD to /backup/procs_priv.MYD01] copying ./MysqL/general_log.frm to /backup/general_log.frm01] copying ./MysqL/general_log.CSM to /backup/general_log.CSM01] copying ./MysqL/general_log.CSV to /backup/general_log.CSV01] copying ./MysqL/slow_log.frm to /backup/slow_log.frm01] copying ./MysqL/slow_log.CSM to /backup/slow_log.CSM01] copying ./MysqL/slow_log.CSV to /backup/slow_log.CSV01] copying ./MysqL/event.frm to /backup/event.frm01] copying ./MysqL/event.MYI to /backup/event.MYI01] copying ./MysqL/event.MYD to /backup/event.MYD01] copying ./MysqL/ndb_binlog_index.frm to /backup/ndb_binlog_index.frm01] copying ./MysqL/ndb_binlog_index.MYI to /backup/ndb_binlog_index.MYI01] copying ./MysqL/ndb_binlog_index.MYD to /backup/ndb_binlog_index.MYD01] copying ./MysqL/innodb_table_stats.frm to /backup/innodb_table_stats.frm01] copying ./MysqL/innodb_index_stats.frm to /backup/innodb_index_stats.frm01] copying ./MysqL/slave_relay_log_info.frm to /backup/slave_relay_log_info.frm01] copying ./MysqL/slave_master_info.frm to /backup/slave_master_info.frm01] copying ./MysqL/slave_worker_info.frm to /backup/slave_worker_info.frm01] copying ./MysqL/gtID_executed.frm to /backup/gtID_executed.frm01] copying ./MysqL/server_cost.frm to /backup/server_cost.frm01] copying ./MysqL/engine_cost.frm to /backup/engine_cost.frm01] copying ./MysqL/proxIEs_priv.frm to /backup/proxIEs_priv.frm01] copying ./MysqL/proxIEs_priv.MYI to /backup/proxIEs_priv.MYI01] copying ./MysqL/proxIEs_priv.MYD to /backup/proxIEs_priv.MYD01] copying ./sys/db.opt to /backup/01] copying ./sys/version.frm to /backup/version.frm01] copying ./sys/sys_config.frm to /backup/sys_config.frm01] copying ./sys/statements_with_full_table_scans.frm to /backup/statements_with_full_table_scans.frm01] copying ./sys/sys_config_insert_set_user.TRN to /backup/sys_config_insert_set_user.TRN01] copying ./sys/processList.frm to /backup/processList.frm01] copying ./sys/sys_config.TRG to /backup/sys_config.TRG01] copying ./sys/statements_with_sorting.frm to /backup/statements_with_sorting.frm01] copying ./sys/sys_config_update_set_user.TRN to /backup/sys_config_update_set_user.TRN01] copying ./sys/x@0024statements_with_sorting.frm to /backup/x@0024statements_with_sorting.frm01] copying ./sys/innodb_buffer_stats_by_schema.frm to /backup/innodb_buffer_stats_by_schema.frm01] copying ./sys/schema_index_statistics.frm to /backup/schema_index_statistics.frm01] copying ./sys/x@0024innodb_buffer_stats_by_schema.frm to /backup/x@0024innodb_buffer_stats_by_schema.frm01] copying ./sys/statements_with_temp_tables.frm to /backup/statements_with_temp_tables.frm01] copying ./sys/innodb_buffer_stats_by_table.frm to /backup/innodb_buffer_stats_by_table.frm01] copying ./sys/x@0024ps_schema_table_statistics_io.frm to /backup/x@0024ps_schema_table_statistics_io.frm01] copying ./sys/x@0024innodb_buffer_stats_by_table.frm to /backup/x@0024innodb_buffer_stats_by_table.frm01] copying ./sys/host_summary.frm to /backup/host_summary.frm01] copying ./sys/innodb_lock_waits.frm to /backup/innodb_lock_waits.frm01] copying ./sys/x@0024host_summary.frm to /backup/x@0024host_summary.frm01] copying ./sys/x@0024innodb_lock_waits.frm to /backup/x@0024innodb_lock_waits.frm01] copying ./sys/waits_by_user_by_latency.frm to /backup/waits_by_user_by_latency.frm01] copying ./sys/schema_object_overvIEw.frm to /backup/schema_object_overvIEw.frm01] copying ./sys/user_summary_by_file_io_type.frm to /backup/user_summary_by_file_io_type.frm01] copying ./sys/schema_auto_increment_columns.frm to /backup/schema_auto_increment_columns.frm01] copying ./sys/user_summary_by_file_io.frm to /backup/user_summary_by_file_io.frm01] copying ./sys/x@0024schema_flattened_keys.frm to /backup/x@0024schema_flattened_keys.frm01] copying ./sys/x@0024user_summary_by_file_io.frm to /backup/x@0024user_summary_by_file_io.frm01] copying ./sys/schema_redundant_indexes.frm to /backup/schema_redundant_indexes.frm01] copying ./sys/user_summary_by_statement_type.frm to /backup/user_summary_by_statement_type.frm01] copying ./sys/ps_check_lost_instrumentation.frm to /backup/ps_check_lost_instrumentation.frm01] copying ./sys/latest_file_io.frm to /backup/latest_file_io.frm01] copying ./sys/waits_by_host_by_latency.frm to /backup/waits_by_host_by_latency.frm01] copying ./sys/x@0024latest_file_io.frm to /backup/x@0024latest_file_io.frm01] copying ./sys/waits_global_by_latency.frm to /backup/waits_global_by_latency.frm01] copying ./sys/io_by_thread_by_latency.frm to /backup/io_by_thread_by_latency.frm01] copying ./sys/user_summary_by_stages.frm to /backup/user_summary_by_stages.frm01] copying ./sys/x@0024io_by_thread_by_latency.frm to /backup/x@0024io_by_thread_by_latency.frm01] copying ./sys/x@0024user_summary_by_stages.frm to /backup/x@0024user_summary_by_stages.frm01] copying ./sys/io_global_by_file_by_bytes.frm to /backup/io_global_by_file_by_bytes.frm01] copying ./sys/x@0024schema_index_statistics.frm to /backup/x@0024schema_index_statistics.frm01] copying ./sys/x@0024io_global_by_file_by_bytes.frm to /backup/x@0024io_global_by_file_by_bytes.frm01] copying ./sys/user_summary.frm to /backup/user_summary.frm01] copying ./sys/io_global_by_file_by_latency.frm to /backup/io_global_by_file_by_latency.frm01] copying ./sys/schema_table_statistics.frm to /backup/schema_table_statistics.frm01] copying ./sys/x@0024io_global_by_file_by_latency.frm to /backup/x@0024io_global_by_file_by_latency.frm01] copying ./sys/x@0024user_summary.frm to /backup/x@0024user_summary.frm01] copying ./sys/io_global_by_wait_by_bytes.frm to /backup/io_global_by_wait_by_bytes.frm01] copying ./sys/x@0024schema_table_statistics.frm to /backup/x@0024schema_table_statistics.frm01] copying ./sys/x@0024io_global_by_wait_by_bytes.frm to /backup/x@0024io_global_by_wait_by_bytes.frm01] copying ./sys/host_summary_by_file_io_type.frm to /backup/host_summary_by_file_io_type.frm01] copying ./sys/io_global_by_wait_by_latency.frm to /backup/io_global_by_wait_by_latency.frm01] copying ./sys/schema_table_statistics_with_buffer.frm to /backup/schema_table_statistics_with_buffer.frm01] copying ./sys/x@0024io_global_by_wait_by_latency.frm to /backup/x@0024io_global_by_wait_by_latency.frm01] copying ./sys/host_summary_by_file_io.frm to /backup/host_summary_by_file_io.frm01] copying ./sys/memory_by_user_by_current_bytes.frm to /backup/memory_by_user_by_current_bytes.frm01] copying ./sys/schema_table_lock_waits.frm to /backup/schema_table_lock_waits.frm01] copying ./sys/x@0024memory_by_user_by_current_bytes.frm to /backup/x@0024memory_by_user_by_current_bytes.frm01] copying ./sys/x@0024host_summary_by_file_io.frm to /backup/x@0024host_summary_by_file_io.frm01] copying ./sys/memory_by_host_by_current_bytes.frm to /backup/memory_by_host_by_current_bytes.frm01] copying ./sys/x@0024schema_table_lock_waits.frm to /backup/x@0024schema_table_lock_waits.frm01] copying ./sys/x@0024memory_by_host_by_current_bytes.frm to /backup/x@0024memory_by_host_by_current_bytes.frm01] copying ./sys/statement_analysis.frm to /backup/statement_analysis.frm01] copying ./sys/memory_by_thread_by_current_bytes.frm to /backup/memory_by_thread_by_current_bytes.frm01] copying ./sys/x@0024statement_analysis.frm to /backup/x@0024statement_analysis.frm01] copying ./sys/x@0024memory_by_thread_by_current_bytes.frm to /backup/x@0024memory_by_thread_by_current_bytes.frm01] copying ./sys/host_summary_by_statement_type.frm to /backup/host_summary_by_statement_type.frm01] copying ./sys/memory_global_by_current_bytes.frm to /backup/memory_global_by_current_bytes.frm01] copying ./sys/statements_with_errors_or_warnings.frm to /backup/statements_with_errors_or_warnings.frm01] copying ./sys/x@0024memory_global_by_current_bytes.frm to /backup/x@0024memory_global_by_current_bytes.frm01] copying ./sys/metrics.frm to /backup/metrics.frm01] copying ./sys/memory_global_total.frm to /backup/memory_global_total.frm01] copying ./sys/host_summary_by_stages.frm to /backup/host_summary_by_stages.frm01] copying ./sys/x@0024memory_global_total.frm to /backup/x@0024memory_global_total.frm01] copying ./sys/session.frm to /backup/session.frm01] copying ./sys/x@0024schema_table_statistics_with_buffer.frm to /backup/x@0024schema_table_statistics_with_buffer.frm01] copying ./sys/x@0024statements_with_errors_or_warnings.frm to /backup/x@0024statements_with_errors_or_warnings.frm01] copying ./sys/schema_tables_with_full_table_scans.frm to /backup/schema_tables_with_full_table_scans.frm01] copying ./sys/schema_unused_indexes.frm to /backup/schema_unused_indexes.frm01] copying ./sys/x@0024schema_tables_with_full_table_scans.frm to /backup/x@0024schema_tables_with_full_table_scans.frm01] copying ./sys/x@0024host_summary_by_stages.frm to /backup/x@0024host_summary_by_stages.frm01] copying ./sys/x@0024statements_with_full_table_scans.frm to /backup/x@0024statements_with_full_table_scans.frm01] copying ./sys/x@0024ps_digest_avg_latency_distribution.frm to /backup/x@0024ps_digest_avg_latency_distribution.frm01] copying ./sys/x@0024ps_digest_95th_percentile_by_avg_us.frm to /backup/x@0024ps_digest_95th_percentile_by_avg_us.frm01] copying ./sys/statements_with_runtimes_in_95th_percentile.frm to /backup/statements_with_runtimes_in_95th_percentile.frm01] copying ./sys/x@0024statements_with_runtimes_in_95th_percentile.frm to /backup/x@0024statements_with_runtimes_in_95th_percentile.frm01] copying ./sys/x@0024statements_with_temp_tables.frm to /backup/x@0024statements_with_temp_tables.frm01] copying ./sys/x@0024user_summary_by_file_io_type.frm to /backup/x@0024user_summary_by_file_io_type.frm01] copying ./sys/x@0024user_summary_by_statement_type.frm to /backup/x@0024user_summary_by_statement_type.frm01] copying ./sys/user_summary_by_statement_latency.frm to /backup/user_summary_by_statement_latency.frm01] copying ./sys/x@0024user_summary_by_statement_latency.frm to /backup/x@0024user_summary_by_statement_latency.frm01] copying ./sys/x@0024host_summary_by_file_io_type.frm to /backup/x@0024host_summary_by_file_io_type.frm01] copying ./sys/x@0024host_summary_by_statement_type.frm to /backup/x@0024host_summary_by_statement_type.frm01] copying ./sys/host_summary_by_statement_latency.frm to /backup/host_summary_by_statement_latency.frm01] copying ./sys/x@0024host_summary_by_statement_latency.frm to /backup/x@0024host_summary_by_statement_latency.frm01] copying ./sys/wait_classes_global_by_avg_latency.frm to /backup/wait_classes_global_by_avg_latency.frm01] copying ./sys/x@0024wait_classes_global_by_avg_latency.frm to /backup/x@0024wait_classes_global_by_avg_latency.frm01] copying ./sys/wait_classes_global_by_latency.frm to /backup/wait_classes_global_by_latency.frm01] copying ./sys/x@0024wait_classes_global_by_latency.frm to /backup/x@0024wait_classes_global_by_latency.frm01] copying ./sys/x@0024waits_by_user_by_latency.frm to /backup/x@0024waits_by_user_by_latency.frm01] copying ./sys/x@0024waits_by_host_by_latency.frm to /backup/x@0024waits_by_host_by_latency.frm01] copying ./sys/x@0024waits_global_by_latency.frm to /backup/x@0024waits_global_by_latency.frm01] copying ./sys/[email protected] to /backup/[email protected]01] copying ./sys/[email protected] to /backup/[email protected]01] copying ./sys/session_ssl_status.frm to /backup/session_ssl_status.frm01] copying ./test_database/db.opt to /backup/49 [01] copying ./test_database/test01.frm to /backup/test01.frm01] copying ./performance_schema/db.opt to /backup/47/performance_schema/01] copying ./performance_schema/cond_instances.frm to /backup/cond_instances.frm01] copying ./performance_schema/events_waits_current.frm to /backup/events_waits_current.frm01] copying ./performance_schema/events_waits_history.frm to /backup/events_waits_history.frm01] copying ./performance_schema/events_waits_history_long.frm to /backup/events_waits_history_long.frm01] copying ./performance_schema/events_waits_summary_by_instance.frm to /backup/events_waits_summary_by_instance.frm01] copying ./performance_schema/events_waits_summary_by_host_by_event_name.frm to /backup/events_waits_summary_by_host_by_event_name.frm01] copying ./performance_schema/events_waits_summary_by_user_by_event_name.frm to /backup/events_waits_summary_by_user_by_event_name.frm01] copying ./performance_schema/events_waits_summary_by_account_by_event_name.frm to /backup/events_waits_summary_by_account_by_event_name.frm01] copying ./performance_schema/events_waits_summary_by_thread_by_event_name.frm to /backup/events_waits_summary_by_thread_by_event_name.frm01] copying ./performance_schema/events_waits_summary_global_by_event_name.frm to /backup/events_waits_summary_global_by_event_name.frm01] copying ./performance_schema/file_instances.frm to /backup/file_instances.frm01] copying ./performance_schema/file_summary_by_event_name.frm to /backup/file_summary_by_event_name.frm01] copying ./performance_schema/file_summary_by_instance.frm to /backup/file_summary_by_instance.frm01] copying ./performance_schema/socket_instances.frm to /backup/socket_instances.frm01] copying ./performance_schema/socket_summary_by_instance.frm to /backup/socket_summary_by_instance.frm01] copying ./performance_schema/socket_summary_by_event_name.frm to /backup/socket_summary_by_event_name.frm01] copying ./performance_schema/host_cache.frm to /backup/host_cache.frm01] copying ./performance_schema/mutex_instances.frm to /backup/mutex_instances.frm01] copying ./performance_schema/objects_summary_global_by_type.frm to /backup/objects_summary_global_by_type.frm01] copying ./performance_schema/performance_timers.frm to /backup/performance_timers.frm01] copying ./performance_schema/rwlock_instances.frm to /backup/rwlock_instances.frm01] copying ./performance_schema/setup_actors.frm to /backup/setup_actors.frm01] copying ./performance_schema/setup_consumers.frm to /backup/setup_consumers.frm01] copying ./performance_schema/setup_instruments.frm to /backup/setup_instruments.frm01] copying ./performance_schema/setup_objects.frm to /backup/setup_objects.frm01] copying ./performance_schema/setup_timers.frm to /backup/setup_timers.frm01] copying ./performance_schema/table_io_waits_summary_by_index_usage.frm to /backup/table_io_waits_summary_by_index_usage.frm01] copying ./performance_schema/table_io_waits_summary_by_table.frm to /backup/table_io_waits_summary_by_table.frm01] copying ./performance_schema/table_lock_waits_summary_by_table.frm to /backup/table_lock_waits_summary_by_table.frm01] copying ./performance_schema/threads.frm to /backup/threads.frm01] copying ./performance_schema/events_stages_current.frm to /backup/events_stages_current.frm01] copying ./performance_schema/events_stages_history.frm to /backup/events_stages_history.frm01] copying ./performance_schema/events_stages_history_long.frm to /backup/events_stages_history_long.frm01] copying ./performance_schema/events_stages_summary_by_thread_by_event_name.frm to /backup/events_stages_summary_by_thread_by_event_name.frm01] copying ./performance_schema/events_stages_summary_by_host_by_event_name.frm to /backup/events_stages_summary_by_host_by_event_name.frm01] copying ./performance_schema/events_stages_summary_by_user_by_event_name.frm to /backup/events_stages_summary_by_user_by_event_name.frm01] copying ./performance_schema/events_stages_summary_by_account_by_event_name.frm to /backup/events_stages_summary_by_account_by_event_name.frm01] copying ./performance_schema/events_stages_summary_global_by_event_name.frm to /backup/events_stages_summary_global_by_event_name.frm01] copying ./performance_schema/events_statements_current.frm to /backup/events_statements_current.frm01] copying ./performance_schema/events_statements_history.frm to /backup/events_statements_history.frm01] copying ./performance_schema/events_statements_history_long.frm to /backup/events_statements_history_long.frm01] copying ./performance_schema/events_statements_summary_by_thread_by_event_name.frm to /backup/events_statements_summary_by_thread_by_event_name.frm01] copying ./performance_schema/events_statements_summary_by_host_by_event_name.frm to /backup/events_statements_summary_by_host_by_event_name.frm01] copying ./performance_schema/events_statements_summary_by_user_by_event_name.frm to /backup/events_statements_summary_by_user_by_event_name.frm01] copying ./performance_schema/events_statements_summary_by_account_by_event_name.frm to /backup/events_statements_summary_by_account_by_event_name.frm01] copying ./performance_schema/events_statements_summary_global_by_event_name.frm to /backup/events_statements_summary_global_by_event_name.frm01] copying ./performance_schema/events_transactions_current.frm to /backup/events_transactions_current.frm01] copying ./performance_schema/events_transactions_history.frm to /backup/events_transactions_history.frm01] copying ./performance_schema/events_transactions_history_long.frm to /backup/events_transactions_history_long.frm01] copying ./performance_schema/events_transactions_summary_by_thread_by_event_name.frm to /backup/events_transactions_summary_by_thread_by_event_name.frm01] copying ./performance_schema/events_transactions_summary_by_host_by_event_name.frm to /backup/events_transactions_summary_by_host_by_event_name.frm01] copying ./performance_schema/events_transactions_summary_by_user_by_event_name.frm to /backup/events_transactions_summary_by_user_by_event_name.frm01] copying ./performance_schema/events_transactions_summary_by_account_by_event_name.frm to /backup/events_transactions_summary_by_account_by_event_name.frm01] copying ./performance_schema/events_transactions_summary_global_by_event_name.frm to /backup/events_transactions_summary_global_by_event_name.frm01] copying ./performance_schema/hosts.frm to /backup/hosts.frm01] copying ./performance_schema/users.frm to /backup/users.frm01] copying ./performance_schema/accounts.frm to /backup/accounts.frm01] copying ./performance_schema/memory_summary_global_by_event_name.frm to /backup/memory_summary_global_by_event_name.frm01] copying ./performance_schema/memory_summary_by_thread_by_event_name.frm to /backup/memory_summary_by_thread_by_event_name.frm01] copying ./performance_schema/memory_summary_by_account_by_event_name.frm to /backup/memory_summary_by_account_by_event_name.frm01] copying ./performance_schema/memory_summary_by_host_by_event_name.frm to /backup/memory_summary_by_host_by_event_name.frm01] copying ./performance_schema/memory_summary_by_user_by_event_name.frm to /backup/memory_summary_by_user_by_event_name.frm01] copying ./performance_schema/events_statements_summary_by_digest.frm to /backup/events_statements_summary_by_digest.frm01] copying ./performance_schema/events_statements_summary_by_program.frm to /backup/events_statements_summary_by_program.frm01] copying ./performance_schema/prepared_statements_instances.frm to /backup/prepared_statements_instances.frm01] copying ./performance_schema/replication_connection_configuration.frm to /backup/replication_connection_configuration.frm01] copying ./performance_schema/replication_group_member_stats.frm to /backup/replication_group_member_stats.frm01] copying ./performance_schema/replication_group_members.frm to /backup/replication_group_members.frm01] copying ./performance_schema/replication_connection_status.frm to /backup/replication_connection_status.frm01] copying ./performance_schema/replication_applIEr_configuration.frm to /backup/replication_applIEr_configuration.frm01] copying ./performance_schema/replication_applIEr_status.frm to /backup/replication_applIEr_status.frm01] copying ./performance_schema/replication_applIEr_status_by_coordinator.frm to /backup/replication_applIEr_status_by_coordinator.frm01] copying ./performance_schema/replication_applIEr_status_by_worker.frm to /backup/replication_applIEr_status_by_worker.frm01] copying ./performance_schema/session_connect_attrs.frm to /backup/session_connect_attrs.frm01] copying ./performance_schema/session_account_connect_attrs.frm to /backup/session_account_connect_attrs.frm01] copying ./performance_schema/table_handles.frm to /backup/table_handles.frm01] copying ./performance_schema/Metadata_locks.frm to /backup/Metadata_locks.frm01] copying ./performance_schema/user_variables_by_thread.frm to /backup/user_variables_by_thread.frm01] copying ./performance_schema/variables_by_thread.frm to /backup/variables_by_thread.frm01] copying ./performance_schema/global_variables.frm to /backup/global_variables.frm01] copying ./performance_schema/session_variables.frm to /backup/session_variables.frm01] copying ./performance_schema/status_by_thread.frm to /backup/status_by_thread.frm01] copying ./performance_schema/status_by_user.frm to /backup/status_by_user.frm01] copying ./performance_schema/status_by_host.frm to /backup/status_by_host.frm01] copying ./performance_schema/status_by_account.frm to /backup/status_by_account.frm01] copying ./performance_schema/global_status.frm to /backup/global_status.frm01] copying ./performance_schema/session_status.frm to /backup/session_status.frm00] Writing /backup/47/2019-11-06_22-32-57/00]        ...33-49/35-29/36/36-37-24/38-11/49 Finished backing up non-49 Executing FLUSH NO_WRITE_TO_binlog ENGINE LOGS...xtrabackup: The latest check point (for incremental): 2625682xtrabackup: StopPing log copying thread..49 >> log scanned up to () Executing UNLOCK tableS All tables unlocked00] copying ib_buffer_pool to /backup/ib_buffer_pool49 Backup created in directory /backup/2020-02-11_16-42-47/'47/backup-my.cnfxtrabackup_infodonextrabackup: Transaction log of lsn (2625682) to () was copIEd.49 completed OK!

VIEw Code

 

最终会在备份路径下生成一个以时间"yyyy-mm-dd_hh:mi:ss"的文件。

[root@MysqLserver backup]# pwd/backup[root@MysqLserver backup]# ls47

 

（3.2）使用innobackupex执行增量备份

MysqL的增量备份与Oracle RMAN增量备份十分相似，仅仅备份那些发生过改变的块(MysqL叫页，page)，Oracle是通过SCN(system changer number)号来记录数据块的改变的，而MysqL是通过LSN(Log Sequence Number)来记录页的改变。每次全备或增量备份时，innobackupex都会在备份集中创建一个名为"xtrabackup_checkpoints"的文件，里面记录了最后修改的LSN号，在后续增量备份中，只要备份比该LSN大的page和二进制日志即可。

执行增量备份的命令如下：

[root@MysqLserver backup]# innobackupex --user=root --password=' --incremental --incremental-basedir=47 /backup/increm

 相对于全备来说，新增了2个参数：

--incremental                ：代表创建增量备份

--incremental-basedir   ：增量备份需要依赖于某次全备，该参数指定了全备的路径

 

innobackupex增量备份执行过程如下：

[root@MysqLserver backup]# innobackupex --user=root --password=47 /backup/incrementxtrabackup: recognized server arguments: --datadir=/usr/local/MysqL/17:08:1313  version_check Connecting to MysqL server with DSN 13 Connecting to MysqL server host: localhost,1)">: 29b4ca5)incremental backup from 2625682 is enabled.xtrabackup: uses posix_fadvise().xtrabackup: cd to /usr/local/MysqL/13 >> log scanned up to (0xtrabackup: using the full scan for incremental backup13 [01] copying ./ibdata1 to /backup/increment/02-11_17-08-13/ibdata1.delta01] copying ./MysqL/plugin.ibd to /backup/increment/13/MysqL/plugin.ibd.delta01] copying ./MysqL/servers.ibd to /backup/increment/servers.ibd.delta01] copying ./MysqL/help_topic.ibd to /backup/increment/help_topic.ibd.delta14 [01] copying ./MysqL/help_category.ibd to /backup/increment/help_category.ibd.delta01] copying ./MysqL/help_relation.ibd to /backup/increment/help_relation.ibd.delta01] copying ./MysqL/help_keyword.ibd to /backup/increment/help_keyword.ibd.delta01] copying ./MysqL/time_zone_name.ibd to /backup/increment/time_zone_name.ibd.delta01] copying ./MysqL/time_zone.ibd to /backup/increment/time_zone.ibd.delta01] copying ./MysqL/time_zone_Transition.ibd to /backup/increment/time_zone_Transition.ibd.delta01] copying ./MysqL/time_zone_Transition_type.ibd to /backup/increment/time_zone_Transition_type.ibd.delta01] copying ./MysqL/time_zone_leap_second.ibd to /backup/increment/time_zone_leap_second.ibd.delta01] copying ./MysqL/innodb_table_stats.ibd to /backup/increment/innodb_table_stats.ibd.delta01] copying ./MysqL/innodb_index_stats.ibd to /backup/increment/innodb_index_stats.ibd.delta01] copying ./MysqL/slave_relay_log_info.ibd to /backup/increment/slave_relay_log_info.ibd.delta01] copying ./MysqL/slave_master_info.ibd to /backup/increment/slave_master_info.ibd.delta01] copying ./MysqL/slave_worker_info.ibd to /backup/increment/slave_worker_info.ibd.delta01] copying ./MysqL/gtID_executed.ibd to /backup/increment/gtID_executed.ibd.delta01] copying ./MysqL/server_cost.ibd to /backup/increment/server_cost.ibd.delta01] copying ./MysqL/engine_cost.ibd to /backup/increment/engine_cost.ibd.delta01] copying ./sys/sys_config.ibd to /backup/increment/13/sys/sys_config.ibd.delta01] copying ./test_database/test01.ibd to /backup/increment/13/test_database/test01.ibd.delta14 >> log scanned up to (1414 Starting to backup non-01] copying ./MysqL/db.opt to /backup/increment/01] copying ./MysqL/db.frm to /backup/increment/01] copying ./MysqL/db.MYI to /backup/increment/01] copying ./MysqL/db.MYD to /backup/increment/01] copying ./MysqL/user.frm to /backup/increment/01] copying ./MysqL/user.MYI to /backup/increment/01] copying ./MysqL/user.MYD to /backup/increment/01] copying ./MysqL/func.frm to /backup/increment/01] copying ./MysqL/func.MYI to /backup/increment/01] copying ./MysqL/func.MYD to /backup/increment/01] copying ./MysqL/plugin.frm to /backup/increment/01] copying ./MysqL/servers.frm to /backup/increment/01] copying ./MysqL/tables_priv.frm to /backup/increment/01] copying ./MysqL/tables_priv.MYI to /backup/increment/01] copying ./MysqL/tables_priv.MYD to /backup/increment/01] copying ./MysqL/columns_priv.frm to /backup/increment/01] copying ./MysqL/columns_priv.MYI to /backup/increment/01] copying ./MysqL/columns_priv.MYD to /backup/increment/01] copying ./MysqL/help_topic.frm to /backup/increment/01] copying ./MysqL/help_category.frm to /backup/increment/01] copying ./MysqL/help_relation.frm to /backup/increment/01] copying ./MysqL/help_keyword.frm to /backup/increment/01] copying ./MysqL/time_zone_name.frm to /backup/increment/01] copying ./MysqL/time_zone.frm to /backup/increment/01] copying ./MysqL/time_zone_Transition.frm to /backup/increment/01] copying ./MysqL/time_zone_Transition_type.frm to /backup/increment/01] copying ./MysqL/time_zone_leap_second.frm to /backup/increment/01] copying ./MysqL/proc.frm to /backup/increment/01] copying ./MysqL/proc.MYI to /backup/increment/01] copying ./MysqL/proc.MYD to /backup/increment/01] copying ./MysqL/procs_priv.frm to /backup/increment/01] copying ./MysqL/procs_priv.MYI to /backup/increment/01] copying ./MysqL/procs_priv.MYD to /backup/increment/01] copying ./MysqL/general_log.frm to /backup/increment/01] copying ./MysqL/general_log.CSM to /backup/increment/01] copying ./MysqL/general_log.CSV to /backup/increment/01] copying ./MysqL/slow_log.frm to /backup/increment/01] copying ./MysqL/slow_log.CSM to /backup/increment/01] copying ./MysqL/slow_log.CSV to /backup/increment/01] copying ./MysqL/event.frm to /backup/increment/01] copying ./MysqL/event.MYI to /backup/increment/01] copying ./MysqL/event.MYD to /backup/increment/01] copying ./MysqL/ndb_binlog_index.frm to /backup/increment/01] copying ./MysqL/ndb_binlog_index.MYI to /backup/increment/01] copying ./MysqL/ndb_binlog_index.MYD to /backup/increment/01] copying ./MysqL/innodb_table_stats.frm to /backup/increment/01] copying ./MysqL/innodb_index_stats.frm to /backup/increment/01] copying ./MysqL/slave_relay_log_info.frm to /backup/increment/01] copying ./MysqL/slave_master_info.frm to /backup/increment/01] copying ./MysqL/slave_worker_info.frm to /backup/increment/01] copying ./MysqL/gtID_executed.frm to /backup/increment/01] copying ./MysqL/server_cost.frm to /backup/increment/01] copying ./MysqL/engine_cost.frm to /backup/increment/01] copying ./MysqL/proxIEs_priv.frm to /backup/increment/01] copying ./MysqL/proxIEs_priv.MYI to /backup/increment/01] copying ./MysqL/proxIEs_priv.MYD to /backup/increment/01] copying ./sys/db.opt to /backup/increment/01] copying ./sys/version.frm to /backup/increment/01] copying ./sys/sys_config.frm to /backup/increment/01] copying ./sys/statements_with_full_table_scans.frm to /backup/increment/01] copying ./sys/sys_config_insert_set_user.TRN to /backup/increment/01] copying ./sys/processList.frm to /backup/increment/01] copying ./sys/sys_config.TRG to /backup/increment/01] copying ./sys/statements_with_sorting.frm to /backup/increment/01] copying ./sys/sys_config_update_set_user.TRN to /backup/increment/01] copying ./sys/x@0024statements_with_sorting.frm to /backup/increment/01] copying ./sys/innodb_buffer_stats_by_schema.frm to /backup/increment/01] copying ./sys/schema_index_statistics.frm to /backup/increment/01] copying ./sys/x@0024innodb_buffer_stats_by_schema.frm to /backup/increment/01] copying ./sys/statements_with_temp_tables.frm to /backup/increment/01] copying ./sys/innodb_buffer_stats_by_table.frm to /backup/increment/01] copying ./sys/x@0024ps_schema_table_statistics_io.frm to /backup/increment/01] copying ./sys/x@0024innodb_buffer_stats_by_table.frm to /backup/increment/01] copying ./sys/host_summary.frm to /backup/increment/01] copying ./sys/innodb_lock_waits.frm to /backup/increment/01] copying ./sys/x@0024host_summary.frm to /backup/increment/01] copying ./sys/x@0024innodb_lock_waits.frm to /backup/increment/01] copying ./sys/waits_by_user_by_latency.frm to /backup/increment/01] copying ./sys/schema_object_overvIEw.frm to /backup/increment/01] copying ./sys/user_summary_by_file_io_type.frm to /backup/increment/01] copying ./sys/schema_auto_increment_columns.frm to /backup/increment/01] copying ./sys/user_summary_by_file_io.frm to /backup/increment/01] copying ./sys/x@0024schema_flattened_keys.frm to /backup/increment/01] copying ./sys/x@0024user_summary_by_file_io.frm to /backup/increment/01] copying ./sys/schema_redundant_indexes.frm to /backup/increment/01] copying ./sys/user_summary_by_statement_type.frm to /backup/increment/01] copying ./sys/ps_check_lost_instrumentation.frm to /backup/increment/01] copying ./sys/latest_file_io.frm to /backup/increment/01] copying ./sys/waits_by_host_by_latency.frm to /backup/increment/01] copying ./sys/x@0024latest_file_io.frm to /backup/increment/01] copying ./sys/waits_global_by_latency.frm to /backup/increment/01] copying ./sys/io_by_thread_by_latency.frm to /backup/increment/01] copying ./sys/user_summary_by_stages.frm to /backup/increment/01] copying ./sys/x@0024io_by_thread_by_latency.frm to /backup/increment/01] copying ./sys/x@0024user_summary_by_stages.frm to /backup/increment/01] copying ./sys/io_global_by_file_by_bytes.frm to /backup/increment/01] copying ./sys/x@0024schema_index_statistics.frm to /backup/increment/01] copying ./sys/x@0024io_global_by_file_by_bytes.frm to /backup/increment/01] copying ./sys/user_summary.frm to /backup/increment/01] copying ./sys/io_global_by_file_by_latency.frm to /backup/increment/01] copying ./sys/schema_table_statistics.frm to /backup/increment/01] copying ./sys/x@0024io_global_by_file_by_latency.frm to /backup/increment/01] copying ./sys/x@0024user_summary.frm to /backup/increment/01] copying ./sys/io_global_by_wait_by_bytes.frm to /backup/increment/01] copying ./sys/x@0024schema_table_statistics.frm to /backup/increment/01] copying ./sys/x@0024io_global_by_wait_by_bytes.frm to /backup/increment/01] copying ./sys/host_summary_by_file_io_type.frm to /backup/increment/01] copying ./sys/io_global_by_wait_by_latency.frm to /backup/increment/01] copying ./sys/schema_table_statistics_with_buffer.frm to /backup/increment/01] copying ./sys/x@0024io_global_by_wait_by_latency.frm to /backup/increment/01] copying ./sys/host_summary_by_file_io.frm to /backup/increment/01] copying ./sys/memory_by_user_by_current_bytes.frm to /backup/increment/01] copying ./sys/schema_table_lock_waits.frm to /backup/increment/01] copying ./sys/x@0024memory_by_user_by_current_bytes.frm to /backup/increment/01] copying ./sys/x@0024host_summary_by_file_io.frm to /backup/increment/01] copying ./sys/memory_by_host_by_current_bytes.frm to /backup/increment/01] copying ./sys/x@0024schema_table_lock_waits.frm to /backup/increment/01] copying ./sys/x@0024memory_by_host_by_current_bytes.frm to /backup/increment/01] copying ./sys/statement_analysis.frm to /backup/increment/01] copying ./sys/memory_by_thread_by_current_bytes.frm to /backup/increment/01] copying ./sys/x@0024statement_analysis.frm to /backup/increment/01] copying ./sys/x@0024memory_by_thread_by_current_bytes.frm to /backup/increment/01] copying ./sys/host_summary_by_statement_type.frm to /backup/increment/01] copying ./sys/memory_global_by_current_bytes.frm to /backup/increment/01] copying ./sys/statements_with_errors_or_warnings.frm to /backup/increment/01] copying ./sys/x@0024memory_global_by_current_bytes.frm to /backup/increment/01] copying ./sys/metrics.frm to /backup/increment/01] copying ./sys/memory_global_total.frm to /backup/increment/01] copying ./sys/host_summary_by_stages.frm to /backup/increment/01] copying ./sys/x@0024memory_global_total.frm to /backup/increment/01] copying ./sys/session.frm to /backup/increment/01] copying ./sys/x@0024schema_table_statistics_with_buffer.frm to /backup/increment/01] copying ./sys/x@0024statements_with_errors_or_warnings.frm to /backup/increment/01] copying ./sys/schema_tables_with_full_table_scans.frm to /backup/increment/01] copying ./sys/schema_unused_indexes.frm to /backup/increment/01] copying ./sys/x@0024schema_tables_with_full_table_scans.frm to /backup/increment/01] copying ./sys/x@0024host_summary_by_stages.frm to /backup/increment/01] copying ./sys/x@0024statements_with_full_table_scans.frm to /backup/increment/01] copying ./sys/x@0024ps_digest_avg_latency_distribution.frm to /backup/increment/01] copying ./sys/x@0024ps_digest_95th_percentile_by_avg_us.frm to /backup/increment/01] copying ./sys/statements_with_runtimes_in_95th_percentile.frm to /backup/increment/01] copying ./sys/x@0024statements_with_runtimes_in_95th_percentile.frm to /backup/increment/01] copying ./sys/x@0024statements_with_temp_tables.frm to /backup/increment/01] copying ./sys/x@0024user_summary_by_file_io_type.frm to /backup/increment/01] copying ./sys/x@0024user_summary_by_statement_type.frm to /backup/increment/01] copying ./sys/user_summary_by_statement_latency.frm to /backup/increment/01] copying ./sys/x@0024user_summary_by_statement_latency.frm to /backup/increment/01] copying ./sys/x@0024host_summary_by_file_io_type.frm to /backup/increment/01] copying ./sys/x@0024host_summary_by_statement_type.frm to /backup/increment/01] copying ./sys/host_summary_by_statement_latency.frm to /backup/increment/01] copying ./sys/x@0024host_summary_by_statement_latency.frm to /backup/increment/01] copying ./sys/wait_classes_global_by_avg_latency.frm to /backup/increment/01] copying ./sys/x@0024wait_classes_global_by_avg_latency.frm to /backup/increment/01] copying ./sys/wait_classes_global_by_latency.frm to /backup/increment/01] copying ./sys/x@0024wait_classes_global_by_latency.frm to /backup/increment/01] copying ./sys/x@0024waits_by_user_by_latency.frm to /backup/increment/01] copying ./sys/x@0024waits_by_host_by_latency.frm to /backup/increment/01] copying ./sys/x@0024waits_global_by_latency.frm to /backup/increment/01] copying ./sys/[email protected] to /backup/increment/01] copying ./sys/[email protected] to /backup/increment/01] copying ./sys/session_ssl_status.frm to /backup/increment/01] copying ./test_database/db.opt to /backup/increment/01] copying ./test_database/test01.frm to /backup/increment/01] copying ./performance_schema/db.opt to /backup/increment/13/performance_schema/01] copying ./performance_schema/cond_instances.frm to /backup/increment/01] copying ./performance_schema/events_waits_current.frm to /backup/increment/15 [01] copying ./performance_schema/events_waits_history.frm to /backup/increment/01] copying ./performance_schema/events_waits_history_long.frm to /backup/increment/01] copying ./performance_schema/events_waits_summary_by_instance.frm to /backup/increment/01] copying ./performance_schema/events_waits_summary_by_host_by_event_name.frm to /backup/increment/01] copying ./performance_schema/events_waits_summary_by_user_by_event_name.frm to /backup/increment/01] copying ./performance_schema/events_waits_summary_by_account_by_event_name.frm to /backup/increment/01] copying ./performance_schema/events_waits_summary_by_thread_by_event_name.frm to /backup/increment/01] copying ./performance_schema/events_waits_summary_global_by_event_name.frm to /backup/increment/01] copying ./performance_schema/file_instances.frm to /backup/increment/01] copying ./performance_schema/file_summary_by_event_name.frm to /backup/increment/01] copying ./performance_schema/file_summary_by_instance.frm to /backup/increment/01] copying ./performance_schema/socket_instances.frm to /backup/increment/01] copying ./performance_schema/socket_summary_by_instance.frm to /backup/increment/01] copying ./performance_schema/socket_summary_by_event_name.frm to /backup/increment/01] copying ./performance_schema/host_cache.frm to /backup/increment/01] copying ./performance_schema/mutex_instances.frm to /backup/increment/01] copying ./performance_schema/objects_summary_global_by_type.frm to /backup/increment/01] copying ./performance_schema/performance_timers.frm to /backup/increment/01] copying ./performance_schema/rwlock_instances.frm to /backup/increment/01] copying ./performance_schema/setup_actors.frm to /backup/increment/01] copying ./performance_schema/setup_consumers.frm to /backup/increment/01] copying ./performance_schema/setup_instruments.frm to /backup/increment/01] copying ./performance_schema/setup_objects.frm to /backup/increment/01] copying ./performance_schema/setup_timers.frm to /backup/increment/01] copying ./performance_schema/table_io_waits_summary_by_index_usage.frm to /backup/increment/01] copying ./performance_schema/table_io_waits_summary_by_table.frm to /backup/increment/01] copying ./performance_schema/table_lock_waits_summary_by_table.frm to /backup/increment/01] copying ./performance_schema/threads.frm to /backup/increment/01] copying ./performance_schema/events_stages_current.frm to /backup/increment/01] copying ./performance_schema/events_stages_history.frm to /backup/increment/01] copying ./performance_schema/events_stages_history_long.frm to /backup/increment/01] copying ./performance_schema/events_stages_summary_by_thread_by_event_name.frm to /backup/increment/01] copying ./performance_schema/events_stages_summary_by_host_by_event_name.frm to /backup/increment/01] copying ./performance_schema/events_stages_summary_by_user_by_event_name.frm to /backup/increment/01] copying ./performance_schema/events_stages_summary_by_account_by_event_name.frm to /backup/increment/01] copying ./performance_schema/events_stages_summary_global_by_event_name.frm to /backup/increment/01] copying ./performance_schema/events_statements_current.frm to /backup/increment/01] copying ./performance_schema/events_statements_history.frm to /backup/increment/01] copying ./performance_schema/events_statements_history_long.frm to /backup/increment/01] copying ./performance_schema/events_statements_summary_by_thread_by_event_name.frm to /backup/increment/01] copying ./performance_schema/events_statements_summary_by_host_by_event_name.frm to /backup/increment/01] copying ./performance_schema/events_statements_summary_by_user_by_event_name.frm to /backup/increment/01] copying ./performance_schema/events_statements_summary_by_account_by_event_name.frm to /backup/increment/01] copying ./performance_schema/events_statements_summary_global_by_event_name.frm to /backup/increment/01] copying ./performance_schema/events_transactions_current.frm to /backup/increment/01] copying ./performance_schema/events_transactions_history.frm to /backup/increment/01] copying ./performance_schema/events_transactions_history_long.frm to /backup/increment/01] copying ./performance_schema/events_transactions_summary_by_thread_by_event_name.frm to /backup/increment/01] copying ./performance_schema/events_transactions_summary_by_host_by_event_name.frm to /backup/increment/01] copying ./performance_schema/events_transactions_summary_by_user_by_event_name.frm to /backup/increment/01] copying ./performance_schema/events_transactions_summary_by_account_by_event_name.frm to /backup/increment/01] copying ./performance_schema/events_transactions_summary_global_by_event_name.frm to /backup/increment/01] copying ./performance_schema/hosts.frm to /backup/increment/01] copying ./performance_schema/users.frm to /backup/increment/01] copying ./performance_schema/accounts.frm to /backup/increment/01] copying ./performance_schema/memory_summary_global_by_event_name.frm to /backup/increment/01] copying ./performance_schema/memory_summary_by_thread_by_event_name.frm to /backup/increment/01] copying ./performance_schema/memory_summary_by_account_by_event_name.frm to /backup/increment/01] copying ./performance_schema/memory_summary_by_host_by_event_name.frm to /backup/increment/01] copying ./performance_schema/memory_summary_by_user_by_event_name.frm to /backup/increment/01] copying ./performance_schema/events_statements_summary_by_digest.frm to /backup/increment/01] copying ./performance_schema/events_statements_summary_by_program.frm to /backup/increment/01] copying ./performance_schema/prepared_statements_instances.frm to /backup/increment/01] copying ./performance_schema/replication_connection_configuration.frm to /backup/increment/01] copying ./performance_schema/replication_group_member_stats.frm to /backup/increment/01] copying ./performance_schema/replication_group_members.frm to /backup/increment/01] copying ./performance_schema/replication_connection_status.frm to /backup/increment/01] copying ./performance_schema/replication_applIEr_configuration.frm to /backup/increment/01] copying ./performance_schema/replication_applIEr_status.frm to /backup/increment/01] copying ./performance_schema/replication_applIEr_status_by_coordinator.frm to /backup/increment/01] copying ./performance_schema/replication_applIEr_status_by_worker.frm to /backup/increment/01] copying ./performance_schema/session_connect_attrs.frm to /backup/increment/01] copying ./performance_schema/session_account_connect_attrs.frm to /backup/increment/01] copying ./performance_schema/table_handles.frm to /backup/increment/01] copying ./performance_schema/Metadata_locks.frm to /backup/increment/01] copying ./performance_schema/user_variables_by_thread.frm to /backup/increment/01] copying ./performance_schema/variables_by_thread.frm to /backup/increment/01] copying ./performance_schema/global_variables.frm to /backup/increment/01] copying ./performance_schema/session_variables.frm to /backup/increment/01] copying ./performance_schema/status_by_thread.frm to /backup/increment/01] copying ./performance_schema/status_by_user.frm to /backup/increment/01] copying ./performance_schema/status_by_host.frm to /backup/increment/01] copying ./performance_schema/status_by_account.frm to /backup/increment/01] copying ./performance_schema/global_status.frm to /backup/increment/01] copying ./performance_schema/session_status.frm to /backup/increment/00] Writing /backup/increment/13/15 Finished backing up non-1515 >> log scanned up to (00] copying ib_buffer_pool to /backup/increment/15 Backup created /backup/increment/2020-02-11_17-08-13/13/backup-15 completed OK!

VIEw Code

 

与全备一样，会在备份路径下生成一个以时间"yyyy-mm-dd_hh:mi:ss"的文件。

[root@MysqLserver increment]# pwd/backup/increment[root@MysqLserver increment]# 13

 

（四）使用innobackupex恢复数据库

从恢复类别上来看，在使innobackupex来恢复数据库时，有全量恢复和增量恢复2种，全量恢复就是恢复全备后的数据库，增量恢复则是先做全量恢复，在对增量备份的数据进行恢复。不管是全量恢复还是增量恢复。都需要执行2个步骤：

准备恢复(prepare)。在执行完备份后，数据集是不能直接拿来使用的，因为备份的数据文件中可能包含未提交的事物或者已经提交但数据还未保存到数据文件中的事物，准备恢复的过程就是先前滚redo日志中发生的改变，再回滚未提交的事物，总而使数据文件达到一致性状态。对于innobackupex来说，准备恢复对应的参数是"--apply-log"。执行恢复(copy-back)。将已经准备好的备份集，恢复到指定的路径(datadir参数)下。对于innobackupex来说，准备恢复对应的参数是"--copy-back"。

（4.1）使用innobackupex执行全量恢复

执行全量恢复的命令如下：

# 准备恢复innobackupex --default-file=/etc/my.cnf --apply-log /path/to/BACKUP-DIR# 执行恢复innobackupex --default-file=/etc/my.cnf --copy-back /path/to/BACKUP-DIR

 

全量恢复例子：

STEP1：执行完整备份[root@MysqLserver backup]#innobackupex --user=root --password=' /backup/STEP2：删除测试数据库lijiamandb，并关闭数据库[root@MysqLserver backup]# MysqL -uroot -p123456MysqL: [Warning] Using a password on the command line interface can be insecure.Welcome to the MysqL monitor.  Commands end with ; or \g.Your MysqL connection ID is 26Server version:  MysqL Community Server (GPL)MysqL> drop database lijiamandb;query OK,2 rows affected (0.01 sec)MysqL> exitBye[root@MysqLserver backup]# service MysqLd stopShutting down MysqL.... SUCCESS!STEP3：准备还原数据库[root@MysqLserver backup]# innobackupex --default-file=/etc/my.cnf --apply-log /backup/02-11_18-46-01xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-ID=0 --redo-log-version=1 xtrabackup: recognized clIEnt arguments: 18:55:19 innobackupex: Starting the apply-log operationimportant: Please check that the apply-log run completes successfully.           At the end of a successful apply-log run innobackupex           prints .innobackupex version : 29b4ca5)xtrabackup: cd to /backup/01/...省略InnoDB: file ./ibtmp1' size is Now 12 MB.InnoDB: 96 redo rollback segment(s) found.  redo rollback segment(s) are active.InnoDB: 32 non-redo rollback segment(s) are active.InnoDB: 26 started; log sequence number 78175765xtrabackup: starting shutdown with innodb_fast_shutdown = InnoDB: FTS optimize thread exiting.InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 7817578422 completed OK!STEP4：开始还原数据库  # 注意：在开始还原之前，需要保证数据文件存放位置(datadir)文件为空，否则会报错[root@MysqLserver backup]# innobackupex --default-file=/etc/my.cnf --copy-back /backup/xtrabackup: recognized server arguments: --datadir=/usr/local/MysqL/35 innobackupex: Starting the copy-back operationimportant: Please check that the copy-back run completes successfully.           At the end of a successful copy-back run innobackupex           prints : 29b4ca5)Original data directory /usr/local/MysqL/data is not empty![root@MysqLserver data]# rm -rf * # 开始还原数据库[root@MysqLserver backup]# innobackupex --default-56:14 innobackupex: Starting the copy-: 29b4ca5)01] copying ib_logfile0 to /usr/local/MysqL/data/ib_logfile0...省略01] copying ./xtrabackup_info to /usr/local/MysqL/data/01] copying ./xtrabackup_master_key_ID to /usr/local/MysqL/data/xtrabackup_master_key_ID01] copying ./ibtmp1 to /usr/local/MysqL/data/ibtmp114 completed OK!STEP5：修改还原文件的权限为MysqL:MysqL [root@MysqLserver data]# ls -ltotal 122920-rw-r----- 1 root root      284 Feb 56 ib_buffer_pool-rw-r----- 1 root root 12582912 Feb  ibdata1-rw-r----- 50331648 Feb  ib_logfile0-rw-r-----  ib_logfile1-rw-r-----  ibtmp1drwxr-x--- 2 root root       92 Feb  lijiamandbdrwxr-x--- 2 root root     4096 Feb  MysqLdrwxr-x--- 8192 Feb  performance_schemadrwxr-x---  sys-rw-r----- 447 Feb  xtrabackup_info-rw-r----- 1 root root        1 Feb  xtrabackup_master_key_ID[root@MysqLserver data]# cd ..[root@MysqLserver MysqL]# pwd/usr/local/MysqL[root@MysqLserver MysqL]# chown -R MysqL:MysqL data/STEP6：重启数据库[root@MysqLserver backup]# service MysqLd startStarting MysqL.Logging to /usr/local/MysqL/data/MysqLserver.err. SUCCESS!STEP7：确认数据库是否恢复[root@MysqLserver backup]# MysqL -uroot -p123456Server version:  MysqL Community Server (GPL)MysqL> show databases;+--------------------+| Database           |+--------------------+| information_schema || lijiamandb         || MysqL              || performance_schema || sys                |+--------------------+5 rows in set (0.00 sec)

 

 （4.2）使用innobackupex执行增量恢复

执行增量恢复的命令如下：

# 对全量备份执行恢复准备innobackupex --default-file=/etc/my.cnf --apply-log --redo-only /path/to/FulL_BACKUP-# 对增量备份1执行恢复准备innobackupex --default-file=/etc/my.cnf --apply-log --redo-only /path/to/FulL_BACKUP-DIR --incremental-dir=/path/to/INCR_BACKUP-DIR_12执行恢复准备# 需要注意的是，这里没有了--redo-log,在应用增量备份时，最后一份增量备份集不需要指定该参数innobackupex --default-file=/etc/my.cnf --apply-log /path/to/FulL_BACKUP-DIR --incremental-dir=/path/to/INCR_BACKUP-DIR_2file=/etc/my.cnf --copy-back /path/to/FulL_BACKUP-DIR

 

 增量恢复例子：

STEP1:执行全量备份[root@MysqLserver backup]# innobackupex --user=root --password=STEP2：在全量备份的基础上执行增量备份[root@MysqLserver backup]# innobackupex --user=root --password=02-11_20-54 /backup/STEP3：删除测试数据，看最终是否可以恢复回来[root@MysqLserver backup]# MysqL -uroot -p123456MysqL> use lijiamandb;Reading table information  completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changed MysqL> select count(*) from test01;+----------+| count(*) |+----------+|    10000 |+----------+1 row  sec)MysqL> ) from test02;+----------+| count(*) |+----------+|     truncate table test01;query OK,1)">0 rows affected ( delete table test02;MysqL> delete from test02;query OK,1)">10000 rows affected (0.02 sec)MysqL> ) from test01;+----------+| count(*) |+----------+|        0 |+----------+) from test02;+----------+| count(*) |+----------+|         exitByeSTEP4：关闭数据库[root@MysqLserver backup]# service MysqLd status SUCCESS! MysqL running (2004)[root@MysqLserver backup]# service MysqLd stopShutting down MysqL.... SUCCESS!STEP5：准备全量备份[root@MysqLserver backup]# lrttotal drwxr-x--- 6 root root 211 Feb 20:13 54     #全量备份drwxr-x--- 237 Feb 15 15-04     #增量备份[root@MysqLserver backup]# innobackupex --default-file=/etc/my.cnf --apply-log --redo-only /backup/b_fast_checksum=512 --innodb_undo_directory=./ --innodb_undo_tablespaces= xtrabackup: recognized clIEnt arguments: ...省略InnoDB: Doing recovery: scanned up to log sequence number 5054779 (0%)InnoDB: Database was not shutdown normally!InnoDB: Starting crash recovery.xtrabackup: starting shutdown with innodb_fast_shutdown = InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 5054788STEP6：准备增量备份[root@MysqLserver backup]# innobackupex --default-54  --incremental-dir=/backup/26 innobackupex: Starting the apply-log operation...省略xtrabackup: starting shutdown with innodb_fast_shutdown = 505552831 completed OK!STEP7：执行还原# 删除data目录并重建[root@MysqLserver backup]# cd /usr/local/MysqL/[root@MysqLserver MysqL]# rm -rf data/mkdir data[root@MysqLserver MysqL]# innobackupex --default-58 innobackupex: Starting the copy-back operation...省略58 [58 completed OK!STEP8：修改data目录的权限[root@MysqLserver MysqL]# STEP9：重启数据库，确认数据已经恢复回来[root@MysqLserver MysqL]# service MysqLd startStarting MysqL.Logging to  [root@MysqLserver MysqL]# MysqL -uroot -p123456MysqL>ADatabase changedMysqL>  exitBye

 

【完】

总结

以上是内存溢出为你收集整理的MySQL物理物理备份与还原工具xtraBackup全部内容，希望文章能够帮你解决MySQL物理物理备份与还原工具xtraBackup所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。