转自
《叶问》是知数堂新设计的互动栏目,不定期给大家提供技术知识小贴士,形式不限,或提问、或讨论均可,并在当天发布答案,让大家轻轻松松利用碎片时间就可以学到最实用的知识点。
知数堂 - 最靠谱、最有品质的培训品牌 http://www.3wedu.net/
叶问专辑 https://mp.weixin.qq.com/mp/homepage?__biz=MzI1OTU2MDA4NQ%3D%3D&hid=15&sn=8a530aa309c1fe6e4d99b3a0d49a9695
2018年6月10日,周日
MySQL主从复制什么原因会造成不一致,如何预防及解决?
一、导致主从不一致的原因主要有:
-
人为原因导致从库与主库数据不一致(从库写入)
-
主从复制过程中,主库异常宕机
-
设置了ignore/do/rewrite等replication等规则
-
binlog非row格式
-
异步复制本身不保证,半同步存在提交读的问题,增强半同步起来比较完美。 但对于异常重启(Replication Crash Safe),从库写数据(GTID)的防范,还需要策略来保证。
-
从库中断很久,binlog应用不连续,监控并及时修复主从
-
从库启用了诸如存储过程,从库禁用存储过程等
-
数据库大小版本/分支版本导致数据不一致?,主从版本统一
-
备份的时候没有指定参数 例如mysqldump --master-data=2 等
-
主从sql_mode 不一致
-
一主二从环境,二从的server id一致
-
MySQL自增列 主从不一致
-
主从信息保存在文件里面,文件本身的刷新是非事务的,导致从库重启后开始执行点大于实际执行点
-
采用5.6的after_commit方式半同步,主库当机可能会引起主从不一致,要看binlog是否传到了从库
-
启用增强半同步了(5.7的after_sync方式),但是从库延迟超时自动切换成异步复制
二、预防和解决的方案有:
-
master:innodb_flush_log_at_trx_commit=1&sync_binlog=1
-
slave:master_info_repository="TABLE"&relay_log_info_repository="TABLE"&relay_log_recovery=1
-
设置从库库为只读模式
-
可以使用5.7增强半同步避免数据丢失等
-
binlog row格式
-
必须引定期的数据校验机制
-
当使用延迟复制的时候,此时主从数据也是不一致的(计划内),但在切换中,不要把延迟从提升为主库哦~
-
mha在主从切换的过程中,因主库系统宕机,可能造成主从不一致(mha本身机制导致这个问题)
2018年6月11日,周一
你为什么会决定进行分库分表,分库分表过程中遇到什么难题,如何解决的?
一、为什么决定进行分库分表?
-
根据业务类型,和业务容量的评估,来选择和判断是否使用分库分表
-
当前数据库本事具有的能力,压力的评估
-
数据库的物理隔离,例如减少锁的争用、资源的消耗和隔离等
-
热点表较多,并且数据量大,可能会导致锁争抢,性能下降
-
数据库的高并发,数据库的读写压力过大,可能会导致数据库或系统宕机
-
数据库(MySQL5.7以下)连接数过高,会增加系统压力
-
单表数据量大,如SQL使用不当,会导致io随机读写比例高。查询慢(大表上的B+树太大,扫描太慢,甚至可能需要4层B+树)
-
备份和恢复时间比较长
二、都遇到什么问题?
-
全局pk(主键和唯一索引)的冲突检测不准确,全局的自增主键支持不够好
-
分片键的选择。如没有选择好,可能会影响SQL执行效率
-
分布式事务,中间价产品对分布式事务的支持力度
-
对于开发来说,需要进行业务的拆分
-
对于开发来说,部分SQL不兼容则需要代码重构,工作量的评估
-
对于开发来说,跨库join,跨库查询
三、如何解决?
-
使用全局分号器。或者使用全局唯一id,(应用生成顺序唯一int类型做为全局主键)
-
应用层来判断唯一索引
-
配合应用选择合适的分片键,并加上索引
-
配合应用,配合开发,对不兼容SQL的进行整改
2018年6月12日,周二
MySQL高可用架构应该考虑什么? 你认为应该如何设计?
一、MySQL高可用架构应该考虑什么?
-
对业务的了解,需要考虑业务对数据库一致性要求的敏感程度,切换过程中是否有事务会丢失
-
对于基础设施的了解,需要了解基础设施的高可用的架构。例如 单网线,单电源等情况
-
对于数据库故障时间掌握,业务方最多能容忍时间范围,因为高可用切换导致的应用不可用时间
-
需要了解主流的高可用的优缺点:例如 MHA/PXC/MGR 等。
-
考虑多IDC多副本分布,支持IDC级别节点全部掉线后,业务可以切到另一个机房
二、你认为应该如何设计?
-
基础层 和基础运维部门配合,了解和避免网络/ 硬盘/ 电源等是否会出现单点故障
-
应用层 和应用开发同学配合,在关键业务中记录SQL日志,可以做到即使切换,出现丢事务的情况,也可以通过手工补的方式保证数据一致性,例如:交易型的业务引入状态机,事务状态,应对数据库切换后事务重做
-
业务层 了解自己的应用,根据不同的应用制定合理的高可用策略。
-
单机多实例 环境及基于虚拟机或容器的设计不能分布在同一台物理机上。
-
最终大招 在数据库不可用 ,可以把已提及的事务先存储到队列或者其他位置,等数据库恢复,重新应用
2018年6月13日,周三
MySQL备份,使用xtrabackup备份全实例数据时,会造成锁等待吗?那么如果使用mysqldump进行备份呢?
一、xtrabackup和mysqldump会造成锁等待吗?
-
xtrabackup会,它在备份时会产生短暂的全局读锁FTWL(flush table with read lock),用于拷贝frm/MYD/MYI等文件,以及记录binlog信息。如果MyISAM表的数据量非常大,则拷贝时间就越长,加锁的时间也越长
-
mysqldump有可能会。如果只是添加 --single-transacton 选项用于保证备份数据一致性,这时就不会产生FTWL锁了。但通常我们为了让备份文件和binlog保持一致,通常也会设置 --master-data 选项用于获得当前binlog信息,这种情况也会短暂加锁
-
数据量特别大的话,建议优先用 xtrabackup,提高备份/恢复速度。而如果数据量不是太大或者想备份单表,则建议用mysqldump了,方便逻辑恢复。各有利弊,注意其适用场景
二、xtrabackup冷知识
-
基于MySQL 5.6版本开发的xtrabackup,会在备份过程中生成内部通信文件 suspend file,用于 xtrabackup 和 innobackupex 的通信,备份结束后文件删除,默认文件位置 /tmp/xtrabackup_suspended
-
如果在备份过程中,修改了 /tmp 的访问权限或该文件的权限,则两个程序间直接不能通信,会造成 xtrabackup hang 住,正在备份的表不能正常释放锁,会造成锁等待,此时需要强制 kill 掉 xtrabackup 进程
2018年6月15日,周五
MySQL 5.7开始支持JSON,那还有必要使用MongoDB存JSON吗?请列出你的观点/理由。
一、观点A:支持MySQL存储JSON
1.MongoDB不支持事务,而MySQL支持事务
2.MySQL相对MongoDB而言,MySQL的稳定性要优于MongoDB
3.MySQL支持多种存储引擎
二、观点B:支持MongoDB存储JSON
1.从性能的角度考虑,对于JSON读写效率MongoDB要优于MySQL
2.MongoDB相对MySQL而言,MongoDB的扩展性要优于MySQL
3.MongoDB支持更多的JSON函数
三、总结
1.如果应用程序无事务要求,存储数据表结构复杂并且经常被修改, 例如游戏中装备等场景用MongoDB比较适合
2.如果应用程序有事务要求,存储数据的"表"之间相互有关联,例如有订单系统等场景用MySQL比较适合
3.整体来看相对看好MySQL的JSON功能,在未来官方的努力下MySQL的JSON功能有机会反超MongoDB
2018年6月17日,周日
当数据被误删除/误操作后造成数据丢失。你尝试过用什么手段来挽救数据/损失?
一、前提
1.当数据被误删除/误操作后,第一时间要关闭数据库。业务方需要紧急挂停机公告,避免数据二次污染,用于保护数据的一致性2.BINLOG格式为ROW格式,不讨论其他格式的BINLOG
二、数据被误操作(update/delete/drop)造成数据丢失,可以用哪些手段来恢复?
1.BINLOG恢复:可以使用逆向解析BINLOG工具来恢复。例如:binlog2SQL等
2.延迟从库: 可以通过解除延迟从库,并指定BINLOG结束位置点,可以实现数据恢复
三、数据被误删除(rm/物理文件损坏)造成数据丢失,可以用哪些手段来恢复?
1.如果有备份,可以通过备份恢复 mysqldump/xtrabackup + binlog 来实现全量+增量恢复
2.如果无备份但是有从库,可以通过主从切换,提升从库为主库,从而实现数据恢复
3.如果无备份并且无从库,但MySQL没有重启,可以通过拷贝/proc/$pid/fd中的文件,来进行尝试恢复
4.如果无备份并且无从库,但MySQL有重启,可以通过extundelete或undrop-for-innodb来恢复
2018年6月19日,周二
MySQL 5.7的复制架构,在有异步复制、半同步、增强半同步、MGR等的生产中,该如何选择?
一、生产环境中:
几种复制场景都有存在的价值。下面分别描述一下:
-
从成熟度上来选择,推荐:异步复制(GTID+ROW)
-
从数据安全及更高性能上选择:增强半同步 (在这个结构下也可以把innodb_flush_log_trx_commit调整到非1, 从而获得更好的性能)
-
对于主从切换控制觉的不好管理,又对数据一致性要求特别高的场景,可以使用MGR
二、理由:
-
异步复制,相对来讲非常成熟,对于环境运维也比较容易上手
-
增强半同步复制,可以安全的保证数据传输到从库上,对于单节点的配置上不用要求太严格,特别从库上也可以更宽松一点,而且在一致性和性能有较高的提升,但对运维上有一定的要求
-
MGR组复制。相对增强半同步复制,MGR更能确保数据的一致性,事务的提交,必须经过组内大多数节点(n/2+1)决议并通过,才能得以提交。MGR架构对运维难度要更高,不过它也更完美
总的来讲,从技术实现上来看:MGR> 增强半同步>异步复制。
未来可能见到更多的MGR在生产中使用,对于MySQL的运维的要求也会更上一层楼。
2018年6月20日,周三
为什么说pt-osc可能会引起主从延迟,有什么好办法解决或规避吗?
-
若复制中binlog使用row格式,对大表使用pt-osc把数据从旧表拷贝到临时表,期间会产生大量的binlog,从而导致延时
-
pt-osc在搬数据过程中insert...select是有行锁的,会降低事务并行度;且pt-osc搬数据过程中生成的binlog不是并行的,所以在slave不能并行回放
-
可以通过设定参数 --chunk-size、--chunk-time控制每次拷贝数据大小,也可以设定--max-log、check-interval、check-slave-lag等参数控制主从复制延迟程度(但这样可能会造成pt-osc工作耗时太久,需要自行权衡)
2018年6月21日,周四
你遇到过哪些原因造成MySQL异步复制延迟?
-
master上多为并发事务,salve上则多为单线程回放(MySQL 5.7起,支持真正的并行回放,有所缓解)
-
异步复制,本来就是有一定延迟的(否则也不叫做异步了,介意的话可以改成半同步复制)
-
slave机器一般性能比master更弱(这是很常见的误区,其实slave对机 器性能要求并不低)
-
有时为了节省机器资源,会在slave上运行多个实例
-
表结构设计不合理,尤其是在MySQL 5.6之前没主键,几乎会造成所有更新都全表扫描一遍,效率非常低
-
slave上运行大量只读低效率的SQL
-
大量大事务,也会造成slave无法并行回放
-
业务设计缺陷,或网络延迟等导致延迟
2018年6月22日,周五
MySQL每天产生了多大容量的binlog,用SQL语句能查到吗?
首先,这是个假设性命题(又一个钓鱼题)。
这个需求完全可以通过系统层命令,配合MySQL中的“FLUSH BINARY LOGS”快速完成。 运行SHOW MASTER/BINARY LOGS命令能查看全部binlog列表,但没办法区别哪些是当天内生成的。2018年6月23日,周六
用什么方法可以防止误删数据?
以下几个措施可以防止误删数据,如下:
-
-
生产环境中,业务代码尽量不明文保存数据库连接账号密码信息
-
重要的DML、DDL通过平台型工具自动实施,减少人工操作
-
部署延迟复制从库,万一误删除时用于数据回档,且从库设置为read-only
-
确认备份制度及时有效
-
启用SQL审计功能,养成良好SQL习惯
-
启用 sql_safe_updates 选项,不允许没 WHERE 条件的更新/删除
-
将系统层的rm改为mv
-
线上不进行物理删除,改为逻辑删除(将row data标记为不可用)
-
启用堡垒机,屏蔽高危SQL
-
降低数据库中普通账号的权限级别
-
务必开启binlog
-
MySQL 8.0相对于5.7的复制改进,都有哪些呢?
的公开课也讨论了这个命题,简单概括主要有两部分:
一、普通复制功能改进
-
新增WRITESET并行复制模式,提高并行度,降低延迟
-
在多源复制中,可在线动态修改每个channel的filter rule,并且能在P_S中查看/监控
-
Binary Log中存储更多元数据,并支持毫秒级别的延迟监控
-
对JSON Documents的复制效率更高了
-
支持DDL Crashsafe
-
增加caching_sha2_password安全策略,提高复制安全性
二、MGR功能改进:
-
支持设置节点权重,且权重最大的在线节点将被选举为主
-
每个节点中存储更多的状态信息,如版本、角色等
-
可根据从节点的事务状态,自动化流控
-
离开集群的服务器自动被设置为read only,避免被误操作更新数据
-
可监控MGR的内存使用情况
2018年6月25日,周一
跑truncate table,4亿条数据会不会造成长时间锁表呢?有什么更好的方法吗?
最好是create新表,然后交叉rename对调,再drop/truncate table或其他方式清除数据。
一、可操作步骤:
-
创建新的 tmp 表,正式表与tmp表表名交换(注意在一个SQL里完成,并锁表)
-
对 tmp 表创建硬链接 ln tmp.ibd tmp.ibd.hdlk
-
mysql中删除表tmp(truncate / drop 都行)
-
然后找个业务不繁忙的时间删除数据文件或者用coreutils 的truncate慢慢搞
二、关于truncate table,官档解释:
Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows, or a sequence of DROP TABLE and CREATE TABLE statements
When a table is truncated, it is dropped and re-created in a new .ibd file, and the freed space is returned to the operating system
2018年6月26日,周二
明明有个索引“感觉”应该被选中,EXPLAIN时在possible_keys也有它,但最后没被选中,可能的原因有哪些?
一、执行计划如下:
desc select * from t1 where c2 >= 2;
key: NULL key_len: NULL rows: 14 filtered: 92.86 Extra: Using where二、可能的原因如下:
-
隐式转换
-
表碎片,因为表的碎片率过高
-
根据索引读取到的数据在整个表中的数据占比超过30%
-
统计信息没有及时更新
三、上述执行计划的结果是:
预计扫描的行数为14行,filtered(是指返回结果的行占需要读到的行的百分比)的值为92%。
当前执行计划中filtered值92% 说明根据索引查询获取的结果占整张表的92%,在MySQL中根据索引查询的结果占整张表的数据30%则不会走索,所以不会走索引。
另外,也有可能是表的碎片率过高或隐式转换导致的。
2018年6月27日,周三
主从复制线程均正常(为Yes,也没报错),Master的binlog已到binlog.000100,但slave上看到Master_Log_File却只到binlog.000090,可能的原因有哪些?
首先要注意,这是Master_Log_File IO线程延迟,并不是Relay_Master_Log_File SQL线程延迟。
一、可能的原因如下:
-
由于sync_relay_log值过低,导致Slave频繁刷新relay_log文件,使 Slave的硬盘资源消耗过高,所以导致SlaveIO Thread很慢。
-
Master/Slave压力过大导致Slave IO Thread不能及时响应, 无法及时获得Master的event。
-
网络丢包严重。小包可以连接并且保持连接不断,但是大包就无法发送。可能是Master和Slave关于TCP MTU值设置不一致导致。
-
Master和Slave网络链接已经断开。但slave_net_timeout值等于0(表示完全禁用心跳)或者slave_net_timeout和Slave_heartbeat_period非常大(表示检测主从心跳的时间)。
-
Master的binlog非常大,io线程的file很长时间都在读同一个。
二、总结
本次案例是在主库进行压力测试,在压力测试的过程中,因为Master本身的压力就很大Master来不及把binlog发送给Slave。所以表面上看起来没有延迟,但实际上已经产生了延迟。。
2018年7月4日,周三如何优化Linux操作系统用于MySQL环境?
一、初级玩法
1. 在BIOS及内核层面关闭NUMA
2. 在BIOS层面将CPU、内存均设置最大性能模式
3. 在BIOS层面关闭CPU节能模式
4. 修改IO Scheduler为deadline 或 noop
5. 使用xfs文件系统,挂载选项noatime、nodiratime、nobarrier
6. 在内核层面设置vm.swappiness<=5,vm.dirty_ratio<=10, vm.dirty_background_rati<=5
7. 在内核层面修改用户可最大打开文件数和线程数为65535
8. 禁用SWAP分区
二、高端玩法
1. 使用最新稳定Linux发行版
2. 升级各个硬件设备到最新稳定firmware版本
3. 使用SSD时,开启TRIM功能,并且可以的话文件系统block size和SSD对齐
4. 当磁盘I/O存在瓶颈时,除了常规因素外,还需要关注中断不均衡的可能性
2018年7月5日,周四MySQL 8.0 InnoDB哪些新特性你最期待,为什么?
1. 数据字典全部采用InnoDB引擎存储,支持DDL原子性、crash safe,metadata管理更完善
2. 快速在线加新列(腾讯互娱DBA团队贡献) 3. 并行redo log,并提升redo log的I/O性能 4. 新增倒序索引 5. 增强CBO特性 6. 消除了buffer pool mutex(Percona的贡献) 7. 自增ID持久化 8. 行锁增加SKIP LOCKED和NOWAIT特性选项 9. 新增事务CATS特性,大大提升事务性能(Michigan大学贡献) 10. memcached plugin增强 11. 增强JSON性能、功能 12. 新增智能选项 innodb_dedicated_server
2018年7月10日,周二
MySQL hang的原因有哪些?
1. MySQL使用资源过高导致服务器太累扛不住。例如CPU、内存、 I/O等开销。
2. 磁盘无可用空间。
3. MySQL频繁的创建和销毁连接。
4. MySQL使用的最大文件打开数和连接数,超过了操作系统的限制。
5. MySQL的锁不能有效的释放。例如持有行锁或者表锁,造成了MDL等待。
6. MySQL的bug导致的。
导致MySQL hang住的原因有很多,不局限于上述因素,还需要机智的你来挖掘。
2018年7月12日,周四
专访王晓伟老师,MySQL数据导入数据仓库(Hadoop)有哪几种方式?
1. 传统方式,采用mysqldump等工具将数据文件上传至HDFS
2. 使用Sqoop Kettle等ETL工具,将数据表对应导入Hive的数据表
3. 使用kafka+flume方案,将mysql binlog通过流式采集的方式导入Hadoop
4. 设计实现Hive的快照表、增量表、全量表,实现MySQL到Hive数据的增量导入,并支持分库分表等特性。