MySQL

yii2 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、f

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