MySQL数据库备份与恢复详解附实例

一、备份作用

1.备份

  能够防止由于机械故障以及人为误操作带来的数据丢失，例如将数据库文件保存在了其它地方。比如把文件拷贝到U盘里，电脑出故障，数据还在。

2.冗余

  数据有多份冗余，但不等备份，只能防止机械故障带来的数据丢失，例如主备模式、数据库集群。类似于复制文件到本机上，电脑死机仍恢复不了。

二、备份内容

databases　Binlog my.conf
  所有备份数据都应放在非数据库本地，而且建议有多份副本。测试环境中做日常恢复演练，恢复较备份更为重要。
  备份过程中必须考虑因素：数据的一致性和服务的可用性

三、备份类型

1.物理备份

  直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本。

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

2）冷备
  备份数据文件,需要停机，是在关闭数据库的时候进行的。

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

2.逻辑备份

  备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库，效率相对较低。

3.区别

	逻辑备份	物理备份
备份方式	备份数据库建表、建库、插入sql语句	备份数据库物理文件
优点	备份文件相对较小,只备份表中的数据与结构	恢复速度比较快
缺点	恢复速度较慢(需要重建索引,存储过程等)	备份文件相对较大(备份表空间,包含数据与索引)
对业务影响	I/O负载加大	I/O负载加大
代表工具	mysqldump	ibbackup、xtrabackup，mysqlbackup

四、备份工具

1.ibbackup
官方备份工具,收费,物理备份

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

3.mysqldump
官方自带备份工具,开源免费,逻辑备份(速度慢)

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

五、物理备份方式

1.完整备份（完全备份）

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

优点：备份与恢复操作简单方便，恢复时一次恢复到位，恢复速度快。
缺点：占用空间大，备份速度慢。

2.增量备份

  每次备份上一次备份到现在产生的新数据。
  只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次 完整备份或上次的增量备份的时间为时间点，仅备份这之间的数据变化。

特点：
  因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从 上一次的完整备份起按备份时间顺序，逐个备份版本进行恢复，恢复时间长，如中 间某次的备份数据损坏，将导致数据的丢失。

3.差异备份

  只备份跟完整备份不一样的
  备份那些自从第一次次完全备份之后被修改过的所有文件，备份的时间起点 是从第一次次完整备份起，且以后每次备份都是和第一次完整备份进行比较（注意 是第一次，不是上一次），备份自第一次完整备份以来所有的修改过的文件。备份数据量会越来越大。

特点：
  占用空间比增量备份大，比完整备份小，恢复时仅需要恢复第一个完 整版本和最后一次的差异版本，恢复速度介于完整备份和增量备份之