一 简介:这里汇总了一些mysql常见的问题
二 案例场景
问题1 mysql设置了默认慢日志记录1S,为何会记录不超过1S的sql语句
答案 mysql~log_queries_not_using_indexes变量会记录全表扫描的语句,判断优先级高于执行时间,也就是说,你如果打开这个变量,哪怕执行不到1S的查询也会记录到慢日志中,建议关闭
问题2 程序错误信息 max_allowed_packet 设置过小导致java程序写入失败
答案 1 max_allowed_packet=32M 写入配置文件并重启
2 set global max_allowed_packet=33554432
问题3 mysql崩溃后无法启动,mysql.error中报BUG报错
分析 此问题最常见于断电后数据库无法启动造成的
1数据页损坏
2数据字典损坏
在ibdata1中（或以其他方式命名）文件中的数据与在单个表空间/.ibd / .frm文件的数据之间有明显的关联。当该关联丢失或损坏，可能会发生不好的情况。所以这像这样的数据字典的错误出现，最常见的原因是有些文件被手动移动或修改。它通常归结为：“数据字典预计这一文件或表空间在这里，但它不在！”，或“.ibd / .frm文件预计此项目在数据字典中，但它不在！ “。再次记住，数据字典存储在ibdata文件中，在大多数环境中，就是MySQL数据目录中的ibdata1。
3 日志页损坏
4 版本BUG(一些BUG是能起来的,比如AHI造成的,但是另一些是起不来的)
通用解决办法：
一 调整恢复级别
1 调整恢复级别,请注意,恢复前先备份下,因为恢复是有代价的
2 设置参数 innodb_force_recovery=N 下面是恢复参数代价
1(SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页。
2(SRV_FORCE_NO_BACKGROUND):阻止主线程的运行，如主线程需要执行full purge操作，会导致crash。
3(SRV_FORCE_NO_TRX_UNDO):不执行事务回滚操作。
4(SRV_FORCE_NO_IBUF_MERGE):不执行插入缓冲的合并操作。
5(SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日志，InnoDB存储引擎会将未提交的事务视为已提交。
6(SRV_FORCE_NO_LOG_REDO):不执行前滚的操作。
mysql进程停止下
1 /nnochecksum -f /tol/mysql/data/ibdata1 ibdata1文件修复
2 #!/bin/bash ibd文件修复
for i in $( ls /var/lib/mysql/*/*.ibd)
do
innochecksum $i
done
3 过滤出拥有页损坏的表
4 mysqldump导出非页损坏的表 然后导入到新实例
问题4 mysql所在服务器的linux时间不对 该怎么办
分析 时间不对要根据分析进行定位
时间对mysql数据的影响有几个方面,由于mysql默认采用的是linux系统时间 1 now(),curdate()等时间函数 2 线程连接时间 Time()统计 3 datetime的uptime属性,默认插入时间等
j解决办法： 1 如果时间小于正确时间,采用ntp更改即可,然后通过show processlst观察更改时间前后的数量统计,是否有连接数断开->如果更改时间,线程的time会加上你更改的时间段,防止超过wait_timeout限制而断开连接
2 如果时间大于正确时间,慎重修改,因为有人测试过,修改后,重启mysql服务会产生异常,最好采用MHA切换后再修改
补充:强烈建议在机器上线时启动NTP服务,校对好,防止出问题
四 innodb自增长不连续可能是什么情况
1 数据库重启造成的ID不连续
分析 因为自增ID的值是保存在内存中的,如果重启后,最大增值ID会从表中取,而内存中的已经丢失,造成ID增长异常
2 数据库插入数据唯一键冲突
分析 当唯一键冲突插入失败时,虽然没有插入数据,但是自增ID还是需要+1的,这样就造成了不连续
3 事务回滚
分析 事务回滚也会造成增长ID不连续,因为涉及到复杂的回滚判断机制
4 批量插入语句
分析
insert select load data等批量插入语句,mysql对于自增ID锁的申请并不会一次申请一个,会一次申请的数量慢慢增多,可能造成ID增长异常
五 因为memory导致的主从同步异常
现象:主库down机后MHA切换,从库指向新主却发生同步异常
分析: 通过binlog定位分析出这是一张内存表,所以哪怕备份,内存表也是无法保存的,搭建的新的从库照样会有同步异常
解决: 1 将内存表转化为innodb引擎 2 主从同步跳过内存表错误 3取消内存表相关业务
注意: 1 在row格式下,内存表的操作也是记录在binlog中的 2 memory引擎表是和myisam一样,表级别的锁
六 insert into a select from b 究竟加什么锁
分析 在RR模式下 insert select 会对B进行扫描加锁,包括行锁和gap lock,
1 insert into a selec from b 是默认扫描逐行加锁,并非表级锁,效果等同于order id(PK)
总结 1 由于在RR模式下要维持一致性读,所以需要加锁
2 这条语句属于一个事务
3 尽量在业务低峰期进行操作,减少影响
4 利用explain进行条件观察,减少锁定行数,条件必须要利用到索引,否则会导致锁定全表
七 获取最新插入主键值,你需要知道什么
目的 当我们向主键是自增型的数据库表中（如 user 表）插入一条记录时，我们期望获取刚刚写入这条记录的id，（因为某些场景下需要获得这个id去做其他的操作），此时我们需要使用SELECT LAST_INSERT_ID()这个语句来获取插入记录后返回的自增id的值；
注意 1假如你使用一条INSERT语句插入多个行， LAST_INSERT_ID() 只会返回插入的第一行数据时产生的值。
初次建立的LAST_INSERT_ID默认值为0,一旦有值插入,则获取自增列的最大值作为默认值
2 每次session建立的LAST_INSERT_ID()都是独立的,新建立的session
1 对于主键,插入的值为获取主键最大值+1
2 对于其他,插入的值为初始化值0
3 对于insert后执行,会获取指定的插入ID,符合我们的预期,请注意,对于每次的所有表插入操作都会进行统计,查询和插入所使用的Connection对象必须是同一个才可以，否则返回值是你所不希望看到的
八 一个由于触发BUG导致的mysql cash问题
日志输出 Error: semaphore wait has lasted > 600 seconds , 剩下的就是常见的叙述,您可能遇到一个mysql bug之类的
现象分析 通过文档和相关资料查询,自适应hash索引可能导致 rw latch争用,导致cash
解决方案 官方和网上都建议关闭自适应hash索引(set global innodb_adaptive_hash_index=0),但是这样会降低性能,而且此库压力本来就大
我的方案 因为有MHA,可以高可用.先进行程序优化,减小压力(我觉得是因为压力导致的争用).如果再发生一次MHA切换,我就会考虑关闭自适应hash索引
九 pt-osc导致的死锁问题
1 死锁日志表现形式
事务1 INSERT LOW_PRIORITY IGNORE INTO
事务2 REPLACE INTO
2 分析
pt-osc的死锁问题造成的核心就是自增锁的问题,
路线1 pt-osc insert select是分段的,会在新表申请自增锁,然后插入.
路线2 pt-osc tigger触发动作以replace into方式插入新表
锁相关
事务一
持有：原表记录上的x锁
等待：new表上的auto-inc lock
事务二
持有：new 表上的auto-inc lock
等待：原 表上的S锁
描述 程序日志也会打印针对原表的死锁
3 解决方式
1 尽量切割减少锁定范围 chunk-size
2 不要在表活动高峰期采用pt-osc工作
4 总结
1 insert新表的时候，是区间进行对原表查询，在还没有insert完成的情况下，对原表这个区间的数据进行了更新操作，导致死锁的发生
2 pt-osc其实还是很少发生死锁的
十一 mysqldump引发的数据覆盖
需求:因为要同步线上库,又要保留本地测试库,所以mysqldump出一份,结果测试数据库被覆盖失误了
分析: mysqldump -B 选项 会在sql文本里增加use选项,所以在指定新库然后导入后还是会选择覆盖原库.强烈不建议使用
-add-drop-database 这里会添加删除想通库的提示,也请不要加入这个选项.为了您的数据安全
十一 安装mysql相关工具导致的依赖包问题
分析:安装MHA和xtrabackup工具发生依赖冲突,解决了好久
解决方式: 1 删除/etc/yum/respond.d的yum源
2 下载两个yum源
wget http://mirrors.163.com/.help/CentOS7-Base-163.repo && wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
3 yum clean all 执行后重新安装
十二 mysql通过show processlist 大量处于open /close table阶段
分析 open/close table基本是与table_cache有关,mysql相关的参数大概有2个
table_open_cache 表缓存个数,与数量有关
table_open_cache_instances 涉及缓存表缓存的锁争用
解决方式 1 降低查询本身频率 2 优化慢sql 3 设置读写分离 4 调整以上两个数值
十三 mysql在执行truncate时候,大量表处于open/close状态,但是一旦truncate完成,这种状态就会消失
分析 这种案例和上面的案例有些相似,却又不同.八怪大神经过pt-stack跟踪源码问题,定位到了是AHI导致的可能BUG问题
解决方式 关闭 自适应HASH索引
补充: 1 上面也有一个因为AHI导致的BUG问题 导致mysql索引
2 是否选择关闭AHI,要看关闭前后的程序效率对比,如果大量下降,请不要关闭.如果没什么影响,则建议关闭
十四 程序执行事务时,报max_binlog_cache不足问题
具体错误描述 ulti-statement TRANSACTION required more THAN 'max_binlog_cache_size' bytes of STORAGE、
分析 1 事务根据生成binlog cache的大小算作事务的大小,由max_binlog_cache_size控制
2 max_binlog_cache_size 默认大小为32K
3 max_binlog_cache_size 是session级别独享的
总结 1 调整 参数大小 2 对大事务进行拆分
补充
1 Binlog_cache_disk_use表示因为我们binlog_cache_size设计的内存不足导致缓存二进制日志用到了临时文件的次数
Binlog_cache_use 表示 用binlog_cache_size缓存的次数
2 当对应的Binlog_cache_disk_use 值比较大的时候 我们可以考虑适当的调高 binlog_cache_size 对应的值
十五 jdbc报连接错误问题
具体错误描述 error code [1094]; Unknown thread id: 73125270; nested exception is java.sql.SQLException: Unknown thread id: 73125270
分析 1 根据官网可以查看的错误代码解释,不过没什么用
2 经过与研发沟通,确认研发设置了jdbc超时时间
总结 jdbc超时时间的设置,一旦sql本身超过超时时间的设置,会发送kill session信息请求,导致jdbc报错
优化你的sql或者将超时时间设大都有助于错误的减少出现
扫一扫
4127
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
