分享一个低配VPS下运行的mysql配置文件

在各种内存CPU核心只有1/2核，内存只有512M/1G的vps下，内存、CPU、硬盘都不是太充裕。因此主要思路是，禁止吃内存大户innodb引擎，默认使用MyISAM。禁止吃硬盘大户log-bin，再调节一些参数到合适的数值。

[mysql]

# CLIENT #
port                             = 3306
socket                           = /var/run/mysqld/mysqld.sock

[mysqld]

# GENERAL #
user                             = mysql
default-storage-engine           = MyISAM
default-tmp-storage-engine       = MYISAM
socket                           = /var/run/mysqld/mysqld.sock
pid-file                         = /var/run/mysqld/mysqld.pid
bind-address                     = 127.0.0.1
lc-messages-dir                  = /usr/share/mysql/
skip-external-locking

# MyISAM #
key-buffer-size                  = 16M
myisam-recover                   = FORCE,BACKUP

# SAFETY #
max-allowed-packet               = 16M
max-connect-errors               = 256
skip-name-resolve
sysdate-is-now                   = 1

# DATA STORAGE #
datadir                          = /var/lib/mysql/

# CACHES AND LIMITS #
tmp-table-size                   = 32M
max-heap-table-size              = 32M
query-cache-type                 = 0
query-cache-size                 = 0
max-connections                  = 64
thread-stack                     = 192K
thread-cache-size                = 4
thread-concurrency               = 2
open-files-limit                 = 1028
table-definition-cache           = 256
table-open-cache                 = 64

# INNODB #
loose-skip-innodb
loose-innodb-trx                 = 0
loose-innodb-locks               = 0
loose-innodb-lock-waits          = 0
loose-innodb-cmp                 = 0
loose-innodb-cmp-per-index       = 0
loose-innodb-cmp-per-index-reset = 0
loose-innodb-cmp-reset           = 0
loose-innodb-cmpmem              = 0
loose-innodb-cmpmem-reset        = 0
loose-innodb-buffer-page         = 0
loose-innodb-buffer-page-lru     = 0
loose-innodb-buffer-pool-stats   = 0
loose-innodb-metrics             = 0
loose-innodb-ft-default-stopword = 0
loose-innodb-ft-inserted         = 0
loose-innodb-ft-deleted          = 0
loose-innodb-ft-being-deleted    = 0
loose-innodb-ft-config           = 0
loose-innodb-ft-index-cache      = 0
loose-innodb-ft-index-table      = 0
loose-innodb-sys-tables          = 0
loose-innodb-sys-tablestats      = 0
loose-innodb-sys-indexes         = 0
loose-innodb-sys-columns         = 0
loose-innodb-sys-fields          = 0
loose-innodb-sys-foreign         = 0
loose-innodb-sys-foreign-cols    = 0

# LOGGING #
log-error                        = /var/log/mysql/error.log
log-queries-not-using-indexes    = 0
long-query-time                  = 1
slow-query-log                   = 1
slow-query-log-file              = /var/log/mysql/slow.log

重启后mysql的内存占用大概为70M+，其中和内存关系较大的是key-buffer-size、tmp-table-size和thread-cache-size属性，作用分别为缓存MyISAM的索引、执行Group By之类语言时使用的临时表内存空间、查询线程。

配置的使用环境为percona server 5.6，其他版本的mysql没有测试。

最后修改于 2016-11-06