mysql配置调优详解

 一般的优化参数都要写在[mysqld] 下面否则是不会生效的

[mysqld]
#开启MySQL计划任务
event_scheduler=ON

#默认数据库引擎
default-storage-engine=INNODB

#默认字符集，如果是网上在线客服应用，建议采用utf8mb4
character-set-server=utf8

#mysql 服务器监听端口
port = 3306

#数据库启动路径
datadir=/var/lib/mysql

# Disabling symbolic-links is recommended to prevent assorted security risks
# 是否支持符号链接，即数据库或表可以存储在my.cnf中指定datadir之外的分区或目录，为0不开启
symbolic-links=0

#mysql数据库通讯模块
socket=/var/lib/mysql/mysql.sock

#mysql 运行的用户名
user=mysql

#选项就能禁用DNS解析，连接速度会快很多。不过，这样的话就不能在MySQL的授权表中使用主机名了而只能用ip格式
skip-name-resolve

#MyISAM存储引擎也同样会使用这个参数，MySQL4.0之后，这个值默认是开启的。
#作用是避免MySQL的外部锁定(老版本的MySQL此参数叫做skip-locking)，减少出错几率增强稳定性。建议默认值。
skip-external-locking

#最大连接数
max_connections=3000

#最大错误连接数。它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况。
max_connect_errors=6000


#是否开启bin日志，
log_bin = mysql-bin

# bin日志复制级别：Row、Statement、Mixed
binlog_format = mixed

#超过30天的binlog删除  
expire_logs_days = 7

#是否开启慢sql统计
slow_query_log = 1

#执行时间超过多少秒的sql语句，归类到慢sql，支持小数点
long_query_time = 2

#慢日志所在的目录
slow_query_log_file=/var/lib/mysql/mysql-slow.log


#打开/关闭独享表空间。强制设置为1
innodb_file_per_table=1

#innodb_buffer_pool_instances
#的值主要用于将innodb buffer pool进行划分，通过划分innodb buffer pool为多个实例，
#可以提高并发能力，并且减少了不同线程读写造成的缓冲页。每一页从其中一个buffer pool中使用hash函数随机的读取和写入。
#每个buffer pool管理和维护各自的信息，包括free lists、flush lists、LRUs等。 
##大致意思是 当 innodb_buffer_pool_size 设置的 大于 1GB  以后  那么此参数设置就尤为重要了,    
#MySQL 5.6.6开始 此参数默认为 8,   主要目的是为了解决 互斥锁,  每个缓冲池管理其自己的空闲列表,
#提高查询并发性, 对于互斥锁 可以自行补脑吧, 如果innodb_buffer_pool_size大于1.3GB，
#则innodb_buffer_pool_instances的默认值为innodb_buffer_pool_size / 128MB   即大致为 10 左右.每个实例 具有独立的缓存区块
innodb_buffer_pool_instances = 8


#innodb的处理缓存
innodb_buffer_pool_size=2048M


#这个参数在MySQL 5.6.1中已经被标记为过时，在5.7.2版本的MySQL中被移除。
#innodb_thread_concurrency的默认值为0，它表示默认情况下不限制线程并发执行的数量，值 innodb_thread_sleep_delay被忽略。
#如果一个工作负载中，并发用户线程的数量小于64，建议设置innodb_thread_concurrency=0；
#如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置innodb_thread_concurrency=128，
#并通过不断的降低这个参数，96, 80, 64等等，直到发现能够提供最佳性能的线程数，
#例如，假设系统通常有40到50个用户，但定期的数量增加至60，70，甚至200。
#你会发现，性能在80个并发用户设置时表现稳定，如果高于这个数，性能反而下降。在这种情况下，建议设置innodb_thread_concurrency参数为80，以避免影响性能。
innodb_thread_concurrency = 0



#purge thread 
#负责回收已经使用并分配的undo页，purge操作默认是由master thread中完成的，为了减轻master thread的工作，
#提高cpu使用率以及提升存储引擎的性能。用户可以在参数文件中添加如下命令来启动独立的purge thread。
innodb_purge_threads=1

#IO thread 
#主要负责IO请求的回掉处理。分别为write、read、insert buffer和log IO thread。线程数量可以通过参数进行调整。5.6以后的版本可以通过
#innodb_write_io_threads和innodb_read_io_threads来限制读写线程，
#而在5.6版本以前，只有一个参数innodb_file_io_threads来控制读写总线程数。
innodb_read_io_threads = 8
innodb_write_io_threads = 8
 
#0：如果innodb_flush_log_at_trx_commit的值为0,log buffer每秒就会被刷写日志文件到磁盘，提交事务的时候不做任何操作（执行是由mysql的master thread线程来执行的。  
# 主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件(REDO LOG)中。不论事务是否已经提交）默认的日志文件是ib_logfile0,ib_logfile1  
# 1：当设为默认值1的时候，每次提交事务的时候，都会将log buffer刷写到日志。  
# 2：如果设为2,每次提交事务都会写日志，但并不会执行刷的操作。每秒定时会刷到日志文件。要注意的是，并不能保证100%每秒一定都会刷到磁盘，这要取决于进程的调度。  
# 每次事务提交的时候将数据写入事务日志，而这里的写入仅是调用了文件系统的写入操作，而文件系统是有 缓存的，所以这个写入并不能保证数据已经写入到物理磁盘  
# 默认值1是为了保证完整的ACID。当然，你可以将这个配置项设为1以外的值来换取更高的性能，但是在系统崩溃的时候，你将会丢失1秒的数据。  
# 设为0的话，mysqld进程崩溃的时候，就会丢失最后1秒的事务。设为2,只有在操作系统崩溃或者断电的时候才会丢失最后1秒的数据。InnoDB在做恢复的时候会忽略这个值。  
# 总结设为1当然是最安全的，但性能页是最差的（相对其他两个参数而言，但不是不能接受）。如果对数据一致性和完整性要求不高，完全可以设为2，如果只最求性能，例如高并发写的日志服务器，设为0来获得更高性能  
innodb_flush_log_at_trx_commit = 0 




#innodb_autoextend_increment（动态，默认为8M）
#当自动扩展表空间被填满之时，为扩展而增加的尺寸（MB为单位）。
innodb_autoextend_increment = 128M


# 此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，但意外的故障将会丢失数据。MySQL开发人员建议设置为1－8M之间
innodb_log_buffer_size = 64M


# 此参数确定数据日志文件的大小，更大的设置可以提高性能，但也会增加恢复故障数据库所需的时间  
innodb_log_file_size = 128M


#通常来说，在MyISAM里读写操作是串行的，但当对同一个表进行查询和插入操作时，为了降低锁竞争的频率，根据concurrent_insert的设置，MyISAM是可以并行处理查询和插入的：
#当concurrent_insert=0时，不允许并发插入功能。
#当concurrent_insert=1时，允许对没有洞洞的表使用并发插入，新数据位于数据文件结尾（缺省）。
#当concurrent_insert=2时，不管表有没有洞洞，都允许在数据文件结尾并发插入。
#这样看来，把concurrent_insert设置为2是很划算的，至于由此产生的文件碎片，可以定期使用OPTIMIZE TABLE语法优化。
concurrent_insert = 2 



#缺省情况下，写操作的优先级要高于读操作的优先级，即便是先发送的读请求，后发送的写请求，此时也会优先处理写请求，
#然后再处理读请求。这就造成一 个问题：一旦我发出若干个写请求，就会堵塞所有的读请求，直到写请求全都处理完，
#max_write_lock_count=1，当系统处理一个写操作后，就会暂停写操作，给读操作执行的机会
max_write_lock_count = 0


#lower_case_table_names（大小写敏感）。0表示敏感，1表示不敏感
lower_case_table_names=1


#打开的最大文件数
open_files_limit =65535


# MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，  
# 然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。  
# 如果期望在一个短时间内有很多连接，你需要增加它。也就是说，如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中，  
# 以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。  
back_log = 500


# 服务器关闭非交互连接之前等待活动的秒数。在线程启动时，根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值
wait_timeout = 1200

# 服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。默认值：28800秒（8小时）
interactive_timeout = 1200


# 批量插入缓存大小， 这个参数是针对MyISAM存储引擎来说的。适用于在一次性插入100-1000+条记录时， 提高效率。默认值是8M。可以针对数据量的大小，翻倍增加。 
bulk_insert_buffer_size = 512M


#延迟插入的最大限制
delayed_insert_limit = 500

#一个事务在没有提交的时候，产生的日志，记录到Cache中；等到事务提交需要提交的时候，则把日志持久化到磁盘。
#默认binlog_cache_size大小32K
binlog_cache_size = 256M


#这是设置最大二进制日志的缓存区大小的变量。若处理多语句事务时需要的内存大小比设置值大的话就会提示一个error。
max_binlog_cache_size = 512M


# MySQL每打开一个表，都会读入一些数据到table_open_cache缓存中，当MySQL在这个缓存中找不到相应信息时，才会去磁盘上读取。默认值64  
# 假定系统有200个并发连接，则需将此参数设置为200*N(N为每个连接所需的文件描述符数目)；  
# 当把table_open_cache设置为很大时，如果系统处理不了那么多文件描述符，那么就会出现客户端失效，连接不上  
table_open_cache = 128


# 定义了用户可以创建的内存表(memory table)的大小。这个值用来计算内存表的最大行数值。这个变量支持动态改变  
max_heap_table_size = 2048M


# MySQL的heap（堆积）表缓冲大小。所有联合在一个DML指令内完成，并且大多数联合甚至可以不用临时表即可以完成。  
# 大多数临时表是基于内存的(HEAP)表。具有大的记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。  
# 如果某个内部heap（堆积）表大小超过tmp_table_size，MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。
# 还可以通过设置tmp_table_size选项来增加临时表的大小。也就是说，如果调高该值，MySQL同时将增加heap表的大小，
# 可达到提高联接查询速度的效果
tmp_table_size = 1024M 


# MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。  
# 如果不能，可以尝试增加sort_buffer_size变量的大小 
#每个连接独享缓存，200连接*512M= 
sort_buffer_size = 512M


# 联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享
join_buffer_size = 512M

# 指定用于索引的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，  
# 系统将开始换页并且真的变慢了。对于内存在4GB左右的服务器该参数可设置为384M或512M。通过检查状态值Key_read_requests和Key_reads，  
# 可以知道key_buffer_size设置是否合理。比例key_reads/key_read_requests应该尽可能的低，  
# 至少是1:100，1:1000更好(上述状态值可以使用SHOW STATUS LIKE 'key_read%'获得)。注意：该参数值设置的过大反而会是服务器整体效率降低  
key_buffer_size = 512M


# MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。  
# 如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能 
read_buffer_size = 256M


# MySQL的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，  
# MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。
# 但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大  
read_rnd_buffer_size = 256M


# 这个值（默认8）表示可以重新利用保存在缓存中线程的数量，当断开连接时如果缓存中还有空间，那么客户端的线程将被放到缓存中，  
# 如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，  
# 增加这个值可以改善系统性能.通过比较Connections和Threads_created状态的变量，可以看到这个变量的作用。(–>表示要调整的值)  
# 根据物理内存设置规则如下：  
# 1G  —> 8  
# 2G  —> 16  
# 3G  —> 32  
# 大于3G  —> 64  
thread_cache_size = 64


#MySQL的查询缓冲大小使用查询缓冲，MySQL将SELECT语句和查询结果存放在缓冲区中，  
# 今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。根据MySQL用户手册，使用查询缓冲最多可以达到238%的效率。  
# 通过检查状态值'Qcache_%'，可以知道query_cache_size设置是否合理：如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，  
# 如果Qcache_hits的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；如果Qcache_hits的值不大，则表明你的查询重复率很低，  
# 这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。此外，在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲 
query_cache_size = 512M

#指定单个查询能够使用的缓冲区大小，默认1M 
query_cache_limit = 64M

#sql_mode常用值如下: 
#   ONLY_FULL_GROUP_BY:
#	对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中
#    NO_AUTO_VALUE_ON_ZERO:
#    该值影响自增长列的插入。默认设置下,插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0,而该列又是自增长的,那么这个选项就有用了。
#    STRICT_TRANS_TABLES:
#    在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制
#    NO_ZERO_IN_DATE:
#    在严格模式下,不允许日期和月份为零
#    NO_ZERO_DATE:
#   设置该值,mysql数据库不允许插入零日期,插入零日期会抛出错误而不是警告。
#    ERROR_FOR_DIVISION_BY_ZERO:
#    在INSERT或UPDATE过程中,如果数据被零除,则产生错误而非警告。如 果未给出该模式,那么数据被零除时MySQL返回NULL
#    NO_AUTO_CREATE_USER:
#    禁止GRANT创建密码为空的用户
#    NO_ENGINE_SUBSTITUTION:
#    如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常
#    PIPES_AS_CONCAT:
#    将"||"视为字符串的连接操作符而非或运算符,这和Oracle数据库是一样的,也和字符串的拼接函数Concat相类似
#    ANSI_QUOTES:
#    启用ANSI_QUOTES后,不能用双引号来引用字符串,因为它被解释为识别符
#    如果使用mysql,为了继续保留大家使用oracle的习惯,可以对mysql的sql_mode设置如下:
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES


# MyISAM设置恢复表之时使用的缓冲区的尺寸，当在REPAIR TABLE或用CREATE INDEX创建索引或ALTER TABLE过程中排序 MyISAM索引分配的缓冲区 
myisam_sort_buffer_size = 512M

# 如果临时文件会变得超过索引，不要使用快速排序索引方法来创建一个索引。注释：这个参数以字节的形式给出  
myisam_max_sort_file_size = 2G

# 如果该值大于1，在Repair by sorting过程中并行创建MyISAM表索引(每个索引在自己的线程内) 
myisam_repair_threads = 1


#binlog日志开启的数据库 ，可用于主从复制指定某个库复制
binlog_do_db=crm1
binlog_do_db=crm2

#binlog日志关闭的数据库 ，可用于主从复制忽略某个库复制
binlog_ignore_db=crm3

#开启慢日志查询值为1
slow_query_log=1

#查询超过2秒写入慢日志
long_query_time=2
#慢日志写入位置
slow_query_log_file=/var/lib/mysql/mysql-slow.log




[mysqldump]
#服务器发送和接受的最大包长度  
max_allowed_packet=96M

[myisamchk]
key_buffer_size = 1024M
sort_buffer_size = 1024M
read_buffer = 128M
write_buffer = 128M

[client]
port = 3306 
socket=/var/lib/mysql/mysql.sock

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid