返回
网站建设知识
Mysql热备xtrabackup的使用
2025-07-22 09:57  点击:0

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://percona/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',position73316020115:26:07innobackupex:Connectiontodatabaseserverclosed16020115:26:07innobackupex:completedOK!

先对数据库进行一些修改。

mysql>createdatabasesharelinux;QueryOK,1rowaffected(0.00sec)mysql>usesharelinux;Databasechangedmysql>createtablet1(idint,namevarchar(10));QueryOK,0rowsaffected(0.12sec)mysql>insertintot1values(1,'zhangsan'),(2,'lisi'),(3,'wangwu');QueryOK,3rowsaffected(0.08sec)Records:3Duplicates:0Warnings:0mysql>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:Alltablesunlockedinnobackupex:Backupcreatedindirectory'/data/backup/incremental_one'innobackupex:MySQLbinlogposition:filename'mysql-bin.000001',position123816020115:39:26innobackupex:Connectiontodatabaseserverclosed16020115:39:26innobackupex:completedOK!

再对数据库进行修改

mysql>createdatabasedb01;QueryOK,1rowaffected(0.00sec)mysql>usedb01;Databasechangedmysql>createtablet2(idint,namevarchar(10));QueryOK,0rowsaffected(0.04sec)mysql>insertintot2values(1,'zhangsan'),(2,'lisi'),(3,'wangwu');QueryOK,3rowsaffected(0.02sec)Records:3Duplicates:0Warnings:0mysql>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',position168916020115:59:10innobackupex:Connectiontodatabaseserverclosed16020115:59:10innobackupex:completedOK!

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

#ls/usr/local/webserver/mysql5.6/data/auto.cnfib_logfile0mysql-bin.000001node1.pidtestdb01ib_logfile1mysql-bin.indexperformance_schemazabbixibdata1mysqlnode1.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=1InnoDB:Startingshutdown...InnoDB:Shutdowncompleted;logsequencenumber2232733816020116: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:StartingtocopyInnoDBsystemtablespaceinnobackupex: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:StartingtocopyInnoDBundotablespacesinnobackupex:in'/data/backup/base'innobackupex:backto'/usr/local/webserver/mysql5.6/data'innobackupex:StartingtocopyInnoDBlogfilesinnobackupex: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/#这个目录下的数据已经恢复回来total79916drwxr-x---.2rootroot4096Feb116:18db01-rw-r-----.1rootroot79691776Feb116:18ibdata1drwx------.2rootroot4096Feb115:26mysqldrwxr-xr-x.2rootroot4096Feb115:26performance_schemadrwxr-x---.2rootroot4096Feb116:13sharelinuxdrwxr-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_logfiledrwx------.2rootroot12288Feb115:26zabbix#chownmysql:mysql/usr/local/webserver/mysql5.6/data/-R#将目录更改为mysql用户#pkillmysql#杀死原来的mysql进程，然后重启数据库#servicemysqldstartStartingMySQL..[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)