MySQL Innobackupex备份之二-- 异机备份构建主从数据库案例

怼烎@ 2022-04-17 03:57 332阅读 0赞

**案例说明：**

**1）host177 为生产库主从库的从库**

**2）host166 为测试库主从库的主库**

**3）host163 为测试库主从库的从库**

**4）每周日定期将线上的数据同步到测试库，构建测试库的主从架构**

**1、host177异机备份的脚本**

\[root@host177 backup\]\# cat  inno\_bk\_166\_slave.sh  
\#!/bin/bash  
\#Description:xtrabackup complete  
\#Author:created by duia.com  
\#2018-07-04 v0.1  
\#Usage: 脚本用于本地主机压缩备份并传送到远程主机，在异地恢复

DBUSER=admin                    \#数据库备份用户  
DBPASSWD='beijing'               \#数据库备份用户口令  
SSH\_USER=root                  \#ssh连接远程主机用户（如果主机间建立信任关系，则不需要）  
SSH\_PASSWD=  
SSH\_PORT=39996             \#ssh连接远程主机用户口令（如果主机间建立信任关系，则不需要）

DATE=$(date +"%Y-%m-%d\_%H-%M-%S")   
BKHOST=127.0.0.1                  \# 备份的数据库主机ip  
DEF\_CNF=/etc/my.cnf               \# 备份数据库的配置文件  
PORT=3306                        \# 备份数据库的端口  
SOCK\_F=/tmp/mysql.sock           \# 连接备份数据库的socket文件  
REMOTE\_HOST=host166              \# 远程服务器的IP  
REMOTE\_SH\_DIR=/data2/backup  
DB\_BK\_DIR=$REMOTE\_SH\_DIR/mars    \# 此目录用于远程主机压缩备份解压后文件存储的目录  
BACKUP\_DIR=$REMOTE\_SH\_DIR/full      \# 存储数据库备份后，从远程主机传送过来的压缩备份  
BAK\_DIR=$REMOTE\_SH\_DIR/bk\_backup    \# 归档历史压缩备份，定期删除  
SLAVE\_HOST=host163               \#从库主机  
SLAVE\_BKDIR=/data/backup/full

\# 备份函数

function bk\_fun()

\# 首先连接到远程主机，将历史备份进行归档

/usr/bin/ssh -Tq -p $SSH\_PORT $SSH\_USER@$REMOTE\_HOST "sh $REMOTE\_SH\_DIR/bak\_archive.sh"  
echo "$BACKUP\_DIR: \`ls -l $BACKUP\_DIR\`"

\# 通过innobackupex进行备份（本机是主从库中的从库）

\[\[ -d $BACKUP\_DIR \]\] || mkdir $BACKUP\_DIR  
/usr/bin/innobackupex --defaults-file=$DEF\_CNF --host $BKHOST --socket $SOCK\_F --port $PORT --no-timestamp  --safe-slave-backup --user=$DBUSER --password=$DBPASSWD --stream=tar /tmp |gzip |ssh -p $SSH\_PORT $SSH\_USER@$REMOTE\_HOST "cat -> $BACKUP\_DIR/mars\_"$DATE".tar.gz"

\# 执行备份函数，将日志信息写入bk\_info文件

bk\_fun > /data/backup/bk\_info.txt  2>&1

\# 判断备份是否成功

if \[ "\`tail -1 /data/backup/bk\_info.txt|grep -i -o 'completed OK' \`" = "completed OK" \] ; then

\#如果成功，连接到远程主机，将压缩备份解压到指定的目录

/usr/bin/ssh -Tq -p $SSH\_PORT $SSH\_USER@$REMOTE\_HOST<<EOF

cd $BACKUP\_DIR

\# 在将备份文件解压到此目录前，将目录清空

\[\[ -d $DB\_BK\_DIR \]\] && rm -rf $DB\_BK\_DIR/\*

/bin/tar zxvfi \*.tar.gz  -C $DB\_BK\_DIR

\#将备份拷贝到host163的从库下

/usr/bin/scp -P $SSH\_PORT \*.tar.gz  $SLAVE\_HOST:/$SLAVE\_BKDIR

\# 做数据库恢复前的prepare工作（应用redo log，保证备份文件的一致性）

/usr/bin/innobackupex --apply-log $DB\_BK\_DIR  > /data2/backup/bk\_prepare.log 2>&1

\# 执行远程shell脚本恢复数据库

/bin/sh  $REMOTE\_SH\_DIR/db\_restore.sh  >$REMOTE\_SH\_DIR/bk\_info.log 2>&1

\# 删除归档备份目录下超过2天的历史备份

\# find $BAK\_DIR -ctime +2 -exec rm \{\} \\;

exit

EOF

**2、host166用于数据库恢复的脚本**

\[root@host166 backup\]\# cat db\_restore.sh   
\#!/bin/bash  
\#Name: db\_restore.sh

\#本脚本完成的任务如下：

\# 1） 关闭数据库实例

\# 2） 删除以前数据库数据文件的备份，并将数据文件的存储目录改名备份，建立新的数据文件存储目录并修改其属性（chown）

\# 3） 通过innobackupex将远程主机备份过来的数据文件move到数据文件存储目录下并修改文件的属性（chown）

\# 4） 启动数据库实例，删除远程主机备份过来的数据文件，数据库恢复完成！

\#Usage:   kill mysqld  && restore database backup && start mysql server

\#变量信息

BASE\_DIR=/data       \#数据存储根目录  
   
DATA\_DIR=data3307     \#数据库数据存储目录datadir

DB\_CNF=/etc/my3307.cnf

BAK\_DIR=/data2/backup  \#数据备份根目录

DBUSER='root'

DBPASS='beijing'

DB\_PORT=3307

SOCKET=/var/lib/mysql/mysql3307.sock

BACKUP\_DIR=$BAK\_DIR/mars   \#从远程主机备份的数据存储在此目录下

MYSQL\_CMDDIR=/usr/local/mysql/bin

\#1) Usage: Kill mysql server  首先关闭数据库实例

NUM=\`netstat -an|grep -i listen|grep -o $DB\_PORT|uniq\`

if \[ -z $NUM \] ; then

echo "MySQL server process is end !"

else

$MYSQL\_CMDDIR/mysqladmin -u$DBUSER -p$DBPASS -S $SOCKET shutdown

\#2) 查看上次恢复时datadir的备份（data.old）是否还存在，如果存在则删除;如果不存在data.old的备份，则先对datadir建立备份（data.old）

cd $BASE\_DIR

if \[ -d "$\{DATA\_DIR\}.old" \] ; then  
       
    /bin/rm -rf $\{DATA\_DIR\}.old

/bin/mv $DATA\_DIR $\{DATA\_DIR\}.old  
   
    /bin/mkdir $DATA\_DIR

/bin/chown -R mysql.mysql $DATA\_DIR

else  
    /bin/mv $DATA\_DIR $\{DATA\_DIR\}.old

/bin/mkdir $DATA\_DIR

/bin/chown -R mysql.mysql $DATA\_DIR

\#3) Restore备份到数据文件的目录（datadir）下

/usr/bin/innobackupex --defaults-file=$DB\_CNF --move-back $BACKUP\_DIR >$BAK\_DIR/restore\_db.log 2>&1

\#4) 启动数据库，如果数据库启动正常，删除备份解压后的文件

if \[ "\`tail -1 $BAK\_DIR/restore\_db.log|grep -o 'completed OK'\`" = "completed OK" \]; then

/bin/rm -rf $BACKUP\_DIR/\*

/bin/chown -R mysql.mysql $DATA\_DIR

echo "Database restore is complete is Ok ,Time is: \`date\` " > $BAK\_DIR/info.txt

$MYSQL\_CMDDIR/mysqld\_safe --defaults-file=$DB\_CNF &

\# 数据恢复完成

**3、host163测试机从库恢复脚本**

\[root@host163 backup\]\# cat bk\_restore.sh   
\#!/bin/bash  
\#Name: bk\_restore.sh

\#本脚本完成的任务如下：

\# 1） 关闭数据库实例

\# 2） 删除以前数据库数据文件的备份，并将数据文件的存储目录改名备份，建立新的数据文件存储目录并修改其属性（chown）

\# 3） 通过innobackupex将远程主机备份过来的数据文件move到数据文件存储目录下并修改文件的属性（chown）

\# 4） 启动数据库实例，删除远程主机备份过来的数据文件，数据库恢复完成！

\#Usage:   kill mysqld  && restore database backup && start mysql server

\#变量信息

BASE\_DIR=/data       \#数据存储根目录  
   
DATA\_DIR=data3307     \#数据库数据存储目录datadir

DB\_CNF=/etc/my3307.cnf

BAK\_DIR=/data/backup  \#数据备份根目录

DBUSER='root'

DBPASS='beijing'

DB\_PORT=3307

SOCKET=/tmp/mysql3307.sock

BACKUP\_DIR=$BAK\_DIR/mars   \#从远程主机备份的数据存储在此目录下

BK\_FULL\_DIR=$BAK\_DIR/full

MYSQL\_CMDDIR=/usr/local/mysql/bin

\#解压备份文件

if \[ \`ls $BK\_FULL\_DIR|wc -l\` -gt 0 \];then

cd $BK\_FULL\_DIR

/usr/bin/tar zxvfi \*.tar.gz -C $BACKUP\_DIR

\#Prepare 备份文件

/usr/bin/innobackupex --apply-log $BACKUP\_DIR

\#1) Usage: Kill mysql server  首先关闭数据库实例

NUM=\`netstat -an|grep -i listen|grep -o $DB\_PORT|uniq\`

if \[ -z $NUM \] ; then

echo "MySQL server process is end !"

else

/usr/local/mysql/bin/mysqladmin -u$DBUSER -p$DBPASS -S $SOCKET shutdown

\#2) 查看上次恢复时datadir的备份（data.old）是否还存在，如果存在则删除;如果不存在data.old的备份，则先对datadir建立备份（data.old）

cd $BASE\_DIR

if \[ -d "$DATA\_DIR" \] ; then  
       
    /usr/bin/rm -rf $DATA\_DIR

/usr/bin/mkdir $DATA\_DIR

/usr/bin/chown -R mysql.mysql $DATA\_DIR

else

/usr/bin/mkdir $DATA\_DIR

/usr/bin/chown -R mysql.mysql $DATA\_DIR

\#3) Restore备份到数据文件的目录（datadir）下

/usr/bin/innobackupex --defaults-file=$DB\_CNF --move-back $BACKUP\_DIR >$BAK\_DIR/restore\_db.log 2>&1

\#4) 启动数据库，如果数据库启动正常，删除备份解压后的文件

if \[ "\`tail -1 $BAK\_DIR/restore\_db.log|grep -o 'completed OK'\`" = "completed OK" \]; then

/usr/bin/rm -rf $BACKUP\_DIR/\*

/usr/bin/chown -R mysql.mysql $DATA\_DIR

echo "Database restore is complete is Ok ,Time is: \`date\` " > $BAK\_DIR/info.txt

/usr/local/mysql/bin/mysqld\_safe --defaults-file=$DB\_CNF &

sleep 20

NUM=\`netstat -an|grep -i listen|grep -o $DB\_PORT|uniq\`

if \[ -z $NUM \] ; then

echo "MySQL server process is not start !"

else

$MYSQL\_CMDDIR/mysql -u$DBUSER -p'Dui\_56\#!Dals' -S $SOCKET <<EOF  
use mysql;  
update user set password=password('beijing) where user='mars';  
update user set password=password('beijing') where user='duia';  
update user set password=password('beijing') where user='dmars';  
flush privileges;  
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, ALTER, EXECUTE ON mars.\*  TO 'mars'@'%'  identified by 'beijing';  
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, ALTER, EXECUTE ON english.\* TO 'mars'@'%'  identified by 'beijing';  
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, ALTER, EXECUTE ON agent.\* TO 'mars'@'%'  identified by 'beijing';  
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, ALTER, EXECUTE ON \*.\* TO 'duia'@'%' IDENTIFIED BY  'beijing';  
GRANT USAGE ON \*.\* TO 'dmars'@'%' IDENTIFIED BY 'beijing';  
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON english.\* TO 'dmars'@'%';  
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON agent.\* TO 'dmars'@'%' ;  
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON mars.\* TO 'dmars'@'%' ;  
GRANT USAGE ON \*.\* TO 'denglish'@'%' IDENTIFIED BY PASSWORD '\*B16C1F390339DC9F41F9AF4B75DC56E01BFB';  
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, ALTER, EXECUTE ON english.\* TO 'denglish'@'%';  
update user set password=password('beijing') where user='root';  
flush privileges;  
exit  
EOF

/usr/bin/rm -rf $BK\_FULL\_DIR/\*.tar.gz

**4、更新用户信息脚本**

\[root@host163 backup\]\# cat cr\_user.sh   
\#!/bin/bash

BASE\_DIR=/data       \#数据存储根目录  
   
DATA\_DIR=data3307     \#数据库数据存储目录datadir

DB\_CNF=/etc/my3307.cnf

BAK\_DIR=/data/backup  \#数据备份根目录

DBUSER='root'

DBPASS='cRz7Nvwdr3tWnQ'

DB\_PORT=3307

SOCKET=/tmp/mysql3307.sock

BACKUP\_DIR=$BAK\_DIR/mars   \#从远程主机备份的数据存储在此目录下

BK\_FULL\_DIR=$BAK\_DIR/full

MYSQL\_CMDDIR=/usr/local/mysql/bin

NUM=\`netstat -an|grep -i listen|grep -o $DB\_PORT|uniq\`

if \[ -z $NUM \] ; then

echo "MySQL server process is not start !"

else

$MYSQL\_CMDDIR/mysql -u$DBUSER -p'beijing' -S $SOCKET <<EOF  
use mysql;  
update user set password=password('beijing') where user='mars';  
update user set password=password('beijing') where user='duia';  
update user set password=password('beijing') where user='dmars';  
flush privileges;  
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, ALTER, EXECUTE ON mars.\*  TO 'mars'@'%'  identified by 'beijing';  
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, ALTER, EXECUTE ON english.\* TO 'mars'@'%'  identified by 'beijing';  
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, ALTER, EXECUTE ON agent.\* TO 'mars'@'%'  identified by 'beijing';  
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, ALTER, EXECUTE ON \*.\* TO 'duia'@'%' IDENTIFIED BY  'beijing';  
GRANT USAGE ON \*.\* TO 'dmars'@'%' IDENTIFIED BY 'beijing';  
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON english.\* TO 'dmars'@'%';  
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON agent.\* TO 'dmars'@'%' ;  
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON mars.\* TO 'dmars'@'%' ;  
GRANT USAGE ON \*.\* TO 'denglish'@'%' IDENTIFIED BY PASSWORD '\*B16C1F390339D6CC41F9AF4B75BCDC56E01BFB';  
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, ALTER, EXECUTE ON english.\* TO 'denglish'@'%';  
update user set password=password('beijing') where user='root';  
flush privileges;  
exit  
EOF

/usr/bin/rm -rf $BK\_FULL\_DIR/\*.tar.gz

\# 数据恢复完成