xtrabackup远程备份的方法教程

2025-03-11 技术教程 151

这篇文章主要讲解了“xtrabackup远程备份的方法教程”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“xtrabackup远程备份的方法教程”吧！

生产环境中常常将数据库备份至远程服务器，本次测试中，数据库服务器：192.168.8.50，远程备份中心：192.168.8.63

1、配置SSH秘钥认证

ssh-keygen -t rsa

ssh-copy-id -i ~/.ssh/id_rsa.pub mysql@192.168.8.63

2、使用tar进行备份并以时间对备份进行命名

/usr/local/xtrabackup/bin/innobackupex--defaults-file=/etc/my.cnf--user=root--password=mysql--port3306--socket=/tmp/mysqld.sock--stream=tar./|sshmysql@192.168.8.63\"cat->/backup_center/$(date+%Y-%m-%d_%H:%m:%S).tar"。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/user_bak.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/time_zone.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/help_relation.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/time_zone_transition_type.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/help_topic.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/db.MYDto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/slow_log.CSMto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/slow_log.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/general_log.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/slave_relay_log_info.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/tables_priv.MYIto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/ndb_binlog_index.MYDto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/slave_master_info.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/func.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/proc.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/time_zone_name.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/func.MYDto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/procs_priv.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/tables_priv.MYDto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/columns_priv.MYIto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/general_log.CSMto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/ndb_binlog_index.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/gtid_executed.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/procs_priv.MYDto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/func.MYIto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/help_keyword.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/columns_priv.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/proxies_priv.MYIto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/proc.MYIto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/time_zone_transition.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/time_zone_leap_second.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/general_log.CSVto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/servers.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/help_category.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/proxies_priv.MYDto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/event.MYDto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/engine_cost.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/user.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/ndb_binlog_index.MYIto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/db.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/plugin.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/user.MYIto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/columns_priv.MYDto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/innodb_table_stats.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/innodb_index_stats.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/event.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/slow_log.CSVto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/db.MYIto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./mysql/user.MYDto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_transactions_history.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_waits_history.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_statements_summary_by_digest.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/hosts.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/prepared_statements_instances.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/rwlock_instances.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/threads.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/setup_objects.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/objects_summary_global_by_type.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/replication_connection_status.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_statements_summary_by_thread_by_event_name.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/memory_summary_by_host_by_event_name.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_statements_summary_by_host_by_event_name.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/session_variables.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/replication_group_member_stats.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/setup_actors.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/memory_summary_by_account_by_event_name.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_transactions_history_long.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/users.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/setup_consumers.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_stages_history_long.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_waits_summary_global_by_event_name.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_statements_summary_by_program.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/file_instances.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/table_handles.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/variables_by_thread.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/memory_summary_global_by_event_name.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_transactions_summary_by_user_by_event_name.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/table_io_waits_summary_by_index_usage.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_transactions_summary_global_by_event_name.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/status_by_host.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_waits_summary_by_thread_by_event_name.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_stages_summary_by_account_by_event_name.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/session_connect_attrs.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/status_by_account.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/metadata_locks.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_transactions_summary_by_host_by_event_name.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_statements_history_long.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/global_variables.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/table_lock_waits_summary_by_table.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/mutex_instances.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/session_status.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_transactions_summary_by_account_by_event_name.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_stages_summary_by_user_by_event_name.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/table_io_waits_summary_by_table.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_statements_current.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_stages_current.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/replication_connection_configuration.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/memory_summary_by_thread_by_event_name.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/status_by_thread.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/replication_applier_status_by_worker.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/cond_instances.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/performance_timers.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_statements_summary_global_by_event_name.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/accounts.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/session_account_connect_attrs.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_waits_summary_by_instance.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/setup_instruments.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_waits_summary_by_user_by_event_name.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_waits_current.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_stages_summary_by_thread_by_event_name.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_waits_history_long.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/global_status.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/replication_group_members.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_stages_summary_by_host_by_event_name.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/replication_applier_status_by_coordinator.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/file_summary_by_instance.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_transactions_current.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/socket_summary_by_event_name.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/db.optto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/socket_instances.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_stages_summary_global_by_event_name.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/setup_timers.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/host_cache.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_waits_summary_by_account_by_event_name.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_statements_history.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_transactions_summary_by_thread_by_event_name.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/user_variables_by_thread.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/file_summary_by_event_name.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_statements_summary_by_user_by_event_name.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_stages_history.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/replication_applier_status.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_statements_summary_by_account_by_event_name.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/socket_summary_by_instance.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/status_by_user.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/memory_summary_by_user_by_event_name.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/events_waits_summary_by_host_by_event_name.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53[01]Streaming./performance_schema/replication_applier_configuration.frmto<STDOUT>18080916:23:53[01]...done18080916:23:53Finishedbackingupnon-InnoDBtablesandfiles18080916:23:53[00]Streaming<STDOUT>18080916:23:53[00]...done18080916:23:53ExecutingFLUSHNO_WRITE_TO_BINLOGENGINELOGS...xtrabackup:Thelatestcheckpoint(forincremental):'3504097424'xtrabackup:Stoppinglogcopyingthread..18080916:23:53>>logscannedupto(3504097433)18080916:23:53ExecutingUNLOCKTABLES18080916:23:53Alltablesunlocked18080916:23:53[00]Streamingib_buffer_poolto<STDOUT>18080916:23:53[00]...done18080916:23:53Backupcreatedindirectory'/backup/full_backup/'MySQLbinlogposition:filename'mysql-bin.000013',position'154'18080916:23:53[00]Streaming<STDOUT>18080916:23:53[00]...done18080916:23:53[00]Streaming<STDOUT>18080916:23:53[00]...donextrabackup:Transactionlogoflsn(3504097424)to(3504097433)wascopied.18080916:23:53completedOK!

3、登录备份服务器查看备份文件

[root@oneproxybackup_center]#lltotal3683656-rw-rw-r--1mysqlmysql3772058112Aug916:242018-08-09_16:08:13.tar

感谢各位的阅读，以上就是“xtrabackup远程备份的方法教程”的内容了，经过本文的学习后，相信大家对xtrabackup远程备份的方法教程这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！