MySQL management-using XtraBackup for Hot Backup

Introduction to XtraBackup

XtraBackup replaces the innodbbackup tool, and XtraBackup is fully compatible with the innodb Storage engine, and implements full physical layer hot backup for the innodb Storage engine.

Site: http://www.percona.com/

Percona has modified mysql and has higher performance. percona server

Percona server also provides the synchronous replication cluster function for mysql modification, so percona's influence in the circle even exceeds the official mysql version.

For MyISAM tables, only warm backup is supported, and Incremental backup is not supported.

More advanced features of XtraBackup can only be implemented on the innodb Storage engine, and the advanced features also rely on the mysql database to implement separate tablespace for the innodb engine. Otherwise, single table or single Database Export cannot be implemented.

 

Use each table space

Before using xtrabackup, we need to check whether mysql currently uses each table tablespace. If not, we must change it to a separate table tablespace.

Mysql> show global variables like '% innodb_file_p % ';
+ ----------------------- + ------- +
| Variable_name | Value |
+ ----------------------- + ------- +
| Innodb_file_per_table | OFF |
+ ----------------------- + ------- +
1 row in set (0.00 sec)

Here is the off status, so we need to start this function. If xtrabackup or various advanced features are used in the future, we recommend that you directly write the default configuration into the configuration file when installing mysql, it will be very troublesome to change the data in the middle. Fortunately, we are in a testing environment. We need to export the data, change the configuration, and then import the data.

[Root @ test2 ~] # Mysqldump-uroot -- lock-all-tables -- all-databases -- master-data = 2 -- events>./1. SQL

Close Database

[Root @ test2 ~] #/Etc/init. d/mysqld stop
Shutting down MySQL... SUCCESS!

Add the following parameters to edit the configuration file:

[Root @ test2 ~] # Vim/etc/my. cnf

Innodb_file_per_table = 1

Then delete the data file and reinitialize it.

[Root @ test2 data] # pwd
/Mydata/data

Delete all data files

[Root @ test2 data] # rm-fr *

Initialize mysql

[Root @ test mysql] # pwd
/Usr/local/mysql
[Root @ test mysql] # scripts/mysql_install_db -- user = mysql -- datadir =/mydata/data/-- basedir =/usr/local/mysql/

[Root @ test mysql] #/etc/init. d/mysqld start

Check whether each table has a separate tablespace.

Mysql> showglobal variables like '% innodb_file_per % ';

+ ----------------------- + ------- +

| Variable_name | Value |

+ ----------------------- + ------- +

| Innodb_file_per_table | ON |

+ ----------------------- + ------- +

1 row in set (0.00sec)

Restore data

Mysql> source ~ /1. SQL

[Root @ test2 data] # ll wpdb/

Total 220

-Rw-r --. 1 mysqlmysql 61 Apr 6 11: 05 db. opt

-Rw-r --. 1 mysql 8646 Apr 6 students. frm

-Rw-r --. 1 mysql 98304 Apr 6 11: 05students. ibd

-Rw-r --. 1 mysql 8556 Apr 6 tb4.frm

-Rw-r --. 1 mysql 98304 Apr 6 tb4.ibd

As shown above, each table has a separate tablespace.

 

Install percona-xtrabackup

Resolve Dependencies

[Root @ test ~] # Yum-y install libaio perl-Time-HiRes perl-DBD-MySQL perl-IO-Socket-SSL

First install percona-xtrabackup

[Root @ test2 tools] # rpm-ivh percona-xtrabackup-2.1.4-656.rhel6.x86_64.rpm

Warning: percona-xtrabackup-2.1.4-656.rhel6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing... ######################################## ### [100%]
1: percona-xtrabackup ##################################### ###### [100%]

View Software attributes

[Root @ test tools] # rpm-qlpercona-xtrabackup
/Usr/bin/innobackupex # unified command INTERFACE
/Usr/bin/innobackupex-1.5.1
/Usr/bin/xbcrypt
/Usr/bin/xbstream # supports stream backup
/Usr/bin/xtrabackup
/Usr/bin/xtrabackup_55 # apply to mysql5.5 and 5.6 respectively, and automatically determine the current mysql version based on the program, and then apply the matching tool to back up the data.
/Usr/bin/xtrabackup_56

/Usr/share/doc/percona-xtrabackup-2.1.4
/Usr/share/doc/percona-xtrabackup-2.1.4/COPYING

 

 

Use xtrabackup for full backup

Xtracbackup supports hot backup when backing up the innodb table, but the backup method is relatively simple. First, the files backed up by the table structure are directly copied, the data file reads the corresponding data block LSN (log serial number) and then determines whether a backup is required. Its full backup is the latest serial number from the serial number 0 to the present.

Then, during Incremental backup, the serial number from the last backup or Incremental backup to the current latest serial number will be backed up, therefore, It scans the log serial number of each data block to determine whether it needs to be backed up and how to back up the data.

There is no doubt that full backup is required. Every block must be backed up.

Incremental Backup must be the data block from the last backup serial number to the updated serial number

Therefore, data blocks are directly backed up physically.

 

During backup, the following files are generated in the relevant directory:

Xtrabackup_checkpoints checkpoint file

· Backup Type (such as full or incremental)

· Backup status (for example, whether the status is prepared or not, simply put, the backed up data cannot be used to restore immediately, you also need to synchronize the submitted items in all the transaction logs to the data file and roll back the uncommitted items. This process is called a preprocessing process, therefore, the backup data cannot be restored without pre-processing)

 

LSN (log serial number) range information, such:

· Full backup LSN range: 0-16000

· Incremental Backup LSN range: 16001-17000

· The second backup LSN ranges from 17001 to 18000.

· Determine whether the current incremental data is complete or incremental by using the LSN number (the data changed from the previous time to the current time)

 

For xtrabackup, when we use full backup + Incremental backup, the following points must be known if we expect full + incremental recovery during recovery:

1. Some logs in the transaction log are committed, some logs are not committed, and uncommitted transactions need to be rolled back;

2. 1. If something is not submitted during full backup in the first Incremental backup, It is submitted during the first Incremental backup, in full backup, rollback does not make any sense. Therefore, xtra is used to back up data, which is the same as the restoration process using mysqldump, instead, complete backup is performed first and then complete backup is preprocessed, but only half of the full backup is processed during preprocessing;

3. Only the submitted items are synchronized to the data file, but the uncommitted items are not rolled back;

4. As long as there are incremental uncommitted items, you cannot roll back, then, after the submitted transaction is synchronized to the data file, the first Incremental Backup will be applied to the full backup (if there are multiple Incremental backup data ), merge Incremental backup to full backup. Therefore, some items in the first Incremental Backup have been submitted, but the full backup data has not been submitted. Therefore, the Incremental backup is merged, after the merge is completed, the second increment is merged, and so on.

5. After the data is merged, the LNS number ranges from 0 to the latest. After the data is merged, It is restored using binary data.

 

Therefore, when we have Incremental backup, we cannot roll back uncommitted items. Otherwise, the incremental data cannot be used. If we do not roll back uncommitted items, in this case, there may be uncommitted transactions in the last increment, so mysql can only be started to repair the database. Therefore, the last stage depends on the mysql storage engine.

 

The process of using xtrabackup to implement full backup is very simple, but it must be noted that only users with the execution permission can perform backup.

If you want to use a user with the minimum permission for backup, you can create such users based on the following command:

Mysql> CREATEUSER 'bkpuser' @ 'localhost' identified by 's3cret ';
Mysql> revoke all privileges, grant option from 'bkpuser ';
Mysql> grant reload, lock tables, replication client on *. * TO 'bkpuser' @ 'localhost'; # authorization. These three permissions guarantee the minimum backup permission.

Mysql> FLUSHPRIVILEGES;

As the test environment is used, we use root for backup/restoration.

XtraBackup details: click here
XtraBackup: click here

Recommended reading:

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

1 2 3 4 Next Page