专栏首页阿dai_linux使用xtrabackup完成远程备份 转

使用xtrabackup完成远程备份 转

转载收藏,以防丢失

需求

Can I backup remote databases from my local server
02-27-2013, 06:17 AM
Hi, 
I am using mysqldump so far for taking daily backups of my Production databases. It takes around 6 to 7 hours for 210 GB of data and thus looking for other backup methods. 

I have one single util server where I run mysqldump for all my production servers with one single script. I store my backup files in the same server. It is really easy to maintain. 

Is that possible with Xtrabackup? Do I have to install and run xtrabackup in all my production servers? I can see there are options to transfer the backup files to any remote server. But I need to know whether I can run xtrabackup from a remote server connecting all my database servers as in mysqldump. 

What are the options for it?


Thanks,
Tags: None

解决办法

I have used XtraBackup to back up remote servers to a local server. In fact I use this method a lot when I want to create a slave in a different geographic location. First I would suggest reading the XtraBackup Manual so you understand the differences between mysqldump and XtraBackup as they are very different.

If you determine you can use XtraBackup in your situation then I can share what I have done in the past in hopes that you can use part of it in your situation.

In this example lets says we have two locations or data centers (DC1 and DC2) and that you have a MySQL server (A) at DC1 that you want to back up to DC2.

Because you want to back up a remote server to a local server, I will assume you may be connected over a VPN (which I have used this method for) or other network link.

I suggest using pigz for compression over gzip as pigz can use multiple processors. So if possible, install pigz on A at DC1 ( google pigz )

We are also going to make use of the "stream" option in XtraBackup and netcat (nc) 

Please read the XtraBackup documentation to make sure you select the correct options for your situation. 

At DC1 on A I run:
innobackupex --user=username --password=password --stream=tar ./ | pigz | nc -l 1234

At DC2 on the server I want to receive the back up at I run in the directory I want to store the back up:
nc 1234 | gunzip | tar ixvf - 2> xtrabackup.log

Note, this will store your entire MySQL data directory uncompressed at DC2 on your back up server. This is useful for creating a slave at DC2 and there are great directions on this in teh Xtrabackup Manual.

I have done this with 600 GB databases in 2-3 hours over a VPN and much quicker on a 1GB network ( where A server had 16 cores). 


You could also just send the file like:

innobackupex --user=username --password=password --stream=tar ./ | pigz | ssh user@dc2_server "cat - > /backupdirectory/mysql_backup.tar.gz"


There are other great options you may need such as --slave-info and --safe-slave-backup and if the locking at the end of the back up is an issue you can use the --no-lock option and then use pt-table-checksum and pt-table-sync after you create your slave to bring it in line with your master. This is a bit more advanced, and while I have done it several times, I used Percona Support to do it! ( worth the investment trust me!)

(adsbygoogle = window.adsbygoogle || []).push({});

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

推荐阅读

  • 如何将设计思维应用到精益初创公司的软件开发

    我们所说的设计思维,是指由 IDEO 公司的 Tim Brown 提出,并且正在改变全世界组织的设计思维,简称 DT。(译者注:IDDO,当代最具影响力的设计公司之一)

    Aceyclee
    Serverless无服务器云函数
  • InnoDB 事务加锁分析

    一般大家对数据库事务的了解可能停留在事务的ACID特性以及事务4种不同的隔离级别层面上,而对于事务 4 种不同隔离级别如何实现了解相对较少。

    2020labs小助手
    MySQLSQL数据库MVCMVCC
  • FutureTask 核心源码解析

    研究源码,一般我们都从整体以及实例先入手,再研究细节,不至于一开始就“深陷其中而"当局者迷".

    JavaEdge
    HTTPJava
  • 200行代码落地人脸识别开锁应用

    2019年国庆,帮朋友实现了一个人脸识别进行开锁的功能,用在他的真人实景游戏业务中。几个月来运行稳定,体验良好,借着这个春节宅家的时间,整理一下这个应用的实现过程。

    高树磊
    人脸识别图像处理
  • 滑动验证码攻防对抗

        在业务安全领域,滑动验证码已经是国内继,传统字符型验证码之后的标配。众所周知,打码平台和机器学习这两种绕过验证码的方式,已经是攻击者很主流的思路,不再阐述。冷渗透介绍的是一个冷门的绕过思路和防御方案。这些积累,均来自于实战之中,希望有用。

    周俊辉
    HTTP网络安全安全网站
  • 程序员进阶必读,万字总结Mysql优化精华篇

    price decimal(8,2)有2位小数的定点数,定点数支持很大的数(甚至是超过int,bigint存储范围的数)

    程序员内点事
    全文检索缓存SQL数据库Python
  • 运维转型 | 运维人不再只是“救火英雄”

    各行各业都开启了数字化转型的进程,运维团队在这种时代的浪潮中又该何去何从?我在帮助一些企业落地了运维技术平台之后,开始反思这个问题,并将所思所想整理成本篇文章。

    嘉为科技
    企业运维自动化云计算
  • WEB开发常见的安全漏洞和解决思路

    SQL注入时web开发中最常见也是危害性最大的安全漏洞,SQL注入攻击可能会导致 服务器故障,数据泄漏,数据被恶意删除等等严重后果。

    windwei
    SQLHTTPhttps安全漏洞PHP
  • 详解Winograd变换矩阵生成原理

    文本首发知乎:https://zhuanlan.zhihu.com/p/87516875

    Ldpe2G
    编程算法
  • Linux网络性能优化相关策略

    1. rx-checksumming:校验接收报文的checksum。

    glinuxer
    Linux

扫码关注云+社区

领取腾讯云代金券