Percona xtrabackup backup MySQL instance (GO)

The usual, prologue, just started with mysqldump, backup 100g+ database, plus server busy, backup speed like snail, so look for more efficient backup method. Online are said to use Xtrabackup more suitable for backup large database, and backup efficiency is high, try to use once, the result is obvious feeling than mysqldump backup and recovery speed is much faster, then use it. Prostration Day at home also nothing, combined with production environment, wrote this document, just contact friends, you can refer to, not much to say, into the topic!

1. Pre-initial Backup preparation work

1.1 Understanding Backup methods

Hot backup: Read/write not affected (MYSQLDUMP-->INNODB)

Warm backup: Can only perform read operations (MYSQLDUMP-->MYISAM)

Cold backup: Offline backup, read/write not available

Logical backup: Export data in a text file (mysqldump)

Physical Backup: Copy data Files (xtrabackup, mysqlhotcopy)

Full backup: Back up all data

Incremental backup: Backs up only data that has changed since the last full or incremental backup

Differential backup: Backs up only data that has changed since the last full backup

1.2 Creating a backup User

12	mysql> grant reload,lock tables,replication client on *.* to ‘bak‘@‘localhost‘identified by ‘bak2015‘;     mysql> flush privileges;

1.3 Mounting method

CentOS:

# RPM-IVH http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

# yum Install Percona-xtrabackup #xtrabackup2.2 does not support the MySQL5.1 InnoDB engine, as required to install version 2.0 (https://www.percona.com/downloads/ XTRABACKUP/XTRABACKUP-2.0.8/RPM/RHEL6/X86_64/PERCONA-XTRABACKUP-20-2.0.8-587.RHEL6.X86_64.RPM)

Ubuntu:

# sudo apt-get install xtrabackup #14.04 The default has this package, 14.04 before the version if no longer download the corresponding version of this address: https://www.percona.com/downloads/XtraBackup/

1.4 Understanding Common Parameters

--user= #指定数据库备份用户

--password= #指定数据库备份用户密码

--port= #指定数据库端口

--host= #指定备份主机

--socket= #指定socket文件路径

--databases= #备份指定数据库, separated by multiple spaces, such as--databases= "Dbname1 dbname2", without backing up all libraries

--defaults-file= #指定my. CNF configuration file

--apply-log #日志回滚

--incremental= #增量备份, followed by incremental backup path

--incremental-basedir= #增量备份, referring to the last incremental backup path

--redo-only #合并全备和增量备份数据文件

--copy-back #将备份数据复制到数据库, the database directory should be empty

--no-timestamp #生成备份文件不以时间戳为目录名

--stream= #指定流的格式做备份,--Stream=tar, archive backup files

[Email protected] Dst_dir #备份到远程主机

2. Full Backup and Recovery

2.1 Full Backup

# Innobackupex--user=bak--password= ' bak2015 '/mysql_backup

2.2 Backup Recovery

# Innobackupex--DEFAULTS-FILE=/ETC/MYSQL/MY.CNF--copy-back/home/loongtao/mysql_backup/2015-02-08_11-56-48/

2.3 Backup File Description

# ls 2015-02-08_11-56-48

BACKUP-MY.CNF: Record innobackup use to MySQL parameter

Xtrabackup_binary: The executable file used in the backup

Xtrabackup_checkpoints: Record the type of backup, start and end log sequence numbers

Xtrabackup_logfile: A log copy thread is opened in the backup to monitor the InnoDB log file (Ib_logfile) and will be copied to this file if modified

3. Full backup + incremental backup and recovery

3.1 Full backup

# Innobackupex--user=bak--password= ' bak2015 '/mysql_backup

#备份后位置是:/mysql_backup/2015-02-08_11-56-48

3.2 Incremental Backup 1

# Innobackupex--user=bak--password= ' bak2015 '--incremental/data1/mysql_backup--incremental-basedir=/mysql_backup /2015-02-08_11-56-48 #指定上次完整备份目录

3.3 Incremental Backup 2

# Innobackupex--user=bak--password= ' bak2015 '--incremental/data1/mysql_backup--incremental-basedir=/mysql_backup /2015-02-08_12-16-06 #指定上次增量备份目录

3.4 View xtrabackup_checkpoints files at a glance, you can see incremental backups based on log sequence numbers

3.5 Backup Recovery

3.5.1 Backup Recovery Ideas

Incremental backup 1, incremental backup 2 ... Merge to full backup, add together a new full backup, a new full backup in the form of a copy to the database empty directory (rm/var/lib/mysql/*-RF)

3.5.2 Prepare a full backup

#xtrabackup把备份过程中可能有尚未提交的事务或已经提交但未同步数据文件的事务, write to the Xtrabackup_logfile file, so you have to roll back through this log file, to synchronize the unfinished transaction to the backup file, to ensure that the data file is consistent.

# innobackup--apply-log--redo-only 2015-02-08_11-56-48

3.5.3 merging the first incremental backup

# Innobackupex--apply-log--redo-only/mysql_backup/2015-02-08_11-56-48/--incremental-dir=mysql_backup/2015-02-08 _12-16-06

3.5.4 Merge a second incremental backup

# Innobackupex--apply-log--redo-only/mysql_backup/2015-02-08_11-56-48/--incremental-dir=mysql_backup/2015-02-08 _16-06-53

3.5.5 Restore full backup (at this point the 2015-02-08_11-56-48 full backup already contains all incremental backups and can be verified by looking at the checkpoints)

# Innobackupex--DEFAULTS-FILE=/ETC/MYSQL/MY.CNF--copy-back/mysql_backup/2015-02-08_11-56-48/

3.5.6 Modifying the Restore data file permissions

# Chown-r Mysql.mysql/var/lib/mysql

3.5.7 start MySQL to view database recovery

#/etc/init.d/mysqld Start

4. backup File archive compression

4.1 Archive and send to backup server

# Innobackupex--databases=test--user=bak--password= ' bak2015 '--stream=tar/mysql_backup 2>/mysql_backup/bak.log |ssh [email protected] "cat->/mysql_backup/' Date +%f '. Tar"

Decompression: tar-ixvf ' date +%f '. Tar

4.2 Archive Backup

# Innobackupex--databases=test--user=bak--password= ' bak2015 '--stream=tar/mysql_backup >/mysql_backup/' Date +%F '. Tar

Decompression: tar-ixvf ' date +%f '. Tar

4.3 Compressed archive backup

# Innobackupex--databases=test--user=bak--password= ' bak2015 '--stream=tar/mysql_backup |gzip >/mysql_backup/' Date +%f '. tar.gz

Decompression: tar-izxvf ' Date +%f '. tar.gz

Percona xtrabackup backup MySQL instance (GO)