Innobackupex full backup database, innobackupex

Innobackupex full backup database, innobackupex

For MySQL Database Hot backup, xtrabackup is the best choice except MySQL enterprise backup. This tool provides Hot Backup Based on the innodb Storage engine, supports full, Incremental backup, partial backup, point-in-time recovery, and uses xtrabackup backup to redo slave. The xtrabackup toolkit contains an innobackupex command line tool that supports both the InnoDB engine and the MyISAM engine. This article mainly describes the backup principles of trabackup and provides relevant demonstrations.

 

1. Install and deploy Xtrabackup
: Http://www.percona.com/software/percona-xtrabackup
# This installation version is 2.2.5. After downloading and decompressing it into an rpm file, install it directly.

# Tar-xvf Percona-XtraBackup-2.2.5-r5027-el5-x86_64-bundle.tar
# Rpm-Uvh percona-xtrabackup-2.2.5-5027.el5.x86_64.rpm
# Rpm-Uvh percona-xtrabackup-debuginfo-2.2.5-5027.el5.x86_64.rpm
# Rpm-Uvh percona-xtrabackup-test-2.2.5-5027.el5.x86_64.rpm
[Root @ app ~] # Ls/usr/bin/xtraba */usr/bin/inno *
/Usr/bin/innobackupex/usr/bin/innochecksum/usr/bin/xtrabackup

Xtrabackup has two main tools: xtrabackup and innobackupex.
A. xtrabackup can only back up InnoDB and XtraDB data tables, but cannot back up MyISAM data tables.
B. innobackupex is a perl script that encapsulates xtrabackup. Supports backing up InnoDB and MyISAM engine tables at the same time.
Note: The xtrabackup and innobackupex described in this article can be considered as the same concept.

 

2. How xtrabackup works
The InnoDB engine is very similar to Oracle in that it uses the redo and undo mechanisms. Therefore, during hot backup, you need to consider the issue of writing online transaction logs in the log buffer to files in a timely manner. If the log buffer is not written in time, it will be overwritten by the cyclic write feature of the log. Xtrabackup will remember log sequence number (LSN) at startup, and then copy InnoDB data one page at a time. At the same time, it monitors the logs in the log buffer. Once the log buffer changes, that is, data inconsistency occurs, the process will be immediately captured and the changed page will be copied to the xtrabckup log, after all innoDB data files are copied, the monitoring log buffer and log replication are stopped.
Xtrabackup rolls back the committed transactions, uncommitted or failed transactions during recovery to ensure data consistency. Therefore, InnoDB tables are not locked during Backup. Because XtraBackup's built-in InnoDB library is rw when opening files, users running XtraBackup must have read and write permissions on InnoDB data files.

3. extrabackup backup steps

4. demonstrate full backup

# Current environment robin @ localhost [(none)]> show variables like 'version '; + --------------- + ------------ + | Variable_name | Value | + --------------- + ------------ + | version | 5.6.12-log | + --------------- + ------------ + robin @ localhost [(none)]> show variables like '% default_storage %'; + ------------------------ + -------- + | Variable_name | Value | + ---------------------- + -------- + | default_storage_engine | InnoDB | + ------ ------------------ + -------- + # Full backup database [mysql @ app ~] $ Innobackupex -- user = robin-password = xxx -- port = 3606 -- socket =/tmp/mysql3606.sock \> -- defaults-file =/data/inst3606/data3606/my3606.cnf/data/bak /hotbakInnoDB Backup Utility v1.5.1-xtrabackup; copyright 2003,200 9 Innobase Oy ............ 141105 15:41:59 innobackupex: Connecting to MySQL server with DSN 'dbi: mysql :;............. innobackupex: Using mysql server version 5.6.12-loginnobackupex: Creat Ed backup directory/data/bak/hotbak/2014-11-05_15-42-02 # create a timestamp-based folder under the specified backup directory 141105 15:42:02 innobackupex: Starting ibbackup with command: xtrabackup -- defaults-file = "/data/inst3606/data3606/my3606.cnf" -- defaults-group = "mysqld" -- backup -- suspend-at-end -- target-dir =/data/ bak/hotbak/2014-11-05_15-42-02 -- tmpdir =/tmp -- extra-lsndir = '/tmp' innobackupex: waiting for ibbackup (pid = 27441) to suspendi Nnobackupex: Suspend file '/data/bak/hotbak/2014-11-05_15-42-02/xtrabackup_suspended_2' # obtain the configuration information about innodb from the configuration file xtrabackup version 2.2.5 based on MySQL server 5.6.21 Linux (x86_64) (revision id:) xtrabackup: uses posix_fadvise (). xtrabackup: cd to/data/inst3606/data3606xtrabackup: open files limit requested 0, set to 1024 # Author: Leshamixtrabackup: using the following InnoDB configuration: # Blog: Http://blog.csdn.net/leshamixtrabackup: innodb_data_home_dir =. /xtrabackup: innodb_data_file_path = ibdata1: 10 M: autoextendxtrabackup: innodb_log_group_home_dir =. /xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 50331648> log scanned up to (380655683) # scan the innodb log lsn and copy the inndodb system tablespace [01] Copying. /ibdata1 to/data/bak/hotbak/2014-11-05_15-42-02/ibdata1> log scanned u P to (380655683)> log scanned up to (380655683) [01]... done [01] Copying. /mysql/slave_relay_log_info.ibd to/data/bak/hotbak/2014-11-05_15-42-02/mysql/slave_relay_log_info.ibd # similar parts are omitted, all are ibd files based on the innodb engine... [01] Copying. /tempdb/tb_user.ibd to/data/bak/hotbak/2014-11-05_15-42-02/tempdb/tb_user.ibd [01]... done> log scanned up to (380655683) xtrabackup: Creating suspend file '/data/bak/hotbak/2014- 27441/xtrabackup_suspended_2 'with pid '000000' 141105 15:42:30 innobackupex: Continuing after ibbackup has suspended141105 15:42:30 innobackupex: Executing flush tables with read lock... # buffer the data file and lock the table 141105 15:42:30 innobackupex: All tables locked and flushed to disk141105 15:42:30 innobackupex: starting to backup non-InnoDB tables and files # copy non-innodb tables and Related files innobackupex: in subdirectories Of '/data/inst3606/data3606 'innobackupex: Backing up files'/data/inst3606/data3606/mysql /*. {frm, isl, MYD, MYI, MAD, MAI, MRG, TRG, TRN, ARM, ARZ, CSM, CSV, opt, par} '(74 files)> log scanned up to (380655683) innobackupex: Backing up files '/data/inst3606/data3606/salary /*. {frm, isl, MYD, MYI, MAD, MAI, MRG, TRG, TRN, ARM, ARZ, CSM, CSV, opt, par} '(99 files) innobackupex: backing up files '/data/inst3606/data3606/Walkman Ce_schema /*. {frm, isl, MYD, MYI, MAD, MAI, MRG, TRG, TRN, ARM, ARZ, CSM, CSV, opt, par} '(53 files) innobackupex: backing up file '/data/inst3606/data3606/tempdb/tb_user.frm' innobackupex: Backing up file '/data/inst3606/data3606/tempdb/db. opt '2017 15:42:31 innobackupex: Finished backing up non-InnoDB tables and files141105 15:42:31 innobackupex: Executing flush engine logs... # Write the innodb logfile to the disk at, 141105 1 innobackupex: Waiting for log copying to finishxtrabackup: The latest check point (for incremental): '000000' # obtain The latest lsnxtrabackup: Stopping log copying thread...> log scanned up to (380655683) xtrabackup: Creating suspend file '/data/bak/hotbak/2014-11-05_15-42-02/with' with pid '000000' xtrabackup: Transaction log of lsn (27441) to (380655683) was copied.141105 15:42:32 inn Obackupex: All tables unlocked # unlock tableinnobackupex: Backup created in directory '/data/bak/hotbak/alipay' # list binlog locations innobackupex: MySQL binlog position: filename 'inst3606bin. 100', position 000001, GTID of the last change ''352 15:42:32 innobackupex: Connection to database server closed141105 15:42:32 innobackupex: completed OK!

5. Backup Information

In addition to viewing the backup information from the input log, you can also obtain the information generated during the backup from the backup location # view the files generated during the backup [mysql @ app ~] $ Ls/data/bak/hotbak/2014-11-05_15-42-02/backup-my.cnf mysql salary partition performance_schema tempdb xtrabackup_checkpoints xtrabackup_logfile # view the overall backup information [mysql @ app ~] $ More/data/bak/hotbak/2014-11-05_15-42-02/xtrabackup_infouuid = bytes = tool_name = innobackupextool_command = -- user = robin-password = xxx -- port = 3606 -- socket =/tmp/mysql3606.sock -- defaults -file =/data/inst3606/data3606/my3606.cnf/data/bak/hotbaktool_version = 1.5.1-xtrabackupibbackup_version = xtrabackup version 2.2.5 based on MySQL server 5.6.21 Linux (x86_64) (Revision id:) server_version = 5.6.12-logstart_time = 2014-11-05 15: 41: 59end_time = 2014-11-05 15: 42: 32lock_time = 2binlog_pos = filename 'inst3606bin. 100', position 000001, GTID of the last change ''innodb _ from_lsn = 0innodb_to_lsn = 380655683 partial = Nincremental = Nformat = filecompact = Ncompressed = Nencrypted = N # view information related to the backup binlog [mysql @ app ~] $ More/data/bak/hotbak/2014-11-05_15-42-02/xtrabackup_binlog_infoinst3606bin.000001 352 # checkpoint information [mysql @ app ~] $ More/data/bak/hotbak/2014-11-05_15-42-02/xtrabackup_checkpointsbackup_type = full-backupedfrom_lsn = 0to_lsn = 410655683last_lsn = 380655683 compact = 0

6. Files generated by innobackupexe backup
Backup-my.cnf
MySQL files +
Xtrabackup_binlog_info
Xtrabackup_binlog_pos_innodb
Xtrabackup_slave_info (When-slave-info is used)
Xtrabackup_checkpoints
Only after-apply-log
Xtrabackup_logfile
Xtrabackup_binary