MySQL 备份计划，mysqldump以及xtranbackup的实现原理

(1)备份计划

视库的大小来定，一般来说 100G 内的库，可以考虑使用 mysqldump 来做，因为 mysqldump更加轻巧灵活，备份时间选在业务低峰期，可以每天进行都进行全量备份(mysqldump 备份出来的文件比较小，压缩之后更小)。100G 以上的库，可以考虑用 xtranbackup 来做，备份速度明显要比 mysqldump 要快。一般是选择一周一个全备，其余每天进行增量备份，备份时间为业务低峰期。

(2)备份恢复时间

物理备份恢复快，逻辑备份恢复慢 这里跟机器，尤其是硬盘的速率有关系，以下列举几个仅供参考 20G的2分钟（mysqldump） 80G的30分钟(mysqldump) 111G的30分钟（mysqldump) 288G的3小时（xtra) 3T的4小时（xtra) 逻辑导入时间一般是备份时间的5倍以上

(3)备份恢复失败如何处理

首先在恢复之前就应该做足准备工作，避免恢复的时候出错。比如说备份之后的有效性检查、权限检查、空间检查等。如果万一报错，再根据报错的提示来进行相应的调整。

(4)mysqldump和xtrabackup实现原理

mysqldump

mysqldump 属于逻辑备份。加入—single-transaction 选项可以进行一致性备份。后台进程会先设置 session 的事务隔离级别为 RR(SET SESSION TRANSACTION ISOLATION LEVELREPEATABLE READ)， 之后显式开启一个事务(START TRANSACTION /!40100 WITH CONSISTENTSNAPSHOT /)，这样就保证了该事务里读到的数据都是事务事务时候的快照。之后再把表的数据读取出来。 如果加上—master-data=1 的话，在刚开始的时候还会加一个数据库的读锁 (FLUSH TABLES WITH READ LOCK),等开启事务后，再记录下数据库此时 binlog 的位置(showmaster status)，马上解锁，再读取表的数据。等所有的数据都已经导完，就可以结束事务

Xtrabackup

xtrabackup 属于物理备份，直接拷贝表空间文件，同时不断扫描产生的 redo 日志并保存下来。最后完成 innodb 的备份后，会做一个 flush engine logs 的操作(老版本在有 bug，在5.6 上不做此操作会丢数据)，确保所有的 redo log 都已经落盘(涉及到事务的两阶段提交 概念，因为 xtrabackup 并不拷贝 binlog，所以必须保证所有的 redo log 都落盘，否则可能会丢最后一组提交事务的数据)。这个时间点就是 innodb 完成备份的时间点，数据文件虽然不是一致性的，但是有这段时间的 redo 就可以让数据文件达到一致性(恢复的时候做的事 情)。然后还需要 flush tables with read lock，把 myisam 等其他引擎的表给备份出来，备份完后解锁。 这样就做到了完美的热备。

mysqldump中备份出来的sql，如果我想sql文件中，一行只有一个insert….value()的话，怎么办？如果备份需要带上master的复制点信息怎么办？

  1. --skip-extended-insert
  2. [root@helei-zhuanshu ~]# mysqldump -uroot -p helei --skip-extended-insert
  3. Enter password:
  4.   KEY `idx_c1` (`c1`),
  5.   KEY `idx_c2` (`c2`)
  6. ) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=latin1;
  7. /*!40101 SET character_set_client = @saved_cs_client */;
  9. --
  10. -- Dumping data for table `helei`
  11. --
  13. LOCK TABLES `helei` WRITE;
  14. /*!40000 ALTER TABLE `helei` DISABLE KEYS */;
  15. INSERT INTO `helei` VALUES (1,32,37,38,'2016-10-18 06:19:24','susususususususususususu');
  16. INSERT INTO `helei` VALUES (2,37,46,21,'2016-10-18 06:19:24','susususususu');
  17. INSERT INTO `helei` VALUES (3,21,5,14,'2016-10-18 06:19:24','susu');

如何从mysqldump产生的全库备份中只恢复某一个库、某一张表？

  1. # 全库备份
  2. [root@HE1 ~]# mysqldump -uroot -p --single-transaction -A --master-data=2 >dump.sql
  4. # 只还原erp库的内容
  5. [root@HE1 ~]# mysql -uroot -pMANAGER erp --one-database <dump.sql
  7. # 可以看出这里主要用到的参数是--one-database简写-o的参数，极大方便了我们的恢复灵活性
  9. # 那么如何从全库备份中抽取某张表呢，全库恢复，再恢复某张表小库还可以，大库就很麻烦了，那我们可以利用正则表达式来进行快速抽取，具体实现方法如下：
  11. # 从全库备份中抽取出t表的表结构
  12. [root@HE1 ~]# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `t`/!d;q' dump.sql
  14. DROP TABLE IF EXISTS`t`;
  15. /*!40101 SET@saved_cs_client     =@@character_set_client */;
  16. /*!40101 SETcharacter_set_client = utf8 */;
  17. CREATE TABLE `t` (
  18.   `id` int(10) NOT NULL AUTO_INCREMENT,
  19.   `age` tinyint(4) NOT NULL DEFAULT '0',
  20.   `name` varchar(30) NOT NULL DEFAULT '',
  21.   PRIMARY KEY (`id`)
  22. ) ENGINE=InnoDBAUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
  23. /*!40101 SETcharacter_set_client = @saved_cs_client */;
  25. # 从全库备份中抽取出t表的内容
  26. [root@HE1 ~]# grep'INSERT INTO `t`' dump.sql
  27. INSERT INTO `t`VALUES (0,0,''),(1,0,'aa'),(2,0,'bbb'),(3,25,'helei');