Innobackupex MySQL full Standby, add-on and restore

Brief introduction:

Before this is through the mysqldump to back up the database, because it is a logical backup, so the use of this backup method of data is very safe, cross-platform, version is easy.

There are pros and cons, logical backup in your database is relatively large, backup, recovery of data time is very long, so to continue to improve, the use of physical backup.

Because the online database table uses the hybrid engine MyISAM and Innodb, so also can not use mysqlhotcopy, this tool is very good use, but can only back up MyISAM.

Percona Xtrabackup is a backup tool developed by Percona company for the physical hot standby of MySQL database, and is an open source project.

Understand that there is a Innobackupex support MyISAM and Innodb backup under the Xtrabackup tool, so come on.

First, install the Xtrabackup

Yum Install https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/ redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

# or

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/ 6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpmyuminstall epel-  Yum Install perl-dbd-> Rpm-ivh percona-xtrabackup--2.4. 4-1. el6.x86_64.rpm

# need to resolve dependencies

Second, Innobackupex fully prepared

Shell > Innobackupex--user=user--password=passwd --no-timestamp./29 /'date +%h-%m '. dbname

#--no-timestamp if not added, a backup-time directory is generated in the backup directory, and the backup data exists in that directory

# Back up the entire database to a./2016/09-29/in a directory with the backup time and custom name

# Although you can use--stream=tar Plus | Gzip > is packaged and compressed directly, but the backup speed is reduced, so I prefer to back up quickly, then deal with packaging, compression.

# fully prepared script

Shell > Vim script/backupdb.SH#!/bin/bash#0 0*/2* *SH/root/script/backupdb.SH# Author:wang Xiaoqiang ./ the/ indbname='alldatabases'Dbuser='User'Dbpassword='passwd'Backup_dir='/data/backup_db'Backup_log='/root/script/logs/backup.log'Ymtime=`Date+%y-%m ' Dtime=`Date+%d ' Wlog () {Echo-E"' Date"+%f%T"' DBname: $ state: $2\n">>$backup _log} [ ! -D $backup _dir/$ymtime] &&mkdir-P $backup _dir/$YMTIMECD $backup _dir/$ymtime &&/usr/bin/innobackupex--user= $dbuser--password= $dbpassword--no-timestamp $dbname >/dev/NULL 2>&1if[$?-eq0]; ThenWlog $dbname'Backup success.'Dbakfile= $dbname. $dtime.Tar. GZTarZCF $dbakfile $dbname--remove-files [$? -eq0] && Wlog $dbakfile'packaging success.'|| Wlog $dbakfile'packaging failed.'ElseWlog $dbname'Backup failed.'fi# End

Three, Innobackupex full standby recovery

Shell >/etc/init.d/Mysql.server Stop # First shuts down the database shell> cd/data/backup_db # Switch to backup directory Shell>MV/usr/local/mysql-5.5. the/Data Data.old # Back up the original directory of the Shell>TarZXF Alldatabase. One.Tar. GZ # Then unpack the original backup shell> Innobackupex--apply-log alldatabase # Apply the log on the backup, generally do not see the error and finally show OK there is no problem (--use-memory Use this parameter to speed up)161011  A: -: -Completed ok!Shell> Innobackupex--copy-back alldatabase # Restores the backup to my.cnf specified DataDir, does not specify--defaults-file, the default/etc/My.cnfError:datadir must be specified. # error message display, in default Config file/etc/my.cnf, datadir configuration item shell not found> vim/etc/my.cnf # Join DATADIR configuration item DataDir=/usr/local/mysql-5.5. the/Datashell> Innobackupex--copy-back Alldatabase # Execute copy action again, no error and show OK161011  A: -: theCompleted ok!Shell> ll-d/usr/local/mysql-5.5. the/DATADRWXR-X---6Root root4096October One  A: -/usr/local/mysql-5.5. the/Datashell>Chown-R mysql.mysql/usr/local/mysql-5.5. the/Data # Modify database permissions Shell>/etc/init.d/mysql.server Start

# Backup has been restored!
# Review the entire recovery process and find a problem: Everything in the database was gone ...
In other words, Innobackupex only applies to this MySQL in addition to the system library, only one library backup.
# Although there are options--databases can specify which database to back up, but the prepared result is all databases, and the time of recovery is also full coverage.
# 1, use Innobackupex back up the whole library, then restore to a new library, and then through mysqldump will need to restore a single library backup once, and then import the database to be restored (to consider the recovery time).
# 2, using Innobackupex to back up only one business library of MySQL server, restore to only the business library in MySQL server.
# 3, if there is more than one database in a MySQL Server, and only want to back up a library, and the library volume is small, we recommend using mysqldump Backup.

Third, the Innobackupex to increase the preparation

 Shell > Mysql-uuser-ppassword-a # Create test libraries, tables, insert test data mysql  > CREATE DATABASE test_incre character Set utf8;mysql  > CREATE TABLE test_incre.incre (id  int  (10 ) not Span style= "COLOR: #0000ff" >null , name varchar (20   > INSERT into test_incre.incre values ( 1 ,  " wang xiaoqiang  "   > Quitshell  > Innobackupex--user=user--password=password--database=test_incres--no-timestamp/data/backup_db/test_ Incre # First time fully prepared 

# Prove again:--databases is to sell Moe, you can go to the source database directory Du-sh with the target backup directory Du-sh, really is a hair.

Shell > Mysql-uuser-ppassword-A # Add test data MySQL> insert into test_incre.incre values (2,' Xiao Guaishou'), MySQL>> Innobackupex--user=user--password=password-- Database=test_incres--no-timestamp--incremental-basedir=/data/backup_db/test_incre--incremental/data/backup_db /test_incre01

#--incremental-basedir=/data/backup_db/test_incre first time to add a specified full standby path
#--incremental Parameter Description This is an add-on and specifies the add-on path

# Full directory Du-sh and add directory Du-sh comparison found that the directory is only less than the full directory of 1G, which shows that for the MyISAM table, or fully prepared!!!

Shell > Mysql-uuser-ppassword-A # Add test data again MySQL> insert into test_incre.incre values (3,' 
    King'); MySQL>> Innobackupex--user=user--password=password-- Database=test_incres--no-timestamp--incremental-basedir=/data/backup_db/test_incre01--incremental/data/backup_ Db/test_incre02

# The second replenishment is based on the first replenishment, so--incremental-basedir specifies the first add-on directory, the last specified second add-on directory

Shell >CatTest_incre/xtrabackup_checkpoints # Backup_type Tip: This is a full standby, from_lsn from0Start to1601917End Backup_type= full-BACKUPEDFROM_LSN=0TO_LSN=1601917LAST_LSN=1601917Compact=0Recover_binlog_info=0Shell>CatTest_incre01/xtrabackup_checkpoints # Backup_type Tip: This is an add-on, from_lsn from the last-ended LSN1601917Start to1602789End Backup_type=INCREMENTALFROM_LSN=1601917TO_LSN=1602789LAST_LSN=1602789Compact=0Recover_binlog_info=0Shell>CatTest_incre02/xtrabackup_checkpoints # Backup_type hint; this is an add-on, from_lsn from the last-ended LSN1602789Start to1603652End Backup_type=INCREMENTALFROM_LSN=1602789TO_LSN=1603652LAST_LSN=1603652Compact=0Recover_binlog_info=0

# OK, this is the incremental backup, you can see: For the MyISAM is all full-scale backup, and there is no--databases to say.

# Add Script

Iv. Innobackupex Recovery

Shell >/etc/init.d/Mysql.server Stop # Close the database shell> cd/data/Backup_dbshell>MV/usr/local/mysql-5.5. the/Data data.old02 # Back up the original database shell> Innobackupex--apply-log--redo-only test_incre # Specify a fully-prepared directory,--redo-only the committed transaction is rolled forward, only the last incremental recovery does not need to add the parameter shell> Innobackupex--apply-log--redo-only test_incre--incremental-dir=/DATA/BACKUP_DB/TEST_INCRE01 #--incremental-dirSpecifies the first add directory (absolute path) shell> Innobackupex--apply-log test_incre--incremental-dir=/DATA/BACKUP_DB/TEST_INCRE02 # Specifies a second add-on directory, and the last incremental restore does not require adding--redo-Only parameter Shell> Innobackupex--copy-back Test_incre # The last step, copy the consolidated full file, location my.cnf datadir Specify path Shell>Chown-R mysql.mysql/usr/local/mysql-5.5. the/Datashell>/etc/init.d/mysql.server Start # Starts the database to see if the data is recovering the shell> Mysql-uuser-ppassword-Amysql>Select*From Test_incre.incre;+----+----------------+|ID| Name |+----+----------------+|1| Wang Xiaoqiang | |2| Xiao Guaishou | |3| King |+----+----------------+

# OK, Wood has a problem!

Innobackupex MySQL full Standby, add-on and restore