【Mysql】mysql备份shell脚本,备份和恢复策略菜鸟版【mysql备份】2021-3-7

前言

---

熟练掌握mysql备份会恢复是一个菜鸟运维必备技能，是避免有意无意删库进宫吃牢饭的有效手段。本文将从设计一个简单的备份和恢复策略，希望关键的时候能救人一命。

---

备份常见的有物理备份和逻辑备份，对于他们的具体的区别，我这里不会赘述。本次采用的备份策略是逻辑备份，采用周循坏备份模拟公司备份。

一、 场景设计

---

1.每周一到周六凌晨四点做增量备份，增量备份是基于上一次备份而做的，只包含变化的数据集，一般情况不会比原始数据量大，所以可以减少服务器的开销、备份时间、备份空间。每周日凌晨四点做一次全量备份。

2.保存一周的备份，删除旧的备份，节省空间。

二、技术介绍

说明

本次的备份策略适用中小型数据库。

---

1.mysqldump

对于做全量备份最常用的逻辑备份工具 mysqldump 和物理备份工具 xtrabackup。本文通过使用mysqldump完成全备,曾备通过mysqlbinlog实现。mysqldump：基于MySQL客户端的一个逻辑备份工具，可实现温备，可以使用-u，-p，-h等选项备份远程数据库上的数据

---

2.mysqlbinlog

binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志。
有了binlog日志就可以肆意妄为的删库了，如果你保留了一个月内的binlog日志，以为着你可以恢复这一个月内任意一个时间点的数据，所以开启和备份binlog日志很重要。

---

3.计划任务

通过centos自带的定时计划任务可以实现某一时刻备份mysql库。

---

三、开始实施备份策略

说明：

本次备份策略基于mysql5.7实现，自行安装好，了解自己安装mysql的位置，数据存放的位置。

1.建一个目录用来存放binlog日志

[root@masert ~]# mkdir -p /data/binlog/mysql-bin
[root@masert ~]# chown -R mysql.mysql /data/binlog/

2.修改mysql配置文件，开启binlog功能

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

（1）添加下面三条配置，保存退出

server_id=7
#mysql5.6可以不设置，mysql5.7必须设置，值随便设，保证唯一性不冲突就行

log_bin=/data/binlog/mysql-bin
#日志位置和日志名前缀。这样设置之后，binlog日志文件实际存储的位置是在/data/binlog/之下，而mysql-bin这个目录是生成binlog日志文件名的前缀。

sync_binlog=1
#binlog日志刷盘的策略，每次事务提立刻刷写到磁盘

binlog_format=row
#binlog的日志记录格式为row模式

3.重启mysql服务

[root@masert ~]# /usr/local/mysql/support-files/mysql.server restart

4.登录mysql（我的mysql无密码登录）

[root@masert ~]# mysql -uroot

5.建库建表，插入数据，准备好全备前的数据。

mysql> create database school;
Query OK, 1 row affected (0.01 sec)
mysql> use school
Database changed
mysql> create table student(id int primary key not null, name 		varchar(10), age int);
Query OK, 0 rows affected (0.03 sec)
   mysql> insert into student value(1,'xiaodong',18),(2,'wangqiang',20),(3,'laowang',19);
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | xiaodong  |   18 |
|  2 | wangqiang |   20 |
|  3 | laowang   |   19 |
+----+-----------+------+
3 rows in set (0.00 sec)

6.现在是周日凌晨四点，做全备

（1）退出mysql，用mysqldump完成全备。

[root@masert ~]# mysqldump -uroot  -A  -R -E -- triggers --master-data=2 --single-transaction --max_allowed_packet=64M   >/data/full.sql
[root@masert ~]# ll -h
总用量 836K
-rw-------. 1 root root 1.4K 1月   5 03:00 anaconda-ks.cfg
-rw-r--r--  1 root root 832K 3月   7 17:48 full.sql
drwxr-xr-x  2 root root  133 3月   7 17:21 test

7.现在是周一凌晨四点，做增备

（1）这是第一次增备，数据是基于周日做的全备后到周一凌晨四点。假设第一次增备建一个表以及插入了一些数据。

mysql> use school;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table zhou1(id int,note varchar(20));
insert into school.zhou1 value(1,'one day');

（2）截取日志，增量备份

查看最新一个binlog日志文件名称和Position

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000005 |     1339 |              |                  | e2984408-7d89-11eb-bc0a-000c29407e0e:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000005';

由图中可以看出第一次增量备份的起始点。

（3）使用mysqlbinlog做增量备份

[root@masert /data]# mysqlbinlog --start-position=953 --stop-position=1339  /data/binlog/mysql-bin.000005 > /data/inc1.sql 

8.现在是周二凌晨四点，做增备

（1）这是第二次增备，数据是基于周一做的增备后到周二凌晨四点。第二次增备做了的事：建一个表以及插入了一些数据。

mysql> use school
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table zhou2(id int,note varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into zhou2 value(2,'2.day');
Query OK, 1 row affected (0.00 sec)

（2）截取日志，增量备份

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000005 |     1794 |              |                  | e2984408-7d89-11eb-bc0a-000c29407e0e:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000005';

由图中可以看出第二次增量备份的起始点。

（3）使用mysqlbinlog做增量备份

[root@masert /data]# mysqlbinlog --start-position=1404 --stop-position=1794  /data/binlog/mysql-bin.000005 > /data/inc2.sql 

剩下的周三周四周五周六增备，周日全备都类似上面的做法。

四.模拟故障恢复策略

1.假设周二增备之后，周三的新增的数据只有数据库增加多一张zhou3表，zhang3表插了一些数据。

mysql> use school
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> create table zhou3(in int,note varchar(20));
mysql> insert into zhou3 value(3,'NO.3-day');
Query OK, 1 row affected (0.01 sec)

2.人为故障

这个时候不知道哪个吊毛删库了，准备进行恢复。

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
| zhou1            |
| zhou2            |
| zhou3            |
+------------------+
4 rows in set (0.00 sec)

mysql> drop database school;

school直接被删除了，别慌。

3.恢复

第一时间维护，告诉上级

（1）进入数据库，关掉binlog

mysql>set sql_log_bin=0; 暂时关闭

（2）恢复上周三全备，直接在数据库导入备份数据

mysql> source /data/full.sql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> use school
Database changed
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+
1 row in set (0.00 sec)
mysql> select * from student;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | xiaodong  |   18 |
|  2 | wangqiang |   20 |
|  3 | laowang   |   19 |
+----+-----------+------+
3 rows in set (0.00 sec)

可以看到上一次全备的数据回来了

（3）恢复周一增备的数据

mysql>mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
| zhou1            |
+------------------+
2 rows in set (0.00 sec)
 mysql> source /data/inc1.sql
 mysql> select * from zhou1;
+------+---------+
| id   | note    |
+------+---------+
|    1 | one day |
+------+---------+
1 row in set (0.00 sec)

周一增备的回来了

（4）恢复周二增备的数据

mysql>source /data/inc2.sql
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
| zhou1            |
| zhou2            |
+------------------+
3 rows in set (0.00 sec)

mysql> select * from zhou2;
+------+-------+
| id   | note  |
+------+-------+
|    2 | 2.day |
+------+-------+
1 row in set (0.00 sec)	

周二做的增备的数据也回来了

（5）周三因为删库了，没有做增备，通过binlog日志截取恢复，省略了，没有可做的。

五.shell脚本完成备份策略。恢复策略

1.周日全备脚本

#!/bin/bash
bkDir=/data/backup/ #备份存放目录
Date=`date +%Y%m%d` #用日期备份文件名字
cd $bkDir
DumpFile=$Date.full.sql
GZDumpFile=$Date.full_sql.tar.gz
mysqldump -uroot -p  -A  -R -E --triggers --master-data=2 --single-transaction --max_allowed_packet=64M > $DumpFile
if [ $? -eq 0 ]
then
echo "完全备份成功！生成的mysql完全备份的压缩文件$GZDumpFile存放在:$bkDir目录下"
else
echo "生成完全备份失败！"
fi
rm -r /data/backup/inc/*
mysql -e "flush logs;"
tar -zvcf $GZDumpFile $DumpFile 1> /dev/null 2> /dev/null
rm -f  $DumpFile
last_week_Date=`date -d '7 days ago' +%Y%m%d`
last_week_DateFile=${last_week_Date}".full_sql.tar.gz"
rm $last_week_DateFile 1> /dev/null 2> /dev/null
if [ $? -eq 0 ] 
then
echo "删除$last_week_DateFile成功！"
else
echo "没有七天前的完全备份文件可删除！"
fi

2.周一到周六增备脚本

#!/bin/bash
idxdir=/data/binlog/mysql-bin.index
bindir=/data/binlog/
incdir=/data/backup/inc/
today=`date +%Y%m%d`
wk_num=`date +%w`
case $wk_num in 
  "0")
      week=日 ;;
  "1")      
      week=一 ;;
  "2")      
      week=二 ;;
  "3")
      week=三 ;;
  "4")
      week=四 ;;
  "5")
      week=五 ;;
  "6")
      week=六 ;;
  * ) 
      echo "获取失败！" ;;
esac
mysql -e "flush logs;"
num=`wc -l $idxdir | awk '{print $1}'`
count=0
for file in `cat $idxdir`
do
base=`basename $file`
count=`expr $count + 1`
target=`expr $num - 1`
if [ $target -eq  $count ]
then
cp ${bindir}${base} ${incdir}inc_[${today}_星期${week}].${base}
fi
done

3.设置计划任务crontab，定时执行脚步备份

（1）安装crontab

[root@master /data/binlog]# yum install crontabs

（2）设置定时执行脚本

[root@master /data/binlog]# crontab -e 

输入以下内容并保存退出：

0 4 * * 0 sh /data/binlog/full_bk.sh
0 4 * * 1-6 sh /data/binlog/inc.sh

（3）启动crontab

[root@master /data/binlog]# systemctl start crond.service 
[root@master /data/binlog]# systemctl status crond.service 
● crond.service - Command Scheduler
   Loaded: loaded (/usr/lib/systemd/system/crond.service; enabled; vendor preset: enabled)
   Active: active (running) since 一 2021-03-08 18:56:43 CST; 3min 1s ago
 Main PID: 1279 (crond)
   CGroup: /system.slice/crond.service
           └─1279 /usr/sbin/crond -n

3月 08 18:56:43 master systemd[1]: Started Command Scheduler.
3月 08 18:56:43 master crond[1279]: (CRON) INFO (Syslog will be used instead of sendmail.)
3月 08 18:56:43 master crond[1279]: (CRON) INFO (RANDOM_DELAY will be scaled with factor 35% if used.)
3月 08 18:56:43 master crond[1279]: (CRON) INFO (running with inotify support)
3月 08 18:56:43 master crond[1279]: (CRON) INFO (@reboot jobs will be run at computer's startup.)

4.恢复策略

前言：我们假设在周三上午九点，发现有人误删了所有数据库。这个时候，告知上级情况，业务需要暂停，业务界面挂维护公告。开始恢复数据库。
恢复思路：
【1】、登录mysql,关掉binlog日志，不让恢复过程写入binlog日志中，方便恢复近期数据
【2】、解压全备压缩文件，获取上周日完全备份的所有数据。
【3】、通过mysqlbinlog命令，提取获取本周每天的增量备份。
【4】、因为删库发生在周三，找到周三生成的binlog日志。使用mysqlbinlog截取周三凌晨四点增量备份后的第一个pos点到最新数据变化的pos点之间的数据文件，然后生成.sql文件，去掉里面的删库语句。
【5】、总体是先导入全备sql数据，然后导入周一增备，周二增备，周三增备的sql数据(周三凌晨四点的增备就是周二凌晨四点到周三凌晨四点的二进制日志)，最后导入第四点说的数据。

（1）产生模拟数据

周日全备的内容是建一个test库，有一个data表，里面有如下数据。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> create table data (note varchar(30));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into data value ('this is full_bk');
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test.data;
+-----------------+
| note            |
+-----------------+
| this is full_bk |
+-----------------+
1 row in set (0.00 sec)
mysql>exit

这个时候退出mysql，执行全备脚本，模拟周日凌晨四点进行全备。
[root@master /data/binlog]# ls
full_bk.sh  inc.sh  mysql-bin  mysql-bin.000001  mysql-bin.000002  mysql-bin.index  test.sh
[root@master /data/binlog]# ./full_bk.sh 
Enter password: 
完全备份成功！生成的mysql完全备份的压缩文件20210308.full_sql.tar.gz存放在:/data/backup/目录下
没有七天前的完全备份文件可删除！

周一增备的内容只是往data表里插入了一条数据：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test.data value('this is Monday_inc_bk');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from test.data;
+-----------------------+
| note                  |
+-----------------------+
| this is full_bk       |
| this is Monday_inc_bk |
+-----------------------+
2 rows in set (0.00 sec)

mysql> exit
Bye

执行增备脚本，模拟周一凌晨四点增备操作。
[root@master /data/binlog]# ls
full_bk.sh  inc.sh  mysql-bin  mysql-bin.000001  mysql-bin.000002  mysql-bin.000003  mysql-bin.index  test.sh
[root@master /data/binlog]# ./inc.sh 
[root@master /data/binlog]# ls /data/backup/inc/
inc_[20210308_星期一].mysql-bin.000002

周二增备的内容只是往data表里插入了一条数据：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test.data value('this is Tuesday_inc_bk');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test.data;
+------------------------+
| note                   |
+------------------------+
| this is full_bk        |
| this is Monday_inc_bk  |
| this is Tuesday_inc_bk |
+------------------------+
3 rows in set (0.00 sec)

mysql> exit;
Bye

执行增备脚本，模拟周二凌晨四点增备操作。
[root@master /data/binlog]# ls
full_bk.sh  inc.sh  mysql-bin  mysql-bin.000001  mysql-bin.000002  mysql-bin.000003  mysql-bin.000004  mysql-bin.index  test.sh
[root@master /data/binlog]# ./inc.sh 
[root@master /data/binlog]# ls /data/backup/inc/
inc_[20210309_星期二].mysql-bin.000003   inc_[20210308_星期一].mysql-bin.000002

周三增备的内容只是往data表里插入了一条数据：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test.data value("this is Wednesday_inc_bk!");
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test.data;
+---------------------------+
| note                      |
+---------------------------+
| this is full_bk           |
| this is Monday_inc_bk     |
| this is Tuesday_inc_bk    |
| this is Wednesday_inc_bk! |
+---------------------------+
4 rows in set (0.00 sec)

执行增备脚本，模拟周三凌晨四点增备操作。
[root@master /data/binlog]# ls
full_bk.sh  inc.sh  mysql-bin  mysql-bin.000001  mysql-bin.000002  mysql-bin.000003  mysql-bin.000004  mysql-bin.000005  mysql-bin.index  test.sh
[root@master /data/binlog]# ./inc.sh
[root@master /data/binlog]# ls /data/backup/inc/
inc_[20210309_星期二].mysql-bin.000003  inc_[20210310_星期三].mysql-bin.000004  inc_[20210308_星期一].mysql-bin.000002

假设周三增备到周三上午九点删库前的数据只有这么一条数据：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)


mysql> insert into test.data value('drop database,Wednesday_data');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test.data;
+------------------------------+
| note                         |
+------------------------------+
| this is full_bk              |
| this is Monday_inc_bk        |
| this is Tuesday_inc_bk       |
| this is Wednesday_inc_bk!    |
| drop database,Wednesday_data |
+------------------------------+
5 rows in set (0.00 sec)
mysql> exit

现在是周三上午九点，这个时候有个吊毛来删库！！！

mysql> drop database test;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

test库已经没了

（2）恢复数据

恢复全备

[root@master /data/backup]# ls
20210309.full_sql.tar.gz  inc
[root@master /data/backup]# tar zxvf  20210309.full_sql.tar.gz 
20210309.full.sql
[root@master /data/backup]# ls
20210309.full.sql  20210309.full_sql.tar.gz  inc

登录mysql
先暂时关闭binlog日志。
mysql> set sql_log_bin=0;
mysql> source /data/backup/20210309.full.sql

恢复增备
最简单的办法就是把每天的增备和删库那天的binlog日志一起导出成.sql，然后删除导出sql文件中的删库语句。

[root@master /data/backup/inc]# mysqlbinlog inc_\[20210308_星期一\].mysql-bin.000002 inc_\[20210309_星期二\].mysql-bin.000003 inc_\[20210310_星期三\].mysql-bin.000004 mysql-bin.000005 > inc.sql

上面的mysql-bin.000005日志是删库那天的binlog日志，自己手动拷贝过去。

得出的sql文件，去掉其中的恶意删库语句；

[root@master /data/backup/inc]# grep drop inc.sql 
drop database test
[root@master /data/backup/inc]# sed -i -e "/drop database test/d"  inc.sql 

开始恢复增备
mysql> source /data/backup/inc/inc.sql

查看恢复情况：mysql> use test

Database changed
mysql> select * from data;
+------------------------------+
| note                         |
+------------------------------+
| this is full_bk              |
| this is Monday_inc_bk        |
| this is Tuesday_inc_bk       |
| this is Wednesday_inc_bk!    |
| drop database,Wednesday_data |
+------------------------------+
5 rows in set (0.00 sec)

数据回来了！

五.总结

本文主要通过简单的案例，来演示日常备份策略，当数据库数据丢失时，提出了一个简单的恢复策略，方便菜鸟学习mysql备份和恢复。当然恢复的方法还有很多，如果你的数据量大的时候，建议采用xbk工具进行物理备份的方式。本文涉及了mysqldump，binlog日志恢复的方式。此外还有基于GTID与binlog日志的方式进行的备份和恢复策略，这种方式更适合跨binlog日志文件恢复数据。总之在日常工作中尽量避免删库的发生，同时做好备份的工作。