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

最新推荐文章于 2022-07-14 10:31:11 发布

客居天涯

最新推荐文章于 2022-07-14 10:31:11 发布

阅读量778

点赞数

分类专栏：数据库技术

本文链接：https://blog.csdn.net/lqx0405/article/details/83999890

版权

数据库技术专栏收录该内容

479 篇文章 3 订阅

订阅专栏

案例说明：

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