手機版
你好,游客 登錄 注冊 搜索
背景:
閱讀新聞

MySQL數據庫使用Xtrabackup全備和全備還原

[日期:2019-12-25] 來源:Linux社區  作者:Linux [字體: ]

一、使用innobackupex創建全備

語法
innobackupex --user=DBUSER --password=DBUSERPASS /path/to/backup/dir/
innobackupex --user=DBUSER --password=DBUSERPASS --backup --target-dir=/path/to/BACKUP-DIR/

OptionDescription
--backup 默認選項
–port 端口
–socket socket
–host 主機
--no-timestamp 指定了這個選項備份會直接備份在BACKUP-DIR,不再創建時間戳文件夾
--target-dir 指定了這個選項備份會直接備份在BACKUP-DIR,不再創建時間戳文件夾
--defaults-file 指定默認的配置文件,必須為第一個選項

[[email protected] backup]# innobackupex --defaults-file=/etc/my.cnf  --user=root --password=wangshengzhuang /backup/
160904 18:01:55 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!".

160904 18:01:56  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock' as 'root'  (using password: YES).
160904 18:01:56  version_check Connected to MySQL server
160904 18:01:56  version_check Executing a version check against the server...
160904 18:01:56  version_check Done.
160904 18:01:56 Connecting to MySQL server host: localhost, user: root, password: set, port: 0, socket: /var/lib/mysql/mysql.sock
Using server version 5.6.30-log
innobackupex version 2.4.4 based on MySQL server 5.7.13 Linux (x86_64) (revision id: df58cf2)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
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
160904 18:01:56 >> log scanned up to (1626027)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 3 for mysql/slave_relay_log_info, old maximum was 0
160904 18:01:56 [01] Copying ./ibdata1 to /backup/2019-09-04_18-01-55/ibdata1
160904 18:01:56 [01]        ...done
160904 18:01:56 [01] Copying ./mysql/slave_relay_log_info.ibd to /backup/2019-09-04_18-01-55/mysql/slave_relay_log_info.ibd
160904 18:01:56 [01]        ...done
160904 18:01:56 [01] Copying ./mysql/slave_worker_info.ibd to /backup/2019-09-04_18-01-55/mysql/slave_worker_info.ibd
160904 18:01:56 [01]        ...done
160904 18:01:56 [01] Copying ./mysql/slave_master_info.ibd to /backup/2019-09-04_18-01-55/mysql/slave_master_info.ibd
160904 18:01:56 [01]        ...done
160904 18:01:56 [01] Copying ./mysql/innodb_index_stats.ibd to /backup/2019-09-04_18-01-55/mysql/innodb_index_stats.ibd
160904 18:01:56 [01]        ...done
160904 18:01:56 [01] Copying ./mysql/innodb_table_stats.ibd to /backup/2019-09-04_18-01-55/mysql/innodb_table_stats.ibd
160904 18:01:56 [01]        ...done
160904 18:01:57 >> log scanned up to (1626027)
160904 18:01:57 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
160904 18:01:57 Executing FLUSH TABLES WITH READ LOCK...
160904 18:01:57 Starting to backup non-InnoDB tables and files
160904 18:01:57 [00] Writing test/db.opt
160904 18:01:57 [00]        ...done
160904 18:01:57 [01] Copying ./mysql/servers.frm to /backup/2019-09-04_18-01-55/mysql/servers.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/tables_priv.MYD to /backup/2019-09-04_18-01-55/mysql/tables_priv.MYD
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/help_keyword.frm to /backup/2019-09-04_18-01-55/mysql/help_keyword.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/ndb_binlog_index.MYD to /backup/2019-09-04_18-01-55/mysql/ndb_binlog_index.MYD
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/columns_priv.MYD to /backup/2019-09-04_18-01-55/mysql/columns_priv.MYD
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/servers.MYD to /backup/2019-09-04_18-01-55/mysql/servers.MYD
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/user.MYD to /backup/2019-09-04_18-01-55/mysql/user.MYD
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/event.frm to /backup/2019-09-04_18-01-55/mysql/event.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/time_zone_transition.MYD to /backup/2019-09-04_18-01-55/mysql/time_zone_transition.MYD
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/proc.MYI to /backup/2019-09-04_18-01-55/mysql/proc.MYI
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/time_zone_leap_second.MYI to /backup/2019-09-04_18-01-55/mysql/time_zone_leap_second.MYI
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/help_topic.MYI to /backup/2019-09-04_18-01-55/mysql/help_topic.MYI
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/time_zone_transition.MYI to /backup/2019-09-04_18-01-55/mysql/time_zone_transition.MYI
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/general_log.CSV to /backup/2019-09-04_18-01-55/mysql/general_log.CSV
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/time_zone_transition.frm to /backup/2019-09-04_18-01-55/mysql/time_zone_transition.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/func.MYD to /backup/2019-09-04_18-01-55/mysql/func.MYD
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/user.MYI to /backup/2019-09-04_18-01-55/mysql/user.MYI
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/help_relation.MYI to /backup/2019-09-04_18-01-55/mysql/help_relation.MYI
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/plugin.frm to /backup/2019-09-04_18-01-55/mysql/plugin.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/time_zone_name.frm to /backup/2019-09-04_18-01-55/mysql/time_zone_name.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/ndb_binlog_index.MYI to /backup/2019-09-04_18-01-55/mysql/ndb_binlog_index.MYI
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/help_relation.MYD to /backup/2019-09-04_18-01-55/mysql/help_relation.MYD
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/plugin.MYD to /backup/2019-09-04_18-01-55/mysql/plugin.MYD
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/help_category.MYD to /backup/2019-09-04_18-01-55/mysql/help_category.MYD
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/event.MYI to /backup/2019-09-04_18-01-55/mysql/event.MYI
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/event.MYD to /backup/2019-09-04_18-01-55/mysql/event.MYD
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/procs_priv.MYI to /backup/2019-09-04_18-01-55/mysql/procs_priv.MYI
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/slow_log.frm to /backup/2019-09-04_18-01-55/mysql/slow_log.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/help_keyword.MYI to /backup/2019-09-04_18-01-55/mysql/help_keyword.MYI
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/user.frm to /backup/2019-09-04_18-01-55/mysql/user.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/slow_log.CSV to /backup/2019-09-04_18-01-55/mysql/slow_log.CSV
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/help_keyword.MYD to /backup/2019-09-04_18-01-55/mysql/help_keyword.MYD
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/plugin.MYI to /backup/2019-09-04_18-01-55/mysql/plugin.MYI
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/func.frm to /backup/2019-09-04_18-01-55/mysql/func.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/proxies_priv.MYD to /backup/2019-09-04_18-01-55/mysql/proxies_priv.MYD
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/time_zone_leap_second.frm to /backup/2019-09-04_18-01-55/mysql/time_zone_leap_second.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/slave_master_info.frm to /backup/2019-09-04_18-01-55/mysql/slave_master_info.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/help_relation.frm to /backup/2019-09-04_18-01-55/mysql/help_relation.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/func.MYI to /backup/2019-09-04_18-01-55/mysql/func.MYI
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/procs_priv.frm to /backup/2019-09-04_18-01-55/mysql/procs_priv.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/proc.frm to /backup/2019-09-04_18-01-55/mysql/proc.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/innodb_index_stats.frm to /backup/2019-09-04_18-01-55/mysql/innodb_index_stats.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/db.MYD to /backup/2019-09-04_18-01-55/mysql/db.MYD
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/db.frm to /backup/2019-09-04_18-01-55/mysql/db.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/columns_priv.MYI to /backup/2019-09-04_18-01-55/mysql/columns_priv.MYI
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/time_zone.frm to /backup/2019-09-04_18-01-55/mysql/time_zone.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/columns_priv.frm to /backup/2019-09-04_18-01-55/mysql/columns_priv.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/slave_worker_info.frm to /backup/2019-09-04_18-01-55/mysql/slave_worker_info.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/time_zone_transition_type.frm to /backup/2019-09-04_18-01-55/mysql/time_zone_transition_type.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/help_topic.frm to /backup/2019-09-04_18-01-55/mysql/help_topic.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/servers.MYI to /backup/2019-09-04_18-01-55/mysql/servers.MYI
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/help_category.frm to /backup/2019-09-04_18-01-55/mysql/help_category.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/help_category.MYI to /backup/2019-09-04_18-01-55/mysql/help_category.MYI
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/general_log.frm to /backup/2019-09-04_18-01-55/mysql/general_log.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/time_zone_leap_second.MYD to /backup/2019-09-04_18-01-55/mysql/time_zone_leap_second.MYD
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/tables_priv.MYI to /backup/2019-09-04_18-01-55/mysql/tables_priv.MYI
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/time_zone_name.MYI to /backup/2019-09-04_18-01-55/mysql/time_zone_name.MYI
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/tables_priv.frm to /backup/2019-09-04_18-01-55/mysql/tables_priv.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/time_zone_transition_type.MYI to /backup/2019-09-04_18-01-55/mysql/time_zone_transition_type.MYI
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/time_zone_name.MYD to /backup/2019-09-04_18-01-55/mysql/time_zone_name.MYD
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/slave_relay_log_info.frm to /backup/2019-09-04_18-01-55/mysql/slave_relay_log_info.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/ndb_binlog_index.frm to /backup/2019-09-04_18-01-55/mysql/ndb_binlog_index.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/time_zone.MYD to /backup/2019-09-04_18-01-55/mysql/time_zone.MYD
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/innodb_table_stats.frm to /backup/2019-09-04_18-01-55/mysql/innodb_table_stats.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/proc.MYD to /backup/2019-09-04_18-01-55/mysql/proc.MYD
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/time_zone_transition_type.MYD to /backup/2019-09-04_18-01-55/mysql/time_zone_transition_type.MYD
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/general_log.CSM to /backup/2019-09-04_18-01-55/mysql/general_log.CSM
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/proxies_priv.frm to /backup/2019-09-04_18-01-55/mysql/proxies_priv.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/db.MYI to /backup/2019-09-04_18-01-55/mysql/db.MYI
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/proxies_priv.MYI to /backup/2019-09-04_18-01-55/mysql/proxies_priv.MYI
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/procs_priv.MYD to /backup/2019-09-04_18-01-55/mysql/procs_priv.MYD
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/help_topic.MYD to /backup/2019-09-04_18-01-55/mysql/help_topic.MYD
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/time_zone.MYI to /backup/2019-09-04_18-01-55/mysql/time_zone.MYI
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./mysql/slow_log.CSM to /backup/2019-09-04_18-01-55/mysql/slow_log.CSM
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./performance_schema/hosts.frm to /backup/2019-09-04_18-01-55/performance_schema/hosts.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./performance_schema/setup_objects.frm to /backup/2019-09-04_18-01-55/performance_schema/setup_objects.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./performance_schema/file_instances.frm to /backup/2019-09-04_18-01-55/performance_schema/file_instances.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./performance_schema/performance_timers.frm to /backup/2019-09-04_18-01-55/performance_schema/performance_timers.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./performance_schema/table_io_waits_summary_by_index_usage.frm to /backup/2019-09-04_18-01-55/performance_schema/table_io_waits_summary_by_index_usage.frm
160904 18:01:57 [01]        ...done
160904 18:01:57 [01] Copying ./performance_schema/events_statements_summary_by_user_by_event_name.frm to /backup/2019-09-04_18-01-55/performance_schema/events_statements_summary_by_user_by_event_name.frm
160904 18:01:57 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/objects_summary_global_by_type.frm to /backup/2019-09-04_18-01-55/performance_schema/objects_summary_global_by_type.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/events_statements_current.frm to /backup/2019-09-04_18-01-55/performance_schema/events_statements_current.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/accounts.frm to /backup/2019-09-04_18-01-55/performance_schema/accounts.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 >> log scanned up to (1626027)
160904 18:01:58 [01] Copying ./performance_schema/socket_summary_by_event_name.frm to /backup/2019-09-04_18-01-55/performance_schema/socket_summary_by_event_name.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/mutex_instances.frm to /backup/2019-09-04_18-01-55/performance_schema/mutex_instances.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/table_lock_waits_summary_by_table.frm to /backup/2019-09-04_18-01-55/performance_schema/table_lock_waits_summary_by_table.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/users.frm to /backup/2019-09-04_18-01-55/performance_schema/users.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/file_summary_by_event_name.frm to /backup/2019-09-04_18-01-55/performance_schema/file_summary_by_event_name.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/events_statements_history_long.frm to /backup/2019-09-04_18-01-55/performance_schema/events_statements_history_long.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/host_cache.frm to /backup/2019-09-04_18-01-55/performance_schema/host_cache.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/events_statements_summary_by_account_by_event_name.frm to /backup/2019-09-04_18-01-55/performance_schema/events_statements_summary_by_account_by_event_name.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/events_waits_summary_by_user_by_event_name.frm to /backup/2019-09-04_18-01-55/performance_schema/events_waits_summary_by_user_by_event_name.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/events_statements_summary_by_digest.frm to /backup/2019-09-04_18-01-55/performance_schema/events_statements_summary_by_digest.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/session_account_connect_attrs.frm to /backup/2019-09-04_18-01-55/performance_schema/session_account_connect_attrs.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/setup_actors.frm to /backup/2019-09-04_18-01-55/performance_schema/setup_actors.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/events_waits_summary_by_instance.frm to /backup/2019-09-04_18-01-55/performance_schema/events_waits_summary_by_instance.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/setup_instruments.frm to /backup/2019-09-04_18-01-55/performance_schema/setup_instruments.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/events_stages_history_long.frm to /backup/2019-09-04_18-01-55/performance_schema/events_stages_history_long.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/events_waits_history_long.frm to /backup/2019-09-04_18-01-55/performance_schema/events_waits_history_long.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/events_statements_history.frm to /backup/2019-09-04_18-01-55/performance_schema/events_statements_history.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/events_stages_summary_by_host_by_event_name.frm to /backup/2019-09-04_18-01-55/performance_schema/events_stages_summary_by_host_by_event_name.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/events_waits_summary_by_thread_by_event_name.frm to /backup/2019-09-04_18-01-55/performance_schema/events_waits_summary_by_thread_by_event_name.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/events_waits_current.frm to /backup/2019-09-04_18-01-55/performance_schema/events_waits_current.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/cond_instances.frm to /backup/2019-09-04_18-01-55/performance_schema/cond_instances.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/table_io_waits_summary_by_table.frm to /backup/2019-09-04_18-01-55/performance_schema/table_io_waits_summary_by_table.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/events_stages_summary_by_account_by_event_name.frm to /backup/2019-09-04_18-01-55/performance_schema/events_stages_summary_by_account_by_event_name.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/events_waits_summary_global_by_event_name.frm to /backup/2019-09-04_18-01-55/performance_schema/events_waits_summary_global_by_event_name.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/threads.frm to /backup/2019-09-04_18-01-55/performance_schema/threads.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/events_stages_summary_by_thread_by_event_name.frm to /backup/2019-09-04_18-01-55/performance_schema/events_stages_summary_by_thread_by_event_name.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/session_connect_attrs.frm to /backup/2019-09-04_18-01-55/performance_schema/session_connect_attrs.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/events_statements_summary_global_by_event_name.frm to /backup/2019-09-04_18-01-55/performance_schema/events_statements_summary_global_by_event_name.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/db.opt to /backup/2019-09-04_18-01-55/performance_schema/db.opt
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/events_waits_summary_by_host_by_event_name.frm to /backup/2019-09-04_18-01-55/performance_schema/events_waits_summary_by_host_by_event_name.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/file_summary_by_instance.frm to /backup/2019-09-04_18-01-55/performance_schema/file_summary_by_instance.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/events_waits_summary_by_account_by_event_name.frm to /backup/2019-09-04_18-01-55/performance_schema/events_waits_summary_by_account_by_event_name.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/events_stages_summary_global_by_event_name.frm to /backup/2019-09-04_18-01-55/performance_schema/events_stages_summary_global_by_event_name.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/events_stages_current.frm to /backup/2019-09-04_18-01-55/performance_schema/events_stages_current.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/events_waits_history.frm to /backup/2019-09-04_18-01-55/performance_schema/events_waits_history.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/socket_instances.frm to /backup/2019-09-04_18-01-55/performance_schema/socket_instances.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/events_statements_summary_by_host_by_event_name.frm to /backup/2019-09-04_18-01-55/performance_schema/events_statements_summary_by_host_by_event_name.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/setup_consumers.frm to /backup/2019-09-04_18-01-55/performance_schema/setup_consumers.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/socket_summary_by_instance.frm to /backup/2019-09-04_18-01-55/performance_schema/socket_summary_by_instance.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/events_statements_summary_by_thread_by_event_name.frm to /backup/2019-09-04_18-01-55/performance_schema/events_statements_summary_by_thread_by_event_name.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/rwlock_instances.frm to /backup/2019-09-04_18-01-55/performance_schema/rwlock_instances.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/events_stages_summary_by_user_by_event_name.frm to /backup/2019-09-04_18-01-55/performance_schema/events_stages_summary_by_user_by_event_name.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/events_stages_history.frm to /backup/2019-09-04_18-01-55/performance_schema/events_stages_history.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 [01] Copying ./performance_schema/setup_timers.frm to /backup/2019-09-04_18-01-55/performance_schema/setup_timers.frm
160904 18:01:58 [01]        ...done
160904 18:01:58 Finished backing up non-InnoDB tables and files
160904 18:01:58 [00] Writing xtrabackup_binlog_info
160904 18:01:58 [00]        ...done
160904 18:01:58 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1626027'
xtrabackup: Stopping log copying thread.
.160904 18:01:58 >> log scanned up to (1626027)

160904 18:01:58 Executing UNLOCK TABLES
160904 18:01:58 All tables unlocked
160904 18:01:58 Backup created in directory '/backup/2019-09-04_18-01-55'
MySQL binlog position: filename 'mysql-bin.000012', position '262'
160904 18:01:58 [00] Writing backup-my.cnf
160904 18:01:58 [00]        ...done
160904 18:01:58 [00] Writing xtrabackup_info
160904 18:01:58 [00]        ...done
xtrabackup: Transaction log of lsn (1626027) to (1626027) was copied.
160904 18:01:59 completed OK!

最后的completed OK!表示成功

二、使用innobackupex預備全備

一般情況下,在備份完成后,數據尚且不能用于恢復操作,因為備份的數據中可能會包含尚未提交的事務或已經提交但尚未同步至數據文件中的事務。因此,此時數據文件仍處理不一致狀態。“準備”的主要作用正是通過回滾未提交的事務及同步已經提交的事務至數據文件也使得數據文件處于一致性狀態。讀取備份文件夾中的配置文件,然后innobackupex重做已提交事務,回滾未提交事務,之后數據就被寫到了備份的數據文件(innodb文件)中,并重建日志文件。這一步隱式調用了2次xtrabackup –prepare。

語法:
innobackupex --apply-log --use-memory=4G /path/to/BACKUP-DIR/日期文件夾

例子:
[[email protected] backup]# innobackupex --apply-log /backup/2019-09-04_18-01-55/
160904 18:04:24 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.4 based on MySQL server 5.7.13 Linux (x86_64) (revision id: df58cf2)
xtrabackup: cd to /backup/2019-09-04_18-01-55
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(1626027)
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 __sync_synchronize() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.3
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: The log sequence number 1626007 in the system tablespace does not match the log sequence number 1626027 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Doing recovery: scanned up to log sequence number 1626027 (0%)
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: 5.7.13 started; log sequence number 1626027

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1626046
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 __sync_synchronize() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.3
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=1626046
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 1626124
InnoDB: Doing recovery: scanned up to log sequence number 1626133 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 1626133 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
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: 5.7.13 started; log sequence number 1626133
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1626152
160904 18:04:27 completed OK!

如果執行正確,其最后輸出的幾行信息通常如下:completed OK!


在實現“準備”的過程中,innobackupex 通常還可以使用 --use-memory 選項來指定其可以使用的內存的大小,默認通常為 100M。如果有足夠的內存可用,可以多劃分一些內存給 prepare 的過程,以提高其完成速度。

三、使用innobackupex還原備份

innobackupex命令的--copy-back選項用于執行恢復操作,其通過復制所有數據相關的文件至mysql服務器DATADIR目錄中來執行恢復過程。innobackupex通過backup-my.cnf來獲取DATADIR目錄的相關信息。

語法:
innobackupex --copy-back /path/to/BACKUP-DIR
innobackupex --copy-back --datadir=恢復的數據目錄 /path/to/BACKUP-DIR

注:
1.datadir必須是為空的,innobackupex –copy-back不會覆蓋已存在的文件
2.還原時需要先關閉服務,如果服務是啟動的,那么就不能還原到datadir。

例子
[[email protected] mysql]# innobackupex --copy-back /backup/2019-09-04_18-01-55/
160904 18:06:31 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.
          At the end of a successful copy-back run innobackupex
          prints "completed OK!".

innobackupex version 2.4.4 based on MySQL server 5.7.13 Linux (x86_64) (revision id: df58cf2)
160904 18:06:31 [01] Copying ib_logfile0 to /var/lib/mysql/ib_logfile0
160904 18:06:31 [01]        ...done
160904 18:06:31 [01] Copying ib_logfile1 to /var/lib/mysql/ib_logfile1
160904 18:06:31 [01]        ...done
160904 18:06:31 [01] Copying ibdata1 to /var/lib/mysql/ibdata1
160904 18:06:31 [01]        ...done
160904 18:06:31 [01] Copying ./test/db.opt to /var/lib/mysql/test/db.opt
160904 18:06:31 [01]        ...done
160904 18:06:31 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
160904 18:06:31 [01]        ...done
160904 18:06:31 [01] Copying ./ibtmp1 to /var/lib/mysql/ibtmp1
160904 18:06:31 [01]        ...done
160904 18:06:31 [01] Copying ./mysql/servers.frm to /var/lib/mysql/mysql/servers.frm
160904 18:06:31 [01]        ...done
160904 18:06:31 [01] Copying ./mysql/slave_relay_log_info.ibd to /var/lib/mysql/mysql/slave_relay_log_info.ibd
160904 18:06:31 [01]        ...done
160904 18:06:31 [01] Copying ./mysql/tables_priv.MYD to /var/lib/mysql/mysql/tables_priv.MYD
160904 18:06:31 [01]        ...done
160904 18:06:31 [01] Copying ./mysql/help_keyword.frm to /var/lib/mysql/mysql/help_keyword.frm
160904 18:06:31 [01]        ...done
160904 18:06:31 [01] Copying ./mysql/ndb_binlog_index.MYD to /var/lib/mysql/mysql/ndb_binlog_index.MYD
160904 18:06:31 [01]        ...done
160904 18:06:31 [01] Copying ./mysql/columns_priv.MYD to /var/lib/mysql/mysql/columns_priv.MYD
160904 18:06:31 [01]        ...done
160904 18:06:31 [01] Copying ./mysql/servers.MYD to /var/lib/mysql/mysql/servers.MYD
160904 18:06:31 [01]        ...done
160904 18:06:31 [01] Copying ./mysql/user.MYD to /var/lib/mysql/mysql/user.MYD
160904 18:06:31 [01]        ...done
160904 18:06:31 [01] Copying ./mysql/event.frm to /var/lib/mysql/mysql/event.frm
160904 18:06:31 [01]        ...done
160904 18:06:31 [01] Copying ./mysql/time_zone_transition.MYD to /var/lib/mysql/mysql/time_zone_transition.MYD
160904 18:06:31 [01]        ...done
160904 18:06:31 [01] Copying ./mysql/proc.MYI to /var/lib/mysql/mysql/proc.MYI
160904 18:06:31 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/slave_worker_info.ibd to /var/lib/mysql/mysql/slave_worker_info.ibd
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/time_zone_leap_second.MYI to /var/lib/mysql/mysql/time_zone_leap_second.MYI
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/help_topic.MYI to /var/lib/mysql/mysql/help_topic.MYI
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/time_zone_transition.MYI to /var/lib/mysql/mysql/time_zone_transition.MYI
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/general_log.CSV to /var/lib/mysql/mysql/general_log.CSV
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/slave_master_info.ibd to /var/lib/mysql/mysql/slave_master_info.ibd
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/time_zone_transition.frm to /var/lib/mysql/mysql/time_zone_transition.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/func.MYD to /var/lib/mysql/mysql/func.MYD
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/user.MYI to /var/lib/mysql/mysql/user.MYI
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/help_relation.MYI to /var/lib/mysql/mysql/help_relation.MYI
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/plugin.frm to /var/lib/mysql/mysql/plugin.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/time_zone_name.frm to /var/lib/mysql/mysql/time_zone_name.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/ndb_binlog_index.MYI to /var/lib/mysql/mysql/ndb_binlog_index.MYI
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/help_relation.MYD to /var/lib/mysql/mysql/help_relation.MYD
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/plugin.MYD to /var/lib/mysql/mysql/plugin.MYD
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/help_category.MYD to /var/lib/mysql/mysql/help_category.MYD
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/event.MYI to /var/lib/mysql/mysql/event.MYI
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/event.MYD to /var/lib/mysql/mysql/event.MYD
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/procs_priv.MYI to /var/lib/mysql/mysql/procs_priv.MYI
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/slow_log.frm to /var/lib/mysql/mysql/slow_log.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/help_keyword.MYI to /var/lib/mysql/mysql/help_keyword.MYI
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/user.frm to /var/lib/mysql/mysql/user.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/slow_log.CSV to /var/lib/mysql/mysql/slow_log.CSV
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/help_keyword.MYD to /var/lib/mysql/mysql/help_keyword.MYD
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/plugin.MYI to /var/lib/mysql/mysql/plugin.MYI
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/func.frm to /var/lib/mysql/mysql/func.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/innodb_index_stats.ibd to /var/lib/mysql/mysql/innodb_index_stats.ibd
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/proxies_priv.MYD to /var/lib/mysql/mysql/proxies_priv.MYD
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/time_zone_leap_second.frm to /var/lib/mysql/mysql/time_zone_leap_second.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/slave_master_info.frm to /var/lib/mysql/mysql/slave_master_info.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/help_relation.frm to /var/lib/mysql/mysql/help_relation.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/func.MYI to /var/lib/mysql/mysql/func.MYI
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/procs_priv.frm to /var/lib/mysql/mysql/procs_priv.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/proc.frm to /var/lib/mysql/mysql/proc.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/innodb_index_stats.frm to /var/lib/mysql/mysql/innodb_index_stats.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/db.MYD to /var/lib/mysql/mysql/db.MYD
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/db.frm to /var/lib/mysql/mysql/db.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/columns_priv.MYI to /var/lib/mysql/mysql/columns_priv.MYI
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/time_zone.frm to /var/lib/mysql/mysql/time_zone.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/columns_priv.frm to /var/lib/mysql/mysql/columns_priv.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/slave_worker_info.frm to /var/lib/mysql/mysql/slave_worker_info.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/time_zone_transition_type.frm to /var/lib/mysql/mysql/time_zone_transition_type.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/help_topic.frm to /var/lib/mysql/mysql/help_topic.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/servers.MYI to /var/lib/mysql/mysql/servers.MYI
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/innodb_table_stats.ibd to /var/lib/mysql/mysql/innodb_table_stats.ibd
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/help_category.frm to /var/lib/mysql/mysql/help_category.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/help_category.MYI to /var/lib/mysql/mysql/help_category.MYI
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/general_log.frm to /var/lib/mysql/mysql/general_log.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/time_zone_leap_second.MYD to /var/lib/mysql/mysql/time_zone_leap_second.MYD
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/tables_priv.MYI to /var/lib/mysql/mysql/tables_priv.MYI
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/time_zone_name.MYI to /var/lib/mysql/mysql/time_zone_name.MYI
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/tables_priv.frm to /var/lib/mysql/mysql/tables_priv.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/time_zone_transition_type.MYI to /var/lib/mysql/mysql/time_zone_transition_type.MYI
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/time_zone_name.MYD to /var/lib/mysql/mysql/time_zone_name.MYD
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/slave_relay_log_info.frm to /var/lib/mysql/mysql/slave_relay_log_info.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/ndb_binlog_index.frm to /var/lib/mysql/mysql/ndb_binlog_index.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/time_zone.MYD to /var/lib/mysql/mysql/time_zone.MYD
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/innodb_table_stats.frm to /var/lib/mysql/mysql/innodb_table_stats.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/proc.MYD to /var/lib/mysql/mysql/proc.MYD
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/time_zone_transition_type.MYD to /var/lib/mysql/mysql/time_zone_transition_type.MYD
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/general_log.CSM to /var/lib/mysql/mysql/general_log.CSM
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/proxies_priv.frm to /var/lib/mysql/mysql/proxies_priv.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/db.MYI to /var/lib/mysql/mysql/db.MYI
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/proxies_priv.MYI to /var/lib/mysql/mysql/proxies_priv.MYI
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/procs_priv.MYD to /var/lib/mysql/mysql/procs_priv.MYD
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/help_topic.MYD to /var/lib/mysql/mysql/help_topic.MYD
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/time_zone.MYI to /var/lib/mysql/mysql/time_zone.MYI
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./mysql/slow_log.CSM to /var/lib/mysql/mysql/slow_log.CSM
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./performance_schema/hosts.frm to /var/lib/mysql/performance_schema/hosts.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./performance_schema/setup_objects.frm to /var/lib/mysql/performance_schema/setup_objects.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./performance_schema/file_instances.frm to /var/lib/mysql/performance_schema/file_instances.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./performance_schema/performance_timers.frm to /var/lib/mysql/performance_schema/performance_timers.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./performance_schema/table_io_waits_summary_by_index_usage.frm to /var/lib/mysql/performance_schema/table_io_waits_summary_by_index_usage.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./performance_schema/events_statements_summary_by_user_by_event_name.frm to /var/lib/mysql/performance_schema/events_statements_summary_by_user_by_event_name.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./performance_schema/objects_summary_global_by_type.frm to /var/lib/mysql/performance_schema/objects_summary_global_by_type.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./performance_schema/events_statements_current.frm to /var/lib/mysql/performance_schema/events_statements_current.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./performance_schema/accounts.frm to /var/lib/mysql/performance_schema/accounts.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./performance_schema/socket_summary_by_event_name.frm to /var/lib/mysql/performance_schema/socket_summary_by_event_name.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./performance_schema/mutex_instances.frm to /var/lib/mysql/performance_schema/mutex_instances.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./performance_schema/table_lock_waits_summary_by_table.frm to /var/lib/mysql/performance_schema/table_lock_waits_summary_by_table.frm
160904 18:06:32 [01]        ...done
160904 18:06:32 [01] Copying ./performance_schema/users.frm to /var/lib/mysql/performance_schema/users.frm
160904 18:06:32 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/file_summary_by_event_name.frm to /var/lib/mysql/performance_schema/file_summary_by_event_name.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/events_statements_history_long.frm to /var/lib/mysql/performance_schema/events_statements_history_long.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/host_cache.frm to /var/lib/mysql/performance_schema/host_cache.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/events_statements_summary_by_account_by_event_name.frm to /var/lib/mysql/performance_schema/events_statements_summary_by_account_by_event_name.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/events_waits_summary_by_user_by_event_name.frm to /var/lib/mysql/performance_schema/events_waits_summary_by_user_by_event_name.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/events_statements_summary_by_digest.frm to /var/lib/mysql/performance_schema/events_statements_summary_by_digest.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/session_account_connect_attrs.frm to /var/lib/mysql/performance_schema/session_account_connect_attrs.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/setup_actors.frm to /var/lib/mysql/performance_schema/setup_actors.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/events_waits_summary_by_instance.frm to /var/lib/mysql/performance_schema/events_waits_summary_by_instance.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/setup_instruments.frm to /var/lib/mysql/performance_schema/setup_instruments.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/events_stages_history_long.frm to /var/lib/mysql/performance_schema/events_stages_history_long.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/events_waits_history_long.frm to /var/lib/mysql/performance_schema/events_waits_history_long.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/events_statements_history.frm to /var/lib/mysql/performance_schema/events_statements_history.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/events_stages_summary_by_host_by_event_name.frm to /var/lib/mysql/performance_schema/events_stages_summary_by_host_by_event_name.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/events_waits_summary_by_thread_by_event_name.frm to /var/lib/mysql/performance_schema/events_waits_summary_by_thread_by_event_name.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/events_waits_current.frm to /var/lib/mysql/performance_schema/events_waits_current.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/cond_instances.frm to /var/lib/mysql/performance_schema/cond_instances.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/table_io_waits_summary_by_table.frm to /var/lib/mysql/performance_schema/table_io_waits_summary_by_table.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/events_stages_summary_by_account_by_event_name.frm to /var/lib/mysql/performance_schema/events_stages_summary_by_account_by_event_name.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/events_waits_summary_global_by_event_name.frm to /var/lib/mysql/performance_schema/events_waits_summary_global_by_event_name.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/threads.frm to /var/lib/mysql/performance_schema/threads.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/events_stages_summary_by_thread_by_event_name.frm to /var/lib/mysql/performance_schema/events_stages_summary_by_thread_by_event_name.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/session_connect_attrs.frm to /var/lib/mysql/performance_schema/session_connect_attrs.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/events_statements_summary_global_by_event_name.frm to /var/lib/mysql/performance_schema/events_statements_summary_global_by_event_name.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/db.opt to /var/lib/mysql/performance_schema/db.opt
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/events_waits_summary_by_host_by_event_name.frm to /var/lib/mysql/performance_schema/events_waits_summary_by_host_by_event_name.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/file_summary_by_instance.frm to /var/lib/mysql/performance_schema/file_summary_by_instance.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/events_waits_summary_by_account_by_event_name.frm to /var/lib/mysql/performance_schema/events_waits_summary_by_account_by_event_name.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/events_stages_summary_global_by_event_name.frm to /var/lib/mysql/performance_schema/events_stages_summary_global_by_event_name.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/events_stages_current.frm to /var/lib/mysql/performance_schema/events_stages_current.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/events_waits_history.frm to /var/lib/mysql/performance_schema/events_waits_history.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/socket_instances.frm to /var/lib/mysql/performance_schema/socket_instances.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/events_statements_summary_by_host_by_event_name.frm to /var/lib/mysql/performance_schema/events_statements_summary_by_host_by_event_name.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/setup_consumers.frm to /var/lib/mysql/performance_schema/setup_consumers.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/socket_summary_by_instance.frm to /var/lib/mysql/performance_schema/socket_summary_by_instance.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/events_statements_summary_by_thread_by_event_name.frm to /var/lib/mysql/performance_schema/events_statements_summary_by_thread_by_event_name.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/rwlock_instances.frm to /var/lib/mysql/performance_schema/rwlock_instances.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/events_stages_summary_by_user_by_event_name.frm to /var/lib/mysql/performance_schema/events_stages_summary_by_user_by_event_name.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/events_stages_history.frm to /var/lib/mysql/performance_schema/events_stages_history.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 [01] Copying ./performance_schema/setup_timers.frm to /var/lib/mysql/performance_schema/setup_timers.frm
160904 18:06:33 [01]        ...done
160904 18:06:33 completed OK!

如果執行正確,其輸出信息的最后幾行通常如下:completed OK! 請確保如上信息的最行一行出現“innobackupex: completed OK!”。


如果服務器剩余空間不足,你可以使用 --move-back替換掉 --copy-back。

啟動數據庫

當數據恢復至DATADIR目錄以后,還需要確保所有數據文件的屬主和屬組均為正確的用戶,如mysql,否則,在啟動mysqld之前還需要事先修改數據文件的屬主和屬組。如:
chown -R mysql.mysql 新的數據目錄

現在可以啟動mysql了

四、Xtrabackup備份文件的說明

查看備份的輸出,使用innobakupex備份時,其會調用xtrabackup備份所有的InnoDB表,復制所有關于表結構定義的相關文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相關文件,同時還會備份觸發器和數據庫配置信息相關的文件。這些文件會被保存至一個以時間命令的目錄中。
[[email protected] backup]# tree -L 2 /backup/
/backup/
└── 2019-09-04_18-01-55
    ├── backup-my.cnf
    ├── ibdata1
    ├── ib_logfile0
    ├── ib_logfile1
    ├── ibtmp1
    ├── mysql
    ├── performance_schema
    ├── test
    ├── xtrabackup_binlog_info
    ├── xtrabackup_checkpoints
    ├── xtrabackup_info
    └── xtrabackup_logfile

4 directories, 9 files

在備份的同時,innobackupex還會在備份目錄中創建如下文件:

(1)xtrabackup_checkpoints

包含備份類型(如完全或增量)、備份狀態(如是否已經為prepared狀態)和LSN(日志序列號)范圍信息;每個InnoDB頁(通常為16k大小)都會包含一個日志序列號,即LSN。LSN是整個數據庫系統的系統版本號,每個頁面相關的LSN能夠表明此頁面最近是如何發生改變的。
[[email protected] 2019-09-04_18-01-55]# cat xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 1626027
last_lsn = 1626027
compact = 0
recover_binlog_info = 0

(2)xtrabackup_info

mysql服務器當前正在使用的二進制日志文件及至備份這一刻為止二進制日志事件的位置。
[[email protected] 2019-09-04_18-01-55]# cat xtrabackup_info
uuid = 9e4c2078-7286-11e6-92a6-0800279b6e4d
name =
tool_name = innobackupex
tool_command = --defaults-file=/etc/my.cnf --user=root --password=... /backup/
tool_version = 2.4.4
ibbackup_version = 2.4.4
server_version = 5.6.30-log
start_time = 2019-09-04 18:01:56
end_time = 2019-09-04 18:01:58
lock_time = 0
binlog_pos = filename 'mysql-bin.000012', position '262'
innodb_from_lsn = 0
innodb_to_lsn = 1626027
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N

(3)xtrabackup_binlog_info

二進制日志文件及用于InnoDB或XtraDB表的二進制日志文件的當前position。
[[email protected] 2019-09-04_18-01-55]# cat xtrabackup_binlog_info
mysql-bin.000012    262

(4)xtrabackup_logfile

開始以來的二進制日志,用于prepare的時候實現一致性

(5)backup-my.cnf

備份命令用到的配置選項信息;
[[email protected] 2019-09-04_18-01-55]# cat backup-my.cnf
# This MySQL options file was generated by innobackupex.

# The MySQL server
[mysqld]
innodb_checksum_algorithm=innodb
innodb_log_checksum_algorithm=innodb
innodb_data_file_path=ibdata1:12M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=50331648
innodb_fast_checksum=false
innodb_page_size=16384
innodb_log_block_size=512
innodb_undo_directory=.
innodb_undo_tablespaces=0
server_id=1

redo_log_version=0

Linux公社的RSS地址http://www.okbuvi.live/rssFeed.aspx

本文永久更新鏈接地址http://www.okbuvi.live/Linux/2019-12/161813.htm

linux
相關資訊       Xtrabackup  MySQL全備 
本文評論   查看全部評論 (0)
表情: 表情 姓名: 字數

       

評論聲明
  • 尊重網上道德,遵守中華人民共和國的各項有關法律法規
  • 承擔一切因您的行為而直接或間接導致的民事或刑事法律責任
  • 本站管理人員有權保留或刪除其管轄留言中的任意內容
  • 本站有權在網站內轉載或引用您的評論
  • 參與本評論即表明您已經閱讀并接受上述條款
海口彩票论坛