mysql 整理之mysql 备份和恢复

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

（1）备份计划

（2）备份恢复时间

（3）备份恢复失败如何处理

原理：

mysqldump

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

Xtrabackup:

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

备份计划：

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

100G以上的库，可以考虑用xtranbackup来做，备份速度明显要比mysqldump要快。一般是选择一周一个全备，其余每天进行增量备份，备份时间为业务低峰期。

备份恢复时间：

物理备份恢复快，逻辑备份恢复慢

备份恢复失败如何处理：

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

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

1).--skip-extended-insert

2).--master-date=1

 

7.3    xtrabackup是如何做到带上master的复制点的信息的？

因为xtrabackup是多线程，一个线程不停地在拷贝新产生的redo文件，另外的线程去备份数据库，当所有表空间备份完成的时候，它会执行flushtable with read lock操作锁住所有表，然后执行showmaster status; 接着执行flushengine logs; 最后解锁表。执行showmaster status; 时就能获取到mster的复制点信息，执行flush engine logs 强制把redo文件刷新到磁盘。

 

7.4 MySQL数据库备份方式有那几种（只讨论InnoDB存储引擎），至少写四种。

1、关服务，直接拷贝ibd、frm、redolog、my.cnf

2、select ... into outfile;对应load data infile 恢复

   fileds terminated by 'x';每个列的分隔符。默认'\t'

   optionally encolsed by 'x';字符串的包含符。默认''

   escaped by 'x':转义符，默认为'\\'

   starting by 'x';每行的开始符。默认''

   terminated by 'x':每行结束符。默认'\n'

 

3、mysqldump:对应mysql恢复

  single-transaction:备份开始先执行start transaction

     但不能有DDL操作，否则无法保证一致性读

  master-data:如果没有指定single-transaction,则用lock-all-tables

      1:显示masterstatus，并且changemaster

      2:只显示change，但不执行

 

mysqlimport:与load data infile类似，但支持导入多个表，表之间并发导入

 

4、二进制日志binlog备份

    通过mysqlbinlog命令从binlog提取sql

 

5、xtrabackup

    先记录当前redo位置

    然后拷贝共享表空间和独立表空间数据

    最后根据redo日志和开始位置，重做redo

 

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

从全库中抽取一个库内容：--one-database简写成 –o 

mysql -uroot-proot databasename --one-database <dump.sql

从全库中抽取一个表的内容：grep'INSERT INTO `tablename`' dump.sql