MySQL Online backup and recovery Tool-xtrabackup

1 Introduction to the principle of xtrabackup

Xtrabackup is a innodb to do data backup tools, Support Online hot backup (backup without affecting the data read and write), is a commercial backup tool InnoDB hotbackup a good alternative.
Xtrabackup has two main tools: Xtrabackup, Innobackupex
(1) Xtrabackup can only back up InnoDB and xtradb two data tables, but not myisam data tables

(2) Innobackupex is a reference to the InnoDB hotbackup innoback script modified, Innobackupex is a Perl script package, encapsulated Xtrabackup, So the InnoDB and MyISAM can be backed up at the same time, but a read lock is required to handle the MyISAM. and added some options to use, such as slave-info can record backup after recovery, as slave need some information, according to this information, can easily use Backup to redo slave.

(3) Use Help: Http://www.percona.com/docs/wiki/percona-xtrabackup:start

(4) What xtrabackup can do
Online (Hot) backup InnoDB, xtradb tables for the entire library
Incremental backup based on Xtrabackup's last backup of the whole library (InnoDB only)
Generate backups as streams that can be saved directly to a remote machine (useful when the hard disk space is low)
The MySQL database itself provides tools that do not support true incremental backups, and binary log recovery is point-in-time (Point-in-time) recovery instead of incremental backups. The Xtrabackup tool supports incremental backups of the InnoDB storage engine and works as follows:

(1) Complete a full backup first and record the LSN (log Sequence number) of the checkpoint at this point.

  (2) In a process incremental backup, the LSN of each page in the table space is compared to the LSN at the time of the last backup, and if so, the page is backed up, and the LSN of the current checkpoint is logged.   
  First, find and record the last checkpoint in logfile. Then start copying the InnoDB's logfile to xtrabackup_logfile from the LSN location, and then start copying all of the data files. ibd; After copying all the data files, the copy logfile is stopped. &NBSP
 
  Because all data modifications are recorded in the logfile, the data files are modified immediately during the backup process, and the data can still be consistent by parsing xtrabackup_logfile while recovering.  
 
Xtrabackup backup principle  
  Xtrabackup InnoDB based crash-recovery function. It replicates the data file of InnoDB, because it does not lock the table, the copied information is inconsistent, use crash-recovery when recovering, make the data recovery consistent.  

  InnoDB maintains a redo log, also known as transaction log, the transaction log, which contains all the changes to the InnoDB data. When InnoDB is started, it will check the data file and transaction log, and will do two steps:  

 
  Xtrabackup when backing up, a page by page copy of InnoDB data, and do not lock the table, at the same time, xtrabackup there is another thread monitoring transactions log, once the log changes, the changed log Pages copied away. Why the rush to copy away? Because the transactions log file is limited in size, when it is full, it starts from scratch, so the new data may overwrite the old data.  
  In the prepare process, Xtrabackup uses the transactions log that is copied to InnoDB crash the backed up recovery data file.  

(5) Implementation Details
Xtrabackup Open the InnoDB data file in Read-write mode and then copy it. It does not actually modify this file. That is, the user running xtrabackup must have read and write access to the InnoDB data file. The reason for using the read-write mode is because Xtrabackup uses its built-in InnoDB library to open the file, while the InnoDB library opens the file with RW.

Xtrabackup to replicate large amounts of data from the file system, so it uses posix_fadvise () as much as possible to tell the OS not to cache the data it reads to improve performance. Because this data is not reused, the OS is not so smart. If you want to cache, a few g of data, the OS virtual memory will be a great pressure, other processes, such as mysqld is likely to be swap out, so the system will be greatly affected.

In the process of backing up InnoDB page, Xtrabackup reads and writes 1MB of data each time, 1mb/16kb=64 a page. This is not configurable. After reading the 1MB data, xtrabackup the 1MB data one page at a time, using InnoDB's buf_page_is_corrupted () function to check if the data on this page is normal, and if the data is not normal, reread the page and reread it up to 10 times. If it fails, the backup fails and exits. When copying transactions log, read and write 512KB data at a time. The same cannot be configured.

2.1 xtrabackup Open Source Tools

•Support Online hot standby and recovery•large data volumes, backup recovery is faster•supports backup recovery from slave backup in replication mode•disadvantage: In the incremental backup, the backup base of the full file can not be compressed, otherwise the backup fails; When the table structure changes, the change partial backup is invalid. •Installing the rpm version •rpm-ivhxtrabackup-1.6-245.rhel5.x86_64.rpm
2.2 compression Backup and decompression
•Backup:innobackupex --user=root--port=3306--defaults-file=/etc/my.cnf--no-lock--socket=/data/mbdata /open/mysql.sock--stream=tar/home/backup/all/alldb | Gzip 1>/home/backup/all/alldb.tar.gz( minutes ) •decompression:[[email protected]~]# tar-izxvf alldb.tar.gz-C backup
2.3 Recovery
•Stop Database service mysqld stop first ; •(1):innobackupex--apply-log--user=root--defaults-file=/etc/my.cnf--no-lock/root/alldb •(2): Back Up and delete the original directory /open/mbdata/open/ All Files •(3):innobackupex--copy-back--user=root--defaults-file=/etc/my.cnf--no-lock/root/alldb

"" if the disk is not enough, direct [[email protected]alldb]#mv */data/mbdata/open/

•(4):chown-Rmysql:mysql/data/mbdata/open •(5):service mysqldstart;
2.4 Incremental backup
•must have uncompressed full-volume backup file directory, first full-amount re-increment•(1): Monday Full-scale backup,

Time/usr/bin/innobackupex --user=root--password=******--host=127.0.0.1--port=3307-- DEFAULTS-FILE=/ETC/MY.CNF--slave-info--socket=/data/mbdata/open/mysql.sock--no-timestamp/home/ backup/all/alldb 2>$dirlog

•(2): The remaining Tuesday to Sunday makes an incremental backup ;

2.5 Incremental backup script

•Time /usr/bin/innobackupex --user=root--password=****--socket=/data/mbdata/open/ Mysql.sock--port=3307--slave-info--defaults-file=/etc/my.cnf--no-timestamp--safe-slave-backup--incremental --incremental-basedir=/home/backup/all/alldb/home/backup/daily/daily_20120409 2>>/home /backup/daily/dailybackup_$tday.log

2.5 Incremental Recovery (1)

• Extract Files • Full recovery first, then incremental recovery • (1): First restore full standby innobackupex--apply-log--user=root--defaults-file=/data/mbdata/my.cnf--no-lock/ Home/backup/all/alldb

PS:/home/backup/all/alldb is a full-scale backup directory

• (2): Restore incremental backup Innobackupex/home/backup/all/alldb--incremental–incremental dir=/home/backup/daily/daily_2012_03_22 --apply-log

PS:/home/backup/daily/daily_2012_03_22 is an incremental backup directory

• (3): Stop the database, servicemysqldstop; then back up and delete the original directory/open/mbdata/open/all Files • (4): Restore the database to the default directory Innobackupex--copy-back--user= Root--DEFAULTS-FILE=/ETC/MY.CNF--no-lock/home/backup/all/alldb

2.5 Incremental Recovery (2)

• (5): Chown-r mysql:mysql/data/mbdata/open (6): Service mysqldstart; (7): Log in to MySQL and verify the data. • Total recovery time: about a minute

2.6 Remote Backup

•/usr/bin/innobackupex --user=root--port=3306--defaults-file=/data//open/my.cnf --slave-info --socket=/data/ mbdata/open/--no-timestamp [email protected]--= "-cp-carcfour alldb_$tdayPS:$tday2 is a variable

MySQL Online backup and recovery Tool-xtrabackup