MySQL Backup and recovery xtrabackup

Xtrabackup: Provided by Percona, instead of ibbackup Backup tool. Used to back up its xtradb engine data, XTRADB itself is InnoDB enhanced full compatibility InnoDB, only MyISAM Win Bei, more advanced features support InnoDB.

Xtrabackup advanced features depend on whether the MySQL InnoDB engine enables a single table space per table, or you cannot use a single table import to export

Characteristics:

(1) Fast and reliable backup process

(2) The backup process does not interrupt the executing transaction

(3) Ability to save disk space and traffic based on functions such as compression

(4) Automatically implement backup inspection

(5) Fast restore speed

To install the Percona xtrabackup tool:

[email protected] mydata]# yum-y install perl-dbi perl-dbd-mysql perl-time-hires Perl-io-socket-ssl Main Installation perl-dbd-mysql Perl-time-hires package

RPM-IVH libev4-version.x86_64.rpm relies on LIBEV4 64-bit version RPM package RPM-IVH percona-xtrabackup-2.3.2-1.el6.x86_64.rpm

Common options:

--host designated Host

--user Specify user name

--password Specifying a password

--port Specifying ports

--databases Specifying a database

--incremental Creating an incremental backup

--incremental-basedir specifying a directory that contains a full backup

--INCREMENTAL-DIR Specifies the directory that contains the incremental backup

--apply-log preprocessing operations on backups

In general, data cannot be used for recovery operations after the backup is complete, because the data that is backed up may contain transactions that have not yet been committed or that have been committed but have not been synchronized to the data file. Therefore, the data file still handles the inconsistent state at this time. The primary role of Prep is to keep the data file in a consistent state by rolling back uncommitted transactions and synchronizing committed transactions to data files.

--redo-only do not roll back uncommitted transactions

--copy-back Restore the backup directory

When you use a Innobakupex backup, it calls Xtrabackup back up all InnoDB tables, copies all related files (. frm) about the table structure definition, and related files for the MyISAM, MERGE, CSV, and archive tables. It also backs up files related to triggers and database configuration information, which are saved to a directory in a time command.

Backup, Innobackupex also creates the following files in the backup directory:

(1) xtrabackup_checkpoints--backup type (such as full or incremental), backup status (such as whether it is already prepared status) and LSN (log sequence number) range information, each InnoDB page (usually 16k size) contains a log sequence number, That is, LSN. LSN is the system version number of the entire database system, and each page-related LSN can indicate how the page has changed recently.

(2) Xtrabackup_binlog_info--mysql the binary log file currently in use by the server and the location of the binary log event up to the moment the backup was made.

(3) xtrabackup_binlog_pos_innodb--binary log files and the current position for binary log files for InnoDB or xtradb tables.

(4) Xtrabackup executable files used in xtrabackup_binary--backup;

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

When using Innobackupex for backup, you can also use the--NO-TIMESTAMP option to prevent commands from automatically creating a time-named directory, so that the Innobackupex command creates a Backup-dir directory to store the backup data.

If you want to back up with a user with minimal permissions, you can create such a user based on the following command:

mysql> create user ' backuser ' @ ' localhost ' identified by ' Backuser '

mysql> REVOKE all privileges,grant OPTION from ' backuser ' @ ' localhost '; Reclaim all permissions for this user

mysql> GRANT reload,lock tables,replication CLIENT on * * to ' baskuser ' @ ' localhost ';

Authorization refresh, lock table, user View server status

mysql> FLUSH privileges; Refresh Authorization Table

Backup process:

Note: You need to start MySQL on backup, turn off MySQL on recovery, empty the MySQL data directory and not reinitialize, a full backup should be done immediately after recovering the data

Full backup :

Innobackupex--user=backuser--password=backuser/backup/xtrabackup/

The backup automatically generates files that end with the current date time

Incremental Backup 1:

Innobackupex--user=backuser--password=backuser--incremental/backup/xtrabackup/--incremental-basedir=/backup/ Xtrabackup/2016-06-14_16-37-07/basedir Specifying a full backup

Incremental Backup 2:

Innobackupex--user=backuser--password=backuser--incremental/backup/xtrabackup/--incremental-basedir=/backup/ Xtrabackup/2016-06-14_16-44-03/basedir Specifying incremental Backups 1

To export the pre-crash binary log:

mysql> show master status;+------------------+------- ---+--------------+------------------+-------------------+| file              | position | binlog_do_db | binlog_ignore_ db | executed_gtid_set |+------------------+----------+--------------+------------------+------ -------------+| mysql-bin.000028 |      4200|               |                   |                    |+------------------+----------+------------ --+------------------+-------------------+1 row in set  (0.00 sec) 

Mysqlbinlog mysql-bin.000028--start-position=3204--stop-position=4200 >/backup/xtrabackup/increment.sql View Incremental backups File and location, export pre-crash data

Recovery process:

The Prepare incremental backup is a bit different from organizing a full backup, especially to note that:

(1) You need to "replay" a transaction that has already been committed on each backup, including full and individual incremental backups. Replay, all backup data will be merged into a full backup.

(2) Use the option--apply-log to "roll back" uncommitted transactions based on all backups--redo-only

Pre-processing a full backup:

Innobackupex--apply-log--redo-only/backup/xtrabackup/2016-06-14_16-37-07/

Preprocessing Incremental Backup 1:

Innobackupex--apply-log--redo-only/backup/xtrabackup/2016-06-14_16-37-07/--incremental-dir=/backup/xtrabackup/ 2016-06-14_16-44-03/

Preprocessing Incremental Backup 2:

Innobackupex--apply-log--redo-only/backup/xtrabackup/2016-06-14_16-37-07/--incremental-dir=/backup/xtrabackup/ 2016-06-14_16-45-26/

Recovering a full backup: Because the previous incremental backup has been preprocessed to a full backup

Innobackupex--copy-back 2016-06-14_16-37-07/

Restore the pre-crash binary log:

Mysql> SET sql_log_bin=0; Turn off the binary logging feature

Mysql> Source/backup/xtrabackup/increment.sql; Recovering binary Log Backups

Mysql> SET sql_log_bin=1; Restore complete turn on binary log

mysql> FLUSH privileges; Restore completed reread authorization form

Before starting the Mysqld service, you also need to modify the owner and host groups of the data file in advance

Chown-r mysql:mysql/mydata/data/

Cat/backup/xtrabackup/2016-06-14_16-45-26/xtrabackup_binlog_info

View log files and locations for incremental backup 2

Cat/backup/xtrabackup/2016-06-14_16-37-07/xtrabackup_binlog_info

View log files and locations for full backups consistent with incremental backup 2

If the last incremental backup has uncommitted transactions, it can only be repaired automatically after MySQL startup, similar to the MySQL crash repair action

At this point to use xtrabackup Backup, restore MySQL completed, if there are errors please advise!

This article is from "Why No!" blog, be sure to keep this source http://mydove.blog.51cto.com/8487472/1790820

MySQL Backup and recovery xtrabackup