文档章节

使用innobackupex进行mysql备份

阿dai
 阿dai
发布于 2017/08/23 22:21
字数 2009
阅读 142
收藏 0
点赞 0
评论 0

使用innobackupex备份MySQL数据库

安装innobackupex

安装yum扩展源:

[root@adailinux ~]# rpm -ivh  http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

安装innobackupex

[root@adailinux ~]# yum install -y percona-xtrabackup

进行全量备份

说明: 使用xtrabackup进行备份需要使用mysql用户,该用户需要有备份的权限。

创建mysql用户

创建mysql用户:
[root@adailinux ~]# mysql -uroot -p'123456'
Welcome to the MySQL monitor.
mysql> grant RELOAD, LOCK TABLES, REPLICATION CLIENT on *.* to 'bakuser'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.13 sec)
#该用户只需要有备份权限即可,所以在创建用户时只授予其部分权限
mysql> flush privileges;
Query OK, 0 rows affected (0.05 sec)
#刷新!
mysql> quit
Bye

全量备份

创建一个目录用于存放备份文件:
[root@adailinux ~]# mkdir /data/backup

开始备份:
[root@adailinux ~]# innobackupex --defaults-file=/etc/my.cnf --user=bakuser --password='123456' -S /tmp/mysql.sock /data/backup
至此,备份完成!

说明:
--defaults-file:指定mysql的配置文件(该参数必须放在首位)
--user:指定用于备份的mysql用户
--password:mysql用户密码
-S:=socket,指定MySQL的socket文件(也可以使用-h,该参数非必须!)

查看备份文件:

[root@adailinux ~]# cd /data/backup/
[root@adailinux backup]# ls
2017-08-23_08-49-22
[root@adailinux backup]# ls 2017-08-23_08-49-22/
backup-my.cnf  mysql               test                    xtrabackup_info
ibdata1        performance_schema  xtrabackup_checkpoints  xtrabackup_logfile

注: ibdata1为核心文件,其中存放的是:储存格式;INNODB类型数据状态下,ibdata用来储存文件的数据,而库名的文件夹里面的那些表文件只是结构而已。

恢复数据库

在恢复数据库之前需要先停止MySQL服务!

终止mysql服务:
[root@adailinux backup]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS! 

恢复准备:

[root@adailinux backup]# mv /data/mysql /data/mysql.bak
[root@adailinux backup]# mkdir /data/mysql
[root@adailinux backup]# chown mysql:mysql /data/mysql
#进行备份前将原/data/mysql中文件清空
#在此只为学习,所以只对该文件进行更名,然后重建该目录并更改权限

开始恢复:

匹配数据文件:

[root@adailinux backup]# innobackupex --use-memory=256M --apply-log /data/backup/2017-08-23_08-49-22

说明: 进行数据库恢复时,先匹配用于恢复的数据文件。
--use-memory:指定执行数据库恢复操作时的运行内存(添加该选项的目的通过指定其运行内存来加快恢复速度,可不加该参数)。
--apply-log:指定要恢复的数据文件(来自备份文件)

恢复:

[root@adailinux backup]# innobackupex --defaults-file=/etc/my.cnf --copy-back /data/backup/2017-08-23_08-49-22/
至此,数据库恢复完成!

说明:
--copy-back:指定用于恢复的数据文件目录

增量备份

增量备份是在全量备份的基础上进行的。
注: 该过程根据man文档进行操作。

   Incremental Backups with innobackupex
       As  not  all  information  changes between each backup, the incremental backup
       strategy uses this to reduce the storage needs and the duration  of  making  a
       backup.

       This  can  be  done  because  each InnoDB page has a log sequence number, LSN,
       which acts as a version number of the entire database. Every time the database
       is modified, this number gets incremented.

       An incremental backup copies all pages since a specific LSN.

       Once  the pages have been put together in their respective order, applying the
       logs will recreate the process that affected the database, yielding  the  data
       at the moment of the most recently created backup.

   Creating an Incremental Backups with innobackupex
       First,  you  need to make a full backup as the BASE for subsequent incremental
       backups:

          $ innobackupex /data/backups

       This will create a timestamped directory in /data/backups. Assuming  that  the
       backup   is  done  last  day  of  the  month,  BASEDIR  would  be  /data/back‐
       ups/2013-03-31_23-01-18, for example.

       NOTE:
          You can use the innobackupex --no-timestamp option to override this  behav‐
          ior and the backup will be created in the given directory.

       If  you  check  at the xtrabackup-checkpoints file in BASE-DIR, you should see
       something like:

          backup_type = full-backuped
          from_lsn = 0
          to_lsn = 1626007
          last_lsn = 1626007
          compact = 0
          recover_binlog_info = 1

       To create an incremental backup the next day, use the --incremental option and
       provide the BASEDIR:

          $ innobackupex --incremental /data/backups --incremental-basedir=BASEDIR

       and  another  timestamped  directory will be created in /data/backups, in this
       example, /data/backups/2013-04-01_23-01-18 containing the incremental  backup.
       We will call this INCREMENTAL-DIR-1.

       If  you  check  at  the  xtrabackup-checkpoints file in INCREMENTAL-DIR-1, you
       should see something like:

          backup_type = incremental
          from_lsn = 1626007
          to_lsn = 4124244
          last_lsn = 4124244
          compact = 0
          recover_binlog_info = 1

       Creating another incremental backup the next day will be analogous,  but  this
       time the previous incremental one will be base:

          $ innobackupex --incremental /data/backups --incremental-basedir=INCREMENTAL-DIR-1

       Yielding  (in  this  example)  /data/backups/2013-04-02_23-01-18.  We will use
       INCREMENTAL-DIR-2 instead for simplicity.

       At this point, the xtrabackup-checkpoints  file  in  INCREMENTAL-DIR-2  should
       contain something like:

          backup_type = incremental
          from_lsn = 4124244
          to_lsn = 6938371
          last_lsn = 7110572
          compact = 0
          recover_binlog_info = 1

       As  it  was  said  before,  an  incremental  backup only copy pages with a LSN
       greater than a specific value. Providing the LSN would have produced  directo‐
       ries with the same data inside:

          innobackupex --incremental /data/backups --incremental-lsn=4124244
          innobackupex --incremental /data/backups --incremental-lsn=6938371

       This is a very useful way of doing an incremental backup, since not always the
       base or the last incremental will be available in the system.

       WARNING:
          This procedure only affects XtraDB or  InnoDB-based  tables.  Other  tables
          with  a different storage engine, e.g. MyISAM, will be copied entirely each
          time an incremental backup is performed.

   Preparing an Incremental Backup with innobackupex
       Preparing incremental backups is a bit different than full ones. This is, per‐
       haps, the stage where more attention is needed:

          • First,  only  the committed transactions must be replayed on each backup.
            This will merge the base full backup with the incremental ones.

          • Then, the uncommitted transaction must be rolled back in order to have  a
            ready-to-use backup.

       If  you replay the committed transactions and rollback the uncommitted ones on
       the base backup, you will not be able to add the incremental ones. If  you  do
       this on an incremental one, you won’t be able to add data from that moment and
       the remaining increments.

       Having this  in  mind,  the  procedure  is  very  straight-forward  using  the
       --redo-only option, starting with the base backup:

          innobackupex --apply-log --redo-only BASE-DIR

       You should see an output similar to:

          160103 22:00:12 InnoDB: Shutdown completed; log sequence number 4124244
          160103 22:00:12 innobackupex: completed OK!

       Then, the first incremental backup can be applied to the base backup, by issu‐
       ing:

          innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1

       You should see an output similar to the previous one  but  with  corresponding
       LSN:

          160103 22:08:43 InnoDB: Shutdown completed; log sequence number 6938371
          160103 22:08:43 innobackupex: completed OK!

       If  no  --incremental-dir is set, innobackupex will use the most recent subdi‐
       rectory created in the basedir.

       At this moment, BASE-DIR contains the data up  to  the  moment  of  the  first
       incremental backup. Note that the full data will always be in the directory of
       the base backup, as we are appending the increments to it.

       Repeat the procedure with the second one:

          innobackupex --apply-log BASE-DIR --incremental-dir=INCREMENTAL-DIR-2

       If the “completed OK!” message was shown, the final data will be in  the  base
       backup directory, BASE-DIR.

       NOTE:
          --redo-only  should  be  used when merging all incrementals except the last
          one. That’s why the previous line doesn’t contain the  --redo-only  option.
          Even  if  the  --redo-only was used on the last step, backup would still be
          consistent but in that case server would perform the rollback phase.

       You can use this procedure to add more increments to the base, as long as  you
       do  it in the chronological order that the backups were done. If you merge the
       incrementals in the wrong order, the backup  will  be  useless.  If  you  have
       doubts about the order that they must be applied, you can check the file xtra‐
       backup_checkpoints at the directory of each one, as shown in the beginning  of
       this section.

       Once  you  merge  the base with all the increments, you can prepare it to roll
       back the uncommitted transactions:

          innobackupex --apply-log BASE-DIR

       Now your backup is ready to be  used  immediately  after  restoring  it.  This
       preparation  step  is optional. However, if you restore without doing the pre‐
       pare, the database server will begin to rollback uncommitted transactions, the
       same  work  it  would do if a crash had occurred. This results in delay as the
       database server starts, and you can avoid the delay if you do the prepare.

       Note that the iblog* files will not be created by innobackupex,  if  you  want
       them  to  be created, use xtrabackup –prepare on the directory. Otherwise, the
       files will be created by the server once started.
Restoring Incremental Backups with innobackupex
       After preparing the incremental backups, the base directory contains the  same
       as a full one. For restoring it you can use:

          innobackupex --copy-back BASE-DIR

       and   you   may   have   to  change  the  ownership  as  detailed  on  restor‐
       ing_a_backup_ibk.

创建增量备份

备份1:

[root@adailinux mysql]# innobackupex --defaults-file=/etc/my.cnf --user=bakuser --password='123456' --incremental /data/backup --incremental-basedir=/data/backup/2017-08-23_08-49-22

[root@adailinux backup]# ll
总用量 0
drwx------ 7 root root 235 8月  23 22:13 2017-08-23_08-49-22
drwx------ 6 root root 210 8月  23 22:12 2017-08-23_21-56-12

备份2:

[root@adailinux backup]# innobackupex --defaults-file=/etc/my.cnf --user=bakuser --password='123456' --incremental /data/backup --incremental-basedir=/data/backup/2017-08-23_21-56-12

[root@adailinux backup]# ll
总用量 0
drwx------ 7 root root 235 8月  23 22:13 2017-08-23_08-49-22
drwx------ 6 root root 210 8月  23 22:12 2017-08-23_21-56-12
drwx------ 7 root root 223 8月  23 22:13 2017-08-23_22-02-36

合并备份文件

[root@adailinux backup]# innobackupex --apply-log --redo-only /data/backup/2017-08-23_08-49-22

[root@adailinux backup]# innobackupex --apply-log --redo-only /data/backup/2017-08-23_08-49-22 --incremental-dir=/data/backup/2017-08-23_21-56-12

[root@adailinux backup]# innobackupex --apply-log  /data/backup/2017-08-23_08-49-22 --incremental-dir=/data/backup/2017-08-23_22-02-36

注意: 关于--redo-only参数,在进行增量备份文件的合并时,除了 在进行最后一份增量文件的合并时不需要添加该参数 外,其余都要加上该参数!!!

恢复

[root@adailinux backup]# innobackupex --copy-back /data/backup/2017-08-23_08-49-22

© 著作权归作者所有

共有 人打赏支持
阿dai
粉丝 61
博文 200
码字总数 260657
作品 0
昌平
运维
MariaDB之基于Percona Xtrabackup备份大数据库【完整备份与增量备份】(二)

Xtrabackup简介 Percona XtraBackup是开源免费的MySQL数据库热备份软件,它能对InnoDB和XtraDB存储引擎的数据库非阻塞地备份(对于MyISAM的备份同样需要加表锁)。XtraBackup支持所有的Perco...

Linux秋
2014/04/16
0
0
MySQL进阶——使用xtrabackup进行数据库备份

一、安装 1、简介 Xtrabackup是由percona提供的mysql数据库备份工具,据官方介绍,这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。特点: (1)备份过程快速、可靠; ...

蜗牛的嘲讽
2016/11/22
0
0
马哥linux干货分享使用Xtrabackup进行MySQL备份

http://www.magedu.com/ 一、安装 1、简介 Xtrabackup是由percona提供的mysql数据库备份工具,据官方介绍,这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。特点: (1...

小小295813080
2014/08/28
1K
1
使用Xtrabackup进行MySQL备份

使用Xtrabackup进行MySQL备份 2012-05-17 23:54:23 标签:MySQL备份 马哥教育 xtrabackup 原创作品,允许转载,转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法...

cstsncv
2017/11/02
0
0
马哥linux教程分享之使用Xtrabackup进行MySQL备份

一、安装 1、简介 Xtrabackup是由percona提供的mysql数据库备份工具,据官方介绍,这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。特点: (1)备份过程快速、可靠; ...

小小295813080
2014/09/03
3.8K
0
使用Xtrabackup进行MySQL备份

本文系原创,首发于http://mageedu.blog.51cto.com。转载请务必保留链接信息。 一、安装 1、简介 Xtrabackup是由percona提供的mysql数据库备份工具,据官方介绍,这也是世界上惟一一款开源的...

马哥教育
2013/10/11
0
0
生产环境MySQL快速备份工具XtraBackup使用案例

生产环境MySQL快速备份工具XtraBackup使用案例 MySQL的备份有很多种方式,最常用的应该是MySQLdump了,将数据库导出一个 sql文件,还原的时候source db.sql 就欢快的执行了。小伙伴愉快的布上...

人情世故
2016/05/23
159
0
Percona XtraBackup备份mysql数据库 技术手册

作者: 沈小然 版本: 文档编号: 日期:2016年 6月 14日 1 介绍Percona XtraBackup软件 Xtrabackup是由percona开发的一个开源软件,代替商业付费软件MySQL Enterprise Backup (InnoDB Hot B...

LionelShen
2016/06/14
336
0
xtrabackup原理及用法详解

开源的一款数据库备份工具,可以去官网下载并使用 percona-backupxtra对InonoDB会进行增量备份,面对MyISAM的数据进持完全备份。 1 xtrabackup只能备份innodb和xtradb两种引擎的表,而不能备...

小鸟加油
2017/03/29
0
0
innobackupex备份

yum install libaio perl-Time-HiRes perl-DBD-MySQL perl-IO-Socket-SSL yum install percona-xtrabackup rpm -ql percona-xtrabackup |grep bin /usr/bin/innobackupex #将xtrabackup进行封......

潘麟
2016/03/17
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

Java设计模式学习之工厂模式

在Java(或者叫做面向对象语言)的世界中,工厂模式被广泛应用于项目中,也许你并没有听说过,不过也许你已经在使用了。 简单来说,工厂模式的出现源于增加程序序的可扩展性,降低耦合度。之...

路小磊
7分钟前
0
0
npm profile 新功能介绍

转载地址 npm profile 新功能介绍 npm新版本新推来一个功能,npm profile,这个可以更改自己简介信息的命令,以后可以不用去登录网站来修改自己的简介了 具体的这个功能的支持大概是在6这个版...

durban
18分钟前
0
0
Serial2Ethernet Bi-redirection

Serial Tool Serial Tool is a utility for developing serial communications, custom protocols or device testing. You can set up bytes to send accordingly to your protocol and save......

zungyiu
24分钟前
0
0
python里求解物理学上的双弹簧质能系统

物理的模型如下: 在这个系统里有两个物体,它们的质量分别是m1和m2,被两个弹簧连接在一起,伸缩系统为k1和k2,左端固定。假定没有外力时,两个弹簧的长度为L1和L2。 由于两物体有重力,那么...

wangxuwei
39分钟前
0
0
apolloxlua 介绍

##项目介绍 apolloxlua 目前支持javascript到lua的翻译。可以在openresty和luajit里使用。这个工具分为两种模式, 一种是web模式,可以通过网页使用。另外一种是tool模式, 通常作为大规模翻...

钟元OSS
46分钟前
0
0
Mybatis入门

简介: 定义:Mybatis是一个支持普通SQL查询、存储过程和高级映射的持久层框架。 途径:MyBatis通过XML文件或者注解的形式配置映射,实现数据库查询。 特性:动态SQL语句。 文件结构:Mybat...

霍淇滨
54分钟前
0
0
开发技术瓶颈期,如何突破

前言 读书、学习的那些事情,以前我也陆续叨叨了不少,但总觉得 “学习方法” 就是一个永远在路上的话题。个人的能力、经验积累与习惯方法不尽相同,而且一篇文章甚至一本书都很难将学习方法...

_小迷糊
55分钟前
0
0
安装tensorflow-XXX报错

报错: tensorflow-0.5.0-cp27-none-linux_x86_64.whl is not a supported wheel on this platform. 解决: wget https://bootstrap.pypa.io/get-pip.py sudo python2.7 get-pip.py sudo p......

Yao--靠自己
57分钟前
0
0
JVM学习手册(一):JVM模型

一直从事JAVA开发,天天和JVM打交道,仔细想想对JVM还真的不是特别了解,实在是不应该.周六看了许多资料,也算有点心得,记录一下。 JVM内存模型分为5个区域:方法区,堆,虚拟机栈,本地方法栈,程序计...

勤奋的蚂蚁
今天
0
0
转行零基础该如何学Python?这些一定要明白!

转行零基础学Python编程开发难度大吗?从哪学起?近期很多小伙伴问我,如果自己转行学习Python,完全0基础能否学会呢?Python的难度到底有多大?今天,小编就来为大家详细解读一下这个问题。...

猫咪编程
今天
2
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部