mysql 配置文件详解

秒速五厘米 2022-08-06 08:19 135阅读 0赞

\#  
\# The MySQL database server configuration file.  
\#  
\# You can copy this to one of:  
\# - "/etc/mysql/my.cnf" to set global options,  
\# - "~/.my.cnf" to set user-specific options.  
\#   
\# One can use all long options that the program supports.  
\# Run program with --help to get a list of available options and with  
\# --print-defaults to see which it would actually understand and use.  
\#  
\# For explanations see  
\# http://dev.mysql.com/doc/mysql/en/server-system-variables.html  
  
  
  
  
\# This will be passed to all mysql clients  
\# It has been reported that passwords should be enclosed with ticks/quotes  
\# escpecially if they contain "\#" chars...  
\# Remember to edit /etc/mysql/debian.cnf when changing the socket location.  
\[client\]  
port            = 3306  
socket          = /var/run/mysqld/mysqld.sock  
  
  
  
  
\# Here is entries for some specific programs  
\# The following values assume you have at least 32M ram  
  
  
  
  
\# This was formally known as \[safe\_mysqld\]. Both versions are currently parsed.  
\[mysqld\_safe\]  
socket          = /var/run/mysqld/mysqld.sock  
nice            = 0  
  
  
  
  
\[mysqld\]  
\#  
\# \* Basic Settings  
\#  
user            = mysql  
pid-file        = /var/run/mysqld/mysqld.pid  
socket          = /var/run/mysqld/mysqld.sock  
port            = 3306  
basedir         = /usr  
datadir         = /var/lib/mysql  
tmpdir          = /tmp  
lc-messages-dir = /usr/share/mysql  
skip-external-locking  
\#  
\# Instead of skip-networking the default is now to listen only on  
\# localhost which is more compatible and is not less secure.  
\#bind-address           = 127.0.0.1  
\#  
\# \* Fine Tuning  
\#  
key\_buffer              = 16M \# 索引缓冲区的大小，严格说是它决定了数据库索引处理的速度，尤其 是索引读的速度  
max\_allowed\_packet      = 16M   
thread\_stack            = 192K  
thread\_cache\_size       = 8  \#重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能  
  根据物理内存设置规则如下：1G  ---> 8    2G  ---> 16    3G  ---> 32    >3G  ---> 64  
\# This replaces the startup script and checks MyISAM tables if needed  
\# the first time they are touched  
myisam-recover         = BACKUP  
\#max\_connections        = 100 \#实际MySQL服务器允许的最大连接数16384；  
\#table\_cache            = 64 \#高速缓存的大小.table\_cache的值在2G内存以下的机器中的值默认时256到 512，如果机器有4G内存,则默认这个值是2048，但这决意味着机器内存越大，这个值应该越大，因为table\_cache加大后，使得mysql对 SQL响应的速度更快了，不可避免的会产生更多的死锁（dead lock），这样反而使得数据库整个一套操作慢了下来，严重影响性能。  
\#thread\_concurrency     = 10  \#对mysql的性能影响很大, 在多个cpu(或多核)的情况下，错误设置了thread\_concurrency的值, 会导致mysql不能充分利用多cpu(或多核), 出现同一时刻只能一个cpu(或核)在工作的情况。应设为CPU核数的2倍. 比如有一个双核的CPU, 那么thread\_concurrency的应该为4; 2个双核的cpu, thread\_concurrency的值应为8.  
\#  
\# \* Query Cache Configuration  
\#  
query\_cache\_limit       = 1M  \#单个查询能够使用的缓冲区大小.缺省为1M  
query\_cache\_size        = 16M \#缓存机制简单的说就是缓存sql文本及查询结果，如果运行相同的sql，服务器直接从缓存中取到结果，而不需要再去解析和执行sql。如果表更改了，那么使用这个表的所有缓冲查询将不再有效，查询缓存值的相关条目被清空  
\#  
\# \* Logging and Replication  
\#  
\# Both location gets rotated by the cronjob.  
\# Be aware that this log type is a performance killer.  
\# As of 5.1 you can enable the log at runtime!  
\#general\_log\_file        = /var/log/mysql/mysql.log  \#查询日志  
\#general\_log             = 1  
\#  
\# Error log - should be very few entries.  
\#  
log\_error = /var/log/mysql/error.log  \#错误日志  
\#  
\# Here you can see queries with especially long duration  
\#log\_slow\_queries       = /var/log/mysql/mysql-slow.log \#慢查询日志  
\#long\_query\_time = 2  
\#log-queries-not-using-indexes  
\#  
\# The following can be used as easy to replay backup logs or for replication.  
\# note: if you are setting up a replication slave, see README.Debian about  
\#       other settings you may need to change.  
server-id               = 2  
\#log\_bin                        = /var/log/mysql/mysql-bin.log \#二进制日志  
expire\_logs\_days        = 10  
max\_binlog\_size         = 100M  
\#binlog\_do\_db           = include\_database\_name  
\#binlog\_ignore\_db       = include\_database\_name  
\#  
\# \* InnoDB  
\#  
\# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.  
\# Read the manual for more InnoDB related options. There are many!  
\#  
\# \* Security Features  
\#  
\# Read the manual, too, if you want chroot!  
\# chroot = /var/lib/mysql/  
\#  
\# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".  
\#  
\# ssl-ca=/etc/mysql/cacert.pem  
\# ssl-cert=/etc/mysql/server-cert.pem  
\# ssl-key=/etc/mysql/server-key.pem  
  
  
  
  
  
  
  
  
  
  
  
  
\[mysqldump\]  
quick  
quote-names  
max\_allowed\_packet      = 16M  
  
  
  
  
\[mysql\]  
\#no-auto-rehash \# faster start of mysql but no tab completition  
  
  
  
  
\[isamchk\]  
key\_buffer              = 16M \# 索引缓冲区的大小，严格说是它决定了数据库索引处理的速度，尤其 是索引读的速度  
  
  
  
  
\#  
\# \* IMPORTANT: Additional settings that can override those from this file!  
\#   The files must end with '.cnf', otherwise they'll be ignored.  
\#

!includedir /etc/mysql/conf.d/

![微笑][smile.gif]如果你觉得此文章对你有用，给我点动力，请我喝杯奶茶咯![微笑][smile.gif]

![20151119105239139][]

[smile.gif]: /images/20220806/04da5c25ced248eeb882dcafb45b6540.png
[20151119105239139]: /images/20220806/0c4108a66bf14e79a0dcc6f2656b187d.png