mysql 配置文件详解 秒速五厘米 2022-08-06 08:19 142阅读 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
还没有评论,来说两句吧...