Xtrabackup backup of large databases (full backup and Incremental Backup)

Xtrabackup backup of large databases (full backup and Incremental Backup)

Xtrabackup

Xtrabackup is a free Database Hot Backup Software open source by percona. It can back up InnoDB databases and XtraDB storage engine databases in non-blocking ways (Table locks are also required for MyISAM backup ); mysqldump is a logical backup. The biggest drawback is that the backup and recovery speed is slow. If the database is larger than 50 GB, mysqldump is not suitable for backup.

Advantages of Xtrabackup

1) fast backup and reliable physical backup
2) The backup process does not interrupt ongoing transactions (no table lock required)
3) Saving disk space and traffic based on compression and other functions
4) automatic backup Verification
5) Fast Restoration
6) data can be circulated and transmitted to another machine.

Xtrabackup Principle

There are four executable files after Xtrabackup is installed. Two of the most important backup tools are innobackupex and xtrabackup.
1) xtrabackup is used to back up InnoDB tables. It cannot back up non-Innodb tables and has no interaction with the mysql server;

2) innobackupex is a script used to back up non-InnoDB tables. It also calls the xtrabackup command to back up InnoDB tables and interacts with mysql server, such as locking tables and obtaining location points. Simply put, a layer of encapsulation is implemented based on xtrabackup;
3) xbcrypt encryption and decryption Backup Tool
4) xbstream transfer tool, similar to tar

1) A redo log file is maintained in the InnoDB Engine table, which is also called a transaction log file. Transaction logs store the modification of data records in each InnoDB table.
2) When xtrabackup is started, it will remember the log sequence number (LSN), that is, the location of the current redo record, and copy all data files.
3) The replication process takes some time. Therefore, if the data file LSN is changed during this period, it runs a background process to monitor transaction logs, the changes to each data file in the transaction log are kept in mind.

Install Xtrabackup

Download Xtrabackup from official website
1 # https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.5/binary/RedHat/6/x86_64/percona-xtrabackup-2.3.5-1.el6.x86_64.rpm

Search and download several dependent rpm packages
# Http://rpmfind.net/linux/RPM/
Perl-DBI-1.609-4.el6.x86_64.rpm
Perl-DBD-MySQL-4.013-3.el6.x86_64.rpm
Libev-4.04-2.el6.x86_64.rpm

Check the rpm package installation status after installation.

[Root @ db02 tools] # rpm-ivh percona-xtrabackup-2.3.5-1.el6.x86_64.rpm
Warning: percona-xtrabackup-2.3.5-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing... ######################################## ### [100%]
1: percona-xtrabackup ##################################### ###### [100%]
[Root @ db02 tools] # rpm-qa | grep xtrabackup
Percona-xtrabackup-2.3.5-1.el6.x86_64
[Root @ db02 tools] # rpm-ql percona-xtrabackup-2.3.5-1.el6.x86_64.
/Usr/bin/innobackupex # innobackupex script Tool
/Usr/bin/xbcloud
/Usr/bin/xbcloud_osenv
/Usr/bin/xbcrypt
/Usr/bin/xbstream
/Usr/bin/xtrabackup # primary backup tool
/Usr/share/doc/percona-xtrabackup-2.3.5
/Usr/share/doc/percona-xtrabackup-2.3.5/COPYING
/Usr/share/man/man1/innobackupex.1.gz
/Usr/share/man/man1/xbcrypt.1.gz
/Usr/share/man/man1/xbstream.1.gz
/Usr/share/man/man1/xtrabackup.1.gz

Make a full backup of the database first
[Root @ db02 data] # innobackupex -- defaults-file =/data/3306/my. cnf -- user = root -- password = li123456/backup/full/
...
60710 08:24:47 [00] Writing backup-my.cnf
160710 08:24:47 [00]... done
160710 08:24:47 [00] Writing xtrabackup_info
160710 08:24:47 [00]... done
Xtrabackup: Transaction log of lsn (1672995) to (1672995) was copied.
160710 08:24:47 completed OK!

Now you can see that/backup/full has the data file backed up.
[Root @ db02 full] # cd/backup/full/2016-07-10_08-24-43/
[Root @ db02 2016-07-10_08-24-43] # ll
Total usage 131108
-Rw-r ----- 1 root 387 July 10 08:24 backup-my.cnf
-Rw-r ----- 1 root 134217728 July 10 08:24 ibdata1
Drwx ------ 2 root 4096 July 10 08:24 lichengbing
Drwx ------ 2 root 4096 July 10 08:24 lilongzi
Drwx ------ 2 root 4096 July 10 08:24 mysql
Drwx ------ 2 root 4096 July 10 08:24 performance_schema
-Rw-r ----- 1 root 21 08:24 xtrabackup_binlog_info
-Rw-r ----- 1 root 113 July 10 08:24 xtrabackup_checkpoints # record the LSN File
-Rw-r ----- 1 root 503 July 10 08:24 xtrabackup_info
-Rw-r ----- 1 root 2560 July 10 08:24 xtrabackup_logfile
[Root @ db02 2016-07-10_08-24-43] # cat xtrabackup_checkpoints
Backup_type = full-backuped # The Backup Type is full backup.
From_lsn = 0 # Start LSN (because it is full backup, the start LSN is 0)
To_lsn = 1672995 # End LSN
Last_lsns = 1672995
Compact = 0
Recover_binlog_info = 0

Now that the full backup is complete, we can simulate data destruction and then restore the data.
Stop the database first
[Root @ db02 3306] # mysqladmin-uroot-pli123456-S/data/3306/mysql. sock shutdown
[Root @ db02 3306] # mv/data/3306/data/3306/data_bak/
[Root @ db02 3306] # mkdir-p/data/3306/data/# A new empty data folder must be created for restoration. Otherwise, an error will be returned.
Original data directory/data/3306/data is not empty!

Now we are enabling the database and find that the database file is damaged and cannot be started.
[Root @ db02 3306] # mysqld_safe -- defaults-file =/data/3306/my. cnf &
160710 10:05:14 mysqld_safe Logging to '/data/3306/mysql_oldboy3306.err '.
160710 10:05:14 mysqld_safe Starting mysqld daemon with databases from/data/3306/data
160710 10:05:15 mysqld_safe mysqld from pid file/data/3306/mysqld. pid ended
# Pid process file cannot be started
[1] + Done mysqld_safe -- defaults-file =/data/3306/my. cnf
[Root @ db02 3306] # tail mysql_oldboy3306.err
160710 10:05:15 InnoDB: 5.5.32 started; log sequence number 1595668
160710 10:05:15 [Note] Recovering after a crash using/data/3306/mysql-bin
160710 10:05:15 [Note] Starting crash recovery...
160710 10:05:15 [Note] Crash recovery finished.
160710 10:05:15 [Note] Server hostname (bind-address): '0. 0.0.0 '; port: 3306
160710 10:05:15 [Note]-'0. 0.0.0 'resolves to '0. 0.0.0 ';
160710 10:05:15 [Note] Server socket created on IP: '0. 0.0.0 '.
160710 10:05:15 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql. host' doesn' t exist # prompt that the relevant database Table is not found
160710 10:05:15 mysqld_safe mysqld from pid file/data/3306/mysqld. pid ended

Prepare full recovery
[Root @ db02 3306] # innobackupex -- defaults-file =/data/3306/my. cnf -- user = root -- password = li123456 -- apply-log/backup/full/2016-07-10_08-24-43/
...
InnoDB: Waiting for purge to start
InnoDB: 5.6.24 started; log sequence number 1673228
Xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1673238
160710 08:42:01 completed OK!

# The apply-log parameter indicates that the redo log status of the database is restored before the database data is restored.

The next step is to restore the data file
[Root @ db02 3306] # innobackupex -- defaults-file =/data/3306/my. cnf -- copy-back/backup/full/2016-07-10_08-24-43/
...
160710 08:43:41 [01]... done
160710 08:43:41 [01] Copying./mysql/user. frm to/data/3306/data/mysql/user. frm
160710 08:43:41 [01]... done
160710 08:43:41 [01] Copying./mysql/tables_priv.MYI to/data/3306/data/mysql/tables_priv.MYI
160710 08:43:41 [01]... done
160710 08:43:41 [01] Copying./mysql/proxies_priv.MYI to/data/3306/data/mysql/proxies_priv.MYI
160710 08:43:41 [01]... done
160710 08:43:41 completed OK!

Then, the data file group is changed and the database is started successfully.
[Root @ db02 3306] # chown-R mysql. mysql data
[Root @ db02 data] # mysqld_safe -- defaults-file =/data/3306/my. cnf &
[1] 90409
[Root @ db02 data] # mysql-uroot-pli123456-S/data/3306/mysql. sock
Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Lichengbing | # data table recovered successfully
| Lilongzi |
| Mysql |
| Performance_schema |
+ -------------------- +
5 rows in set (0.00 sec)

Simulate Incremental backup again
Insert several new rows of data
[Root @ db02 opt] # mysql-uroot-pli123456-S/data/3306/mysql. sock lichengbing </opt/test2. SQL
Mysql> select * from lichengbing. test;
+ ---- + ------- +
| Id | name |
+ ---- + ------- +
| 1 | one |
| 2 | two |
| 3 | three |
| 4 | four | # at this time, 4, 5, and 6 are equivalent to incremental database files.
| 5 | five |
| 6 | six |
+ ---- + ------- +
6 rows in set (0.00 sec)

Incremental Backup
[Root @ db02 opt] # innobackupex -- defaults-file =/data/3306/my. cnf -- user = root -- password = li123456 -- incremental/backup/add/-- incremental-basedir =/backup/full/2016-07-10_08-24-43/
...
160710 08:55:15 [00] Writing xtrabackup_info
160710 08:55:15 [00]... done
Xtrabackup: Transaction log of lsn (1673693) to (1673693) was copied.
160710 08:55:15 completed OK!
[Root @ db02 add] # cd/backup/add/2016-07-10_08-55-12/
[Root @ db02 2016-07-10_08-55-12] # cat xtrabackup_checkpoints
Backup_type = incremental
From_lsn = 1672995 # The starting LSN of the Incremental backup is obtained by reading the full-Backup xtrabackup_checkpoints file.
To_lsn = 1673693 # incremental end LSN
Last_lsns = 1673693
Compact = 0
Recover_binlog_info = 0

Incremental recovery
Incremental recovery and full recovery are different. Note the following:
1) on each backup (including full backup and Incremental Backup), the committed transactions must be "replayed". After the replay, all the backup data will be merged to the full backup;

2) Roll Back uncommitted transactions based on all backups"

Close Database
[Root @ db02 ~] # Mysqladmin-uroot-pli123456-S/data/3306/mysql. sock shutdown

Perform full backup rollback

[Root @ db02 ~] # Innobackupex -- defaults-file =/data/3306/my. cnf -- user = root -- pli123456 -- apply-log -- redo-only/backup/full/2016-07-10_08-24-43/

# -- Redo-only when incremental recovery is performed, the data files of full and Incremental Backup must be rolled before the recovery of committed transactions in the redo log file! This parameter combines full backup and Incremental backup data files, but does not include the data files of the last Incremental backup.

Merge full backup and Incremental Backup
[Root @ db02 ~] # Innobackupex -- defaults-files =/data/3306/my. cnf -- user = root -- pli123456 -- apply-log/backup/full/2016-07-10_08-24-43/-- incremental-dir =/backup/add/2016-07-10_08-55-12/
#/Backup/full/2016-07-10_08-24-43/full backup directory
# -- Incremental-dir =/backup/add/2016-07-10_08-55-12/incremental directory
# -- Redo-only: only one Incremental backup is required. Therefore, this parameter is not required. If there are N incremental backups, the Incremental backup is restored in sequence, the -- redo-only parameter must be added in addition to the last one.

Destroys the database and restores full backup and Incremental backup.
[Root @ db02 3306] # rm-fr data_bak
[Root @ db02 3306] # mv data data_bak
[Root @ db02 3306] # innobackupex -- defaults-file =/data/3306/my. cnf -- copy-back/backup/full/2016-07-10_08-24-43/
...
160710 09:54:54 [01]... done
160710 09:54:54 [01] Copying./mysql/user. frm to/data/3306/data/mysql/user. frm
160710 09:54:54 [01]... done
160710 09:54:54 [01] Copying./mysql/tables_priv.MYI to/data/3306/data/mysql/tables_priv.MYI
160710 09:54:54 [01]... done
160710 09:54:55 [01] Copying./mysql/proxies_priv.MYI to/data/3306/data/mysql/proxies_priv.MYI
160710 09:54:55 [01]... done
160710 09:54:55 completed OK!
[Root @ db02 3306] # chown-R mysql. mysql data

Recovery successful. log in to view
1234567891011121314 [root @ db02 data] # mysqld_safe -- defaults-file =/data/3306/my. cnf &
[Root @ db02 data] # mysql-uroot-pli123456-S/data/3306/mysql. sock
Mysql> select * from lichengbing. test;
+ ---- + ------- +
| Id | name |
+ ---- + ------- +
| 1 | one |
| 2 | two |
| 3 | three |
| 4 | four |
| 5 | five |
| 6 | six |
+ ---- + ------- +
6 rows in set (0.00 sec)

Other backup examples
Database Backup
[Root @ db02 each] # innobackupex -- defaults-file =/data/3306/my. cnf -- user = root -- password = li123456 -- databases = "lichengbing"/backup/each/

Table backup
[Root @ db02 each] # innobackupex -- defaults-file =/data/3306/my. cnf -- user = root -- password = li123456 -- databases = "lichengbing test"/backup/each/

Back up data in compressed format
[Root @ db02 full] # innobackupex -- defaults-file =/data/3306/my. cnf -- user = root -- password = li123456 -- stream = tar/backup/full/| gzip>/backup/full/back _ 'date before running f'.tar.gz

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

This article permanently updates the link address: