怎么使用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.gz 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 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 wrapper innobackupex: Using mysql server version 5.0.67-log innobackupex: Created backup directory /bak 090625 15:23:00 innobackupex: Starting mysql with options: --unbuffered --user=root 090625 15:23:00 innobackupex: Connected to database with mysql child process (pid=3431) 090625 15:23:04 innobackupex: Connection to database server closed 090625 15:23:04 innobackupex: Starting ibbackup with command: xtrabackup --backup --suspend-at-end --log-stream --target-dir=./ innobackupex: Waiting for ibbackup (pid=3565) to suspend innobackupex: Suspend file '/opt/mysqldata/xtrabackup_suspended' xtrabackup: suspend-at-end is enabled. xtrabackup: uses posix_fadvise(). xtrabackup: cd to /opt/mysqldata xtrabackup: Target instance is assumed as followings. xtrabackup: innodb_data_home_dir = /opt/mysqldata xtrabackup: innodb_data_file_path = ibdata1:10G;ibdata2:10G xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 4 xtrabackup: innodb_log_file_size = 104857600 xtrabackup: use O_DIRECT xtrabackup: Stream mode. >> log scanned up to (0 3053406941) 090625 15:23:06 innobackupex: Continuing after ibbackup has suspended innobackupex: Starting to backup InnoDB tables and indexes innobackupex: 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 it innobackupex: 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=root 090625 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 disk 090625 15:29:39 innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI, innobackupex: .TRG, .TRN, and .opt files in innobackupex: 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 files innobackupex: Resuming ibbackup xtrabackup: 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 unlocked 090625 15:29:44 innobackupex: Connection to database server closed innobackupex: Backup created in directory '/bak/' innobackupex: MySQL binlog position: filename '', position innobackupex: MySQL slave binlog position: master host '127.0.0.1', filename 'mysql-bin.000006', position 227207755 090625 15:29:44 innobackupex: innobackup completed OK! innobackupex: You must use -i (--ignore-zeros) option for extraction of the tar stream.
以上是“怎么使用Xtrabackup备份MySQL数据库”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注创新互联-成都网站建设公司行业资讯频道!
名称栏目:怎么使用Xtrabackup备份MySQL数据库-创新互联
URL链接:http://scyanting.com/article/cepheh.html