Xtrabackup Database Backup tool

Xtrabackup

Official principles

in the InnoDB internally maintains a redo log file, which we can also call a transaction log file. The transaction log stores record modifications for each of the InnoDB table data. When InnoDB starts,InnoDB examines the data file and transaction log and performs two steps: it applies (rolls forward) the transaction log to the data file that has been committed, and rolls back the data that has been modified but not committed.

Xtrabackup remembers the log sequencenumber(LSN) at startup and copies all the data files. The copy process takes some time, so if the data file changes during this period, the database will be at a different point in time. At this point,xtrabackup runs a background process that monitors the transaction log and copies the latest modifications from the transaction log. Xtrabackup must continue to do this because the transaction log is rotated repeatedly and the transaction log can be reused. as a result, Xtrabackup has kept track of changes to every data file in the transaction log since it started.

The above is the xtrabackup backup process. Next is the preparation (prepare) process. In this process,Xtrabackup uses the previously copied transaction log to perform a disaster recovery on individual data files ( just as it did when MySQL was started). When this process is over, the database can be restored and restored.

Xtrabackup innobackupex can allow us to back up myisam table and frm innobackupex xtrabackup until xtrabackup after copying the data file, then execute FLUSH TABLES with READ lock MyISAM data file, finally release the lock.

backup myisam and Innodb InnoDB xtrabackup at the beginning of the point. This point in time is the same as executing the FLUSH TABLES with READ lock myisam oracle, innodb prepare< Span style= "font-family: ' The song Body '; The process can be called the recover myisam The data replication process can be called the restore (Restore).

Both the Xtrabackup and Innobackupex tools offer a number of features that are not mentioned in the previous article. Each feature is described in detail in the manual. In brief, these tools provide streaming (streaming) backups, incremental (incremental) backups, and so on, by replicating data files, copying log files, and committing logs to data files (roll forward) to achieve a variety of composite backup methods.

Xtrabackup There are two main tools: Xtrabackup , Innobackupex

1,Xtrabackupcan only be backed upInnoDBand theXtraDBtwo kinds of data sheets, but not backupMyISAMData Sheet 
2 ,innobackupex-1.5.1it encapsulates theXtrabackupis a script wrapper, so it can be backed up at the same timeInnoDBand theMyISAM, but in processingMyISAMyou need to add a read lock 

Installation

Download website

#wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/source/tarball/percona-xtrabackup-2.4.4.tar.gz

Download the required dependencies for installation , starting with the MySQL 5.7.5 Boost Library is required

#wget http://downloads.sourceforge.net/project/boost/boost/1.59.0/boost_1_59_0.tar.gz

#yum Install cmake gcc gcc-c++ libaiolibaio-devel automake autoconf BZR bison libtool ncurses-devel libgcrypt-devellibcurl -devel libev-devel Python-sphinx perl-digest-md5 perl-dbd-mysql-y

#cp percona-xtrabackup-2.4.4.tar.gz/usr/local/src/

#cp boost_1_59_0.tar.gz/usr/local/src/

#cd/usr/local/src/

#tar ZXF boost_1_59_0.tar.gz

Installing Xtrabackup

#tar ZXF percona-xtrabackup-2.4.4.tar.gz

#cd percona-xtrabackup-2.4.4

#cmake-dbuild_config=xtrabackup_release-ddownload_boost=1-dwith_boost=. /boost_1_59_0

#make-j4

#make Install

#ln-s/usr/local/xtrabackup/bin/innobackupex/usr/bin/

#ln-S/usr/local/xtrabackup/bin/xtrabackup/usr/bin/

Common options:

--defaults-file # configuration file path for the database

--apply-log # ready to start the MySQL service on a backup .

--copy-back # Copy data from the backup directory, index, log to The initial location specified in the My.cnf file.

--no-timestamp # do not automatically generate a time directory when creating a backup, you can customize the backup directory name for example: /backups/mysql/base

--databases # used to specify the database to be backed up and How to use multiple library files : "Database1 Database2″

--incremental # Incremental backup based on full backup, followed by incremental backup storage directory path

--incremental-basedir=directory # The full backup path directory required for incremental backups or the directory path of the last incremental backup

--incremental-dir=directory # directory path for incremental backup storage

--redo-only # is used to prepare incremental backup content to merge data into the full backup directory, in conjunction with the Incremental-dir incremental backup directory.

--force-non-empty-directories # If it is a specific library backup restore, do not need to delete the entire MySQL directory, only the specific library and related files can be restored with this parameter will not be error.

Backup

Full backup

#innobackupex--defaults-file=/data/mysql/data3307/my.cnf--user=root--password=123456--socket=/data/mysql/ data3307/mysql.sock/back/full/

--host: Specify the backup server IP

--defaults-file: Specifying a database configuration file

--user=root: Backing Up users

--password=123456: Backing up user passwords

--socket=/data/mysql/data3307/mysql.sock: Specify sock file

/back/full: Database Full backup directory

Incremental backup

The first incremental backup needs to be done on a full backup basis.

#innobackupex--defaults-file=/data/mysql/data3307/my.cnf--user=root--password=123456--socket=/data/mysql/ Data3307/mysql.sock--incremental/back/incre/--incremental-basedir=/back/full/2016-07-28_01-46-28/

--incremental/back/incre: Specify this incremental backup to hold the directory

--incremental-basedir=/back/full/2016-07-28_01-46-28: Specify a full backup storage directory

Make a second incremental backup

#innobackupex--defaults-file=/data/mysql/data3307/my.cnf--user=root--password=123456--socket=/data/mysql/ Data3307/mysql.sock--incremental/back/incre/--incremental-basedir=/back/incre/2016-07-28_01-50-41/

--incremental/back/incre: Specify this incremental backup to hold the directory

--INCREMENTAL-BASEDIR=/BACK/INCRE/2016-07-28_01-50-41: Specifies that the first incremental backup directory is an incremental backup of the reference point

etc...

Data recovery

Full backup Recovery

#/etc/init.d/mysql3307 stop

#innobackupex--user=root--password=123456--socket=/data/mysql/data3307/mysql.sock--apply-log--use-memory=100M/ back/full/2016-08-08_00-46-06/

#mv/data/mysql/data3307/data/mysql/data3307.bak

#mkdir –p/data/mysql/data3307

#innobackupex--user=root--password=123456--socket=/data/mysql/data3307/mysql.sock--copy-back--datadir=/data/ mysql/data3307/back/full/2016-08-08_00-46-06/

#cp/data/mysql/data3307.bak/my.cnf/data/mysql/data3307/

#chown –R mysql.mysql/data/mysql/data3307

#/etc/init.d/mysql3307 start

Incremental backup Recovery

Stop MySQL

#/etc/init.d/mysql3307 stop

Apply a full backup

#innobackupex--user=root--password=123456--socket=/data/mysql/data3307/mysql.sock--apply-log--use-memory=100M- -redo-only/back/full/2016-08-08_00-46-06/

Apply the first incremental backup to a full backup

#innobackupex--user=root--password=123456--socket=/data/mysql/data3307/mysql.sock--apply-log--use-memory=100M- -redo-only/back/full/2016-08-08_00-46-06/--incremental-dir=/back/incre/2016-07-28_01-46-28/

Apply a second incremental backup to a full backup

#innobackupex--user=root--password=123456--socket=/data/mysql/data3307/mysql.sock--apply-log--use-memory=100M- -redo-only/back/full/2016-08-08_00-46-06/--incremental-dir=/back/incre/2016-07-28_01-50-41/

etc... The last incremental library does not add a –redo-only Options

Create mysql data Catalog

#mv/data/mysql/data3307/data/mysql/data3307.bak

#mkdir/data/mysql/data3307

Restore a full backup to the database

#innobackupex--user=root--password=123456--socket=/data/mysql/data3307/mysql.sock--copy-back--datadir=/data/ mysql/data3307/back/full/2016-08-08_00-46-06/

start MySQL

#cp data3307.bak/my.cnf data3307/

#chown-R Mysql.mysql data3307

#/etc/init.d/mysql3307 start

See if data is restored

file description for backup :

(1) xtrabackup_checkpoints―― the backup type (such as full or incremental), the backup state (such as whether it is already in the prepared state), and the LSN ( log sequence number ) range information;

each The InnoDB page ( usually 16k size ) contains a log sequence number, the 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.

#cat xtrabackup_checkpoints

Backup_type = full-backuped # Note This is fully prepared

FROM_LSN = 0 # Fully-prepared LSN start number

TO_LSN = 2026561686 # full record to the maximum serial number

LAST_LSN = 2026562643 # The current log sequence number after full provisioning is complete

Compact = 0 # Note not packaged

(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.

#cat Xtrabackup_binlog_info

mysql-bin.0000011371180-1-175

(3) Backup-my.cnf―― Backup command to use the configuration options information and backup-independent will not be logged, backup configuration files need to be backed up separately.

#cat backup-my.cnf

# This MySQL options file is generated Byinnobackupex.

# The MySQL server

[Mysqld]

Innodb_checksum_algorithm=innodb

Innodb_log_checksum_algorithm=innodb

Innodb_data_file_path=ibdata1:12m:autoextend

innodb_log_files_in_group=2

innodb_log_file_size=50331648

Innodb_fast_checksum=false

innodb_page_size=16384

innodb_log_block_size=512

innodb_undo_directory=.

Innodb_undo_tablespaces=0

Server_id=1

Redo_log_version=0

(4) Xtrabackup_info―― Records The version information of mariadb and some attribute information, which is used when detecting version matching.

#cat Xtrabackup_info

UUID = cda678f9-4dde-11e6-9906-0050562d6ff0

Name =

Tool_name = Innobackupex

Tool_command =--defaults-file=/data/mysql/data3307/my.cnf--use-memory=4g--user=root--password= ...--port=3307-- Socket=/data/mysql/data3307/mysql.sock--incremental/back/dump/incre/2016-07-20_02-28-40--incremental-basedir/ Back/dump/incre/2016-07-20_02-28-40/2016-07-20_02-29-12

Tool_version = 2.4.4

Ibbackup_version = 2.4.4

Server_version = 10.1.14-mariadb

Start_time = 2016-07-20 02:29:58

End_time = 2016-07-20 02:30:00

Lock_time = 0

Binlog_pos = filename ' master-bin.000031 ', position ' 1532 ', GTID of the last change ' 0-1-4383 '

INNODB_FROM_LSN = 1729763

INNODB_TO_LSN = 1735728

partial = N

incremental = Y

format = File

Compact = N

compressed = N

encrypted = N

Xtrabackup Database Backup tool