为mysql寻找最佳备份方法和备份

一、为什么要备份？

    灾难恢复

    需求改变

    测试

二、事先考虑的问题

    可以容忍丢失多长时间的数据？

    恢复要在多长时间内完成？

    是否需要持续提供服务？

    需要恢复什么，整个数据库服务器？单个数据库？一个或多个表？某个语句？

三、备份类型

    根据是否需要数据库离线分为：

      冷备：coldbackup,关闭mysql服务，或不允许读写请求

      温备：warmbackup，备份的同时仅支持读请求

      热备：hotbackup，备份的同时，业务功能不受影响，需要工具和数据库引擎支持

    根据要备份的数据范围可分为：

      完全备份：fullbackup，备份全部数据集，服务器、数据库等根据关注点觉定

      增量备份：incrementalbackup，上次完全备份或增量备份以来改变了的数据；

      差异备份：differentialbackup，上次完全备份以来改变了的数据

    根据备份数据或是文件，可以分为：

      物理备份：直接备份数据文件

      逻辑备份：备份表中数据和库代码等

四、备份对象：

    数据：

    配置文件：经常备份/etc目录

    代码：存储过程、存储函数、触发器等；

    OS相关的配置文件：

    二进制日志：

例一：使用SELECT INTO OUTFILE备份恢复单表

1

2

3

例二：mysqldump的MySQL客户端工具使用

  mysqldump可以备份整个服务器，单个或部分数据库，单个或部分表，表中某些行，存储过程，存储函   数，触发器

  能自动记录备份时的二进制日志文件及相应position

    -uusername -hhostname  -ppassword

    -B, --databasesdbname 备份指定数据库

    -x，--lock-all-tables 锁定所有表

    -l，--lock-tables 锁定单张表

    --master-data=[0|1|2] 0不启用，1启用，2记录正在访问的二进制日志并记录访问点

    --single-transaction 基于此选项能实现InnoDB热备，不需要同时使用--lock-all-tables;

    -A,--all-databases;备份整个服务器上的所有库

1

2

3

4

5

6

  注：恢复时注意当前所在库，如果原库不存在请新建并use.

  myslqdump热备-->   完全备份 + 增量备份 + 二进制 

1

2

3

4

5

6

 备份恢复需要mysql服务器离线，

1

2

3

4

5

  注：也可以使用mysql -uroot -pmypass < /backup/filename.sql 输入重定向恢复

例三：使用二进制日志操作

1

2

3

4

5

  误操作恢复：

1

2

3

1

2

3

  注：在恢复的过程中如果继续有二进制日志写入，则需FLUSH LOGS之后再恢复。

　常见的备份方式

　　MySQL本身为我们提供了mysqldump、mysqlbinlog远程备份工具，percona也为我们提供了强大的Xtrabackup， 加上开源的mydumper，还有基于主从同步的延迟备份、从库冷备等方式，以及基于文件系统快照的备份，其实选择已经多到眼花缭乱。而备份本身是为了恢 复，所以能够让我们在出现故障后迅速、准确恢复的备份方式，就是最适合我们的，当然，同时能够省钱、省事，那就非常完美。下面就我理解的几种备份工具进行 一些比较，探讨下它们各自的适用场景。

  　1. mysqldump& mydumper

　　mysqldump是最简单的逻辑备份方式。在备份myisam表的时候，如果要得到一致的数据，就需要锁表，简单而粗暴。而在备份innodb表 的时候，加上–master-data=1–single-transaction 选项，在事务开始时刻，记录下binlog pos点，然后利用mvcc来获取一致的数据，由于是一个长事务，在写入和更新量很大的数据库上，将产生非常多的undo，显著影响性能，所以要慎用。

   　2. 基于文件系统的快照

　　基于文件系统的快照，是物理备份的一种。在备份前需要进行一些复杂的设置，在备份开始时刻获得快照并记录下binlog pos点，然后采用类似copy-on-write的方式，把快照进行转储。转储快照本身会消耗一定的IO资源，而且在写入压力较大的实例上，保存被更改 数据块的前印象也会消耗IO，最终表现为整体性能的下降。而且服务器还要为copy-on-write快照预留较多的磁盘空间，这本身对资源也是一种浪 费。因此这种备份方式我们使用的不多。

  　3.Xtrabackup

这或许是最为广泛的备份方式。percona之所以家喻户晓，Xtrabackup应该功不可没。它实际上是物理备份+逻辑备份的组合。在备份 innodb表的时候，它拷贝ibd文件，并一刻不停的监视redo log的变化，append到自己的事务日志文件。在拷贝ibd文件过程中，ibd文件本身可能被写”花”，这都不是问题，因为在拷贝完成后的第一个 prepare阶段，Xtrabackup采用类似于innodb崩溃恢复的方法，把数据文件恢复到与日志文件一致的状态，并把未提交的事务回滚。如果同 时需要备份myisam表以及innodb表结构等文件，那么就需要用flush tables with lock来获得全局锁，开始拷贝这些不再变化的文件，同时获得binlog位置，拷贝结束后释放锁，也停止对redo log的监视。

  由于mysql中不可避免的含有myisam表，同时innobackup并不备份表结构等文件，因此想要完整的备份mysql实例，就少不了要执行 flush tables with read lock，而这个语句会被任何查询(包括select)阻塞，在阻塞过程中，它又反过来阻塞任何查询(包括select)。如果碰巧备份实例上有长查询先 于flush tables with read lock执行，数据库就会hang住。而当flush tables with read lock获得全局锁后，虽然查询可以执行，但是仍会阻塞更新，所以，我们希望flush tables withread lock从发起到结束，持续的时间越短越好。

为了解决这个问题，有两种比较有效的方法：

　　1. 尽量不用myisam表。

　　2. Xtrabackup增加了–rsync选项，通过两次rsync来减少持有全局锁的时间。

  优点：在线热备，全备+增备+流备，支持限速，支持压缩，支持加密。

　　缺点：需要获取全局锁，如果遇到长查询，等待时间将不可控，因此要做好监控，必要时杀死长查询或自杀;遇到超大的实例，备份过程较长，redo log太大会影响恢复速度，这种情况下最好采用延迟备份。

　　4. mysqlbinlog 5.6

　　上述所有的备份方式，都只能把数据库恢复到备份的某个时间点：mysqldump和mydumper，以及snapshot是备份开始的时间 点;Xtrabackup是备份结束的时间点。要想实现point in time的恢复，还必须备份binlog。同时binlog也是实现增备的宝贵资源。

  　　blackhole对于备份binlog是极好的。一方面可以长久的备份binlog用于恢复数据库，另一方面，在其上配置半同步复制，可以有效防止主库的binlog丢失。

  备份策略一：直接拷贝数据库文件（不推荐）

备份策略二：使用mysqlhotcopy备份数据库（完全备份，适合小型数据库备份）

备份策略三：使用mysqldump备份数据库（完全+增量备份，适合中型数据库备份）

备份策略四：使用主从复制机制(replication)（实现数据库实时备份）

考？mysqldump做的备份，数据还原时，是否会产生二进制日志？

     mysql> SET SESSION sql_log_bin=0;

     mysql> SOURCE /path/from/somefile.sql;

     mysql> SET SESSION sql_log_bin=1;

本文出自 “” 博客，请务必保留此出处