MySQL增量备份和全量备份

1 全量备份

1.1 创建用于备份的目录

mkdir /root/mysql.backup

1.2 创建存入备份文件的目录

mkdir /root/mysql.backup/data

1.3 进入备份目录，创建备份脚本

cd /root/mysql.backup

vim mysqlBackuoShell.sh

#!/bin/bash 
   
#保存备份个数,31条 
number=31 
#备份保存路径 
backup_dir=/root/mysql.backup/data 
#日期 
dd=`date +%Y-%m-%d_%H_%M_%S`
#备份工具 
tool=mysqldump 
#用户名 
username=root 
#密码 
password=Abc123456.
#将要备份的数据库 
database_name=test
   
#运行内容,结果用gzip压缩下
$tool -u $username -p$password --single-transaction --master-data=2 --flush-logs -B $database_name|gzip > $backup_dir/$database_name-$dd.sql.gz
   
#写创建备份日志 
echo "create $backup_dir/$database_name-$dd.sql.gz" >> $backup_dir/log.txt 
   
#找出需要删除的备份 
delfile=`ls -l -crt  $backup_dir/*.sql.gz | awk '{print $9 }' | head -1` 
   
#判断现在的备份数量是否大于$number 
count=`ls -l -crt  $backup_dir/*.sql.gz | awk '{print $9 }' | wc -l` 
   
if [ $count -gt $number ] 
then 
  rm $delfile  //删除最早生成的备份，只保留number数量的备份 
  #写删除文件日志 
  echo "delete $delfile" >> $backup_dir/log.txt 
fi

1.4 给脚本赋予执行权限

chmod +x  mysqlBackuoShell.sh

1.5 创建定时脚本

vim mysqlRollBack.cron

内容如下

# 每天凌晨三点进行一次全量备份
0 3 * * * /data/mysqlbackup/mysqlBackuoShell.sh

1.6 启动定时任务

crontab mysqlRollBack.cron

1.7 查看定时任务是否成功

crontab -l

1.8 删除定时任务

如果不想执行定时任务,直接删除就行了

crontab -r

1.9 编辑定时任务

如果想要修改执行时间,可以编辑

crontab -e

2 增量备份

2.1 查看是否开启binlog日志

show variables like '%log_bin%';

2.2 如果没开启，则修改配置文件

vim /etc/my.cnf

#节点Id,注意集群中不能重复,单节点不配置也可以
server-id=123
#开启binlog日志,指定其存放位置
log-bin=/var/lib/mysql/mysql-bin
#开启binlog自动过期
expire_logs_days=10

2.3 重启MysQL服务

service mysqld restart

2.4 查看binlog日志是否成功开启

show variables like '%log_bin%';

3 模拟删库恢复

3.1 先查询当前所有的二进制日志

show master logs;

3.2 查看当前数据库中user表中的数据

3.3 手动执行一次全量备份

./mysqlBackuoShell.sh

3.4 查看是否备份成功

3.5 再次查询当前所有的二进制日志

这时查看多出了一条mysql-bin.000019,说明刚才全量备份后，重新建立了二进制文件，备份后的数据修改操作日志只会存入最新的这条二进制文件中

show master logs;

3.5 对数据库进行修改操作

向user表中新增一条数据（name=王五），此时新增这条数据的操作日志会存入最新的那条二进制文件中（mysql-bin.000019）

3.6 删除数据库

此时删库的操作也将存入最新的二进制文件中（mysql-bin.000019）

此时MySQL服务中已经没有test这个数据库

3.7 恢复操作

1.首先刷新二进制日志,此时会新建一条新的二进制文件（mysql-bin.000020），之后所有操作都将存入这条最新的二进制文件中,接下来我们只需要分析mysql-bin.000019这个二进制文件删库的位置

flush logs;

2.分析二进制文件 mysql-bin.000019，查看删库的日志位置

show binlog events in 'mysql-bin.000019';

发现删库操作在621这个位置，所以我们只需要把数据恢复621之前的位置（485）就可以了
3. 先进行全量恢复
进入存放备份文件的目录

cd /root/mysql.backup/data/

解压备份文件

gzip -d 备份文件

执行恢复命令

mysql -uroot -p -v  </root/mysql.backup/data/test-2023-03-08_14_32_02.sql

此时上一次全量备份的数据已经恢复完成，最新增加的那条（name=王五）还没恢复

4 增量恢复，恢复上一次全量恢复后到删库之前的数据
进入二进制文件存放位置，执行增量恢复操作

mysqlbinlog 常用参数选项解释：
--start-position=875 起始pos点
--stop-position=954 结束pos点
--start-datetime="2016-9-25 22:01:08" 起始时间点
--stop-datetime="2019-9-25 22:09:46" 结束时间点
--skip-gtids是忽略GTIDs报错
-d 指定数据库

mysqlbinlog --stop-position=485 -d test  mysql-bin.000019 | mysql -uroot -pAbc123456.

此时最新的那条数据（name=王五）已经恢复过来了