目录
数据库备份的分类
数据备份的重要性
数据库备份的分类
常见的备份方法
MySQL完全备份与恢复
MySQL完全备份介绍
MySQL完全备份的优缺点
数据库完全备份分类
完全备份操作
物理冷备份
逻辑备份(mysqldump的使用)
MySQL增量备份与恢复
MySQL增量备份
特点
增量备份与恢复操作
增量备份
增量恢复
断点恢复
数据库备份的分类
数据备份的重要性
- 备份的主要目的是灾难恢复
- 在生产环境中,数据的安全性至关重要
- 任何数据的丢失都可能产生严重的后果
- 造成数据丢失的原因
- 程序错误
- 人为操作错误
- 运算错误
- 磁盘故障
- 灾难(如火灾、地震)和盗窃
数据库备份的分类
从物理与逻辑的角度,备份可分为
-
物理备份:对数据库操作系统的物理文件(如数据文件、日志文件等)的备份
- 物理备份方法
- 冷备份(脱机备份):是在关闭数据库的时候进行的
- 热备份(联机备份):数据库处于运行状态,依赖于数据库的日志文件
- 温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作
-
逻辑备份:对数据库逻辑组件(如:表等数据库对象)的备份
从数据库的备份策略角度,备份可分为
常见的备份方法
- 物理冷备
- 备份时数据库处于关闭状态,直接打包数据库文件
- 备份速度快,恢复时也是最简单的
- 专用备份工具mydump或mysqlhotcopy
- mysqldump常用的逻辑备份工具
- mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
- 启用二进制日志进行增量备份
- 第三方工具备份
- 免费的MySQL热备份软件Percona、XtraBackup
MySQL完全备份与恢复
MySQL完全备份介绍
- 是对整个数据库、数据库结构和文件结构的备份
- 保存的是备份完成时刻的数据库
- 是差异备份与增量备份的基础
MySQL完全备份的优缺点
-
优点
-
缺点
- 数据存在大量的重复
- 占用大量的备份空间
- 备份与恢复时间长
数据库完全备份分类
- 物理冷备份与恢复
- 关闭MySQL数据库
- 使用tar命令直接打包数据库文件夹
- 直接替换现有MySQL目录即可
- mysqldump备份与恢复
- MySQL自带的备份工具,可方便实现对MySQL的备份
- 可以将指定的库、表导出为SQL脚本
- 使用命令mysql导入备份的数据
完全备份操作
物理冷备份
备份
过程:关闭数据库 ——> 对数据目录进行打包压缩
恢复数据库
过程:解压备份文件 ——> 替换数据目录 ——> 启动数据库
逻辑备份(mysqldump的使用)
mysqldump是常用的逻辑备份工具。
mysqldump可以将指定的库、表导出为SQL脚本。
mysqldump完全备份
mysqldump备份数据库
mysqldump命令对单个库进行完全备份
mysqldump -u用户名 -p[密码] [选项] [数据库名] > /备份路径/备份文件名
-p后面不跟密码的话,会有交互的形式输入密码
![](https://img-blog.csdnimg.cn/94b5aacccbe84bf2a5e988f51454e877.png)
查看备份文件的内容。
![](https://img-blog.csdnimg.cn/22d2f19f58984cb0a30966a7a1ffac90.png)
mysqldump命令对多个库进行完全备份
mysqldump -u用户名 -p[密码] [选项] --databases 库名1 [库名2] ... > /备份路径/备份文件名
![](https://img-blog.csdnimg.cn/eeab23ec88fe48458841603ea6972684.png)
![](https://img-blog.csdnimg.cn/32a9706e3e6b4f0e8fd2391cbe462984.png)
对所有库进行完全备份
mysqldump -u用户名 -p[密码] [选项] --all-databases > /备份路径/备份文件名
![](https://img-blog.csdnimg.cn/d6ca670d96914a92bdbeeedbb9baeab2.png)
mysqldump备份数据表
mysqldump可针对库内特定的表进行备份
使用mysqldump备份表的操作
mysqldump -u用户名 -p[密码] [选项] 数据库名 表1 表2 ... > /备份路径/备份文件名
备份单个表
![](https://img-blog.csdnimg.cn/4aeb3fa8140c44e8a3d1c5c2d5d2a943.png)
备份多个表
![](https://img-blog.csdnimg.cn/a216355bd83f4caf86c105a1c34b2fa4.png)
-d只保存表结构
-d选项的作用:只保存表结构,不保存表数据
![](https://img-blog.csdnimg.cn/90296d9a8b7f4cae9614603a5973c467.png)
查看备份文件,只有创建表的命令,没有插入数据的命令。
![](https://img-blog.csdnimg.cn/2cea547e09204b76b212f60b00f7220c.png)
mysqldump恢复
mysql -u [用户] -p[密码] -e 'SQL语句'
-e选项:用于指定连接MySQL后执行的命令,命令执行完后自动退出
mysqldump恢复库
补充:如果备份时想保存数据库名,则带上--databases,单库备份多库备份都可以
之前的多库备份带上了--databases,这里查看该备份文件。
![](https://img-blog.csdnimg.cn/a4b15a34b3a04b5fbafaf5a2885b12dd.png)
如果备份文件中保留了数据库名,则恢复命令中不用指定数据库,mysql -u [用户] -p[密码] < /备份路径/备份文件名
mysqldump恢复表
MySQL增量备份与恢复
MySQL增量备份
增量备份是自上一次备份后增加/变化的文件或者内容
特点
- 没有重复数据,备份量不大,时间短
- 恢复需要上次完全备份及完全备份之后所有的增量备份才能恢复,而且要对所有增量备份进行逐个反推恢复
增量备份与恢复操作
准备工作
-
准备一个数据库,并在里面创建一个表
-
创建一个用于存放备份文件的目录
-
开启二进制日志功能
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin #开启二进制日志。如果使用相对路径,则保存在/usr/local/mysql/data/目录下
binlog_format = MIXED #可选,指定二进制日志(binlog)的记录格式为MIXED
server-id = 1
systemctl restart mysqld
ls -l /usr/local/mysql/data/mysql-bin.*
复制代码
![](https://img-blog.csdnimg.cn/91812565eb284ddca9e7b2aa31148a8b.png)
增量备份
-
每周先对数据库或者表进行完全备份
-
每天进行增量备份操作,生成新的二进制日志文件(例如mysql-bin.000002)
mysqladmin -u root -p flush-logs
![](https://img-blog.csdnimg.cn/img_convert/00a1ef8ae80d256f30b5a3557ca58222.webp?x-oss-process=image/format,png)
-
插入新数据,以模拟数据的增加或变更
-
再次刷新生成新的二进制日志文件(例如mysql-bin.000003)
- 之前的步骤3的数据库操作会保存到mysql-bin.000002文件中,之后数据库数据再发生变化则保存在mysql-bin.00003文件中
查看二进制日志文件的内容
mv /usr/local/mysql/data/mysql-bin.000002 /opt/backup/binlog-$(date +%Y%m%d)
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/backup/binlog-20221028
#--base64-output=decode-rows:使用64位编码机制去解码并按行读取
#-v:显示详细内容
复制代码
![](https://img-blog.csdnimg.cn/408bb30ec6084b7e96d16af5e5b14304.png)
增量恢复
- 一般恢复
- 基于位置恢复
- 数据库在某一时间点可能既有错误的操作也有正确的操作
- 可以基于精准的位置跳过错误的操作
- 基于时间点恢复
一般恢复
模拟表中数据丢失
-
删除新增加的内容模拟数据丢失
-
增量恢复,新增加的两条数据记录保存在mysql-bin.000002日志中
mysqlbinlog --no-defaults 二进制日志文件路径 | mysql -u 用户 -p密码
-
查看表的内容
模拟整个表丢失
-
删除表表模拟表的丢失
-
先恢复完全备份的数据
![](https://img-blog.csdnimg.cn/img_convert/ae8bd84ed15f8865bc5189e00698eb3b.webp?x-oss-process=image/format,png)
-
再恢复增量备份的内容
断点恢复
准备工作
-
再插入两条数据,用于断点恢复实验
-
刷新数据库生成新的二进制
-
将上个二进制文件移动到备份目录
基于位置恢复
查看备份文件
![](https://img-blog.csdnimg.cn/ce8ade98c2fb40bb8e9399317ea2e770.png)
mysqlbinlog --no-defaults --start-position='位置点' 文件名 | mysql -u root -p
#从某一个位置点开始恢复,一直到日志结尾
mysqlbinlog --no-defaults --stop-position='位置点' 文件名 | mysql -u root -p
#从日志开头,一直恢复到某一个位置点前结束
mysqlbinlog --no-defaults --start-position='xxx' --stop-position='位置点' 文件名 | mysql -u root -p
#从某一个位置点开始恢复,一直到某一个位置点前结束
复制代码
![](https://img-blog.csdnimg.cn/8ac1abb40a3a4ef5aca98c4ceaac565d.png)
示例:恢复熊二的信息
![](https://img-blog.csdnimg.cn/2f562c38b7db47d2b21259bf7f32de97.png)
![](https://img-blog.csdnimg.cn/777ca06bb0d04196834b8f71c5163edc.png)
基于时间点恢复
mysqlbinlog --no-defaults --start-datetime='时间点' 文件名 | mysql -u root -p
#从某一个时间点开始恢复,一直到日志结尾
mysqlbinlog --no-defaults --stop-datetime='时间点' 文件名 | mysql -u root -p
#从日志开头,一直恢复到某一个时间点前结束
mysqlbinlog --no-defaults --start-datetime='起始时间点' --stop-datetime='结束时间点' 文件名 | mysql -u root -p
#从某一个时间点开始恢复,一直到某一个位置点前结束
复制代码
注意:日期必须是"yyyy-mm-dd"的格式。
示例:恢复熊二的信息
![](https://img-blog.csdnimg.cn/dabd1655abe94b2d81fa14fcf58e4855.png)
![](https://img-blog.csdnimg.cn/e0a57978768e40728cc2e7be6d1cc955.png)
![](https://img-blog.csdnimg.cn/b7831c32efad4facad56c65792676ec8.png)
断点恢复总结
- 如果恢复某条SQL语句之前的数据,就--stop在这个语句的位置点
- 如果恢复某条SQL语句之后的数据,就从这个语句的点位开始
- 位置点--start-position
- 时间点--start-datetime='YYYY-mm-dd HH:MM:SS'