设计MySQL备份方案

    细节之前的建议：

 

 

最大的权衡是备份时间与备份负载。提高备份的优先级，代价是降低服务器性能。

 

  • 在生产实践中，大数据库来说，物理备份是必须的：逻辑备份太慢并且受资源限制，逻辑备份中恢复需要很长时间。基于快照的备份，例如Perconna XtraBackup和MySQL Enterprise Backup是最好的选择。
  • 保留多个备份集
  • 定期从逻辑备份（或者物理备份）中抽取数据进行恢复测试
  • 保存二进制日志以用于基于故障时间点的恢复。expire_logs_days应该设置的足够长.至少大于全量备份的间隔时间。
  • 完不借助备份工具本身来监控备份和备份的过程。需要另外验证备份是否正常。
  • 通过演练整个恢复过程来测试备份和恢复。测算恢复所需要的资源（CPU、磁盘空间、实际时间，以及网络带宽等）
  • 对安全性要仔细考虑。
  • 在线备份还是离线备份
         规划备份是，有一些与性能相关的因素需要考虑：
  • 锁时间：需要持有锁多长时间，例如备份期间持有的全局FLUSH TABLES WITH READ LOCK?
  • 备份时间： 复制备份到目的地需要多久
  • 备份负载：在复制备份到目的地时对服务器性能的影响有多少
  • 恢复时间：把备份镜像从存储位置复制到MySQL服务器，重放二进制日志需要多久？

逻辑备份还是物理备份

     逻辑备份（也叫"导出"）和直接复制原始文件的物理备份。

 

逻辑备份有如下的优点：

逻辑备份可以使用编辑器或grep和sed之类的命令查看和操作的普通文件。 恢复非常简单。直接导入。 可以通过网络来备份和恢复 可以在类似Amazon RDS这样不能访问底层文件系统中使用 非常灵活，因为mysqldump大部分人喜欢的工具. 与存储引擎无关 有助于避免数据损坏，物理备份可能磁盘损坏

逻辑备份的缺点：

 

最大的缺点是恢复，测试恢复所需要的时间将非常重要.

 

物理备份有如下好处：

 

 

事实上，逻辑备份最可怕的地方就是不确定的还原时间。

物理备份的缺点：

  • 必须由数据库服务器完成生成逻辑备份的工作，因此要使用更多的cpu周期
  • 逻辑备份在某些场景下笔数据库文件本身更大
  • 基于文件的物理备份，只需要将需要的文件复制到其他地方即可完成备份。不需要其他额外的工作来生成原始文件
  • 恢复更简单，取决于存储引擎。对于MyISAM复制到目的地即可。InnoDB需要停止数据库服务采取其他的步骤
  • InnoDB和MyISAM的物理备份非常容易跨平台、操作系统和MySQL版本
  • 从屋里备份中恢复会更快，因为不需要执行任何SQL或构建索引
  • InnoDB的原始文件通常比相应的逻辑备份要大的多。InnoDB的表空间包含很多未使用的空间。还有很多空间被用来做存储数据意外的用途（插入缓冲、回滚段等）
  • 物理备份不总是可以跨平台、操作系统以及MySQL版本。文件名大小写敏感和浮点格式是可能会遇到的麻烦。很可能因浮点格式不同而不能移动文件到另一个系统

备份什么

     恢复的需求决定需要备份什么，最简单的测率是指备份数据和表定义

下面是MySQL备份需要考虑的几点：

  • 非显著数据： 容易被忽略的数据，例如二进制日志和InnoDB事务日志
  • 代码 ： 触发器和存储过程
  • 复制配置： 有复制关系的。二进制日志、中继日志、日志索引文件和info文件
  • 服务器配置： 配置文件
  • 选定的操作系统文件管理脚本等

 

郑亮备份和差异备份：

     差异备份是对自上次全备份后所有改变的部分而做的备份，而增量备份则是自从任意类型的上次备份后所有修改做的备份

下面是一些建议：

  • 使用Percona XtraBackup和MySQL Enterprise Backup中的增量备份特性
  • 备份二进制日志。
  • 不要备份没有改变的表。
  • 不要备份没有改变的行
  • 某些数据根本不需要备份
  • 备份所有的数据，然后发送到一个有去重特性的目的地。

 

增量备份的缺点包括增加恢复复杂性，额外的风险，以及更长的恢复时间。如果可全备，考虑到简便性，我们建议尽量做全备。

 

存储引擎和一致性

     数据一致性：只要服务器上使用REPEATABLE READ事务隔离级别，并且没有任何DDL，就一定会有完美的一致性，以及基于时间点的数据快照，且在备份过程中不会阻塞任何后续的工作。

      文件一致性:如果是MyISAM，可能是锁住并刷新表。

  复制：从悲苦中备份最大的好处是可以不干扰主库，避免在主库上增加额外的负载。

 

⑤备份数据：
     -- 生成逻辑备份：逻辑备份有 SQL导出和符号分隔文件
          SQL导出；phpMyAdmin,Navicat、mysqldump等工具都可以做
          缺点：巨大的sql语句、单个巨大文件，成本高
          符号分隔文件备份(比上面更优)：
          
          缺点：仅用于MySQL、写权限(一般root不担心)、不覆盖存在文件
          
     -- 文件快照：使用LVM规划快照
          
          注：快照不等同于备份

⑥从备份中恢复：
     -- 恢复物理备份：InnoDB有诸多限制，而MyISAM则可直接将物理备份复制粘贴过去。
          注：准备物理备份时需同时准备逻辑备份。
          
     -- 还原逻辑备份：
          SQL文件命令导入：  
               $ mysql < sakila-backup.sql
               ---------------------------------------------------
               mysql中：SET SQL_LOG_BIN = 0;
                              SOURCE sakila-backup.sql
                              SET SQL_LOG_BIN = 0;
               注：上面两种方法均性可，相比较source报错不强退，不严谨。
               若备份压缩过，则：
                    $ gunzip -c sakila-backup.sql.gz | mysql
               若想恢复单个表则：
                    $ grep 'INSERT INTO `actor`' sakila-backup.sql | mysql sakila   或  $ gunzip -c sakila-backup.sql.gz | grep 'INSERT INTO `actor`' | mysql sakila

          加载符号分隔文件：
               优化技巧：创建一个命名管道并将解压数据流流向里面
               

     -- 基于时间点的恢复：只要有二进制日志文件，就可以恢复任何希望的时间点。