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
188
189
190
191
192
193
194
195
196
197
198
| # For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
## 基础设置 ##
#设置一个服务ID
server-id=1
#端口
port=3306
# mysql进程用户
user=mysql
#basedir=/usr/loca/mysql
#数据存储目录
datadir=/cloud.yunzhankeji/mysql/data
#server和client在同一台服务器,并且使用localhost进行链接的时候,就会使用socket来进行连接.
socket=/var/lib/mysql/mysql.sock
#PID存放位置
pid-file=/cloud.yunzhankeji/mysql/data/mysqld.pid
#临时文件
tmpdir=/cloud.yunzhankeji/mysql/tmp
#back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。当前系统4096
back_log=3072
#禁用符号链接,防止安全风险.
symbolic-links=0
#对同一主机超过以下设置的参数值,则禁止连接
max_connect_errors=30
#禁用DNS解析
skip_name_resolve=1
#最大数据包大小
max_allowed_packet=64M
#编码方式
character_set_server=utf8mb4
#初始链接时 设置
init_connect='SET NAMES utf8mb4'
#字符集排序规则
collation-server=utf8mb4_general_ci
#最大连接数
max_connections=8000
#所有线程能打开的表的数量。
table_open_cache=5120M
#每个数据库最大连接数
max_user_connections=800
#限制连接时间
wait_timeout=1800
#sql语句大小写敏感
lower_case_table_names=1
#因在内网.绑定IP,暂不做设置
#bind-address=172.22.14.71
#禁止复制进程随着数据库的启动而启动
skip-slave-start
#服务器线程缓存
thread_cache_size=64
#查询缓存
query_cache_type=1
query_cache_size=256M
#临时表占内存
tmp_table_size=5120M
#读入缓存
read_buffer_size=16M
#排序缓存
sort_buffer_size=16M
#每个客户端ORDER BY 缓存
read_rnd_buffer_size=16M
#表描述符缓存大小，可减少文件打开/关闭次数
table_open_cache=5120M
#查询块最小内存快
query_cache_min_res_unit=128M
#索引块缓存区大小
key_buffer_size=8G
key_cache_block_size=1M
## 日志相关 ##
#错误日志
log-error=/cloud.yunzhankeji/mysql/log/mysql3306-error.log
#开启慢查询日志
slow_query_log=1
#超过100则认为是慢查询
long_query_time=2
#查询检查返回少于该参数指定行的SQL不被记录到慢查询日志
min_examined_row_limit=100
# 慢查询日志目录
slow_query_log_file=/cloud.yunzhankeji/mysql/log/mysql-slow.log
#将没有使用索引的语句记录到慢查询日志
log_queries_not_using_indexes=1
#设定每分钟记录到日志的未使用索引的语句数目，超过这个数目后只记录语句数量和花费的总时间
log-slow-admin-statements=1
#记录从库上的慢查询
log_slow_slave_statements=0
#日志时间差异
log_timestamps=system
#开启bin-log日志,主要用来使用主从配置
log-bin=/coud.yunzhankeji/mysql/log/mysql3306-bin
binlog_rows_query_log_events=1
binlog_format=ROW
binlog_rows_query_log_events=1
binlog_gtid_simple_recovery=1
#不开启二进制日志文件的数据库
binlog-ignore-db=information_schema
#不启二进制日志文件的数据库
binlog-ignore-db=cluster
#不启二进制日志文件的数据库
binlog-ignore-db=mysql
#日志过期时间 设置为0永不过期
expire_logs_days=30
#超过max_binlog_size或超过6小时会切换到下一序号文件
max_binlog_size=500M
sync_binlog=0
sync_relay_log=0
log-bin-trust-function-creators=1
super_read_only=0
secure_file_priv=
#设置mode
#STRICT_TRANS_TABLES 严格模式,进行数据的严格校验,错误数据不能插入,报error错误.
#NO_ZERO_IN_DATE 在严格模式下,不允许日期和月份为零.
#NO_ZERO_DATE 不允许插入零日期,插入零日期会抛出错误而不是警告.
#ERROR_FOR_DIVISION_BY_ZERO 在INSERT或UPDATE过程中,如果数据被零除,则产生错误而非警告.如 果未给出该模式,那么数据被零除时MySQL返回NULL.
#NO_AUTO_CREATE_USER 禁止GRANT创建密码为空的用户
#NO_ENGINE_SUBSTITUTION 如果需要的存储引擎被禁用或未编译,那么抛出错误.不设置此值时,用默认的存储引擎替代,并抛出一个异常.
#PIPES_AS_CONCAT 将"||"视为字符串的连接操作符而非或运算符,这和Oracle数据库是一样的,也和字符串的拼接函数Concat相类似
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT
##innodb配置
innodb_lru_scan_depth=2000
innodb_buffer_pool_size = 25G
innodb_buffer_pool_instances = 8
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
#重做相位恢复 文件大小
innodb_log_file_size = 1024M
innodb_log_files_in_group = 3
innodb_lock_wait_timeout = 10
innodb_io_capacity = 4000
innodb_io_capacity_max = 65536
innodb_log_group_home_dir=/cloud.yunzhankeji/mysql/redo
innodb_data_home_dir = /cloud.yunzhankeji/mysql/data
innodb_undo_directory=/cloud.yunzhankeji/mysql/undo
innodb_undo_tablespaces=3
innodb_undo_logs=128
innodb_max_undo_log_size=1024M
innodb_purge_rseg_truncate_frequency
innodb_undo_log_truncate=1
innodb_file_format = Barracuda # is deprecated and will be removed
innodb_file_format_max = Barracuda # is deprecated and will be removed
#允许单列索引最大达到3072.否则最大为767
innodb_large_prefix=1
innodb_thread_concurrency=128
#死锁记录到error_log中
innodb_print_all_deadlocks=1
innodb_sort_buffer_size=16M
#重做相位恢复 缓存大小
innodb_log_buffer_size=64M
##replication settings
#master_info_repository = TABLE
#relay_log_info_repository = TABLE
#sync_relay_log_info = 1
#sync_master_info = 1
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
relay_log_recovery=1
slave_skip_errors = ddl_exist_errors
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 8
slave_pending_jobs_size_max=128M
skip-slave-start
event_scheduler=ON
#relay-log=/dtwb/mysql/log/mysql-relay-bin
#relay-log-index=/dtwb/mysql/log/mysql-relay-bin.index
#master-info-file=/dtwb/mysql/log/mysql-master.info
#relay-log-info-file=/dtwb/mysql/log/mysql-relay-log.info
|