最新消息: 关于Git&GitHub 版本控制你了解多少?
您现在的位置是:群英 > 数据库 >
MySQL增量备份与恢复如何实现?
PHP中文网发表于 2021-03-02 11:59 次浏览

免费学习推荐:mysql视频教程

文章目录

  • 一、MySQL 增量备份
    • 1.增量备份的概念
      • 1.1 为什么使用增量备份
      • 1.2 增量备份的特点
    • 2.增量备份示例
  • 二、MySQL 增量恢复
    • 1.增量恢复的场景
    • 2.丢失完全备份之后更改的数据的恢复步骤
    • 3.完全备份之后丢失所有数据的恢复步骤
    • 4. 基于时间点与位置的恢复
      • 4.1 基于时间点的恢复
      • 4.1 基于位置的操作
    • 5. 指定企业备份策略的思路

一、MySQL 增量备份

增量备份可以在完全备份的基础上,减少备份文件的大小,从而加快备份和恢复的速度

1.增量备份的概念

1.1 为什么使用增量备份

  • 前面章节讲到了完全备份有两种方式,一种是使用 tar 打包数据文件,另一种是
    使用 mysqldump 进行完全备份
  • 完全备份存在的问题很容易看到,每次都是把所有的数据内容进行备份,备份数据中有大量的重复数据,并且完全备份的时间与恢复的时间很长
  • 解决完全备份存在的问题就是使用增量备份的方式,增量备份就是备份自上一次备份之后增加或改变的文件或者内容

1.2 增量备份的特点

  • 增量备份的优点是没有重复数据,备份量不大,时间短
  • 缺点也很明显,需要上次完全备份及完全备份之后所有的增量备份才能恢复,而且对所有增量备份进行逐个反推恢复,操作较为繁锁
  • MySQL 没有提供直接的增量备份方法,但是可以通过 MySQL 的二进制日志(binary
    logs)间接实现增量备份

二进制日志对备份的意义如下:

  1. 二进制日志保存了所有更新或者可能更新数据库的操作
  2. 二进制日志在启动 MySQL 服务器后开始记录,并在文件达到 max_binlog_size 所设置的大小或者接收到 flush logs 命令后重新创建新的日志文件
  3. 只需要定时执行 flush logs 方法重新创建新的日志,生成二进制文件序列,并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份

2.增量备份示例

  1. 开启二进制日志功能
vim /etc/my.cnf...[mysqld]log-bin=mysql-binbinlog_format = MIXED#指定二进制日志(binlog)的记录格式为 MIXEDsystemctl restart mysqld.service#重启服务cd /usr/local/mysql/datals -l /usr/local/mysql/data/mysql-bin.*#查看二进制文件#二进制日志(binlog)有3种不同的记录格式:STATEMENT(基于SQL语句)、ROW(基于行)、MIXED(混合模式)#默认格式是 STATEMENT


  1. 每周选择服务器负载较轻的时间段,或者用户访问较少的时间段进行备份
mysqldump -uroot -p123123 SCHOOL CLASS01 > /opt/SCHOOL_CLASS01_$(date  %F).sql#对表进行完全备份mysqldump -uroot -p123123 --all-databases SCHOOL > /opt/SCHOOL_$(date  %F).sql#对库进行完全备份crontab -e#也可以使用计划性任务来执行30 3 * * 3 mysqldump -uroot -p123123 SCHOOL CLASS01 > /opt/SCHOOL_CLASS01_$(date  %F).sql30 3 * * 3 mysqldump -uroot -p123123 --all-databases SCHOOL > /opt/SCHOOL_$(date  %F).sql每周三的凌晨 3:00 对数据库和表进行完全备份


  1. 可每天进行增量备份操作,生成新的二进制日志文件,这样在插入新的数据后,新的二进制文件对应的就是数据库的变化的内容
ls /usr/local/mysql/datamysqladmin -uroot -p123123 flush-logs

  1. 插入新的数据,以模拟数据的增加或变更
use SCHOOL;insert into CLASS01 values(3,'wangsan','woman','games');insert into CLASS01 values(4,'wangsi','man','runing');select * from CLASS01;

  1. 生成新的二进制文件并查看其内容
cd /usr/local/mysql/data/lsmysqladmin -uroot -p123123 flush-logs

cp mysql-bin.000002 /opt/#将记录变更的二进制文件02复制至/opt目录下cd /opt/lsmysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002#使用64位编码机制去解码,按行读取详细内容


二、MySQL 增量恢复

  • 增量恢复比完全恢复操作更为繁琐
  • 每个增量备份都是单独的个体,数据不重复,需要控制得更加精确

1.增量恢复的场景

  • 当数据发送错误时,应根据实际情况选择使用完全备份恢复,还是增量备份
  • 增量备份的场景是:
    • 人为的 SQL 语句破坏了数据库
    • 在进行下一次全备之前发送系统故障导致数据库数据丢失
    • 在主从架构中,主库数据发送了故障
  • 根据数据丢失的情况可以分为两类:
    • 只丢失了完全备份之后更改的数据
    • 完全备份之后丢失所有的数据

2.丢失完全备份之后更改的数据的恢复步骤

  • 当完全备份之后更改的数据丢失,需要把完全备份之后的所有增量备份文件逐个恢复
  • 步骤如下:
mysql -uroot -p123123use SCHOOL;delete from CLASS1 where id=3;delete from CLASS1 where id=4;#删除插入的两条数据,模拟完全备份后数据丢失的故障select * from CLASS01;#检查quitmysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -uroot -p123123#使用二进制文件进行恢复操作mysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"#检查表内容是否恢复


3.完全备份之后丢失所有数据的恢复步骤

  • 当完全备份和增量备份之后,所有的数据丢失,需要把完全备份和所有增量备份文件逐个恢复
  • 步骤如下:
mysql -uroot -p123123use SCHOOL;drop table CLASS01;#直接删除整个表,假设完全备份后所有数据都丢失了quitmysql -uroot -p123123 SCHOOL < /opt/SCHOOL_CLASS01_2021-02-06.sqlmysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"#进行完全备份后查看一下mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -uroot -p123123#增量备份mysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"


4. 基于时间点与位置的恢复

  • 利用二进制日志可实现基于时间点与位置的恢复,例如由于误操作删除了一张表,这时完全恢复是没有用的
  • 因为日志里还有误操作的语句,我们需要的是恢复到误操作之前的状态,然后跳过误操作的语句,再恢复后面操作的语句

4.1 基于时间点的恢复

  • 基于时间点的恢复,就是将某个起始时间的二进制文件导入数据库中,从而跳过某个发生错误的时间点实现数据的恢复
  • 使用 mysqlbinlog 加上 --stop-datetime 选项,表示在哪个时间点结束,后面误操作的语句不执行
  • –start-datetime 选项表示执行后面的语句
  • 结合使用它们就可以跳过误操作的语句,完成恢复工作
  • 需要注意的是,二进制文件中保存的日期格式需要调整为用“-”分割
#恢复用户“wangsan”的数据,而不恢复“wangsi”mysql -uroot -p123123 -e "truncate table SCHOOL.CLASS01;"mysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"mysqlbinlog --no-defaults --stop-datetime='2021-02-06 15:58:39' /opt/mysql-bin.000002 |mysql -uroot -p123123mysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"

#恢复“wangsi”的数据mysqlbinlog --no-defaults --start-datetime='2021-02-06 15:58:39' /opt/mysql-bin.000002 |mysql -uroot -p

4.1 基于位置的操作

  • 基于位置的恢复,就是使用基于时间点的恢复
  • 可能会出现在一个时间点里既同时存在正确的操作又存在错误的操作,基于位置是一种更为精确的恢复方式
mysqlbinlog --no-defaults --stop-position='609' /opt/mysql-bin.000002 | mysql -uroot -p#使用64位编码机制去解码并按行读取二进制文件02(增量备份)的详细内容......略

#仅恢复“1810”之前的数据,即不恢复“wangsi”的数据mysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"mysql -uroot -p123123 -e "truncate table SCHOOL.CLASS01;"mysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"mysqlbinlog --no-defaults --stop-position='1810' /opt/mysql-bin.000002 | mysql -uroot -pmysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"

#仅恢复“wangsi”的数据,跳过“wangsan”的数据恢复,即仅有第四条记录mysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"mysqlbinlog --no-defaults --start-position='1810' /opt/mysql-bin.000002 | mysql -uroot -p123123mysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"

5. 指定企业备份策略的思路

  • 指定企业备份策略要根据企业数据库的实际读写的频繁性与数据的重要性进行
  • 数据更新频繁,则应该进行较为频繁的备份
  • 数据较为重要,则在有适当更新时进行备份
  • 在数据库压力小的时段进行全备,如一周一次,然后每天增备
  • 根据公司的规模,中小公司可一天一次全备,大公司可每周一次全备,每天进行一次增备,并且尽量为企业实现主从复制架构

相关免费学习推荐:mysql数据库(视频)

以上就是MySQL介绍增量备份与恢复的详细内容,更多请关注群英网络其它相关文章!

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:mmqy2019@163.com进行举报,并提供相关证据,查实之后,将立刻删除涉嫌侵权内容。
相关信息推荐
2021-08-26 18:00:46 关键词:navicat连接mysql报错1045
摘要:navicat连接mysql报错1045怎怎么回事?很多朋友在navicat连接mysql出现报错1045,不知道要怎样解决。对于这个问题可能是因为忘记密码导致的,下面小编就给大家分享一下解决方法。..
2021-08-25 17:36:23 关键词:mysql表连接
摘要:mysql中怎样进行表连接?我们在进行多表查询的时候,常常会需要进行表连接,这篇文章就主要给大家介绍,内连接和外连接这两种方式,感兴趣的朋友跟随小编一起来学习一下吧。..
2021-08-25 17:36:12 关键词:mysql约束
摘要:MySQL约束有哪些?MySQL约束有主键约束、外键约束、唯一约束、检查约束、非空约束和默认值约束这六种。对新手来说,可能不是很理解这些MySQL约束,下面小编就大家通俗的介绍一下这..
2021-08-25 17:36:03 关键词:mysql创建用户
摘要:mysql怎样快速创建新用户?我们想要创建新用户,可以使用CREATE USER语句、INSERT 语句和GRANT语句,但是这三种方式创建的用户在权限上有一定的差别,下面我们就来具体了解一下。..
2021-08-25 17:35:59 关键词:mysql索引
摘要:这篇文章主要给大家分享的是mysql中索引的内容,详细介绍了mysql索引以及常见的mysql索引操作,对新手学习mysql索引有一定的借鉴价值,有这方面学习需要的朋友可以参考,下面我们就来..