mysql数据库备份脚本指定表_MySQL数据库备份脚本

MySQL数据库备份脚本

实验环境：跨主机操作

mysql-server：192.168.200.101

mysql-client：192.168.200.102     两台机器都安装mariadb

实验要求：对mysql-server的auth库和client库实现异地备份，每天凌晨2:00进行备份，撰写一个数据恢复脚本。

MySQL服务端授权，给予select和lock tables(锁表)权限，以备份

[root@localhost ~]# mysql -uroot -p123456

mysql> grant select,lock tables on auth.* to 'admin'@'192.168.200.102' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> grant select,lock tables on client.* to 'admin'@'192.168.200.102' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

客户端安装客户端软件

[root@client ~]# yum -y install mysql 或者安装mariadb

连接测试

[root@client ~]# mysql -uadmin -p -h192.168.200.101

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| auth               |

| client             |

| test               |

+--------------------+

4 rows in set (0.00 sec)

撰写客户端备份脚本

[root@client ~]# vim /opt/bakmysql.sh

#!/bin/bash

# MySQL数据库备份脚本

#设置登录变量

MY_USER="admin"

MY_PASS="123456"

MY_HOST="192.168.200.101"

MY_CONN="-u$MY_USER -p$MY_PASS -h$MY_HOST"

#设置备份的数据库

MY_DB1="auth"

MY_DB2="client"

#定义备份路径、工具、时间、文件名

BF_DIR="backup"

BF_CMD="/usr/bin/mysqldump"

BF_TIME=$(date +%Y%m%d-%H%M)

NAME_1="$MY_DB1-$BF_TIME"

NAME_2="$MY_DB2-$BF_TIME"

#备份为.sql脚本，然后打包压缩(打包后删除原文件)

[ -d $BF_DIR ] || mkdir -p $BF_DIR

cd $BF_DIR

$BF_CMD $MY_CONN --databases $MY_DB1 > $NAME_1.sql

$BF_CMD $MY_CONN --databases $MY_DB2 > $NAME_2.sql

/bin/tar zcf $NAME_1.tar.gz $NAME_1.sql --remove &>/dev/null

/bin/tar zcf $NAME_2.tar.gz $NAME_2.sql --remove &>/dev/null

[root@client ~]# chmod +x /opt/bakmysql.sh

[root@client ~]# /opt/bakmysql.sh

[root@client ~]# ls backup/

auth-20160505-1805.tar.gz  client-20160505-1805.tar.gz

[root@client ~]# tar tvf backup/auth-20160505-1805.tar.gz

-rw-r--r-- root/root      1967 2016-05-05 18:05 auth-20160505-1805.sql

[root@client ~]# tar tvf backup/client-20160505-1805.tar.gz

-rw-r--r-- root/root      2250 2016-05-05 18:05 client-20160505-1805.sql

[root@client ~]# crontab -e

02***/opt/bakmysql.sh

执行后重启crontab

改变系统时间，执行任务计划，模拟每天的备份，为之后的恢复脚本做准备

[root@client ~]# date 050601592016.59//MMDDhhmmYY.SS月日小时分钟.秒

2016年05月06日星期五01:59:59 CST

[root@client ~]# ls backup/

auth-20160505-1805.tar.gz  client-20160505-1805.tar.gz

auth-20160506-0200.tar.gz  client-20160506-0200.tar.gz

[root@client ~]# date 050701592016.59

2016年05月07日星期六01:59:59 CST

[root@client ~]# date 050801592016.59

2016年05月08日星期日01:59:59 CST

[root@client ~]# ls backup/

auth-20160505-1805.tar.gz  auth-20160508-0200.tar.gz    client-20160507-0200.tar.gz

auth-20160506-0200.tar.gz  client-20160505-1805.tar.gz  client-20160508-0200.tar.gz

auth-20160507-0200.tar.gz  client-20160506-0200.tar.gz

撰写数据恢复脚本

[root@client ~]# vim /opt/restore_mysql.sh

#!/bin/bash

#恢复MySQL数据库数据脚本

#设置变量

MY_USER="admin"

MY_PASS="123456"

MY_HOST="192.168.200.101"

BF_DIR="backup"

mkdir .aaa     //创建隐藏目录

ls $BF_DIR |column -t > .aaa/db_list        //竖项显示

awk -F'-' '{print $2}' .aaa/db_list > .aaa/dt.txt

read -p "请指定要恢复数据库的日期(YYYYMMDD):" dt

if [ $dt -ge 20191130 ] && [ $dt -le 20191201 ];then

grep "$dt" .aaa/dt.txt &>/dev/null

if [ $? -ne 0 ];then

echo "很抱歉,您恢复数据库的备份日期不再备份日期范围内"

else

echo "搜索到的可恢复数据库如下:"

awk -F'-' /$dt/'{print NR,$1}' .aaa/db_list

read -p "请选择您要恢复数据库的编号: " nb

nm=$(awk -F'-' /$dt/'{print NR,$1}' .aaa/db_list |awk /$nb/'{print $2}')

echo "现在开始恢复数据库:$nm到$dt"

cd $BF_DIR

onm=$(ls |grep "$nm-$dt")

mkdir .bbb

tar xf $onm -C .bbb

mysql -u$MY_USER -p$MY_PASS -h$MY_HOST  < .bbb/*

echo "$nm已经恢复到$dt"

rm -rf .bbb

cd - &>/dev/null

rm -rf .aaa

fi

else

echo "很抱歉,您恢复数据库的备份日期不再备份日期范围内"

fi

[root@client ~]# chmod +x /opt/restore_mysql.sh

如在客户端恢复数据，需要开放权限

mysql> grant all on auth.* to 'admin'@'192.168.200.102';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all on client.* to 'admin'@'192.168.200.102';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

恢复测试：

[root@client ~]# /opt/restore_mysql.sh

请指定要恢复数据库的日期(YYYYMMDD):20160507

搜索到的可恢复数据库如下:

3 auth

7 client

请选择您要恢复数据库的编号: 3

现在开始恢复数据库:auth到20160507

auth已经恢复到20160507

[root@client ~]# /opt/restore_mysql.sh

请指定要恢复数据库的日期(YYYYMMDD):20100101

很抱歉,您恢复数据库的备份日期不再备份日期范围内

bash -X 脚本名  查看执行过程