mysql配置参数详解[通俗易懂]

大家好，又见面了，我是你们的朋友全栈君。

    [client]
    #password = your_password
    port        = 3306
    socket      = /tmp/mysql.sock
    
    [mysqld]
    port        = 3306
    socket      = /tmp/mysql.sock
    datadir = /usr/local/mysql/var
    skip-external-locking
    key_buffer_size = 32M
    max_allowed_packet = 1M
    table_open_cache = 128
    sort_buffer_size = 768K
    net_buffer_length = 8K
    read_buffer_size = 768K
    read_rnd_buffer_size = 512K
    myisam_sort_buffer_size = 8M
    thread_cache_size = 16
    query_cache_size = 16M
    tmp_table_size = 32M
    
    explicit_defaults_for_timestamp = true
    #skip-networking
    max_connections = 500
    max_connect_errors = 100
    open_files_limit = 65535
    
    log-bin=mysql-bin
    binlog_format=mixed
    server-id   = 1
    expire_logs_days = 10
    
    #loose-innodb-trx=0
    #loose-innodb-locks=0
    #loose-innodb-lock-waits=0
    #loose-innodb-cmp=0
    #loose-innodb-cmp-per-index=0
    #loose-innodb-cmp-per-index-reset=0
    #loose-innodb-cmp-reset=0
    #loose-innodb-cmpmem=0
    #loose-innodb-cmpmem-reset=0
    #loose-innodb-buffer-page=0
    #loose-innodb-buffer-page-lru=0
    #loose-innodb-buffer-pool-stats=0
    #loose-innodb-metrics=0
    #loose-innodb-ft-default-stopword=0
    #loose-innodb-ft-inserted=0
    #loose-innodb-ft-deleted=0
    #loose-innodb-ft-being-deleted=0
    #loose-innodb-ft-config=0
    #loose-innodb-ft-index-cache=0
    #loose-innodb-ft-index-table=0
    #loose-innodb-sys-tables=0
    #loose-innodb-sys-tablestats=0
    #loose-innodb-sys-indexes=0
    #loose-innodb-sys-columns=0
    #loose-innodb-sys-fields=0
    #loose-innodb-sys-foreign=0
    #loose-innodb-sys-foreign-cols=0
    
    default_storage_engine = InnoDB
    innodb_data_home_dir = /usr/local/mysql/var
    innodb_data_file_path = ibdata1:10M:autoextend
    innodb_log_group_home_dir = /usr/local/mysql/var
    innodb_buffer_pool_size = 128M
    innodb_log_file_size = 32M
    innodb_log_buffer_size = 8M
    innodb_flush_log_at_trx_commit = 1
    innodb_lock_wait_timeout = 50
    
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [mysql]
    no-auto-rehash
    
    [myisamchk]
    key_buffer_size = 32M
    sort_buffer_size = 768K
    read_buffer = 2M
    write_buffer = 2M
    
    [mysqlhotcopy]
    interactive-timeout

skip-external-locking
跳过外部锁定，当外部锁定起作用的时候，每个进程访问数据表，则必须等待之前的进程完成操作并解除锁定，由于服务器访问数据库时经常需要等待解锁，所以在单服务器环境下开启外部锁定则会让MySQL性能下降，所以默认开启本项跳过外部锁定

key_buffer_size
myisam表缓冲区的大小。这个大小的设置，可以通过sql语句show global status like ‘key_read%’;，然后用Key_reads / Key_read_requests 的值，如果这个值很大。就需要调大key_buffer_size的大小了。理论上来说，key_buffer_size越小越好，但是过小的话，容易造成内存浪费，一般来说能达到0.001以下就已经非常的好了。

max_allowed_packet
server可接收数据包的小大，如果发送的SQL语句超过了这个允许的大小，系统则会自动限制掉，导致本次查询直接失败。

table_open_cache
该值的设置可使用sql语句show status like ‘open%tables’;查看open_tables的值，如果跟table_open_cache相等，则就需要增加了。但是也要注意不能设置的太高。因为系统有文件描述符的限制，如果设置的太高也会造成性能的不稳定或者连接失败，所以要检测不能超过文件描述符的限制。比如打开myisam需要两个文件描述符。

sort_buffer_size
排序缓冲。我们可以通过增大该值来提高order by 或者 group by 的处理性能，一般设置2M或者4M之前尅满足大多数应用的需求，需要注意的是每次线程都会创建自己独立的缓冲区，而不是整个系统共享的缓冲区，不要因为设置的过大而造成系统的内存不足。

net_buffer_length
TCP/IP和套接字通信缓冲区大小,创建长度达net_buffer_length的行。但有需要时，可以动态扩大到 max_allowed_packet的大小。mysqldump在导出的时候，创建达到net_buffer_lengh长度的sql行。建议默认就ok。

read_buffer_size
设置以顺序扫描的方式扫描表数据的时候分配的缓冲区的大小。每个线程进行顺序扫描的时候都会产生该buffer，而且同一个Query中如果有多个表进行全表扫描，会产生多个该buffer。如果有很多顺序扫描，可以增大该值。默认值为128K，必须为4K的倍数。（不仅仅针对MYISAM，这个针对所有引擎）

read_rnd_buffer_size
(随机读缓冲区大小)这个参数用在sort查询之后，以保证获取以顺序的方式获取到查询的数据。如果你有很多order by 查询语句，增长这值能够提升性能。默认256K

myisam_sort_buffer_size
当对MyISAM表执行repair table或创建索引时，用以缓存排序索引；设置太小时可能会遇到” myisam_sort_buffer_size is too small”

thread_cache_size
设置Thread Cache池中可以缓存的连接池线程最大数量，可设置为0-16384，MySQL<= 5.6.7默认为0，MySQL>= 5.6.8默认值为-1（自动调整）。1GB内存我们配置为8，2GB内存我们配置为16，4GB或4GB以上内在我们配置为64。当客户端断开时，客户端的线程将被缓存，如果当前缓存数少于thread_cache_size时，只有在缓存中没找到的时候才创建新的线程。默认值可以遵循公式：8 + (max_connections / 100)，上限为100。

query_cache_size
指定Mysql查询缓存缓冲区的大小，可以通过在Mysql控制台观察，如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，如果Qcache_hits的值非常大，则表明查询缓冲使用的非常频繁。需要注意的是即使query_cache_type设置为0，query_cache_size设置的内存还是会被分配。MySQL<= 5.6.7默认值为0，MySQL>= 5.6.8默认值为1M。

tmp_table_size
设置内部临时表最大值，如果超过该值，则会将临时表写入磁盘，其范围为1KB至4GB。如果你需要作很多的高级GROUP BY查询而且同时你有很多内存的时候，可以增大这个值。这个值不适用用户创建的内存表。

explicit_defaults_for_timestamp
在5.6.6之后，声明为NOT NULL且没有默认子句的TIMESTAMP列是没有默认值的。往数据表中插入列，又没有给TIMESTAMP列赋值时，如果是严格SQL模式，会抛出一个错误，如果严格SQL模式没有启用，该列会赋值为’0000-00-00 00:00:00′，同时出现一个警告。（这和MySQL处理其他时间类型数据一样，如DATETIME）,当该值为true的时候，则是关闭该警告。

skip-networking
开启该选项可以彻底关闭MYSQL的TCP/IP连接方式，如果WEB服务器是以远程连接的方式访问MYSQL的数据库服务器，则不要开启该选项，否则将无法正常连接。

max_connections
指定MYSQL允许的最大连接进程数，如果在访问程序时经常出现TOO MANY CONNECTIONS的错误提示，则需要增大该参数值。默认值151。SHOW VARIABLES LIKE ‘%max_connections%’;通过查看SHOW STATUS LIKE ‘%conn%’;来设置。不能一味增大该值，否则内存吃不消

max_connect_errors
如果某一客户端尝试连接此MySQL服务器，但是失败（如密码错误等等）指定的次数，则MySQL会无条件强制阻止此客户端连接。如果希望重置此计数器的值，则必须重启MySQL服务器或者执行flush hosts;，当这一客户端成功连接一次MySQL服务器后，针对此客户端的max_connect_errors会清零。

open_files_limit
MySQL打开的文件描述符限制。默认1024。当错误日志显示：Too many open files，则就需要调高该值了。

log-bin
指定该值则表示启动时，mysqld写入包含所有更新数据的sql命令的日志文件，如果没有给出值，则默认名为-bin后面所跟的主机名

binlog_format
指定mysql的复制模式：①基于sql语句的复制statement；②基于行的复制row；③混合模式复制mixed(一般的复制使用statement，对于该模式无法复制的则使用row保存)

server-id
mysql同步的数据中包含server-id，用于标识该语句最初是从哪个server写入。每一个同步中的slave在master上都对应一个master线程，该线程就是通过slave的server-id来标识的；每个slave在master端最多有一个master线程，如果两个slave的server-id 相同，则后一个连接成功时，前一个将被踢掉。

expire_logs_days
设置二进制日志的过期天数，超过天数的日志则自动删除，当值是0的时候表示永不过期。

default_storage_engine
设置默认采用的存储引擎。默认是innodb

innodb_data_home_dir
指定存放数据文件的路径，如果没有指定，系统则在MySQL的datadir目录下创建数据文件，如果为空，则可以在innodb_data_file_path中指定一个决定路径，innodb不创建目录，所以在启动前请确认指定的目录是否已经存在，如果不存在，则需要进行创建。

innodb_data_file_path
指定所有innodb数据文件的路径，还指定了初始大小配置，最大分配以及超出起始分配界限是否应当增加文件的大小。格式如下：path-to-datafile:size-allocation[:autoextend[:max-size-allocation]]，本配置ibdata1:10M:autoextend则指定了数据文件的名称是idbata1，初始大小为10M，超出起始大小后自动增加文件的大小。

innodb_log_group_home_dir
事务被提交并写入到表空间磁盘文件上之前，事务数据存储位置。通常这个目录设置与innodb_data_home_dir变量相同，但是为了获得最大性能，建议分离两个目录到单独的物理存储阵列上，这样可以保证IO资源不起冲突，利于服务器处理大量高并发连接。

innodb_buffer_pool_size
定义了 InnoDB 存储引擎的表数据和索引数据的最大内存缓冲区大小。和 MyISAM 存储引擎不同， MyISAM 的 key_buffer_size 只能缓存索引键，而 innodb_buffer_pool_size 却可以缓存数据块和索引键。一般来说，它越大Innodb的吞吐量(单位：tps)就越高。所以，在不影响服务器上其他程序正常工作的情况下，这个值总是越大越好。适当的增加这个参数的大小，可以有效的减少 InnoDB 类型的表的磁盘 I/O 。在一个以 InnoDB 为主的专用数据库服务器上，可以考虑把该参数设置为物理内存大小的 60%-80%。但是当innodb做crash recovery的时候，大的pool buffer会让recovery奇慢无比。 一种折衷的解决方法就是：启动的时候用小的pool buffer，恢复完成以后改用大的pool bufer。默认值128M，如果是专用mysql服务器，可设置为操作系统的内存的70%~80%，如果还有nginx等，可以设置为50%。

innodb_log_file_size
每个日志文件在日志组中的大小，不过innodb_log_file_size * innodb_log_files_in_group的值不能超过一个略小于512GB的最大值。通常设置256MB就足够了。

innodb_log_buffer_size
这是InnoDB存储引擎的事务日志所使用的缓冲区。InnoDB 在写事务日志的时候，为了提高性能，也是先将信息写入 Innofb Log Buffer 中，当满足 innodb_flush_log_trx_commit 参数所设置的相应条件（或者日志缓冲区写满）之后，才会将日志写到文件（或者同步到磁盘）中。可以通过 innodb_log_buffer_size 参数设置其可以使用的最大内存空间。一个大的日志缓冲区能够在事务提交前不将日志写入磁盘，所以，如果你一个事物更新多行数据，设置这个值能有效节省IO。默认8M。

innodb_flush_log_at_trx_commit
抱怨Innodb比MyISAM慢100倍？那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电池供电缓存（Battery backed up cache）时。设成1对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统挂了时才可能丢数据。

innodb_lock_wait_timeout
该值指的是事务等待获取资源等待的最长时间，超过这个时间还未分配到资源则会返回应用失败。参数的时间单位是秒，最小可设置为1s(一般不会设置得这么小)，最大可设置1073741824秒（34年，一条语句锁等待超过30分钟估计业务该有反馈了）

默认安装时这个值是50s

innodb_autoinc_lock_mode
为了解决自增主键锁表的问题而引入该值。该值为0时每次申请自增主键时需要锁表；该值为1的时候会对insert语句进行预判，如果可预判需要的ID则执行新方式，申请轻量锁，如果不可预判，则申请表锁；该值为2则全部申请轻量锁，这样可保证并发最高，但是会发生成的自增值不一致的情况。默认是1。

max_allowed_packet
设定在网络传输中一次消息传输量的最大值，MySQL<= 5.6.5系统默认值为1M，MySQL>= 5.6.6默认值为4M，取值范围（1M~1G），必须设定为1024的倍数，单位为字节。

no-auto-rehash
在终端中不启用tab补齐功能。当改为auto-rehash则再次登录MySQL则就可以使用命令自动补全了。在windows版本中不可用。这是windows版本的一个bug。