MySQL online backup and recovery tools -- & gt; XtraBackup

MySQL online backup and recovery tools --> XtraBackup

1 Overview of Xtrabackup principles

Xtrabackup is a tool used to back up InnoDB data. It supports online Hot Backup (data read/write is not affected during Backup) and is a good alternative to InnoDB Hotbackup.

Xtrabackup has two main tools: xtrabackup and innobackupex.
(1) xtrabackup can only back up InnoDB and XtraDB data tables, but cannot back up MyISAM data tables.

(2) innobackupex was modified based on the innoback script of InnoDB Hotbackup. innobackupex is a perl script encapsulation that encapsulates xtrabackup. Therefore, innodb and myisam can be backed up at the same time, however, you need to add a read lock when processing myisam. Some use options are added. For example, slave-info can record the information required by slave after Backup recovery. Based on this information, you can easily use backup to redo slave.

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

(4) What can Xtrabackup do?
Online (hot) backup of InnoDB and XtraDB tables of the entire database
Incremental Backup (innodb only) based on the last full-database backup of xtrabackup)
Backup is generated in the form of a stream and can be saved directly to a remote machine (useful when the local hard disk space is insufficient)
The tools provided by the MySQL database itself do not support real Incremental backup. binary log recovery is point-in-time recovery rather than Incremental backup. The Xtrabackup tool supports Incremental backup of the InnoDB Storage engine. The working principle is as follows:
 
(1) complete a full backup and record the LSN (Log Sequence Number) of the checkpoint ).

(2) during Incremental backup, compare whether the LSN of each page in the tablespace is greater than the LSN of the last backup. If yes, back up the page and record the LSN of the current checkpoint.
First, find and record the last checkpoint ("last checkpoint LSN") in the logfile, and then copy the InnoDB logfile to xtrabackup_logfile starting from the location of the LSN. Then, start copying all data files. ibd; stops copying logfiles only after all data files are copied.
 
Because all the data changes are recorded in the logfile, the data file has been modified during the backup process, and the data can still be consistent by parsing xtrabackup_logfile during restoration.
 
Xtrabackup backup Principle
XtraBackup is based on InnoDB's crash-recovery function. It copies the data file of innodb. Because the table is not locked, the copied data is inconsistent, and crash-recovery is used during recovery to ensure data recovery is consistent.

InnoDB maintains a redo log, also known as the transaction log, which contains all changes to innodb data. When InnoDB is started, it checks data file and transaction log first, and performs two steps:

During backup, xtraBackup copies innodb data one page at a time without locking the table. At the same time, XtraBackup has another thread that monitors the transactions log. Once the log changes, copy the changed log pages. Why are we in a hurry to copy it? Because the transactions log file is limited in size, it will be written from the beginning after it is fully written, so the new data may overwrite the old data.
During the prepare process, XtraBackup uses the copied transactions log to perform crash recovery ing on the backed-up innodb data file.

(5) Implementation Details
XtraBackup open the innodb data file in read-write mode, and then copy it. In fact, it will not modify this file. That is to say, the user running XtraBackup must have read and write permissions on the data files of innodb. The read-write mode is used because XtraBackup uses its built-in innodb library to open files, while the innodb Library Opens files with rw.
 
XtraBackup needs to copy a large amount of data from the file system, so it tries its best to use posix_fadvise () to tell the OS not to cache the data read to improve performance. Because the data will not be reused, but the OS is not so smart. If you want to cache a few GB of data, it will put a lot of pressure on the virtual memory of the OS. other processes, such as mysqld, may be pushed out by swap, in this way, the system will be greatly affected.
 
During innodb page backup, XtraBackup reads and writes 1 MB of data each time, and 1 MB/16 KB = 64 pages. This cannot be configured. After reading 1 MB of data, XtraBackup traverses the 1 mb data page by page. Use the buf_page_is_upted () function of innodb to check whether the data on this page is normal. If the data is abnormal, read this page again and read the page again for up to 10 times. If the page still fails, the backup will fail and exit. Each time a transactions log is copied, KB of data is read and written. It cannot be configured.

2.1 Xtrabackup Open-Source Tool

Supports online hot backup and recovery
When there is a large amount of data, Backup recovery is fast
Backup Recovery in replication Mode
Disadvantage: during Incremental backup, the full backup files used as the backup base cannot be compressed; otherwise, the backup fails. When the Incremental backup is used, if the table structure is changed, some backup changes are invalid.
Install 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 (40G 45 minutes)
Decompress: [root @ localhost ~] # Tar -izxvfalldb.tar.gz-C backup

2.3 recovery

Stop Database service mysqldstop first;
(1): innobackupex -- apply-log -- user = root -- defaults-file =/etc/my. cnf -- no-lock/root/alldb
(2): Back up and delete all files in the original directory/open/mbdata/open /.
(3): innobackupex -- copy-back -- user = root -- defaults-file =/etc/my. cnf -- no-lock/root/alldb
[] If the disk is not enough, directly [root @ clonedb-m209alldb] # mv */data/mbdata/open/

(4): chown-Rmysql: mysql/data/mbdata/open
(5): service mysqldstart;

2.4 Incremental Backup

You must have a directory of uncompressed full backup files.
(1): Full backup on Monday,
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) Incremental backup for the remaining Tuesday to Sunday;

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-basedir =/home/backup/all/alldb/home/backup/daily/daily_20120409 2>/home/ backup/daily/dailybackup _ $ tday. log

2.5 incremental recovery (1)

Decompress the file
Restore the entire volume first, and then restore the incremental data.
(1): first restore the full backup innobackupex -- apply-log -- user = root -- defaults-file =/data/mbdata/my. cnf -- no-lock/home/backup/all/alldb

PS:/home/backup/all/alldb is the full backup directory

(2) restore the 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 the Incremental backup Directory

(3): Stop the database, servicemysqldstop; then back up and delete all files in the original directory/open/mbdata/open /.
(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 on to mysql and verify the data.
Total recovery time: about 45 minutes

2.6 Remote Backup

/Usr/bin/innobackupex -- user = root -- port = 3306 -- defaults-file =/data/mbdata/open/my. cnf -- slave-info -- socket =/data/mbdata/open/mysql. sock -- no-timestamp -- remote-host = root@10.100.200.53 -- scpopt = "-Cp-carcfour"/home/backup/all/alldb _ $ tday2>/home/backup/all/ allbackup. log
Ps: $ tday2 is a variable

MySQL management-using XtraBackup for Hot Backup

MySQL open-source backup tool Xtrabackup backup deployment

MySQL Xtrabackup backup and recovery

Use XtraBackup to implement MySQL master-slave replication and quick deployment [master-slave table lock-free]

Install and use Xtrabackup from Percona to back up MySQL

XtraBackup details: click here
XtraBackup: click here

This article permanently updates the link address: