Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to use Xtrabackup backup MySQL database", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to use Xtrabackup backup MySQL database" this article.

The backup of MySQL database has always been a headache. Although there are many kinds of tools, one is really easy to use, but it is very difficult to find. Mysqldump can be used as a logical backup tool for data, but it cannot be used for online hot backup. Without a physical backup tool, the recovery time will be unacceptably long when the amount of data is relatively large. InnoDB does have a commercial InnoDB Hotbackup that enables online hot backup of InnoDB engine tables. Recently found a tool, percona's Xtrabackup, is an open source alternative to InnoDB Hotbackup, you can online physical backup of InnoDB/XtraDB engine tables, try it, very good, it is worth recommending to MySQL DBA.

The following is an example of an actual backup, which uses gzip to compress the backup stream. The size of the compressed database is about 20GB, and the compressed size is 340MB. Of course, the compressed size is related to the actual space used in the database. The backup time is about 6 minutes and 44 seconds.

[@ more@]

Innobackupex is modified with reference to InnoDB Hotbackup's innoback script, mainly to facilitate the simultaneous backup of InnoDB and MyISAM engine tables, and to add some options to use. For example,-slave-info can record some information needed by slave after backup and recovery. According to this information, you can easily use backup to redo slave.

Note that you need to use the-I parameter when unpacking from the backed up tar package. The latest release is version 0.7, click here to download

MySQL DBA, how do you make backups? Welcome to discuss here.

$innobackupex-1.5.1-- user=root-- stream=tar / bak/-- slave-info | gzip > / bak/bak_mysql.tar.gzInnoDB Backup Utility v1.5.1 Please check that the backup run completes successfully; Copyright 2003, 2009 Please check that the backup run completes successfully 2, June 1991.IMPORTANT. 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). Several lines are omitted here > > 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). Some lines are omitted here > > 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' (206files) > > 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}'(207files) 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): '0and 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.1The scene filename' mysql-bin.000006' Position 227207755090625 15:29:44 innobackupex: innobackup completed Okumbinnobackupex: You must use-I (--ignore-zeros) option for extraction of the tar stream. The above is all the contents of the article "how to use Xtrabackup to back up MySQL database". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.