分类： Mysql/postgreSQL
2009-04-21 09:46:26
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 (david.yang@actionsky.com)
(--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 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 (david.yang@actionsky.com)
(--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 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。
自己写脚本实现自动化，不过这脚本我就不贴了。
