如何使用Xtrabackup备份MySQL数据库

这篇文章将为大家详细讲解有关如何使用Xtrabackup备份MySQL数据库，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

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

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

[@more@]

tar -ixvf mysqlbak.tar /opt/mysqldata

(注意这里一定要加i参数，不然无法解压出来)

注意解出来的文件和目录的属主以及权限是否正确。如果是将备份恢复到一台全新的环境，则需要修改/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 underthe 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/mysqldataxtrabackup  Ver rc-0.7 for 5.0.77 unknown-linux-gnu (x86_64)xtrabackup: cd to /opt/mysqldataxtrabackup: 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:10000Mxtrabackup:   innodb_log_group_home_dir = ./xtrabackup:   innodb_log_files_in_group = 1xtrabackup:   innodb_log_file_size = 2882535424xtrabackup: 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 2288109039090708  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 2526 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 5051 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 7576 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99InnoDB: Apply batch completedInnoDB: 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 2526 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 5051 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 7576 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99InnoDB: Apply batch completedInnoDB: In a MySQL replication slave the last master binlog fileInnoDB: position 0 350504077, file name mysql-bin.000748InnoDB: Last MySQL binlog file position 0 36434864, file name /opt/mysqllog/mysql-bin.003015090708 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.003015xtrabackup: starting shutdown with innodb_fast_shutdown = 1090708 10:52:17  InnoDB: Starting shutdown...090708 10:52:24  InnoDB: Shutdown completed; log sequence number 514 3887732530090708 10:52:24  innobackupex: Restarting xtrabackup with command:xtrabackup --prepare --target-dir=/opt/mysqldatafor creating ib_logfile*xtrabackup  Ver rc-0.7 for 5.0.77 unknown-linux-gnu (x86_64)xtrabackup: cd to /opt/mysqldataxtrabackup: 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:10000Mxtrabackup:   innodb_log_group_home_dir = ./xtrabackup:   innodb_log_files_in_group = 4xtrabackup:   innodb_log_file_size = 104857600xtrabackup: 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 createdInnoDB: Setting log file ./ib_logfile0 size to 100 MBInnoDB: Database physically writes the file full: wait...InnoDB: Progress in MB: 100090708 10:52:25  InnoDB: Log file ./ib_logfile1 did not exist: new to be createdInnoDB: Setting log file ./ib_logfile1 size to 100 MBInnoDB: Database physically writes the file full: wait...InnoDB: Progress in MB: 100090708 10:52:26  InnoDB: Log file ./ib_logfile2 did not exist: new to be createdInnoDB: Setting log file ./ib_logfile2 size to 100 MBInnoDB: Database physically writes the file full: wait...InnoDB: Progress in MB: 100090708 10:52:26  InnoDB: Log file ./ib_logfile3 did not exist: new to be createdInnoDB: Setting log file ./ib_logfile3 size to 100 MBInnoDB: Database physically writes the file full: wait...InnoDB: Progress in MB: 100InnoDB: The log sequence number in ibdata files does not matchInnoDB: 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 fileInnoDB: position 0 350504077, file name mysql-bin.000748InnoDB: Last MySQL binlog file position 0 36434864, file name /opt/mysqllog/mysql-bin.003015090708 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.003015xtrabackup: starting shutdown with innodb_fast_shutdown = 1090708 10:52:27  InnoDB: Starting shutdown...090708 10:52:29  InnoDB: Shutdown completed; log sequence number 514 3887732748090708 10:52:29  innobackupex: innobackup completed OK!

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

关于"如何使用Xtrabackup备份MySQL数据库"这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。