MySQL工作中常用的参数

基本参数配置

datadir：指定mysql的数据目录位置，用于存放mysql数据库文件、日志文件等。

　　配置示例：datadir=D:/wamp/mysqldata/Data

default-character-set：mysql服务器默认字符集设置。

　　配置示例：default-character-set=utf8

skip-grant-tables：当忘记mysql用户密码的时候，可以在mysql配置文件中配置该参数，跳过权限表验证，不需要密码即可登录mysql。

关于慢查询的配置信息

log_slow_queries ON/OFF慢查询sql记录

slow_lauch_time 默认为2秒 规定查询时长超过多久算是慢查询

slow_query_log ON/OFF 开启慢查询记录日志

slow_query_log_file 慢查询记录日志文件的存放路径

日志相关

log-error：指定错误日志文件名称，用于记录当mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。

　　配置示例：log-error="WJT-PC.err"（默认在mysql数据目录下）

log-bin：指定二进制日志文件名称，用于记录对数据造成更改的所有查询语句。

　　配置示例：log-bin="WJT-PC-bin.log"（默认在mysql数据目录下）

binlog-do-db：指定将更新记录到二进制日志的数据库，其他所有没有显式指定的数据库更新将被忽略，不记录在日志中。

　　配置示例：binlog-do-db=db_name

binlog-ignore-db：指定不将更新记录到二进制日志的数据库，其他没有显式忽略的数据库都将进行记录。

　　配置示例：binlog-ignore-db=db_name如果想记录或忽略多个数据库，可以对上面两个选项分别使用多次。

sync-binlog：指定多少次写日志后同步磁盘。
  N>0  — 每向二进制日志文件写入N条SQL或N个事务后，则把二进制日志文件的数据刷新到磁盘上；

  N=0  — 不主动刷新二进制日志文件的数据到磁盘上，而是由操作系统决定；

推荐配置组合：

  N=1,1  — 适合数据安全性要求非常高，而且磁盘IO写能力足够支持业务，比如充值消费系统；

  N=1,0  — 适合数据安全性要求高，磁盘IO写能力支持业务不富余，允许备库落后或无复制；

  N=2,0或2,m(0<m<100)  — 适合数据安全性有要求，允许丢失一点事务日志，复制架构的延迟也能接受；

  N=0,0  — 磁盘IO写能力有限，无复制或允许复制延迟稍微长点能接受，例如：日志性登记业务；
　　配置示例：sync-binlog=N

general-log：是否开启查询日志记录。

　　配置示例：general-log=1

general_log_file：指定查询日志文件名，用于记录所有的查询语句。

　　配置示例：general_log_file="WJT-PC.log"（默认在mysql数据目录下）

slow-query-log：是否开启慢查询日志记录。

　　配置示例：slow-query-log=1

slow_query_log_file：指定慢查询日志文件名称，用于记录消耗时间较长的查询语句。

　　配置示例：slow_query_log_file="WJT-PC-slow.log"（默认在mysql数据目录下）

long_query_time：设置慢查询的时间，超过这个时间的查询语句才记录日志。

　　配置示例：long_query_time=10（单位：秒）

log-slow-admin-statements：是否将慢管理语句（例如OPTIMIZE TABLE、ANALYZE TABLE和ALTER TABLE）写入慢查询日志。

存储引擎相关

default-table-type：设置mysql的默认存储引擎。

innodb_data_home_dir：InnoDB引擎的共享表空间数据文件根目录。若没有设置，则使用mysql的datadir目录作为缺省目录。

innodb_data_file_path：单独指定共享表空间数据文件的路径与大小。数据文件的完整路径由innodb_data_home_dir与这里配置的值组合起来，文件大小以MB单位指定。

　　配置示例：innodb_data_home_dir=innodb_data_file_path=ibdata1:12M;/data/mysql/mysql3306/data1/ibdata2:12M:autoextend

　　如果想为innodb表空间指定不同目录下的文件，必须指定 innodb_data_home_dir =。这个例子中会在datadir下建立ibdata1，在/data/MySQL/mysql3306/data1/目录下创建ibdata2。

innodb_file_per_table：是否开启独立表空间，若开启，InnoDB将使用独立的.idb文件创建新表而不是在共享表空间中创建。
启用单表空间，减少共享表空间维护成本，减少空闲磁盘空间释放的压力。另外，大数据量情况下 的性能，也会有性能上的提升，为此建议大家使用独立表空间 代替  共享表空间的方式；
　　配置示例：innodb_file_per_table=1

innodb_autoinc_lock_mode：配置在向有着auto_increment列的表插入数据时，相关锁的行为。该参数有3个取值：

　　0：tradition传统，所有的insert语 句开始的时候得到一个表级的auto_inc锁，在语句结束的时候才能释放 这个锁，影响了并发的插入。

　　1：consecutive连续，mysql可以一次生成 几个连续的auto_inc的值，auto_inc不需要一直保持到语句结束，只要 语句得到了相应的值后就可以提前释放锁（这也是mysql的默认模式）。

　　2：interleaved交错，这个模式下已经没有了auto_inc锁，所以性能是最好的，但是对于同一个语句来说它得到的auto_inc的值可能不是连续的。

　　配置示例：innodb_autoinc_lock_mode=1

low_priority_updates：在myisam引擎锁使用中，默认情况下写请求优先于读请求，可以通过将该参数设置为1来使myisam引擎给予读请求优先权限， 所有的insert、update、delete和lock table write语句将等待直到受影响的表没有挂起的select或lock table read。

　　配置示例：low_priority_updates=0（默认配置）

max_write_lock_count：当一个myisam表的写锁定达到这个值后，mysql就暂时 将写请求优先级降低，给部分读请求获得锁的机会。

innodb_lock_wait_timeout：InnoDB锁等待超时参数，若事务在该时间内没有获 得需要的锁，则发生回滚。

　　配置示例：innodb_lock_wait_timeout=50（默认50秒）

max_heap_table_size：设置memory表的最大空间大小，该变量可以用来计算 memory表的max_rows值。在已有memory表上设置该参数是没有效果 的，除非重建表。

innodb_flush_log_at_trx_commit = N：

  N=0  – 每隔一秒，把事务日志缓存区的数据写到日志文件中，以及把日志文件的数据刷新到磁盘上；

  N=1  – 每个事务提交时候，把事务日志从缓存区写到日志文件中，并且刷新日志文件的数据到磁盘上；

  N=2  – 每事务提交的时候，把事务日志数据从缓存区写到日志文件中；每隔一秒，刷新一次日志文件，但不一定刷新到磁盘上，而是取决于操作系统的调度；

查询相关

max_sort_length：配置对blob或text类型的列进行排序时使用的字节数（只对配置的前max_sort_length个字节进行排序，其他的被忽略）

max_length_for_sort：mysql有两种排序算法，两次传输排序和单次传输排序。当查询需要所有列的总长度不超过max_length_for_sort时，mysql使用 单次传输排序，否则使用两次传输排序。

optimizer_search_depth：在关联查询中，当需要关联的表数量超过optimizer_search_depth的时候，优化器会使用“贪婪”搜索的方式查找“最优”的关联顺序。

buffer_size

 key_buffer_size

 key_buffer_size:只能缓存MyISAM或类MyISAM引擎的索引数据，而innodb_buffer_pool_size不仅能缓存索引数据，还能缓存元数据，但是对于我们只使用InnoDB引擎的数据库系统而言，此参数值也不能设置过于偏小，因为临时表可能会使用到此键缓存区空间，索引缓存区推荐：64M；
uery_cache_type  and query_cache_size

n  query_cache_type=N

  N=0  —- 禁用查询缓存的功能；
  N=1  —- 启用产讯缓存的功能，缓存所有符合要求的查询结果集，除SELECT SQL_NO_CACHE..， 以及不符合查询缓存设置的结果集外；
  N=2  —- 仅仅缓存SELECT SQL_CACHE …子句的查询结果集，除不符合查询缓存设置的结果集外；

  n  query_cache_size

  查询缓存设置多大才是合理？至少需要从四个维度考虑：
  ①   查询缓存区对DDL和DML语句的性能影响
  ②   查询缓存区的内部维护成本；
  ③   查询缓存区的命中率及内存使用率等综合考虑
  ④   业务类型

innodb_commit_concurrency  含义：同一时刻，允许多少个线程同时提交InnoDB事务，默认值为0，范围0-1000。

0      — 允许任意数量的事务在同一时间点提交；

N>0  — 允许N个事务在同一时间点提交； 
注意事项：

①   mysqld提供服务时，不许把 innodb_commit_concurrency 的值从0改为非0，或非0的值改为0；
②   mysqld提供时，允许把 innodb_commit_concurrency 的值N>0改为M，且M>0；