# MySQL常用配置

MySQL配置参数多达数百个,不过常用的并不多,很多配置我们不需要关心,只要掌握一些必要的配置项就能解决99%的问题,当遇见了那1%的问题时mysql可能已经不能满足你的需求了。

这里推荐一个在线生成配置文件的网址:https://imysql.com/my-cnf-wizard.html,我们只需要做出一些场景描述就可以得到一个配置文件,再根据具体的场景做适当的修改就行了。

#
## my.cnf for MySQL 5.7/8.0
## 本配置文件主要适用于MySQL 5.7/8.0版本
#
[client]
port	= 3306
socket	= /data/mysql/mysql.sock
[mysqld]
user	= mysql
port	= 3306
basedir	= /usr/local/mysql
datadir	= /data/mysql
socket	= /data/mysql/mysql.sock
pid-file = mysqldb.pid
character-set-server = utf8mb4
skip_name_resolve = 1
#若你的MySQL数据库主要运行在境外,请务必根据实际情况调整本参数
default_time_zone = "+8:00"
open_files_limit    = 65535
back_log = 1024
max_connections = 512
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
table_open_cache_instances = 64
th_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 768
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M
slow_query_log = 1
log_timestamps = SYSTEM
slow_query_log_file = /data/mysql/slow.log
log-error = /data/mysql/error.log
long_query_time = 0.1
log_queries_not_using_indexes =1
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 100
log_slow_admin_statements = 1
log_slow_slave_statements = 1
server-id = 3306
log-bin = /data/mysql/mybinlog
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
#注意:MySQL 8.0开始,binlog_expire_logs_seconds选项也存在的话,会忽略expire_logs_days选项
expire_logs_days = 7
master_info_repository = TABLE
relay_log_info_repository = TABLE
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
binlog_format = row
binlog_checksum = 1
relay_log_recovery = 1
relay-log-purge = 1
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
lock_wait_timeout = 3600
explicit_defaults_for_timestamp = 1
innodb_thread_concurrency = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30
transaction_isolation = REPEATABLE-READ
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 22938M
innodb_buffer_pool_instances = 4
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_max_undo_log_size = 4G
innodb_undo_directory = /data/mysql/undolog
innodb_undo_tablespaces = 95
# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话,可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话,则可以调整的更高,比如 50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_sync = 0
innodb_flush_neighbors = 0
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
innodb_stats_on_metadata = 0
innodb_undo_log_truncate = 1
# some var for MySQL 5.7
innodb_checksums = 1
#innodb_file_format = Barracuda
#innodb_file_format_max = Barracuda
query_cache_size = 0
query_cache_type = 0
innodb_undo_logs = 128
#注意:MySQL 8.0.16开始删除该选项
internal_tmp_disk_storage_engine = InnoDB
innodb_status_file = 1
#注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快
innodb_status_output = 0
innodb_status_output_locks = 0
#performance_schema
performance_schema = 1
performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'
#innodb monitor
innodb_monitor_enable="module_innodb"
innodb_monitor_enable="module_server"
innodb_monitor_enable="module_dml"
innodb_monitor_enable="module_ddl"
innodb_monitor_enable="module_trx"
innodb_monitor_enable="module_os"
innodb_monitor_enable="module_purge"
innodb_monitor_enable="module_log"
innodb_monitor_enable="module_lock"
innodb_monitor_enable="module_buffer"
innodb_monitor_enable="module_index"
innodb_monitor_enable="module_ibuf_system"
innodb_monitor_enable="module_buffer_page"
innodb_monitor_enable="module_adaptive_hash"
[mysqldump]
quick
max_allowed_packet = 32M
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

# client配置项

该配置项常用在我们需要通过mysql命令行来执行一些初始安装操作。

[client]
# mysql命令行默认的连接端口,也可以通过-p参数来指定端口
port	= 3306
# mysql命令行默认连接的mysqld服务的socket通信文件
# 如果没有配置该且在我们修改了mysqld的socket通信文件的默认位置,则需要通过-S参数指定该文件
# 也可使通过-h来指定要连接的ip地址
socket	= /data/mysql/mysql.sock
# mysql命令行默认使用的用户名
user=root
# root用户的密码
password=密码
1
2
3
4
5
6
7
8
9
10
11
12

# mysqld配置项

# 基本配置

# 启动mysql服务的系统账户,开发环境通常为root
user	= root
# mysql服务的端口
port	= 3306
# mysql的位置
basedir	= /usr/local/mysql
# mysql数据目录的位置
datadir	= /data/mysql
# mysql服务的通信文件
socket	= /data/mysql/mysql.sock
# mysql服务启动后的进程id
pid-file = mysqldb.pid
# 是否dns解析,大型项目架构中通常会有一个内部dns服务器,此时设置为0合适,如果设置为1就只能使用ip地址了。
skip_name_resolve = 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 字符集配置

# utf8mb4是utf8的超级,支持4字节的unicode,缺点是比utf8占用空间多点,最好设置为utf8mb4吧
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
1
2
3

# sql语法/行为配置

# 表名忽略大小写
lower_case_table_names=1
# 语法限制,一般不设置,常用的就是only full group by
# sql_mode=
1
2
3
4

# 连接优化配置

# tcp连接缓冲池大小
back_log = 500
# 最大连接数
max_connections=3000
# 每个用户的最大连接数,如果只有一个用户可以设置为0,或者小于等于max_connections
max_user_connections=500
# 最大并行连接数
max_used_connections=1000
# 最大连接失败次数,超过则禁止连接,直到重启或执行flush hosts命令
max_connect_errors = 6000
1
2
3
4
5
6
7
8
9
10

# 查询缓存优化配置

# 排序,分组
sort_buffer_size = 4M
# 连接查询
join_buffer_size = 4M
# 索引占用的内存代下
key_buffer_size = 2048M
# 每个连接全表扫描时的缓冲区大小
read_buffer_size = 8M
# 每个连接随机扫描的缓冲区大小
read_rnd_buffer_size = 4M
# 不限制大小,由mysql自己决定
query_cache_size=0
# 开启缓存
query_cache_type=1
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 事务配置

# 修改默认事务级别
transaction_isolation=READ-COMMITTED
1
2

# 线程优化

thread_cache_size = 768
1