扫二维码与项目经理沟通
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
这篇文章将为大家详细讲解有关如何使用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 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数据库”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
微信二维码
移动版官网