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

How to understand Xtrabackup, I believe that many inexperienced people are at a loss about it. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Just Xtrabackup, talk about your own experience.

Xtrabackup includes two parts, innobackupex-1.5.1 and xtrabackup, and I am using the new version 0.9 source package. Innobackupex-1.5.1 is a perl script that outsources a layer of xtrabackup, adding support for the MyISAM storage engine and other options.

Innobackupex

Backup [here, full backup]

Innobackupex-1.5.1-user=root-password=123456 / backup/mysql 2 & > / backup/mysql/backup_out.log

Innobackupex-1.5.1 will generate a date-time HASH hash directory under the backup directory, such as / backup/mysql/2009-10-28 hash 10-40-11. At the same time, the running process can also be written to the log for later viewing.

Let's take a look at what important files innobackupex-1.5.1 has helped us back up.

Ls-1 / backup/mysql/2009-10-28 million 10-40-11

Backup-my.cnf-backup MySQL configuration file my.cnf

Ibdata1-backup InnoDB data space files

Mysql-backup mysql system library

Mysql-stderr-innobackupex-1.5.1 run error message

Mysql-stdout-innobackupex-1.5.1 running tracking record

Mytestdb-backup user-defined database

Test-backup test library

Xtrabackup_binlog_info-record binlog information and time points during backup

Xtrabackup_checkpoints-record backup type and time point

Xtrabackup_logfile-binary log

Attached: the mytestdb library contains tables for MyISAM and InnoDB storage engines

Ls-1 / backup/mysql/2009-10-28 million 10-40-11/mytestdb

Record.frm

Record.ibd

Shop.frm

Shop.MYD

Shop.MYI

Here we look at the table definition files, data files, and index files of the mytestdb,MyISAM and InnoDB storage engines that have been backed up ^ _ ^

Restore

-prepare log files

Innobackupex-1.5.1-- apply-log / backup/mysql/2009-10-28 million 10-40-11

Ls / backup/mysql/2009-10-28 InnoDB 10-40-11 We see more InnoDB log files in addition to the above files

Ib_logfile0 ib_logfile1

-copy backup files-- actual recovery [like MyISAM file copy]

Innobackupex-1.5.1-- copy-back / backup/mysql/2009-10-28 million 10-40-11

Ls-l / data/dbfile/

Looking at the server data file directory, we can see that all the backed-up files have come through copy.

Chown-R mysql.mysql / data/dbfile

Modify data directory permissions

Restart mysqld to take effect

Xtrabackup

Backup

(full backup)

#. / xtrabackup-backup-target-dir=/backup/mysql

(single db backup)

#. / xtrabackup-backup-tables=mytestdb.*-target-dir=/backup/mysql

(incremental backup)

#. / xtrabackup-backup-target-dir=/backup/mysql-incremental-basedir=/backup/mysql/inc

(prepare)

#. / xtrabackup-- prepare-- target-dir=/backup/mysql Note: literally explain that prepare means preparation-that is, it is not a direct recovery. Like innobackupex-1.5.1, you need to write the InnoDB log file ib_logfile*, so here are two times to complete the preparatory work before recovery.

When restoring, just copy the file directly to the data directory.

If you delete the database directory or InnoDB-related files by mistake, this will help you recover the complete data at that time; if you only want to restore it regularly, it will overwrite the files in the current data directory

Difference:

Innobackupex-1.5.1 backs up all database files [including table definition files, data files, index files]

Xtrabackup only backs up the tablename.ibd file, which is the data file of the InnoDB table, so we need to back up the table definition file or the database structure of dump separately, otherwise we can delete the data directory by mistake. Uh... The structure and data of the new dump may not be correct.

(apply incremental backup)

#. / xtrabackup-prepare-target-dir=/backup/mysql-incremental-dir=/backup/mysql/inc

Chown-R mysql.mysql / data/dbfile

Modify data directory permissions

Restart mysqld to take effect

Incremental backup to be studied, To be continued. [@ more@]

Innobackupex-1.5.1 backup extension

Package backup [package must specify a stream-- stream=tar]

Tar package

Innobackupex-1.5.1-user=root-password=123456-stream=tar / backup/mysql 2 & > / backup/mysql/backup_out.log 1 > / data/backup/mysql/last.tar

Gz package

Innobackupex-1.5.1-user=root-password=123456-stream=tar / backup/mysql 2 & > / backup/mysql/backup_out.log | gzip > / backup/mysql/last.tar.gz

Remote backup

Nnobackupex-1.5.1-user=root-password=123456-stream=tar / backup/mysql | ssh user@192.168.x.x gzip ">" / backup/mysql/backup.tar.gz

Specify database backup

Innobackupex-1.5.1-- user=root-- password=123456-- databases=mytestdb-- stream=tar / backup/mysql | gzip > / backup/mysql/mytestdb.tar.gz

We need to talk about the decompression of the package.

# mkdir / backup/mysql/tmp

# tar ixzvf last.tar.gz-Ctmp [you must specify the I parameter to get all files, otherwise it's just backup-my.cnf]

After reading the above, have you mastered how to understand Xtrabackup? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.