Production environment MySQL Quick Backup tool xtrabackup use case

Mysql Backup There are many ways, the most commonly used should be mysqldump , the database is exported to a SQL file, restore the source Db.sql on the cheerful execution. Small partner Happy cloth on a shell script, with Cron, happy to look at the daily backup file generation, feel secure. What if a mysql exceeds 10G ? Please believe me, the export time takes several hours, then restore it, time will be longer. I used to restore a 6G database with SQL for more than 3 hours.

Mysqldump This logical backup of the shortcomings of not much to say, said Xtrabackup, but also PERCONA, the name of the red, trustworthy. Official Note:

1. Fast and reliable

2. No interruption of transaction processing during backup

3. Save disk space and network bandwidth

4. Automatic Backup verification

5.High-availability lines due to fast restores

above said, anyway I was letter, or that 6G more library, full spare 5 minutes, restore also only took 5 minutes. Guys, remember, if it's really up to you to restore data with a backup, you can restore it for a few hours and the boss will have an idea! hehe.

Operating System: CentOS Release 6.5

mysql:5.6.21

First, installation

Yum Install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

Yum Install Percona-xtrabackup

Second, start to use

Xtrabackup contains several tools for Innobackupex,xtrabackup,xbcrypt,xbstream. Innobackupex supports MYISAM,INNODB. All, I choose to use Innobackupex.

1. Create a backup user, you know, minimum permissions

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 to complete database preparation

Innobackupex--user=bkuser--password=bk2015/mnt/backups

--user provide a user name to connect to the database

--password the password for the connection database

/mnt/backups storage path for backup files

After the command execution is completed, the 2015-02-03_15-11-51 directory is generated under the/mnt/backups directory, which is a full backup of our current database. The directory is Innobackupex automatically generated directory, there is a parameter can control the option--no-timestamp, if added to this parameter, the directory will not be automatically generated, the backup is created under the provided path. This parameter is more convenient to use in scripts.

3. using a full backup for database restore

Restore preparation work

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

--apply-log There may be uncommitted transactions in the created backup that need to be rolled back or replayed from the log

Restores

stop MySQL Services,

Service Mysqld Stop

back up the original MySQL Data Catalog

mv/mnt/data/mysql/mnt/data/mysql20150203

Perform a restore operation

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

Change directory permissions, start service

Chown–r Mysql.mysql/mnt/data/mysqlservice mysqld Start

4.innobackupex Incremental Backup

Incremental backups need to be backed up once, and on a full-fledged basis, the next backup backs up only the current time to the data that changed on the last complete basis. The biggest benefit is saving disk space. Of course, the downside is that the restoration process will become complex.

4.1 First full Standby

Innobackupex--user=bkuser--password=bk2015/mnt/backups

there is a xtrabackup-checkpoints in the backup directory 2015-02-03_15-11-51 , which reads as follows :

Backup_type = full-backuped

FROM_LSN = 0

TO_LSN = 1291135

Create an incremental backup

Innobackupex--incremental/mnt/backups--incremental-basedir=/mnt/backups/2015-02-03_15-11-51

--incremental Using this parameter to create an incremental backup

--incremental-basedir Specifies the directory that was last backed up, such as the first add-on after a full backup, the directory is a complete directory, and if this is a second add-on, the directory is the first to be added

Check the contents of the Xtraback_checkpoint file in the file of the Add directory , and you will find thatthe LSN has changed

Backup_type = Incremental

FROM_LSN = 1291135

TO_LSN = 1352113

5. restoring an incremental backup

5.1 Restore Preparation work

process :--apply-log application to full directory, first add, second add ... nth Add-on , when all applications are complete, a new full standby + all add -on.

Innobackupex--apply-log--redo-only Base-dir

Innobackupex--apply-log--redo-only Base-dir--incremental-dir=incremental-dir-1

Innobackupex--apply-log Base-dir--incremental-dir=incremental-dir-2

Base-dir Fully prepared catalogue
Incremental-dir-1 first-time replenishment
Incremental-dir-2 Second Replenishment

--redo-only Important , the parameter needs to be specified for each increment, except for the last one. Otherwise, uncommitted transactions will execute rollback.

Once the above steps have been completed, perform the

Innobackupex--apply-log Base-dir

The rest of the steps can be restored in a fully prepared manner

This article is from the "Candle Shadow Red" blog, be sure to keep this source http://gccmx.blog.51cto.com/479381/1611158

Production environment MySQL Quick Backup tool xtrabackup use case