Use Case of MySQL quick backup tool XtraBackup in production environment

There are many ways to back up MySQL. The most common method is MySQLdump. Export the database to an SQL file. When restoring, sourcedb. SQL is executed cheerfully. Small

There are many ways to back up MySQL. The most common method is MySQLdump. Export the database to an SQL file. When restoring, source db. SQL is executed happily. Small

There are many ways to back up MySQL. The most common method is MySQLdump. Export the database to an SQL file. When restoring, source db. SQL is executed happily. Our friends happily put on a shell script with cron, and happily watched the generation of backup files every day, feeling safe. What if a MySQL instance exceeds 10 Gb? Believe me, it may take several hours to export data. It will take a longer time to restore the data. I used SQL to restore a 6 GB database and spent more than three hours.

The disadvantages of the logical backup such as MySQLdump are not mentioned. Let's talk about the xtrabackup and PERCONA. It's worth mentioning. Official notes:

1. fast and reliable

2. transactions are not interrupted during the backup process.

3. Saving disk space and network bandwidth

4. Automatic Backup Verification

5. high-availability rows due to rapid restoration

As mentioned above, I believe it, or the database with more than 6 GB, and it takes 5 minutes to restore the database. Remember, if you restore Data Using backup for a few hours, the boss will have an idea! Haha.

Operating System: CentOS release 6.5

MySQL: 5.6.21

I. Installation

Yum install

Yum install percona-xtrabackup

Ii. Start Using

Xtrabackup includes innobackupex, xtrabackup, xbcrypt, and xbstream tools. Innobackupex supports MyISAM and InnoDB. All, I choose to use innobackupex.

1. Create a backup user. You know, minimum permission.
Mysql> create user 'bkuser' @ 'localhost' identified by 'bk2015 ';
Query OK, 0 rows affected (0.16sec)
Mysql> grant reload, locktables, replication client on *. * to 'bkuser' @ 'localhost ';
Query OK, 0 rows affected (0.00sec)
Mysql> flush privileges;
Query OK, 0 rows affected (0.01sec)

2. Use innobackupex for full database backup
Innobackupex -- user = bkuser -- password = bk2015/mnt/backups

-- User: the user name used to connect to the database.
-- Password: password used to connect to the database
/Mnt/backups backup file storage path

After the command is executed, the directory 2015-02-03_15-11-51 is generated under the/mnt/backups Directory, which is a complete backup of our current database. This directory is the directory automatically generated by innobackupex. A parameter can control this option -- no-timestamp. If this parameter is added, the directory will not be automatically generated, and the backup will be created under the provided path. This parameter is easy to use in the script.

3. Use full backup for database Restoration

Restore preparation
Innobackupex -- apply-log/mnt/backups/2015-02-03_15-11-51

-- Some uncommitted transactions may exist in the backup created by apply-log, which must be rolled back or played back from the log.

Restore
Stop the mysql service,
Service mysqld stop

Back up the original mysql DATA DIRECTORY
Mv/mnt/data/mysql/mnt/data/mysql20150203

Restore

Innobackupex -- copy-back/mnt/backups/2015-02-03_15-11-51/

Change directory permissions and start the service

Chown-R mysql. mysql/mnt/data/mysql

Service mysqld start

4. innobackupex Incremental Backup
Incremental backup is based on one backup. On the basis of full backup, the next backup only backs up data that has changed from the current time to the last time. The biggest benefit is that it saves disk space. Of course, the bad thing is restoration, and the restoration process will become complicated.

4.1 perform full backup first
Innobackupex -- user = bkuser -- password = bk2015/mnt/backups

The backup directory contains xtrabackup-checkpoints, which is as follows:
Backup_type = full-backuped
From_lsn = 0
To_lsns = 1291135

Create Incremental Backup
Innobackupex -- incremental/mnt/backups -- incremental-basedir =/mnt/backups/2015-02-03_15-11-51 -- incremental uses this parameter to create an incremental backup
-- Incremental-basedir specifies the directory of the last backup. For example, this is the first incremental backup after the full backup. This directory is a complete directory. If this is the second incremental backup, this directory is the first directory to be added for backup.

Check the content of the xtraback_checkpoint file in the file added to the backup directory. The lsn is changed.
Backup_type = incrementalfrom_lsn = 1291135to_lsn = 1352113

5. Restore Incremental Backup

5.1 restore preparation
Process: -- apply-log is applied to the full backup directory. The first step is to add backup, and the second step is to add backup ...... The N-step Incremental backup is equivalent to a new full backup + all incremental backups after all applications are completed.

Innobackupex -- apply-log -- redo-only BASE-DIR

Innobackupex -- apply-log -- redo-only BASE-DIR -- incremental-dir = INCREMENTAL-DIR-1innobackupex -- apply-log BASE-DIR -- incremental-dir = INCREMENTAL-DIR-2BASE-DIR full backup directory
First incremental INCREMENTAL-DIR-1
INCREMENTAL-DIR-2 secondary Incremental Backup

-- Redo-only is important. This parameter must be specified for each Incremental backup, except for the last time. Otherwise, uncommitted transactions will be rolled back.
After the above steps are completed, execute again
Innobackupex -- apply-log the remaining steps of the BASE-DIR are restored in full backup mode.

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