文档章节

使用xtrabackup完成远程备份

阿dai
 阿dai
发布于 09/21 00:09
字数 640
阅读 17
收藏 0

转载收藏,以防丢失

需求

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!)

本文转载自:https://www.percona.com/forums/questions-discussions/percona-xtrabackup/8816-can-i-backup-remote-...

共有 人打赏支持
阿dai
粉丝 60
博文 213
码字总数 279880
作品 0
昌平
运维
mysql备份工具innobackupex,xtrabackup-2.1安装与测试

一、Xtrabackup介绍 1.1:Xtrabackup是什么 Xtrabackup是一个对InnoDB做数据备份的工具,支持在线热备份(备份时不影响数据读写),是商业备份工具InnoDB Hotbackup的一个很好的替代品。 Xtra...

SANSOM
2013/09/10
0
3
Xtrabackup--InnoDB备份工具介绍(2)

接上回: mysql> select Host,User,Password from mysql.user;+------------------+--------+-------------------------------------------+| Host | User | Password |+------------------+......

无聊的悟空
2014/02/17
0
0
MySQL备份工具——Xtrabackup之简介

MySQL备份工具——Xtrabackup之简介 一、简介 1、xtrabackup是percona公司开发的一款基于MySQL的开源备份工具,且优于ibbackup。 2、xtradb存储引擎也是percona公司为MySQL开发的,是InnoDB的...

perofu
2014/12/05
0
0
xtrabackup 使用说明(续)

xtrabackup 使用说明(续) 背景: 关于物理备份工具xtrabackup的一些说明可以先看之前写过的文章说明:xtrabackup 安装使用。现在xtrabackup版本升级到了2.4.4,相比之前的2.1有了比较大的变...

xiaocao13140
05/21
0
0
逻辑备份mysqldump和物理备份xtrabackup的流程

mysqldump备份原理 备份的基本流程如下: FLUSH TABLES FLUSH TABLES WITH READ LOCK SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ START TRANSACTION WITH CONSISTENT SNAPSHO......

wdw王大为
05/23
0
0

没有更多内容

加载失败,请刷新页面

加载更多

CentOS7防火墙firewalld操作

firewalld Linux上新用的防火墙软件,跟iptables差不多的工具。 firewall-cmd 是 firewalld 的字符界面管理工具,firewalld是CentOS7的一大特性,最大的好处有两个:支持动态更新,不用重启服...

dingdayu
今天
1
0
关于组件化的最初步

一个工程可能会有多个版本,有国际版、国内版、还有针对各种不同的渠道化的打包版本、这个属于我们日常经常见到的打包差异化版本需求。 而对于工程的开发,比如以前的公司,分成了有三大块业...

DannyCoder
今天
2
0
Spring的Resttemplate发送带header的post请求

private HttpHeaders getJsonHeader() { HttpHeaders headers = new HttpHeaders(); MediaType type = MediaType.parseMediaType("application/json; charset=UTF-8"); ......

qiang123
昨天
3
0
Spring Cloud Gateway 之 Only one connection receive subscriber allowed

都说Spring Cloud Gateway好,我也来试试,可是配置了总是报下面这个错误: java.lang.IllegalStateException: Only one connection receive subscriber allowed. 困扰了我几天的问题,原来...

ThinkGem
昨天
26
0
学习设计模式——观察者模式

1. 认识观察者模式 1. 定义:定义对象之间一种一对多的依赖关系,当一个对象状态发生变化时,依赖该对象的其他对象都会得到通知并进行相应的变化。 2. 组织结构: Subject:目标对象类,会被...

江左煤郎
昨天
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部