MySQL配置文件参数详解

  1. 云栖社区>
  2. 博客>
  3. 正文

MySQL配置文件参数详解

技术小胖子 2017-12-15 22:13:00 浏览666
展开阅读全文

[client]

port = 3307

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

default-character-set=utf8


[mysql]

prompt = mysql(\\u@\h-\R:\\m:\\s [\\d])>

#提示当前数据库操作用户名、库名、时间等

default-character-set=utf8


[mysqld]

basedir = /usr/local/mysql5_6

datadir = /usr/local/mysql5_6/data

port = 3307

server_id = 111

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

log-error = /usr/local/mysql5_6/error.log

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


character-set-server=utf8


max_connections = 3000

#MySQL允许最大的进程连接数,如果经常出现Too Many Connections的错误提示,则需要增大此值。


skip-external-locking

skip-name-resolve


max_allowed_packet = 32M

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


wait_timeout = 28800

#结束回话等待时间


thread_cache_size = 64

#服务器线程缓存这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求,那么请求将从缓存中读取,如果缓存中是空的或者是新的请求,那么这个线程将被重新创建,如果有很多新的线程,增加这个值可以改善系统性能.通过比较Connections 和 Threads_created 状态的变量,可以看到这个变量的作用。设置规则如下:1GB 内存配置为8,2GB配置为16,3GB配置为32,4GB或更高内存,可配置更大。


explicit_defaults_for_timestamp=true

query_cache_limit = 4M

#指定单个查询能够使用的缓冲区大小,缺省为1M


query_cache_min_res_unit = 2k

#默认是4KB,设置值大对大数据查询有好处,但如果你的查询都是小数据查询,就容易造成内存碎片和浪费

#查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%

#如果查询缓存碎片率超过20%,可以用FLUSH QUERY CACHE整理缓存碎片,或者试试减小query_cache_min_res_unit,如果你的查询都是小数据量的话。

#查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%

#查询缓存利用率在25%以下的话说明query_cache_size设置的过大,可适当减小;查询缓存利用率在80%以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小,要不就是碎片太多。

#查询缓存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%


tmp_table_size = 128M

# tmp_table_size 的默认大小是 32M。如果一张临时表超出该大小,MySQL产生一个 The table tbl_name is full 形式的错误,如果你做很多高级 GROUP BY 查询,增加 tmp_table_size 值。如果超过该值,则会将临时表写入磁盘。


thread_concurrency = 32

#设置thread_concurrency的值的正确与否,对mysql的性能影响很大,在多个cpu(或多核)的情况下,错误设置了thread_concurrency的值,会导致mysql不能充分利用多cpu(或多核),出现同一时刻只能一个cpu(或核)在工作的情况。thread_concurrency应设为CPU核数的2倍比如有一个双核的CPU,那么thread_concurrency的应该为4;2个双核的cpu, thread_concurrency的值应为8


open_files_limit = 10240

#每个客户端的连接也是一个文件句柄。


back_log = 2048

#在MYSQL暂时停止响应新请求之前,短时间内的多少个请求可以被存在堆栈中。如果系统在短时间内有很多连接,则需要增大该参数的值,该参数值指定到来的TCP/IP连接的监听队列的大小。


innodb_flush_log_at_trx_commit = 1

#每次commit 日志缓存中的数据刷到磁盘中


innodb_lock_wait_timeout = 50

#InnoDB事务在被回滚之前可以等待一个锁定的超时秒数。InnoDB在它自己的锁定表中自动检测事务死锁并且回滚事务。InnoDB用LOCKTABLES语句注意到锁定设置。默认值是50秒


#innodb_auto_extend_increment = 64M

#可帮助降低碎片


innodb_buffer_pool_size = 8192M

#这对Innodb表来说非常重要。Innodb相比MyISAM表对缓冲更为敏感。MyISAM可以在默认的key_buffer_size设置下运行的可以,然而Innodb在默认的innodb_buffer_pool_size设置下却跟蜗牛似的。由于Innodb把数据和索引都缓存起来,无需留给操作系统太多的内存,因此如果只需要用Innodb的话则可以设置它高达70-80%的可用内存。一些应用于 key_buffer 的规则有 — 如果你的数据量不大,并且不会暴增,那么无需把innodb_buffer_pool_size 设置的太大了


innodb_log_buffer_size = 16M

#此参数确定些日志文件所用的内存大小,以M为单位。缓冲区更大能提高性能,但意外的故障将会丢失数据


innodb_buffer_pool_instances = 8

#用于优化更高并发的负载


innodb_log_file_size = 60M

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


innodb_log_files_in_group = 3M

#为提高性能,MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3M


innodb_io_capacity = 400

#InnoDB曾经在代码里写死了假设服务器运行在每秒100个I/O操作的单硬盘上。默认值很糟糕。现在可以告诉InnoDB服务器有多大的I/O能力。有时需要把这个值设置得相当高(像SSD这样极快的存储设备上需要设置为上万)才能稳定地刷新脏页。


innodb_write_io_threads = 4

#文件IO的线程数,一般为 4,但是在 Windows 下,可以设置得较大。


innodb_read_io_threads = 4

#文件IO的线程数,一般为 4,但是在 Windows 下,可以设置得较大。


innodb_thread_concurrency = 8

#服务器有几个CPU就设置为几,建议用默认设置,一般为8


innodb_file_per_table = ON

#其默认值在mysql5.6.6+后为ON。开启此选项后,关于InnoDB表的数据和索引单独存储在自己的表空间中(.ibd结尾的文件)。否则,存储在系统的表空间中(ibdata)。


innodb_print_all_deadlocks = 1

#死锁信息打印到错误日志里


#------快速预热Buffer_Pool缓冲池-------#

#在之前的版本里,如果一台高负荷的机器重启后,内存中大量的热数据被清空,此时就会重新从磁盘加载到Buffer_Pool缓冲池里,这样当高峰期间,性能就会变得很差,连接数就会很高。在关闭MySQL时,会把内存中的热数据保存在磁盘里ib_buffer_pool文件中,位于数据目录下,在启动后,会自动加载热数据到Buffer_Pool缓冲池里。


innodb_buffer_pool_dump_at_shutdown = 1

#在关闭时把热数据dump到本地磁盘。


innodb_buffer_pool_dump_now = 1

#采用手工方式把热数据dump到本地磁盘。


innodb_buffer_pool_load_at_startup = 1

#在启动时把热数据加载到内存。


innodb_buffer_pool_load_now = 1

#采用手工方式把热数据加载到内存。


table_open_cache = 4096

#从官方文档看出在MySQL5.6.8+开始默认值为2000,就能简单的判断出原来默认值是不够的。可以通过观察Opened_tables其值及其一段时间的变化来检查该变量。如果看到Opened_tables的值很大并且又不经常执行FLUSH TABLES(执行其命令强制所有的表重新关闭且打开),那么可能你应该增加该变量的值。


max_connect_errors = 1000

#设置每个主机的连接请求异常中断的最大次数,当超过该次数,MYSQL服务器将禁止host的连接请求,直到mysql服务器重启或通过flushhosts命令清空此host的相关信息。


expire_logs_days = 7

#如果启用了二进制日志,应该打开这个选项,可以让服务器在指定的天数之后清理旧的二进制日志。如果不启用,最终服务器的空间会被耗尽,导致服务器卡住或崩溃。


sort_buffer_size = 1M

#Sort_Buffer_Size是一个connection级参数,在每个connection(session)第一次需要使用这个buffer的时候,一次性分配设置的内存。Sort_Buffer_Size并不是越大越好,由于是connection级的参数,过大的设置+高并发可能会耗尽系统内存资源。例如:500个连接将会消耗500*sort_buffer_size(8M)=4G内存,Sort_Buffer_Size 超过2KB的时候,就会使用mmap() 而不是 malloc() 来进行内存分配,导致效率降低。


join_buffer_size = 8M

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


read_buffer_size = 1M

#MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区,MySql会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁,并且你认为频繁扫描进行得太慢,可以通过增加该变量值以及内存缓冲区大小提高其性能。和sort_buffer_size一样,该参数对应的分配内存也是每个连接独享。


read_rnd_buffer_size = 16M

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


myisam_sort_buffer_size = 256M

# MyISAM表发生变化时重新排序所需的缓冲


bulk_insert_buffer_size = 64M

#批量插入数据缓存大小,可以有效提高插入效率,默认为8M


key_buffer_size = 1024M

#批定用于索引的缓冲区大小,增加它可以得到更好的索引处理性能,对于内存在4GB左右的服务器来说,该参数可设置为256MB或384MB。


myisam_max_sort_file_size = 10G

# MySQL重建索引时所允许的最大临时文件的大小 (当 REPAIR, ALTER TABLE 或者 LOAD DATA INFILE)如果文件大小比此值更大,索引会通过键值缓冲创建(更慢)



#-------------------mysql5.6的主从增加了不少参数,提升了主从同步的安全和效率,以下主从参数详解--------------------#

server-id=1

binlog-format=ROW

log-bin=master-bin.log

log-bin-index=master-bin.index

log-slave-updates=true


#gtid-mode=on

#enforce-gtid-consistency=true

#这两个参数是启用mysql5.6中的UUID同步模式,两个参数必须一起打开,否则报错,slave在做同步复制时,无须找到binlog日志和POS点,直接change master to master_auto_position=1即可,自动找点同步。

#GTID的局限性: (鉴于这些局限性,慎用,因此做了关闭处理)

#1.CREATE TABLE...SELECT语句不支持。因为该语句会被拆分成createtable和insert两个事务,并且这个两个事务被分配了同一个GTID,这会导致insert被备库忽略掉。

#2.不支持CREATE TEMPORARY TABLE、DROP TEMPORARY TABLE 临时表操作。


master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

slave-parallel-workers=2

#默认是0,不开启,最大并发数为1024个线程。主从复制启用4个sql线程,提高从服务器吞吐量,减少延迟,使用并发的 SQL 线程对不同数据库并行应用事件


binlog-checksum=CRC32

master-verify-checksum=1

slave-sql-verify-checksum=1

binlog-rows-query-log-events=1

#这四个参数是启用binlog/relaylog的校验,防止日志出错


sync_binlog=1

#默认情况下,并不是每次写入时都将binlog与硬盘同步。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃,有可能binlog中最后的语句丢失了。要想防止这种情况,你可以使用sync_binlog全局变量(1是最安全的值,但也是最慢的),使binlog在每N次binlog写入后与硬盘同步。即使sync_binlog设置为1,出现崩溃时,也有可能表内容和binlog内容之间存在不一致性。如果使用InnoDB表,MySQL服务器处理COMMIT语句,它将整个事务写入binlog并将事务提交到InnoDB中。如果在两次操作之间出现崩溃,重启时,事务被InnoDB回滚,但仍然存在binlog中。可以用--innodb-safe-binlog选项来增加InnoDB表内容和binlog之间的一致性。(注释:在MySQL5.1中不需要--innodb-safe-binlog;由于引入了XA事务支持,该选项作废了),该选项可以提供更大程度的安全,使每个事务的binlog(sync_binlog=1)和(默认情况为真)InnoDB日志与硬盘同步,该选项的效果是崩溃后重启时,在滚回事务后,MySQL服务器从binlog剪切回滚的InnoDB事务。这样可以确保binlog反馈InnoDB表的确切数据等,并使从服务器保持与主服务器保持同步(不接收 回滚的语句)。


relay_log_purge = 1

relay_log_recovery = 1

#这两个是启用relaylog的自动修复功能,避免由于网络之类的外因造成日志损坏,主从停止。


slave-skip-errors = 1062


binlog_cache_size = 4M


max_binlog_cache_size = 2G

#指定binary log缓存的最大容量,如果设置的过小,则在执行复杂查询语句时MySQL会出错。


max_binlog_size = 1G

#指定binary log文件的最大容量,默认为1GB



[mysqldump]

quick

max_allowed_packet = 2048M


sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES


     本文转自aaron428 51CTO博客,原文链接http://blog.51cto.com/aaronsa/1740634:,如需转载请自行联系原作者





网友评论

登录后评论
0/500
评论
技术小胖子
+ 关注