MySQL数据库-备份及恢复快速上手-优快云博客

今天给伙伴们分享一下MySQL数据库-备份及恢复，希望看了有所收获。

我是公众号「想吃西红柿」「云原生运维实战派」作者，对云原生运维感兴趣，也保持时刻学习，后续会分享工作中用到的运维技术，在运维的路上得到支持和共同进步！

如果伙伴们看了文档觉得有用，欢迎大家关注我的公众号，获取相关文档。爱运维，爱生活。

一、MySQL备份概述

1、为什么要备份

能够防止由于机械故障以及人为误操作带来的数据丢失，例如将数据库文件保存在了其它地方。
冗余：数据有多份冗余，但不等备份，只能防止机械故障还来的数据丢失，例如主备模式、数据库集群。
交易性，钱国家合规要求 180 数据银行终生磁带，分布式存储 oss 归档存储

2、备份必须重视的内容

备份内容 databases　Binlog my.conf
所有备份数据都应放在非数据库本地，而且建议有多份副本。
测试环境中做日常恢复演练，恢复较备份更为重要。

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

数据的一致性数据在高负载的情况下不适合备份
服务的可用性备份开始锁表（只读模式）

4、MySQL 备份类型

1、物理备份

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

1、热备(hot backup)

在线备份，数据库处于运行状态，这种备份方法依赖于数据库的日志文件
对应用基本无影响(应用程序读写不会阻塞,但是性能还是会有下降,所以尽量不要在主上做备份,在从库上做)

2、冷备(cold backup)

备份数据文件,需要停机，是在关闭数据库的时候进行的
备份 datadir 目录下的所有文件文件备份，cp文件

3、温备(warm backup)

针对myisam的备份(myisam不支持热备),备份时候实例只读不可写，数据库锁定表格（不可写入但可读）的状态下进行的
对应用影响很大
通常加一个读锁

2、逻辑备份

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

3、物理和逻辑备份的区别

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

4、备份方式的选择

从以下几个维度考虑备份方式
- 备份速度
- 恢复速度
- 备份大小
- 对业务影响

5、MySQL 备份工具

1、ibbackup

官方备份工具
收费
物理备份

2、xtrabackup

开源社区备份工具
开源免费,上面那东西的免费版本(老版本有问题,备份出来的数据可能有问题)
物理备份

3、mysqldump

官方自带备份工具开源免费
逻辑备份(速度慢)
不阻塞dml,阻塞ddl

4、mysqlbackup

mysql 官方备份工具
innodb 引擎的表mysqlbackup可以进行热备
非innodb表mysqlbackup就只能温备
物理备份，备份还原速度快
适合大规模数据使用

6、MySQL 备份策略

数据库备份方案：

全量备份
增量备份
差异备份

备份方案	特点
全量备份	全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。数据恢复快。备份时间长
增量备份	增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。没有重复的备份数据备份时间短恢复数据时必须按一定的顺序进行
差异备份	备份上一次的完全备份后发生变化的所有文件。差异备份是指在一次全备份后到进行差异备份的这段时间内对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。

二、MySQL逻辑备份 mysqldump

1、mysqldump 简介

mysqldump 是 MySQL 自带的逻辑备份工具。可以保证数据的一致性和服务的可用性。
它的备份原理是通过协议连接到 MySQL 数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的 insert 语句，当我们需要还原这些数据时，只要执行这些 insert 语句，即可将对应的数据还原。

2、备份命令

1、命令格式

mysqldump [选项] 数据库名 [表名] > 脚本名

或

mysqldump [选项] --数据库名 [选项 表名] > 脚本名

或

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

2、选项说明

参数名	缩写	含义
–host	-h	服务器IP地址
–port	-P	服务器端口号
–user	-u	MySQL 用户名
–pasword	-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		备份触发器
–routines	-R	备份存储过程和存储函数

3、备份实例

备份所有数据库：

[root@edenluo.com ~]# mysqldump -uroot -p --all-databases > /backup/mysqldump/all.db

备份指定数据库：

[root@edenluo.com ~]# mysqldump -uroot -p --databases test > /backup/mysqldump/test.db

备份指定数据库指定表(多个表以空格间隔)

[root@edenluo.com ~]# mysqldump -uroot -p  mysql db event > /backup/mysqldump/2table.db

备份指定数据库排除某些表

[root@edenluo.com ~]# mysqldump -uroot -p test --ignore-table=test.t1 --ignore-table=test.t2 > /backup/mysqldump/test2.db
[root@edenluo.com ~]# mysqldump -uroot -p'1234' test --ignore-table=test.t9 --set-gtid-purged=OFF --single-transaction --master-data=1 --flush-logs > test1.sql