MySQL 配置文件详解

近几个月 team 里就剩我一个工程师，前端后端运维杂活儿一大堆，各种开会 & 面试，一直没来得及写 blog，近期小组内部一起研究 mysql，所以先分享我的 mysql 配置(附带部分配置项说明)以供参考，之后更新也当做个记录。

environment

  • MacBook Pro (Retina, 13-inch, Early 2015)
  • macOS 10.12.5
  • slave version 5.7.18
  • master version 5.5.33

my.cnf

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187

[client]
port = 3306
socket = /tmp/mysql.sock
# 低版本未指定时不同终端可能会出现不同字符集
default-character-set = utf8mb4

[mysqld]
# basic settings #
user = mysql
port = 3306
socket = /tmp/mysql.sock
server_id = 2
character-set-server = utf8mb4
default-storage-engine = INNODB
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
autocommit = 1
# 将事务隔离级别设置为READ-COMMITTED
transaction_isolation = READ-COMMITTED
# 设置处理TIMESTAMP列的方式，详见官方文档说明
explicit_defaults_for_timestamp = 1
# 接受的数据包大小，有时大的插入和更新会失败，将max_allowed_packet设置适当避免该问题
max_allowed_packet = 128M
event_scheduler = 1
# 关闭鸡肋的query_cache
query_cache_type = 0
query_cache_size = 0
# os file system config #
lower_case_table_names = 2

# connection #
# 使用MySQL客户端连接超时时间设为3分钟
interactive_timeout = 1800
# 使用JDBC连接超时时间设为3分钟
wait_timeout = 1800
# 锁等待时间
lock_wait_timeout = 1800
# 该参数目的是不再进行反解析，可以加快数据库的反应时间
skip_name_resolve = 1
# 允许的最大连接数
max_connections = 512
# 允许的最大错误连接数，超过该值客户端将被屏蔽，默认为100，一般将该值设的较大避免客户端被屏蔽引发难以预料的问题
max_connect_errors = 1000000

# table cache performance settings
table_open_cache = 4096
table_definition_cache = 4096
table_open_cache_instances = 32

# session memory settings #
# MySQL读入缓冲区大小
read_buffer_size = 16M
# MySQL随机读缓冲区大小
read_rnd_buffer_size = 32M
# 排序缓存大小，在排序大量数据时该值将影响order by子句的执行效率
sort_buffer_size = 32M
# 临时表大小，在排序和连接较多时，适当
tmp_table_size = 64M
# 连接缓存大小，在连接大表时，该值将影响连接查询的效率
join_buffer_size = 128M
thread_cache_size = 64
thread_stack = 512K

# log settings #
log_error = error.log
# 开启慢查询日志
slow_query_log = 1
# 慢查询日志存放位置
slow_query_log_file = /usr/local/var/mysql/liubindeMBP-slow.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
# 二进制日志过期时间
expire_logs_days = 90
# 超过多少秒的查询，被视为慢查询
long_query_time = 2  
min_examined_row_limit = 100  
binlog-rows-query-log-events = 1  
log-bin-trust-function-creators = 1  
expire-logs-days = 90  
#log-slave-updates = 1

# innodb settings #
innodb_page_size = 16384  
# InnoDB缓存池大小
innodb_buffer_pool_size = 4G
# InnoDB缓存池实例数
innodb_buffer_pool_instances = 4
# 在启动时把热数据加载到内存
innodb_buffer_pool_load_at_startup = 1
# 数据库关闭时自动dump数据
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 4096
innodb_lock_wait_timeout = 5
innodb_io_capacity = 10000
innodb_io_capacity_max = 20000
innodb_flush_method = O_DIRECT
innodb_undo_logs = 128
innodb_undo_tablespaces = 0
innodb_flush_neighbors = 0
innodb_log_file_size = 8589934592
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16777216
innodb_purge_threads = 4
innodb_large_prefix = 1
# 并发运行的线程数，设置为0表示不限制
innodb_thread_concurrency = 8
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 67108864
innodb_write_io_threads = 16
innodb_read_io_threads = 16
innodb_file_per_table = 1
innodb_stats_persistent_sample_pages = 64
innodb_autoinc_lock_mode = 2
innodb_online_alter_log_max_size=1G
innodb_open_files=4096

# replication settings #
replicate-do-db = panorama
# 测试机(v5.5) 没这个参数
binlog_checksum = NONE
# master.info保存在表中
#master_info_repository = TABLE 
# relay.info保存在表中
relay_log_info_repository = TABLE
# 当每进行1次事务提交之后，MySQL将进行一次fsync磁盘同步，以此来保证无损复制
sync_binlog = 1
# 启动GTID模式
#gtid_mode = on
# 启动GTID模式
#enforce_gtid_consistency = 1
# 从服务器的更新写入二进制日志,便于主从切换时，从服务器已经开启二进制日志
log_slave_updates
# 以row格式记录binlog
#binlog_format = ROW
binlog_format = STATEMENT
#binlog_rows_query_log_events = 1
relay_log = relay.log
relay_log_index = relay_log.index
# 允许从库宕机后，重新从master上获取日志，保证relay-log的完整性
relay_log_recovery = 1
slave_skip_errors = ddl_exist_errors
#slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'

# semi sync replication settings #
#plugin_load = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
plugin_load = "validate_password.so"
#rpl_semi_sync_master_enabled = 1
#rpl_semi_sync_master_timeout = 3000
#rpl_semi_sync_slave_enabled = 1

# password plugin #
validate_password_policy=MEDIUM
validate-password=FORCE_PLUS_PERMANENT

# new innodb settings #
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_undo_log_truncate = 1
# 当超过这个阀值（默认是1G），会触发truncate回收（收缩）动作，truncate后空间缩小到10M
innodb_max_undo_log_size = 2G
innodb_purge_rseg_truncate_frequency = 128
# new replication settings #
#slave-parallel-type = LOGICAL_CLOCK
#slave-parallel-workers = 4
#slave_preserve_commit_order = 1
#slave_transaction_retries = 128
# other change settings #
binlog_gtid_simple_recovery = 1
log_timestamps = system
show_compatibility_56 = on

[mysql]
prompt = [\\u@\\h][\\d]>\\_
no-auto-rehash
default-character-set = utf8mb4

[mysqldump]
quick

[isamchk]
# 5.7版本部分系统表还在使用MyISAM，结合系统中引擎使用情况适当优化
key_buffer_size = 2G
myisam_sort_buffer_size = 128M

补充

Question: Mysql configuration file sections

Description:
I am trying to understand the different sections inside the my.ini configuration file ([client], [mysqld], [mysql]) and so on, I am looking for a guide describing each of the optional sections for the my.ini file, Also i was wondering what is the difference between init_connect and init-connect and i mean between the underscore(_) and the hyphen(-), Thank you all and have a nice day.

Answer:

[mysql] applies to the mysql command line client - mysql and client options

[client] applies to all connecting clients (including mysql cli) - mysql and client options

[mysqld] applies to the mysql server - server options

[mysqldump] applies to the utility of the same name - mysqldump options

…etc

The difference between the (-) and the (_) is the context in which it is used.

(-) is used in command line parameters, where (_) is used in options file parameters.

You can see more in the docs: http://dev.mysql.com/doc/refman/5.5/en/option-files.html

reference

MySQL
MySQL

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议 ，转载请注明出处！