Innobackupex full backup database

Innobackupex full backup database

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/hotbak

InnoDB 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-log

Innobackupex: Created 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 suspend
Innobackupex: Suspend file '/data/bak/hotbak/2014-11-05_15-42-02/xtrabackup_su11-0ded_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/data3606
Xtrabackup: open files limit requested 0, set to 1024 # Author: Leshami
Xtrabackup: using the following InnoDB configuration: # Blog: http://blog.csdn.net/leshami
Xtrabackup: innodb_data_home_dir = ./
Xtrabackup: innodb_data_file_path = ibdata1: 10 M: autoextend
Xtrabackup: 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 up 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
# Omitted similar parts, all of which 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-11-05_15-42-02/xtrabackup_suspended_2' with pid '123'

141105 15:42:30 innobackupex: Continuing after ibbackup has suincluded
141105 15:42:30 innobackupex: Executing flush tables with read lock... # buffer writes to the data file and LOCK the table
141105 15:42:30 innobackupex: All tables locked and flushed to disk

141105 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/performance_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'
141105 15:42:31 innobackupex: Finished backing up non-InnoDB tables and files

141105 15:42:31 innobackupex: Executing flush engine logs... # innodb logfile write to disk
141105 15:42:31 innobackupex: Waiting for log copying to finish

Xtrabackup: The latest check point (for incremental): '000000' # obtain The latest lsn
Xtrabackup: Stopping log copying thread.
.> Log scanned up to (380655683)

Xtrabackup: Creating suspend file '/data/bak/hotbak/2014-11-05_15-42-02/xtrabackup_log_copied' with pid '123'
Xtrabackup: Transaction log of lsn (380655683) to (380655683) was copied.
141105 15:42:32 innobackupex: All tables unlocked # unlock table

Innobackupex: Backup created in directory '/data/bak/hotbak/2014-11-05_15-42-02' # list binlog locations
Innobackupex: MySQL binlog position: filename 'inst3606bin. 100', position 000001, GTID of the last change''
141105 15:42:32 innobackupex: Connection to database server closed
141105 15:42:32 innobackupex: completed OK! & Nbsp;

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 Backup
[Mysql @ app ~] $ Ls/data/bak/hotbak/2014-11-05_15-42-02/
Backup-my.cnf mysql salary xtrabackup_binlog_info xtrabackup_info
Ibdata1 performance_schema tempdb xtrabackup_checkpoints xtrabackup_logfile

# View overall backup information
[Mysql @ app ~] $ More/data/bak/hotbak/2014-11-05_15-42-02/xtrabackup_info
Uuid = 4d49753a-64bf-11e4-8850-8c89a5d424ae
Name =
Tool_name = innobackupex
Tool_command = -- user = robin-password = xxx -- port = 3606 -- socket =/tmp/mysql3606.sock
-- Defaults-file =/data/inst3606/data3606/my3606.cnf/data/bak/hotbak
Tool_version = 1.5.1-xtrabackup
Ibbackup_version = xtrabackup version 2.2.5 based on MySQL server 5.6.21 Linux (x86_64) (revision id :)
Server_version = 5.6.12-log
Start_time = 15:41:59
End_time = 15:42:32
Lock_time = 2
Binlog_pos = filename 'inst3606bin. 100', position 000001, GTID of the last change''
Innodb_from_lsn = 0
Innodb_to_lsn = 380655683
Partial = N
Incremental = N
Format = file
Compact = N
Compressed = N
Encrypted = N

# Viewing information related to the backup binlog
[Mysql @ app ~] $ More/data/bak/hotbak/2014-11-05_15-42-02/xtrabackup_binlog_info
Inst3606bin. 000001 352

# Checkpoint Information
[Mysql @ app ~] $ More/data/bak/hotbak/2014-11-05_15-42-02/xtrabackup_checkpoints
Backup_type = full-backuped
From_lsn = 0
To_lsns = 380655683
Last_lsns = 380655683
Compact = 0 & nbsp;

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

This article permanently updates the link address: