怎么使用Xtrabackup备份MySQL数据库

这篇文章主要为大家展示了"怎么使用Xtrabackup备份MySQL数据库"，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下"怎么使用Xtrabackup备份MySQL数据库"这篇文章吧。

MySQL数据库的备份，一直是个比较头痛的问题。各种工具虽然不少，但一个真正好用易用的，却又非常难找。Mysqldump做为数据的逻辑备份 工具还行，但是无法进行在线热备，而没有物理备份工具，在数据量比较大的时候，恢复的时间也会长得无法接受。InnoDB倒是有个商业的InnoDB Hotbackup，可以对InnoDB引擎的表实现在线热备。最近发现了一个工具，percona出品的Xtrabackup，是InnoDB Hotbackup的一个开源替代品，可以在线对InnoDB/XtraDB引擎的表进行物理备份，试用了一下，非常的不错，值得向MySQL DBA们推荐。

下面是一个实际备份的例子，采用了gzip将备份流进行压缩，约20GB的数据库，压缩后大小为340MB，当然，压缩后的大小跟数据库中实际使用的空间是相关的。备份时间约6分44秒。

[@more@]

innobackupex是参考了InnoDB Hotbackup的innoback脚本修改而来的，主要是为了方便的同时备份InnoDB和MyISAM引擎的表，并且加入了一些使用的选项。如 -slave-info可以记录备份恢复后，作为slave需要的一些信息，根据这些信息，可以很方便的利用备份来重做slave。

注意，从备份后的tar包解包的时候，需要使用-i参数。最新发布的是0.7版，猛击这里下载

MySQL DBA们，你是如何做备份的呢？欢迎到这里探讨。

$innobackupex-1.5.1 --user=root --stream=tar /bak/ --slave-info | gzip > /bak/bak_mysql.tar.gzInnoDB 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 backup run completes successfully.           At the end of a successful backup run innobackup           prints "innobackup completed OK!".innobackupex: Using mysql  Ver 14.12 Distrib 5.0.67, for redhat-linux-gnu (x86_64) using  EditLine wrapperinnobackupex: Using mysql server version 5.0.67-loginnobackupex: Created backup directory /bak090625 15:23:00  innobackupex: Starting mysql with options: --unbuffered --user=root090625 15:23:00  innobackupex: Connected to database with mysql child process (pid=3431)090625 15:23:04  innobackupex: Connection to database server closed090625 15:23:04  innobackupex: Starting ibbackup with command:xtrabackup --backup --suspend-at-end --log-stream --target-dir=./innobackupex: Waiting for ibbackup (pid=3565) to suspendinnobackupex: Suspend file '/opt/mysqldata/xtrabackup_suspended'xtrabackup: suspend-at-end is enabled.xtrabackup: uses posix_fadvise().xtrabackup: cd to /opt/mysqldataxtrabackup: Target instance is assumed as followings.xtrabackup:   innodb_data_home_dir = /opt/mysqldataxtrabackup:   innodb_data_file_path = ibdata1:10G;ibdata2:10Gxtrabackup:   innodb_log_group_home_dir = ./xtrabackup:   innodb_log_files_in_group = 4xtrabackup:   innodb_log_file_size = 104857600xtrabackup: use O_DIRECTxtrabackup: Stream mode.>> log scanned up to (0 3053406941)090625 15:23:06  innobackupex: Continuing after ibbackup has suspendedinnobackupex: Starting to backup InnoDB tables and indexesinnobackupex: from original InnoDB data directory '/opt/mysqldata'innobackupex: Backing up as tar stream 'ibdata1'>> log scanned up to (0 3053416714)...这里省略若干行>> log scanned up to (0 3054123851)tar: ibdata1: file changed as we read itinnobackupex: Backing up as tar stream 'ibdata2'>> log scanned up to (0 3054142116)...这里省略若干行>> log scanned up to (0 3054618483)innobackupex: Backing up file '/opt/mysqldata/test/sp.ibd'innobackupex: Backing up file '/opt/mysqldata/test/tmp_dy.ibd'innobackupex: Backing up files '/opt/mysqldata/testdb/*.ibd' (206 files)>> log scanned up to (0 3054638401)>> log scanned up to (0 3054668860)tar: testdb/group_group_thread_0027.ibd: file changed as we read it>> log scanned up to (0 3054695015)>> log scanned up to (0 3054928216)tar: testdb/group_thread_reply_0007.ibd: file changed as we read it>> log scanned up to (0 3054952588)>> log scanned up to (0 3055005439)tar: testdb/group_user_0001.ibd: file changed as we read it>> log scanned up to (0 3055028610)>> log scanned up to (0 3055044650)tar: testdb/group_user_0006.ibd: file changed as we read it>> log scanned up to (0 3055060461)innobackupex: Backing up file '/opt/mysqldata/testdb/comments.ibd'innobackupex: Backing up file '/opt/mysqldata/testdb/testdb.ibd'innobackupex: Backing up file '/opt/mysqldata/testdb/testdb_content.ibd'innobackupex: Backing up file '/opt/mysqldata/testdb/testdb_ids.ibd'090625 15:29:17  innobackupex: Starting mysql with options: --unbuffered --user=root090625 15:29:17  innobackupex: Connected to database with mysql child process (pid=5458)>> log scanned up to (0 3055072495)090625 15:29:21  innobackupex: Starting to lock all tables...>> log scanned up to (0 3055087148)>> log scanned up to (0 3055119993)090625 15:29:39  innobackupex: All tables locked and flushed to disk090625 15:29:39  innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,innobackupex: .TRG, .TRN, and .opt files ininnobackupex: subdirectories of '/opt/mysqldata'innobackupex: Backing up file '/opt/mysqldata/test/sp.frm'innobackupex: Backing up file '/opt/mysqldata/test/tmp_dy.frm'innobackupex: Backing up files '/opt/mysqldata/testdb/*.{frm,MYD,MYI,MRG,TRG,TRN,opt}' (207 files)innobackupex: Backing up file '/opt/mysqldata/testdb/comments.frm'innobackupex: Backing up file '/opt/mysqldata/testdb/testdb.frm'innobackupex: Backing up file '/opt/mysqldata/testdb/testdb_content.frm'innobackupex: Backing up file '/opt/mysqldata/testdb/testdb_ids.frm'innobackupex: Backing up file '/opt/mysqldata/testdb/db.opt'innobackupex: Backing up files '/opt/mysqldata/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,opt}' (52 files)090625 15:29:40  innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, and .opt filesinnobackupex: Resuming ibbackupxtrabackup: The latest check point (for incremental): '0:3054881736'>> log scanned up to (0 3055120013)xtrabackup: Transaction log of lsn (0 3053102766) to (0 3055120013) was copied.090625 15:29:44  innobackupex: All tables unlocked090625 15:29:44  innobackupex: Connection to database server closedinnobackupex: Backup created in directory '/bak/'innobackupex: MySQL binlog position: filename '', positioninnobackupex: MySQL slave binlog position: master host '127.0.0.1',filename 'mysql-bin.000006', position 227207755090625 15:29:44  innobackupex: innobackup completed OK!innobackupex: You must use -i (--ignore-zeros) option for extraction of the tar stream.

以上是"怎么使用Xtrabackup备份MySQL数据库"这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注行业资讯频道！