Shulou(Shulou.com)06/01 Report--

Http://hongge.blog.51cto.com/

Using xtrabackup for MySQL database backup

As described earlier, mysqldump backup uses logical backup, and its biggest disadvantage is that the backup and recovery speed is slow. For a database less than 50G, this speed is acceptable, but if the database is very large, it is not suitable to use mysqldump backup.

At this time, you need an easy-to-use and efficient tool, and xtrabackup is one of them, known as the free version of InnoDB HotBackup.

The Xtrabackup implementation is a physical backup and a physical hot backup

At present, there are two mainstream tools to implement physical hot backup: ibbackup and xtrabackup;ibbackup are commercial software that require authorization and are very expensive. Xtrabackup is more powerful than ibbackup, but it is open source. So we are here to introduce the use of xtrabackup.

Xtrabackup provides two command-line tools:

Xtrabackup: dedicated to backing up data from InnoDB and XtraDB engines

Innobackupex: this is a perl script that invokes the xtrabackup command during execution so that you can back up both InnoDB and MyISAM engine objects.

Xtrabackup is a mysql database backup tool provided by percona with the following features:

(1) the backup process is fast and reliable

(2) the backup process will not interrupt the transaction in progress.

(3) it can save disk space and traffic based on compression and other functions.

(4) automatic backup verification

(5) the reduction speed is fast.

Official link address: http://www.percona.com/software/percona-xtrabackup; you can download the source code compilation and installation, or you can download the appropriate RPM package or install it using yum or download the binary source package.

Install xtrabackup

1) download xtrabackup

Wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/tarball/percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz

2) decompression

# tar zxf percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz

3) enter the decompression directory

# cd percona-xtrabackup-2.4.4-Linux-x86_64/

4) copy all programs under bin to / usr/bin

[root@localhost percona-xtrabackup-2.4.4-Linux-x86_64] # cp bin/* / usr/bin/

There are two main tools in Xtrabackup:

Xtrabackup: a tool for hot backup of data in innodb and xtradb tables. It supports online hot backup. Innodb tables can be backed up without locking, but this tool cannot operate Myisam engine tables.

Innobackupex: a perl script that encapsulates xtrabackup and can handle both Innodb and Myisam, but requires a read lock when dealing with Myisam.

Because read locks are required to operate on Myisam, which blocks writes to online services, and Innodb does not have such restrictions, the greater the proportion of Innodb table types in the database, the more advantageous it is.

4) install the relevant plug-ins

# yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-TermReadKey.x86_64 perl-Digest-MD5-y

5) download percona-toolkit and install

# wget https://www.percona.com/downloads/percona-toolkit/2.2.19/RPM/percona-toolkit-2.2.19-1.noarch.rpm

# rpm-vih percona-toolkit-2.2.19-1.noarch.rpm

Now you can start the backup.

Option 1: xtrabackup full backup + binlog incremental backup

1. Backup

Create a backup directory

# mkdir-p / opt/mysqlbackup/ {full,inc}

Full: directory for full storage; inc: directory for incremental backups

1) full backup

Basic syntax: # innobackupex-- user=DBUSER-- password=DBUSERPASS / path/to/BACKUP-DIR/

Execute the following command to make a full backup:

# innobackupex-user=root-password=123456 / opt/mysqlbackup/full

Note:-- defaults-file=/etc/my.cnf specifies the configuration file my.cfg for mysql, which must be the first parameter if specified.

/ path/to/BACKUP-DIR/ specifies the destination directory where the backup is stored, and the backup process creates a directory named after the backup time to store the backup files.

The following prompt appears. Indicates success

Backed up files:

At the same time of the backup, the backup data creates a directory under the backup directory with the name of the current date and time to store the backup files:

The description of each document:

(1) xtrabackup_checkpoints-backup type (such as full or incremental), backup status (such as prepared status) and LSN (log serial number) scope information

Each InnoDB page (usually 16k in size) contains a log sequence number, LSN. LSN is the system version number of the entire database system, and the LSN associated with each page can indicate how the page has changed recently.

(2) xtrabackup_binlog_info-the location of the binary log files currently in use by the mysql server and the binary log events up to the moment of backup.

(3) xtrabackup_binlog_pos_innodb-the current position of the binary log file and the binary log file used for the InnoDB or XtraDB table.

(4) xtrabackup_binary-the executable file of xtrabackup used in backup

(5) backup-my.cnf-configuration option information used for backup commands

When using innobackupex for backup, you can also use the-- no-timestamp option to prevent the command from automatically creating a directory named by time; in this way, the innobackupex command will create a BACKUP-DIR directory to store backup data

Note: the relevant options describe:

Where-- user specifies the user name to connect to the database,-- password specifies the password to connect to the database,-- defaults-file specifies the configuration file for the database, from which innobackupex needs to obtain information such as datadir. -- database specifies the database to be backed up. The database specified here is only valid for MyISAM tables and is complete for InnoDB data (InnoDB data in all databases are backed up, not only the specified database is backed up, but also during recovery); / opt/mysqlbackup/full is the location where the backup files are stored.

Note: the user who backs up the database needs to have the appropriate permissions, and if you want to use a user with minimum privileges for backup, you can create such a user based on the following command:

Mysql > create user' bkpuser'@'localhost' identified by '123456'

Mysql > revoke all privileges,grant option from 'bkpuser'@'localhost'

Mysql > grant reload,lock tables,replication client, process on *. * to 'bkpuser'@'localhost'

Mysql > flush privileges

At this point, it is completely successful, then insert a few pieces of data into a library in mysql, and then make an incremental backup.

Perform a binary log incremental backup of the post-database changes of the full backup:

View the binlog log location (position) when viewing a full backup:

Simulate database modification:

2) incremental backup binaries:

# mysqlbinlog-- start-position=2378 / usr/local/mysql/data/mysql-bin.000023 > / opt/mysqlbackup/inc/ `date +% F`.sql

2. Restore the database:

Simulate database corruption:

Here I directly use delete data catalog files to simulate corruption.

# rm-fr / usr/local/mysql/data/*

Restore a full backup:

(1) prepare (prepare) a full backup

In general, after the backup is complete, the data cannot be used for restore operations because the backed up data may contain transactions that have not yet been committed or transactions that have been committed but have not been synchronized to the data file. Therefore, the data file is still in an inconsistent state at this time. The main function of "preparation" is to make the data file consistent by rolling back the uncommitted transaction and synchronizing the committed transaction to the data file.

At the end of the prepare process, the InnoDB table data has been rolled forward to the end of the entire backup, rather than to the point where the xtrabackup just started.

The-- apply-log option of the innobakupex command can be used to achieve the above functionality. Such as the following command:

-- apply-log indicates that the log is applied to the data file, and the data in the backup file is restored to the database after completion:

# innobackupex-- apply-log / opt/mysqlbackup/full/2016-09-12 11-29-55 /

Note: / opt/mysqlbackup/full/2016-09-12 11-29-55 / the name of the directory where the backup file is located

If executed correctly, the last few lines of information output are usually as follows:

In the process of implementing preparation, innobackupex can also use the-- use-memory option to specify the amount of memory it can use, which is usually 100m by default. If enough memory is available, you can allocate more memory to the prepare process to improve its completion speed.

The-- copy-back option of the innobackupex command is used to perform the restore operation, which performs the restore process by copying all data-related files to the mysql server DATADIR directory. Innobackupex uses backup-my.cnf to get information about the DATADIR directory.

(2) restore database syntax:

# innobackupex-- copy-back / opt/mysqlbackup/full/2016-09-12 11-29-55 /

The-- copy-back here indicates that data recovery is performed. After the data recovery is complete, the permissions of the relevant files need to be modified before the mysql database can start normally.

If executed correctly, the last lines of the output information are usually as follows:

Please make sure that "completed OK!" appears on the top line of the above information.

Modify the restored data directory permissions:

When the data is restored to the DATADIR directory, you also need to make sure that the owner and group of all data files are the correct users, such as mysql, otherwise, you need to modify the owner and group of the data file before starting mysqld. Such as:

# chown-R mysql:mysql / usr/local/mysql/data/

Restart MySQL:

# systemctl restart mysqld

Verify the restored data:

Mysql > select * from tb1

+-+ +

| | id | name |

+-+ +

| | 1 | tom1 |

| | 2 | tom2 |

+-+ +

(3) restore incremental backup:

In order to prevent a large number of binary logs from being generated during restore, you can temporarily close the binary logs during restore and then restore:

Mysql > set sql_log_bin=0

Mysql > source / opt/mysqlbackup/inc/2016-09-12.sql

Restart the binary log and verify the restore data:

Mysql > set sql_log_bin=1

Verify that the data is restored

Option 2. Xtrabackup full backup + xtrabacup incremental backup

When we did incremental backups earlier, we used the same old method: backing up binary logs. Actually, xtrabackup also supports incremental backup.

First introduce the backup principle of xtrabackup

A redo log file is maintained within InnoDB, which we can also call a transaction log file (transaction log, transaction log). The transaction log stores recorded changes to each InnoDB table data. When InnoDB starts, InnoDB examines the data file and transaction log and performs two steps: it applies the committed transaction log to the data file and rolls back the modified but uncommitted data.

Xtrabackup remembers log sequence number (LSN) at startup and copies all data files. The replication process takes some time, so if the data file changes during this period, it will put the database at a different point in time. At this point, xtrabackup runs a background process that monitors the transaction log and copies the latest changes from the transaction log. Xtrabackup must do this continuously because the transaction log rotates repeated writes and can be reused. So xtrabackup keeps recording changes to each data file in the transaction log since it starts. This is the backup process of xtrabackup.

So each InnoDB page will contain a LSN information, and whenever the relevant data changes, the LSN of the related page will automatically grow. This is the basis on which InnoDB tables can be backed up incrementally.

XtraBackup is based on the crash-recovery function of InnoDB. It will copy the data file of innodb, because the table is not locked, the copied data is inconsistent, and crash-recovery is used during recovery to make the data consistent.

When InnoDB starts, it checks data file and transaction log first, and does two steps:

1.It applies committed transaction logentries to the data files

2.it performs an undo operation on anytransactions that modified data but did not commit.

So during the prepare process, XtraBackup uses the copied transactions log to crash recovery the backed up innodb data file.

Test environment preparation

Create a test database and create a table to enter several rows of data

Mysql > create database test

Mysql > use test

Mysql > create table xx (id int,name varchar (20))

Mysql > insert into xx values (1)

Mysql > insert into xx values (2)

1. Xtrabacup for backup

Perform a full backup:

Backup command:

# xtrabackup-defaults-file=/etc/my.cnf-user=root-password= "123456"-port=3306-backup-target-dir=/opt/mysqlbackup/full/full_incre_$ (date +% Y%m%d_%H%M%S)

Some of the display information is shown in the following figure:

Among them,-- defaults-file specifies the configuration file of the database, which must be used as the first parameter if you use this parameter;-- user specifies the user name to connect to the database;-- password specifies the password to connect to the database;-- port specifies the port number to connect to the database;-- backup implements the backup to target-dir;-- the directory path where the backup files are stored. Innobackupex needs to obtain datadir and other information from it;-database specifies the database to be backed up. The database specified here is only valid for the table structure of MyISAM table and InnoDB table, and is complete for InnoDB data (the InnoDB data in all databases are backed up, not only the specified database, but also during recovery); / opt/mysqlbackup/full/ is the location where the backup files are stored.

View full backup files

[root@localhost ~] # ls / opt/mysqlbackup/full/-l

Drwxr-x---. 8 root root 4096 Sep 12 22:11 full_incre_20160912_221111

Xtrabackup for incremental backup

First record some data to achieve the first incremental data:

Mysql > use test

Mysql > insert into xx values (3recorder tom3')

Then perform incremental backup 1

Backup command:

# xtrabackup-defaults-file=/etc/my.cnf-user=root-password= "123456"-port=3306-backup-target-dir=/opt/mysqlbackup/inc/incre_$ (date +% Y%m%d_%H%M%S)-incremental-basedir=/opt/mysqlbackup/full/full_incre_20160912_221111/

Some of the display information is shown in the following figure:

Where-- incremental-basedir specifies the location of the last full backup or incremental backup files (that is, if it is the first incremental backup, it points to the directory where the full backup is located, and when you perform an incremental backup again after performing an incremental backup, its-- incremental-basedir should point to the directory where the last incremental backup was located).

View incremental backup files:

[root@localhost ~] # ls-l / opt/mysqlbackup/inc/

Drwxr-x---. 8 root root 4096 Sep 12 22:15 incre_20160912_221510

Note:

The incremental backup here is actually only for InnoDB, and for MyISAM, it's still a full backup.

Insert a few more rows of data into the table to continue the second incremental backup

Mysql > use test

Mysql > insert into xx values (4)

Mysql > commit

Perform a second incremental backup

Backup command:

# xtrabackup-defaults-file=/etc/my.cnf-user=root-password= "123456"-port=3306-backup-target-dir=/opt/mysqlbackup/inc/incre_$ (date +% Y%m%d_%H%M%S)-incremental-basedir=/opt/mysqlbackup/inc/incre_20160912_221510/

Some of the display information is shown in the following figure:

Note: the second incremental backup-incremental-basedir points to the location of the last incremental backup file.

View incremental backup files

[root@localhost ~] # ls-l / opt/mysqlbackup/inc/

Drwxr-x---. 8 root root 4096 Sep 12 22:15 incre_20160912_221510

Drwxr-x---. 8 root root 4096 Sep 12 22:19 incre_20160912_221916

2. Incremental recovery by xtrabacup

To verify the comparison, delete the data in the table before the two incremental backups

Mysql > use test

Mysql > delete from xx where id=3

Full backup recovery:

Before the recovery, if the full backup is on the remote host, first copy the full backup to the local host. If it is a tar package, you need to unpack it first. The unpacking command is: tar-izxf xxx.tar, where you must use the-I parameter (ignore the 0-byte block in the archive (which usually means the end of the file).

Start full backup restore

The command is as follows:

# xtrabackup-defaults-file=/etc/my.cnf-prepare-user=root-password= "123456"-apply-log-only-target-dir=/opt/mysqlbackup/full/full_incre_20160912_221111/

Some of the display information is shown in the following figure:

Restore to the moment of the first increment

The steps for incremental backup recovery are basically the same as those for full backup recovery, except that the process of applying logs is slightly different. When an incremental backup is restored, all incremental backups are applied to the data files of the full backup one by one, and then the data in the full backup is restored to the database.

Restore command:

# xtrabackup-defaults-file=/etc/my.cnf-prepare-user=root-password= "123456"-apply-log-only-target-dir=/opt/mysqlbackup/full/full_incre_20160912_221111/-incremental-dir=/opt/mysqlbackup/inc/incre_20160912_221510/

Some of the display information is shown in the following figure:

Restore to before the second incremental backup:

Restore command:

# xtrabackup-defaults-file=/etc/my.cnf-prepare-user=root-password= "123456"-apply-log-only-target-dir=/opt/mysqlbackup/full/full_incre_20160912_221111/-incremental-dir=/opt/mysqlbackup/inc/incre_20160912_221916/

Some of the display information is shown in the following figure:

Restore the entire library

Restore command:

# xtrabackup-defaults-file=/etc/my.cnf-prepare-user=root-password= "123456"-target-dir=/opt/mysqlbackup/full/full_incre_20160912_221111/

Some of the display information is shown in the following figure:

Then stop the mysql database:

[root@localhost ~] # systemctl stop mysqld

Start the rsync data file:

# cd / opt/mysqlbackup/full/full_incre_20160912_221111/

# rsync-rvt-- exclude 'xtrabackup_checkpoints'-- exclude' xtrabackup_logfile'. / / usr/local/mysql/data/

When the data is restored to the DATADIR directory, you also need to make sure that the owner and group of all data files are the correct users, such as mysql, otherwise, you need to modify the owner and group of the data file before starting mysqld.

To grant mysql access:

# chown-R mysql:mysql / usr/local/mysql/data/

Start the mysql service:

# systemctl start mysqld

Verification

Log in to mysql and see that the data that was previously deleted after the backup has been restored by 2 incremental backups, as shown below:

[root@localhost] # mysql-uroot-p123456-e "select * from test.xx"

+-+ +

| | id | name |

+-+ +

| | 1 | tom1 |

| | 2 | tom2 |

| | 3 | tom3 |

| | 4 | tom4 |

+-+ +

Scheme 3: innobackupex full database backup + innobackupex incremental backup

Test environment preparation

Create a test database and create a table to enter several rows of data

Mysql > create database test2

Mysql > use test2

Mysql > create table yy (id int,name varchar (20))

Mysql > insert into yy values (1)

Mysql > insert into yy values (2meme Kim2')

1. Innobackupex makes a full backup first.

The command is as follows:

# innobackupex-defaults-file=/etc/my.cnf-user=root-password= "123456" / opt/mysqlbackup/full/full_incre_$ (date +% Y%m%d_%H%M%S)-no-timestamp

Some of the display information is shown in the following figure:

View full backup files

# ll/ opt/mysqlbackup/full/

Drwxr-x---. 10 root root 4096 Sep 12 23:52 full_incre_20160912_235237

Innobackupex makes incremental backup

Make the first incremental backup

Input incremental data first

Mysql > use test2

Mysql > insert into yy values (3memorials Kim3')

Then perform an incremental backup with the following command:

# innobackupex-incremental / opt/mysqlbackup/inc/incre_$ (date +% Y%m%d_%H%M%S)-incremental-basedir=/opt/mysqlbackup/full/full_incre_20160912_235237/-user=root-password= "123456"-no-timestamp

Some of the display information is shown in the following figure:

View incremental backup files

# ll / opt/mysqlbackup/inc/

Drwxr-x---. 10 root root 4096 Sep 12 23:56 incre_20160912_235636

Make a second incremental backup based on the full backup and the first incremental backup

Input incremental data first

Mysql > use test2

Mysql > insert into yy values (4meme Kim 4')

Start the second incremental backup, backup command:

# innobackupex-incremental / opt/mysqlbackup/inc/incre_$ (date +% Y%m%d_%H%M%S)-incremental-basedir=/opt/mysqlbackup/inc/incre_20160912_235636/-user=root-password= "123456"-no-timestamp

Some of the display information is shown in the following figure:

View incremental backup files

# ll / opt/mysqlbackup/inc/

Drwxr-x---. 10 root root 4096 Sep 12 23:56 incre_20160912_235636

Drwxr-x---. 10 root root 4096 Sep 12 23:59 incre_20160912_235942

2. Do incremental recovery with innobackupex

Delete the incremental data twice first to view the results of the verification recovery

Mysql > use test2

Mysql > delete from yy where id=3

Start to restore, restore full backup

The command is as follows:

# innobackupex-apply-log-redo-only / opt/mysqlbackup/full/full_incre_20160912_235237/

Some of the display information is shown in the following figure:

-- redo-only is used to prepare incremental backup content to merge data into a full backup directory, which is used in conjunction with incremental-dir incremental backup directory.

Restore the first incremental backup based on full backup

The command is as follows:

# innobackupex-apply-log-redo-only / opt/mysqlbackup/full/full_incre_20160912_235237/-incremental-dir=/opt/mysqlbackup/inc/incre_20160912_235636/

Restore the second incremental backup based on the full backup and the first incremental backup

The command is as follows:

# innobackupex-apply-log-redo-only / opt/mysqlbackup/full/full_incre_20160912_235237/-incremental-dir=/opt/mysqlbackup/inc/incre_20160912_235942/

Restore the entire database

Stop the database

# systemctl stop mysqld

Empty all files in the data directory

# mkdir-p / tmp/mysqldatabak

# mv / usr/local/mysql/data/* / tmp/mysqldatabak/

Copy the recovered data to the appropriate directory according to the requirements of the configuration file.

# innobackupex-defaults-file=/etc/my.cnf-user=root-password= "123456"-copy-back / opt/mysqlbackup/full/full_incre_20160912_235237/

When the data is restored to the DATADIR directory, you also need to make sure that the owner and group of all data files are the correct users, such as mysql, otherwise, you need to modify the owner and group of the data file before starting mysqld.

Give permission to mysql account

# chown-R mysql:mysql / usr/local/mysql/data/

Start the mysql service

# systemctl start mysqld

Log in to the mysql interface to see if the data has been restored, as shown below:

Mysql > use test2

Mysql > select * from yy

+-+ +

| | id | name |

+-+ +

| | 1 | kim1 |

| | 2 | kim2 |

| | 3 | kim3 |

| | 4 | kim4 |

+-+ +

Attached: Xtrabackup's "streaming" and "backup compression" functions

Xtrabackup supports the "stream" function for backup data files, that is, the backup data can be transferred to tar programs for archiving through STDOUT, instead of being saved directly to a backup directory by default. To use this feature, you only need to use the-- stream option. Such as:

# innobackupex-- user=root-- password= "123456"-- stream=tar / opt/mysqlbackup/full/ | gzip > / opt/mysqlbackup/full/full_ `date +% Flying% H% M% S`.tar.gz

Http://hongge.blog.51cto.com/

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.