MySQL数据库——备份与恢复

MySQL数据库——备份与恢复

一、Mysql备份概述

1.数据备份的重要性

数据的安全性至关重要，任何数据的丢失都可能产生严重的后果。

造成数据丢失的原因：

• 程序错误

• 人为操作错误

• 运算错误

• 磁盘故障

• 灾难（如火灾、地震）和盗窃

为什么要备份？

• 能够防止由于机械故以及人为误操作带来的故据丢失，例如将故据库文件保存在了其它地方。

• 冗余：故据有多份冗余，但不等备份，只能防止机械故障还来的故据丢失，例如主备模式、数据库集群。

2.备份必须重视的内容

• 备份内容 databases Binlog my.conf。

• 所有备份数据都应放在非数据库本地，而且建议有多份副本。

• 测试环境中做日常恢复演练，恢复较备份更为重要。

3.备份过程中必须考虑因素

• 数据的一致性

• 服务的可用性

二、数据库备份的分类

1.物理备份

对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。物理备份又可分为脱机备份（冷备份）和联机备份（热备份）。这种类型的备份适用于出现问题时需要快速恢复的大型重要数据库。

物理备份的方法：

1.冷备份（cold backup）：

​ ▶ 备份数据文件，需要停机，是在关闭数据库的时候进行的。

​ ▶ 备份datadir目录下的所有文件。

2.热备份（hot backup）：

​ ▶ 在线备份，数据库处于运行状态，这种备份方法依赖于数据库的日志文件。

​ ▶ 对应用基本无影响（应用程序读写不会阻塞，但是性能还是会有下降，所以尽量不要在主上做备份，在从库上做）。

3.温备份（warm backup）：

​ ▶ 数据库锁定表格（不可写入但可读）的状态下进行备份操作。

​ ▶ 对应用影响很大，通常加一个读锁。

• 逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份。

2.逻辑备份

对数据库逻辑组件（如表等数据库对象）的备份，表示为逻辑数据库结构create database、createtable等语句和内容（insert语句或分割文本文件)的信息。这种类型的备份适用于可以编辑数据值或表结构较小的数据量，或者在不同机器体系结构上重新创建数据。

-	逻辑备份	物理备份
备份方式	备份数据库逻辑内容	备份数据库物理文件
优点	备份文件相对较小，只备份表中的数据与结构	恢复速度比较快（物理文件恢复基本已经完成恢复)
缺点	恢复速度较慢，需要重建索引，存储过程等	备份文件相对较大（备份表空间，包含数据与索引，碎片)
对业务影响	缓冲池污染（把所有数据读一遍，读到bp中)，l/O负载加大	I/O负载加大
代表工具	mysqldump	ibbackup，xtrabackup，mysqlbackup

3.备份方式的选择

• 从以下几个维度考录备份方式

​ ▶ 备份速度

​ ▶ 恢复速度

​ ▶ 备份大小

​ ▶ 对业务影响

三、Mysql备份工具

1. ibbackup

• 官方备份工具

• 收费

• 物理备份

2. xtrabackup

• 开源社区备份工具

• 开源免费，上面那东西的免费版本（老版本有问题，备份出来的数据可能有问题）

• 物理备份

3. mysqldump

• 官方自带备份工具开源免费

• 逻辑备份（速度慢）

• 不阻塞dml,阻塞ddl

4. mysqlbackup

• mysql官方备份工具

• innodb引擎的表mysqlbackupi可以进行热备

• 非innodb表mysqlbackup就只能温备

• 物理备份，备份还原速度快

• 适合大规模数据使用

四、Mysql备份策略

1.完全备份

• 每次对数据进行完整的备份，即对整个数据库的备份、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础。

• 优点：备份与恢复操作简单方便。

• 缺点：数据存在大量的重复；占用大量的空间：备份与恢复时间长。

2.差异备份

• 自从上次完全备份之后被修改过的文件。

• 备份那些自从上次完全备份之后被修改过的所有文件，备份的时间起点是从上次完整备份起，备份数据量会越来越大。

• 恢复数据时，只需恢复上次的完全备份与最近的一次差异备份。

3.增量备份

• 只有些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完整备份或上次的增量备份的时间为时间点，仅备份这之间的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份起到最后一次增量备份依次恢复，如中间某次的备份数据损坏，将导致数据的丢失。

4.三者区别

五、专用逻辑备份工具mysqldump

• mysqldump是Mysql自带的逻辑备份工具（常用），可以保证数据的一致性和服务的可用性。

• 它的备份原理是通过协议连接到MySQL数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的insert语句，当我们需要还原这些数据时，只要执行这些insert语句，即可将对应的数据还原。

命令格式：

mysqldump [选项] 库名 [表名] > 脚本名
或mysqldump [选项] --库名 [选项 表名] > 脚本名
或mysqldump [选项] --all-databases [选项] > 脚本名

选项说明：

参数名	缩写	含义
–host	-h	服务器IP地址
–port	-P	服务器端口号
–user	-u	MySQL用户名
–password	-p	MySQL密码
–databases	-B	指定要备份的数据库
–all-databases	-A	备份mysql服务器上的所有数据库
–compact		压缩模式，产生更少的输出
–comments		添加注释信息
–complete-insert		输出完成的插入语句
–lock-tables		备份前，锁定所有数据库表
–no-create-db/–no-create-info		禁止生成创建数据库语句
–force		当出现错误时仍然继续备份操作
–default-character-set		指定默认字符集
–add-locks		备份数据库表时锁定数据库表
-single-transaction		保证数据的一致性和服务的可用性
–master-data=1|2		通常等于1，记录binlog日志位置与文件名，追加至备份文件中
–flush-logs	-F	备份之前刷新日志
–events	-E	备份事件调度器代码
–triggers	-T	备份触发器
–routines	-R	备份存储过程和存储函数

END