PerconaXtrabackup quick MySQL backup

Old rule: In the opening remarks, I started to use mysqldump to back up 100G + databases, coupled with the busy server, and the backup speed is like a snail bait, so I am looking for a more efficient backup method. The Internet says that xtrabackup is more suitable for backing up large databases, and the backup efficiency is also high. Just try it once. The result is obviously faster than mysqldump backup and recovery.

Old rule: In the opening remarks, I started to use mysqldump to back up 100G + databases, coupled with the busy server, and the backup speed is like a snail bait, so I am looking for a more efficient backup method. The Internet says that xtrabackup is more suitable for backing up large databases, and the backup efficiency is also high. Just try it once. The result is obviously faster than mysqldump backup and recovery.

Old rule: In the opening remarks, I started to use mysqldump to back up 100G + databases, coupled with the busy server, and the backup speed is like a snail bait, so I am looking for a more efficient backup method. The Internet says that xtrabackup is more suitable for backing up large databases, and the backup efficiency is also high. Just try it once. The result is obviously faster than mysqldump backup and recovery, it will be used later. This document was written in conjunction with the production environment. If you are new to me, you can refer to it. If you are not familiar with it, just get started!

1. Preparations before the initial backup

1.1 understand backup methods

Hot Backup: read/write is not affected (mysqldump --> innodb)

Warm backup: only read operations can be performed (mysqldump --> myisam)

Cold backup: offline backup, read/write unavailable

Logical backup: export data to a text file (mysqldump)

Physical backup: Copy data files (xtrabackup, mysqlhotcopy)

Full backup: Back up all data

Incremental Backup: only the data changed since the last full backup or Incremental backup is backed up.

Differential backup: only the data changed since the last full backup is backed up.

1.2 create a backup user

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

1.3 Installation 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 Innodb Engine of MySQL5.1, and 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) can be installed as needed)

Ubuntu:

# Sudo apt-get install xtrabackup #14.04 by default this package is available, versions earlier than 14.04 if there is no download corresponding version of this address: https://www.percona.com/downloads/XtraBackup/

1.4 understand common parameters

-- User = # specifies the database backup user

-- Password = # specify the password of the Database Backup user

-- Port = # specify the database port

-- Host = # specify the backup host

-- Socket = # specify the socket file path

-- Databases = # specifies the database to be backed up. Multiple spaces are separated, for example, -- databases = "dbname1 dbname2". No database is backed up.

-- Defaults-file = # specify the my. cnf configuration file

-- Apply-log # log rollback

-- Incremental = # incremental backup, followed by the incremental backup path

-- Incremental-basedir = # incremental backup, which refers to the path of the last incremental Backup

-- Redo-only # merge full backup and Incremental backup data files

-- Copy-back # copy the backup data to the database. The database directory must be empty.

-- No-timestamp # The generated backup file is not named as the directory name with the timestamp

-- Stream = # specifies the stream format for backup, -- stream = tar, archive the backup file

-- Remote-host = user @ ip DST_DIR # Back up data to the remote host

2. Complete 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 mysql parameters used by innobackup

Xtrabackup_binary: executable files used in backup

Xtrabackup_checkpoints: record the Backup Type, start and end log serial number

Xtrabackup_logfile: A log copy thread is enabled in the backup to monitor innodb log files (ib_logfile). If this thread is modified, it will be copied to this file.

3. Complete backup + Incremental backup and recovery

3.1 full backup

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

# The backup location is/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 # specify the last full backup directory

3.3 Incremental Backup 2

# Innobackupex -- user = bak -- password = 'bak2015 '-- incremental/data1/mysql_backup -- incremental-basedir =/mysql_backup/2015-02-08_12-16-06 # specify the last incremental Backup Directory

3.4 check the xtrabackup_checkpoints file. You can see the Incremental Backup Based on the log number at a glance.

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151111/12135Ja8-0.jpg "title =" qq 50208160818.png "alt =" wKioL1TXGvCSc7KkAALo5ttBsM8636.jpg "/>

3.5 Backup Recovery

3.5.1 Backup Recovery ideas

Incremental Backup 1. Incremental Backup 2... merge to full backup, and add a new full backup together. Copy the new full backup to the empty database directory (rm/var/lib/mysql/*-rf)

3.5.2 complete backup preparation

# Xtrabackup writes a transaction that can be uncommitted or has been committed but not synchronized to the data file during the backup process to the xtrabackup_logfile file. Therefore, you must first roll back the log file, synchronize unfinished transactions to backup files to ensure consistency of data files.

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

3.5.3 merge 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 the 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 the full backup (at this time, 2015-02-08_11-56-48 the full backup already contains all incremental backups. You can check the checkpoints)

# Innobackupex -- defaults-file =/etc/mysql/my. cnf -- copy-back/mysql_backup/2015-02-08_11-56-48/

3.5.6 modify and restore Data File Permissions

# Chown-R mysql. mysql/var/lib/mysql

3.5.7 start MySQL and check database recovery

#/Etc/init. d/mysqld start

4. Archive and compress backup files

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 root@192.168.18.251 "cat->/mysql_backup/'date too far f'.tar"

Decompress the package: tar-ixvf 'date has been decompressed f'.tar

4.2 archive backup

# Innobackupex -- databases = test -- user = bak -- password = 'bak2015 '-- stream = tar/mysql_backup>/mysql_backup/'date has been created f'.tar

Decompress the package: tar-ixvf 'date has been decompressed f'.tar

4.3 compressed and archive backup

# Innobackupex -- databases = test -- user = bak -- password = 'bak2015 '-- stream = tar/mysql_backup | gzip>/mysql_backup/'date ready before f'.tar.gz

Decompress: tar-izxvf 'date has been decompressed f'.tar.gz