MySQL热备份工具innobackupex介绍及应用 ------ 常用备份方式

2019独角兽企业重金招聘Python工程师标准>>>

  innobackupex是一个perl脚本对xtrbackup的封装，支持InnoDB/XtraDB表数据在线热备份和相关数据结构，对于MyISAM表的备份会产生表锁。

连接到数据库

innobackupex --user=DBUSER --password=SECRET /path/to/backup/dir
innobackupex --user=LUKE --password=US3TH3F0RC3 --stream=tar ./ | bzip2 -
xtrabackup --user=DVADER --password=14MY0URF4TH3R --backup --target-dir=/data/bkps/

其他选项

--port   数据库端口
--socket 套接字地址
--host   主机地址

备份方式：

全量备份 - 原理 

对于InnoDB，XtraBackup基于InnoDB的crash-recovery功能进行备份。

crash-recovery是这样的：InnoDB维护了一个redo log，又称为 transaction log，也叫事务日志，它包含了InnoDB数据的所有改动情况。InnoDB启动的时候先去检查datafile和transaction log，然后应用所有已提交的事务并回滚所有未提交的事务。

XtraBackup在备份的时候并不锁定表，而是一页一页地复制InnoDB的数据，与此同时，XtraBackup还有另外一个线程监视着transactions log，一旦log发生变化，就把变化过的log pages复制走(因为transactions log文件大小有限，写满之后，就会从头再开始写，新数据可能会覆盖到旧的数据，所以一旦变化就要立刻复制走）。在全部数据文件复制完成之后，停止复制logfile。

XtraBackup采用了其内置的InnoDB库以read-write模式打开InnoDB的数据文件，然后每次读写1MB(1MB/16KB=64page)的数据，一页一页地遍历，同时用InnoDB的buf_page_is_corrupted()函数检查此页的数据是否正常，如果正常则进行复制，如不正常则重新读取，最多重读10次，如果还是失败，则备份失败退出。复制transactions log的原理也是一样的，只不过每次读写512KB(512KB/16KB=32page)的数据。

由于XtraBackup其内置的InnoDB库打开文件的时候是rw的，所以运行XtraBackup的用户，必须对InnoDB的数据文件具有读写权限。

由于XtraBackup要从文件系统中复制大量的数据，所以它尽可能地使用posix_fadvise()，来告诉OS不要缓存读取到的数据(因为这些数据不会重用到了)，从而提升性能。如果要缓存的话，大量的数据会对OS的虚拟内存造成很大的压力，其它进程（如mysqld）很有可能会被swap出去，这样就出问题了。同时，XtraBackup在读取数据的时候还尽可能地预读。

由于不锁表，所以复制出来的数据是不一致的，数据的一致性是在恢复的时候使用crash-recovery进行实现的。

对于MyISAM,XtraBackup还是首先锁定所有的表，然后复制所有文件。

全量备份 - 方式

innobackupex --user=DBUSER  --password=SECRET  --host=localhost --port=3306 --defaults-file=/etc/my.cnf --tmpdir=/tmp --slave-info --stream=tar /path/to/backup/dir 2 > full-bak.log  | gzip 1> full-bak.tar.gz

参数说明：

--defaults-file=[my.cnf]    同xtrabackup的--defaults-file参数,innobackupex选项文件，主要指定InnoDB相关参数信息,直接指定my.cnf
--slave-info                备份从库, 加上--slave-info备份目录下会多生成一个xtrabackup_slave_info 文件,这里会保存主日志文件以及偏移, 文件内容类似于:CHANGE MASTER TO MASTER_LOG_FILE='', MASTER_LOG_POS=0
--stream=[tar]              备份文件输出格式, tar时使用tar4ibd , 该文件可在XtarBackup binary文件中获得.如果备份时有指定--stream=tar, 则tar4ibd文件所处目录一定要在$PATH中(因为使用的是tar4ibd去压缩, 在XtraBackup的binary包中可获得该文件)。 
--tmpdir                    在使用参数stream=tar备份的时候，你的xtrabackup_logfile可能会临时放在/tmp目录下，如果你备份的时候并发写入较大的话xtrabackup_logfile可能会很大(5G+)，很可能会撑满你的/tmp目录，可以通过参数--tmpdir指定目录来解决这个问题。

增量备份 - 原理

在完整备份和增量备份文件中都有一个文件xtrabackup_checkpoints会记录备份完成时检查点的LSN。在进行新的增量备份时，XtraBackup会比较表空间中每页的LSN是否大于上次备份完成的LSN，如果是，则备份该页，并记录当前检查点的LSN。

增量备份 - 方式

innobackupex --user=DBUSER  --password=SECRET  --host=localhost --port=3306 --defaults-file=/etc/my.cnf --tmpdir=/tmp --slave-info --stream=xbstream --compress --incremental /path/to/backup/incr-dir --incremental-basedir /path/to/backup/dir 2 > incr-bak.log  | gzip 1> incr-bak.tar.gz

参数说明：

--incremental                 增量备份目录
--incremental-basedir         前一次备份目录
--incremental-lsn=LSN-number  前一次备份的LSN值，只备份比这个值新的ibd pages
--compress                    压缩备份此选项不兼容--stream=tar,只兼容--stream=xbstream,此外加密项encrypted也不能兼容--stream=tar

单库备份

默认参数中--databases=LIST可以备份单库，这个参数说明虽然是指定需要备份数据库，但实际运行过程中它会备份系统中所有数据库的InnoDB表。实际中如果要对单个数据库进行备份可以使用--include=REGEXP，如备份test库可以--include='test.*'备份test库下所有数据表

备份恢复：

全备恢复

innobackupex --apply-log --use-memory=4G /path/to/BACKUP-DIR
/etc/init.d/mysqld stop
innobackupex --copy-back /path/to/BACKUP-DIR
chown -R mysql:mysql /var/lib/mysql
/etc/init.d/mysqld start

参数说明：

--apply-log        在数据库备份好后，这些备份的数据并不能立即用于恢复，因为这些刚备份的数据里包含了未提交的数据，需要回滚undo数据！也包括的已完成的事务在重做日志文件中并没有写入数据文件中，这些数据需要重做redo  这个参数正是用于做这些事情，以保证数据文件的一致性。在数据库恢复之前，须先对备份的数据文件应用此参数。（innobackup会重现重做日志文件（redo log file）中的事务条目，重做已经提交的事务和回滚未提交的事务
--use-memory=4G    此参数用来控制备份所使用到的内存大小，默认为100M！ 一般与--apply-log一起使用
--copy-back        把备份数据拷贝回server的datadir，它决定于my.cnf中的datadir参数，另外在恢复时datadir目录必须是空的，并且mysql数据库必须为关闭状态

增量恢复

innobackupex --apply-log --redo-only /path/to/BACKUP-DIR    # 全备目录
innobackupex --apply-log --redo-only /path/to/BACKUP-DIR --incremental-dir=/path/to/INCREMENTAL-DIR-1    # 第一次增备目录
innobackupex --apply-log /path/to/BACKUP-DIR --incremental-dir=/path/to/INCREMENTAL-DIR-2    # 第二次增备目录
innobackupex --apply-log /path/to/BACKUP-DIR
/etc/init.d/mysqld stop
innobackupex --copy-back /path/to/BACKUP-DIR
chown -R mysql:mysql /var/lib/mysql
/etc/init.d/mysqld start

参数说明：

--redo-only    在做增量恢复时，全备和增量备份的数据文件在恢复前必须先将在重做日志文件中的已提交的事务重做。此参数将会合并全备和增量备份的数据文件，但不包括最后一次增量备份的数据文件

单表恢复

innobackupex --apply-log --export /path/to/backup
CREATE TABLE mytable (...) ENGINE=InnoDB;
ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;
cp /path/to/backup/mydatabase/mytables.ibd /var/lib/mysql/mydatabase/
cp /path/to/backup/mydatabase/mytable.exp /var/lib/mysql/mydatabase/
cp /path/to/backup/mydatabase/mytable.cfg /var/lib/mysql/mydatabase/
chown mysql.mysql -R /var/lib/mysql/mydatabase
ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;

参数说明：

--export    导出单个表

使用限制：

因为针对单个表恢复生成的数据文件为单个表空间数据文件，所以数据库必须使用innodb_file_per_table来为每个表使用独立表空间

以上方法在MySQL 5.6或Percona Server 5.6中可以成功倒入表空间数据，在MySQL 5.6之前的版本需要其他额外的操作才能完成数据恢复。

ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;

在MySQL 5.6之前的版本上面的语句会产生

root@localhost [innodb_test] 04:58:15>ALTER TABLE innodb1 IMPORT TABLESPACE;
ERROR 1030 (HY000): Got error -1 from storage engine
root@localhost [innodb_test] 04:59:03>

日志中为：

141022 16:59:03  InnoDB: Error: tablespace id and flags in file './innodb_test/innodb1.ibd' are 815 and 0, but in the InnoDB
InnoDB: data dictionary they are 819 and 0.
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
141022 16:59:03  InnoDB: cannot find or open in the database directory the .ibd file of
InnoDB: table `innodb_test`.`innodb1`
InnoDB: in ALTER TABLE ... IMPORT TABLESPACE

这是因为新表的tablespace id和原来的tablespace id不一致导致不能成功倒入表空间。解决的办法是我们新创建一个数据库实例不断的创建和删除表当表的tablespace id与老的tablespace id一直时我们将表空间的数据倒入到新表然后使用mysqldump对数据进行备份然后倒入的原来的数据库中。

  示例中tablespace id为815我们需要再创建813个 innodb table,(table id 1已经被占用，需要比对应.ibd 文件的table id 小1， 所以是 815-2=813 )

drop procedure if exists proc_createtable;
delimiter &&

create procedure proc_createtable()
begin

declare tid int;

set tid=0; 
repeat
  set tid=tid+1;
  create table t (id int);
  drop table t;
  insert into innodb1 values (tid);
until tid=813 end repeat;

end&&

delimiter ;

复制表空间数据

ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;
cp /path/to/backup/mydatabase/mytables.ibd /var/lib/mysql/mydatabase/
cp /path/to/backup/mydatabase/mytable.exp /var/lib/mysql/mydatabase/
cp /path/to/backup/mydatabase/mytable.cfg /var/lib/mysql/mydatabase/
chown mysql.mysql -R /var/lib/mysql/mydatabase
ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;

备份表数据

mysqldump -uroot -proot -P 3322 innodb_test innodb1 > innodb1.sql

恢复表数据

mysqldump -uroot -proot innodb_test < innodb1.sql

转载于:https://my.oschina.net/u/2252260/blog/335882