使用xtrabackup远程备份到及重建数据库

待我称王封你为后i 2022-05-18 04:37 1349阅读 0赞

# 使用xtrabackup远程备份到及重建数据库 #

官网

https://www.percona.com/doc/percona-xtrabackup/LATEST/innobackupex/streaming\_backups\_innobackupex.html

远程压缩

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"

本地压缩

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/

准备工作：

从percona网站下载xtrabackup：

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.3/binary/tarball/percona-xtrabackup-2.3.3-[Linux][]\-x86\_64.tar.gz

创建[数据库][Link 1]备份帐号：GRANT SELECT, RELOAD, PROCESS, SUPER, LOCK TABLES, REPLICATION SLAVE, REPLICATION CLIENT, SHOW VIEW ON \*.\* TO 'db\_user'@'localhost' IDENTIFIED BY 'db\_password';

使用percona xtrabackup备份工具，以流备份的方式，直接从本地备份到远程服务器上，当备份数据比较大，而本地磁盘容量又比较小时很有用。

1.备份

\#使用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][Link 1]/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

\-- 注意 --databases参数值要用引号括起来

2.恢复

\#解压数据库备份文件  
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\}

\#将数据文件拷贝到数据目录，如果解压的时候文件已经在数据库目录下，则可省略此步骤          
\#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

异常处理：

问题1. XtraBackup备份出现"Can't locate Digest/MD5.pm in @INC"  
原因分析：安装Xtrabackup 2.4时，没有安装 perl-Digest-MD5组件  
解决方法：安装perl-Digest-MD5后，问题即可解决。  
redhat及周边系统：yum  install perl-Digest-MD5  
debian及周边系统：apt-get install  perl-Digest-MD5  
参考文档【http://www.cnblogs.com/kerrycode/p/6433075.html】

问题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

原因：没有安装perl-DBD-MySQL

解决方法：yum install perl-DBD-MySQL

[Linux]: http://lib.csdn.net/base/linux
[Link 1]: http://lib.csdn.net/base/mysql