MySQL数据库的备份与恢复

数据库备份的分类

物理与逻辑的角度

1. 物理备份：对数据库操作系统的物理文件进行备份

• 冷备份（脱机备份）：在关闭数据库的时候进行
• 热备份（联机备份）：数据库处于运行状态，这种备份方法依赖于数据库日志文件

2. 逻辑备份：对数据库逻辑组件进行备份

备份策略的角度

1. 完全备份：每次对数据进行完整地备份
2. 差异备份：备份那些自从上次完全备份之后被修改过的文件
3. 增量备份：只有那些在上次完全备份或者增量备份后被修改的文件才会被备份

完全备份与恢复：

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

完全备份的优缺点

• 完全备份的优点：
  备份与恢复操作简单方便
• 完全备份的缺点：
  数据存在大量的重复
  占用大量的备份空间
  备份与恢复时间长

MySQL备份思路

• 定期实施备份，制定备份计划或策略，并严格遵守
• 除了进行完全备份，开启MySQL服务器的日志功能也是非常重要的
  完全备份加上日志，可以对MySQL进行最大化还原
• 使用统一的和易理解的备份文件名称
  不要使用backup1、backup2等这样没有意义的名字
  推荐使用库名和表名加上时间的命名规则

备份的实验操作

1. 实验准备：先创建好一些数据库与表：
   
   
2. 对数据库进行完全备份

tar jcvf /opt/mysql-$(date +%F).tar.xz /home/mysql/school/

3. 使用mysqldump命令对单个数据库进行备份

mysqldump -u root -p school > /opt/school.sql

4. 使用mysqldump命令对多个数据库进行备份

mysqldump -u root -p --databases school bbs > /opt/bbs_school.sql

5. 使用mysqldump命令对所有数据库进行备份
   mysqldump -u root -p --opt --all-databases > /opt/all.sql
6. 使用mysqldump命令对表进行备份
   mysqldump -u root -p school student > /opt/school_student.sql
7. 使用mysqldump命令对表结构进行备份，不备份数据。
   mysqldump -u root -p -d school student > /opt/school_student.sql

完全备份的恢复

• 使用mysqldump命令导出的SQL备份脚本，在数据恢复期间可以用一下方法导入
  source命令
  mysql命令

恢复的实验操作

1. 使用source恢复数据库
   登录到数据库中进行操作
   先删除，再进行恢复
   source /opt/school.sql
   
   恢复成功！
2. 使用mysql命令恢复数据库
   先删除，再进行恢复

mysql -uroot -p school < /opt/school.sql

增量备份与恢复

增量备份的特点

• 使用mysqldump进行完全备份所存在的问题
  备份数据中有重复数据
  备份时间与恢复时间长
• 增量备份就是自上一次备份之后增加或变化的文件或者内容
• 增量备份的特点
  没有重复数据，备份量不大，时间短
  恢复麻烦：需要上次完全备份之后的增量备份才能恢复，而且对所有增量备份进行逐个反推恢复
• MySQL没有直接提供增量备份的方法
• 可以通过MySQL提供的二进制间接实现增量备份
• MySQL二进制日志对增量备份的意义
  二进制日志保存了所有的更新或者可能更新的数据库操作
  二进制日志在启动MySQL服务器之后开始记录，并在文件达到max_binlog_size所设置的大小或者受到flush logs命令重新创建新的日志文件
  只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列。并及时把这些旧的日志保存到安全的地方就成了一个时间段的增量备份

备份的实验操作

1. 开启二进制日志功能，重启服务

vim /etc/my.cnf

 systemctl restart mysqld

2. 首先执行一次完整性备份

 mysqldump -u root -p school > /opt/school.sql

3. 执行增量备份
   mysqladmin -u root -p flush-logs

4. 实验误操作
   进入数据库
   

 mysqladmin -u root -p flush-logs

5. 查看二进制日志文件查看误操作

mysqlbinlog --no-defaults mysql-bin.000002

生成备份文件

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002 > /opt/bak.txt

恢复的实验操作

1. 时间断点恢复
   从日志开到截止到某个时间点的恢复

mysqlbinlog --no-defaults --stop-datetime='2020-01-08 19:35:47' /home/mysql/mysql-bin.000002 | mysql -uroot -p

从某个时间点截止到日志结尾的恢复

mysqlbinlog --no-defaults --start-datetime='2020-01-08 19:38:45' /home/mysql/mysql-bin.000002 | mysql -uroot -p

执行完之后，查看表

恢复完成！

2. 位置断点恢复
   从日志开到截止到某个位置点的恢复

mysqlbinlog --no-defaults --stop-position='203' /home/mysql/mysql-bin.000002 | mysql -uroot -p

从某个位置点截止到日志结尾的恢复

 mysqlbinlog --no-defaults --start-position='344' /home/mysql/mysql-bin.000002 | mysql -uroot -p

执行完之后，查看表

恢复完成
3. 一般恢复

 mysqlbinlog --no-defaults 增量备份文件 | mysql -uroot -p