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 ([email]david.yang@actionsky.com[/email])
(--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
[url]http://www.innodb.com[/url] 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 ([email]david.yang@actionsky.com[/email])
(--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
[url]http://www.innodb.com[/url] 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
。
自己写脚本实现自动化，不过这脚本我就不贴了。
本文转自 david_yeung 51CTO博客，原文链接：
http://blog.51cto.com/yueliangdao0608/151811
，如需转载请自行联系原作者
