首页 > 代码库 > xtrabackup热备和恢复mysql

xtrabackup热备和恢复mysql


热备份与恢复

3.1xtrabackup介绍:

热备份的方式也是直接复制数据物理文件,和冷备份一样,但是热备份可以不停机直接复制,一般用于7*24小时不间断的重要核心业务。Mysql的社区版本热备份工具InnoDB Hot Backup是付费的,只能试用30天,商业版才可以永久的使用,

Percona公司发布一个xtrabackup热备工具,和付费的工具一样,支持在线热备(备份时不影响数据的读写)是商业工具InnoDB Hot Backup的一个很好的替代。

xtrabackup热备工具是一个开源的工具,他可以非常快速的备份和恢复mysql数据库。Xrabackup 中包含两个工具: 

  1. xtrabackup适用于热备份InnoDBXtraDB表中数据的工具,不能备份其他类型的表,也不能备份数据表结构

  2. innobakupex是将xtrabackup进行封装的perl脚本,他提供备份MyISAM表的能力,由于innobakupex功能更完善,所以一般采用innobakupex来进行备份,但在处理myisam时需要加一个读锁。并且加入了一些使用的选项。如slave-info可以记录备份恢复后,作为slave需要的一些信息,根据这些信息,可以很方便的利用备份来重做slave

Xtrabackup可以做什么:

在线()备份整个库的InnoDB XtraDB

xtrabackup的上一次整库备份基础上做增量备份(innodbonly)

以流的形式产生备份,可以直接保存到远程机器上(本机硬盘空间不足时很有用)

MySQL数据库本身提供的工具并不支持真正的增量备份,二进制日志恢复是point-in-time(时间点)的恢复而不是增量备份。Xtrabackup工具支持对InnoDB存储引擎的增量备份,工作原理如下:

(1)首先完成一个完全备份,并记录下此时检查点的LSN(LogSequenceNumber)

(2)在进程增量备份时,比较表空间中每个页的LSN是否大于上次备份时的LSN,如果是,则备份该页,同时记录当前检查点的LSN

  首先,在logfile中找到并记录最后一个checkpoint(last checkpoint LSN),然后开始从LSN的位置开始拷贝InnoDBlogfilextrabackup_logfile;接着,开始拷贝全部的数据文件.ibd;在拷贝全部数据文件结束之后,才停止拷贝logfile

 

  因为logfile里面记录全部的数据修改情况,所以,即时在备份过程中数据文件被修改过了,恢复时仍然能够通过解析xtrabackup_logfile保持数据的一致。

3.2Xtrabackup备份原理

XtraBackup基于InnoDBcrash-recovery功能。它会复制innodbdatafile,由于不锁表,复制出来的数据是不一致的,在恢复的时候使用crash-recovery,使得数据恢复一致。

InnoDB维护了一个redo log,又称为transactionlog,事务日志,它包含了innodb数据的所有改动情况。当InnoDB启动的时候,它会先去检查data filetransaction log,并且会做二步操作:

XtraBackup在备份的时候,一页一页地复制innodb的数据,而且不锁定表,与此同时,XtraBackup还有另外一个线程监视着transactions log,一旦log发生变化,就把变化过的logpages复制走。为什么要急着复制走呢?因为transactions log文件大小有限,写满之后,就会从头再开始写,所以新数据可能会覆盖到旧的数据。 

  在prepare过程中,XtraBackup使用复制到的transactions log对备份出来的innodb data file进行crash recovery 

3.3实现细节 

XtraBackupread-write模式打开innodb的数据文件,然后对其进行复制。其实它不会修改此文件。也就是说,运行XtraBackup的用户,必须对innodb的数据文件具有读写权限。之所以采用read-write模式是因为XtraBackup采用了其内置的 innodb库来打开文件,而innodb库打开文件的时候就是rw的。 

XtraBackup要从文件系统中复制大量的数据,所以它尽可能地使用posix_fadvise(),来告诉OS不要缓存读取到的数据,从而提升性能。因为这些数据不会重用到了,OS却没有这么聪明。如果要缓存一下的话,几个G的数据,会对OS的虚拟内存造成很大的压力,其它进程,比如mysqld很有可能被swap出去,这样系统就会受到很大影响了。

在备份innodbpage的过程中,XtraBackup每次读写1MB的数据,1MB/16KB=64page。这个不可配置。读1MB数据之后,XtraBackup一页一页地遍历这1MB数据,使用innodbbuf_page_is_corrupted()函数检查此页的数据是否正常,如果数据不正常,就重新读取这一页,最多重新读取10次,如果还是失败,备份就失败了,退出。在复制transactionslog的时候,每次读写512KB的数据。同样不可以配置。

3.4xtrabackup工具的安装

1软件下载地址

http://www.percona.com/downloads/软件下载地址

2直接下载二进制包安装

不编译安装,编译安装需要和MySQL源码包配合。

wgethttp://www.percona.com/downloads/XtraBackup/XtraBackup-2.1.9/binary/Linux/x86_64/percona-xtrabackup-2.1.9-744-Linux-x86_64.tar.gz

tar zxvf percona-xtrabackup-2.1.9-744-Linux-x86_64.tar.gz

[root@localhost bin]# cd/opt/percona-xtrabackup-2.1.9-Linux-x86_64/bin/

[root@localhostpercona-xtrabackup-2.1.9-Linux-x86_64]# ll bin/

总用量 114072

-rwxr-xr-x 1 root root   168821 5   2 2014 innobackupex

lrwxrwxrwx 1 root root       12 4   4 10:11 innobackupex-1.5.1 ->innobackupex

-rwxr-xr-x 1 root root  2226551 5   2 2014 xbcrypt

-rwxr-xr-x 1 root root  2300304 5   2 2014 xbstream

-rwxr-xr-x 1 root root 13178050 5   2 2014 xtrabackup

-rwxr-xr-x 1 root root 16598470 5   2 2014 xtrabackup_55

-rwxr-xr-x 1 root root 82321834 5   2 2014 xtrabackup_56


cp innobackupex-1.5.1 /usr/bin/innobackupex

cp xtrabackup_55 /usr/bin/xtrabackup

cp xtrabackup /usr/bin

安装相关插件

yum install perl-DBI -y

yum install perl-DBD-MySQL -y

yum install perl-Time-HiRes -y

yum install perl-IO-Socket-SSL –y

 

yum install perl-TermReadKey.x86_64 -y

3.5 innobackupex参数介绍:

--defaults-file=/etc/my.cnf:指定my.cnf配置文件位置

--user=root:指定链接数据库的用户名

--apply-log:对xtrabackup—prepare参数的封装

--copy-back:做数据恢复时把备份文件拷贝到mysql服务器的datadir目录下

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

--stream:通过指定的数据格式将备份的数据输出到标准输出

--tmpdir:当指定了—remote-host或者是—stream参数后,事务日志需要临时存储到本地磁盘,此参数默认使用了mysql服务器的配置

--use-memory:此参数结合ibbackup使用,类似于xrtabackup的参数use-memory参数

--throttle=IOS:同xtrabackup的参数throttle

--sleep:是给ibbackup使用的,指定每备份1M数据,进程停止考呗多少秒,也是为了减少对正常业务的影响,具体查看ibbackup的手册

-compass:对备份的数据进行压缩,仅仅支持ibbackupxtrabackup还没有实现

--include=REGEXPxtrabackup参数的封装,也支持ibbackup

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

--password="*****":访问mysql的用户口令,当mysqlroot口令为空时省略password参数。

PORT=3306 :指定mysql监听的端口

--slave-infoslave-info可以记录备份恢 复后,作为slave需要的一些信息,根据这些信息,可以很方便的利用备份来重做slave

/mysql_backup:备份位置

--socket=SOCKET mysql服务器的socket文件的位置

 

2>/mysql_backup/ innobackupex.log:记录备份时的输出 

--databases=数据库名:使用这个参数,针对某个数据库进行备份,如果不加这个参数默认就是全部的库备份

innobackupex  --defaults-file= /data/mysqldata/3307/my.cnf--user=root --password= zywerj@11erwe --databases="cooolyisydb" /backup/full_data/

3.6实例演示操作:

innobackupex  --defaults-file= /data/mysqldata/3307/my.cnf--user=root --password= zywerj@11erwe --databases="cooolyisydb"  /backup/full_data/

 

[root@localhost backup]# innobackupex  --defaults-file= /data/mysqldata/3307/my.cnf--user=root --password=zywerj@11erwe --databases="cooolyisydb"/backup/full_data/

Can‘t locate Time/HiRes.pm in @INC (@INC contains:/usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl/usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at/usr/bin/innobackupex line 23.

BEGIN failed--compilation aborted at/usr/bin/innobackupex line 23.

 

报错解决办法:

yum -y install perl-Time-HiRes

参考:http://blog.csdn.net/lwei_998/article/details/40896321

1.单库备份及恢复过程:

创建备份数据存放的目录:

mkdir -p/backup/{full_data,dk_data,zl_data}

注:full_data 全库备份目录

dk_data 单库备份目录

zl_data 增量备份目录

 

[root@localhost ~]# time innobackupex--defaults-file=/etc/my.cnf --user=root --password=Zy@634#R0ot--socket=/tmp/mysql.sock --port=3306 --databases="mahtu" --slave-info/backup/full_data/

 

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright2003, 2009 Innobase Oy

and Percona LLC and/or its affiliates2009-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

 

170404 13:28:55 innobackupex: Connecting to MySQL server with DSN‘dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/tmp/mysql.sock‘as ‘root‘  (using password: YES).

170404 13:28:55 innobackupex: Connected to MySQL server

170404 13:28:55 innobackupex: Executing a version check against the server...

170404 13:28:58 innobackupex: Done.

IMPORTANT: Please check that the backup runcompletes successfully.

           Atthe end of a successful backup run innobackupex

          prints "completed OK!".

 

innobackupex: Using mysql server version 5.5.48

 

innobackupex: Created backup directory /backup/full_data/2017-04-04_13-28-58

 

170404 13:28:58 innobackupex: Starting ibbackup with command: xtrabackup_55  --defaults-file="/etc/my.cnf"  --defaults-group="mysqld" --backup--suspend-at-end --target-dir=/backup/full_data/2017-04-04_13-28-58 --tmpdir=/tmp

innobackupex: Waiting for ibbackup (pid=26398) tosuspend

innobackupex: Suspend file‘/backup/full_data/2017-04-04_13-28-58/xtrabackup_suspended_2‘

 

xtrabackup_55 version 2.1.9 for Percona Server5.5.35 Linux (x86_64) (revision id: 744)

xtrabackup: uses posix_fadvise().

xtrabackup: cd to /data/mysql/

xtrabackup: open files limit requested 65535, setto 65535

xtrabackup: using the following InnoDBconfiguration:

xtrabackup:  innodb_data_home_dir = /usr/local/mysql/var

xtrabackup:  innodb_data_file_path = ibdata1:10M:autoextend

xtrabackup:  innodb_log_group_home_dir = /usr/local/mysql/var

xtrabackup:  innodb_log_files_in_group = 2

xtrabackup:  innodb_log_file_size = 268435456

>> log scanned up to (619371504)

[01] Copying/usr/local/mysql/var/ibdata1 to /backup/full_data/2017-04-04_13-28-58/ibdata1

>> log scanned up to (619371504)

.......................

>> log scanned up to (619371544)

[01]       ...done

>> log scanned up to (619371544)

xtrabackup: Creating suspend file‘/backup/full_data/2017-04-04_13-28-58/xtrabackup_suspended_2‘ with pid ‘26398‘

 

170404 13:29:13 innobackupex: Continuing after ibbackup has suspended

170404 13:29:13 innobackupex: Starting to lock all tables...

>> log scanned up to (619371544)

170404 13:29:13 innobackupex: All tables locked and flushed to disk

170404 13:29:13 innobackupex: Starting to backup non-InnoDB tables and files

innobackupex: in subdirectories of ‘/data/mysql/‘

innobackupex: Backing up files‘/data/mysql//mahtu/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}‘(747 files)

>> log scanned up to (619371544)

...............

>> log scanned up to (619371544)

170404 13:29:19 innobackupex: Finished backing up non-InnoDB tables and files

170404 13:29:19 innobackupex: Waiting for log copying to finish

xtrabackup: The latest check point (forincremental): ‘619371544‘

xtrabackup: Stopping log copying thread.

.>> log scanned up to (619371544)

 

xtrabackup: Creating suspend file‘/backup/full_data/2017-04-04_13-28-58/xtrabackup_log_copied‘ with pid ‘26398‘

xtrabackup: Transaction log of lsn (619371135) to(619371544) was copied.

170404 13:29:20 innobackupex: All tables unlocked

 

innobackupex: Backup created in directory‘/backup/full_data/2017-04-04_13-28-58‘

innobackupex: MySQL slave binlog position: masterhost ‘192.168.10.20‘, filename ‘mysql-bin.000001‘, position 35593612

170404 13:29:20 innobackupex: Connection to database server closed

170404 13:29:20 innobackupex: completed OK!

 

real  0m25.738s

user 0m2.765s

sys   0m3.927s

 

备份数据恢复的报错提示:

[root@localhost 2017-04-04_16-04-46]#innobackupex --defaults-file=/etc/my.cnf --copy-back  /backup/dk_data/2017-04-04_16-04-46/

 

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright2003, 2009 Innobase Oy

and Percona LLC and/or its affiliates2009-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 

IMPORTANT: Please check that the copy-back runcompletes successfully.

           Atthe end of a successful copy-back run innobackupex

           prints "completed OK!".

innobackupex: Error: Original data directory‘/data/mysql/‘ is not empty! at /usr/bin/innobackupex line 2113.

root@localhost mysql]# ps -ef|grep"/data/mysql/"|grep -v grep

root    28100     1  0 14:01 pts/1    00:00:00 /bin/sh/usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql/--pid-file=/data/mysql//localhost.localdomain.pid

mysql   28598 28100 29 14:01 pts/1   01:21:31 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql--datadir=/data/mysql/ --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql--log-error=/data/mysql//localhost.localdomain.err --open-files-limit=65535--pid-file=/data/mysql//localhost.localdomain.pid --socket=/tmp/mysql.sock--port=3306

原因是xtrabackup在恢复mysql的备份数据时,mysql环境必须是全新的环境,没有任何数据的

数据目录为/data/mysql,恢复之前必须把这个目录下的文件全部移走,然后把文件/usr/local/mysql/var/ibdata1 移到其他的目录,这样才可以正常的恢复备份的数据库

 

以下是恢复备份的数据库cooolyisydb

[root@localhost var]# time innobackupex--defaults-file=/etc/my.cnf --copy-back /backup/dk_data/2017-04-04_16-04-46/


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

IMPORTANT: Please check that the copy-back run completes successfully.

           At the end of a successful copy-back run innobackupex

           prints "completed OK!".

innobackupex: Starting to copy files in ‘/backup/dk_data/2017-04-04_16-04-46‘

innobackupex: back to original data directory ‘/data/mysql/‘

innobackupex: Copying ‘/backup/dk_data/2017-04-04_16-04-46/xtrabackup_slave_info‘ to ‘/data/mysql/xtrabackup_slave_info‘

innobackupex: Creating directory ‘/data/mysql/cooolyisydb‘

......................

innobackupex: Starting to copy InnoDB log files

innobackupex: in‘/backup/dk_data/2017-04-04_16-04-46‘

innobackupex: back to original InnoDB log directory‘/usr/local/mysql/var‘

innobackupex: Finished copying back files.

 

170404 17:03:35 innobackupex: completed OK!

 

real  5m25.809s

user 0m0.303s

sys   0m13.316s


[root@localhost ~]# time mysqldump -uroot-p -B mahtu >test_mahtu.sql

Enter password:

real  0m36.932s

user 0m10.967s

sys   0m1.337s

得出结论:innobackupex 备份比mysqldump备份的速度快

3.7压缩备份

xbstream 打包:

[root@localhost bin]# innobackupex  --defaults-file=/etc/my.cnf --user=root--password=Zy@66#erot --stream=xbstream --databases="cooolyisydb"/backup/full_data/ > /backup/full_data/backup.xbstream

--compress压缩参数

[root@localhost full_data]# innobackupex  --defaults-file=/etc/my.cnf --user=root--password=Zy@66##erot --stream=xbstream --compress--databases="cooolyisydb" /backup/full_data/ >/backup/full_data/backup.xbstream

tar 压缩方法:

innobackupex --defaults-file=/data/3307/my.cnf --user=system--password=test3306 --databases="oldboy xtra_test" --stream=tar/backup/data/yasuo/|gzip > /backup/data/yasuo/test.dir_$(date +%F).tar.gz

 

[root@localhost full_data]# innobackupex  --defaults-file=/etc/my.cnf --user=root--password=Zy@66##erot --stream=tar --databases="cooolyisydb"/backup/full_data/|gzip > /backup/full_data/cooolyisydb.$(date +%F).tar.gz

3.8解压备份的方法:

xbstream 解压备份方法:

[root@db02 yasuo]#  xbstream -x </backup/data/yasuo/backup.xbstream -C /mysql.test/ -C指定解压的目录

 

tar解压备份方法:

tar ixf /backup/data/yasuo/test.dir_2016-01-17.tar.gz -C /mysql.test/

 

3.9备份到远端的方法

[root@xtrabackup ~]# innobackupex --compress--stream=xbstream /root/backup/ | ssh oldboy@192.168.1.123 "xbstream -x -C

/root/backup/"

比较三种压缩的方式:

[root@localhost full_data]# du -sh *

5.8G          backup.xbstream

7.3G          backup.xbstream1

2.2G          cooolyisydb.2017-04-04.tar.gz

 

3.10innobackupex 也可以进行分库分表备份

innobackupex  --defaults-file=/data/3307/my.cnf--user=system --password=123456 --databases="mahtu student"/mysql.test/

备份到/mysql.test/

 

参考文档:

http://www.cnblogs.com/galengao/p/5755835.html

https://yq.aliyun.com/articles/45746

 

3.11采用xtrabackup在线扩容mysql-slave

参考资料:

https://yq.aliyun.com/articles/45746?spm=5176.100239.blogcont47287.13.Hl2bkt

 

 


本文出自 “10931853” 博客,请务必保留此出处http://wujianwei.blog.51cto.com/10931853/1934084

xtrabackup热备和恢复mysql