MySQL DBA常用技能集锦

1、MySQL DBA常用技能集锦技术创新，变革未来1.1主从又不一致了老生常谈的参数一致性相关innodb_flush_log_at_trx_commit=1sync_binlog=1复制安全relay_log_info_repository = TABLEmaster_info_repository = TABLE sync_relay_log = 1sync_master_info = 1影响复制安全的参数故事背景MySQL5.6.24主实例Binlog磁盘满磁盘清理影响面主从实例不同步，数据不一致现象；重做所有从实例原因ERRORCouldnotopen./master-bin.00006

2、4forlogging(error 13).TurningloggingoffforthewholedurationOftheMySQL serverprocess.Toturnitonagain:fixthecause,shutdownthe MySQLserverandrestartit.影响复制安全的参数分析binlog_error_actionignore_errorabort_server验证：ABORT_SERVERErrormessage:Binaryloggingnotpossible.Eitherdiskisfullorfilesystemisreadonlywhilerot

3、atingthe binlog.Abortingtheserver影响复制安全的参数结论5.6.22版本增加了Abort_server选项5.6版本默认值为IGNORE_ERROR5.7版本默认值为ABORT_SERVER该参数支持动态修改推荐生产环境配置ABORT_SERVER选项1.22亿行单表的故事一张2亿行单表的故事InnoDB:Operatingsystemerrornumber0.InnoDB:Check that your OS andfile systemsupport files ofthissize.故事背景CPU：64c Mem：256G Disk：SSD2亿行70G单表

4、上做了alter table加字段操作，报错如下：mysqlaltertablefriendaddcolumntagvarchar(255)comment好友对应的 tags;InnoDB:Error:Writetofile(merge)failedatoffset3039821824.InnoDB:1048576bytesshouldhavebeenwritten,only471040werewritten.影响面不要跟我谈影响面泡面都凉了！一张2亿行单表的故事分析问题直接原因：/tmp满了，重新配置了tmpdir2亿3000W条记录:mysqlaltertablefriendaddcolu

5、mntagvarchar(255) comment好友对应的tags;QueryOK,0rowsaffected(1hour48min36.33sec)Records:0Duplicates:0Warnings:0一张2亿行单表的故事问题1：何时产生临时表？altertabletablecopy-#sql-INPLACE-#sql-ib/tmpdir复杂排序Orderby-SQL_*.Groupby-SQL_*.参考文献：/doc/refman/5.7/en/temporary-files.html一张2亿行单表的故事问题2:altertable添加列属于哪种操作参考文献：/doc/refma

6、n/5.6/en/innodb-create-index-overview.html一张2亿行单表的故事问题3:是否有其他的DDL方式 mysql：onlineddlpt-osc：perconaonlineschemachangegh-ost：GitHubsonlineschemamigrationforMySQL参考文献：/doc/refman/5.6/en/innodb-online-ddl.html /doc/percona-toolkit/2.1/pt-online-schema-change.html /github/gh-ost一张2亿行单表的故事总结1.tmpdir在生产上线时提

7、前规划，建议单独配置足够容量 的目录2.操作ddl前建议根据altertable的类型，对照官方文档 确认是inplace还是tablecopy一张2亿行单表的故事总结3. DDL操作方式的原则 表数据量大小最直接影响选择ddl方式的因素，建议超过100万行的表，通过posc或gh-ost操作是否可以接受延迟接受延迟的环境，表小于100万，用原生 不接受延迟的环境，使用posc或gh-ost1.3疯狂的Slowlog疯狂的SlowLog疯狂的SlowLoggrow quickly疯狂的SlowLog正确的姿势应该是这样的2.1偶遇Metadatalock偶遇metadata lockMetad

8、ata lock wait 出现的场景有大事务正在使用表存在有未提交的事物存在有未提交的事务,且是失败操作解决方案找到未释放metadatalock的session，并killKillddl语句，等待下一次运维时间变更偶遇metadata lockMetadata lock wait 出现的场景1有大事务正在使用表偶遇metadata lockMetadata lock wait 出现的场景2存在有未提交的事务偶遇metadata lockMetadata lock wait 出现的场景3存在有未提交的事务,且是失败操作2.2复制延迟，再延迟复制延迟的几种可能主从服务器的配置或者压力不一，从实

9、例的IO能 力太弱主库的TPS太高导致备库延迟备份WaitingforglobalreadlockDDL导致备库延迟（altertable,create index,optimizetable,repairtable）运行大事务对无主键的表进行删除或者更新运行大事务背景max_binlog_size=512M-rw-r-1mysqlmysql512MMay1110:17mysql-bin.000063-rw-r-1mysqlmysql512MMay1115:59mysql-bin.000064-rw-r-1mysqlmysql1220MMay1222:23mysql-bin.000065缺少主

10、键背景Binlog：Row模式，没有主键，表数据量3000万左右主上执行DELETEFROM*_snsdb.*_gamedata_service缺少主键mysqlbinlog -vv -base64-output=decode-rows mysqld-relay- bin.000065 -start-position=542989459|less缺少主键3.0观测视角，火焰图背景知识Different types of flame graphs:On-CPUOff-CPUHot/ColdDifferentialMemory背景知识Profiling tools:Linux:Solaris, i

11、llumos, FreeBSD: Mac OS X:Windows:perf, SystemTap, DTraceDTrace and Instruments Xperf.exe参考文献：Mysqld DTrace Probe Reference /doc/refman/5.7/en/dba-dtrace-mysqld-ref.html用 Systemtap 探 索 MySQL /docs/archives/168场景1：基准测试Mysql5.7测试： 总规模1亿行 数据量大小15G 单表只读 4Gbuffer其他默认场景1：基准测试原生mysql测试，总数据量1亿， 数据量大小15G, 4G

12、buffer，其他默认场景1：基准测试原生mysql测试，总数据量1亿， 数据量大小15G, 8Gbuffer场景1：基准测试Mysql5.7测试： 总规模1亿行 数据量大小15G 单表只读 4G/8G buffer场景2：中间件性能损失测试单实例5.7VS中间件+单实例5.7场景2：中间件性能损失测试MySQLProxy场景2：中间件性能损失测试Mysql_no_sslProxy场景2：中间件性能损失测试单实例5.7VS中间件+单实例5.74.0为神马参数也不一致MySQL配置参数和生效参数不一致背景客户端报错：Cant open file: .testmytable.frm (errno:

13、 24) shell perror 24OS error code 24: Too many open filesmysql的配置文件是这么写的 open_files_limit =4000max_connections = 500table_open_cache = 1000系统 ulimit -n是这样子设的 1500mysql动态生效的值是这样子的 open_files_limit = 1500max_connections = 500table_open_cache = 495MySQL配置参数和生效参数不一致MySQL调整参数的方式根据配置(三个参数的配置值或默认值)计算 Reque

14、st_open_files(需要的文件描述符)获取有效的系统的限制值 effective_open_files根据effective_open_files调整request_open_files根据调整后的request_open_files，计算实际生效的参数值(showvariables)MySQL配置参数和生效参数不一致MySQL调整参数的方式1.根据配置(三个参数的配置值或默认值)计算 request_open_files(需要的文件描述符)request_open_files=maxlimit_1= max_connections + table_cache_size * 2 + 1

15、0; limit_2= max_connections * 5;limit_3= open_files_limit ? open_files_limit : 5000;/mysqlmax_connections = 500table_open_cache = 1000 open_files_limit=4000/ulimit -n 15002510250050005000！MySQL配置参数和生效参数不一致MySQL调整参数的方式2. 获取有效的系统的限制值effective_open_fileseffective_open_files:/mysqlmax_connections = 500t

16、able_open_cache = 1000 open_files_limit=4000/ulimit -n 150015005000?NO1500MySQL配置参数和生效参数不一致MySQL调整参数的方式3.根据effective_open_files 调整request_open_files/mysqlmax_connections = 500table_open_cache = 1000 open_files_limit=4000/ulimit -n 150015005000requested_open_files=min(effective_open_files，request_ope

17、n_files)结论1:open_files_limit=1500MySQL配置参数和生效参数不一致MySQL调整参数的方式4. 根据调整后的request_open_files，计算实际生效的参数值(show variables 可查看参数值)max_connections/mysql max_connections = 500table_open_cache = 1000 open_files_limit=4000/ulimit -n 1500Min（limit = requested_open_files - 10 - TABLE_OPEN_CACHE_MIN * 2 ，max_connections）1500-10-800=690500table_cache_sizeMin（max(TABLE_OPEN_CACHE_MIN,(requested_open_files 10 - max_connections)/2)，table_cache_size400（1500-10-500)/2=495结论： max_connections =500 table_open_cache=4951000默认值为400MySQL配置参数和生效参数不一致mysql的配置文件是这么写的 open_f