MySQL5.6配置案例
记录一下某台MySQL5.6的my.cnf配置,(本例是8核8G):[client]
socket = /var/lib/mysql/mysql.sock
port = 3306
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/xdb1.pid
user = mysql
server_id = 1
default_storage_engine = InnoDB
# InnoDB
innodb_log_file_size = 48M
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 64M
innodb_write_io_threads = 8
innodb_read_io_threads = 8
# MyISAM
#key_buffer_size = 48M
# character-set
character-set-server=utf8
collation-server=utf8_general_ci
# name-resolve
skip-host-cache
skip-name-resolve
max_connections = 600
max_connect_errors = 30
open_files_limit = 65535
table_open_cache = 2000
max_allowed_packet = 4M
binlog_cache_size = 4M
max_heap_table_size = 16M
tmp_table_size = 16M
#lower_case_table_names = 1
#skip-external-locking
# LOG
log_error = /data/mysql/xdb1.log
long_query_time = 2
slow-query-log
slow_query_log_file = /data/mysql/xdb1-slow.log
# Others
explicit_defaults_for_timestamp=true
open_files_limit = 5000
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqldump]
quick
max_allowed_packet=16M
参考:https://blog.linuxeye.com/379.html MySQL性能调优my.cnf详解
https://help.directadmin.com/item.php?id=44 CentOS and MySQL 5.6
https://www.techinfobest.com/optimize-mysql-table_open_cache/ Optimize MySQL table_open_cache
评论