![1493217769625452.png blob.png](/ueditor/php/upload/image/20170426/1493217769625452.png)
![1493217821326654.png blob.png](/ueditor/php/upload/image/20170426/1493217821326654.png)
![1493217884897396.png blob.png](/ueditor/php/upload/image/20170426/1493217884897396.png)
![1493218177225904.png blob.png](/ueditor/php/upload/image/20170426/1493218177225904.png)
![1493218295952433.png blob.png](/ueditor/php/upload/image/20170426/1493218295952433.png)
· 不完全备份(上面少写了)
冷备:把机器关掉 copy数据库文件
热备(hot backup):在数据是运行中,进行的备份
增倍:在上一次备份的基础上做一个增加的备份,今天相对于昨天的变更备份
不完全备份:只备份一张表两张表
![1493218496923559.png blob.png](/ueditor/php/upload/image/20170426/1493218496923559.png)
ntsqldump单进程不太完美
mydumper多进程 sql层备份
xtrabackup基本上大家都在用
在有slave的情景下为什么还要有备份?
人为误操作 在主库上truncate table 从库上也会执行truncate table
mysql 5.6引入delay replication
可以指定从库和主库延迟多长时间 比如说一小时 一小时内主库上有误操作可以不同步到从库上
sql_thread 执行同步 不应用就行了
![1493218904624650.png blob.png](/ueditor/php/upload/image/20170426/1493218904624650.png)
只备份一个表的话只有select权限就可以 如果要一致性就要有lock tables权限
涉及到存储过程和视图就要把show view 和 trigger权限都给上 要不然会报错权限不足
![1493219020686718.png blob.png](/ueditor/php/upload/image/20170426/1493219020686718.png)
造成问题:
-
热数据冲掉
-
冷数据加载 备份时间长
![1493219302576377.png blob.png](/ueditor/php/upload/image/20170426/1493219302576377.png)
–trigger=false(2个减号)
-t –no-create-info(Don't write table createion info. 不要创建schema)
-d 不要数据只要表结构
-R存储过程
-n 不要创建库
-E event
-f 重置备份
建议每个分开备份
特别对于数据文件单独放
如果数据库不超过100G 并且内存又很大 可以使用
如果已经100G 内存只有16G 就不要考虑使用mysqldump了
考虑的场景是内存和数据差不多的场景采取用 如果超过3倍以上就不要考虑了
因为用起来太慢了
![1493220782900122.png blob.png](/ueditor/php/upload/image/20170426/1493220782900122.png)
会有一个问题 如果这个表没有id字段会报错
所以加上where条件的话 要明确这个条件和表是有内容的
mysqldump -t –where="id<10" db1 tb1
这样可以简单的从一个表里拿到一部分去做测试方便拿到子集
DDL不受事务管理 保护不了一致性
一致性快照:拿到一个数据在某一时间一致性的数据 t1时间的备份拿到t1时间的镜像
–set-gtid-purged 会带着gtid的值 表明多少gtid之前的不要了 这样一个标识
默认dump出来的代码:insert into tb1 values(),();
-c参数之后变成:insert into tb1(c1,c2,c3) values(c1,c2,c3);
会变成一个完整的格式,支持对原来表结构进行修改
-h:host
-u :用户
-P:端口号
-p:密码
![1493221229372082.png blob.png](/ueditor/php/upload/image/20170426/1493221229372082.png)
![1493259085491640.png blob.png](/ueditor/php/upload/image/20170427/1493259085491640.png)
不同版本的mysqldump变化很频繁
–replace -> replace into
![1493259470872188.png blob.png](/ueditor/php/upload/image/20170427/1493259470872188.png)
后面再演示
利用mysqldump备份有什么问题?
要注意:
1. 数据的内存的比例
2.mysqldump备份很慢,热数据冲掉冷数据加载时间过程备份时间过长
3.建议只备份数据量小的
4.Innodb表 –single-transanction可以不锁表
5.mysqldump还是单进程的(mydumper多线程并行sql层备份 https://launchpad.net/mydumper)
![1493260528698348.png blob.png](/ueditor/php/upload/image/20170427/1493260528698348.png)
还是基于sql层的备份
![1493260624642154.png blob.png](/ueditor/php/upload/image/20170427/1493260624642154.png)
![1493260718133715.png blob.png](/ueditor/php/upload/image/20170427/1493260718133715.png)
![1493261234242859.png blob.png](/ueditor/php/upload/image/20170427/1493261234242859.png)
mysql本身官方的东西太弱了
很多优秀的东西都是开源的
工具:percona-tools
备份:xtrabackup
监控:zabbix
![1493261826121202.png blob.png](/ueditor/php/upload/image/20170427/1493261826121202.png)
Xtrabackup 是类似于官方的企业备份工具 完全开源
Hot Onloine backup tool for MYSQL
图里的Memory是Innodb Buffer Pool
数据更新时
1.把数据文件拉到内存更新
2.把日志写到redo log
3.更新完了再把数据回写到data file里
在一个完整的事务环境保护下作了redo undo相应措施
redo 是为防止在update更新时没有完全提交 崩掉了 可以通过redo 来恢复到数据文件里
![1495615673467481.png image.png](/ueditor/php/upload/image/20170524/1495615673467481.png)
这个16K 是代表page size 可以自己定义
从data file里拿到内存的是page size的大小 这个就是操作最小单位
比如说以下示例:
select * from user where user_id=100;
除了会把user_id=100的page全读取到buffer pool里
还会预读后面的内容 每次取满page size大小
最小单位基于page size 5.6可以调整
这个时候就体现出key value缓存的好处
key value缓存就是一条记录缓存不是基于page的缓存
没有预读直接获取准确值
innodb_buffer_pool_size 是基于page的缓存
每次操作读回来的大小是page size效率上会比key value缓存慢
![1493262325241240.png blob.png](/ueditor/php/upload/image/20170427/1493262325241240.png)
innodb crash recovery
如果更新中挂掉了 可以通过redo log恢复
100G 库 5.5引入innodb fast crash recovery 2,3分钟恢复
5.1之前要很久(1个多小时) 可以在errorlog里可以看到进度
![1493262589908660.png blob.png](/ueditor/php/upload/image/20170427/1493262589908660.png)
在内存里备份的时候先形成xtrabackup log
把数据库的任何变成以redo log的形式写入到xtrabackup log文件里
在这里面把数据文件全部copy走
任何变更都在这个文件里有了
相当于在内存挂掉之后用redo log恢复一下做一次crash recovery恢复
很快把数据文件恢复出来
Xtrabackup其实就时利用innodb的crash recovery原理
![1493263042809273.png blob.png](/ueditor/php/upload/image/20170427/1493263042809273.png)
![1493263182189423.png blob.png](/ueditor/php/upload/image/20170427/1493263182189423.png)
数据恢复是经过内存在内存里合并写入到数据文件里
对一个业务很繁忙的系统备份出来xtrabackup log很大 不要再高峰时间备份
fast recovery 的逻辑
-
依赖于redo log(redolog会记录哪个page变更,之后能得到一个page no)
-
拿到page no把数据(原始页)从备份的数据里读到内存里
-
拿到内存之后修改合并
-
持久化到数据文件
![1493263273141224.png blob.png](/ueditor/php/upload/image/20170427/1493263273141224.png)
![1493264292314955.png blob.png](/ueditor/php/upload/image/20170427/1493264292314955.png)
XtraBackup 2.4.1 GA版本发布,终于支持了MySQL 5.7的物理备份
tokudb不支持 可能会按非事务引擎来备份
![1493264596839531.png blob.png](/ueditor/php/upload/image/20170427/1493264596839531.png)
innodb/xtradb可以hot backup
myisam非事务引擎都是要加锁的
![1493264779459281.png blob.png](/ueditor/php/upload/image/20170427/1493264779459281.png)
innobackupex 是调用xtrabackup
中间2个会影响效率不建议使用
xtrabackup不会copy *.frm (表结构文件)
![1493265216677647.png blob.png](/ueditor/php/upload/image/20170427/1493265216677647.png)
ibdata里有什么数据?
1、system tablespace(系统表空间)
ibdata files包含:(共享表空间文件)
(1)、internal data dictionary:数据字典
(2)、insert buffer:插入buffer
(3)、rollback segments:回滚表空间
(3)、undo pages:回滚页
(4)、Double write buffer:写入缓冲区
(5)、undo tablespace
参照叶的课程
mysql 5.6 引入了一个特性: undo tablespace可以独立配置
![1493268782338023.png blob.png](/ueditor/php/upload/image/20170427/1493268782338023.png)
-
先形成xtabackup log文件记录内存里的变更
-
copy innodb数据文件 .ibd , ibdata1
-
设置读锁为了一致性
-
copy表空间,表结构等文件
-
拿到binlog位置
-
释放锁
-
停掉copy 结束
全事务引擎的–no-lock
![1493269142786876.png blob.png](/ueditor/php/upload/image/20170427/1493269142786876.png)
在做这个的时候是调用xtrabackup来copy的
![1493269489846367.png blob.png](/ueditor/php/upload/image/20170427/1493269489846367.png)
/etc/my.cnf /etc/mysql/my.cnf
/usr/local/mysql/etc/my.cnf
如果不是标准安装需要制定配置文件
指定备份目录
innobackupex –defaults-file=/path/my.cnf /backup/to/path/
依赖变量:
datadir
socket
如果当前用户连不到mysql里需要提供用户密码
–user=
–password=
–host=
–port=
–apply-log
备份完之后会形成backup-my.cnf
![1493269767469692.png blob.png](/ueditor/php/upload/image/20170427/1493269767469692.png)
数据库刚开始备份时形成xtrabackup_info
xrtabackup_binlog_info: 获得binlog位置show master status的输出
–apply-log之后会形成
xtabackup_slave_info
xtrabackup_binlog_post_innodb
一般情况下xrtabackup_binlog_info应该和xtrabackup_binlog_post_innodb 是相等的
如果不相等以xtrabackup_binlog_post_innodb 为准
xtrabackup_logfile是最早形成的
backup-my.cny有数据库的redu log大小定义
![1493270181315841.png blob.png](/ueditor/php/upload/image/20170427/1493270181315841.png)
![1493270509559963.png blob.png](/ueditor/php/upload/image/20170427/1493270509559963.png)
![1493270572852576.png blob.png](/ueditor/php/upload/image/20170427/1493270572852576.png)
这里没有gtid比较不爽 不知道现在有没有(https://sanwen8.cn/p/22b9Mii.html)
![1493270756634498.png blob.png](/ueditor/php/upload/image/20170427/1493270756634498.png)
这是测试环境里没有写入 to_lsn和last_lsn是一样的
如果在live里写入会很多 to_lsn和last_lsn会差很大
lsn是log sql no在系统show engine innodb status\G里面输出
![1493270927340587.png blob.png](/ueditor/php/upload/image/20170427/1493270927340587.png)
其实就是字节大小
![1493270994697934.png blob.png](/ueditor/php/upload/image/20170427/1493270994697934.png)
![1493271102185449.png blob.png](/ueditor/php/upload/image/20170427/1493271102185449.png)
ibdata1:100M:autoextend
被同事改成了1G
已经完成初始化后,再改,然后重启能不能启动?为什么?
实际的datafile 大于1G可以起来小于1G起不来
ibdata1:10M:autoextend
改小可以起来
因为:定义的值要<=实际的文件 关键在autoextend 只要比定义值小就起不来 大就没问题
innodb_log_file_in_group :log个数
innodb_log_file_size :log大小
这2个参数关系到重新做apply log(好像是这个听的不是太清楚)的时候需要把这2个初始化出来
![1493271627385812.png blob.png](/ueditor/php/upload/image/20170427/1493271627385812.png)
innodb_log_file_size
5.5要重新导入导出重建数据库
5.6可以修改重启就可以了
![1493299480348644.bmp untitled.bmp](/ueditor/php/upload/image/20170427/1493299480348644.bmp)
![1493300021706860.png blob.png](/ueditor/php/upload/image/20170427/1493300021706860.png)
指定上一次备份的文件在哪
![1493300572452091.png blob.png](/ueditor/php/upload/image/20170427/1493300572452091.png)
拿到之后主要是找xtabackup_info 找上一次备份的last_lsn找到后把大于这个lsn的data page全部copy一遍,copy到下图位置
![1493300603806716.png blob.png](/ueditor/php/upload/image/20170427/1493300603806716.png)
得到lsn后也可以有偷懒的做法 在做增量备份之前可以指定last_lsn在哪,在这个地方直接指定一个lsn就可一直接备份增量也不用指定上个备份目录在哪,这个做法就是大于这个lsn的page全把他copy一份
这样就是简单的做了一个增量
![1493300662770886.png blob.png](/ueditor/php/upload/image/20170427/1493300662770886.png)
![1493300811664688.png blob.png](/ueditor/php/upload/image/20170427/1493300811664688.png)
恢复:
full_1
incr_1
incr_2
如果incr_1 增量坏了
那就悲剧了
![1493301269260164.png blob.png](/ueditor/php/upload/image/20170427/1493301269260164.png)
data-page, index-page
更紧凑备份:只要data-page不要index-page
恢复的时候要重建index-page
![1493301350533558.png blob.png](/ueditor/php/upload/image/20170427/1493301350533558.png)
innodb_file_per_table:独立表空间
![1493301418269348.png blob.png](/ueditor/php/upload/image/20170427/1493301418269348.png)
include-tables-file=/path/tblist.txt //指定备份哪些表
–database //指定备份哪个库
![1493301478397299.png blob.png](/ueditor/php/upload/image/20170427/1493301478397299.png)
恢复的时候要多一个-export,会多一个tb.exp
紧凑备份
好处是减少备份集,备份小一点
![1493301530206282.png blob.png](/ueditor/php/upload/image/20170427/1493301530206282.png)
紧凑备份恢复的时候要索引重建
![1493301567467753.png blob.png](/ueditor/php/upload/image/20170427/1493301567467753.png)
恢复的时间会比平常时间多好几倍时间 因为索引要重建
========================================================
![1493301659351654.png blob.png](/ueditor/php/upload/image/20170427/1493301659351654.png)
![1493301787651002.png blob.png](/ueditor/php/upload/image/20170427/1493301787651002.png)
export之后除了之前的tb_a.frm, tb_ibd
会生成一个tb_a.cfg
然后把这个文件scp到远程空间上 或者scp到需要导入的那台机器上
导入的机器上需要创建一个同样的表结构
需要把当前表空间干掉![1493302001498913.png blob.png](/ueditor/php/upload/image/20170427/1493302001498913.png)
干掉之后如果没有表空间导进来的话就完蛋了
tb_a.ibd 表空间删掉了
把表空间import进来: alter table tb_a import tablespace;
这个特性是MySQL 5.6引入的
![1493302482626540.png blob.png](/ueditor/php/upload/image/20170427/1493302482626540.png)
![1493302535245731.png blob.png](/ueditor/php/upload/image/20170427/1493302535245731.png)
基本每天一个全被+每天备份相应的binlog
比如:
早上4点做全备
4点前的binlog也要copy一下
通过天全备+今天的binlog 备份
可以还原到昨天的备份到今天任何一个时间点的备份
还原到今天某个时间点需要什么东西呢?
昨天凌晨4点的全量到今天凌晨4点的全量是什么呢? 是今天今天4点的binlog
重要的放到HDFS上
![1493303199791690.png blob.png](/ueditor/php/upload/image/20170427/1493303199791690.png)