MySQL存储引擎及备份恢复

MYSQL存储引擎(常用innodb、myisam)

　　1、建表的时候可以指定引擎

　　2、引擎决定了表的存储方式

　　3、不同引擎的表对应不同的功能和特性

　　4、最常用的是innodb引擎

　　　　　MVCC、支持事务、行锁、外键、写不阻塞读、redo（支持脏写、快速提交）

　　增删改速度快，commit时候卡住了的原因：

　　　　写有问题（只有commit的时候是真正写）

 

 

MYSQL备份

1、根据备份的方法不同，可以将备份分为冷备（offline backup）和热备（online backup）

　　热备指数据库运行时直接备份，对正在运行的数据库操作没有任何影响

　　冷备指数据库停止的情况下，一般只需要复制相关的数据库物理文件即可

 

2、按照备份后文件的内容，备份又可以分为逻辑备份和物理备份

　　物理备份：将数据页拷贝出来（xtrabackup工具）

　　逻辑备份：将数据行拷贝出来

 

备份会生产的影响：

　　1、备份对io的影响（备份会严重的占用io，必然会影响生产），特别是物理备份

　　2、备份占用网络资源（从服务器拿走时，占用带宽）

　　3、备份会产生锁的问题

　　　　备份对生产影响很大，将备份卸载到从库

逻辑备份

　　1、工具：mysqldump

mysqldump --help

　　　　1、登录信息 -S -u -p -h -P

　　　　2、-F 切换一下binlog

　　　　3、-l：锁表（锁myisam表）

　　　　4、--single-transaction（不支持ddl）

　　　　5、--skip-lock-tables

　　mysqldump -uroot -p123456 -S /data/mysql.sock test

　　默认情况下、备份期间所有的表都被锁住，而且是排它锁，增删改查全被阻塞

　　2、锁

　　　　-l：保证每个库是一致的，但是对于不同的数据库，数据是不一致的

　　　　-x：保证每一个所有的库和表是一致的

　　　　加的是读锁

　　　　--single-transaction 保证innodb所有表数据的一致性

 

注意：binlog恢复的起点永远是备份开始的那个时刻

 

现在几乎所有的系统都在用innodb引擎，因此我们使用--single-transaction来实现数据的备份

 

不锁表，说的是不锁dml和select，但是对应备份期间的ddl操作还是会阻塞

 

恢复原理：

　　innodb表

　　　　1、备份一个一致性时刻（MVCC特性）

　　　　2、使用binlog追加恢复（备份的时刻到想恢复的时刻）

　　myisam表

　　　　加锁 

对于mysqldump最重要的是如何确认备份的起点时间

　　1、备份文件的建立时间

　　2、binlog的切换时间（强行切换时间，文件大小一定不是1G）

 

 

逻辑备份

　　1、备份速度慢（连接数据库，将数据一行一行导出来，对表做全表扫描）

　　2、恢复速度慢

　　MVCC特性 --single-transaction

myisam需要全局锁表，使用 -x选项

核心点：知晓恢复binlog的起点

　　1、如何知道binlog里面的日志包含的时间范围

　　2、-F 强制

 

数据库的升级和迁移非常适合使用逻辑备份

利用SQL是标准的这个特性

数据更是标准

 

如何实现逻辑备份的并行处理？

　　使用手工的分拆，实现库和表并行备份、并行恢复

　　例如：有40个表、10个大表、16颗cpu

　　　　对于10个大表启动10个备份线程

 

 

恢复案例

　　1、恢复备份+binlog

　　　　1、数据库大约在9点做了一个备份

　　　　2、有两个表t12和t14大约在上午11点被删除

　　　　3、要求将t12和t14找回来

　　　　　　1、确定表被删除的时间(上午11点)

　　　　　　使用binlog最后修改时间来确认每一个binlog里面包含的日志时间段

　　　　　　结合mysqlbinlog -vv|grep -C 20 来确认具体的时间点

　　　　　　2、截取最后一个binlog对应的日志段

　　　　　　3、确定备份开始的时间点，找到对应的binlog，如果使用了-F，

　　　　　　　　很容易确定备份的起点，使用文件建立时间和文件大小

　　　　　　4、直接进行恢复

　　　　　　5、追加binlog进行恢复

 

逻辑备份最大的问题

　　1、不容易确定备份的起点，也就是binlog需要恢复的起点

　　2、恢复速度慢、单线程恢复

　　3、myisam表需要锁表

　　4、备份速度慢

逻辑备份主要用来导数

====================================

物理备份

1、物理备份+binlog

　　myisam表来说 物理备份也是锁表

　　innodb表来说

　　　　物理备份不锁表，备份出来的数据页都是不一致的，根本用不了

　　　　使用备份期间生成的redolog的备份，可以将备份的数据页恢复成备份结束的时刻

　　　　使用备份结束以后对应的binlog，将数据库恢复到对应的时间点

　　　　回滚备份结束时刻对应的事务，因为这些事务是备份结束后提交的，事务日志一定保存在binlog中

 

关于备份的几个点

　　1、压缩 压缩会节省很大的空间，但是会非常消耗CPU

　　2、加密 加密也会消耗CPU

　　3、并行可以加快备份速度

　　　　--parallel= 加大IO和CPU的压力 但是速度很快

　　　　一般的并行度不要超过cpu的core的数量，最大不要超过4倍

　　　　4、限流 --throttle=1000

 

物理备份的原理

　　1、拷贝数据页

　　2、myisam表来说，通过表来实现数据的一致性

　　3、innodb表来说，使用redolog来实现数据的一致性

　　4、备份期间的redolog、applylog（备份完成以后马上可以进行applylog）、rollback

　　5、物理备份需要能够读懂各种info文件

　　6、备份速度快、恢复相对比较容易（info文件的支持）

　　7、并行、限流

　　8、备份增量

　　　　1、在谁的基础上进行增量 全备 增量

　　　　2、增量恢复时，只要没和binlog对接，就一直使用redo-only，不进行回滚，否则恢复失效

　　　　3、和binlog对接时，最好不要加上redo-log，实现自动回滚，如加了redo-log，也没有问题

　　　　4、增量备份适合数据仓库系统，不是很适合在线交易系统

 

增量备份

binlog只有在还原和binlog接壤的地方，才会进行回滚，其他所有地方都不进行回滚

1、增量的含义是自上次备份以来发生改变的数据页

2、增量备份并没有降低IO，也是全扫描

3、增量备份不是很适合OLTP系统

4、增量备份适合数据仓库系统

 

 

设计一套带有全备和增量的备份方案，要求恢复增量不超过2次，尽量降低备份的数据量、备份实现自动化（写脚本）、写出各个时间点的恢复方案，模拟一个删除操作，进行相应的恢复

 

第一天全备、第二天增量、第三天增量、第四天还没有来得及增量，数据库出现问题

1、全备

2、apply-log+redo-only

3、增量

4、增量

5、将3和4对应增量追加到全备中去

6、使用最新的全备中的xtrabackup_info文件中指定的binlog的新位置，开始跑binlog，一直到最新的

 

备注： 从库上进行备份的时候，最好将主从的应用线程给关闭

 

--incremental //指定进行增量

--incremental-basedir=name //指定全备位置（也可能是增量备份）

--incremental-dir=name //

 

 

增量需要指定在谁的基础上增量

1、可以在全备的基础上进行增量

2、也可以在增量的基础上进行增量

全备+apply log(redo only)+增量备份

第一步 （全备）

innobackupex --user=root --password=123456 --no-timestamp /backup

全备一般不使用时间戳目录

[root@localhost backup]# cat xtrabackup_info（看懂以下信息）

第二步（加上redo-only）

备份完成以后，接着做一个下面的动作，因为后面会有增量，所以这个地方需要加上redo-only

innobackupex --apply-log --redo-only(不会滚) /backup

第三步(也就是第二天，在全备的基础上做的增量) 做一些insert

innobackupex --incremental --incremental-basedir=/backup/ --user=root --password=123456 /backup/increment/

第四步(也就是第三天，在第二天增量的基础上做的增备)做一些insert

innobackupex --incremental --incremental-basedir=/backup/increment/2016-12-22_05-28-54/ --user=root --password=123456 /backup/increment/

第五步（在全备的基础上做增量）

innobackupex --incremental --incremental-basedir=/backup/ --user=root --password=123456 /backup/increment/

第四天的时候，数据库出现问题了

 

恢复演练

一、将第二天的增量追加到全备上

innobackupex --apply-log --redo-only --incremental /backup/ --incremental-dir=/backup/increment/2016-12-30_16-58-03

二、将第三天的增量追加到新的全备上

innobackupex --apply-log --redo-only --incremental /backup/ --incremental-dir=/backup/increment/2016-12-30_17-01-25

三、使用全备的xtrabackup_info中提示binlog的位置，继续跑binlog，跑到合适的点位置

跑binlog的两种方式：

1、mysqlbinlog -vv ...|mysql -uroot -p

2、先mysqlbinlog -vv ... >d1.sql 然后 mysql -uroot -p <d1.sql

对于binlog，我们要学会使用--start-position和--stop-position进行日志的截取

 

数据库服务器时间

1、同步

2、准时

负载很重的服务器的时间通常会慢！

数据库服务器的时间最好和标准时间同步，因为应用很可能会访问类似于now（）这样的函数，来记录一些时间点

数据库服务器的时间可以往新的时间调，也就是慢了可以调的快一点，但快了不能往慢了调