Mysql热备xtrabackup的使用

页面导航:首页 > 数据库 > Mysql > Mysql热备xtrabackup的使用

Mysql热备xtrabackup的使用

来源: 作者: 时间:2016-02-20 09:26 【

InnoDB 有个商业的InnoDB Hotbackup,可以对InnoDB引擎的表实现在线热备。而 percona出品的Xtrabackup,是InnoDB Hotbackup的一个开源替代品,可以在线对InnoDB XtraDB引擎的表进行物理备份。mysqldump支持

InnoDB 有个商业的InnoDB Hotbackup,可以对InnoDB引擎的表实现在线热备。而 percona出品的Xtrabackup,是InnoDB Hotbackup的一个开源替代品,可以在线对InnoDB/XtraDB引擎的表进行物理备份。mysqldump支持在线备份,不过是逻辑备份,效率比较差。当数据量比较小的时候,mysqldump还可以胜任,当数据量大的时候,恢复时间却让人无法忍受,于是开源工具xtrabackup就应运而生了,xtrabackup属于物理备份,效率很不错。

 

xtrabackup提供了两种命令行工具:

xtrabackup:用于备份InnoDB引擎的数据(不会备份myisam比如mysql权限相关表等,也不会自动copy frm文件);innobackupex:一个perl脚本,在执行过程中会调用xtrabackup命令,用该命令即可以备份InnoDB,也可以备份MyISAM/copy frm文件,只不过在备份myisam表时候会添加一个读锁。

 

实验环境:CentOS release 6.5 (Final),mysql Ver 14.14 Distrib 5.6.14

xtrabackup安装

#wgethttps://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.2.13/binary/redhat/6/x86_64/percona-xtrabackup-22-2.2.13-1.el6.x86_64.rpm
#yum-yinstalllibaiolibaio-develperl-Time-HiRescurlcurl-develzlib-developenssl-develperlcpioexpat-develgettext-develperl-ExtUtils-MakeMakerperl-DBD-MySQL.*packagebzrbisonncurses-develzlib-devel
#rpm-ivhpercona-xtrabackup-22-2.2.13-1.el6.x86_64.rpm

部分参数说明

--user指定执行备份的用户。
--password指定执行备份用户的密码。
--defaults-file指定mysql的选项文件路径。
--no-timestamp不要显示时间戳。
--incremental告诉xtrabackup这次是创建增量备份。
--incremental-basedir指定一个全量备份的路径作为增量备份的基础。
--redo-only如果进行准备工作完成后,还有其他的增量备份集待处理,就需要指定这个参数。
--apply-log从指定的选项文件中读取配置信息并应用日志等,这就意味对备份集做恢复的准备工作。
--copy-back将指定备份集恢复到指定的路径下。

 

全备

#将全备的数据备份到/data/backup/base
#innobackupex--user=root--password=redhat--defaults-file=/usr/local/webserver/mysql5.6/my.cnf--no-timestamp/data/backup/base
......................
innobackupex:Backupcreatedindirectory'/data/backup/base'
innobackupex:MySQLbinlogposition:filename'mysql-bin.000001',position733
16020115:26:07innobackupex:Connectiontodatabaseserverclosed
16020115:26:07innobackupex:completedOK!

先对进行一些修改。

>createdatabasesharelinux;
QueryOK,1rowaffected(0.00sec)

mysql>usesharelinux;
Databasechanged
mysql>createtablet1(idint,namevarchar(10));
QueryOK,0rowsaffected(0.12sec)

mysql>insertintot1values(1,'zhangsan'),(2,'lisi'),(3,'wangwu');
QueryOK,3rowsaffected(0.08sec)
Records:3Duplicates:0Warnings:0

mysql>select*fromt1;
+------+----------+
|id|name|
+------+----------+
|1|zhangsan|
|2|lisi|
|3|wangwu|
+------+----------+
3rowsinset(0.00sec)

第一次增量备份

#第一次增量备份目录/data/backup/incremental_one
#innobackupex--user=root--password=redhat--defaults-file=/usr/local/webserver/mysql5.6/my.cnf--no-timestamp--incremental/data/backup/incremental_one--incremental-basedir=/data/backup/base/
......................................................
xtrabackup:Creatingsuspendfile'/data/backup/incremental_one/xtrabackup_log_copied'withpid'19979'
xtrabackup:Transactionlogoflsn(22333659)to(22333659)wascopied.
16020115:39:26innobackupex:Alltablesunlocked
innobackupex:Backupcreatedindirectory'/data/backup/incremental_one'
innobackupex:MySQLbinlogposition:filename'mysql-bin.000001',position1238
16020115:39:26innobackupex:Connectiontodatabaseserverclosed
16020115:39:26innobackupex:completedOK!

再对数据库进行修改

mysql>createdatabasedb01;
QueryOK,1rowaffected(0.00sec)

mysql>usedb01;
Databasechanged
mysql>createtablet2(idint,namevarchar(10));
QueryOK,0rowsaffected(0.04sec)

mysql>insertintot2values(1,'zhangsan'),(2,'lisi'),(3,'wangwu');
QueryOK,3rowsaffected(0.02sec)
Records:3Duplicates:0Warnings:0

mysql>select*fromt2;
+------+----------+
|id|name|
+------+----------+
|1|zhangsan|
|2|lisi|
|3|wangwu|
+------+----------+
3rowsinset(0.00sec)

第二次增量备份

#第二次增量备份目录/data/backup/incremental_two
#innobackupex--user=root--password=redhat--defaults-file=/usr/local/webserver/mysql5.6/my.cnf--no-timestamp--incremental/data/backup/incremental_two--incremental-basedir=/data/backup/incremental_one/
........................................
innobackupex:Backupcreatedindirectory'/data/backup/incremental_two'
innobackupex:MySQLbinlogposition:filename'mysql-bin.000001',position1689
16020115:59:10innobackupex:Connectiontodatabaseserverclosed
16020115:59:10innobackupex:completedOK!

模拟故障,删除数据库的数据文件

#ls/usr/local/webserver/mysql5.6/data/
auto.cnfib_logfile0mysql-bin.000001node1.pidtest
db01ib_logfile1mysql-bin.indexperformance_schemazabbix
ibdata1mysqlnode1.errsharelinux
#rm-rf/usr/local/webserver/mysql5.6/data/*

恢复准备

#innobackupex--user=root--password=redhat--defaults-file=/usr/local/webserver/mysql5.6/my.cnf--apply-log--redo-only/data/backup/base/
........................................
xtrabackup:startingshutdownwithinnodb_fast_shutdown=1
InnoDB:Startingshutdown...
InnoDB:Shutdowncompleted;logsequencenumber22327338
16020116:09:44innobackupex:completedOK!
#innobackupex--user=root--password=redhat--defaults-file=/usr/local/webserver/mysql5.6/my.cnf--apply-log--redo-only/data/backup/base/--incremental-dir=/data/backup/incremental_one/
...........................................
innobackupex:Copying'/data/backup/incremental_one/performance_schema/events_stages_history.frm'to'/data/backup/base/performance_schema/events_stages_history.frm'
innobackupex:Copying'/data/backup/incremental_one/performance_schema/setup_instruments.frm'to'/data/backup/base/performance_schema/setup_instruments.frm'
16020116:13:15innobackupex:completedOK!
#innobackupex--user=root--password=redhat--defaults-file=/usr/local/webserver/mysql5.6/my.cnf--apply-log/data/backup/base/--incremental-dir=/data/backup/incremental_two/
innobackupex:Copying'/data/backup/incremental_two/performance_schema/setup_instruments.frm'to'/data/backup/base/performance_schema/setup_instruments.frm'
innobackupex:Copying'/data/backup/incremental_two/db01/db.opt'to'/data/backup/base/db01/db.opt'
innobackupex:Copying'/data/backup/incremental_two/db01/t2.frm'to'/data/backup/base/db01/t2.frm'
16020116:18:27innobackupex:completedOK!

数据恢复

#innobackupex--user=root--password=redhat--defaults-file=/usr/local/webserver/mysql5.6/my.cnf--copy-back/data/backup/base/
...........................................
innobackupex:StartingtocopyInnoDBsystemtablespace
innobackupex:in'/data/backup/base'
innobackupex:backtooriginalInnoDBdatadirectory'/usr/local/webserver/mysql5.6/data'
innobackupex:Copying'/data/backup/base/ibdata1'to'/usr/local/webserver/mysql5.6/data/ibdata1'

innobackupex:StartingtocopyInnoDBundotablespaces
innobackupex:in'/data/backup/base'
innobackupex:backto'/usr/local/webserver/mysql5.6/data'

innobackupex:StartingtocopyInnoDBlogfiles
innobackupex:in'/data/backup/base'
innobackupex:backtooriginalInnoDBlogdirectory'/usr/local/webserver/mysql5.6/data'
innobackupex:Finishedcopyingbackfiles.

16020116:23:08innobackupex:completedOK!

查看目录

#ll/usr/local/webserver/mysql5.6/data/#这个目录下的数据已经恢复回来
total79916
drwxr-x---.2rootroot4096Feb116:18db01
-rw-r-----.1rootroot79691776Feb116:18ibdata1
drwx------.2rootroot4096Feb115:26mysql
drwxr-xr-x.2rootroot4096Feb115:26performance_schema
drwxr-x---.2rootroot4096Feb116:13sharelinux
drwxr-xr-x.2rootroot4096Feb115:26test
-rw-r--r--.1rootroot22Feb116:18xtrabackup_binlog_info
-rw-r-----.1rootroot91Feb116:18xtrabackup_checkpoints
-rw-r--r--.1rootroot722Feb116:18xtrabackup_info
-rw-r-----.1rootroot2097152Feb116:09xtrabackup_logfile
drwx------.2rootroot12288Feb115:26zabbix

#chownmysql:mysql/usr/local/webserver/mysql5.6/data/-R#将目录更改为mysql用户
#pkillmysql#杀死原来的mysql进程,然后重启数据库
#servicemysqldstart
StartingMySQL..[OK]

数据已经恢复回来了

mysql>showdatabases;
+--------------------+
|Database|
+--------------------+
|information_schema|
|db01|
|mysql|
|performance_schema|
|sharelinux|
|test|
|zabbix|
+--------------------+
7rowsinset(0.14sec)

mysql>select*fromsharelinux.t1;#第一次增量备份的数据
+------+----------+
|id|name|
+------+----------+
|1|zhangsan|
|2|lisi|
|3|wangwu|
+------+----------+
3rowsinset(0.03sec)

mysql>select*fromdb01.t2;#第二次增量备份的数据
+------+----------+
|id|name|
+------+----------+
|1|zhangsan|
|2|lisi|
|3|wangwu|
+------+----------+
3rowsinset(0.08sec)
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<