mysql5.6之my.cnf配置详解

my.cnf

[mysqld]
# GENERAL #
user                            = mysql
port                            = 3306
default_storage_engine          = InnoDB
socket                          = /tmp/mysql.sock
tmpdir                          = /dev/shm  放到共享内存里面，效率更高，  共享内存默认是物理内存的一半
character-set-server            = gbk     
collation-server                = gbk_chinese_ci  （校验级，不建议设置，让系统自己选择）
init-connection="set names gbk";
如果指定非utf8  和laint 1的话 建议添加 init-connection="set names gbk"; 这个
新创建的连接客户端要设计他的字符集
http://imysql.com/tag/字符集   参考
http://imysql.cn/2013/10/29/misunderstand-about-charset-handshake.shtml 参考
| utf8_general_ci          | utf8     |  33 | Yes     | Yes      |       1 | utf8默认的字符集编码

net_buffer_length               = 1024K         
skip-name-resolve
skip-external-locking
ft_min_word_len = 4
event_scheduler = 0

tmpdir                          = /dev/shm   指向共享内存 如果内存比较小需要注意

skip-character-set-client-handshake  忽略客户端连接过来的字符集，使用服务器端指定的字符集

## File
back_log                        = 500   比如我现在有1000个连接过来 数据库现在只能连接100个进程，另外900缓存起来存到back_log中
open_files_limit                = 65535 不超过内核的上限值就可以 ，但是不要设置太小   

# MyISAM #
key_buffer_size                 = 64M      只针对Myisam引擎以Innodb表为主 这个 就足够用了 如果以myisam表为主这个需要设置大些

#myisam_recover_options          = FORCE,BACKUP
myisam_sort_buffer_size         = 32M
myisam_repair_threads = 1

# SAFETY #
max_allowed_packet              = 128M    这个参数一般给默认值就可以了，设置导入导出环境这块设置不一样可能会报错。
skip_name_resolve




#buffers & cache
table_open_cache      = 2048    
table_definition_cache = 2048
max_heap_table_size = 96M
tmp_table_size = 96M
如果排序多的话下面2个参数加大一些


sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 512   有点小可以加大一些 ，要根据我们的情况来顶，如果前端使用连接池512已经够用了，如果使用短链接需要加大 可以设置生1M
query_cache_size = 0     =0 是关闭掉
query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 192K
read_buffer_size = 2M
read_rnd_buffer_size = 2M
bulk_insert_buffer_size = 32M


#connecter
max_connections                = 1100    数据库最大连接数
max_user_connections           = 1000   用户最大连接数
max_connect_errors              = 1000000   输入错误多少次 锁定用户

#timeout
wait_timeout                  = 100   连接池一般是长连接，长连接 这个值就不要设置太小，太小的话超过一段时间就会自动踢掉了，可以根据
实际情况设置时间
interactive_timeout           = 100

connect_timeout              = 20   连接超时，设置太大会有问题，如果网络有问题就    
slave-net-timeout            = 20  这个可以设置小一点，太长会导致连接进程等待

transaction_isolation = READ-COMMITTED   事物隔离级别，交易类的用RR，如果代码曾有能力解决幻读用rc
#performance_schema=0
 
# BINARY LOGGING #
log_bin                         = /database/mysql/binlog/mysql-bin   日志存放位置
expire_logs_days                = 6    日志存放天数
sync_binlog                     = 0          sync_binlog减小io开销使用  主从必须设置为1 好保证主从一致性，
设置为1 影响性能 ，设置为0 速度快数据不安全 可设置为0，1， 2， 3，5，，10
binlog_format                   = row
max_binlog_size                 = 1024M

# LOGGING #
log_error                       = /database/mysql/logs/mysql-error.log    错误日志的位置
#log_queries_not_using_indexes  = 1
slow_query_log                 = 1
slow_query_log_file            = /database/mysql/logs/mysql-slow.log    慢日志的位置
long_query_time                = 1  慢查询的时候 可以小一点  ，每个sql 最好不要超过20毫秒
log-output                     = file     控制慢日志输出的   file|table  可以文件，也可表
log_warnings                   = 2     log_warnings= 2，可以避免Aborted connection


#thread pool
thread_handling=pool-of-threads
thread_pool_oversubscribe=3  意思是一个cpu核心同时可以处理多少个并发线程的链接
 thread_pool_oversubscribe = cpu核心数  这个值不建议超过3

# INNODB #
#innodb_adaptive_flushing_method = estimate
innodb_adaptive_flushing        = 1  顺序io 的参数   自适应刷新，开启比较合理
innodb_data_home_dir            = /database/mysql/ibdata
innodb_log_group_home_dir       = /database/mysql/ibdata_log
innodb_data_file_path           = ibdata1:2048M:autoextend    共享表空间的大小
#innodb_adaptive_hash_index_partitions= 4
innodb_additional_mem_pool_size = 16M
innodb_flush_method             = O_DIRECT   


innodb_log_files_in_group       = 2
innodb_log_file_size            = 512M
innodb_log_buffer_size          = 16M
innodb_flush_log_at_trx_commit  = 1  innodb_flush_log_at_trx_commit = 0 非交易类，=1 交易类，保持一致性要设置成1 否则设置为0
innodb_file_per_table           = 1
innodb_buffer_pool_size         = 32G
innodb_buffer_pool_instances    = 8    比如场景为32 G 划分为4个相当于每个都有8个G子分区来进行管理，但是前提条件是内存一定要大于8G，也就是说切分完之后每个
都要大于1个G，大内存   高并发场景 设置要好点

innodb_blocking_buffer_pool_restore = ON
innodb_buffer_pool_restore_at_startup = 300
innodb_merge_sort_block_size    = 2M
innodb_kill_idle_transaction    = 300  查询或者dml语句超过一定时间内都会被kill掉 建议不设置
innodb_max_dirty_pages_pct      = 65
innodb_io_capacity              = 300  根据硬盘的iops来设置   ssd可以设置为2000普通的设置300-500


innodb_blocking_buffer_pool_restore = ON
innodb_buffer_pool_restore_at_startup = 300
innodb_merge_sort_block_size    = 2M
https://bugs.launchpad.net/percona-server/+bug/1072538


innodb_thread_concurrency       = 0  nnodb_thread_concurrency       = 0
一般是设置0或者CPU*2  现在已经基本上不用太关心了5.6开始当做透明的就好了
innodb_read_io_threads          = 12
innodb_write_io_threads         = 16
innodb_file_format              = Barracuda
innodb_flush_neighbor_pages     = 0    文件格式
innodb_change_buffering         = all


innodb_stats_on_metadata        = 0
innodb_lock_wait_timeout        = 30    等待锁的时间根据业务来判断
innodb_rollback_on_timeout
innodb_sync_spin_loops           = 100
innodb_spin_wait_delay           = 30
innodb_support_xa               = 0

innodb_concurrency_tickets      = 500   默认值500
log_bin_trust_function_creators = 1  
 log_bin_trust_function_creators=1：触发器和存储过程时启用。
注释：
开启binlog时，是否允许创建存储程序
（除非有SUPER权限，或者指定DETERMINISTIC、READS SQL DATA、NO SQL）
可动态修改，属于一个功能性的东西。
innodb_purge_threads            =1   purge线程  只能设置为1 5.7才可以有多个
innodb_purge_batch_size         = 32
innodb_old_blocks_pct           = 75

#Replication
server-id = 113306
#log-slave-updates
#skip_slave_start   看情况是否开启，如果作为中级节点必须开启否则不用开启，log-slave-updates 建议这个要开启
#read_only         = 1
slave-skip-errors=1032   跳过指定错误
skip_slave_start   = 1     开机自动启动同步，早起版本建议不要开启

innodb_max_dirty_pages_pct      = 35 这个参数看环境 ，建议设置为50以下，毕竟数据库以读为主，

如果设置过多，刷新的时候会造成瓶颈



#relay log
#relay-log = /database/mysql/binlog/slave-relay-bin
#relay-log-index = /database/mysql/binlog/slave-relay-bin.index
max-relay-log-size = 256M    
relay_log_purge = 1
#slave_parallel_workers = 8  并行的复制，多个数据库就要开启并行



my.cnf 里头，各个section什么意思
mysqld_safe
针对mysqld_safe这个进程或者程序一个配置的区间，这个区间只会被mysqld_safe读取
[mysql]
只针对 mysql 这个cli客户端工具

[client]
 针对全部的client，例如mysql\mysqldump\mysqladmin都是
[mysqldump]
针对mysqldump专区
[mysqld]
mysqld这个服务进程的专区

relay_log_recovery = 1

如果是slave  的话一定要加上

[mysql]
prompt="\\u@\\h \\D \\R:\\m:\\s [\\d]&get; "
pager="less -i -n -S"
no-auto-rehash
tee="/home/mysql/query.log"

记录数据库的操作，加上no-auto-rehash  不扫描MySQL日志

innodb_adaptive_flushing_lwm -- 设置redo log flush低水位线，当需要flush的redo log超过这个低水位线时，立即强制启用adaptive flushing，即便没有设置使用adaptive flushing机制

innodb_buffer_pool_size
innodb_additional_mem_pool_size    缓存数据字典的默认是8M  最好给16M或者32M
innodb_log_buffer_size


read_buffer_size             -- 顺序读缓冲，提高顺序读效率

+read_rnd_buffer_size   -- 随机读缓冲，
+sort_buffer_size           -- 排序缓冲，提高排序效率

+join_buffer_size           -- 表连接缓冲，提高表连接效率

+binlog_cache_size       -- 二进制日志缓冲，提高二进制日志写入效率

+tmp_table_size            -- 内存临时表，提高临时表存储效率

+tmp_table_size            -- 内存临时表，提高临时表存取效率（一般用于group by查询）

+thread_cache_size       -- 线程缓存，
+net_buffer_length       -- 线程持连接缓冲以及读取结果缓冲

+net_buffer_length       -- 线程池连接缓冲以及读取结果缓冲


需要关注的是最大连接数 不要设置太大

tmp_table_size  不要设置太大

innodb_buffer_pool_size                      -- InnoDB高速缓冲，行数据、索引缓冲，以及事务锁、自适应哈希等

+innodb_additional_mem_pool_size    -- InnoDB数据字典额外内存，缓存所有表数据字典

+innodb_log_buffer_size                      -- InnoDB REDO日志缓冲，提高REDO日志写入效率

+key_buffer_size                                   -- MyISAM表索引高速缓冲，提高MyISAM表索引读写效率

+query_cache_size                                -- 查询高速缓存，缓存查询结果，提高反复查询返回效率


+key_buffer_size                                   -- MyISAM表索引高速缓冲，提高MyISAM表索引读写效率（建议少用MyISAM）
+query_cache_size                                -- 查询高速缓存，缓存查询结果，提高反复查询返回效率（建议关闭QC）


+table_cahce                                         -- 表空间文件描述符缓存，提高数据表打开效率

+table_definition_cache                        -- 表定义文件描述符缓存，提高数据表打开效率


MyISAM和InnoDB buffer区别
InnoDB既缓存索引，又缓存数据
MyISAM只缓存索引，不缓存数据

max_heap_table_size 负责设置MEMORY/HEAP表最大容量，不管其他执行SQL产生的临时表，如果内存不够用，则不允许写入新的数据，MEMORY/HEAP表也不会转成磁盘表，只会告警超限后拒绝写入

tmp_table_size 不负责限制 MEMORY/HEAP表最大容量，如果执行SQL产生临时表超过 tmp_table_size/max_heap_table_size，则会产生基于磁盘的MyISAM表
| default_tmp_storage_engine                             | InnoDB  
tmp_table_size 不负责限制 MEMORY/HEAP表最大容量，如果执行SQL产生临时表超过 tmp_table_size/max_heap_table_size，则会产生基于磁盘的MyISAM表（如果default_tmp_storage_engine选项没有设定为InnoDB的话）

关闭查询缓存：
query_cache_type = 0
query_cache_size = 0



官方MySQL版本：
Total memory allocated 1098907648; in additional pool allocated 0
Dictionary memory allocated 37754
Buffer pool size   65535
Free buffers       65283
Database pages     252
Old database pages 0
Modified db pages  0


Buffer pool size 大小16kb
Buffer pool size   65535 * 16KB = 1G

innodb_old_blocks_pct
旧的数据块的百分比
innodb_old_blocks_time
当buff_pool不够用的时候会根据时间讲旧的清除掉

新数据都是热数据过一段时间就放到存旧数据的里面，过段时间不用就清楚掉。



=======================================================
Percona分支版本：
Total memory allocated 858521600 (818M); in additional pool allocated 0
Total memory allocated by read views 88
Internal hash tables (constant factor + variable factor) -- 内部用途哈希表缓冲
    Adaptive hash index 13302240        (13281656 + 20584) -- 自适应哈希索引，约12.6M
    Page hash           830936 (buffer pool 0 only) -- data page哈希，约0.79MB，只缓存了buffer_pool_0这个instance，如果 innodb_buffer_pool_instances 设置大于0的话
    Dictionary cache    3382197         (3321904 + 60293) -- 数据字典缓冲，约3.2MB
    File system         820024  (812272 + 7752) -- 文件子系统缓冲，约0.78MB
    Lock system         2311256         (2311256 + 0) -- 锁子系统缓冲，约2.2MB
    Recovery system     0       (0 + 0)  -- 恢复子系统缓冲，目前是0
Dictionary memory allocated 60293
Buffer pool size        51199
Buffer pool size, bytes 838844416
Free buffers            50795 -- 空闲buffer，约793MB
Database pages          403   -- 缓冲热点数据的buffer，约6KB
Old database pages      0     -- 缓冲旧数据的buffer
Modified db pages       0     -- 缓冲最近修改数据的buffer
叶金荣(4700963)21:18:01

默认128M（老版本默认8M，坑很多），以InnoDB表为主的话，设为物理内存50~70%
[MySQL FAQ]系列 -- 数据不算大，备份却非常慢，http://imysql.com/2009/09/18/mysql-faq-why-backup-is-so-slow.html

adaptive index hash, 自适应哈希索引，用来管理buffer pool的哈希索引

adaptive index hash, size= innodb_buffer_pool / 64，随着buffer的频繁更新，会随之上升
   Lock system         2311256         (2311256 + 0) -- 锁子系统缓冲，约2.2MB

事件等待  当Innodb_buffer_pool_wait_free      就是事件等待 这个时候很有可能是内存不够 ，查看当系统的内存，如果有
剩余的内存就给buffer_pool多分配一些，如果剩余内存不多的情况下，建议添加内存或者迁移
| Innodb_buffer_pool_wait_free                  | 0            |
| Innodb_log_waits                              | 0            |


innodb_additional_mem_pool_size
数据字典以及内部数据结构缓存，表数量越多，相应的内存需要越大。
默认8M，通常设置为8~32M足够，一般建议设置为16M，如果确实不够用，那么会从系统中请求增加分配内存，并且错误日志中会提醒，目前至少还未发生过。


innodb_log_buffer_size
show global status 查看 Innodb_log_waits 是否大于0，是的话，就需要提高 innodb_log_buffer_size，否则维持原样。
show global stauts 查看30~60秒钟 Innodb_os_log_written 的间隔差异值，即可计算出 innodb_log_buffer_size 设置多大合适。
默认8M，一般设置为16 ~ 64M足够了。


max_heap_table_size 负责设置MEMORY/HEAP表最大容量，不管其他执行SQL产生的临时表，如果内存不够用，则不允许写入新的数据，MEMORY/HEAP表也不会转成磁盘表，只会告警超限后拒绝写入
tmp_table_size 不负责限制 MEMORY/HEAP表最大容量，如果执行SQL产生临时表超过 tmp_table_size/max_heap_table_size，则会产生基于磁盘的MyISAM表（如果default_tmp_storage_engine选项没有设定为InnoDB的话）

tmp_table_size            -- 内存临时表，提高临时表存取效率（一般用于group by查询）    ；max_heap_table_size 负责设置MEMORY/HEAP表最大容量，不管其他执行SQL产生的临时表，如果内存不够用，则不允许写入新的数据，MEMORY/HEAP表也不会转成磁盘表，只会告警超限后拒绝写入