MySQL的全量备份和增量备份以及利用binlog日志实现恢复增量数据;
常用命令:
查看当前写入的binlog日志文件
mysql> show master status;
获取binlog文件列表
mysql> show binary logs;
一、利用binlog日志恢复mysql数据
1.事前准备
开启binlog日志，需要在mysql的主配置文件内的[mysqld]区添加
[root@k8s-node3 ~]# cat /etc/my.cnf
[mysqld]
...
#设置日志三种格式：STATEMENT、ROW、MIXED 。
binlog_format = mixed
#设置日志路径，注意路经需要mysql用户有权限写,这里可以写绝对路径,也可以直接写mysql-bin(后者默认就是在/var/lib/mysql目录下)
log-bin = /usr/local/mysql/logs/mysql-bin.log
#设置binlog清理时间
expire_logs_days = 7
#binlog每个日志文件大小
max_binlog_size = 100m
#binlog缓存大小
binlog_cache_size = 4m
#最大binlog缓存大小
max_binlog_cache_size = 512m
#配置serverid
server-id=1
...
重启mysql并查看binlog是否开启
mysql> show variables like 'log_%';
2.创建两个库，ops和ops1并分别创建表和插入一条数据；
创建库
mysql> create database ops;
mysql> create database ops1;
创建表
mysql> use ops;
mysql> CREATE TABLE IF NOT EXISTS `member` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`name` varchar(16) NOT NULL,`sex` enum('m','w') NOT NULL DEFAULT 'm',`age` tinyint(3) unsigned NOT NULL,`classid` char(6) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
mysql> show tables;
mysql> use ops1;
mysql> CREATE TABLE IF NOT EXISTS `member` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`name` varchar(16) NOT NULL,`sex` enum('m','w') NOT NULL DEFAULT 'm',`age` tinyint(3) unsigned NOT NULL,`classid` char(6) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
mysql> show tables;
插入数据
mysql> use ops;
mysql> insert into member(`name`,`sex`,`age`,`classid`) values('wangshibo','m',27,'cls1'),('guohuihui','w',27,'cls2');
mysql> use ops1;
mysql> insert into member(`name`,`sex`,`age`,`classid`) values('wangshibo','m',27,'cls1'),('guohuihui','w',27,'cls2');
二、场景模拟
1.ops库会在每天凌晨四点进行一次完全备份的定时任务计划，如下：
0 4 * * * /usr/local/mysql/bin/mysqldump -uroot -p123456 -F --databases ops ops1 2>/dev/null | gzip > /data/bak/ops_$(date +%F).sql.gz
-F：执行mysqldump命令时，刷新binlog日志
这里我们手动执行一下，
./mysqldump -uroot -p123456 -F --databases ops ops1 2>/dev/null | gzip > /data/bak/ops_$(date +%F).sql.gz
等到数据库备份完成，就不用担心数据库的丢失了，因为有完全备份数据在，由于上面在全备的时候使用了-F选项，那么当数据备份操作刚开始的时候系统就会自动的刷新log，这样就会自动产生一个新的binlog日志，这个新的binlog日志就会用来记录备份之后的数据库’增删改操作’；
查看一下：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 155 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
也就是说 mysql-bin.000003是用来记录4:00之后对数据库的所有'增删改'操作;
2.早上九点上班了，由于业务的需求会对数据库进行各种'增删改'操作。
比如：在ops库下和ops1库下member表内插入，修改了数据等等；
先是早上进行了插入数据：
mysql> insert into ops.member(`name`,`sex`,`age`,`classid`) values('yiyi','w',20,'cls1'),('xiaoer','m',22,'cls3'),('zhangsan','w',21,'cls5'),('lisi','m',20,'cls4'),('wangwu','w',26,'cls6');
mysql> insert into ops1.member(`name`,`sex`,`age`,`classid`) values('yiyi','w',20,'cls1'),('xiaoer','m',22,'cls3'),('zhangsan','w',21,'cls5'),('lisi','m',20,'cls4'),('wangwu','w',26,'cls6');
mysql> select * from member;
+----+-----------+-----+-----+---------+
| id | name | sex | age | classid |
+----+-----------+-----+-----+---------+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | guohuihui | w | 27 | cls2 |
| 3 | yiyi | w | 20 | cls1 |
| 4 | xiaoer | m | 22 | cls3 |
| 5 | zhangsan | w | 21 | cls5 |
| 6 | lisi | m | 20 | cls4 |
| 7 | wangwu | w | 26 | cls6 |
+----+-----------+-----+-----+---------+
7 rows in set (0.00 sec)
3.中午又执行了修改数据的操作：
mysql> update ops.member set name='李四' where id=4;
mysql> update ops1.member set name='李四' where id=4;
mysql> update ops.member set name='小二' where id=2;
mysql> update ops1.member set name='小二' where id=2;
mysql> select * from member;
+----+-----------+-----+-----+---------+
| id | name | sex | age | classid |
+----+-----------+-----+-----+---------+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | 小二 | w | 27 | cls2 |
| 3 | yiyi | w | 20 | cls1 |
| 4 | 李四 | m | 22 | cls3 |
| 5 | zhangsan | w | 21 | cls5 |
| 6 | lisi | m | 20 | cls4 |
| 7 | wangwu | w | 26 | cls6 |
+----+-----------+-----+-----+---------+
7 rows in set (0.00 sec)
4.在下午18:00的时候，悲剧莫名其妙的出现了！
手贱执行了drop的语句，直接删除了ops1库！
mysql> drop database ops;
mysql> drop database ops1;
再手残又创建了一个数据库ops2并插入了数据
mysql> create database ops2;
mysql> use ops2
mysql> CREATE TABLE IF NOT EXISTS `member` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`name` varchar(16) NOT NULL,`sex` enum('m','w') NOT NULL DEFAULT 'm',`age` tinyint(3) unsigned NOT NULL,`classid` char(6) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
mysql> insert into ops2.member(`name`,`sex`,`age`,`classid`) values('yiyi','w',20,'cls1'),('xiaoer','m',22,'cls3'),('zhangsan','w',21,'cls5'),('lisi','m',20,'cls4'),('wangwu','w',26,'cls6');
5.这种时候一定不要慌，先仔细观察最后一个binlog日志，并记录下关键的pos点，到底是哪个pos点的操作导致了数据库的破坏（通常在最后几步）；
a、先备份一下最后一个binlog日志文件(可以使用show master status;命令来查看现写入的文件)：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 3902 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#备份binlog日志文件
[root@k8s-node3 ~]# cd /usr/local/mysql/logs
[root@k8s-node3 logs]# cp -a mysql-bin.000003 /data/bak/
[root@k8s-node3 logs]# ll /data/bak/
总用量 8
-rw-r----- 1 mysql mysql 3902 11月 24 17:51 mysql-bin.000003
-rw-r--r-- 1 root root 888 11月 24 16:31 ops_2022-11-24.sql.gz
b、接着执行一次刷新日志索引的操作，重新开始新的binlog日志记录文件。按理说mysql-bin.000003这个文件就不会再有后续的写入了，因为便于我们分析原因及查找ops节点，以后所有数据库操作都会写入到下一个日志文件；
mysql> flush logs;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 155 | | | |
+------------------+----------+--------------+------------------+-------------------+
6.读取binlog日志
a、方法一：使用mysqlbinlog读取binlog日志：
[root@k8s-node3 ~]# /usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/logs/mysql-bin.000003
b、登录服务器，并查看（推荐此方法）
mysql> show binlog events in 'mysql-bin.000003';
c、或者
mysql> show binlog events in 'mysql-bin.000003'\G
通过分析，造成ops数据库破坏的pos点区间是介于2513-2614之间（这是按照日志区间的pos节点算的 也就是上图的Pos和End_log_pos的值），造成ops1数据库破坏的pos点区间是介于2691-2795之间，只要恢复到相应的pos点之前就可以了。
7.先把凌晨4点全备的数据恢复（建议另起一个库，等恢复成功后再替换掉当前库即可）
[root@k8s-node3 ~]# cd /data/bak/
[root@k8s-node3 bak]# gzip -d ops_2022-11-24.sql.gz
[root@k8s-node3 bak]# ll
总用量 8
-rw-r----- 1 mysql mysql 3902 11月 24 17:51 mysql-bin.000003
-rw-r--r-- 1 root root 3187 11月 24 16:31 ops_2022-11-24.sql
[root@k8s-node3 bak]# /usr/local/mysql/bin/mysql -uroot -p123456 < ops_2022-11-24.sql
这样就恢复了截至凌晨4点前的备份数据了
mysql> show databases;
mysql> use ops;
mysql> show tables;
+---------------+
| Tables_in_ops |
+---------------+
| member |
+---------------+
1 row in set (0.00 sec)
mysql> select * from member;
+----+-----------+-----+-----+---------+
| id | name | sex | age | classid |
+----+-----------+-----+-----+---------+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | guohuihui | w | 27 | cls2 |
+----+-----------+-----+-----+---------+
2 rows in set (0.00 sec)
但是这仅仅只是恢复了当天凌晨4点之前的数据，在4:00到–18:00之间的数据还没有恢复回来！！这可以根据前面提到的mysql-bin.000003的新binlog日志进行恢复；
8.从binlog日志恢复数据
a、恢复命令的语法格式：
mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名
b、常用参数选项解释：
--start-position=875 起始pos点
--stop-position=954 结束pos点
--start-datetime="2016-9-25 22:01:08" 起始时间点
--stop-datetime="2019-9-25 22:09:46" 结束时间点
--database=ops指定只恢复ops数据库(一台主机上往往有多个数据库，只限本地log日志)
c、不常用选项：
-u --user=name 连接到远程主机的用户名
-p --password[=name]连接到远程主机的密码
-h --host=name 从远程主机上获取binlog日志
--read-from-remote-server从某个Mysql服务器上读取binlog日志
d、小结：实际是将读出的binlog日志内容，通过管道符传递给myslq命令。这些命令，文件尽量写成绝对路径；
e、指定pos结束点恢复（部分恢复）：
也就是还原到删库前的操作，我们的ops和ops1库包括里面的数据都会还原，2513就是删库的Pos点；
[root@k8s-node3 ~]# /usr/local/mysql/bin/mysqlbinlog --no-defaults --stop-position=2513 /usr/local/mysql/logs/mysql-bin.000003 | /usr/local/mysql/bin/mysql -uroot -pEXLztWyG#jj6Kwwy -v
f、指定pos点区间恢复（部分恢复）
在f环节我们已经把ops库恢复到了删库之前的时刻，在删库后我们还做了创建ops2并创建了member表和增加了数据的操作，此时我们要跳过删库并恢复到创建ops2库和创建member表的时候可以采用区间ops点恢复：
/usr/local/mysql/bin/mysqlbinlog --no-defaults --start-position=2614 --stop-position=3419 /usr/local/mysql/logs/mysql-bin.000003 | /usr/local/mysql/bin/mysql -uroot -pEXLztWyG#jj6Kwwy -v
g、此时后面创建ops2库和它的表member恢复回来了，但是ops1库被删除了，因为在这中间有删除ops1库的操作，若想继续恢复后面表中插入的数据，只需要以建表后的Pos点为开始点即可恢复删库之外的所有数据；
/usr/local/mysql/bin/mysqlbinlog --no-defaults --start-position=3419 /usr/local/mysql/logs/mysql-bin.000003 | /usr/local/mysql/bin/mysql -uroot -pEXLztWyG#jj6Kwwy -v
9.指定时间节点区间恢复（部分恢复）：按时间恢复需要mysqlbinlog命令读取binlog日志内容，找时间节点；
a、使用mysqlbinlog命令查看binlog日志
[root@k8s-node3 ~]# /usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/logs/mysql-bin.000003
可以看到这里面的时间点都分别为事件的开始时间和结束时间；
at 2513 – at 2614为 drop database ops的事件
根据上述应还原到删库上一个事件结束的时间 所以就是–stop-datetime为2022-11-24 17:28:07
[root@k8s-node3 ~]# /usr/local/mysql/bin/mysqlbinlog --no-defaults --stop-datetime="2022-11-24 17:28:07" /usr/local/mysql/logs/mysql-bin.000003 | /usr/local/mysql/bin/mysql -uroot -pEXLztWyG#jj6Kwwy -v
此时stopdatetime应该为'drop datebase ops'上一个事件的结束时间，也就是‘drop datebase ops’开始的时间，这样才能将这个时间前的所有数据都恢复；不能写到2022-11-24 17:28:10，否则会更新到drop datebase ops这个操作，其他时间点同此步骤
b、跳过删库环节恢复后面的数据，就是从2022-11-24 17:50:18时间开始恢复；
at 2872 – at 2980为 create database ops2的事件
所以应该从create database ops2的事件开始执行时间恢复，所以–start-datetime也就是2022-11-24 17:50:01
/usr/local/mysql/bin/mysqlbinlog --no-defaults --start-datetime="2022-11-24 17:50:01" /usr/local/mysql/logs/mysql-bin.000003 | /usr/local/mysql/bin/mysql -uroot -pEXLztWyG#jj6Kwwy -v
三、全量备份脚本
[root@k8s-node3 ~]# vim /hqtbj/hqtwww/Script/mysql/mysql_bak_full.sh
#!/bin/bash
#mysqldump.sh
#Date: 2022-05-17
#Author: fandaoshuai
#Function: mysql数据库全量备份,并保留一个月；一个月之后再清理；
DB_User='xxx' #数据库用户
DB_Passwd='xxxx' #数据库密码
DATE=`date -d "now" +%Y-%m-%d` #显示当前的时间
Old_DATE=`date -d "-1 month" +%Y-%m-%d` #显示一个月前的时间
Bak_Dir=/hqtbj/hqtwww/data/mysql-bak/full/$DATE #备份目录
Old_Bak_Dir=/hqtbj/hqtwww/data/mysql-bak/full/$Old_DATE #一个月前的备份目录
echo $DATE"备份开始"
##创建目录##
if [ ! -d "$Bak_Dir" ];then
mkdir -p $Bak_Dir
echo "备份目录不存在，已创建;"
fi
##开始备份##
echo "开始全量备份"
BackupName=FULL-$DATE.sql.gz #备份完成之后的名字
/usr/local/mysql/bin/mysqldump -h127.0.0.1 -P3306 -u${DB_User} -p${DB_Passwd} --all-databases 2>/dev/null | gzip > $Bak_Dir/$BackupName
if [ $? -eq 0 ];then
echo "数据库全量备份已完成："$Bak_Dir/$BackupName
else
echo $BackupName"-数据库全量备份失败"
fi
##清理一个月前的备份文件##
echo "开始清理一个月前的备份文件-备份目录为：" $Old_Bak_Dir
if [ -d "$Old_Bak_Dir" ];then
rm -rf $Old_Bak_Dir
echo "一个月前的备份文件已被清理;"
fi
四、增量备份脚本
[root@k8s-node3 ~]# vim /hqtbj/hqtwww/Script/mysql/mysql_bak_incremental.sh
#!/bin/bash
#mysqldump.sh
#Date: 2022-05-17
#Author: fandaoshuai
#Function: mysql数据库增量备份,并保留一个月；一个月之后再清理；
DB_User='xxx' #数据库用户
DB_Passwd='xxxx' #数据库密码
DATE=`date -d "now" +%Y-%m-%d` #显示当前的时间
Old_DATE=`date -d "-1 month" +%Y-%m-%d` #显示一个月前的时间
Bak_Dir=/hqtbj/hqtwww/data/mysql-bak/incremental/$DATE #备份目录
Old_Bak_Dir=/hqtbj/hqtwww/data/mysql-bak/incremental/$Old_DATE #一个月前的备份目录
Binlog_Dir=/usr/local/mysql/logs #binlog的日志目录
Binlog_File=$Binlog_Dir/mysql-bin.index #binlog日志的索引文件
echo $DATE"备份开始"
/usr/local/mysql/bin/mysqladmin -h127.0.0.1 -P3306 -u${DB_User} -p${DB_Passwd} flush-log #刷新binlog日志文件
Counter=`wc -l $Binlog_File | awk '{print $1}'`
NextNum=0
##创建目录##
if [ ! -d "$Bak_Dir" ];then
mkdir -p $Bak_Dir
echo "备份目录不存在，已创建;"
fi
##开始备份##
for file in `cat $Binlog_File`
do
Base=`basename $file`
NextNum=`expr $NextNum + 1`
if [ $NextNum -eq $Counter ]
then
echo $Base "新日志文件已经跳过"
else
dest=$Bak_Dir/$Base
if (test -e $dest)
then
echo $Base"备份文件已存在"
else
echo "拷贝备份文件"$Base"至备份目录"
cp $Binlog_Dir/$Base $Bak_Dir
echo "增量备份文件"$Base"成功"
fi
fi
done
##清理一个月前的备份文件##
echo "开始清理一个月前的备份文件-备份目录为：" $Old_Bak_Dir
if [ -d "$Old_Bak_Dir" ];then
rm -rf $Old_Bak_Dir
echo "一个月前的备份文件已被清理;"
fi
五、定时任务
一天一小备（增量备份），七天一大备（全量备份）
https://tool.lu/crontab/
[root@k8s-node3 ~]# crontab -e
0 4 */7 * * sh /hqtbj/hqtwww/Script/mysql/mysql_bak_full.sh 1>>/hqtbj/hqtwww/Script/mysql/logs/mysql_full.log 2>&1
0 4 * * * sh /hqtbj/hqtwww/Script/mysql/mysql_bak_incremental.sh 1>>/hqtbj/hqtwww/Script/mysql/logs/mysql_incremental.log 2>&1
备份效果
#全量备份
[root@k8s-node3 mysql]# ./mysql_bak_full.sh
2022-12-08备份开始
备份目录不存在，已创建;
开始全量备份
数据库全量备份已完成：/hqtbj/hqtwww/data/mysql-bak/full/2022-12-08/FULL-2022-12-08.sql.gz
开始清理一个月前的备份文件-备份目录为： /hqtbj/hqtwww/data/mysql-bak/full/2022-11-08
[root@k8s-node3 ~]# ll -lrth /hqtbj/hqtwww/data/mysql-bak/full/2022-12-08/
总用量 1.1G
-rw-r--r-- 1 root root 1.1G 12月 8 03:04 FULL-2022-12-08.sql.gz
#增量备份
[root@k8s-node3 mysql]# ./mysql_bak_incremental.sh
2022-12-08备份开始
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
备份目录不存在，已创建;
拷贝备份文件mysql-bin.000021至备份目录
增量备份文件mysql-bin.000021成功
拷贝备份文件mysql-bin.000022至备份目录
增量备份文件mysql-bin.000022成功
拷贝备份文件mysql-bin.000023至备份目录
增量备份文件mysql-bin.000023成功
拷贝备份文件mysql-bin.000024至备份目录
增量备份文件mysql-bin.000024成功
拷贝备份文件mysql-bin.000025至备份目录
增量备份文件mysql-bin.000025成功
mysql-bin.000026 新日志文件已经跳过
开始清理一个月前的备份文件-备份目录为： /hqtbj/hqtwww/data/mysql-bak/incremental/2022-11-08
[root@k8s-node3 ~]# ll -lrth /hqtbj/hqtwww/data/mysql-bak/incremental/2022-12-08/
-rw-r----- 1 root root 202 12月 8 04:00 mysql-bin.000021
-rw-r----- 1 root root 202 12月 8 04:00 mysql-bin.000022
-rw-r----- 1 root root 202 12月 8 04:00 mysql-bin.000023
-rw-r----- 1 root root 202 12月 8 04:00 mysql-bin.000024
-rw-r----- 1 root root 202 12月 8 04:00 mysql-bin.000025
479
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
