mysqldump+binlog+gtid 实现数据库的增量备份(上)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: mysqldump+binlog+gtid 实现数据库的增量备份

mysqldump备份数据库

完全备份+增加备份,速度相对较慢,适合中小型数据库、MyISAM是温备份,InnoDB是热备份

mysqldump 是采用SQL级别的备份机制,它将数据表导成 SQL 脚本文件,在不同的 MySQL 版本之间升级 时相对比较合适,这也是最常用的备份方法。

备份策略可以这么定:第一次完全备份,每天一次增量备份,每周再做一次完全备份,如此一直重复。而对于重要的且繁忙的系统来说,则可能需要每天一次全量备份,每小时一次增量备份,甚至更频繁。为了不影响线 上业务,实现在线备份,并且能增量备份,最好的办法就是采用主从复制机制(replication),在 slave 机器上做备份。

  1. 示例一:备份恢复单个数据库
    备份:mydqldump -u 用户 –p’密码’ –default-character-set=Iatin1 数据库名 [|gzip] > 备份文件名
    示例:mysqldump -uroot -p'123456' --default-character-set=utf8 -B school > /tmp/school.$(date +%F).sql
    注意:-B 作用:创建数据库和切换到数据库,恢复时不用创建数据库和删表。备份多个库,-B 数据库1 数 据库2 …
    恢复:
    1)MySQL中用source命令
    source /backup/db/school.sql
    2)mysql命令恢复
    mysql -uroot -p123456 company < /backup/db/school.sql
  2. 示例二:备份恢复单个表
    备份:mysqldump -u 用户名 -p 数据库名 表名 > 备份的文件名
    示例:mysqldump -uroot -p school Books >/tmp/Books.$(date+%F).sql
    备份多个表:mysqldump -u 用户名 -p 数据库名 表名1 表名2 > 备份的文件名
  3. 示例三:备份数据结构
    -d只备份库结构,不包含数据内容

示例四:增量备份
前提:
1)my.cnf,是要开启MySQL log-bin日志功能,重启MySQL log_bin = /data/mysql/data/mysql-bin
2)存在一个完全备份,生产环境一般凌晨某个时刻进行全备
示例:mysqldump -uroot -p --default-character-set=utf8 --single-transaction -F -B school |gzip > /server/backup/school_$(date +%F).sql.gz InnoDB 表在备份时,通常启用选项 --single-transaction 来保证备份的一致性
MySQL增量恢复案例图解:
准备数据库和表:

create database it DEFAULT CHARACTER SET utf8; 

学生表:Student(Sno,Sname,Ssex,Ssage,Ssdept)(学号-主键,姓名,性别,年龄,所在系):

CREATE TABLE `Student` ( `Sno` int(10) NOT NULL COMMENT '学号', `Sname` varchar(16) NOT NULL COMMENT '姓名', `Ssex` char(2) NOT NULL COMMENT '性别', `Sage` tinyint(2) NOT NULL DEFAULT '0' COMMENT '学生年龄', `Sdept` varchar(16) DEFAULT 'NULL' COMMENT '学生所在系别', PRIMARY KEY (`Sno`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 

表中插入数据:

INSERT INTO `Student` VALUES (1, '陆亚', '男', 24, '计算机网络'),(2, 'tom', '男', 26, '英语'),(3, '张阳', '男', 21, '物流管理'), (4, 'alex', '女', 22, '电子商务'); 

半夜零点手工全备:

mysqldump -uroot -p123456 -F -B it --default-character-set=utf8 --singletransaction -e | gzip > /server/backup/mysql_backup_`date +%F`.sql.gz 

备份后继续插入数据:

sleep 60
INSERT INTO Student values(0005,'xumubin','男',29,'中文专业'), (0006,'wangzhao','男',21,'导弹专业'); 

模拟用户破坏数据:

sleep 30
drop database it; 

增量备份–恢复过程
1、检查凌晨备份
2、检查全备后的所有binlog

ls -lrt /usr/local/mysql/data/mysql-bin.* 

3、立即刷新并备份出binlog

mysqladmin -uroot -p flush-logs 
cp /usr/local/mysql/data/mysql-bin.000004 /server/backup/ 
#  提示:根据时间点及前一个binlog可以知道发现问题时刻前binlog日志为mysql-bin.000004 

4、恢复binlog生成sql语句

mysqlbinlog mysql-bin.000004 > bin.log 

5、恢复凌晨备份
6、恢复增量备份
mysqlbinlog增量恢复方式
基于时间点恢复
1)指定开始时间到结束时间

myslbinlog mysqlbin.000008 --start-datetime=’2014-10-45 01:10:46’ --stopdatetime=’2014-10-45 03:10:46’-r time.sql 

2)指定开始时间到文件结束

myslbinlog mysqlbin.000008 --start-datetime=’2014-10-45 01:10:46’ -d esen -r time.sql 

3)从文件开头到指定结束时间

myslbinlog mysqlbin.000008 --stop-datetime=’2014-10-45 03:10:46’ -d esen -r time.sql 

基于位置点的增量恢复
1)指定开始位置到结束位置

myslbinlog mysqlbin.000008 --start-position=510 --stop-position=1312 -r pos.sql 

2)指定开始位置到文件结束

myslbinlog mysqlbin.000008 --start-position=510 -r pos.sql 

3)从文件开始位置到指定结束位置

myslbinlog mysqlbin.000008 --stop-position=1312 -r pos.sq

高级备份参数:

-R    备份存储过程及函数 
--triggers    备份触发器 
-E    备份事件 
-F    在备份开始时,刷新一个新binlog日志 
--master-data=2   以注释的形式,保存备份开始时间点的binlog的状态信息 
示例:[root@mysql mysql]# mysqldump -uroot -p -A -R --triggers --masterdata=2 > /tmp/full.sql 
[root@mysql mysql]# grep 'CHANGE MASTER' /tmp/full.sql | head -1 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=766; 

功能:

(1)在备份时,会自动记录,二进制日志文件名和位置号

0 默认值

1 以change master to命令形式,可以用作主从复制

2 以注释的形式记录,备份时刻的文件名+postion号

(2)自动锁表

(3)如果配合--single-transaction,只对非InnoDB表进行锁表备份,InnoDB表进行“热“备, 实际上是实现快照备份。

--single-transaction

innodb 存储引擎开启热备(快照备份)功能

master-data 可以自动加锁

(1)在不加--single-transaction ,启动所有表的温备份,所有表都锁定

(2)加上--single-transaction ,对innodb进行快照备份,对非innodb表可以实现自动锁表功能

示例: 备份必加参数 
mysqldump -uroot -p -A -R -E --triggers --master-data=2 --singletransaction --set-gti


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2月前
|
存储 关系型数据库 MySQL
mysql数据库备份与恢复
MySQL数据库的备份与恢复是确保数据安全性和业务连续性的关键操作。
62 4
|
3月前
|
SQL 关系型数据库 MySQL
【揭秘】MySQL binlog日志与GTID:如何让数据库备份恢复变得轻松简单?
【8月更文挑战第22天】MySQL的binlog日志记录数据变更,用于恢复、复制和点恢复;GTID为每笔事务分配唯一ID,简化复制和恢复流程。开启binlog和GTID后,可通过`mysqldump`进行逻辑备份,包含binlog位置信息,或用`xtrabackup`做物理备份。恢复时,使用`mysql`命令执行备份文件,或通过`innobackupex`恢复物理备份。GTID模式下的主从复制配置更简便。
287 2
|
5天前
|
存储 定位技术 数据库
介绍一下数据库的备份和恢复策略
【10月更文挑战第21】介绍一下数据库的备份和恢复策略
|
2月前
|
关系型数据库 MySQL 数据库
Navicat备份数据库
涵盖`Navicat`数据库备份、数据安全及备份策略等主题。文库采用精美主题,提升阅读体验。
28 1
Navicat备份数据库
|
2月前
|
SQL 数据库 数据安全/隐私保护
如何手动备份数据库?
如何手动备份数据库?
88 1
|
3月前
|
SQL 数据库
Microsoft SQL Server 2014如何来备份数据库
Microsoft SQL Server 2014如何来备份数据库
275 3
|
3月前
|
SQL 关系型数据库 MySQL
在Linux中,如何备份和恢复MySQL数据库?
在Linux中,如何备份和恢复MySQL数据库?
|
3月前
|
存储 数据库 数据库管理
SQLite数据库的备份
【8月更文挑战第20天】SQLite数据库的备份
176 1
|
3月前
|
关系型数据库 MySQL Shell
分享一篇mysql数据库备份脚本
分享一篇mysql数据库备份脚本
30 0
|
3月前
|
关系型数据库 MySQL Linux
在Linux中,如何备份一个数据库?
在Linux中,如何备份一个数据库?