数据库配置

数据库配置

my.cnf

 1[client]
 2user=root
 3password=111111
 4
 5[mysqld]
 6########basic settings########
 7server-id = 223
 8port = 3306 
 9user = mysql
10# bind_address = 172.16.6.39
11character_set_server=utf8mb4
12skip_name_resolve = 1
13# max_connections = 800
14max_connections = 128
15max_connect_errors = 1000
16datadir = /data/mysql_data 
17transaction_isolation = READ-COMMITTED
18explicit_defaults_for_timestamp = 1
19join_buffer_size = 134217728
20tmp_table_size = 67108864
21tmpdir = /tmp
22max_allowed_packet = 16777216
23sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
24interactive_timeout = 1800
25wait_timeout = 1800
26read_buffer_size = 16777216
27read_rnd_buffer_size = 33554432
28sort_buffer_size = 33554432
29########log settings########
30log_error = error.log
31slow_query_log = 1
32slow_query_log_file = slow.log
33log_queries_not_using_indexes = 1
34log_slow_admin_statements = 1
35log_slow_slave_statements = 1
36log_throttle_queries_not_using_indexes = 10
37expire_logs_days = 90
38long_query_time = 2
39min_examined_row_limit = 100
40########replication settings########
41master_info_repository = TABLE
42relay_log_info_repository = TABLE
43log_bin = bin.log
44sync_binlog = 1
45gtid_mode = on
46enforce_gtid_consistency = 1
47log_slave_updates
48binlog_format = row 
49relay_log = relay.log
50relay_log_recovery = 1
51binlog_gtid_simple_recovery = 1
52slave_skip_errors = ddl_exist_errors
53########innodb settings########
54innodb_page_size = 8192
55innodb_buffer_pool_size = 2G 
56innodb_buffer_pool_instances = 8
57innodb_buffer_pool_load_at_startup = 1
58innodb_buffer_pool_dump_at_shutdown = 1
59innodb_lru_scan_depth = 2000
60innodb_lock_wait_timeout = 5
61innodb_io_capacity = 4000
62innodb_io_capacity_max = 8000
63innodb_flush_method = O_DIRECT
64innodb_file_format = Barracuda
65innodb_file_format_max = Barracuda
66innodb_log_group_home_dir = /data/redolog/ 
67innodb_undo_directory = /data/undolog/     
68innodb_undo_logs = 128
69innodb_undo_tablespaces = 3
70innodb_flush_neighbors = 1
71innodb_log_file_size = 1G          
72innodb_log_buffer_size = 16777216
73innodb_purge_threads = 4
74innodb_large_prefix = 1
75innodb_thread_concurrency = 64
76innodb_print_all_deadlocks = 1
77innodb_strict_mode = 1
78innodb_sort_buffer_size = 67108864 
79########semi sync replication settings########
80plugin_dir=/usr/local/mysql/lib/plugin  
81plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
82loose_rpl_semi_sync_master_enabled = 1
83loose_rpl_semi_sync_slave_enabled = 1
84loose_rpl_semi_sync_master_timeout = 5000
85
86[mysqld-5.7]  
87innodb_buffer_pool_dump_pct = 40
88innodb_page_cleaners = 4
89innodb_undo_log_truncate = 1
90innodb_max_undo_log_size = 1G
91innodb_purge_rseg_truncate_frequency = 128
92binlog_gtid_simple_recovery=1
93log_timestamps=system
94transaction_write_set_extraction=MURMUR32
95show_compatibility_56=on
96
97[mysql-5.8]
98sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO

from

  1
  2###########################################################################
  3## my.cnf for MySQL 8.0.x                                                  
  4## 本配置参考  https://imysql.com/my-cnf-wizard.html                         
  5## 注意:                                                                   
  6##   (1)本配置假设物理服务器内存为 16G,总表数量在300之内,中小型企业业务          
  7##   (2)请根据实际情况作调整部分参数                                          
  8##   (3)本人不对这些建议结果负相应责任 ,仅作参考                             
  9###########################################################################
 10
 11###########################################################################
 12##客户端参数配置
 13###########################################################################
 14[client]
 15port	= 3306
 16socket	=/var/lib/mysql/mysqld.sock
 17
 18[mysql]
 19#prompt="\u@mysqldb \R:\m:\s [\d]> "
 20#关闭自动补全sql命令功能
 21no-auto-rehash
 22
 23###########################################################################
 24##服务端参数配置
 25###########################################################################
 26[mysqld]
 27port	= 3306
 28datadir	= /var/lib/mysql
 29socket	= /var/lib/mysql/mysqld.sock
 30log-error = /var/lib/mysql/error.log
 31pid-file = /var/lib/mysql/mysqld.pid
 32
 33#只能用IP地址检查客户端的登录,不用主机名
 34skip_name_resolve = 1
 35
 36#若你的MySQL数据库主要运行在境外,请务必根据实际情况调整本参数
 37default_time_zone = "+8:00"
 38
 39#数据库默认字符集, 主流字符集支持一些特殊表情符号(特殊表情符占用4个字节)
 40character-set-server = utf8mb4
 41
 42#数据库字符集对应一些排序等规则,注意要和character-set-server对应
 43collation-server = utf8mb4_general_ci
 44
 45#设置client连接mysql时的字符集,防止乱码
 46init_connect='SET NAMES utf8mb4'
 47
 48#是否对sql语句大小写敏感,1表示不敏感
 49lower_case_table_names = 1
 50
 51# 执行sql的模式,规定了sql的安全等级, 暂时屏蔽,my.cnf文件中配置报错
 52#sql_mode = STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 53
 54#事务隔离级别,默认为可重复读,mysql默认可重复读级别(此级别下可能参数很多间隙锁,影响性能)
 55transaction_isolation = READ-COMMITTED
 56
 57#TIMESTAMP如果没有显示声明NOT NULL,允许NULL值
 58explicit_defaults_for_timestamp = true
 59
 60#它控制着mysqld进程能使用的最大文件描述(FD)符数量。
 61#需要注意的是这个变量的值并不一定是你设定的值,mysqld会在系统允许的情况下尽量获取更多的FD数量
 62open_files_limit    = 65535
 63
 64#最大连接数
 65max_connections = 300
 66
 67#最大错误连接数
 68max_connect_errors = 600
 69
 70#在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中
 71#官方建议 back_log = 50 + (max_connections / 5),封顶数为65535,默认值= max_connections
 72back_log = 110
 73
 74# The number of open tables for all threads
 75# For example, for 200 concurrent running connections, specify a table cache size of at least 200 * N, 
 76# where N is the maximum number of tables per join in any of the queries which you execute. 
 77table_open_cache = 600
 78
 79# The number of table definitions that can be stored in the definition cache 
 80# MIN(400 + table_open_cache / 2, 2000)
 81table_definition_cache = 700
 82
 83# 为了减少会话之间的争用,可以将opentables缓存划分为table_open_cache/table_open_cache_instances个小缓存
 84table_open_cache_instances = 64
 85
 86# 每个线程的堆栈大小 如果线程堆栈太小,则会限制执行复杂SQL语句
 87thread_stack = 512K
 88
 89# 禁止外部系统锁
 90external-locking = FALSE
 91
 92#SQL数据包发送的大小,如果有BLOB对象建议修改成1G
 93max_allowed_packet = 128M
 94
 95#order by 或group by 时用到
 96#建议先调整为4M,后期观察调整
 97sort_buffer_size = 4M
 98
 99#inner left right join时用到
100#建议先调整为4M,后期观察调整
101join_buffer_size = 4M
102
103# How many threads the server should cache for reuse.
104# 如果您的服务器每秒达到数百个连接,则通常应将thread_cache_size设置得足够高,以便大多数新连接使用缓存线程
105# default value = 8 + ( max_connections / 100) 上限为100
106thread_cache_size = 20
107
108#MySQL连接闲置超过一定时间后(单位:秒)将会被强行关闭
109#MySQL默认的wait_timeout  值为8个小时, interactive_timeout参数需要同时配置才能生效
110interactive_timeout = 1800
111wait_timeout = 1800
112
113#Metadata Lock最大时长(秒), 一般用于控制 alter操作的最大时长sine mysql5.6
114#执行 DML操作时除了增加innodb事务锁外还增加Metadata Lock,其他alter(DDL)session将阻塞
115lock_wait_timeout = 3600
116
117#内部内存临时表的最大值。
118#比如大数据量的group by ,order by时可能用到临时表,
119#超过了这个值将写入磁盘,系统IO压力增大
120tmp_table_size = 64M
121max_heap_table_size = 64M
122
123#--###########################-- 慢SQL日志记录 开始 --##########################################
124
125#是否启用慢查询日志,1为启用,0为禁用  
126slow_query_log = 1
127
128#记录系统时区
129log_timestamps = SYSTEM
130
131#指定慢查询日志文件的路径和名字
132slow_query_log_file = /var/lib/mysql/slow.log
133
134#慢查询执行的秒数,必须达到此值可被记录
135long_query_time = 5
136
137#将没有使用索引的语句记录到慢查询日志  
138log_queries_not_using_indexes = 0
139
140#设定每分钟记录到日志的未使用索引的语句数目,超过这个数目后只记录语句数量和花费的总时间  
141log_throttle_queries_not_using_indexes = 60
142
143#对于查询扫描行数小于此参数的SQL,将不会记录到慢查询日志中
144min_examined_row_limit = 5000
145
146#记录执行缓慢的管理SQL,如alter table,analyze table, check table, create index, drop index, optimize table, repair table等。  
147log_slow_admin_statements = 0
148
149#作为从库时生效, 从库复制中如何有慢sql也将被记录
150#对于ROW格式binlog,不管执行时间有没有超过阈值,都不会写入到从库的慢查询日志
151log_slow_slave_statements = 1
152
153#--###########################-- 慢SQL日志记录 结束 --##########################################
154
155#--###########################-- Bin-Log设置 开始 --############################################
156server-id = 110
157
158#开启bin log 功能
159log-bin=mysql-bin
160
161#binlog 记录内容的方式,记录被操作的每一行
162binlog_format = ROW
163
164#对于binlog_format = ROW模式时,FULL模式可以用于误操作后的flashBack。
165#如果设置为MINIMAL,则会减少记录日志的内容,只记录受影响的列,但对于部分update无法flashBack
166binlog_row_image = FULL
167
168#bin log日志保存的天数
169#如果 binlog_expire_logs_seconds 选项也存在则 expire_logs_days 选项无效
170#expire_logs_days 已经被标注为过期参数
171#expire_logs_days = 7
172binlog_expire_logs_seconds = 1209600
173
174#master status and connection information输出到表mysql.slave_master_info中
175master_info_repository = TABLE
176
177#the slave's position in the relay logs输出到表mysql.slave_relay_log_info中
178relay_log_info_repository = TABLE
179
180#作为从库时生效, 想进行级联复制,则需要此参数
181log_slave_updates
182
183#作为从库时生效, 中继日志relay-log可以自我修复
184relay_log_recovery = 1
185
186#作为从库时生效, 主从复制时忽略的错误
187#如果在备份过程中执行ddl操作,从机需要从主机的备份恢复时可能会异常,从而导致从机同步数据失败
188#如果对数据完整性要求不是很严格,那么这个选项确实可以减轻维护的成本
189slave_skip_errors = ddl_exist_errors
190
191#####RedoLog日志 和 binlog日志的写磁盘频率设置 BEGIN ###################################
192# RedoLog日志(用于增删改事务操作) +  binlog日志(用于归档,主从复制)
193# 为什么会有两份日志呢? 
194# 因为最开始MySQL没有 InnoDB 引擎,自带MyISAM引擎没有 crash-safe能力,binlog日志只用于归档
195# InnoDB 引擎是另一个公司以插件形式引入MySQL的,采用RedoLog日志来实现 crash-safe 能力
196
197# redo log 的写入(即事务操作)拆成两阶段提交(2PC):prepare阶段 和 commit阶段
198#(事务步骤1) 执行commit命令,InnoDB redo log 写盘,然后告知Mysql执行器:[你可以写binlog了,且一并提交事务],事务进入 prepare 状态
199#(事务步骤2) 如果前面 prepare 成功,Mysql执行器生成 binlog 并且将binlog日志写盘
200#(事务步骤3) 如果binlog写盘成功,Mysql执行器一并调用InnoDB引擎的提交事务接口,事务进入 commit 状态,操作完成,事务结束
201
202#参数设置成 1,每次事务都直接持久化到磁盘
203#参数设置成 0,mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
204#参数设置成 2,只有在操作系统崩溃或者系统掉电的情况下,上一秒钟所有事务数据才可能丢失。
205#即便都设置为1,服务崩溃或者服务器主机crash,Mysql也可能丢失但最多一个事务
206
207#控制 redolog 写磁盘频率 默认为1
208innodb_flush_log_at_trx_commit = 1
209
210#控制 binlog 写磁盘频率
211sync_binlog = 1
212
213#####RedoLog日志 和 binlog日志的写磁盘频率设置 END #####################################
214
215#一般数据库中没什么大的事务,设成1~2M,默认32kb
216binlog_cache_size = 4M
217
218#binlog 能够使用的最大cache 内存大小
219max_binlog_cache_size = 2G
220
221#单个binlog 文件大小 默认值是1GB
222max_binlog_size = 1G
223
224#开启GTID复制模式
225gtid_mode = on
226
227#强制gtid一致性,开启后对于create table ... select ...或 CREATE TEMPORARY TABLE 将不被支持
228enforce_gtid_consistency = 1
229
230#解决部分无主键表导致的从库复制延迟问题
231#其基本思路是对于在一个ROWS EVENT中的所有前镜像收集起来,
232#然后在一次扫描全表时,判断HASH中的每一条记录进行更新
233#该参数已经被标注为过期参数
234#slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
235
236# default value is CRC32
237#binlog_checksum = 1
238
239# default value is ON
240#relay-log-purge = 1
241
242#--###########################-- Bin-Log设置 结束 --##########################################
243
244#--###########################-- 可能用到的MyISAM性能设置 开始 --#############################
245
246#对MyISAM表起作用,但是内部的临时磁盘表是MyISAM表,也要使用该值。
247#可以使用检查状态值 created_tmp_disk_tables 得知详情
248key_buffer_size = 15M
249
250#对MyISAM表起作用,但是内部的临时磁盘表是MyISAM表,也要使用该值,
251#例如大表order by、缓存嵌套查询、大容量插入分区。
252read_buffer_size = 8M
253
254#对MyISAM表起作用 读取优化
255read_rnd_buffer_size = 4M
256
257#对MyISAM表起作用 插入优化
258bulk_insert_buffer_size = 64M
259#--###########################-- 可能用到的MyISAM性能设置 开始 --################################
260
261#--###########################-- innodb性能设置 开始 --##########################################
262# Defines the maximum number of threads permitted inside of InnoDB. 
263# A value of 0 (the default) is interpreted as infinite concurrency (no limit)
264innodb_thread_concurrency = 0
265
266#一般设置物理存储的 60% ~ 70%
267innodb_buffer_pool_size = 8G
268
269#当缓冲池大小大于1GB时,将innodb_buffer_pool_instances设置为大于1的值,可以提高繁忙服务器的可伸缩性
270innodb_buffer_pool_instances = 4
271
272#默认启用。指定在MySQL服务器启动时,InnoDB缓冲池通过加载之前保存的相同页面自动预热。 通常与innodb_buffer_pool_dump_at_shutdown结合使用
273innodb_buffer_pool_load_at_startup = 1
274
275#默认启用。指定在MySQL服务器关闭时是否记录在InnoDB缓冲池中缓存的页面,以便在下次重新启动时缩短预热过程
276innodb_buffer_pool_dump_at_shutdown = 1
277
278# Defines the name, size, and attributes of InnoDB system tablespace data files
279innodb_data_file_path = ibdata1:1G:autoextend
280
281#InnoDB用于写入磁盘日志文件的缓冲区大小(以字节为单位)。默认值为16MB
282innodb_log_buffer_size = 32M
283
284#InnoDB日志文件组数量
285innodb_log_files_in_group = 3
286
287#InnoDB日志文件组中每一个文件的大小
288innodb_log_file_size = 2G
289
290#是否开启在线回收(收缩)undo log日志文件,支持动态设置,默认开启
291innodb_undo_log_truncate = 1
292
293#当超过这个阀值(默认是1G),会触发truncate回收(收缩)动作,truncate后空间缩小到10M
294innodb_max_undo_log_size = 4G
295
296#The path where InnoDB creates undo tablespaces
297#没有配置则在数据文件目录下
298#innodb_undo_directory = /var/lib/mysql/undolog
299
300#用于设定创建的undo表空间的个数
301#已经弃用了,只能手动添加undo表空间
302#The innodb_undo_tablespaces variable is deprecated and is no longer configurable as of MySQL 8.0.14
303#innodb_undo_tablespaces = 95
304
305#提高刷新脏页数量和合并插入数量,改善磁盘I/O处理能力
306#根据您的服务器IOPS能力适当调整
307#一般配普通SSD盘的话,可以调整到 10000 - 20000
308#配置高端PCIe SSD卡的话,则可以调整的更高,比如 50000 - 80000
309innodb_io_capacity = 4000
310innodb_io_capacity_max = 8000
311
312#如果打开参数innodb_flush_sync, checkpoint时,flush操作将由page cleaner线程来完成,此时page cleaner会忽略io capacity的限制,进入激烈刷脏
313innodb_flush_sync = 0
314innodb_flush_neighbors = 0
315
316#CPU多核处理能力设置,假设CPU是4颗8核的,设置如下
317#读多,写少可以设成 2:6的比例
318innodb_write_io_threads = 8
319innodb_read_io_threads = 8
320innodb_purge_threads = 4
321innodb_page_cleaners = 4
322innodb_open_files = 65535
323innodb_max_dirty_pages_pct = 50
324
325#该参数针对unix、linux,window上直接注释该参数.默认值为 NULL
326#O_DIRECT减少操作系统级别VFS的缓存和Innodb本身的buffer缓存之间的冲突
327innodb_flush_method = O_DIRECT
328
329innodb_lru_scan_depth = 4000
330innodb_checksum_algorithm = crc32
331
332#为了获取被锁定的资源最大等待时间,默认50秒,超过该时间会报如下错误:
333# ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
334innodb_lock_wait_timeout = 20
335
336#默认OFF,如果事务因为加锁超时,会回滚上一条语句执行的操作。如果设置ON,则整个事务都会回滚
337innodb_rollback_on_timeout = 1
338
339#强所有发生的死锁错误信息记录到 error.log中,之前通过命令行只能查看最近一次死锁信息
340innodb_print_all_deadlocks = 1
341
342#在创建InnoDB索引时用于指定对数据排序的排序缓冲区的大小
343innodb_sort_buffer_size = 67108864
344
345#控制着在向有auto_increment 列的表插入数据时,相关锁的行为,默认为2
346#0:traditonal (每次都会产生表锁)
347#1:consecutive (mysql的默认模式,会产生一个轻量锁,simple insert会获得批量的锁,保证连续插入)
348#2:interleaved (不会锁表,来一个处理一个,并发最高)
349innodb_autoinc_lock_mode = 1
350
351#表示每个表都有自已独立的表空间
352innodb_file_per_table = 1
353
354#指定Online DDL执行期间产生临时日志文件的最大大小,单位字节,默认大小为128MB。
355#日志文件记录的是表在DDL期间的数据插入、更新和删除信息(DML操作),一旦日志文件超过该参数指定值时,
356#DDL执行就会失败并回滚所有未提交的当前DML操作,所以,当执行DDL期间有大量DML操作时可以提高该参数值,
357#但同时也会增加DDL执行完成时应用日志时锁定表的时间
358innodb_online_alter_log_max_size = 4G
359
360#--###########################-- innodb性能设置 结束 --##########################################
361
362[mysqldump]
363quick
364max_allowed_packet = 128M