xtrabackup远程备份的方法教程
短信预约 -IT技能 免费直播动态提醒
这篇文章主要讲解了“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 --port 3306 --socket=/tmp/mysqld.sock --stream=tar ./ | ssh mysql@192.168.8.63 \ "cat - > /backup_center/$(date +%Y-%m-%d_%H:%m:%S).tar"
。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/user_bak.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/time_zone.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/help_relation.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/time_zone_transition_type.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/help_topic.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/db.MYD to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/slow_log.CSM to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/slow_log.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/general_log.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/slave_relay_log_info.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/tables_priv.MYI to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/ndb_binlog_index.MYD to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/slave_master_info.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/func.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/proc.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/time_zone_name.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/func.MYD to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/procs_priv.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/tables_priv.MYD to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/columns_priv.MYI to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/general_log.CSM to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/ndb_binlog_index.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/gtid_executed.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/procs_priv.MYD to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/func.MYI to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/help_keyword.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/columns_priv.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/proxies_priv.MYI to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/proc.MYI to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/time_zone_transition.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/time_zone_leap_second.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/general_log.CSV to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/servers.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/help_category.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/proxies_priv.MYD to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/event.MYD to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/engine_cost.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/user.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/ndb_binlog_index.MYI to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/db.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/plugin.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/user.MYI to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/columns_priv.MYD to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/innodb_table_stats.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/innodb_index_stats.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/event.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/slow_log.CSV to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/db.MYI to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./mysql/user.MYD to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_transactions_history.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_waits_history.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_statements_summary_by_digest.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/hosts.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/prepared_statements_instances.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/rwlock_instances.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/threads.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/setup_objects.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/objects_summary_global_by_type.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/replication_connection_status.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_statements_summary_by_thread_by_event_name.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/memory_summary_by_host_by_event_name.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_statements_summary_by_host_by_event_name.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/session_variables.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/replication_group_member_stats.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/setup_actors.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/memory_summary_by_account_by_event_name.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_transactions_history_long.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/users.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/setup_consumers.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_stages_history_long.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_waits_summary_global_by_event_name.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_statements_summary_by_program.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/file_instances.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/table_handles.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/variables_by_thread.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/memory_summary_global_by_event_name.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_transactions_summary_by_user_by_event_name.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/table_io_waits_summary_by_index_usage.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_transactions_summary_global_by_event_name.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/status_by_host.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_waits_summary_by_thread_by_event_name.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_stages_summary_by_account_by_event_name.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/session_connect_attrs.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/status_by_account.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/metadata_locks.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_transactions_summary_by_host_by_event_name.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_statements_history_long.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/global_variables.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/table_lock_waits_summary_by_table.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/mutex_instances.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/session_status.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_transactions_summary_by_account_by_event_name.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_stages_summary_by_user_by_event_name.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/table_io_waits_summary_by_table.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_statements_current.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_stages_current.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/replication_connection_configuration.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/memory_summary_by_thread_by_event_name.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/status_by_thread.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/replication_applier_status_by_worker.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/cond_instances.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/performance_timers.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_statements_summary_global_by_event_name.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/accounts.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/session_account_connect_attrs.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_waits_summary_by_instance.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/setup_instruments.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_waits_summary_by_user_by_event_name.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_waits_current.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_stages_summary_by_thread_by_event_name.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_waits_history_long.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/global_status.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/replication_group_members.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_stages_summary_by_host_by_event_name.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/replication_applier_status_by_coordinator.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/file_summary_by_instance.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_transactions_current.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/socket_summary_by_event_name.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/db.opt to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/socket_instances.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_stages_summary_global_by_event_name.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/setup_timers.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/host_cache.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_waits_summary_by_account_by_event_name.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_statements_history.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_transactions_summary_by_thread_by_event_name.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/user_variables_by_thread.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/file_summary_by_event_name.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_statements_summary_by_user_by_event_name.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_stages_history.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/replication_applier_status.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_statements_summary_by_account_by_event_name.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/socket_summary_by_instance.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/status_by_user.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/memory_summary_by_user_by_event_name.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/events_waits_summary_by_host_by_event_name.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 [01] Streaming ./performance_schema/replication_applier_configuration.frm to <STDOUT>
180809 16:23:53 [01] ...done
180809 16:23:53 Finished backing up non-InnoDB tables and files
180809 16:23:53 [00] Streaming <STDOUT>
180809 16:23:53 [00] ...done
180809 16:23:53 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '3504097424'
xtrabackup: Stopping log copying thread.
.180809 16:23:53 >> log scanned up to (3504097433)
180809 16:23:53 Executing UNLOCK TABLES
180809 16:23:53 All tables unlocked
180809 16:23:53 [00] Streaming ib_buffer_pool to <STDOUT>
180809 16:23:53 [00] ...done
180809 16:23:53 Backup created in directory '/backup/full_backup/'
MySQL binlog position: filename 'mysql-bin.000013', position '154'
180809 16:23:53 [00] Streaming <STDOUT>
180809 16:23:53 [00] ...done
180809 16:23:53 [00] Streaming <STDOUT>
180809 16:23:53 [00] ...done
xtrabackup: Transaction log of lsn (3504097424) to (3504097433) was copied.
180809 16:23:53 completed OK!
3、登录备份服务器查看备份文件
[root@oneproxy backup_center]# ll
total 3683656
-rw-rw-r-- 1 mysql mysql 3772058112 Aug 9 16:24 2018-08-09_16:08:13.tar
感谢各位的阅读,以上就是“xtrabackup远程备份的方法教程”的内容了,经过本文的学习后,相信大家对xtrabackup远程备份的方法教程这一问题有了更深刻的体会,具体使用情况还需要大家实践验证。这里是亿速云,小编将为大家推送更多相关知识点的文章,欢迎关注!
免责声明:
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341