Xtrabackup backing up the database

1. Installation:

RPM-IVH http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

Yum Install Percona-xtrabackup

#xtrabackup2.2 InnoDB engine not supported for MySQL5.1, if required to install version 2.0 (https://www.percona.com/downloads/XtraBackup/XtraBackup-2.0.8/RPM/rhel6/x86_64/ PERCONA-XTRABACKUP-20-2.0.8-587.RHEL6.X86_64.RPM)

2. Full Backup and Recovery

2.1 Full Backup

Innobackupex--defaults-file=/wqdata/mysql/my.cnf--user=bkuser--password= ' 123456 '/wqdata/mofidbbak/fullbackup/

Upload to Backup server: rsync-avzp/wqdata/mofidbbak/fullbackup/2015-08-18_14-18-11/[email protected]::mofidbbak/--password-file =/etc/rsync.password

2.2 Backup Recovery

Innobackupex--defaults-file=/opt/mysql/my.cnf--copy-back 2015-08-18_08-50-43/

2.3 Backup File Description

# ls 2015-02-08_11-56-48

BACKUP-MY.CNF: Record innobackup use to MySQL parameter

Xtrabackup_binary: The executable file used in the backup

xtrabackup_checkpoints: Record the type of backup, start and end log sequence numbers

Xtrabackup_logfile: A log copy thread is opened in the backup to monitor the InnoDB log file (Ib_logfile) and will be copied to this file if modified

2.4 Incremental backup

Innobackupex--defaults-file=/wqdata/mysql/my.cnf--user=bkuser--password= ' 123456 '--incremental/wqdata/mofidbbak /incremental/--incremental-basedir=/wqdata/mofidbbak/fullbackup/2015-08-19_08-42-04/ #指定上次完整备份目录

Incremental backup Recovery

Innobackupex--apply-log--redo-only. /FULLBACKUP/2015-08-19_08-42-04/Preparation Integrity

Innobackupex--apply-log--redo-only/wqdata/mofidbbak/fullbackup/2015-08-19_08-42-04/--incremental-dir=/wqdata/ mofidbbak/incremental/2015-08-19_08-46-38/Merge the first one

You can then restore the full backup

3.5.1 Backup Recovery Ideas

Incremental backup 1, incremental backup 2 ... Merge to full backup, add together a new full backup, a new full backup in the form of a copy to the database empty directory (rm/var/lib/mysql/*-RF)

3.5.2 Prepare a full backup

#xtrabackup把The backup process may have not committed transactions or have committed but not synchronized data file transactions, write to the xtrabackup_logfile file, so you want to roll back through this log file, Synchronize incomplete transactions to the backup file to ensure that the data files are consistent.

# innobackup--apply-log--redo-only 2015-02-08_11-56-48

3.5.3 merging the first incremental backup

# Innobackupex--apply-log--redo-only/mysql_backup/2015-02-08_11-56-48/--incremental-dir=mysql_backup/2015-02-08 _12-16-06

3.5.4 Merge a second incremental backup

# Innobackupex--apply-log--redo-only/mysql_backup/2015-02-08_11-56-48/--incremental-dir=mysql_backup/2015-02-08 _16-06-53

3.5.5 Restore full backup (at this point the 2015-02-08_11-56-48 full backup already contains all incremental backups and can be verified by looking at Checkpoints )

# Innobackupex--DEFAULTS-FILE=/ETC/MYSQL/MY.CNF--copy-back/mysql_backup/2015-02-08_11-56-48/

3.5.6 Modifying the Restore data file permissions

# Chown-r Mysql.mysql/var/lib/mysql

3.5.7 start MySQL to view database recovery

#/etc/init.d/mysqld Start

Refer to the following two articles:

http://732233048.blog.51cto.com/9323668/1660910

#!/bin/bash# This script is used for full backup of the database, combined with the scheduled task completion # Note: The following variable values may have to be modified, carefully confirm the next mysqluser=rootrsyncuser=rsync_backuprsync_hostip= ' 218.24.71.26 ' ip= ' 223.100.98.84 '                  #本机ip [email protected]mysqlsocket= '/tmp/mysql.sock ' mysql_defaults_file= '/wqdata/mysql/my.cnf ' Backuptodir= '/wqdata/mofidbbak/fullbackup/'      #备份到目录binlogdir = '/wqdata/mydata/data '        #二进制日志目录binlogname = ' mysql-bin.0* '        # Binary log file name rsyncmodule=mofidbbak            # rsync module if [ ! -d  $backuptodir  ];then    mkdir -p $ backuptodirfi# for full backup innobackupex --user= $mysqluser  --password= $mysqlpassword  --socket=$ mysqlsocket --defaults-file= $mysql _defaults_file  $backuptodir  2> /dev/null | |  exit 1# Delete the full backup file a day ago, that is, keep two backup files find  $backuptodir  -type f -mtime +1 -exec rm -f {} \;# Delete the binary log file that was a day ago, that is, the binary log file that was kept for two days cd  $binlogdironedayago _binlog= ' find . -type f -mtime  +1 -name  "$binlogname"  | sort | tail -n1 | awk -f '/'   ' { print $2} "mysql -u  $mysqluser  -p$mysqlpassword -e " Purge master logs  TO  ' $onedayago _binlog ' " 2> /dev/null# find the last full backup file, compress cd  $backuptodirlastfull _ backup= ' ls -l | grep  ' ^d '  | awk  ' {print $9} '  | sort |  tail -n1 ' tar -zcf mysql_full_${ip}_${lastfull_backup}.tar.gz ${lastfull_backup}# Synchronize the compressed file rsync to the backup machine rsync -az --password-file=/etc/rsync.password  ${backuptodir}mysql_full_${ Ip}_${lastfull_backup}.tar.gz [email protected]${rsync_hostip}:: $rsyncmodule  | |  exit 1# Deleting compressed Files Rm -f ${backuptOdir}mysql_full_${ip}_${lastfull_backup}.tar.gz 

Xtrabackup backing up the database