MySql教程

MySQL全面瓦解30:备份与恢复

本文主要是介绍MySQL全面瓦解30:备份与恢复,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

合辑地址:MySQL全面瓦解

1 为什么需要数据库备份

  • 灾难恢复:当发生数据灾难的时候,需要对损坏的数据进行恢复和还原
  • 需求的变更或者回滚:当需求发生变更,或者需要回滚到之前的版本时,数据库备份也显得很重要。
  • 审计:需要知道某一个阶段的数据或者Schema的实际情况
  • 测试:将实际的生产环境的数据导入到本地备份为测试数据,来验证新功能,可以省去很多麻烦。

2 备份需要考虑的几个关键点

  • 恢复点目标(PRO):可以容忍丢失多少数据
  • 恢复时间目标(RTO):需要等待多久将数据恢复
  • 恢复的时候是需要持续提供服务 还是 停机恢复。
  • 需要恢复的内容:整个服务器,多库多表,单库单表,或是特定的事务或语句。

3 备份方案

3.1 离线备份和在线备份

离线备份:就是传统意义上的cold backup(冷备份):需要关闭MySQL服务,读写请求均不允许状态下进行,这种模式下数据损坏和不一致性风险最小。
半离线备份:也就是我们说的warm backup(温备份): MySQL服务不关闭,但只开放了Read操作,关闭了Write操作。
在线备份:也就是hot backup(热备份):在数据备份的同时,MySQL业务持续进行中,仅限于InnoDB引擎。

3.2 逻辑备份和物理备份

3.2.1 逻辑备份:导出数据库表的定义和数据

逻辑备份有如下优点:

  • 恢复非常简单
  • 可以通过网络来备份和恢复
  • 备份的结果为ASCII文件,可以编辑
  • 与存储引擎无关
  • 非常灵活,可以使用mysqldump的工具提供很多可选项。

逻辑备份的缺点:

  • 必须由数据库服务器来完成备份和恢复过程
  • 备份结果占据更多的空间:逻辑备份在某些场景下比数据库文件本身还要大
  • 精度问题,无法保证还原出来的数据强一致
  • 还原时间长:还原之后,加载注释语句,转换存储格式,重建索引都需要消耗一定时间

3.2.2 物理备份:直接复制原数据文件

物理备份的优点:

  • 备份和恢复操作都比较简单,且能够跨平台,操作系统和MySQL版本。
  • 恢复速度快,都是基于文件的,复制到对应的目的地即可,InnoDB需要停止数据库服务,有额外的动作。
  • 步骤更少:不需要执行重新生成数据和重建索引的动作,效率提升。

物理备份的缺点:

  • InnoDB备份的原始文件往往比逻辑备份的大很多,空间要求大。

3.3 根据要备份的数据集合的范围

  • 完全备份:full backup,备份整个数据库信息。
  • 增量备份: incremental backup 上次完全备份或增量备份以来改变了的数据,需与完全备份配合使用。一般来说增量频率高,备份频率也高。
  • 差异备份:differential backup 上次完全备份以来改变了的数据。
  • 建议的恢复策略:
    • 完全+增量+二进制日志
    • 完全+差异+二进制日志

4 备份的内容主要有哪些?

  • 数据:基础数据。
  • 日志:包含 二进制日志 和 InnoDB事务日志 等。
  • 配置信息:包括服务器配置 和 复制相关的配置(主从复制中的中继日志和日志索引文件等)。
  • 代码:存储过程、函数、触发器、视图等
  • 选定的OS文件:入UNIX服务器上的 cron任务、用户和组的配置、管理的脚本、sudo规则等。

5 数据备份和数据恢复方案介绍

5.1 输出outfile文件

使用 select into outfile 方式实现数据的备份和还原
具体的操作步骤如下:

# 选择对应的数据库
mysql> use attend;  
Database changed

# 查询需要备份的数据
mysql> select * from userinfo where id < 10000;
+----+----------+------------------+---------+
| id | usercode | username         | usersex |
+----+----------+------------------+---------+
|  1 | 374532   | 翁智华_attend    |       1 |
|  2 | 123456   | 小度             |       0 |
+----+----------+------------------+---------+
2 rows in set (0.01 sec)

# 选择备份的数据(可以精确条件),应该有两条数据,注意备份的地址具备write权限
mysql> select * from userinfo where id < 10000 into outfile '/Users/Brand/Downloads/tmp/userinfo.txt' ;

# 检查文件是否存在
brand@MacBook-Pro ~ %  cd /Users/Brand/Downloads/tmp/

# 因为它是文本模式,所以我们使用 load data infile 恢复,并且在恢复之前先删除掉要恢复的数据,做个测试
mysql> delete from userinfo where id < 10000;
mysql> load data infile '/Users/Brand/Downloads/tmp/userinfo.txt' into table userinfo;

5.2 使用工具进行备份与还原

可以使用类似 mysqldump工具 或者 mysqlhotcopy工具对数据进行备份和还原,也可以使用免费的热备份软件 Percona XtraBackup。
这边以 mysqldump 为例子演示温备的实现:

5.2.1 备份基本语法

mysqldump -h主机 -P端口 -u用户名  -p密码 param1, param2, param3... > bak_filename.sql

这边对各个字段坐下说明:

  • h:登录用户所在的主机名称
  • P:主机端口
  • u:登录用户用户名
  • p:用户密码
  • param:导出参数(库、表、加锁等参数)
  • ">":将备份数据表的定义和数据写入备份文件的定义
  • bak_filename.sql:备份的文件名

5.2.2 导出全部数据库

–all-databases 或者 -A

mysqldump -uroot -p123456  --all-databases  >  /user/brand/db_bak/all.sql
mysqldump -uroot -p123456  -A  > /user/brand/db_bak/all.sql

5.2.3 导出部分数据表

-databases [dbname,[dbname...]] --tables [tbname,[tbname...]] ,如果多个表where条件相同,也可以组合在一起使用:

mysqldump -uroot -p123456 --databases db1  --tables tb1 --where="id>1000"  > /user/brand/db_bak/db1_tb1.sql

5.2.4 创建之前先删库或表

–add-drop-database 、 –add-drop-table

  • 在create database 前先 drop database;在create table之前先 drop table
  • 默认关闭,所以一般在导入时需要保证数据库已存在。。
mysqldump -uroot -p123456  -A --add-drop-database --skip-add-drop-table >  /user/brand/db_bak/all.sql

5.2.5 锁表

–add-locks:备份数据库表时锁定数据库表,默认就是打开的状态,可以使用–skip-add-locks取消

# 不佳参数选项的时候,默认是添加LOCK的
mysqldump -uroot -p123456  -A  >  /user/brand/db_bak/all.sql

# 取消LOCK的状态
mysqldump -uroot -p123456  -A --skip-add-locks   > /user/brand/db_bak/all_skip_lock.sql

5.2.6 进行压缩

–compact:压缩模式,去掉注释、头尾等结构信息,让输出更少

mysqldump -uroot -p123456  -A --compact >  /user/brand/db_bak/all_compact.sql

5.2.7 数据恢复

使用mysql命令进行恢复,语法如下

mysql -u user -p pwd [dbname] < bak_filename.sql

注意箭头方向

# 删除数据库,模拟数据库损坏
mysql> drop database db1;

# 导入完全备份的文件
mysql < /user/brand/db_bak/all_compact.sql

6 总结

备份和恢复主要使用在以下几个方面:

  • 灾难恢复
  • 需求的变更或者版本回滚
  • 数据和变更审计
  • 多版本测试
这篇关于MySQL全面瓦解30:备份与恢复的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!