mysql中的参数比较多，有数百个，如果都一一记住是比较困难的，当然，如果你是天才，则另当别论，反正我是记不住，哈哈。虽然记不住，但是笨人有笨人的办法。俗话说，好记性不如烂笔头，为了便于后期巩固和查阅，我将涉及性能以及日常问题的一些参数进行归纳总结。我将大类分为了5类，包括连接超时，主从同步，日志相关，事务相关，以及buffer相关，每个小类又细分为若干小类，对于连接超时，主从同步，日志相关，事务相关这四个模块，将会在该篇笔记中进行总结，对于buffer相关的，感觉这部分相对复杂，等我细研究一番再来总结吧。好了，上图吧。
上面的图是一个整体框架，每个小项没有细化了，小项我放到具体的模块了，因为放在一张图里是真的放不下啊。
根据连接超时原因及表现的不同，我将连接超时分为以下四类，读写相关，连接相关，事务相关，主从复制相关。如下:
读写相关:
net_read_timeout:该参数控制连接在终止读之前服务从客户端读取数据等待的时间秒数。
net_write_timeout:该参数控制连接在终止写之前服务写数据block到客户端等待的时间秒数。
这两个参数与连接读写相关，当网络比较差或网络繁忙时可能导致超时，一般可能有如下报错:Lost connection to MySQL server during query……，通常可以通过调大该参数来解决超时问题。
连接相关:
connect_timeout:该参数控制的是连接过程中tcp握手的超时时间。如果在设定的时间内未完成连接握手过程，则会连接失败，，可以通过show global status like "Aborted_connects"，每次初始连接尝试被终止后，该值会加1。一般可能报错Lost connection to MySQL server at,解决方法也是增加该值。具体可参考https://dev.mysql.com/doc/refman/5.7/en/error-lost-connection.html。
wait_timeout:该参数控制的是服务器在关闭非交互式连接之前等待其活动的秒数。一般可能报错MySQL server has gone away。可以通过调整该参数或重新连接即可。
interactive_timeout:该参数控制的是服务器在关闭交互式连接之前等待其活动的秒数。与wait_timeout的区别在于一个作用于交互式连接，一个作用于非交互性连接。
这三个参数都和连接及连接超时相关，遇到连接失败或超时问题，可以先尝试调整这三个参数看看是否能解决问题。
事务相关:
lock_wait_timeout:该参数控制获取元数据锁的超时时间，适用于对表，视图，存储过程，dml操作等所有使用元数据锁的语句。不适用于对系统表的隐式访问及延迟插入。
innodb_lock_wait_timeout:该值控制Innodb事务放弃等待行锁的秒数。一般可能会有如下报错:Lock wait timeout exceeded;try restarting transaction,如果发生锁等待超时，当前语句会被回滚。对于高度交互的应用程序或OLTP系统，可以适当减小该值，对于长时间运行的后端操作例如数据仓库中等待其他大型插入或更新操作完成的转换步骤，可以适当增加该值。该参数仅适用于Innodb行锁，不适用于表锁及死锁。
innodb_rollback_on_timeout:该值控制Innodb事务发生锁超时时是否回滚整个事务，默认值为OFF，即不回滚整个事务。
innodb_flush_log_at_timeout:该值控制刷新日志的时间，默认值为1s，仅对于innodb_flush_log_at_trx_commit设置为0或者2时有效，对innodb_flush_log_at_trx_commit设置1时无效。
这四个参数基本都和事务及事务中涉及的锁或事务安全性相关，了解即可，非必要一般使用默认值即可。
主从复制相关:
slave_net_timeout:该值控制slave没有收到来自master的binlog event数据或心跳信号后认为连接断开中止读取并尝试重新连接的秒数。默认60s,设置该变量后必会立即生效，而是在执行start slave后生效。与该参数相关的几个参数还需要注意一下:
master_heart_period:该值表示心跳检测的周期，在该周期中，如果master没有发送binlog event数据给slave,则会发送心跳信号给slave，默认为slave_net_timeout值的1/2，当修改slave_net_timeout时，不会自动修改该值，需要使用change master to命令手动修改，如果设置该值为0(禁用heartbeat)或者大于slave_net_timeout，当主库没有binlog event数据发送给slave时会导致slave频繁重连master。
master_connect_retry:该值控制超时后冲连尝试的次数，默认86400次。
master_retry_count:该值控制第一次slave_net_timeout超时立即重连后的后续重连时间间隔，默认60s。
这几个参数值可以通过select Connect_retry,Heartbeat, Retry_count from mysql.slave_master_info;或者select * from performance_schema.replication_connection_configuration\G语句进行查看。
rpl_stop_slave_timeout:该值控制执行stop slave命令的时间，默认值为1年，如果执行stop slave的时候slave上正在重放执行大事务，可能会导致死锁或阻塞，影响性能。可以调整该值，达到超时时间后，客户端会返回一条错误信息，但是stop slave命令依旧有效，会在复制线程不再繁忙时继续执行并停止复制。可参考https://dev.mysql.com/doc/refman/5.7/en/replication-options-replica.html#sysvar_rpl_stop_slave_timeout。
rpl_semi_sync_master_timeout:该值控制主从复制模式从半同步复制蜕变为异步复制的超时时间，单位为毫秒，默认10000，当master在事务提交阶段等待该值设定的时间内接收到的来自slave的应答数量低于设定值，则会从半同步转为异步模式。
max_allowed_packet:该值控制slave上实际可以接受的数据包的最大值。当mysql客户端或mysqld服务器接收到大于 max_allowed_packet字节的数据包时，会报错ER_NET_PACKET_TOO_LARGE。可通过增大该值解决。
slave_pending_jobs_size_max:该值控制slave上可用于保存传入的数据包的最大内存，设定的内存在所有副本工作队列间共享。如果一个由一个或多个数据包组成的大事件超过该值，事务会一直保持至所有副本工作人员都有空队列才进行处理。该值应尽量设置的足够大。且需要大于主库max_allowed_packet的大小。一般会有如下报错Cannot schedule event Write_rows, relay-log name ./mysql-relay-bin.xxx, position xxx to Worker thread because its size xxx exceeds xxx of slave_pending_jobs_size_max。
这几个参数都和主从复制传输相关，涉及复制模式，数据包传输，心跳机制，主从同步异常时，可以从这几个参数进行排查。
根据主从同步中的不同模块及常见问题，我将主从同步的参数分为以下三类，复制模式，同步延迟以及同步报错。
复制模式相关：
slave_parallel_type:该值是控制事务在slave上并行执行的策略，在5.6中，该值为database，即基于schema的并行复制，仅当数据被分区到多个数据库且这些数据库在源上独立并同时更新时该值才生效。如果是单库实例，则并不会生效，另外，可能还会导致主从事务先后顺序的问题。在5.7中，该值为logic_clock,即基于group commit的并行复制。可以实现slave上的事务回放与主库一致。当slave_preserve_commit_order值为1时，只能使用logic_clock。该值与下面的参数slave_parallel_workers配合使用，仅当slave_parallel值大于0时，才会进行并行复制。
slave_parallel_workers:该值控制并行执行复制事务的应用线程数，默认为0，即不开启并行复制，修改slave_parallel_workers值在后续start slave执行后生效。
rpl_semi_sync_master_enabled:该值控制在master上是否启用半同步复制，默认值为off,表示关闭，在设置半同步复制时需要修改该值为on。
rpl_semi_sync_slave_enabled:该值控制在slave上是否启用半同步复制，默认值为off,表示关闭，在设置半同步复制时需要修改该值为on。
rpl_semi_sync_master_wait_point:该值在5.7.2添加，控制在半同步复制模式下master向提交事务的客户端返回状态之前等待接收事务的slave确认的点，有两个值，after_sync(默认)，master会将客户端提交的每个事务写入binlog和slave服务器，并将binlog同步到磁盘，在同步之后，master等待slave确认接收的事务，在接收到确认后，master提交事务并将执行结果返回给客户端，此时即使master发生故障，异常crash发生主从切换，数据不会丢失，但是因为此时源master的binlog日志可能包含未提交的事务，因此需要被丢弃。after_commit:master将客户端提交的每个事务写入binlog和slave服务器，并提交事务，提交后master等待slave对事务接收的确认，接收到确认后master将执行结果返回给客户端。此时其他客户端可以在客户端事务提交之前看到提交的事务，如果master发生故障，异常crash发生主从切换，这些客户端会发现他们之前能在master查看到的部分数据丢失。
rpl_semi_sync_master_wait_no_slave:该值表示在半同步复制模式下master收到来自slave的确认数不满足时是否立即转换为异步模式。默认值为on，此时在没有事务提交的空闲时间里，即使master收到来自slave的确认数小于rpl_semi_master_wait_for_slave_count，也不会立刻转换为异步模式，只要在事务超时之前，master收到来自slave的确认数不小于rpl_semi_sync_master_wait_for_slave_count，则会继续保持半同步模式，反之则转换为异步模式。如果rpl_semi_sync_master_wait_no_slave值为off,只要master收到来自slave的确认数小于rpl_semi_sync_master_for_slave_count，则master立即转换为异步模式。但是无论rpl_semi_sync_master_wait_no_slave值设置为on还是off，只要master接收到来自slave的确认不小于rpl_semi_sync_master_wait_for_slave_count，则master自动会从异步模式转换为半同步模式。
gtid_mode:该值控制是否启用GTID的日志记录及日志可以包含的事务类型。在mysql5.7.5之前，该参数需要与log-bin和log-slave-updates配合使用，在mysql5.7.6之前，该变量是只读的，之后在enforce_gtid_consistency设置为on的前提下可以动态设置。在mysql5.7.6之前，该值只有on和off两个可选值，在mysql5.7.6及其之后又新增了两个可选值。on_permissive和off_permissive。
log_slave_updates:该值控制slave服务器从master收到的更新记录是否记录到slave自己的binlog文件中，默认该值是off，即不记录。但是如果希望进行级联复制时，例如，A->B->C,则B需要设置log_slave_updates值为on。
master_auto_position:该值控制slave在同步复制时使用基于gtid的自动定位而不是基于文件和位置点的定位，使用gtid模式时该值需要设置为on。
enforce_gtid_consistency:该值控制为了实现GTID一致性而是否允许执行事务的值，有三个可选项，on，warn，off，当设置为on时，不允许任何事务违反GTID一致性，当设置为warn时，允许所有事务违反GTID一致性，但是会生成警告，当设置为off时，允许所有事务违反GTID一致性。如果开启gtid复制，该值必须为on。当设置为on时，执行某些操作时会报错，如Error Code: 1786 Statement violates GTID consistency: CREATE TABLE ... SELECT.具体见官网:https://dev.mysql.com/doc/refman/5.7/en/replication-options-gtids.html#sysvar_enforce_gtid_consistency
同步延迟:
sync_binlog:该值控制mysql服务器将binlog二进制日志同步到磁盘的频率,该值有三个选项，0，1，N。当设置为0时，mysql服务器不会控制将binlog_cache信息同步到磁盘，而是操作系统自行决定刷盘时间，或者cache满了后才同步到磁盘。此时性能最好，但是如果发生电源故障或者操作系统崩溃，可能会丢失部分已提交但是未同步到binlog文件的事务。当设置为1时，mysql服务器会在事务提交之前同步binlog日志到磁盘，这是最安全的设置，但是由于磁盘写入次数增加会对性能有损耗，在发生电源故障或操作系统崩溃时，二进制日志中丢失的事务处于准备状态，在恢复时可以进行回滚，从而保证不会在binlog中丢失任何事务。当设置为n时，每进行n次事务提交之后，mysql进行一次磁盘同步刷新binlog_cache中的数据到磁盘。此时较高的值会提高性能，但是会增加数据丢失的风险。
innodb_flush_log_at_trx_commit:该值控制在事务提交时日志刷新mysql的buffer及linux操作系统buffer的时机策略。可选值有0，1，2，当设置为1时，日志在每次事务提交时写入到日志并刷新到磁盘，此时性能最差，但是安全性最高，不会丢失事务数据。当设置为2时，表示每次事务提交的时候会将日志刷新到文件系统的buffer中，然后默认每隔1s调用文件系统的flush操作将缓存刷新到磁盘上，此时，即使mysql服务异常，也不会丢失事务，只有当mysql服务所在的主机操作系统损坏或者突然掉电时，才会丢失1s的事务数据，flush刷新操作的频率可以通过修改innodb_flush_log_at_timeout参数实现。此时在性能和安全性之间进行平衡。当设置为0时候，表示默认每隔1s将日志刷新到操作系统的磁盘上，如果mysql服务异常，可能会丢失1s的事务数据，此时性能最好，但是安全性最差。另外，对于设置为0或者2时，由于ddl更改或者其他innodb内部活动，可能会导致刷新日志的频率有所变化，丢失的事务量对应发生变化。
同步报错:
slave-skip-errors:该参数控制跳过指定类型的错误或者指定类型的错误，通过在配置文件中添加该参数然后重启服务即可实现，如果需要跳过指定类型，选择对应错误码即可，如果需要跳过所有错误，指定该值为all即可，但是注意跳过错误可能会导致主从数据不一致。
sql_slave_skip_counter:该参数控制在传统复制模式下主从复制跳过的事件数，建议每次仅跳过一个事件，设置该值不会立即生效，仅当下一次使用start slave后生效，参考命令:slave stop;set global sql_slave_skip_counter = 1;slave start;而对应的在gtid模式下跳过事务的命令为:set gtid_next='aaa-bbb-ccc-ddd:N';begin;commit;set gtid_next='AUTOMATIC';这里aaa-bbb-ccc-ddd:N需要根据show slave status查看的结果实际设置。另外，gtid_next只能跳过一条事务，可以使用gtid_purged跳过多条事务。
根据日志相关中的不同模块及常见参数，我将日志相关的参数分为以下五类，慢日志，binlog日志，redo日志，undo日志及其他部分。
慢日志:
slow_query_log:该值控制是否开启慢查询日志，默认为off，即不开启慢日志。
long_query_time:该值控制查询执行记录到慢日志的时间，可根据业务需求进行合理设置。
slow_query_log_file:该值控制慢查询日志文件的名称。默认值为host_name-slow.log。
上面三个参数都是控制慢sql相关的参数，另外，如果配置了log_queres_not_using_indexs参数，未使用索引的查询也会被写入慢日志，可能会导致慢日志快速增大，与之相关的还有log_slow_admin_statements，min_examined_row_limit，log_throttle_queries_not_using_indexes 等参数，具体可见官网https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html。另外，还可以通过定时任务按天拆分慢日志，分析的话可以使用mysqldumpslow或者pt-query-digest工具。
binlog日志:
binlog_format:该值控制binlog日志的格式，可选项是row，statament,mixed,但是生产上建议使用row模式，虽然这会占用更大磁盘空间，但是很多开源工具都是依赖于binlog的row模式的，例如binlog2sql，因此综合考虑选择row模式还是更有价值的。
sql_log_bin:该值控制当前会话是否启用日志记录到binlog文件，默认为on,当我们需要执行某些不记录binlog的操作时，可以通过设置该值临时关闭当前会话binlog记录。
expire_log_days:该参数控制binlog日志保留时间。mysql会根据binlog文件的操作系统最近修改时间来判断binlog是否过期，在触发mysql过期删除时，mysql会先检查记录binlog文件信息的index文件，找到目前最老的binlog，检查该文件的系统时间，如果发现该文件未过期，则不做操作，如果发现该文件过期，则会找到下一个binlog文件，进行相同判断，直到找到第一个过期的binlog则停止继续查找并删除该binlog及该binlog之前的所有日志。
max_binlog_size:该值控制binlog文件大小的最大值，如果binlog大小超过该值，则服务器会轮换binlog日志。但是事务会以块的形式写入binlog日志，因此不会在多个binlog日志文件之间拆分，会导致binlog文件大小大于该值。
binlog_row_image:该值控制binlog在row模式下写行图像到binlog的模式，有三个可选值，full(记录所有列),minimal(仅记录更改的列及标识行所需的列),noblob(记录除不需要的blob和text列以外的所有列)，在基于row模式的复制在，灭国行更改事件包含两个图像，before图像(记录要更新的行的列)，after图像，记录行更改。删除行前，仅记录before图像，插入行时，记录after图像，更新行时同时记录两者。默认值为full，不建议修改。
max_binlog_cache_size:该值控制事务期间保存binlog日志更改的缓存大小，语句缓存大小由max_binlog_stmt_cache_size控制。如果该值设置过小，当有大事务时，可能会报错Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage;
redo日志：
innodb_log_file_size:该值控制redo日志组每个日志的大小，对应ib_logfilexx文件，单位字节，默认值较小，如果innodb数据表有大量写入操作，合理调整该值能有效提升mysql性能。
innodb_log_file_in_group:该参数控制redo日志组中日志文件数。默认为2，redo日志是循环写的，假设该值为2，则会有两个redo日志文件ib_logfile0和ib_logfile1,innodb引擎会先写ib_logfile0,写满后会切换到ib_logfile1,并做checkpoint，当ib_logfile1写满后会重新写ib_logfile0。
innodb_log_buffer_size:该值控制脏数据写入到redo日志文件之前由于存储数据的日志缓存区大小，当存在批量更新，删除，插入的事务时，可以扩大该值以节省磁盘io,但是如果设置过大，会导致mysql异常crash恢复时间较长。
innodb_log_group_home_dir:该值控制redo日志文件的存储目录，通常是和数据目录一致，如果条件允许的话，建议放在不同的物理存储阵列上，这样可以保证IO资源不冲突，提升数据库性能。
以上几个参数都是redo日志相关的参数，生产中如果需要调整这几个参数，需要重启mysql服务并将原来的ib_logfilexxx文件备份后移除，备份当然是为了回滚了。
undo日志:
innodb_undo_logs:该值控制innodb回滚段的个数，默认为128，每个回滚段同时支持1024个在线事务，调整该值后不会减少物理数的回滚段，只会调整实际用到的回滚段的个数。对应的别名是innodb_rollback_segments,在mysql5.7中，第一个回滚段总是分配给系统表空间，32个回滚段保留给临时表使用，并驻留在临时表空间ibtmp中。如果设置了单独的undo表空间，该值最少应设置为33+innodb_undo_tablespaces。
innodb_undo_directory:该值控制undo表空间的目录。如果未指定，则在数据目录下创建undo表空间。如果需要修改该值，需要重启mysql被备份后移除undo表空间文件。
innodb_undo_tablespaces:该值控制单独的uodo表空间个数，文件名一般为undoxxx,默认值为0，该值初始化后便不可修改。建议设置该值不小于3，因为在truncate undo表空间时，该文件会处于不活跃状态，此时系统将处于不可用状态。当添加undo表空间时，系统表空间中的回滚段将呈现为非活动状态。
innodb_undo_log_truncate:该值控制是否开启undo表空间的自动truncate。默认值为off，设置为on即可。
innodb_max_undo_log_size:控制undo表空间truncate的阈值。默认1G,可以动态调整。
innodb_purge_rseg_truncate_frequency:该值控制释放回滚段所需的调用purge操作的次数。默认值为128，当undo表空间中的rollback segments被释放后，undo表空间才会被truncate，该参数越小，undo表空间被truncate的频率越高。当undo表空间被truncate时，当前undo表空间中的回滚段被停用，其他undo表空间中的活跃回滚段进行工作，这会有轻微性能损耗，因此该值也不建议设置太小。
以上几个参数都是undo日志相关的参数，通过官网可以得知，其中一些参数已被废弃，这里可以仅作了解，另外，在mysql5.5之前，undo空间一直存储在系统表空间中，当有大事务时，会导致系统表空间被撑大，当然，一般生产上很少有5.6之前的版本了。
其他日志:
log_error:该值控制错误日志的位置，会将错误日志和启动消息写入该文件。
general_log:该值控制是否开启通用查询日志，默认值为off,即不开启，该值建议仅在排查问题时临时开启。
general_log_file:该值控制通用日志的名称。
错误日志是mysql中比较重要的日志之一，日常问题排查必不可少，通用日志一般不开启，因为如果业务写入较频繁时，日志会占用较大空间。
在生产环境，为了保证数据安全性，mysql中我们推荐的是双1设置，即sync_binlog=1,innodb_flush_log_at_trx_commit=1，但是实际上双1并不能保证完全不丢数据，因为许多操作系统和一些磁盘硬件欺骗了刷新到磁盘操作。他们可能会告诉 mysqld已经发生了刷新，即使它还没有发生。在这种情况下，即使使用推荐的设置也无法保证事务的持久性，在最坏的情况下，断电可能会损坏InnoDB数据。在 SCSI 磁盘控制器或磁盘本身中使用电池支持的磁盘缓存可加快文件刷新速度，并使操作更安全。我们可以尝试禁用硬件缓存中磁盘写入的缓存。
mysql从5.7.6开始gtid支持以下四个选项:
off:新的和复制的事务都必须是匿名的。
off_permissive:新的事务是匿名的，slave接受的事务可以是匿名或者gtid。
on_permissive:新事务是gtid的，slave接受的事务可以是匿名的或者gtid的。
on:新事务和复制事务都必须是gtid的。
这四个可选项必须按照顺序变更，不允许跳跃，例如直接从off到on_permissive是不允许的。
传统模式->gtid可以参考官网:https://dev.mysql.com/doc/refman/5.7/en/replication-mode-change-online-enable-gtids.html
gtid模式->传统模式可以参考官网:https://dev.mysql.com/doc/refman/5.7/en/replication-mode-change-online-disable-gtids.html。
但是实际上gtid模式相对传统模式来说方便了很多，为啥要切换到传统模式呢，gtid他不香嘛。
当设置autocommit为on时，事务会自动提交，当使用start trasaction或begin语句时或设置autocommit为off时，正常需要手动commit才会显式提交事务。包含但不限于以下场景会导致隐式提交:
1.在事务中执行了ddl操作命令。
2.隐式使用修改mysql数据库中的表，如alter user，create user，drop user，revoke, set password等。
3.在上一个事务还未提交或回滚时又开启了另外一个事务。
4.修改autocommit变量值。
5.使用lock tables等锁定的语句。
6.执行start slave，stop slave,change master to等主从复制相关命令。
7.执行表分析(analyse table)，表优化(optimize table)，维修表(repair table)，重置(reset),刷新(flush)等操作。
以上便是最近梳理的前面四部分的参数了，连接超时，主从同步，日志相关，事务相关，buffer部分下次再梳理，当然，mysql的参数并不仅仅只有这么多，还有一些零散的常用参数下一篇会单独补充。另外，题外话，任何时候对生产都要有敬畏之心，对于参数调整，除非对该参数比较熟悉才可调整。如果不熟悉，在这里有个小小的建议，一动不如一静，有时可能一个不恰当的参数调整就会造成故障的发生，好了，就这样吧。
END
原文作者：DBA修行之路；如有侵权，请联系管理员删除。
