Xtrabackup's Innobackupex online hot standby MySQL database

Xtrabackup Introduction

Xtrabackup is a innodb to do data backup tools, Support Online hot backup (backup without affecting the data read and write), is a commercial backup tool InnoDB hotbackup a good alternative.

Xtrabackup has two main tools: Xtrabackup, Innobackupex

(1) Xtrabackup can only back up InnoDB and xtradb two data tables, but not myisam data tables

(2) The innobackupex-1.5.1 encapsulates the Xtrabackup, which is a script wrapper, so the InnoDB and MyISAM can be backed up at the same time, but a read lock is required to process the MyISAM

PERCONA

Website address: http://www.percona.com/

Software: http://www.percona.com/downloads/XtraBackup/LATEST/binary/tarball/percona-xtrabackup-2.2.3-4982-Linux-x86_64.tar.gz

Deploying software Versions

CentOS 6.4 64bit

Xtrabackup version: percona-xtrabackup-2.2.3-4982-linux-x86_64.tar.gz

MySQL version: mysql-5.6.12-linux-glibc2.5-x86_64.tar.gz

First, install the decompression xtrabackup

1, installation percona-xtrabackuprpm -ivh http://dl.fedoraproject.org/pub/epel/6/x86_64/ epel-release-6-8.noarch.rpm *** Non-mandatory ***{yum -y install cmake gcc gcc-c++  libaio libaio-devel automake autoconf bzr bison libtool ncurses-devel  zlib-devel libgcrypt libgcrypt-devel }yum -y install perl-time-hires  perl-dbd-mysqltar xf percona-xtrabackup-2.2.3-4982-linux-x86_64.tar.gz cp  PERCONA-XTRABACKUP-2.2.3-LINUX-X86_64/BIN/* /USR/BIN/2, install the database install MySQL here no longer: Please refer to my previous blog, thank you ..... [[email protected] ~]# mysql --versionmysql  ver 14.14 distrib  5.6.12, for linux-glibc2.5  (x86_64)  using  EditLine  Wrapper The default storage engine is INNODB need to pay attention to  RM -RF /ETC/MY.CNF (install Perl-dbd-mysql when the default installs Mysql-libs comes with/ETC/MY.CNF ) LN -S /USR/LOCAL/MYSQL/MY.CNF /ETC/3, CREATE database # mysqladmin -uroot password -p 1234# mysql -uroot -p1234mysql> create database security; Mysql> use security;mysql> create table t_user (Usename char), password  char (Ten), Tel char (one)); Mysql> desc t_user;mysql> insert into t_user  values (' Zhengyansheng ', ' 1234 ', ' 13260071987 '); Mysql> insert into t_user values (' Wangyuzhi ', ' 1234 ', ' 13260071987 '); Mysql> insert into t_user values (' Wangqiyu ', ' 1234 ', ' 13260071987 '); Mysql> insert into t_user values (' allentuns ', ' 1234 ', ' 13260071987 '); MySQL > select * from t_user;+---------------+----------+-------------+| usename        | password | tel          |+---------------+----------+-------------+| zhengyansheng | 1234      | 13260071987 | |  wangyuzhi     | 1234     | 13260071987  ||  wangqiyu      | 1234     | 13260071987  | |  Allentuns     | 1234     | 13260071987  |+---------------+----------+-------------+{basedir = /usr/local/mysql/datadir = /mydata/ Datasocket = /tmp/mysql.sock}

Ii. Xtrabackup Online hot standby database

1, create a backup directory and back up all the database mkdir /backup2, the database is fully prepared 1>  full backup of the database # innobackupex --defaults-file=/usr/ Local/mysql/my.cnf --host=localhost --user=root --password=1234 --socket=/tmp/mysql.sock  /backup output below indicates success:innobackupex: backup created in directory  '/backup/2014-08-31_ 00-51-48 ' 140831 00:51:52  innobackupex: connection to database server  closed140831 00:51:52  innobackupex: completed ok!2>  Viewing the backup directory [[email  protected] ~]# ll /backup/Total dosage  4drwxr-xr-x 6 root root 4096 8 Month   31 00:51 2014-08-31_00-51-48   #全备3 >  View the LSN number of the backup [[email protected]  ~]# cat /backup/2014-08-31_00-51-48/xtrabackup_checkpoints backup_type =  full-backupedfrom_lsn = 0to_lsn = 1648701last_lsn = 1648701compact =  03, incremental backup of the database +1  (on a fully prepared basis to increaseBackup) explains several parameters:--incremental-basedir  : Indicates the location of the last full or incremental backup file--incremental: Indicates an incremental backup, where incremental backups are for InnoDB only, For MyISAM, a full backup 1>  first adds data to the database Mysql> create table t_user_one as select  * from t_user;2>  incremental backup of a database +1# innobackupex --defaults-file=/usr/local/mysql /my.cnf --host=localhost --user=root --password=1234 --socket=/tmp/mysql.sock -- Incremental-basedir=/backup/2014-08-31_00-51-48/ --incremental /backup output below indicates success:innobackupex:  backup created in directory  '/backup/2014-08-31_00-53-36 ' 140831 00:53:40   innobackupex: connection to database server closed140831 00:53:40   innobackupex: completed ok!3>  Viewing the backup directory [[email protected] ~]# ll /backup/ Total dosage  8drwxr-xr-x 6 root root 4096 8 Month   31 00:51 2014-08-31_ 00-51-48   #全备drwxr-xr-x 6 root root 4096 8 month   31 00:53 2014-08-31_00-53-36   #增量备份 +14 >  View the LSN number of the backup [[Email protected] ~]# cat /backup/2014-08-31_00-53-36/xtrabackup_ Checkpoints backup_type = incrementalfrom_lsn = 1648701to_lsn = 1661966last _LSN = 1661966COMPACT = 04, incremental backups of the database +2  (incremental backup on an incremental basis) 1>  first add data to the database mysql>  create table t_user_two as select * from t_user;2>  incremental backups of a database +2# innobackupex --defaults-file=/usr/local/mysql/my.cnf --host=localhost --user=root  --password=1234 --socket=/tmp/mysql.sock --incremental-basedir=/backup/2014-08-31_00-53-36/  --incremental /backup# innobackupex --defaults-file=/usr/local/mysql/my.cnf --host= localhost --user=root --password=1234 --socket=/tmp/mysql.sock --incremental-basedir=/ Backup/2014-08-30_13-08-33/ --incremental /backup  output below indicates success: Innobackupex: backup created in directory   '/backup/2014-08-31_00-53-36 ' 140831 00:53:40  innobackupex: connection to  Database server closed140831 00:53:40  innobackupex: completed ok!3>   View backup directory [[email protected] ~]# ll /backup/Total usage  12drwxr-xr-x 6 root  Root 4096 8 month   31 00:51 2014-08-31_00-51-48   #全备drwxr-xr-x 6  Root root 4096 8 Month   31 00:53 2014-08-31_00-53-36   #增量备份 + 1drwxr-xr-x 6 root root 4096 8 Month   31 00:59 2014-08-31_00-59-10    #增量备份 +24>  View the LSN number of the backup [[email protected] ~]# cat /backup/2014-08-31_ 00-59-10/xtrabackup_checkpoints backup_type = incrementalfrom_lsn = 1661966to_lsn  = 1679546last_lsn = 1679546compact = 05, restore Backup data (this data is deleted before making an incremental backup) hypothetical scenario: 1 "misoperation deleted important database, such as Security2" misoperation deleted data file, such as rm - rf /mydata/data/***** Delete Security database ***mysql> show databases;+--------------------+|  database           |+--------------------+|  information_schema | |  mysql              | |  performance_schema | |  security           | |  test               |+-------- ------------+mysql> drop database security; query ok, 3 rows affected  (0.04 sec) mysql> show databases;+-------- ------------+| database           |+-------------- ------+| information_schema | |  mysql              | |  performance_schema | |  test               |+-------- ------------+ Incremental Recovery: The steps for incremental backup recovery are basically the same as the steps for full backup recovery, except that the process of applying the logs is slightly different. When an incremental backup is restored, all incremental backups are first applied to the full backup data file, and then the data from the full backup is restored to the database. 1, stop the database service mysqld stop2, back up the database Cd /mydatacp -a data data_bak3, back up the database backup file CP  -A /BACKUP /BACKUP24, in our environment we do the following, because we only have two incremental backups as follows: # innobackupex -- defaults-file=/usr/local/mysql/my.cnf --host=localhost --user=root --password=1234 -- Socket=/tmp/mysql.sock --apply-log --redo-only /backup/2014-08-31_00-51-48/# innobackupex  --defaults-file=/usr/local/mysql/my.cnf --host=localhost --user=root --password=1234  --socket=/tmp/mysql.sock --apply-log --redo-only /backup/2014-08-31_00-51-48/ -- incremental-dir=/backup/2014-08-31_00-53-36/# innobackupex --defaults-file=/usr/local/mysql/my.cnf --host=localhost -- User=root --password=1234 --socket=/tmp/mysql.sock --apply-log  /backup/2014-08-31_ 00-51-48/ --INCREMENTAL-DIR=/BACKUP/2014-08-31_00-59-10/5, rollback of incomplete logs (note that when recovering, we only restore the full backup file) #  innobackupex --apply-log /backup/2014-08-31_00-51-48/# innobackupex --copy-back / backup/2014-08-31_00-51-48/output The following error message: Innodb backup utility v1.5.1-xtrabackup; copyright  2003, 2009 innobase oyand percona llc and/or its affiliates  2009-2013.  All Rights Reserved.This software is published  Underthe gnu general public license version 2, june 1991.get the  latest version of Percona XtraBackup, documentation, and help  Resources:http://www.percona.com/xb/pimPortant: please check that the copy-back run completes successfully.            At the end of a  successful copy-back run innobackupex            prints  "completed ok!". innobackupex: error: original data directory  '/mydata/data '  is not  empty! at /usr/bin/innobackupex line 2163. Workaround: Empty the MySQL Data directory cd /mydata/datamkdir / Tmp/data_bak20140831mv * /tmp/data_bak20140831/then execute the above statement again and it will succeed # innobackupex --copy-back  /BACKUP/2014-08-31_00-51-48/6, authorization to start MySQL database chown -r mysql.mysql /mydata/data/service  MYSQLD START7, check data consistency mysql> show databases;+--------------------+| database            |+--------------------+| information_schema | |  mysql              | |  performance_schema | |  security  #已经找回  | |  test               |+-------- ------------+successfull !!!! Delete rm -rf /mydata/data/****1, stop database, delete all data files by mistake Service mysqld stoprm -rf /mydata /data/*2, in our environment, let's do the following, because we only have two incremental backups as follows: (Only one time, incremental backups are added to the fully-prepared database) # innobackupex --defaults-file=/ usr/local/mysql/my.cnf --host=localhost --user=root --password=1234 --socket=/tmp/ mysql.sock --apply-log --redo-only /backup/2014-08-31_00-51-48/# innobackupex -- defaults-file=/usr/local/mysql/my.cnf --host=localhost --user=root --password=1234 -- socket=/tmp/mysql.sock --apply-log --redo-only /backup/2014-08-31_00-51-48/ -- Incremental-dir=/backup/2014-08-31_00-53-36/# innobackupex --defaults-file=/usr/local/mysql/my.cnf --host=localhost --user=root  --password=1234 --socket=/tmp/mysql.sock --apply-log  /backup/2014-08-31_00-51-48/  --INCREMENTAL-DIR=/BACKUP/2014-08-31_00-59-10/3, rollback of incomplete logs (note that when recovering, we only restore the full backup file) # innobackupex  --apply-log /backup/2014-08-31_00-51-48/# innobackupex --copy-back /backup/ 2014-08-31_00-51-48/4, authorization to start MySQL database chown -r mysql.mysql /mydata/data/service mysqld  START5, check data consistency mysql> show databases;+--------------------+| database            |+--------------------+| information_schema | |  mysql              | |  performance_schema | |  security  #已经找回  | |  test               |+--------------------+ 

Iii. backup strategies and scripts

#!/bin/bash#time 2014-08-31#this ' s script is allentuns#tel 13260071987#qq  467754239#  per Sunday full volume, Monday to Saturday incremental backup data_path= "/backup" conf_path= "/usr/local/mysql/" weeks= ' date +%y-%u ' Username= "root" password= "1234" host= "127.0.0.1" # create directory if [ ! -d  ${data_path}/${weeks} ]then        mkdir -p ${data_path }/${weeks}else        echo  "Directory already exists" fi#full backupif [  ${week} -eq  7 ];then        echo -e   " #################################################"          echo -e  " #$ (date +%f_%t)  full backup of all databases      # "        echo -e "  ##### ############################################ "        echo " $ (date +%f_%t)   Start full backup ... "      /usr/bin/innobackupex --user= $UserName  --password= $Password  --host=$ Host --defaults-file=${conf_path}/my.cnf --no-timestamp  --slave-info  ${data_ path}/${weeks}/full    /usr/bin/innobackupex --user= $UserName  --password=$ password --host= $Host  --defaults-file=${conf_path}/my.cnf --no-timestamp  -- Slave-info --incremental --incremental-basedir=${data_path}/${weeks}/full ${data_path}/${weeks} /inc_${week}    echo  "$ (date +%f_%t)   fully ready ..." else         echo -e  " ####################################################"          echo -e  " #$ (date +%f_%t)  incremental  Backup of all databases# "        echo -e "  ################################### ################# "        echo " $ (date +%f_%t)   Start incremental backup ... "    /usr/bin/innobackupex --user= $UserName  --password= $Password  --host= $Host   --defaults-file=${conf_path}/my.cnf --no-timestamp  --slave-info -- incremental --incremental-basedir=${data_path}/${weeks}/inc_${week_ago}  ${data_path}/${weeks}/ inc_${week}        echo  "$ (date +%f_%t)   Incremental backup complete ..." fi# This script refers to the blog post, slightly modified http://chenwenming.blog.51cto.com/327092/1075532  #脚本为了节约空间可以使用压缩和删除find  /data/mysql/ Backups -mtime +7 |xargs rm -rf

Iv. problems encountered and workarounds

Error rollup: First error: Never think of a mistake because of a yum -y install mysql-libs problem 1:140831 00:16:44  innobackupex : connecting to mysql server with dsn  ' dbi:mysql:;mysql_read_default_file=/ Usr/local/mysql/my.cnf;mysql_read_default_group=xtrabackup;host=localhost;mysql_socket=/tmp/mysql.sock '   as  ' root '    (using password: yes) .innobackupex: error: failed to  Connect to mysql server as dbd::mysql module is not installed  at /usr/bin/innobackupex line 3006. Workaround: Yum -y install mysql-libs or Yum  -y install perl-time-hires perl-dbd-mysql problem 2:140831 10:04:51  innobackupex : connecting to mysql server with dsn  ' dbi:mysql:;mysql_read_default_file=/ Usr/local/mysql/my.cnf;mysql_read_default_group=xtrabackup '  as  ' root '    (using  Password: yes). InnobacKupex: error: failed to connect to mysql server: dbi connect ('; Mysql_read_default_file=/usr/local/mysql/my.cnf;mysql_read_default_group=xtrabackup ', ' Root ',...)  failed: can ' t connect to local mysql server through socket  '/var/lib/mysql/mysql.sock '   (2)  at /usr/bin/innobackupex line 2995 solution: mkdir / var/lib/mysql/ln -s /tmp/mysql.sock /var/lib/mysql/

V. Drawings, display of backup scripts

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/47/E2/wKioL1QCnKOANmIeAAJhAS1sjqs929.jpg "title=" Innobackup.png "alt=" Wkiol1qcnkoanmieaajhas1sjqs929.jpg "/>

This article is from the "Zheng" blog, make sure to keep this source http://467754239.blog.51cto.com/4878013/1547067

Xtrabackup's Innobackupex online hot standby MySQL database