MySQL配置基本信息参考

配置参数参考，my.cnf

[client]port = 3306[mysqld]#默认存储引擎INNODBdefault-storage-engine=INNODB#GROUP_CONCAT长度group_concat_max_len =99999#端口号port = 3306

#套接字文件

#这里要注意：有时候重启mysql会提示/tmp/mysql.sock不存在.

socket = /usr/local/mysql/mysql.sock

#pid写入文件位置

pid-file = /usr/local/mysql/mysqld.pid

#数据库文件位置

datadir = /home/data/mysql/data

#控制文件打开的个数；

open_files_limit = 10240

#SQL模式

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#当外部锁定（external-locking）起作用时，每个进程若要访问数据表,

#则必须等待之前的进程完成操作并解除锁定。由于服务器访问数据表时经常需要等待解锁,

#因此在单服务器环境下external locking会让MySQL性能下降。

#所以在很多Linux发行版的源中，MySQL配置文件中默认使用了skip-external-locking来避免external locking。skip-external-locking

#跳过DNS反向解析

skip-name-resolve

#关闭TIMESTAMP类型默认值

explicit_defaults_for_timestamp

#不受client字符集影响，保证sever端字符集

skip-character-set-client-handshake

#初始连接字符集UTF8

init-connect='SET NAMES utf8'

#默认数据库字符集

character-set-server=utf8

#查询缓存0,1,2，分别代表了off、on、demandquery_cache_type = 1#单位秒,握手时间超过connect_timeout,连接请求将会被拒绝connect_timeout = 20

#设置在多少秒没收到主库传来的Binary Logs events之后,从库认为网络超时,Slave IO线程会重新连接主库。

#该参数的默认值是3600s ，然而时间太久会造成数据库延迟或者主备库直接的链接异常不能及时发现。

#将 slave_net_timeout 设得很短会造成 Master 没有数据更新时频繁重连。一般线上设置为5s

slave_net_timeout = 30

#这个参数用来配置从服务器的更新是否写入二进制日志，这个选项默认是不打开的，

#但是，如果这个从服务器B是服务器A的从服务器，同时还作为服务器C的主服务器，那么就需要开发这个选项，

#这样它的从服务器C才能获得它的二进制日志进行同步操作

log-slave-updates=1#用于slave服务器，io线程会把server id与自己相同的event写入日志，与log-slave-updates选项冲突replicate-same-server-id=0server_id=10112879101

# 打开二进制日志功能.

# 在复制(replication)配置中,作为MASTER主服务器必须打开此项

# 如果你需要从你最后的备份中做基于时间点的恢复,你也同样需要二进制日志log-bin =/home/data/mysql/binlog/mysql-bin.log#relay-log日志relay-log=mysql-relay-bin

#master-info-repository以及relay-log-info-repository打开以启用崩溃安全的二进制日志/从服务器功能（在事务表而不是平面文件中存储信息）

master-info-repository=TABLErelay-log-info-repository=TABLE

#不写入binlog二进制日志中的数据库

binlog-ignore-db=mysql # No sync databasesbinlog-ignore-db=test # No sync databasesbinlog-ignore-db=information_schema # No sync databasesbinlog-ignore-db=performance_schema # No sync databases

#写入binlog二进制日志中数据库

binlog-do-db=business_dbbinlog-do-db=user_dbbinlog-do-db=plocc_system

#清理binlog

expire-logs-days=15max_binlog_size = 1073741824 # Bin logs size ( 1G )

#使binlog在每1000次binlog写入后与硬盘同步

sync_binlog = 1000

#指定只复制哪个库的数据

replicate-do-db=business_dbreplicate-do-db=user_dbreplicate-do-db=plocc_system

#开启事件调度器Event Scheduler

event_scheduler=1

#MySQL能暂存的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用。

#如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，

#该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源

#如果系统在短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的TCP/IP连接的监听队列的大小。默认值50。

back_log = 500

#MySQL允许最大的进程连接数，

#如果经常出现Too Many Connections的错误提示，则需要增大此值。

max_connections = 6000

#每个用户的最大的进程连接数

max_user_connection = 3000

#每个客户端连接请求异常中断的最大次数,如果达到了此限制.

#这个客户端将会被MySQL服务阻止,直到执行了”FLUSH HOSTS” 或者服务重启

#非法的密码以及其他在链接时的错误会增加此值.

#查看 “Aborted_connects” 状态来获取全局计数器

max_connect_errors = 6000

#表调整缓冲区大小。

#table_cache 参数设置表高速缓存的数目。每个连接进来，都会至少打开一个表缓存。

#因此，table_cache 的大小应与 max_connections 的设置有关。例如，对于 200 个并行运行的连接，应该让表的缓存至少有 200 × N ，这里 N 是应用可以执行的查询的一个联接中表的最大数量。此外，还需要为临时表和文件保留一些额外的文件描述符。

#当Mysql访问一个表时，如果该表在缓存中已经被打开，则可以直接访问缓存；如果还没有被缓存但是在 Mysql 表缓冲区中还有空间，那么这个表就被打开并放入表缓冲区；如果表缓存满了，则会按照一定的规则将当前未用的表释放，或者临时扩大表缓存来存放，使用表缓存的好处是可以更快速地访问表中的内容。

#执行 flush tables 会清空缓存的内容。

#一般来说，可以通过查看数据库运行峰值时间的状态值 Open_tables 和 Opened_tables ，判断是否需要增加 table_cache 的值（其中 open_tables 是当前打开的表的数量， Opened_tables 则是已经打开的表的数量）。

#即如果open_tables接近table_cache的时候，并且Opened_tables这个值在逐步增加，那就要考虑增加这个#值的大小了。还有就是Table_locks_waited比较高的时候，也需要增加table_cache。

table_cache = 614

#表描述符缓存大小，可减少文件打开/关闭次数

table_open_cache = 2048

#设置在网络传输中一次消息传输量的最大值。系统默认值 为1MB，最大值是1GB，必须设置1024的倍数。

#当与大的BLOB字段一起工作时相当必要

max_allowed_packet = 64M

# 在一个事务中binlog为了记录SQL状态所持有的cache大小

# 如果你经常使用大的,多声明的事务,你可以增加此值来获取更大的性能.

# 所有从事务来的状态都将被缓冲在binlog缓冲中然后在提交后一次性写入到binlog中

# 如果事务比此值大, 会使用磁盘上的临时文件来替代.

# 此缓冲在每个连接的事务第一次更新状态时被创建

binlog_cache_size = 1M

# 独立的内存表所允许的最大容量.

# 此选项为了防止意外创建一个超大的内存表导致用尽所有的内存资源.

max_heap_table_size = 256M

#Sort_Buffer_Size被用来处理类似ORDER BY以及GROUP BY队列所引起的排序，每一个要做排序的请求，都会分到一个sort_buffer_size大的缓存

#Sort_Buffer_Size 是一个connection级参数，在每个connection（session）第一次需要使用这个buffer的时候，一次性分配设置的内存。

#Sort_Buffer_Size 并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。例如：500个连接将会消耗 500*sort_buffer_size(8M)=4G内存

#如果超过Sort_Buffer_Size设置的大小，MySQL会将数据写入磁盘来完成排序，导致效率降低。

#属重点优化参数

sort_buffer_size = 8M

#用于表间关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。

#大部分表关联都比较影响查询性能,

#所以将此值设大能够减轻性能影响。

#通过 “Select_full_join” 状态变量查看表关联的数量

join_buffer_size = 8M

#thread_cache_size表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,

#如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，减少线程创建的开销

#可以通过比较 Connections 和 Threads_created 状态变量，来查看thread_cache_size的设置是否起作用。

#设置规则：1GB 内存配置为8，2GB配置为16，3GB配置为32，4GB或更高内存，可配置更大。

thread_cache_size = 128

#此值表示允许应用程序在同一时间运行的线程的数量.

#设置thread_concurrency的值的正确与否,对mysql的性能影响很大, 在多个cpu(或多核)的情况下，错误设置了thread_concurrency的值, 会导致mysql不能充分利用多cpu(或多核), 出现同一时刻只能一个cpu(或核)在工作的情况。

#thread_concurrency应设为CPU核数的2倍

#属重点优化参数

thread_concurrency = 8

#此值用来缓冲 SELECT 的结果并且在下一次同样查询的时候不再执行直接返回结果,如果你有大量的相同的查询并且很少修改表，那么query_cache_size可以极大的提高数据库性能,

#需要注意的是：有时候数据库出现了性能问题，大家就习惯的认为把这个值调大就行了。然而，这个参数加大后也引发了一系列问题。

#我们首先分析一下 query_cache_size的工作原理：一个SELECT查询在DB中工作后，DB会把该语句缓存下来，当同样的一个SQL再次来到DB里调用时，DB在该表没发生变化的情况下把结果从缓存中返回给Client。

#这里有一个关建点，就是DB在利用Query_cache工作时，要求该语句涉及的表在这段时间内没有发生变更。那如果该表在发生变更时，Query_cache里的数据又怎么处理呢？

#首先要把Query_cache和该表相关的语句全部置为失效，然后在写入更新。那么如果Query_cache非常大，该表的查询结构又比较多，查询语句失效也慢，一个更新或是Insert就会很慢，这样看到的就是Update或是Insert怎么这么慢了。

#所以在数据库写入量或是更新量也比较大的系统，该参数不适合分配过大。而且在高并发，写入量大的系统，建议把该功能禁掉。

#重点优化参数

query_cache_size = 64M

#指定单个查询能够使用的缓冲区大小，只有小于此设定值的结果才会被缓冲

#此设置用来保护查询缓冲,防止极大的结果集将其他所有的查询结果都覆盖

#缺省为1M

query_cache_limit = 2M

#被全文检索索引的最小的字长.

#你也许希望减少它,如果你需要搜索更短字的时候.

#注意在你修改此值之后,

#你需要重建你的 FULLTEXT 索引

ft_min_word_len = 4

#设置MYSQL线程使用的堆大小，此容量的内存在每次连接时被预留.

#MySQL 本身常不会需要超过64K的内存

#如果你使用你自己的需要大量堆的UDF函数

#或者你的操作系统对于某些操作需要更多的堆,

#你也许需要将其设置的更高一点.

thread_stack = 192K

#设定默认的事务隔离级别.可用的级别如下:

#READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLEtransaction_isolation = READ-COMMITTED

#此值表示内存中临时表的最大大小，超过限值后就往硬盘写

#此限制是针对单个表的,而不是总和

#注意：

# 1. max_heap_table_size 比 tmp_table_size 小时，则系统会把 max_heap_table_size 的值作为最大的内存临时表的上限。这样可达到提高联接查询速度的效果，建议尽量优化查询，要确保查询过程中生成的临时表在内存中，避免临时表过大导致生成基于硬盘的MyISAM表。# 2. 通过show global status like '%created_tmp%' 查询：Created_tmp_disk_tables和Created_tmp_tables的值，Created_tmp_disk_tables / Created_tmp_tables 值越小越好tmp_table_size = 256M

#binlog日志类型#mixed：混合型binlog_format=mixed

#开启慢查询日志slow_query_log

#文件格式log_output = FILE

# 所有的使用了比这个时间(以秒为单位)更多的查询会被认为是慢速查询.

# 不要在这里使用”0″, 否则会导致所有的查询,甚至非常快的查询页被记录下来(由于MySQL 目前时间的精确度只能达到秒的级别).

long_query_time = 0.5

#慢查询日志位置

slow_query_log_file=/usr/local/mysql/mysqld_slow.log