Use XtraBackup to back up the MySQL database
Xtrabackup is an open source backup tool developed by percona, which is different from ibbackup. Ibbackup is a paid backup tool. In terms of its backup performance, ibbackup is inferior to Xtrabackup. Both ibbackup and Xtrabackup support both online physical full backup and online physical Incremental backup for the Innodb Storage engine. For MyISAM storage engine, only warm backup is supported. For more information, see http://www.percona.com/software/percona-xtrabackup.
Follow these steps to back up a mysql database using Xtrabackup:
I. Installation
1. Introduction
Xtrabackup is a mysql database backup tool provided by percona. According to the official introduction, Xtrabackup is the only open-source tool in the world that can hot backup innodb and xtradb storage engine databases. Xtradb is an enhanced version of Innodb, which is superior to Innodb in performance and has the following features:
(1) The backup process is fast and reliable;
(2) The backup process will not interrupt ongoing transactions;
(3) Saving disk space and traffic based on compression and other functions;
(4) automatic backup check;
(5) Fast Restoration;
2. Installation
Its latest software is available from the http://www.percona.com/software/percona-xtrabackup. This article is based on the RHEL5.8 system. Therefore, you can directly download the rpm package of the corresponding version to install it. here we will not describe the process.
After the installation is complete, A/usr/bin/innobackupex script file is generated, which encapsulates xtrabackup and can process Innodb and Myisam simultaneously. However, a read lock is required when processing Myisam.
3. About innobackupex
Innobackupex
Innobackupex is more powerful. It integrates xtrabackup and other functions. It not only supports full backup/recovery, but also supports Incremental backup and recovery based on time.
Innobackupex backup Principle
Innobackupex first calls xtrabackup to back up innodb data files. After xtrabackup is complete, innobackupex will view the xtrabackup_susponded file. Then, execute "flush tables with read lock" to back up other files.
Innobackupex recovery Principle
Innobackupex first reads my. cnf: Check that the directory corresponding to the variable (datadir, innodb_data_home_dir, innodb_data_file_path, innodb_log_group_home_dir) exists. After confirming that the directory exists, copy the myisam table and index first, then copy innodb tables, indexes, and logs
Percona XtraBackup 2.2.6 is released. This version is now available on the download page and the Percona software library. This version supports Ubuntu 14.10 and CentOS7.
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
II. Implementation of backup
1. Full backup
# Innobackupex -- user = DBUSER -- password = DBUSERPASS/path/to/BACKUP-DIR/
If you want to use a user with the minimum permission for backup, you can create such users based on the following command:
Mysql> create user 'bkpuser' @ 'localhost' identified by 's3cret ';
Mysql> revoke all privileges, grant option from 'bkpuser'; (if an error occurs, do not add it)
Mysql> grant reload, lock tables, replication client on *. * TO 'bkpuser' @ 'localhost ';
Mysql> flush privileges;
When innobakupex is used for backup, it will call xtrabackup to back up all InnoDB tables and copy all the files related to table structure definition (. frm), and files related to MyISAM, MERGE, CSV, and ARCHIVE tables. Files related to triggers and database configuration information are also backed up. These files will be saved to a time-based directory.
During backup, innobackupex creates the following files in the backup directory:
(1) xtrabackup_checkpoints -- Backup Type (such as full or incremental), backup status (such as whether it is already in prepared status), And LSN (log serial number) range information;
Each InnoDB page (usually 16 KB) contains a log serial number, that is, the LSN. The LSN is the system version number of the entire database system. The LSN related to each page can indicate how the page has changed recently.
(2) xtrabackup_binlog_info -- the binary log file currently in use by the mysql server and the location of the binary log event until the moment of backup.
(3) xtrabackup_binlog_pos_innodb -- the current position of the binary log file used for InnoDB or XtraDB tables.
(4) xtrabackup_binary -- The xtrabackup executable file used in backup;
(5) backup-my.cnf-the configuration option information used by the BACKUP command;
When innobackupex is used for backup, you can also use the -- no-timestamp Option to prevent the command from automatically creating a directory named after time. As a result, the innobackupex command creates a BACKUP-DIR directory to store backup data.
For more details, please continue to read the highlights on the next page: