MySQL 完整备份案例及备份恢复脚本

一、MySQL完整备份案例（本机的备份）

1、先创建一个数据库、数据表、写入数据信息

MariaDB [(none)]> create database client;

MariaDB [(none)]> use client

修改配置文件（支持中文）

[root@localhost ~]# vim /etc/my.cnf           

更改的内容：

character_set_server = utf8
collation-server = utf8_general_ci

[client]
default-character-set = utf8

MariaDB [(client)]> show variables like 'character_set_%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

MariaDB [client]> create table user_info(身份证 int(20),姓名 char(20),性别 char(2),用ID号 int(110),资费 int(100));
Query OK, 0 rows affected (0.01 sec)

MariaDB [client]> insert into user_info values('000000001','孙空武','男','011','100');
Query OK, 1 row affected (0.00 sec)

MariaDB [client]> insert into user_info values('000000002','蓝凌','女','012','98');
Query OK, 1 row affected (0.01 sec)

MariaDB [client]> insert into user_info values('000000003','姜纹','女','013','12');
Query OK, 1 row affected (0.01 sec)

MariaDB [client]> insert into user_info values('000000004','关园','男','014','38');
Query OK, 1 row affected (0.01 sec)

MariaDB [client]> insert into user_info values('000000005','罗中昆','男','015','39');
Query OK, 1 row affected (0.01 sec)

MariaDB [client]> select * from user_info;
+-----------+-----------+--------+-------------+--------+
| 身份证 | 姓名 | 性别 | 用户ID号 | 资费 |
+-----------+-----------+--------+-------------+--------+
| 1 | 孙空武 | 男 | 11 | 100 |
| 2 | 蓝凌 | 女 | 12 | 98 |
| 3 | 姜纹 | 女 | 13 | 12 |
| 4 | 关园 | 男 | 14 | 38 |
| 5 | 罗中昆 | 男 | 15 | 39 |
+-----------+-----------+--------+-------------+--------+
5 rows in set (0.00 sec)

2、首先完整备份client.user_info表：

[root@localhost ~]# mysqldump -uroot -p123 client user_info > backup/client.user_info-$(date +%Y%m%d).sql

模拟数据丢失并且恢复数据：

[root@localhost ~]# mysql -uroot -p123 -e 'drop table client.user_info;'
[root@localhost ~]# mysql -uroot -p123 -e 'use client;show tables;'

[root@localhost ~]# mysql -uroot -p123 client < backup/client.user_info-20191013.sql
[root@localhost ~]# mysql -uroot -p123 -e 'use client;show tables;'
+------------------+
| Tables_in_client |
+------------------+
| user_info |
+------------------+

[root@localhost ~]# mysql -uroot -p123 -e 'use client;select * from user_info;'
+-----------+-----------+--------+-------------+--------+
| 身份证 | 姓名 | 性别 | 用户ID号 | 资费 |
+-----------+-----------+--------+-------------+--------+
| 1 | 孙空武 | 男 | 11 | 100 |
| 2 | 蓝凌 | 女 | 12 | 98 |
| 3 | 姜纹 | 女 | 13 | 12 |
| 4 | 关园 | 男 | 14 | 38 |
| 5 | 罗中昆 | 男 | 15 | 39 |
+-----------+-----------+--------+-------------+--------

定期的备份数据：

写一个小脚本：

[root@localhost ~]# vim /opt/client_bak.sh

 里面添加以下内容：

#!/bin/bash
mysqldump -uroot -p123 client user_info > backup/client.user_info-$(date +%Y%m%d).sql

[root@localhost ~]# crontab -e                   //每天的凌晨两点执行这一个脚本
0 2 * * * /opt/client_bak.sh

[root@localhost ~]# systemctl restart crond        //重启一下计划任务

crond的格式：分  时  日 月 周 计划任务

二、跨主机的备份

实验环境：虚拟机ip：192.168.200.111、192.168.200.112

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

112:[root@localhost ~]# yum -y install mariadb                //安装一下mariadb

1、mysql服务端授权给客户端select和lock tables权限，以备份

MariaDB [(none)]> grant select,lock tables on auth.* to 'admin'@'192.168.200.112' identified by '123';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> grant select,lock tables on client.* to 'admin'@'192.168.200.112' identified by '123';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;                          // 刷新授权表
Query OK, 0 rows affected (0.00 sec)

2、连接测试

[root@localhost ~]# mysql -uadmin -p123 -h 192.168.200.111
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 21
Server version: 5.5.41-MariaDB MariaDB Server

Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]>

3、撰写客户端备份脚本：

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

#!/bin/bash
# MySQL数据库备份脚本
# 设置登录变量
MY_USER="admin"
MY_PASS="123"
MY_HOST="192.168.200.111"
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@localhost ~]# chmod +x /opt/bakmysql.sh
[root@localhost ~]# /opt/bakmysql.sh

[root@localhost ~]# ls backup/
auth-20191014-0909.tar.gz client-20191014-0909.tar.gz

添加计划任务：

[root@localhost ~]# crontab -e

0 2 * * * /opt/bakmysql.sh

[root@localhost ~]# systemctl restart crond

4、撰写数据恢复的脚本：

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

#！/bin/bash
# 恢复MySQL数据库数据脚本
# 设置变量

MY_USER="admin"
MY_PASS="123"
MY_HOST="192.168.200.111"
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 20160501 ] && [ $dt -le 20160601 ];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@localhost ~]# chmod +x /opt/restore_mysql.sh

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

111：

MariaDB [(none)]> grant all on auth.* to 'admin'@'192.168.200.112';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant all on client.* to 'admin'@'192.168.200.112';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> drop database client;                     //将client删除再做数据恢复
Query OK, 1 row affected (0.01 sec)