使用Xtrabackup备份MySQL数据库(续)

上一篇里,演示了使用xtrabackup对InnoDB进行在线物理热备,使用innobackupex脚本,可以很简单的一条命令行搞定InnoDB和MyISAM的物理备份。

本文则演示如何从xtrabackup的备份中进行恢复。本次恢复的是一个600GB大小的InnoDB数据库,备份的时候没有使用gzip压缩。

首先将备份好的tar文件解开到目标数据库的数据路径下,这一步类似oracle的restore database:

tar xvf mysqlbak.tar /opt/mysqldata

注意解出来的文件和目录的属主以及权限是否正确。如果是将备份恢复到一台全新的环境,则需要修改/etc/my.cnf,将innodb_data_file_path等参数设置和原备份的库一致。然后执行:

$innobackupex-1.5.1 –apply-log /opt/mysqldata

这一步类似于oracle的recover database,从日志来看,差不多一个小时执行完毕,该InnoDB数据库分配空间600GB,实际使用空间约590GB,并且数据的更新量还是比较大的,大约一个小时apply-log完成。运行的日志简单记录如下:

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy.
All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackup
prints “innobackup completed OK!”.

090708 09:50:44 innobackupex: Starting ibbackup with command:
xtrabackup –prepare –target-dir=/opt/mysqldata

xtrabackup Ver rc-0.7 for 5.0.77 unknown-linux-gnu (x86_64)
xtrabackup: cd to /opt/mysqldata
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2882535424, start_lsn=(514 2288109039)
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10000M;ibdata2:10000M;ibdata3:10000M…;ibdata60:10000M
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2882535424
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by –use-memory parameter)
InnoDB: Log scan progressed past the checkpoint lsn 514 2288109039
090708 9:50:45 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files…
InnoDB: Doing recovery: scanned up to log sequence number 514 2293351424 (0 %)
InnoDB: Doing recovery: scanned up to log sequence number 514 2298594304 (0 %)
InnoDB: Doing recovery: scanned up to log sequence number 514 2303837184 (0 %)
InnoDB: Doing recovery: scanned up to log sequence number 514 2309080064 (0 %)
090708 9:50:47 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
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 514 2314322944 (1 %)
InnoDB: Doing recovery: scanned up to log sequence number 514 2319565824 (1 %)
InnoDB: Doing recovery: scanned up to log sequence number 514 2324808704 (1 %)
InnoDB: Doing recovery: scanned up to log sequence number 514 2330051584 (1 %)
InnoDB: Doing recovery: scanned up to log sequence number 514 2335294464 (1 %)
…这里省略若干行
InnoDB: Doing recovery: scanned up to log sequence number 514 3881944064 (62 %)
InnoDB: Doing recovery: scanned up to log sequence number 514 3887186944 (62 %)
InnoDB: Doing recovery: scanned up to log sequence number 514 3887732530 (62 %)
090708 10:52:00 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
InnoDB: Apply batch completed
InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 0 350504077, file name mysql-bin.000748
InnoDB: Last MySQL binlog file position 0 36434864, file name /opt/mysqllog/mysql-bin.003015
090708 10:52:17 InnoDB: Started; log sequence number 514 3887732530

[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 36434864, file name /opt/mysqllog/mysql-bin.003015

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
090708 10:52:17 InnoDB: Starting shutdown…
090708 10:52:24 InnoDB: Shutdown completed; log sequence number 514 3887732530

090708 10:52:24 innobackupex: Restarting xtrabackup with command:
xtrabackup –prepare –target-dir=/opt/mysqldata
for creating ib_logfile*

xtrabackup Ver rc-0.7 for 5.0.77 unknown-linux-gnu (x86_64)
xtrabackup: cd to /opt/mysqldata
xtrabackup: This target seems to be already prepared.
xtrabackup: notice: xtrabackup_logfile was already used to ‘–prepare’.
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10000M;ibdata2:10000M;ibdata3:10000M;…;ibdata60:10000M
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 4
xtrabackup: innodb_log_file_size = 104857600
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by –use-memory parameter)
090708 10:52:25 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 100 MB
InnoDB: Database physically writes the file full: wait…
InnoDB: Progress in MB: 100
090708 10:52:25 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 100 MB
InnoDB: Database physically writes the file full: wait…
InnoDB: Progress in MB: 100
090708 10:52:26 InnoDB: Log file ./ib_logfile2 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile2 size to 100 MB
InnoDB: Database physically writes the file full: wait…
InnoDB: Progress in MB: 100
090708 10:52:26 InnoDB: Log file ./ib_logfile3 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile3 size to 100 MB
InnoDB: Database physically writes the file full: wait…
InnoDB: Progress in MB: 100
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
090708 10:52:27 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files…
InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 0 350504077, file name mysql-bin.000748
InnoDB: Last MySQL binlog file position 0 36434864, file name /opt/mysqllog/mysql-bin.003015
090708 10:52:27 InnoDB: Started; log sequence number 514 3887732748

[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 36434864, file name /opt/mysqllog/mysql-bin.003015

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
090708 10:52:27 InnoDB: Starting shutdown…
090708 10:52:29 InnoDB: Shutdown completed; log sequence number 514 3887732748
090708 10:52:29 innobackupex: innobackup completed OK!

运行完毕后,启动mysql即可。

觉得文章有用?立即: 和朋友一起 共学习 共进步!

猜您喜欢

文章评论: “使用Xtrabackup备份MySQL数据库(续)

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

*

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>