MySQL配置文件参数的一些说明

文章由LinuxBoy分享于2019-03-30 04:03:17热评（396）

MySQL配置文件参数的一些说明

根据mysql[d] --help --verbose的输出可知 mysql客户端和服务端程序加载配置文件顺序和路径如下

/etc/mysql/my.cnf --> /etc/my.cnf --> --default-extra-file 选项指定的文件 --> ~/.my.cnf

mysql采用集中式的配置文件，配置文件是分块的，以 [NAME] 独立行开始，到下一个[NAME] 独立行结束属于作用于NAME指定的程序的配置。

比如[client] 是所有mysql客户端程序读取的配置块。[server]是所有服务端如mysqld会读取的配置块。[mysqldump]则是只有 mysqldump 才会读取的配置文件，[mysql] 是mysql这个客户端程序的配置块，而[mysqld]是mysql服务端程序mysqld 和 mysqld_safe ,mysqld_multi 的配置文件。

对初始配置文件的参数及其它参数的解释：

[client]
port = 3306
socket = /tmp/mysql.sock
#当mysql客户端和服务器在同一主机上时，使用 socket 通信较TCP/IP效率更高，这里指定的soket文件路径需与[mysqld]中的设置保持一致
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
#禁用external-locking。
参见external-locking的解释：Use system (external) locking (disabled by default). With this option enabled you can run myisamchk to test （not repair) tables
while the MySQL server is running. Disable with --skip-external-locking.
key_buffer_size = 256M
# The size of the buffer used for index blocks for MyISAM tables. Increase this to get better index handling (for all reads and multiple writes) to as much as
you can afford
max_allowed_packet = 1M
# Max packet length to send to or receive from the server
table_open_cache = 256
#The number of cached open tables
sort_buffer_size = 1M
# Each thread that needs to do a sort allocates a buffer of this size
每个需要排序的线程将会被分配这样大小的一个buffer
read_buffer_size = 1M
# Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans, you may want to increase
this value
thread_cache_size = 8
# How many threads we should keep in a cache for reuse
query-cache-type=name
# OFF = Don't cache or retrieve results. ON = Cache all results except SELECT SQL_NO_CACHE ... queries. DEMAND = Cache only SELECT SQL_CACHE ... queries
query_cache_size= 16M
#The memory allocated to store results from old queries #查询缓存的内存总大小，其必须是1024的整数倍，单位为字节。MySQL启动时，一次性分配并且初始化这里指定大小
的内存空间。改变其值，MySQL会立刻删除所有的缓存对象并重新配置其大小及初始化。在性能较强的通用服务器上，查询缓存可能会成影响服务器扩展的因素，因为它存在成为服务器资源竞
争单点的可能性，在多核心的服务器上甚至还有可能导致服务进程宕掉。
不会缓存的内容：用户自定函数、用户自定义的变量、临时表、mysql库的系统表、列级别的权限、存储函数、不确定数据
query_cache_min_res_unit = #
# The minimum size for blocks allocated by the query cache 存储缓存的最小内存块；这个值过小，会减少空间浪费，但会导致更频繁的内存块申请操作；设置的过大，会有着
更高的碎片产生率。可以通过(query_cache_size-Qcache_free_memory)/Qcache_queryes_in_cache来获得一个接近理想的值。同时，如果Qcache_free_blocks存在空闲块，但
Qcache_lowmem_prunes的值仍然在增长，则表明碎片过多导致了缓存结果会过早删除。
query_cache_limit = #
# Don't cache results that are bigger than this #MySQL允许缓存的单个缓存对象的最大值。不过，MySQL只有在查询的所有结果都返回后才知道其是否超出此大小，但其在查询
一开始便会尝试使用缓存存储查询结果，一旦发现超时可缓存最大值则会从缓存中将其删除，并增大Qcache_not_cached的值。因此，如果知道某查询的结果会超出可缓存的最大对象，则应该
在查询语句中使用SQL_NO_CACHE。
query_cache_wlock_invalidate =
# Invalidate queries in query cache on LOCK for write
如果某个数据表被其它的thread锁住，是否仍然从查询缓存中返回结果。OFF表示返回。
thread_concurrency = 4
# Permits the application to give the threads system a hint for the desired number of threads that should be run at the same time. Try number of CPU's*2 for
thread_concurrency
datadir=/mydata/data
innodb_file_per_table = 1 #启用InnoDB表每表一文件，默认所有库使用一个表空间，这个在单表备份和恢复中是必须的，而且也便于管理，建议开启
log-bin=/mybinlog/mysql-bin
#二进制日志目录及文件名前缀
log_slow_queries={YES|NO}
#是否记录慢查询日志。慢查询是指查询的执行时间超出long_query_time参数所设定时长的事件。MySQL 5.6将此参数修改为了slow_query_log。作用范围为全局级别，可用于配置文
件，属动态变量。
long_query_time=#
#设定区别慢查询与一般查询的语句执行时间长度。这里的语句执行时长为实际的执行时间，而非在CPU上的执行时长，因此，负载较重的服务器上更容易产生慢查询。其最小值为0，
默认值为10，单位是秒钟。它也支持毫秒级的解析度。作用范围为全局或会话级别，可用于配置文件，属动态变量。
--slow-query-log
#Log slow queries to a table or log file. Defaults logging to a file hostname-slow.log or a table mysql.slow_log if --log-output=TABLE is used. Must be enabled
to activate other slow log options
--slow-query-log-file=name
#Log slow queries to given log file. Defaults logging to hostname-slow.log. Must be enabled to activate other slow log options
--long-query-time=#
#Log all queries that have taken more than long_query_time seconds to execute to file. The argument will be treated as a decimal value with microsecond
precision
innodb_flush_log_at_trx_commit:
0:the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a
transaction commit；
1：(the default) the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file；
2: the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it.；
For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, use innodb_flush_log_at_trx_commit=1 and
sync_binlog=1 in your master server my.cnf file.
Caution
Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not.
Then the durability of transactions is not guaranteed even with the setting 1, and in the worst case a power outage can even corrupt the InnoDB database. Using a
battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try using the Unix
command hdparm to disable the caching of disk writes in hardware caches, or use some other command specific to the hardware vendor.
innodb_additional_mem_pool_size
The size in bytes of a memory pool InnoDB uses to store data dictionary information and other internal data structures。The more tables you have in your
application, the more memory you need to allocate here. If InnoDB runs out of memory in this pool, it starts to allocate memory from the operating system and writes
warning messages to the MySQL error log. The default value is 8MB.
innodb_buffer_pool_size
The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. mysql5.5上缺省值128M.如果是专用的DB服务器，且以InnoDB引擎为主的场景
，通常可设置物理内存的
50%如果是非专用DB服务器，可以先尝试设置成内存的1/4，如果有问题再调整重要配置参数和变量：
sql_log_bin
在恢复备份的数据（逻辑备份）时，需要关闭二进制日志记录，以免将恢复过程也记录进日志。
innodb_file_per_table = 1
启用InnoDB表每表一文件，默认所有库使用一个表空间，这个在单表备份和恢复中是必须的，而且也便于管理，建议开启

大部分配置文件的参数可在服务器启动时作为选项提供。因此可以使用mysqld --help --verbose查看配置参数的解。并且服务器启动后可以通过该命令输出的末尾查看当前服务器使

用的变量参数值。

关于配置文件中各参数，更全面、详细的请参考http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html

请将版本号5.5改成你所使用的版本。

MySQL配置文件参数的一些说明