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

I. brief introduction

Xtrabackup is a mysql database backup tool provided by percona. According to officials, it is the only open source tool in the world that can provide hot backup for innodb and xtradb databases. Its main feature is that the backup process is fast and reliable, does not interrupt the ongoing transactions, can save disk space and traffic based on compression and other functions, and automatically achieve fast backup detection and restore speed.

II. Implementation of installation, backup and recovery

Installation: the latest version of the software is available from http://www.percona.com/software/percona-xtrabackup/. This article is based on centos6.x 's system, so you can download and install the corresponding version of the rpm package directly, and the process will not be demonstrated here.

Yum-y install percona-toolkit-2.2.4-1.noarch.rpm percona-xtrabackup-2.1.8-733.rhel6.x86_64.rpm

Complete backup and delete the data directory to achieve recovery:

Mysql > set session_sql_log_bin=0; # Import data without recording binary log mysql > source / root/hellodb.sql # Import data mysql > set session_sql_log_bin=1 # enable binary log [root@centos6] # innobackupex-- user=root / mybackups/ # full backup [root@centos6] # service mysqld stop # stop the database Delete the data directory [root@centos6] # rm-rf / mydata/data/* [root@centos6] # innobackupex-- apply-log / mybackups/2016-11-22 backup 15-39-09 / # prepare for a full backup [root@centos6] # innobackupex-- copy-back / mybackups/2016-11-22 recovery 15-39-09 / # restore data from a full backup [root@centos6 data] # ll # data has been successfully restored 28688drwxr-xr-x. 2 root root 4096 November 22 15:43 hellodb-rw-r--r--. 1 root root 18874368 November 22 15:43 ibdata1-rw-r--r--. 1 root root 5242880 November 22 15:43 ib_logfile0-rw-r--r--. 1 root root 5242880 November 22 15:43 ib_logfile1drwxr-xr-x. 2 root root 4096 November 22 15:43 mysqldrwxr-xr-x. 2 root root 4096 November 22 15:43 performance_schemadrwxr-xr-x. 2 root root 4096 November 22 15:43 test [root@centos6 data] # chown-R mysql.mysql. / * # change generic group and generic group [root@centos6 data] # ll total dosage 28688drwxr-xr-x. 2 mysql mysql 4096 November 22 15:43 hellodb-rw-r--r--. 1 mysql mysql 18874368 November 22 15:43 ibdata1-rw-r--r--. 1 mysql mysql 5242880 November 22 15:43 ib_logfile0-rw-r--r--. 1 mysql mysql 5242880 November 22 15:43 ib_logfile1drwxr-xr-x. 2 mysql mysql 4096 November 22 15:43 mysqldrwxr-xr-x. 2 mysql mysql 4096 November 22 15:43 performance_schemadrwxr-xr-x. 2 mysql mysql 4096 November 22 15:43 test [root@centos6 data] # service mysqld start # start the database, and the database does not need to start when restoring the database

From a security point of view, if you want to use a user with minimum privileges for backup, you can create this user for a full backup

Mysql > create user' bkuser'@'localhost' identified by 'passw ord'mysql > revoke all privileges,grant option from' bkuser'; mysql > grant reload,lock tables,replication clinet on *. * to 'bkuser'@'localhost' mysql > flush privileges

Xtrabackup backup file description:

When using innobakupex backup, it invokes xtrabackup to back up all InnoDB tables, copies all related files (.frm) about the table structure definition, and related files for MyISAM, MERGE, CSV, and ARCHIVE tables, as well as files related to triggers and database configuration information. These files are saved to a directory with time commands.

[root@centos6 2016-11-22 May 19-06-45] # the total amount of ll is 18472. 1 root root 260 November 22 19:06 backup-my.cnfdrwx-. 2 root root 4096 November 22 19:06 hellodb-rw-r-. 1 root root 18874368 November 22 19:06 ibdata1drwx-. 2 root root 4096 November 22 19:06 mydbdrwxr-xr-x. 2 root root 4096 November 22 19:06 mysqldrwxr-xr-x. 2 root root 4096 November 22 19:06 performance_schemadrwxr-xr-x. 2 root root 4096 November 22 19:06 test-rw-r--r--. 1 root root 13 November 22 19:06 xtrabackup_binary-rw-r--r--. 1 root root 24 November 22 19:06 xtrabackup_binlog_info-rw-r-. 1 root root 89 November 22 19:06 xtrabackup_checkpoints-rw-r-. 1 root root 2560 November 22 19:06 xtrabackup_ log [root @ centos6 2016-11-22 19-06-45] # cat xtrabackup_checkpointsbackup_type = full-backuped # backup type For example, full backup, Incremental backup, etc. From_lsn = 0 # Log sequence number starts at 0 to_lsn = 1649842 # Log sequence number to where last_lsn = 1649842 # Log sequence number ends compact = 0 [root@centos6 2016-11-22 log 19-06-45] # cat xtrabackup_binlog_info # the binary log file currently in use and its binary location master-bin.000005 245 [root@centos6 2016-11-22 log 19-06-45] # cat xtrabackup_binary # xtrabackup executable file used in backup xtrabackup_55 [root@centos6 2016-11-22 19-06-45] # cat backup-my.cnf # configuration option information used in backup command # This MySQL options file was generated by innobackupex.# The MySQL server [mysqld] innodb_data_file_path=ibdata1:10M:autoextendinnodb_log_files_in_group=2innodb_log_file_size=5242880innodb_fast_checksum=0innodb_page_size=16384innodb_log_block_size= 512 [root @ centos6 2016 / 11 / 22 / 19 / 06 / 45] #

Use innobackupex for incremental backup and data recovery:

Each InnoDB page contains a LSN message, 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, that is, innobackupex is achieved by backing up pages that have changed since the last full backup.

To achieve the first incremental backup, you can use the following command:

# innobackupex-incremental / backup-incremental-basedir=BASEDIR

Where BASEDIR refers to the directory where the full backup is located, and after the execution of this command, the innobackupex command creates a new time-named directory in the / backup directory to hold all incremental backup data. In addition, when you perform an incremental backup again after an incremental backup, its-- incremental-basedir should point to the directory where the last incremental backup was located.

It is important to note that incremental backups can only be applied to InnoDB or XtraDB tables, and for MyISAM tables, incremental backups are actually performed as full backups.

There are some differences between prepare incremental backups and collating full backups, especially the following:

(1) transactions that have been committed need to be "replayed" on each backup (including full and individual incremental backups). After replay, all backup data will be merged into the full backup.

(2) "rollback" uncommitted transactions based on all backups.

Therefore, the operation becomes:

# innobackupex-apply-log-redo-only BASE-DIR

Then execute:

# innobackupex-apply-log-redo-only BASE-DIR-incremental-dir=INCREMENTAL-DIR-1

Then comes the second increment:

# innobackupex-apply-log-redo-only BASE-DIR-incremental-dir=INCREMENTAL-DIR-2

Innobackupex-copy-back / path/to/BACKUP-DIR # initial data

Where BASE-DIR refers to the directory where the full backup is located, INCREMENTAL-DIR-1 refers to the directory of the first incremental backup, INCREMENTAL-DIR-2 refers to the directory of the second incremental backup, and so on, that is, if there are multiple incremental backups, do the same each time.

The example shows that the backup and recovery of incremental data experienced two increments in the course of the experiment, and the corresponding database was modified. After the increment was completed, the mysql database was stopped, the data directory was deleted, and the data recovery operation was carried out.

The implementation code is as follows:

Innobackupex-- user=root / mybackups/ # first full backup innobackupex-- incremental / mybackups/-- incremental-basedir=/mybackups/2016-11-22 backup 19-24-37 / # first incremental backup, inserting tables into the hellodb database and deleting tables T1 innobackupex-- incremental / mybackups/-- incremental-basedir=/mybackups/2016-11-22 databases 19-27-55 / # second incremental backup At this point, the database mydbs is modified, the table is created and the data innobackupex is inserted-- apply-log-- redo-only / mybackups/2016-11-22 transactions 19-24-37 / # after replaying the nearly committed transactions innobackupex-- apply-log-- redo-only / mybackups/2016-11-22 transactions 19-24-37 /-- incremental-dir=/mybackups/2016-11-22 transactions 19-27-55 / # All backup data is merged into full backup innobackupex-- apply-log-- redo-only / mybackups/2016-11-22 percent 19-24-37 /-incremental-dir=/mybackups/2016-11-22 percent 19-29-57 / # after replay, all backup data is merged into full backup innobackupex-- copy-back / mybackups/2016-11-22 percent 19-24-37 / # now the data has been synchronized with the last increment Use this data to restore [root@centos6 data] # cd / mybackups/2016-11-22 19-24-37 / [root@centos6 2016-11-22 19-24-37] # cat xtrabackup_checkpointsbackup_type = full-prepared # first full backup The replay will be consistent with the serial number information in the last incremental backup from_lsn = 0to_lsn = 1708653last_lsn = 1708653compact = 0 [root@centos6 2016-11-22 19-24-37] # cat.. / 2016-11-22 19-29-57/xtrabackup_checkpointsbackup_type = incrementalfrom_lsn = 1703868to_lsn = 1708653compact = 0 [root@centos6 2016-11-22 19-24-37] # cat xtrabackup_binlog_infomaster-bin.000006 8434 [root@centos6 2016-11-22: 19-24-37] # cat.. / 2016-11-22: 19-29-57/xtrabackup_binlog_infomaster-bin.000006 8434 [root@centos6 2016-11-22: 19-24-37] # [root@centos6 data] # Total amount of ll 18456drwxr-xr-x. 2 root root 4096 November 22 19:37 hellodb-rw-r--r--. 1 root root 18874368 November 22 19:37 ibdata1drwxr-xr-x. 2 root root 4096 November 22 19:37 mydbdrwxr-xr-x. 2 root root 4096 November 22 19:37 mydbsdrwxr-xr-x. 2 root root 4096 November 22 19:37 mysqldrwxr-xr-x. 2 root root 4096 November 22 19:37 performance_schemadrwxr-xr-x. 2 root root 4096 November 22 19:37 test [root@centos6 data] # chown-R mysql.mysql. / * [root@centos6 data] # cd.. / binlogs/ [root@centos6 binlogs] # mysqlbinlog-- start-position=245 masterbin.000007pact 50530 SET @ SESSION.PSEUDOSLAVESLAVESLAVEMETDE1ModeledThreads0University50003 SET @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER / *! / / # at 4 "161122 19:31:08 server id 1 end_log_pos 245 Start: binlog v 4, server v 5.5.32-MariaDB-log created 161122 191GAAAA8QAAA8QAAAAAAAAAAAAAAAAAAAANS41LjMyLU1hmlhREItbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SET @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.session .sqlcards increasing numbers off set @ @ session.auto_increment_increment=1, @ @ session.session .autovariation increments offsetbacks @ @ session. Utf8 * / / *! * /; SET @ @ session.session. SET @ @ session.lccalendar timetables namespace namespaces SET @ @ session.collationalization databaseDeFAULTSet @ @ session.collationalization databaseDFAULTGER create table students (id int,name varchar (20)) / *! /; # at 355 "161122 19:32:26 server id 1 end_log_pos 424 Querythread_id=9exec_time=0error_code=0SET timespacks 1479814346 balance" at 424 "161122 19:32:26 server id 1 end_log_pos 537 Querythread_id=9exec_time=0error_code=0SET timespacks 1479814346 Insert into students values / *! * /; # at 537 SET 161122 19:32:26 server id 1 end_log_pos 564 Xid = 155 COMMIT Cummings DELIMITER; # End of log fileROLLBACK / * added by mysqlbinlog * /; / *! 50003 SET components completed TYPERYPERYOLDCOMPLETIONTYPERYOLDCOMPLEIONTYPERYOLDD completed TYPENETOLYPERAPHY [root@centos6 binlogs] # mysqlbinlog-- start-position=245 master-bin.000007 > / root/incr.sql [root@centos6 ~] # mysqlWelcome to the MariaDB monitor. Commands end with; or\ g.Your MariaDB connection id is 3Server version: 5.5.32-MariaDB-log MariaDB ServerCopyright (c) 2000, 2013, Oracle, Monty Program Ab and others.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.MariaDB [(none)] > set sql_log_bin=0; MariaDB [hellodb] > source / root/incr.sqlMariaDB [hellodb] > set sql_log_bin=1;MariaDB [hellodb] > use mydbs;MariaDB [mydbs] > show tables +-+ | Tables_in_mydbs | +-+ | students | | T1 | +-2 rows in set (0.00 sec) MariaDB [mydbs] > select * from studnets;ERROR 1146 (42S02): Table 'mydbs.studnets' doesn't existMariaDB [mydbs] > select * from students +-+-+ | id | name | +-+-+ | 1 | tom | | 2 | jerry | +-+-+ 2 rows in set (0.00 sec) MariaDB [mydbs] > use hellodb;Database changedMariaDB [hellodb] > show tables +-+ | Tables_in_hellodb | +-+ | classes | | coc | | courses | | scores | | students | | tbl | | teachers | | toc | +- -+ 8 rows in set (0.00 sec) MariaDB [hellodb] >

Compare the data before viewing the data and deleting the data catalog. If the data is consistent, the data is restored successfully. Xtrabackup+ binary log can achieve full backup, incremental backup, full backup recovery and incremental data recovery. At the same time, when using this backup tool, it will not affect the normal access of customers and improve the user experience.

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.