innodb经验分享,MySQL备份恢复之XtraBackup

一套测试用的mysql库,之前用的centos6默认源里的mysql 5.1.71的版本
。后来想试用下Percona server 5.7,由于这套库里没有什么重要数据
。所以操作前也未进行备份,配置好源后,直接就进行了安装。数据文件也存放在默认位置,安装完成后,直接启动mysql,发现启动失败,发现无法启动正常启动。

mysql innodb 异常修复经验分享,innodb经验分享

一套测试用的mysql库,之前用的centos6默认源里的mysql 5.1.71的版本
。后来想试用下Percona server 5.7,由于这套库里没有什么重要数据
。所以操作前也未进行备份,配置好源后,直接就进行了安装。数据文件也存放在默认位置,安装完成后,直接启动mysql,发现启动失败,发现无法启动正常启动。

一、回退重新装mysql

为避免再从其他地方导入这个数据的麻烦,先对当前库的数据库文件做了个备份(/var/lib/mysql/位置)。接下来将Percona
server
5.7包进行了卸载,重新安装原先老的5.1.71的包,启动mysql服务,提示Unknown/unsupported
table type: innodb,无法正常启动。

110509 12:04:27 InnoDB: Initializing buffer pool, size = 384.0M
110509 12:04:27 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 157286400 bytes!
110509 12:04:27 [ERROR] Plugin 'InnoDB' init function returned error.
110509 12:04:27 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
110509 12:04:27 [ERROR] Unknown/unsupported table type: innodb
110509 12:04:27 [ERROR] Aborting
110509 12:04:27 [Note] /usr/sbin/mysqld: Shutdown complete

删除/var/lib/mysql/目录,重新启动数据库服务,并初始化,发现正常,show
engines能发现有innodb引擎。再将数据库停掉,将之前备份的/var/lib/mysql/目录的内容覆盖当前位置的内容,重启。又发现不能进行启动,报错内容和刚刚一样。

/var/lib/mysql目录内容的结构如下:

-rw-rw---- 1 mysql mysql 10485760 2月  26 18:10 ibdata1
-rw-rw---- 1 mysql mysql 5242880 2月  26 18:10 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 2月  26 17:20 ib_logfile1
drwx------ 2 mysql mysql   4096 2月  26 17:20 mysql
drwx------ 2 mysql mysql   4096 2月 26 17:24 wiki

wiki目录是测试数据的库,ibdata1文件为数据文件,ib开头的两个文件为日志文件,mysql
目录下为系统库相关的东西
。再次使用初始化的数据,并将wiki目录和ibdata1文件覆盖到/var/lib/mysql
目录下,可以正常启动,也可以正常登录。

二、innodb模块重装

不过在通过mysqldump备份时,又提示unknow table engine “Innodb”
。登录后,查看当前所有的引擎类型,发现其中果然不存在innodb类型:

操作系统 1

通过alter命令修改其中一个表的类型为MyISAM ,发现仍然报错。

操作系统 2

通过 find
查找发现/usr/lib64/mysql/plugin/目录下有ha_innodb_plugin.so文件。印象中mysql5以后的版本支持在线插件安装
。通过下面查看确认,果然支持:

操作系统 3

使用如下命令加载时,发现不成功:

install plugin innodb soname 'ha_innodb.so';

三、备份

在/etc/my.cnf中增加如下配置:

plugin-load=innodb=ha_innodb_plugin.so
plugin_dir=/usr/lib64/mysql/plugin/
default-storage-engine=InnoDB 

发现仍启动失败。查看mysql-error.log发现有如下内容:

InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 7.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html

打开forcing-innodb-recovery官方页面,发现可以通过指定innodb_force_recovery参数,进行强制启动和恢复。在/etc/my.cnf中增加如下内容:

innodb_force_recovery=6

重新启动成功了。通过mysqldump备份也没有问题,将备份数据导入其他主机发现也正常可以测试。

这下就好搞了,将mysql彻底删除,重新安装Percona server
5.7,安装完后,建库,还原数据,程序重新连接,一切OK。

总结:

由于mysql
innodb数据文件的特性,可以在出现问题,无法正常启动时,先将./ib_logfile0
和 ./ib_logfile1
两个日志文件先移走,再启动,如果还不成功,可以用innodb_force_recovery参数进行强制恢复。除此之外,日志也很重启,有问题先看日志。

innodb 异常修复经验分享,innodb经验分享
一套测试用的mysql库,之前用的centos6默认源里的mysql 5.1.71的版本
。后来想试用下Percona server…

一、 简介

一、回退重新装mysql

我们知道,针对InnoDB存储引擎,MySQL本身没有提供合适的热备工具,ibbackup虽是一款高效的首选热备方式,但它是是收费的。好在Percona公司给大家提供了一个开源、免费的Xtrabackup热备工具,它可实现ibbackup的所有功能,并且还扩展支持真正的增量备份功能,是商业备份工具InnoDB
Hotbackup的一个很好的替代品。

为避免再从其他地方导入这个数据的麻烦,先对当前库的数据库文件做了个备份(/var/lib/mysql/位置)。接下来将Percona
server
5.7包进行了卸载,重新安装原先老的5.1.71的包,启动mysql服务,提示Unknown/unsupported
table type: innodb,无法正常启动。

Xtrabackup包括两个主要工具:Xtrabackup和innobackupex:

110509 12:04:27 InnoDB: Initializing buffer pool, size = 384.0M
110509 12:04:27 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 157286400 bytes!
110509 12:04:27 [ERROR] Plugin 'InnoDB' init function returned error.
110509 12:04:27 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
110509 12:04:27 [ERROR] Unknown/unsupported table type: innodb
110509 12:04:27 [ERROR] Aborting
110509 12:04:27 [Note] /usr/sbin/mysqld: Shutdown complete

Xtrabackup只能备份InnoDB和XtraDB两种引擎表,而不能备份MyISAM数据表。

删除/var/lib/mysql/目录,重新启动数据库服务,并初始化,发现正常,show
engines能发现有innodb引擎。再将数据库停掉,将之前备份的/var/lib/mysql/目录的内容覆盖当前位置的内容,重启。又发现不能进行启动,报错内容和刚刚一样。

innobackupex则是参考InnoDBHotbackup的innoback脚本修改而来的一个perl脚本,它封装了xtrabackup,主要是为了能方便的同时备份InnoDB和MyISAM引擎表。但在处理MyISAM时需要加一个读锁,这会阻塞线上服务的写操作,所以数据库中MyISAM表越少越好。另外,该工具还加入了一些其它使用选项,比如slave-info,可以在备份中记录一些Slave需要的信息,根据这些信息,可以很方便的利用备份重做Slave。

/var/lib/mysql目录内容的结构如下:

通过Xtrabackup,不但可在线(热)备份整个库的InnoDB、XtraDB表,还可在上一次整库备份的基础上做增量备份(InnoDB
only),其工作原理如下:

-rw-rw---- 1 mysql mysql 10485760 2月  26 18:10 ibdata1
-rw-rw---- 1 mysql mysql 5242880 2月  26 18:10 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 2月  26 17:20 ib_logfile1
drwx------ 2 mysql mysql   4096 2月  26 17:20 mysql
drwx------ 2 mysql mysql   4096 2月 26 17:24 wiki

首先完成一个完全备份,并记录下此时检查点的LSN(Log Sequence
Number);在进行增量备份时,比较表空间中每个页的LSN是否大于上次备份时的LSN,如果是,则备份该页,同时记录当前检查点的LSN。

wiki目录是测试数据的库,ibdata1文件为数据文件,ib开头的两个文件为日志文件,mysql
目录下为系统库相关的东西
。再次使用初始化的数据,并将wiki目录和ibdata1文件覆盖到/var/lib/mysql
目录下,可以正常启动,也可以正常登录。

注意:在增量备份时,作为备份基础的全备文件不能压缩,否则备份失效;另外,增量备份期间,表结构变更的话,变更部分的备份也会失效。

二、innodb模块重装

二、安装

不过在通过mysqldump备份时,又提示unknow table engine “Innodb”
。登录后,查看当前所有的引擎类型,发现其中果然不存在innodb类型:

Pecona官方网站提供了Xtrabackup源码包、二进制包以及RPM包三种安装包,下载地址为:
本文选择最简单方便的RPM包安装,如下:

操作系统 4

[[email protected]
~]# rpm -ivh percona-xtrabackup-2.0.5-499.rhel5.x86_64.rpm

通过alter命令修改其中一个表的类型为MyISAM ,发现仍然报错。

warning: percona-xtrabackup-2.0.5-499.rhel5.x86_64.rpm:Header V4 DSA
signature: NOKEY, key ID cd2efd2a

操作系统 5

Preparing…
###########################################
[100%]

通过 find
查找发现/usr/lib64/mysql/plugin/目录下有ha_innodb_plugin.so文件。印象中mysql5以后的版本支持在线插件安装
。通过下面查看确认,果然支持:

1:percona-xtrabackup
###########################################
[100%]

操作系统 6

安装完成后,会在/usr/bin/目录下生成如下命令工具:

使用如下命令加载时,发现不成功:

[[email protected]
~]# cd /usr/bin/

install plugin innodb soname 'ha_innodb.so';

[[email protected]
bin]# ls xtr* inno*

三、备份

innobackupex innochecksum xtrabackup_51 xtrapchar xtrapinfo xtrapproto
xtrapstats

在/etc/my.cnf中增加如下配置:

innobackupex-1.5.1 xtrabackup xtrabackup_55 xtrapin xtrapout xtrapreset

plugin-load=innodb=ha_innodb_plugin.so
plugin_dir=/usr/lib64/mysql/plugin/
default-storage-engine=InnoDB 

其中:

发现仍启动失败。查看mysql-error.log发现有如下内容:

innobackupex
是备份时直接使用的工具,它是一个perl脚本,内部封装xtrabackup;

InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 7.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html

innobackupex-1.5.1 是innobackupex文件的一个软链接;

打开forcing-innodb-recovery官方页面,发现可以通过指定innodb_force_recovery参数,进行强制启动和恢复。在/etc/my.cnf中增加如下内容:

xtrabackup 是备份Innodb引擎的脚本;

innodb_force_recovery=6

xtrabackup_51 xtrabackup运行时需要调用的工具,适用于MySQL 5.1版本;

重新启动成功了。通过mysqldump备份也没有问题,将备份数据导入其他主机发现也正常可以测试。

xtrabackup_55 xtrabackup运行时需要调用的工具,适用于MySQL 5.5版本;

这下就好搞了,将mysql彻底删除,重新安装Percona server
5.7,安装完后,建库,还原数据,程序重新连接,一切OK。

三、Xtrabackup的使用

总结:

首先来学习使用Xtrabackup这个命令工具,前面提到:它只能备份InnoDB和XtraDB两种引擎表,不能备份MyISAM数据表。

由于mysql
innodb数据文件的特性,可以在出现问题,无法正常启动时,先将./ib_logfile0
和 ./ib_logfile1
两个日志文件先移走,再启动,如果还不成功,可以用innodb_force_recovery参数进行强制恢复。除此之外,日志也很重启,有问题先看日志。

通过–help参数查看该命令的用法,如下:

您可能感兴趣的文章:

  • InnoDB
    类型MySql恢复表结构与数据
  • MySQL启动时InnoDB引擎被禁用了的解决方法
  • mysql执行sql文件报错Error: Unknown storage
    engine‘InnoDB’的解决方法
  • mysql
    innodb的监控(系统层,数据库层)
  • Mysql更换MyISAM存储引擎为Innodb的操作记录总结
  • 关于MySQL
    innodb_autoinc_lock_mode介绍
  • MySQL优化之InnoDB优化
  • MySQL存储引擎中MyISAM和InnoDB区别详解
  • MySQL提示The InnoDB feature is
    disabled需要开启InnoDB的解决方法
  • MySQL中Innodb的事务隔离级别和锁的关系的讲解教程
  • 详解MySQL中InnoDB的存储文件

Usage: [xtrabackup [–default-file=#] –backup | xtrabackup
[–defaults-file=#] –prepare] [OPTIONS]

参数解读如下:

–defaults-file=#

标识默认的配置文件,可显式手工指定,也可不设置;若不设置,则Xtrabackup缺省将从以下目录查找配置文件:

/etc/my.cnf

/etc/mysql/my.cnf

/usr/local/etc/my.cnf

~/.my.cnf

然后从中读取[mysqld]和[xtrabackup]配置段。[mysqld]中只需指定datadir、innodb_data_home_dir、innodb_data_file_path、innodb_log_group_home_dir、innodb_log_files_in_group、innodb_log_file_size这6个参数即可让Xtrabackup正常工作。

–defaults-extra-file=#

如果使用了该参数,则会在读取了全局配置文件后,再读取这里指定的配置文件

–target-dir=name

备份文件的存放路径

–backup

执行备份操作,将备份文件存放到target-dir指定的目录下

–prepare

对备份文件执行恢复前的准备(生成InnoDB logfile)

–print-param

打印备份或恢复时需要的参数

–use-memory=#

该参数在prepare的时候使用,控制prepare时innodb实例使用的内存量

–suspend-at-end

在target-dir目录下产生一个xtrabackup_suspended文件,将xtrabackup进程挂起,不停地将数据文件的变化同步到备份文件,直到用户手工删除xtrabackup_suspended文件

–throttle=#

每秒钟IO次数,限制backup时使用的I/O操作量,使备份对数据库正常业务的影响最小化

–log-stream

该参数在backup时使用,将xtrabackup_logfile的内容输出到标准输出,使用该参数时会自动使用suspend-at-end参数,innobackupex脚本的stream模式会使用该参数。

–incremental-lsn=name

增量备份时只拷贝LSN比该参数指定值新的ibd
pages,前次备份到了哪个LSN可以看前次备份集的xtrabackup_checkpoints文件

–incremental-basedir=name

该参数在backup的时候使用,备份比该参数指定位置的备份集新的idb pages

–incremental-dir=name

该参数在prepare的时候使用,指定prepare时产生的delta文件和日志文件的存放路径

–tables=name

在备份file-per-table类型的文件时使用,使用正则表达式指定需要备份的innodb表

-h, –datadir=name

MySQL数据库的数据文件目录

示例1:完全备份与恢复(InnoDB)

(1)备份

脚本内容:

[[email protected]
xtrabak]# more xtra_backup.sh
# 2014-04-29
mkdir -p /data/xtrabak/bak_`date +%F`/
echo “backup begin” `date`
xtrabackup –defaults-file=/etc/my.cnf –backup
–target-dir=/data/xtrabak/bak_`date +%F`/
cp -r /var/lib/mysql/test /data/xtrabak/bak_`date +%F`/

注意:xtrabackup只备份数据文件,并不备份数据表结构文件(.frm),所以还需手动拷贝一下,以便xtrabackup恢复时使用

执行备份脚本:

[[email protected]
xtrabak]# sh xtra_backup.sh
backup begin Tue Apr 29 15:22:09 CST 2014
xtrabackup version 2.0.5 for Percona Server 5.1.59 unknown-linux-gnu
(x86_64) (revision id: 499)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql/
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = /var/lib/mysql
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = /var/lib/mysql
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
>> log scanned up to (1830807)
[01] Copying /var/lib/mysql/ibdata1 to
/data/xtrabak/bak_2014-04-29/ibdata1
[01] …done
xtrabackup: The latest check point (for incremental): ‘1830807’
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1830807)

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

从输出可以看出,备份过程首先记录LSN,然后拷贝数据文件(注意,并没有备份表结构文件.frm),最后拷贝并记录LSN。备份完成后,在指定目标目录除了拷贝过来的数据文件ibdata1及表结构文件夹test外,还生成了另外两个文件,分别记录日志及检查点,如下:

[[email protected]
xtrabak]# cd bak_2014-04-29/
[[email protected]
bak_2014-04-29]# ls
ibdata1 test xtrabackup_checkpoints xtrabackup_logfile

恢复

删除库test,然后通过备份文件执行恢复,如下:

脚本内容:

[[email protected]
xtrabak]# more xtra_prepare.sh
xtrabackup –defaults-file=/etc/my.cnf –prepare
–target-dir=/data/xtrabak/bak_`date +%F`/
cp -r /data/xtrabak/bak_`date +%F`/test/ /var/lib/mysql/
rm /var/lib/mysql/ib*
cp /data/xtrabak/bak_`date +%F`/ib* /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql
service mysql restart

说明:脚本中步骤依次为:

实施对备份文件进行恢复前的准备;

从备份文件拷贝数据表结构到默认的数据目录;

删除默认数据目录中对应的数据文件并复制备份的数据文件到默认数据目录;

修改默认数据目录权限并重启MySQL服务

执行恢复脚本:

[[email protected]
xtrabak]# sh xtra_prepare.sh
xtrabackup version 2.0.5 for Percona Server 5.1.59 unknown-linux-gnu
(x86_64) (revision id: 499)
xtrabackup: cd to /data/xtrabak/bak_2014-04-29/
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152,
start_lsn=(1830807)
xtrabackup: Temporary instance for recovery is set as followings.
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 = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: Temporary instance for recovery is set as followings.
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 = 1
xtrabackup: innodb_log_file_size = 2097152
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
140429 15:28:34 InnoDB: Initializing buffer pool, size = 100.0M
140429 15:28:34 InnoDB: Completed initialization of buffer pool
140429 15:28:34 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
140429 15:28:34 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files…
InnoDB: Last MySQL binlog file position 0 85912, file name
./mysql-bin.000026
140429 15:28:34 Percona XtraDB () 1.0.17-12.5
started; log sequence number 1830807

[notice (again)]
If you use binary log and don’t use any hack of group commit,
the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 85912, file name
./mysql-bin.000026

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
140429 15:28:34 InnoDB: Starting shutdown…
140429 15:28:34 InnoDB: Shutdown completed; log sequence number
1832037
Shutting down MySQL…. [ OK ]
Starting MySQL. [ OK ]

执行完成后,检查发现test库已成功恢复。

示例2:增量备份与恢复

增量备份是以完全备份或上一次增量备份为基础的备份,适合数据库很大的情况,支持在线热备,备份期间不锁定表,不影响用户使用,备份恢复效率都比较高。

下面我们来模拟一次增量备份:

(1) 完全备份

现在test库有三张表,执行一次完全备份

--脚本内容:

[[email protected]
xtrabak]# more xtra_full_bak.sh
# 2014-04-29
mkdir -p /data/xtrabak/full_bak_`date +%F`/
xtrabackup –defaults-file=/etc/my.cnf –backup
–target-dir=/data/xtrabak/full_bak_`date +%F`/
cp -r /var/lib/mysql/test/ /data/xtrabak/full_bak_`date +%F`/

--执行脚本

[[email protected]
xtrabak]# sh xtra_full_bak.sh
xtrabackup version 2.0.5 for Percona Server 5.1.59 unknown-linux-gnu
(x86_64) (revision id: 499)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = /var/lib/mysql
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = /var/lib/mysql
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 67108864
>> log scanned up to (1653514)
[01] Copying /var/lib/mysql/ibdata1 to
/data/xtrabak/full_bak_2014-04-29/ibdata1
[01] …done
xtrabackup: The latest check point (for incremental): ‘1653514’
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1653514)

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

执行完成后,查看备份文件

[[email protected]
xtrabak]# cd full_bak_2014-04-29/
[[email protected]
full_bak_2014-04-29]# ls
ibdata1 test xtrabackup_checkpoints xtrabackup_logfile

(2) 增量备份

向test库中添加一张新表,然后执行增量备份

--脚本内容:

[[email protected]
xtrabak]# more xtra_delta_bak.sh
# 2014-04-29
mkdir -p /data/xtrabak/delta_bak_`date +%F`/
xtrabackup –defaults-file=/etc/my.cnf –backup
–target-dir=/data/xtrabak/delta_bak_`date +%F`/
–incremental-basedir=/data/xtrabak/full_bak_`date +%F`/
cp -r /var/lib/mysql/test/ /data/xtrabak/full_bak_`date +%F`/

说明:这里需要再次拷贝test库的表结构文件,因为期间可能会增加或删除表

--执行脚本

[[email protected]
xtrabak]# sh xtra_delta_bak.sh
xtrabackup version 2.0.5 for Percona Server 5.1.59 unknown-linux-gnu
(x86_64) (revision id: 499)
incremental backup from 1653514 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = /var/lib/mysql
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = /var/lib/mysql
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 67108864
>> log scanned up to (1660436)
[01] Copying /var/lib/mysql/ibdata1 to
/data/xtrabak/delta_bak_2014-04-29/ibdata1.delta
[01] …done
xtrabackup: The latest check point (for incremental): ‘1660436’
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1660436)

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

执行完成后,查看备份文件

[[email protected]
xtrabak]# cd delta_bak_2014-04-29/
[[email protected]
delta_bak_2014-04-29]# ls
ibdata1.delta ibdata1.meta xtrabackup_checkpoints xtrabackup_logfile

注意:在增量备份目录下,数据文件是以.delta结尾的,增量备份只备份上一次完全备份后被修改过的page;当然,若再次执行增量备份,可以上一次完全备份为基础,也可以第一次增量备份为基础,每次增量备份的目录都是需要修改的。

(3) 恢复

删除test库,模拟故障,然后通过完全备份及增量备份文件执行恢复。

--脚本内容:

[[email protected]
xtrabak]# more xtra_delta_prepare.sh
xtrabackup –defaults-file=/etc/my.cnf –prepare
–target-dir=/data/xtrabak/full_bak_`date +%F`/
xtrabackup –defaults-file=/etc/my.cnf –prepare
–target-dir=/data/xtrabak/full_bak_`date +%F`/
–incremental-dir=/data/xtrabak/delta_bak_`date +%F`/
cp -r /data/xtrabak/full_bak_`date +%F`/test/ /var/lib/mysql/
rm /var/lib/mysql/ib*
cp /data/xtrabak/full_bak_`date +%F`/ib* /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql
service mysql restart

--执行脚本

[[email protected]
xtrabak]# sh xtra_delta_prepare.sh
xtrabackup version 2.0.5 for Percona Server 5.1.59 unknown-linux-gnu
(x86_64) (revision id: 499)
xtrabackup: cd to /data/xtrabak/full_bak_2014-04-29/
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152,
start_lsn=(1653514)
xtrabackup: Temporary instance for recovery is set as followings.
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 = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: Temporary instance for recovery is set as followings.
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 = 1
xtrabackup: innodb_log_file_size = 2097152
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
140429 18:28:21 InnoDB: Initializing buffer pool, size = 100.0M
140429 18:28:21 InnoDB: Completed initialization of buffer pool
140429 18:28:21 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
140429 18:28:21 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files…
InnoDB: Last MySQL binlog file position 0 2596, file name
./mysql-bin.000043
140429 18:28:22 Percona XtraDB () 1.0.17-12.5
started; log sequence number 1653514

[notice (again)]
If you use binary log and don’t use any hack of group commit,
the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 2596, file name
./mysql-bin.000043

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
140429 18:28:22 InnoDB: Starting shutdown…
140429 18:28:22 InnoDB: Shutdown completed; log sequence number
1653514
xtrabackup version 2.0.5 for Percona Server 5.1.59 unknown-linux-gnu
(x86_64) (revision id: 499)
incremental backup from 1653514 is enabled.
xtrabackup: cd to /data/xtrabak/full_bak_2014-04-29/
xtrabackup: This target seems to be already prepared.
xtrabackup: xtrabackup_logfile detected: size=2097152,
start_lsn=(1660436)
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir =
/data/xtrabak/delta_bak_2014-04-29/
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: page size for
/data/xtrabak/delta_bak_2014-04-29//ibdata1.delta is 16384 bytes
Applying /data/xtrabak/delta_bak_2014-04-29//ibdata1.delta to
././ibdata1…
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir =
/data/xtrabak/delta_bak_2014-04-29/
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
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
140429 18:28:22 InnoDB: Initializing buffer pool, size = 100.0M
140429 18:28:22 InnoDB: Completed initialization of buffer pool
140429 18:28:22 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
140429 18:28:22 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files…
InnoDB: Last MySQL binlog file position 0 2703, file name
./mysql-bin.000045
140429 18:28:22 Percona XtraDB () 1.0.17-12.5
started; log sequence number 1660436

[notice (again)]
If you use binary log and don’t use any hack of group commit,
the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 2703, file name
./mysql-bin.000045

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
140429 18:28:22 InnoDB: Starting shutdown…
140429 18:28:23 InnoDB: Shutdown completed; log sequence number
1660436
Shutting down MySQL… [ OK ]
Starting MySQL….. [ OK ]
执行成功后,检查发现test数据库已成功恢复。

四、 Innobackupex使用

前面提到,Innobackupex可同时备份InnoDB和MyISAM引擎表,所以通常都直接使用innobackupex。需要注意的是my.cnf中必须加上datadir这个参数,因为要根据它来定位InnoDB引擎的数据文件位置。

innobackupex也包含一系列参数,可通过innobackupex
–help命令查看,此处不逐一说明了。

下面我们通过一个完整的示例来演示其用法,如下:

(1) 备份

--脚本内容:

[[email protected]
xtrabak]# more backup.sh
# 2014-04-29
mkdir -p /data/xtrabak/bak_`date +%F`/
echo “backup begin” `date`
log=innobackupex_`date +%F`.log
str=bak_`date +%F`
innobackupex –user=root –password=root123 –defaults-file=/etc/my.cnf
–stream=tar /data/xtrabak/$str/ 2>/data/xtrabak/$log |
gzip>/data/xtrabak/$str/base.tar.gz
echo “backup end” `操作系统 ,date`
cp /etc/my.cnf /data/xtrabak/my_`date +%F`.cnf

--执行脚本:

[[email protected]
xtrabak]# sh backup.sh
backup begin Wed Apr 30 16:15:02 CST 2014
backup end Wed Apr 30 16:15:27 CST 2014

[[email protected]
xtrabak]# cd bak_2014-04-30/
[[email protected]
bak_2014-04-30]# ls
base.tar.gz

执行完成后,生成备份文件的压缩包

(2) 增量日志备份

由于数据库不停的对外提供服务,备份之后的操作会记录到binlong日志文件中,所以我们还应备份后续的日志文件。

此处删除几张表,并切换日志以模拟真实环境,备份完整的binlog日志文件;然后关闭数据库,删除数据库所有文件,以便模拟故障。

--脚本内容(binlog备份到远程机器):

[[email protected]
xtrabak]# more binlog_bak.sh
cd /var/lib/
DATE=`date -d ‘-1 hour’ +%Y%m%d%H00`
touch -t “${DATE}” starttemp
echo “$DATE”
umount /mnt_log>/dev/null 2>&1
mount -t nfs 192.168.3.108:/logcenter /mnt_log
find /var/lib/mysql/mysql-bin.* -newer starttemp|xargs -I {} cp -p {}
/mnt_log/mysql-binlog/192.168.3.28/
# umount -t nfs /mnt_log

(3) 恢复

--脚本内容:

[[email protected]
xtrabak]# more prepare.sh
# 2014-04-30
service mysql stop
mkdir -p /data/xtrabak/prepare_`date +%F`/
tar -ixzvf /data/xtrabak/bak_`date +%F`/base.tar.gz -C
/data/xtrabak/prepare_`date +%F`/
innobackupex –apply-log –user=root –defaults-file=/etc/my.cnf
/data/xtrabak/prepare_`date +%F`/
innobackupex –copy-back –user=root –defaults-file=/etc/my.cnf
/data/xtrabak/prepare_`date +%F`/
chown -R mysql:mysql /var/lib/mysql/
rm -rf /var/lib/mysql/xtrabackup_binlog_pos_innodb
service mysql restart

--完全备份恢复后,通过binlog进行增量恢复

[[email protected]
test]# mysqlbinlog start-position=******
/mnt_log/mysql-binlog/192.168.3.28/mysql-bin.000052 |mysql -uroot
-proot123

注意:start-position的位置可通过解压后的备份文件查看,如下:

[[email protected]
xtrabak]# cd prepare_2014-04-30/

[[email protected]
prepare_2014-04-30]# more xtrabackup_binlog_info
mysql-bin.000047 1472

或者

[[email protected]
prepare_2014-04-30]# more xtrabackup_binlog_pos_innodb
./mysql-bin.000047 1472

成功恢复后,MySQL即可正常使用。

简介
我们知道,针对InnoDB存储引擎,MySQL本身没有提供合适的热备工具,ibbackup虽是一款高效的首选热备方式,但它是是收费的。好在…