Use Xtrabackup remote Backup to and rebuild the database __ database

Website

Https://www.percona.com/doc/percona-xtrabackup/LATEST/innobackupex/streaming_backups_innobackupex.html

Remote compression

Innobackupex--defaults-file=/data0/mysql/3306/my.cnf--user=root--socket=/tmp/mysql_3306.sock--stream=tar./|ssh root@172.16.190.47 "gzip->/data0/mysql/134_3306.tar.gz"

Local compression

Innobackupex--defaults-file=/data0/mysql/3317/my.cnf--user=root--socket=/tmp/mysql_3317.sock--stream=tar/home/ |gzip->/data0/full_backmysql_3317/036_3317.tar.gz

mkdir Temp

TAR-IZXVF 134_3306.tar.gz-c temp/

-

Preparatory work:

Download Xtrabackup from Percona website:

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.3/binary/tarball/ Percona-xtrabackup-2.3.3-linux-x86_64.tar.gz

Create a database backup account: GRANT SELECT, RELOAD, PROCESS, SUPER, LOCK TABLES, REPLICATION SLAVE, REPLICATION CLIENT, show VIEW on *.* to ' d B_user ' @ ' localhost ' identified by ' Db_password ';

Use the Percona xtrabackup Backup tool to back up directly from the local to the remote server in a streaming backup, which is useful when the backup data is large and the local disk capacity is more than an hour.

1. Backup

#使用tar流备份

/opt/viewer/percona-xtrabackup-2.3.3-linux-x86_64/bin/innobackupex--defaults-file= $MY _cnf--tmpdir= $TMP _dir-- Ibbackup=/opt/viewer/percona-xtrabackup-2.3.3-linux-x86_64/bin/xtrabackup--user=user--password=password-- Slave-info--stream tar $DATA _dir | SSH $REMOTE _host "cat->/vobiledata/tmp/${local_host}.mysqldata.tar.lzma"

#使用xbstream流备份

./innobackupex--defaults-file=/etc/mysql/my.cnf--user=xxx--password=xxx--stream=xbstream/vobiledata/mysqldata/ |ssh root@ $REMOTE _host
"Cd/root/percona-xtrabackup-2.3.3-linux-x86_64/bin; Cat-|./xbstream-x-c/vobiledata/mysqldata/"> Innobackupex.log 2>&1

#备份指定数据库

./innobackupex--defaults-file=/etc/mysql/my.cnf--user=xxx--password=xxx--databases= "db1 DB2"--stream=xbstream/ vobiledata/mysqldata/|ssh root@ $REMOTE _host
"Cd/root/percona-xtrabackup-2.3.3-linux-x86_64/bin; Cat-|./xbstream-x-c/vobiledata/mysqldata/"> Innobackupex.log 2>&1

--note that the--databases parameter values are enclosed in quotation marks.

2. Recovery

#解压数据库备份文件
Tar-xi--lzma-f ${db_data_package_file}

#还原数据库
innobackupex-1.5.1--user=root--apply-log--defaults-file={mysql_conf_file}--ibbackup=/usr/bin/xtrabackup_51-- use-memory=2046m ${mysql_data_dir}

#将数据文件拷贝到数据目录, if the file is already in the database directory when you unzip it, you can omit this step
#innobackupex -1.5.1--user=root--copy-back--defaults-file=${mysql_conf_file}--use-memory=2046m--no-lock ${mysql_ Data_dir}

#恢复增量备份示例:

/usr/local/percona-xtrabackup-2.4.4-linux-x86_64/bin/innobackupex--defaults-file=./my.cnf--apply-log-- Redo-only./fullbackup
/usr/local/percona-xtrabackup-2.4.4-linux-x86_64/bin/innobackupex--defaults-file=./my.cnf--apply-log-- Redo-only./fullbackup--INCREMENTAL-DIR=/DATA/DB_RECOVERY/HOSTIP/INCR1
/usr/local/percona-xtrabackup-2.4.4-linux-x86_64/bin/innobackupex--defaults-file=./my.cnf--apply-log-- Redo-only./fullbackup--INCREMENTAL-DIR=/DATA/DB_RECOVERY/HOSTIP/INCR2
/usr/local/percona-xtrabackup-2.4.4-linux-x86_64/bin/innobackupex--defaults-file=./my.cnf--apply-log-- Redo-only./fullbackup--INCREMENTAL-DIR=/DATA/DB_RECOVERY/HOSTIP/INCR3
/usr/local/percona-xtrabackup-2.4.4-linux-x86_64/bin/innobackupex--defaults-file=./my.cnf--apply-log-- Redo-only./fullbackup--INCREMENTAL-DIR=/DATA/DB_RECOVERY/HOSTIP/INCR4
/usr/local/percona-xtrabackup-2.4.4-linux-x86_64/bin/innobackupex--defaults-file=./my.cnf--apply-log-- Redo-only./fullbackup--INCREMENTAL-DIR=/DATA/DB_RECOVERY/HOSTIP/INCR5
/usr/local/percona-xtrabackup-2.4.4-linux-x86_64/bin/innobackupex--defaults-file=./my.cnf--apply-log./ Fullbackup--INCREMENTAL-DIR=/DATA/DB_RECOVERY/HOSTIP/INCR6
/usr/local/percona-xtrabackup-2.4.4-linux-x86_64/bin/innobackupex--defaults-file=./my.cnf--apply-log./ Fullbackup

Exception handling:

Question 1. Xtrabackup backup appears "Can" T locate digest/md5.pm in @INC "
Reason analysis: When installing Xtrabackup 2.4, PERL-DIGEST-MD5 components are not installed
Solution: After installing the PERL-DIGEST-MD5, the problem can be solved.
Redhat and Peripheral systems: Yum Install PERL-DIGEST-MD5
Debian and Peripheral Systems: Apt-get Install PERL-DIGEST-MD5
Reference document "Http://www.cnblogs.com/kerrycode/p/6433075.html"

Question 2:

Important:please Check that the backup run completes successfully.
At the end of a successful backup run Innobackupex
Prints "completed ok!".


170511 16:33:48 Version_check connecting to MySQL server with DSN ' dbi:mysql:;mysql_read_default_group=xtrabackup;host= 127.0.0.1;mysql_socket=/data/mysql5.6/mysql.sock ' as ' db_backup ' (using Password:yes).
Failed to connect to MySQL server as Dbd::mysql module is not installed At-line 1327.
170511 16:33:48 connecting to MySQL server host:127.0.0.1, User:db_backup, Password:set, port:0, Socket:/DATA/MYSQL5. 6/mysql.sock
Using Server version 5.6.27-log

Reason: Perl-dbd-mysql not installed

Workaround: Yum Install Perl-dbd-mysql