INNODB 热备工具试验与总结

INNODB HOT BACKUP是一个商用的可执行文件。不过可以申请30天试用下。

相比MYSQL自身的MYSQLDUMP 导入导出工具来说有以下优点：

1.物理备份。

2.还原速度快。

而MYSQLDUMP在并发小的时候还可以用下。缺点如下：

1.逻辑备份。

2.还原速度慢。

而LVM则可以喝INNODB HOT BACKUP有一拼了，不过有个缺点，就是要建立逻辑卷，而且备份的时候必须得建立跟源数据所在卷一样的临时卷。其他的优点和INNODB HOT BACKUP一样。

不过INNODB HOT BACKUP的缺点也是有的，就是只备份INNODB的表空间以及生成新的REDO 日志。

对于表结构的备份可以直接用MYSQLDUMP来个快速逻辑备份， 或者可以用 INNODB 官方给的开源PERL 脚本来配合备份表结构。

INNODB HOT BACKUP 工具需要的两个配置文件示例可以按照官方文档来设置。

备份目录下的几个文件。

[root@mysql01 backup]# ls

backup.cnf log my.cnf

开始备份，关于具体这两个配置文件是干嘛的，我就不说了，具体看看官方手册。

[root@mysql01 backup]# /root/ibbackup my.cnf backup.cnf

InnoDB Hot Backup version 3.0.0; Copyright 2002-2005 Innobase Oy

License A15436 is granted to actionsky ([e-mail])

(--apply-log works in any computer regardless of the hostname)

Licensed for use in a computer whose hostname is 'mysql01'

Expires 2009-6-1 (year-month-day) at 00:00

See http://www.innodb.com for further information

Type ibbackup --license for detailed license terms, --help for help

Contents of my.cnf:

innodb_data_home_dir got value /var/lib/mysql/data

innodb_data_file_path got value ibdata1:10M:autoextend

datadir got value /var/lib/mysql/data

innodb_log_group_home_dir got value /var/lib/mysql/binlog

innodb_log_files_in_group got value 3

innodb_log_file_size got value 10485760

Contents of backup.cnf:

innodb_data_home_dir got value /var/lib/mysql/backup

innodb_data_file_path got value ibdata1:10M:autoextend

datadir got value /var/lib/mysql/backup

innodb_log_group_home_dir got value /var/lib/mysql/backup

innodb_log_files_in_group got value 3

innodb_log_file_size got value 10485760

ibbackup: Found checkpoint at lsn 0 79531539

ibbackup: Starting log scan from lsn 0 79531520

090416 11:50:02 ibbackup: Copying log...

090416 11:50:02 ibbackup: Log copied, lsn 0 79531539

ibbackup: We wait 1 second before starting copying the data files...

090416 11:50:03 ibbackup: Copying /var/lib/mysql/data/ibdata1

ibbackup: A copied database page was modified at 0 79531539

ibbackup: Scanned log up to lsn 0 79531539

ibbackup: Was able to parse the log up to lsn 0 79531539

ibbackup: Maximum page number for a log record 0

090416 11:50:05 ibbackup: Full backup completed!

开始生成还原数据, 以前还原都是用--restore选项，不过这个选项在以后的版本会被干掉。

新版本都是用 --apply-log 来产生新的LOG文件。

[root@mysql01 backup]# /root/ibbackup --apply-log backup.cnf

InnoDB Hot Backup version 3.0.0; Copyright 2002-2005 Innobase Oy

License A15436 is granted to actionsky ([e-mail])

(--apply-log works in any computer regardless of the hostname)

Licensed for use in a computer whose hostname is 'mysql01'

Expires 2009-6-1 (year-month-day) at 00:00

See http://www.innodb.com for further information

Type ibbackup --license for detailed license terms, --help for help

Contents of backup.cnf:

innodb_data_home_dir got value /var/lib/mysql/backup

innodb_data_file_path got value ibdata1:10M:autoextend

datadir got value /var/lib/mysql/backup

innodb_log_group_home_dir got value /var/lib/mysql/backup

innodb_log_files_in_group got value 3

innodb_log_file_size got value 10485760

090416 11:50:11 ibbackup: ibbackup_logfile's creation parameters:

ibbackup: start lsn 0 79531520, end lsn 0 79531539,

ibbackup: start checkpoint 0 79531539

InnoDB: Doing recovery: scanned up to log sequence number 0 79531539

InnoDB: Starting an apply batch of log records to the database...

InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 Setting log file size to 0 10485760

Setting log file size to 0 10485760

Setting log file size to 0 10485760

ibbackup: We were able to parse ibbackup_logfile up to

ibbackup: lsn 0 79531539

ibbackup: Last MySQL binlog file position 0 249, file name /var/lib/mysql/binlog/mysql.000016

ibbackup: The first data file is '/var/lib/mysql/backup/ibdata1'

ibbackup: and the new created log files are at '/var/lib/mysql/backup/'

090416 11:50:12 ibbackup: Full backup prepared for recovery successfully!

现在目录下有IBDATA以及新的IB_LOG了。

[root@mysql01 backup]# ls -l

total 73840

-rw-r--r-- 1 mysql mysql 219 Apr 16 01:10 backup.cnf

-rw-r----- 1 root root 1024 Apr 16 11:50 ibbackup_logfile

-rw-r----- 1 root root 44040192 Apr 16 11:50 ibdata1

-rw-r----- 1 root root 10485760 Apr 16 11:50 ib_logfile0

-rw-r----- 1 root root 10485760 Apr 16 11:50 ib_logfile1

-rw-r----- 1 root root 10485760 Apr 16 11:50 ib_logfile2

-rw-r--r-- 1 mysql mysql 3608 Apr 16 11:51 log

-rw-r--r-- 1 mysql mysql 215 Apr 16 01:10 my.cnf

[root@mysql01 backup]#

整个的还原过程是：（笨办法）

1.先停掉MYSQLD。

2.COPY这些文件到现网的MySQL数据以及日志目录。

3.再次启动MYSQLD。

自己写脚本实现自动化，不过这脚本我就不贴了。