MYSQL备份与恢复

一.数据库备份的分类

1.数据备份的重要性

• 备份的主要目的是灾难恢复
• 在生产环境中，数据的安全性至关重要
• 任何数据的丢失都可能产生严重的后果

2.物理备份

对数据库操作系统的物理文件(如数据文件:日志文件等)的备份

• 冷备份(脱机备份):是在关闭数据库的时候进行的
• 热备份(联机备份):数据库处于运行状态，依赖于数据库的日志文件
• 温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作

3.备份策略

• 完全备份:每次对数据库进行完整的备份
• 差异备份:备份自从上次完全备份之后被修改过的文件
• 增量备份:只有在上次完全备份或者增量备份后被修改的文件才会被备份

 4.备份工具

• tar压缩打包（完全备份，物理冷备）    
• mysqldump（完全备份，逻辑热备）  
• mysqlhotcopy（完全备份，逻辑热备，仅支持MyISAM和ARCHIVE引擎表）     
• 二进制日志（增量备份）     
• PXB XtraBackup innobackupex（完全备份、增量备份，物理热备）

5.完全备份

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

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

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

6.完全备份分类

1）物理冷备份与恢复

• 关闭MySQL数据库
• 使用tar命令直接打包数据库文件夹
• 直接替换现有MySQL目录即可

2）mysqldump备份与恢复

• MySQL自带的备份工具，可方便实现对MySQL的备份
• 可以将指定的库、表导出为SQL脚本
• 使用命令mysql导入备份的数据

7.增量备份

1）使用mysqldump进行完全备份存在的问题

• 备份数据中有重复数据
• 备份时间与恢复时间过长

是自上一次备份后增加/变化的文件或者内容

2）特点

• 没有重复数据，备份量不大，时间短
• 恢复需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复

3）MySQL没有提供直接的增量备份方法

可通过MySQL提供的二进制日志间接实现增量备份

MySQL二进制日志对备份的意义

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

4）一般恢复

• 将所有备份的二进制日志内容全部恢复

基于位置恢复

• 数据库在某一时间点可能既有错误的操作也有正确的操作·可以基于精准的位置跳过错误的操作

基于时间点恢复

• 跳过某个发生错误的时间点实现数据恢复

二.备份操作

1.物理冷备恢复

1.关闭mysql

systemctl stop mysql 

2.tar命令打包数据库文件

tar zcf /opt/mysql_backup_$(date +%Y%m%d)   data/   备份data文件到opt目录下 
cd /opt/
tar xf mysql_backup_$      解压成一个data目录

3.替换现有mysql目录

mv /opt/data    /usr/local/mysql  
 
重启mysql 
 
进入数据库
show database；

 2.mysqldump备份（完全备份）

mysqldump -u用户 -p密码 库名 > /opt/backup/库名.sql   #只把库里的表备份 不包含库本身
mysqldump -u用户 -p密码 --databases 库名 > /opt/backup/库名.sql #将库和库里的表都备份
mysqldump -u用户 -p密码 --all-databases > 库名.sql  #将所有库都备份
mysqldump -u用户 -p密码  库名 表名1 （表名2） > xxx.sql  #只备份一个或者多个指定的表（不包含库对象本身）

3.完全恢复 

1）先登录数据库，再执行 source sql文件路径  （如何sql文件里只备份了表，需要先 use 切换库再执行 source）

2）mysql -u root -p密码 < sql文件路径           cat sql文件路径 | mysql -u root -p密码                #恢复库
   mysql -u root -p密码 库名 < sql文件路径      cat sql文件路径 | mysql -u root -p密码 库名           #恢复表

4.增量备份 

通过刷新二进制日志实现增量备份
mysqladmin -u root -p密码 flush-logs

查看二进制日志内容
mysqlbinlog --no-defaults --base64-output=decode-rows -v 二进制文件路径

使用二进制日志增量恢复
mysqlbinlog --no-defaults 二进制文件路径 | mysql -u root -p密码

 5.备份脚本解析