[计算机软件及应用]MySQL Troubleshooting笔记

1、mysql troubleshootingmysql troubleshooting读书笔记目录mysql troubleshooting1简介4概要4文章组织架构4chapter 1 basics5incorrect syntax5wrong results from a select5when the problem may have been a previous update5getting information about a query6tracing back errors in data6slow queries6when the server does not answer

2、7issues with solutions specific to storage engines8permission issues8chapter 2 you are not alone: concurrency issues9locks and transactions9transaction：9metadata locking10how concurrency affects performance10other locking issues10replication and concurrency11effectively using mysql troubleshooting t

3、ools11chapter 3 effects of server options14service options14variables that are supposed to change the server behavior14options that limit hardware resources14using the -no-defaults option14performance options15haste makes waste15the set statement15how to check whether changes had an effect15descript

4、ions of variables15chapter 4 mysqls environment24physical hardware limits24operating system limits24effects of other software25chapter 5 troubleshooting replication26displaying slave status：26problems with the i/o thread26problems with the sql thread27chapter 6 troubleshooting techniques and tools28

5、the query28errors and logs28information-gathering tools29localizing the problem (minimizing the test case)30general steps to take in troubleshooting31testing methods31special testing tools31maintenance tools32chapter 7 best practices33backups33gathering the information you need33testing33prevention3

6、3think about it!33简介概要 understand the source of a problem, even when the solution is simple. handle problems that occur when applications run in multiple threads. debug and fix problems caused by configuration options. discover how operating system tuning can affect your server. use troubleshooting

7、techniques specific to replication issues. get a reference to additional troubleshooting techniques and tools, including third-party solutions. learn best practices for safe and effective troubleshooting and for preventing problems.文章组织架构如何定位问题原因。最佳方法是利用标准流程定位问题，列出可能的原因，逐个测试。it is very important to

8、identify what the problem is.chapter 1.basics介绍了基本的故障排查技巧，几乎适用于任何情况。本章只涵盖了单线程问题，即可通过单一线程重现问题。chapter 2.you are not alone: concurrency issues介绍了多线程应用或者被其他应用的事务干扰时出现的问题。chapter 3.effects of server options两部分：第一部分是如何调试定位配置选项导致的问题；第二部分是一些重要选项的参考，即需根据需要调整使用。第二部分还包括关于如何解决某些特定选项导致的问题以及如何测试问题是否已被解决的建议和方法。ch

9、apter 4.mysqls environmentmysql运行的硬件及环境的其他方面对mysql的影响。绝大部分的必要信息指向os，这通常需要系统管理员协助解决。在此列出一些mysql dba需关注的点。chapter 5.troubleshooting replication针对主从场景下产生的问题，针对复制中的特有问题。chapter 6.troubleshooting techniques and tools介绍了额外的技巧和工具。首先介绍原理，然后介绍一些可用工具。chapter 7.best practices介绍了安全有效地处理故障的良好的习惯和行为。chapter 1 bas

10、ics基本原则：从最简单的可能原因开始逐步分析。很多情况下，问题是由琐碎的、基础的原因导致的。incorrect syntax 故障排查的第一步：语法错误。如拼写错误。应特别mysql保留字/关键字作为列名的使用。（此类问题很隐蔽，加必要的引号可避免） 应该测试mysql server端所接收到的sql语句。（可确保server端执行的语句符合预期） 程序的输出功能（如php的echo函数）和general log可以帮助你快速获取sql语句示例：select id from t1 where accessible=1;5.1后accessible为保留字php代码$query = selec

11、t * from t4 where f1 in(;for ($i = 1; $i global级别调整-配置文件修改。how to check whether changes had an effect通过状态变量，只读。session级别显示当前会话的状态，而global显示了server启动以来，或flush status以来的状态值。有时设置的参数值因为超过其范围而未能生效。show session | global variables like variable_name或select variable_value from information_schema.session | g

12、lobal_variables where variable_name = variable_namedescriptions of variablesoptions that affect server and client behaviorserver-related options：影响所有的连接和语句。limits and max_* variables：对相关内容进行大小限制。如group_concat_max_len限制了group_concat函数返回内容的字节数；如max_allowed_packet等permissions：权限。如local_infile选项显示是否运行执行

13、load data local infile语句。sql modes：sql_mode可设置server如何类处理客户端输入。如ansi_quotes告诉mysql server使用在ansi sql标准中指定的引号来取代mysql默认值。当遇到奇怪的查询结果时，检查sql mode来分析是否影响到了你的查询。mysql 5.1.38，innodb plugin中innodb_strict_mode可以对插入innodb表的记录进行严格检查。character sets and collations：字符集是字符/符号跟表示它的字节序列之间的对应关系。collations是排序规则，每个字符集

14、可以有多个排序规则。通常应该保持character_set_*变量、collation_*变量以及create语句中选项保持同一。show variables like %char%;show variables like %coll%;当进行排序或比较遇到类似问题时，应检查字符集选项和表创建语句。operating system handling for lower_case* options：lower_case_filesystem和lower_case_table_names表明os如何处理数据库对象的大小写问题。最好不要调整，尤其是os不区分大小写时。最好为表使用统一的命名，不要在不

15、区分大小写的os上混合使用大小写。init sql：决定了server是否需要在不同时间自动执行一些sql语句。init_file：server启动后自动运行的sql脚本文件init_connect：客户端连接时执行请求前所需做的操作，仅对非super权限的用户起作用。init_slave：作为slave启动sql线程时需执行的操作两个典型的错误用法：忘记在选项中设置了什么；期望init_connect的内容可以对super用户有效。open_files_limit：限制了mysql server可打开的文件数总量。太小则会导致经常性error。反映了硬件限制。log_warnings（是否将

16、警告信息记录到错误日志）：并非sql执行时的warnings，而是揭示server内部运行的调试信息。非0时，会记录warnings到错误日志文件；=2时，记录连接错误日志；在slave上设置为1，则会打印其诊断信息，如binlog、relay-log位置以及同步状态等。replication options：master与slave之间的关系binlog-* and replicate-* filters：相关过滤参数binlog-do-*、replicate-do-*、binlog-ignore-*及replicate-ignore-*。对于slave还有replicate-wild-do

17、-*和replicate-wild-ignore-*。常见问题：特定语句没有被同步；slave上报错“unknown table table_name on query”；statement-based下，一些语句未被同步或者同步出错。常见原因：忘记配置了这些选项；只有显式地use dbname时，过滤条件方会起作用。binary log formats：5.1后可在session级别动态调整。set binlog_format = row | statement | mixed;binlog_direct_non_transactional_updates：指定了非事务表上的update何时

18、应该被写入binlog。可在session级别动态调整，只在statement-based下有效。默认情况下，使用事务时，mysql会先将对非事务表上的更改放入缓存，commit后刷新至binlog。如果未提交之前，其他线程操作了非事务表的相同记录，则会导致主从数据不一致。log_bin_trust_function_creators：告诉mysqld当一个未拥有super权限的用户在master上创建非确定性函数时不触发warnings。binlog_cache_size and friends：包括binlog_cache_size、binlog_stmt_cache_size、max_b

19、inlog_cache_size和max_binlog_stmt_cache_size。在写入binlog之前，可缓存的事务中非事务性/事务性语句的大小。当超过max_binlog_cache_size时，语句失败并报错multi-statement transaction required more than max_binlog_cache_size bytes of storage。检查状态变量binlog_cache_use、binlog_stmt_cache_use、binlog_cache_disk_use和binlog_stmt_cache_disk_use，查看binlog c

20、ache使用频次、事务size超过binlog_cache_use和binlog_stmt_cache_use的频度。当事务大小超过缓存大小时，临时表会被用来存放事务缓存。slave_skip_errors：忽略特定的复制错误。会导致主从数据不一致而且难易发现。read_only：对super权限用户无效，且临时表依然允许被创建。engine options：应该熟悉所用存储引擎所有的选项。innodb optionsinnodb_autoinc_lock_mode：指明了向autoincrement字段插入值时innodb所用的锁模式-traditional、consecutive和inte

21、rleaved。consecutive最安全。innodb_file_per_table：是否使用单独表空间存储表。共享表空间存储表定义。只对设置后新建的表有效。innodb_table_locks：定义了innodb如何处理lock table语句的表锁请求。默认（开启）是立即返回并内部锁定表。关闭后，线程直到所有的锁都被释放之后才会从lock tables write返回。innodb_lock_wait_timeout：事务等待锁定资源的超时时间。超时后报错error 1205 (hy000): lock wait timeout exceeded; try restarting tra

22、nsaction。应该在应用级别处理锁等待后的error，并且不宜设置太大。要根据应用中正常事务所花费的时间来设置。innodb_rollback_on_timeout：一个query因锁等待error中止后，仅最后一条语句被回滚，整个事务并没有被中断。若设置为1，则锁等待超时会导致整个事务被回滚。innodb_use_native_aio：指定innodb使用linux上本地aio接口，还是其自己的实现-simulated aio。该选项被设置后，innodb调度io请求到内核，提高了扩展性。但在遇到加载shared libraries时报错libaio.so.1: cannot open

23、shared object file: no such file or directory.时，应关闭该选项。（innodb plugin1.1引入）innodb_locks_unsafe_for_binlog：控制间隙锁的使用。默认为0，使用间隙锁。不可在session级别使用。myisam optionsmyisam_data_pointer_size：未指定max_rows创建myisam表时，所默认的指针大小。默认为6，2-7之间有效；指针越大，表可包含的行越多。默认值6允许创建256tb大小的表。myisamchk -dvi可以精确地获知指定指针大小时表能达多大，以及使用固定行格式时可存多少行。myisam_recover_options：告知mysql server每次打开myisam表时均检查表是否毁坏或未正常关闭。如果检查失败，则mysql执行check table，必要时会进行修复。可能的值：off、de