MySQL配置文件

1. 查看MySQL参数文件读取顺序

# mysqld –verbose –help | grep -A 1 ‘Default options’

1.1 检查MySQL环境变量

[root@rhel5 usr]# mysqladmin -uroot -p123456 variables

2. 典型my.cnf

MySQL的最佳参数配置,可以参考:
https://github.com/jdaaaaaavid/mysql_best_configuration

2.1 mysqld部分

######################
## CPU 6核 16G内存   ##
######################

[mysqld]
# GENERAL #
user                           = mysql
default_storage_engine         = InnoDB
socket                         = /data/mysql/mysql.sock
pid-file                       = /data/mysql/mysql.pid
character-set-server           = utf8
# MyISAM #
key_buffer_size                = 32M
myisam_recover                 = FORCE,BACKUP
# SAFETY #
max_allowed_packet             = 16M
max_connect_errors             = 1000000
skip_name_resolve
# DATA STORAGE #
datadir                        = /data/mysql/
# BINARY LOGGING #
log_bin                        = /data/mysql/mysql-bin
expire_logs_days               = 14
sync_binlog                    = 1
# CACHES AND LIMITS #
tmp_table_size                 = 32M
max_heap_table_size            = 32M
query_cache_type               = 0
query_cache_size               = 0
max_connections                = 500
thread_cache_size              = 50
open_files_limit               = 65535
table_definition_cache         = 1024
table_open_cache               = 2048
# INNODB #
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 256M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 10G
innodb_thread_concurrency      = 12
thread_handling                = pool-of-threads
#auto_increment_increment      = 2
#auto_increment_offset         = 0
# LOGGING #
log_error                      = /data/mysql/mysql-error.log
log_queries_not_using_indexes  = 1
slow_query_log                 = 1
slow_query_log_file            = /data/mysql/mysql-slow.log

[mysql]
# CLIENT #
port                          = 3306
socket                        = /data/mysql/mysql.sock
default-character-set         = utf8 

2.2 多实例配置

参数含义不解释

[mysqld_multi]
mysqld                          = /usr/bin/mysqld_safe
mysqladmin                      = /usr/bin/mysqladmin
user                            = root
log                             = /tmp/mysqld_multi.log

[mysqld1]
socket                          = /mysqldata1/mysql_3301.sock
port                            = 3301
pid-file                        = /mysqldata1/mysql_3301.pid
datadir                         = /mysqldata1
log_error                       = /mysqldata1/mysql_3301.error
user                            = mysql
character_set_server            = utf8
lower_case_table_names          = 1
init_connect                    = 'SET NAMES utf8'
server_id                       = 1
log_bin                         = master-bin
log-bin-index                   = master-bin.index
relay_log                       = mysql-relay-bin
relay_log_index                 = mysql-relay-bin.index
log_slave_updates               = 1
auto_increment_increment        = 2
auto_increment_offset           = 1
#binlog-ignore-db               = test
rpl_semi_sync_master_enabled    = 1
rpl_semi_sync_slave_enabled     = 1
slow_query_log                  = 1
slow_query_log_file             = /mysqldata1/mysql_3301_slow.log
log_queries_not_using_indexes   = 1

[mysqld2]
socket                          = /mysqldata2/mysql_3302.sock
port                            = 3302
pid-file                        = /mysqldata2/mysql_3302.pid
datadir                         = /mysqldata2
log_error                       = /mysqldata2/mysql_3302.error
user                            = mysql
character_set_server            = utf8
lower_case_table_names          = 1
init_connect                    = 'SET NAMES utf8'
server-id                       = 2
log_bin                         = master-bin
log-bin-index                   = master-bin.index
relay-log                       = mysql-relay-bin
relay-log-index                 = mysql-relay-bin.index
log_slave_updates               = 1
auto_increment_increment        = 2
auto_increment_offset           = 2
rpl_semi_sync_master_enabled    = 1
rpl_semi_sync_slave_enabled     = 1
read_only                       = 1
slow_query_log                  = 1
slow_query_log_file             = /mysqldata2/mysql_3302_slow.log
log_queries_not_using_indexes   = 1

[mysql-proxy]
proxy-address                   = 192.168.1.37:4041
log-file                        = /mysqlproxy/mysqlproxy_4401.error
log-level                       = debug
pid-file                        = /mysqlproxy/mysqlproxy_4401.pid
event-threads                   = 5
#user                           = mysql
max-open-files                  = 1024

[mysql]
socket                          = /mysqldata1/mysql_3301.sock

[mysqladmin]
socket                          = /mysqldata1/mysql_3301.sock

2.3 binlog参数

max_binlog_size                 = 1073741824
binlog_cache_size               = 32768
sync_binlog                     = 0
binlog-do-db                    =
binlog-ignore-db                = test
log_slave_updates               = 1
binlog-format                   = STATEMENT

2.4 InnoDB参数

innodb_read_io_threads          = 4
innodb_write_io_threads         = 4
innodb_buffer_pool_size         = 21474836480			# 20g
innodb_additional_mem_pool_size = 209715200
innodb_log_buffer_size          = 8388608
innodb_log_files_in_group       = 4
innodb_log_file_size            = 524288000
innodb_mirrored_log_groups      = 1
innodb_log_group_home_dir       = ./
innodb_data_file_path           = 

innodb_buffer_pool_instances    = 8
innodb_old_blocks_time          = 1000
innodb_old_blocks_pct           = 37
innodb_max_dirty_pages_pct      = 75
innodb_io_capacity              = 200
innodb_adaptive_flushing        = ON
innodb_purge_batch_size         = 300
innodb_purge_threads            = 1

关于紫砂壶

感悟技术人生
此条目发表在MySQL分类目录,贴了标签。将固定链接加入收藏夹。