×

mysql参考配置以及参数解释

96
猪肉楠
2017.02.22 16:36* 字数 4
[mysql]
prompt                                  
    =\\u@\\h \\D \\R:\\m:\\s [\\d]> mysql命令提示符
[client]
port                                    
    = 3307 客户端连接mysql时使用的端口
socket                                  
    = /tmp/mysql.sock 客户端连接本地mysql时使用的sock文件
default-character-set                   
    = utf8 客户端设置字符集
[mysqld_safe]
#malloc-lib                             
    =/usr/local/lib/libjemalloc.so 指定内存分配算法的so文件
[mysqld]
character-set-server                    
    = utf8 服务器新版本字符集(5.1以下用 default-character-set)
user                                    
    = mysql 运行时用户
port                                    
    = 3307 守护进程监听端口
socket                                  
    = /tmp/mysql.sock 本地sock文件
basedir                                 
    = /opt/mysql57 mysql安装目录
datadir                                 
    = /opt/mysql57/var mysql数据目录

#---------------+
#systemconfig   |
#---------------+
sql_mode                                
    = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER" sql 模式
        strict_trans_tables、strict_all_tables
            严格模式控制mysql如何处理非法(数据类型错误,不适合列,超出范围)或者丢失的输入值(空,且没有default值)
            对于事务表,当启用STRICT_ALL_TABLES或者STRICT_TRANS_TABLES模式时,如果有非法或者丢失,则会出现错误,语句被放弃并滚动。
            对于非事务表。建议事务不要使用其他非事务表
        no_auth_create_user
            在使用grant 给新用户授权后,未加identified by 创建一个密码,则会导致创建一个无密码的sql用户。使用此模式后,此情况避免。
        no_engine_subtitution
            create table 时指定的engine项不被支持,这个时候mysql会支持报错
        no_zero_date
        no_zero_in_date
        only_full_group_by
            select字句需要全部列出group by字段,否则报错
explicit_defaults_for_timestamp         
    = 1 明确指定timestamp类型的默认值,如果没有指定,则为NULL,不会产生一些奇怪的默认行
interactive_timeout
    = 1800 交互式mysql客户端超时时间
wait_timeout                            
    = 1800 非交互式客户端超时时间
open_files_limit                        
    = 65535 mysql进程可以打开的文件限制,可以用 lsof -p PID_OF_MYSQL | wc -l 来统计mysql进程打开的文件总数
back_log
    = 600 参数值指定到来的TCP/IP连接的侦听队列的大小
max_connections                         
    = 4000 允许最大的连接数量
event_scheduler                         
    = 1 允许事件调度(类似定时任务)
max_connect_errors                      
    = 6000 允许每个客户端最多尝试登录次数
skip-name-resolve
    跳过域名解析,只使用ip
skip-external-locking
    跳过外部锁定,多台服务器使用同一个数据库目录,此项必须开启
max_allowed_packet                      
    = 1024M server接受数据包限制,在数据恢复的时候要调整大一些
default-storage-engine                  
    = InnoDB 默认存储引擎
transaction_isolation                   
    = READ-COMMITTED 事务隔离级别,默认是REPEATABLE-READ
#---------------+
#   myisam      |
#---------------+

myisam_sort_buffer_size                 
    = 64M MyISAM表发生变化时重新排序所需的缓冲
myisam_max_sort_file_size               
    =1G MySQL重建索引时所允许的最大临时文件的大小
myisam_repair_threads                   
    = 1 如果一个表拥有超过一个索引, MyISAM 可以通过并行排序使用超过一个线程去修复他们
key_buffer_size                         
    = 256M 用于索引的缓冲区大小,增加它可以得到更好的索引处理性能
#---------------+
#      cache    |
#---------------+
query_cache_type                        
    = 1 开启query_cache                           
query_cache_size                        
    = 64M query_cache总共能使用的内存大小
query_cache_limit                       
    = 8M 指定单个查询能够使用缓冲区最大值,缺省为1M
query_cache_min_res_unit
    = 2k 分配缓冲区空间的最小单位
thread_cache_size
    = 512 重用sql线程的数,既创建出来先不销毁,cache下来,等待下次连接重用,如果在频繁创建的情况下适当多设置
tmp_table_size
    = 32M 控制内存临时表大小,不作用于在用户创建的memory表中。和下边的这个参数同用,最小的起作用。如果超出,则使用磁盘,如果group by 语句多,设置大一些
max_heap_table_size
    = 32M 控制内存临时表大小,作用于在用户创建的memory表中。和上边的这个参数同用,最小的起作用。如果超出,则使用磁盘
table_open_cache                        
    = 614 所有线程打开的表数量,show status like 'open%tables'; 用来查看当前打开的表数,如果大于此值,则增大。    
ft_min_word_len                         
    = 4 MyISAM 全文索引中包括的单词的最小长度,如果此值被修改,则必须执行 REPAIR table tbl_name QUICK.
bulk_insert_buffer_size                 
    = 64M 大量数据(bulk)插入缓存大小、针对MyISAM存储引擎,提高效率    
        针对语句 INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE
sort_buffer_size
    = 12M 有排序操作会分配,通过 SHOW GLOBAL STATUS查看,如果Sort_merge_passes/s 多,可以提高此值,来加速order by和group by操作。
        但如果设置太大,linux分配内存效率也不高。所以要找一个适合的
read_buffer_size                        
    = 24M myisam全表扫描所分配的读缓冲池大小
read_rnd_buffer_size                    
    = 12M sort后的数据是以key-value的形式存在的,使用这些行指针去读取数据,
        将是以指针数据物理的顺序去读取,很大程度上是随机的方式读取数据的。
        MySQL从sort_buffer中读取这些行指针数据,然后通过指针排序后存入read_rnd_buffer中,
        之后再通过指针读取数据时,基本上都是顺序读取了
join_buffer_size
    = 12M 连接缓冲池大小,每个连接会分配一个大小,所以一个查询中,可能分配多个缓冲池大小,加快join操作(加索引,加大此缓冲值)  
table_definition_cache                  
    = 4096 存放表的定义信息缓存,这里存放与存储引擎无关的,独立的表定义相关信息。
tmpdir  
    = /tmp 存放临时文件和临时表的位置
#---------------+
#     log       |
#---------------+
relay_log                               
    = /opt/mysql57/var/mysql-relay-bin 中继日志名,后边加序号,用在slave接受主服务器binlog日志
relay_log_index                         
    = relay-log.index 记录所有的中继日志文件名
slow_query_log                          
    = 1 开启慢查询日志
long_query_time                             
    = 1 慢查询日志时间
log_queries_not_using_indexes           
    = 1 当执行一个sql语句的时候,如果一个表没有索引就会把这个信息记录在慢查询文件中
slow-query-log-file
    = /opt/mysql57/var/slowquery.log 慢查询日志位置
log-bin                                 
    = /opt/mysql57/var/binlog binlog日志位置
log-error                               
    = /opt/mysql57/var/logerr 错误日志位置
binlog_cache_size
    = 32M 给单个事务单个客户端分配的二进制binlog缓存大小,如果经常执行大事务,则可以适当加大
binlog_format
    = ROW 二进制日志文件的格式,有STATEMENT、ROW、MIXED
        5.7.7 之前默认是statement,5.7.7及以后,默认是row
max_binlog_cache_size                   
    = 512M 最大binlog_cache,如果超过了这个值很多,则会自动将事务分成 Multi-statement transaction 多语句事务。
max_binlog_size                         
    = 512M 每个二进制日志的最大大小,如果超过,则会轮转日志
expire_logs_days                        
    = 5 设置binlog老化日期,重启或者flush logs 就会删除过期的二进制日志,手动删除的话,执行PURGE BINARY LOGS
sync_binlog
    = 1 控制mysql如何向磁盘刷新binlog,0为由os自动刷新,n为执行了n个事务以后刷新到磁盘
#---------------+
#   innodb      |
#---------------+
innodb_page_size                        
    = 8192 innodb 页大小(使用ssd应该设置的较小比如4k)
innodb_buffer_pool_instances            
    = 8 缓冲池被分开的实例个数,(当innodb_buffer_pool_size大于1G才会生效,而且每个实例至少分1G则效率高)
innodb_flush_method                     
    = O_DIRECT 数据文件的写入操作是直接从mysql innodb buffer到磁盘的,并不用通过操作系统的缓冲,而真正的完成也是在flush这步,避免了存储引擎和操作系统的双重缓存
innodb_log_group_home_dir               
    = /opt/mysql57/redolog/ innodb存储引擎产生的日志文件目录,默认为mysql数据目录。
innodb_undo_directory                   
    = /opt/mysql57/undolog/ innodb存储undolog产生的日志文件目录,如果不指定,则为mysql数据目录。
innodb_undo_logs                        
    = 128 用于表示回滚段的个数
innodb_undo_tablespaces
    = 3 用于设定创建的undo表空间的个数
innodb_flush_neighbors
    = 1 是否开启刷新邻接页(机械磁盘使用,固态关闭)
innodb_buffer_pool_size                 
    = 20480M innodb存储引擎产生的日志文件大小,如果innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 大于此值, innodb_buffer_pool_chunk_size 会被减少到此值/innodb_buffer_pool_instances
innodb_data_file_path                   
    = ibdata1:1024M:autoextend innodb表空间路径以及大小设置    
innodb_flush_log_at_trx_commit          
    = 2 事务提交刷写到磁盘的方式,0为每秒,1为每次事务都刷,2为每次提交写cache,并每秒刷写到磁盘,这样mysql进程奔溃了以后,数据不丢失,除非机器整个宕机,才会丢失最多1秒的数据
innodb_log_buffer_size
    = 16M 事务日志写入到磁盘持久化之前的缓存池大小
innodb_log_file_size
    = 4096M 控制单个事务日志的大小
innodb_log_files_in_group
    = 3 事务日志的个数
innodb_max_dirty_pages_pct
    = 85 控制innodb向数据文件中猛烈刷写的比例,如果脏页所占比例超过此值,那么就会执行强力的刷写
innodb_lock_wait_timeout
    = 10 等待互斥锁的会话超时时间,只作用于innodb 行级锁,不作用于innodb表级锁
        decrease this value for highly interactive applications or OLTP systems
        increase this value for long-running back-end operations        
innodb_file_per_table
    = 1 为每一张表开启一个表空间文件
innodb_write_io_threads
    = 24 写线程个数,对于多核cpu设置
innodb_read_io_threads
    = 24 读线程个数,对于多核cpu设置
innodb_lru_scan_depth
    = 2000 影响innodb buffer pool 的flush算法,默认1024.
        increasing the value if you have spare I/O capacity under a typical workload
        if a write-intensive workload saturates your I/O capacity, decrease the value, especially in the case of a large buffer pool
innodb_io_capacity
    = 4000 自己预估磁盘的io能力,sata盘设置为200,固态硬盘设置的高一些(2000)
innodb_io_capacity_max
    = 8000 数据刷写到磁盘如果滞后,innodb就会更加激进的,以超过innodb_io_capacity的速率进行刷写,此值为设置最大刷写能力
innodb_purge_threads
    = 4 数字代表使用N个独立线程来清除数据,>= 5.7.8 默认为4.    
innodb_large_prefix
    = 1 允许列索引最大达到3072,0/off的时候最大为767(只作用在 DYNAMIC or COMPRESSED row format)
innodb_thread_concurrency
    = 16 并发线程数量,innodb用自己的线程调度机制(FIFO,通过锁来控制)来控制线程如何进入innodb内核工作,减少了操作系统因为线程之间的上下文切换带来的开销
        If the number of concurrent user threads for a workload is less than 64, set innodb_thread_concurrency=0.
        If your workload is consistently heavy or occasionally spikes, start by setting innodb_thread_concurrency=128 and then lowering the value to 96, 80, 64, and so on, until you find the number of threads that provides the best performance.
innodb_print_all_deadlocks
    = 1 开启后,会将所有deadlock过程记录在error_log里
innodb_strict_mode
    = 1 开启innodb的严格模式,如果出现warnings,则返回errors,建议在开发的时候就开启。
innodb_file_format
    = Barracuda Innodb支持行数据压缩特性,前提是采用Barracuda行存储格式,5.7默认为 Barracuda
        Antelope 支持 REDUNDANT 和 COMPACT 
        Barracuda 支持 COMPRESSED 和 DYNAMIC 
innodb_file_format_max
    = Barracuda Innodb支持行数据压缩特性,前提是采用Barracuda行存储格式
#-------------+
#thread poll  |
#-------------+
thread_handling                         = pool-of-threads                       线程模式,启用线程池
thread_pool_oversubscribe               = 10                                    一个group中活跃线程和等待中的线程超过thread_pool_oversubscribe时,不会创建新的线程。

#---------------+
# replication   |
#---------------+
read-only
    = 1 只读模式,非SUPER权限,通常从服务器设置
master_info_repository
    = TABLE master服务器信息存放位置
relay_log_info_repository
    = TABLE 中继日志信息存放位置
relay_log_recovery
    = 1 salve宕机relaylog损坏,则自动修复
binlog_gtid_simple_recovery             
    = 1 5.7.7默认为1,当服务器重启或者log被purged,gitd查找是否从开始找                                   
gtid_mode
    = off 是否开启gtid模式
enforce_gtid_consistency
    = 1 强制GTID的一致性,如果开启基于gitd的复制,必须开启此项。
slave_compressed_protocol
    = 1 使用主从复制压缩
slave_net_timeout
    = 120 主从重连接超时时间,超过此值以后,从服务器会自动再次向主服务器发起连接请求。设置太小会造成主压力大
server-id                               = 25245 
#auto-increment-increment
    = 2 自增开始值
#auto-increment-offset
    = 1 自增跨度
relay-log-purge                         
    = 1 是否开启尽快的自动清除不在需要的relay日志
replicate-ignore-db 
    = test 设置主从复制排除的数据库
replicate-ignore-db
    = mysql                                 
slave-skip-errors
    = all 跳过所有主从复制中的错误,避免因错导致主从复制暂停,以后可以利用主从校验来恢复数据。
log-slave-updates
    A->B->C 链路复制,B中必须开启此项以及log-bin,这样B中才会将SQL写入二进制日志,用来给C复制
#---------------+
#       other   |
#---------------+
[mysqldump]
quick
max_allowed_packet                      
    = 32M
[xtrabackup]
compress
parallel
    =16
compress-threads
    =16
rebuild-threads
    =16

相关计算

1、myisam 索引存储所占用的空间,用这个来和key_buffer_size 做比较,不要超过此值
  select sum(index_length) from information_schema.tables where engine='myisam';
  也可以
  du -shc `find /path/to/mysql/data/directory/ -name "*.MYI"`
2、使用show status 和 show variables 命令的信息来监控键缓冲的使用情况,如果服务器运行了很长一段时间后,还是没有使用完所有的键缓冲,就可以调小key_buffer_size
  100 - ((key_blocks_unused * key_cache_block_size) * 100 / key_buffer_size)
  key_reads / Uptime (key_reads = `mysqladmin extended-status -r -i 10 | grep Key_reads`)计算每秒的缓存命中数
3、即使没有任何myisam表,依然需要将key_buffer_size 设置为较小的值,例如32M,mysql服务器有时候会在内部使用myisam表,例如group by 语句可能会使用myisam做临时表。
4、myisam_block_size变量控制着索引块大小,也可以指定每个索引块的大小,在create table 或者 create index 语句中使用key_block_size选项即可,最好保持和操作系统块大小一致,否则会发生写时读取
5、查看Threads_created 状态变量,如果查看到很少有每秒创建的新线程数少于十个,应该保持线程缓存足够大(thread_cache_size),查看Threads_connected并且尝试设置thread_cache_size足够大以便能处理业务压力正常的波动。
6、可以通过检查show innodb status 的输出中log部分来监控innodb的日志和日志缓冲区的I/O性能,通过观察Innodb_os_log_written状态变量来查看innodb多日志文件写出了多少数据,一个好用的经验法则是,查看10-100秒间隔的数字,然后记录峰值,可以这个来判断日志缓冲innodb_log_buffer_size是否设置的正好,例如,若看到峰值是每秒写100kb数据到日志,那么1MB的日志缓冲可能足够了,那么256的日志文件足够存储至少2560秒的日志记录。作为 一个经验法则,日志文件的全部大小,应该足够容纳服务器一个小时的活动内容。
7、InnoDB的page size一般是16KB,其数据校验也是针对这16KB来计算的,将数据写入
到磁盘是以page为单位进行操作的。操作系统写文件是以4KB作为单位的,那么每写一个
InnoDB的page到磁盘上,操作系统需要写4个块。而计算机硬件和操作系统,在极端情
况下(比如断电)往往并不能保证这一操作的原子性,16K的数据,写入4K时,发生了系
统断电或系统崩溃,只有一部分写是成功的,这种情况下就是partial page write(部分页
写入)问题。这时page数据出现不一样的情形,从而形成一个"断裂"的page,使数据产生
混乱。这个时候InnoDB对这种块错误是无 能为力的.在一些情况下可以关闭doublewrite
以获取更高的性能。比如在slave上可以关闭,因为即使出现了partial page write问题,数
据还是可以从中继日志中恢复。设置InnoDB_doublewrite=0即可关闭doublewrite buffer
8、通过查看show status中的两个值 Created_tmp_disk_tables、Created_tmp_tables来查看是否使用了硬盘临时表和内存临时表
  mysql -e 'show status;'| egrep 'Created_tmp_disk_tables|Created_tmp_tables'
9、观察Max_used_connections 状态变量随着时间的变化,如果这个值达到了max_connections,说明客户端至少被拒绝了一次,然后适当调整max值
10、通过观察Threads_connected状态变量并且找到它在一般情况下的最大值和最小值 mysql -e 'show status;'| egrep 'Threads' ,另外一个相关变量是Slow_launch_threads,这个状态如果是个很大的值,那么意味着某些情况延迟了连接分配新线程。
11、table_cache_size  这个缓存应该被设置的足够大,以避免总是需要重新打开和重新解析表的定义。可以通过观察Open_tables 的值以及在一段时间的变化来检查该变量,如果看到Opened_tables 每秒变化很大,那么table_cache值可能不够大,这个值从max_connections 的10倍开始设置是比较有道理的,但是在大部分场景下最好保持在10000一下甚至更低。
12、在线配置mysql配置文件地址 tools.percona.com
mysql
Web note ad 1