Xtrabackup全量备份与恢复mysql数据库

标签: xtrabackup 备份 mysql | 发表时间:2014-01-19 23:08 | 作者:quanliyadan
出处:http://blog.csdn.net

一、Xtrabackup简单概述:

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

XtraBackup优势 :
1、无需停止数据库进行InnoDB热备
2、增量备份MySQL
3、流压缩到传输到其它服务器
4、能比较容易地创建主从同步
5、备份MySQL时不会增大服务器负载


二、Xtrabackup的安装

首先列出以下我当前的服务器环境


下载Xtrabackup

[[email protected] softs]# wget http://www.percona.com/downloads/XtraBackup/LATEST/RPM/rhel5/i386/percona-xtrabackup-2.1.6-702.rhel5.i386.rpm--2014-01-14 10:06:41--  http://www.percona.com/downloads/XtraBackup/LATEST/RPM/rhel5/i386/percona-xtrabackup-2.1.6-702.rhel5.i386.rpm

Resolving www.percona.com... 74.121.199.234
……………………此处省略部分内容输出………………

100%[==========================>] 8,662,225    360K/s   in 32s     

2014-01-14 10:07:43 (1448 KB/s) - `percona-xtrabackup-2.1.6-702.rhel5.i386.rpm' saved [8662225/8662225]

rpm包直接安装

[[email protected] softs]# rpm -ivh percona-xtrabackup-2.1.6-702.rhel5.i386.rpm 
error: Failed dependencies:
        perl(DBD::mysql) is needed by percona-xtrabackup-2.1.6-702.rhel5.i386

这里需要解决依赖包的关系,我直接使用本地yum安装

[[email protected] softs]# yum install perl-DBD-MySQL  -y

[[email protected] softs]# rpm -ivh  percona-xtrabackup-2.1.6-702.rhel5.i386.rpm 
Preparing...                ########################################### [100%]
   1:percona-xtrabackup     ########################################### [100%]

再次尝试安装后成功

安装XtraBackup后,其实会有几个工具:
innobackupex:
这个是其实是下面三个工具的一个perl脚本封装,可以备份MyISAM, InnoDB, XtraDB表。
xtrabackup:
一个由C编译而来的二进制文件,只能备份InnoDB和XtraDB数据。
xbcrypt:
用来加密或解密备份的数据。
xbstream:
用来解压或压缩xbstream格式的压缩文件。
建议使用perl封装的innobackupex来作数据库备份,因为比较容易使用。


三、innobackupex相关参数说明

--defaults-file:指定my.cnf参数文件的位置

--apply-log:同xtrabackup的--prepare参数,一般情况下,在备份完成后,数据尚且不能用于恢复操作,因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此,此时数据 文件仍处理不一致状态。--apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。

--copy-back:做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir

--remote-host=HOSTNAME: 通过ssh将备份数据存储到进程服务器上

--stream=[tar]:备份文件输出格式, 该文件可在XtarBackup binary文件中获得. 在使用参数stream=tar备份的时候,你的xtrabackup_logfile可能会临时放在/tmp目录下,如果你备份的时候并发写入较大的话,xtrabackup_logfile可能会很大(5G+),很可能会占满你的/tmp目录,可以通过参数--tmpdir指定目录来解决这个问题.

--tmpdir=DIRECTORY:当有指定--remote-host or --stream时, 事务日志临时存储的目录, 默认采用MySQL配置文件中所指定的临时目录tmpdir

--redo-only --apply-log:强制备份日志时只redo,跳过rollback,这在做增量备份时非常必要

--use-memory=*:该参数在prepare的时候使用,控制prepare时innodb实例使用的内存

--databases=LIST:列出需要备份的databases,如果没有指定该参数,所有包含MyISAM和InnoDB表的database都会被备份

--slave-info:备份从库, 加上--slave-info备份目录下会多生成一个xtrabackup_slave_info 文件, 这里会保存主日志文件以及偏移, 文件内容类似于:CHANGE MASTER TO MASTER_LOG_FILE='', MASTER_LOG_POS=0

--socket=SOCKET:指定mysql.sock所在位置,以便备份进程登录mysql。

四、全量备份

[[email protected] tmp]# mkdir /backup
[[email protected] tmp]# innobackupex --user=root --password=123456 --defaults-file=/usr/local/mysql/etc/my.cnf --port=3306  /backup

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p

140119 05:46:11  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/usr/local/mysql/etc/my.cnf;mysql_read_default_group=xtrabackup;port=3306' as 'root'  (using password: YES).
140119 05:46:11  innobackupex: Connected to MySQL server
140119 05:46:12  innobackupex: Executing a version check against the server...
140119 05:46:17  innobackupex: Done.
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

innobackupex: Using mysql server version 5.6.12-debug-log

innobackupex: Created backup directory /backup/2014-01-19_05-46-19


140119 05:46:19  innobackupex: Starting ibbackup with command: xtrabackup_56  --defaults-file="/usr/local/mysql/etc/my.cnf"  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/backup/2014-01-19_05-46-19 --tmpdir=/tmp
innobackupex: Waiting for ibbackup (pid=2458) to suspend
innobackupex: Suspend file '/backup/2014-01-19_05-46-19/xtrabackup_suspended_2'


xtrabackup_56 version 2.1.6 for MySQL server 5.6.11 Linux (i686) (revision id: 702)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /database
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
>> log scanned up to (2000813)
InnoDB: Allocated tablespace 2, old maximum was 0
[01] Copying ./ibdata1 to /backup/2014-01-19_05-46-19/ibdata1
>> log scanned up to (2000813)
>> log scanned up to (2000813)
>> log scanned up to (2000813)
>> log scanned up to (2000813)
>> log scanned up to (2000813)
[01]        ...done
[01] Copying ./mysql/innodb_index_stats.ibd to /backup/2014-01-19_05-46-19/mysql/innodb_index_stats.ibd
[01]        ...done
[01] Copying ./mysql/slave_worker_info.ibd to /backup/2014-01-19_05-46-19/mysql/slave_worker_info.ibd
[01]        ...done
[01] Copying ./mysql/innodb_table_stats.ibd to /backup/2014-01-19_05-46-19/mysql/innodb_table_stats.ibd
[01]        ...done
[01] Copying ./mysql/slave_relay_log_info.ibd to /backup/2014-01-19_05-46-19/mysql/slave_relay_log_info.ibd
[01]        ...done
[01] Copying ./mysql/slave_master_info.ibd to /backup/2014-01-19_05-46-19/mysql/slave_master_info.ibd
…………………………此处省略大量内容输出…………………………
innobackupex: Backing up file '/database/supor/productnotes.MYI'
innobackupex: Backing up file '/database/supor/productnotes.frm'
innobackupex: Backing up file '/database/supor/orders.frm'
innobackupex: Backing up file '/database/supor/customers.frm'
innobackupex: Backing up file '/database/supor/orderitems.frm'
innobackupex: Backing up file '/database/supor/productnotes.MYD'
140119 05:46:26  innobackupex: Finished backing up non-InnoDB tables and files

140119 05:46:26  innobackupex: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): '2000813'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (2000813)


xtrabackup: Creating suspend file '/backup/2014-01-19_05-46-19/xtrabackup_log_copied' with pid '2458'
xtrabackup: Transaction log of lsn (2000813) to (2000813) was copied.
140119 05:46:27  innobackupex: All tables unlocked


innobackupex: Backup created in directory '/backup/2014-01-19_05-46-19'
140119 05:46:27  innobackupex: Connection to database server closed
140119 05:46:28  innobackupex: completed OK!


备份成功


五、全量备份恢复
现在我模拟将数据库的所有文件误删除,然后进行数据恢复
[[email protected] database]# rm -rf *
[[email protected] database]# ls
[[email protected] database]# 

[[email protected] database]# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --user=root --password=123456 --apply-log /backup/2014-01-19_05-57-08/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.
IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".

140119 06:00:47  innobackupex: Starting ibbackup with command: xtrabackup_56  --defaults-file="/usr/local/mysql/etc/my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/backup/2014-01-19_05-57-08 --tmpdir=/tmp

xtrabackup_56 version 2.1.6 for MySQL server 5.6.11 Linux (i686) (revision id: 702)
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Not using CPU crc32 instructions
……………………………………………………………………………………
[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:


xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
I……………………………………………………………………………………
innobackupex: Creating directory '/database/mysql'
innobackupex: Copying '/backup/2014-01-19_05-57-08/mysql/help_keyword.frm' to '/database/mysql/help_keyword.frm'
innobackupex: Copying '/backup/2014-01-19_05-57-08/mysql/user.MYI' to '/database/mysql/user.MYI'
innobackupex: Copying '/backup/2014-01-19_05-57-08/mysql/help_relation.frm' to '/database/mysql/help_relation.frm'
innobackupex: Copying '/backup/2014-01-19_05-57-08/mysql/slow_log.CSV' to '/database/mysql/slow_log.CSV'
innobackupex: Copying '/backup/2014-01-19_05-57-08/mysql/time_zone_leap_second.frm' to '/database/mysql/time_zone_leap_second.frm'
innobackupex: Copying '/backup/2014-01-19_05-57-08/mysql/time_zone.MYI' to '/database/mysql/time_zone.MYI'
innobackupex: Copying '/backup/2014-01-19_05-57-08/mysql/time_zone_transition.MYI' to '/database/mysql/time_zone_transition.MYI'
innobackupex: Copying '/backup/2014-01-19_05-57-08/mysql/slave_master_info.frm' to '/database/mysql/slave_master_info.frm'
innobackupex: Copying '/backup/2014-01-19_05-57-08/mysql/help_category.MYI' to '/database/mysql/help_category.MYI'
innobackupex: Copying '/backup/2014-01-19_05-57-08/mysql/procs_priv.MYI' to '/database/mysql/procs_priv.MYI'
innobackupex: Copying '/backup/2014-01-19_05-57-08/mysql/help_category.MYD' to '/database/mysql/help_category.MYD'
innobackupex: Copying '/backup/2014-01-19_05-57-08/mysql/func.MYI' to '/database/mysql/func.MYI'
innobackupex: Copying '/backup/2014-01-19_05-57-08/mysql/tables_priv.frm' to '/database/mysql/tables_priv.frm'
innobackupex: Copying '/backup/2014-01-19_05-57-08/mysql/time_zone_transition.MYD' to '/database/mysql/time_zone_transition.MYD'
…………………………………………此处省略大量数据输出………………………………
innobackupex: Copying '/backup/2014-01-19_05-57-08/performance_schema/performance_timers.frm' to '/database/performance_schema/performance_timers.frm'

innobackupex: Creating directory '/database/test'


innobackupex: Starting to copy InnoDB system tablespace
innobackupex: in '/backup/2014-01-19_05-57-08'
innobackupex: back to original InnoDB data directory '/database'
innobackupex: Copying '/backup/2014-01-19_05-57-08/ibdata1' to '/database/ibdata1'


innobackupex: Starting to copy InnoDB undo tablespaces
innobackupex: in '/backup/2014-01-19_05-57-08'
innobackupex: back to '/database'


innobackupex: Starting to copy InnoDB log files
innobackupex: in '/backup/2014-01-19_05-57-08'
innobackupex: back to original InnoDB log directory '/database'
innobackupex: Copying '/backup/2014-01-19_05-57-08/ib_logfile0' to '/database/ib_logfile0'
innobackupex: Copying '/backup/2014-01-19_05-57-08/ib_logfile1' to '/database/ib_logfile1'
innobackupex: Finished copying back files.

140119 06:01:31  innobackupex: completed OK!   ###恢复完成

接下来检查数据是否恢复
[[email protected] database]# ls
ibdata1  ib_logfile0  ib_logfile1  mysql  performance_schema  test

[[email protected] database]# chown -R mysql.mysql /database/

[[email protected] ~]# /etc/init.d/mysql5 restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL............                                 [  OK  ]

全量备份恢复成功



作者:quanliyadan 发表于2014-1-19 15:08:40 原文链接
阅读:62 评论:0 查看评论

相关 [xtrabackup 备份 mysql] 推荐:

使用Percona XtraBackup备份MySQL

- - searchdatabase
  Percona XtraBackup是一款开放源码,免费的MySQL热备份软件,以非阻塞方式执行备份InnoDB和XtraDB为存储引擎的数据库,是商业备份工具InnoDB Hotbackup的一个很好的替代品. 作者的前一家公司就花了不菲美元买了Hotbackup产品.     下载地址: http://www.searchdatabase.com.cn/softwaredownload_10241.htm.

Mysql备份和恢复的一种可行方案---Xtrabackup

- - SegmentFault 最新的文章
这几天使用Xtrabackup实现了下mysql的全库备份和恢复,这里和大家分享下实现的思路. 关于Xtrabackup(又或innobackupex)的介绍这里就不啰嗦了,感兴趣的同学请移步 官方文档,这里只要知道它提供了mysql备份和恢复的功能就可以了. Xtrabackup提供了全量备份和增量备份两种方式,全量就不解释了,增量是指其可以只备份指定位置后的新增数据.

Xtrabackup全量备份与恢复mysql数据库

- - CSDN博客系统运维推荐文章
一、Xtrabackup简单概述:. Percona  Xtrabackup是开源免费的mysql数据库热备份软件,它能对InnoDB和XtraDB存储引擎的数据库非阻塞地备份(对于MyISAM的备份同样需要加表锁). XtraBackup支持所有的Percona Server、MySQL、MariaDB和Drizzle.

Percona Xtrabackup MySQL 全备

- - CSDN博客数据库推荐文章
作者:ylqmf 发表于2013-3-24 17:29:55 原文链接. 阅读:99 评论:0 查看评论.

xtrabackup 增量备份和部分备份

- - CSDN博客推荐文章
① 首先进行0级备份,记录此时LSN. ② 当进行1级备份时,比较表空间中每个页的LSN是否大于0级备份的LSN,如果是,则备份该页,并记录当前的LSN. 0级备份完,xtrabackup会在备份保存点下的xtrabackup_checkpoints文件里记录一个to_lsn值,该值是备份结束后全库的LSN.

Linux 安装 MySQL / MySQL 主从备份

- - BlogJava-首页技术区
假定所有安装包均在 /share目录,安装目录为 /opt !!. ========================= 安装 MySQL =========================. $ vi /opt/mysql/my.cnf (参考下面配置). $ vi /etc/rc.d/init.d/mysql (参考下面配置).

mysql实现增量备份

- - CSDN博客数据库推荐文章
有点要注意 如果你误删了表 想通过这个恢复 必须恢复日志里面有创建表的日志 不然的话是无法回复的  就是必须是从你开始创建表的时候就已经记录日志了 . 恢复到哪个位置 就按照哪个位置来计算. mysql  5.0不支持增量备份.     mysql数据库会以二进制形式 自动把用户对mysql数据库的操作 记录到文件 当用户希望恢复的时候可以使用备份文件 进行回复.

Percona XtraBackup 1.6.7 发布

- - 开源中国社区最新新闻
Percona XtraBackup 1.6.7 发布,该版本主要是 bug 的修复,详细列表请看 这里. XtraBackup 是一个用来备份 MySQL 的 InnoDB 数据库的开源工具. 例如执行增量备份的命令是:.

Percona XtraBackup InnoDB 備份工具

- - 小惡魔 - 電腦技術 - 工作筆記 - AppleBOY
大家可以選擇透過 yum 或 apt Repository 方式安裝,下面介紹 apt 方式即可. 將 apt 伺服器寫入 /etc/apt/sources.list. VERSION 請至換 Ubuntu Server 版號,如果您想測試實驗性版本請加入底下連結. 根據不同的 MySQL 版本來選擇 XtraBackup 指令,可以參考 Choosing the Right Binary,所以大家不要用錯指令了.

mysql数据备份3种方案

- - 开心平淡对待每一天。热爱生活
   mysql按照备份恢复方式分为逻辑备份和物理备份 逻辑备份是备份sql语句,在恢复的时候执行备份的sql语句实现数据库数据的重现. 物理备份就是备份数据文件了,比较形象点就是cp下数据文件,但真正备份的时候自然不是的cp这么简单. 这2种备份各有优劣,一般来说,物理备份恢复速度比较快,占用空间比较大,逻辑备份速度比较慢,占用空间比较小.