my.cnf

一、简介

　　MySQL的启动配置文件。

　　通过mysqld的--defaults-file参数来配置，默认的读取顺序(mysql --help来查看)：

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

　　可见，是按/etc/my.cnf-->/etc/mysql/my.cnf-->/usr/local/mysql/etc/my.cnf-->~/.my.cnf的顺序来读取配置。如果这几个配置文件都有同一个参数，则遵循覆盖原则。

 

二、配置示例

如下是某公司的MySQL 5.6配置my.cnf，谨供参考。

#
[mysqld]

# GENERAL #
character_set_server           = utf8mb4
collation_server               = utf8mb4_general_ci
user                           = mysql
default_storage_engine         = InnoDB
port                           = 3306
socket                         = /usr/local/mysql/multi/3306/socket/mysql.sock
pid_file                       = /usr/local/mysql/multi/3306/pid/mysql.pid

# MyISAM #
key_buffer_size                = 128M
myisam_sort_buffer_size        = 8M
read_rnd_buffer_size           = 262144

# SAFETY #
back_log                       = 50
bind-address                   = 0.0.0.0
innodb_strict_mode             = 1
max_allowed_packet             = 32M
max_connect_errors             = 1000000
interactive_timeout            = 900
wait_timeout                   = 900
skip_grant_tables              = 0
skip_name_resolve              = 1
host_cache_size                = 0
sql_mode                       = TRADITIONAL
sysdate_is_now                 = 1
transaction_isolation          = REPEATABLE-READ
explicit_defaults_for_timestamp = 1

# DATA STORAGE #
basedir                        = /usr/local/mysql
datadir                        = /usr/local/mysql/multi/3306/data
tmpdir                         = /usr/local/mysql/multi/3306/tmp

# BINARY LOGGING #
binlog_cache_size              = 1M
binlog_format                  = ROW
expire_logs_days               = 1
max_binlog_size                = 512M
max_binlog_cache_size          = 4G
log_bin                        = /usr/local/mysql/multi/3306/binlog/mysql-bin
log_bin_index                  = /usr/local/mysql/multi/3306/binlog/mysql-bin.index
sync_binlog                    = 0
innodb_support_xa              = 1
master-info-repository         = TABLE

# REPLICATION #
log_slave_updates              = 0
read_only                      = 0
relay_log                      = /usr/local/mysql/multi/3306/binlog/relay-bin
relay_log_index                = /usr/local/mysql/multi/3306/binlog/relay-bin.index
server_id                      = 26759
slave_skip_errors              = ALL

slave_net_timeout              = 60
relay-log-info-repository      = TABLE
slave_compressed_protocol      = ON

# CACHES AND LIMITS #
max_connections                = 5000
max_heap_table_size            = 128M
tmp_table_size                 = 128M
open_files_limit               = 65535
query_cache_limit              = 1M
query_cache_min_res_unit       = 4096
query_cache_size               = 0
query_cache_type               = 0
join_buffer_size               = 1M
read_buffer_size               = 1M
sort_buffer_size               = 1M
table_definition_cache         = 4096
table_open_cache               = 4000
thread_cache_size              = 1000

# INNODB #
innodb_autoextend_increment    = 8
innodb_buffer_pool_instances   = 8
innodb_buffer_pool_size        = 1G
innodb_buffer_pool_load_at_startup  = ON
innodb_buffer_pool_dump_at_shutdown = ON
innodb_data_file_path          = ibdata1:1024M:autoextend
innodb_fast_shutdown           = 1
innodb_file_format             = Barracuda
innodb_file_format_max         = Barracuda
innodb_file_per_table          = 1
innodb_force_recovery          = 0
innodb_flush_log_at_trx_commit = 0
innodb_flush_method            = O_DIRECT
innodb_lock_wait_timeout       = 50
innodb_log_buffer_size         = 8M
innodb_log_file_size           = 512M
innodb_log_files_in_group      = 2
innodb_open_files              = 10000
innodb_purge_threads           = 4
innodb_read_io_threads         = 8
innodb_write_io_threads        = 8
innodb_show_verbose_locks      = 1
innodb_sync_spin_loops         = 30
innodb_thread_concurrency      = 24
innodb_print_all_deadlocks     = 1

# LOGGING #
general_log                    = 0
general_log_file               = /usr/local/mysql/multi/3306/log/mysql-general.log
log_error                      = /usr/local/mysql/multi/3306/log/mysql-error.log
log_queries_not_using_indexes  = 1
long_query_time                = 1
slow_query_log                 = 1
slow_query_log_file            = /usr/local/mysql/multi/3306/log/mysql-slow.log

#
enforce_gtid_consistency       = 0
gtid_mode                      = OFF