为了配置MRG省几台机器，打算使用多实例，捎带整理下文档，以备查阅。

环境说明

主机: Centos6.5
MySQL: 5.7.18
安装方式:二进制 
安装包:/data/src 
安装位置: /user/local/mysql

解压

1
2
tar zxvf /data/src/mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz  -C /usr/local/
cd /usr/local/;mv mysql-5.7.18-linux-glibc2.5-x86_64/  mysql

创建目录结构

1
2
3
4
mkdir -p /var/run/mysqld  ;
mkdir -p /usr/local/mysql/data330{6,7,8} ;
chmod 750 /var/run/mysqld /usr/local/mysql/data330*;
chown -R mysql:mysql /usr/local/mysql/ /var/run/mysqld ;

准备配置文件(精简版，完整版在后面)

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
[mysqld_multi]  
mysqld     = /usr/local/mysql/bin/mysqld_safe  
mysqladmin = /usr/local/mysql/bin/mysqladmin  
#user       = root  
#pass  = 123456  
 
[mysqld3306]    
port = 3306  
server_id = 3306  
basedir =/usr/local/mysql  
datadir =/usr/local/mysql/data3306  
socket =/tmp/mysql3306.sock    
log-error =/var/log/mysqld3306.log  
pid-file =/var/run/mysqld/mysqld3306.pid  
  
[mysqld3307]    
port = 3307  
server_id = 3307  
basedir =/usr/local/mysql  
datadir =/usr/local/mysql/data3307  
socket =/tmp/mysql3307.sock    
log-error =/var/log/mysqld3307.log  
pid-file =/var/run/mysqld/mysqld3307.pid  

[mysqld3308]    
port = 3308  
server_id = 3308  
basedir =/usr/local/mysql  
datadir =/usr/local/mysql/data3308
socket =/tmp/mysql3308.sock    
log-error =/var/log/mysqld3308.log  
pid-file =/var/run/mysqld/mysqld3308.pid

初始化

1
2
3
4
cd /usr/local/mysql;
bin/mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data3306 --explicit_defaults_for_timestamp
bin/mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data3307 --explicit_defaults_for_timestamp
bin/mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data3308 --explicit_defaults_for_timestamp

生成的随机密码
3306 –>  7rp6k2)QdOHM
3307–>   :MWTA=)vQ8re
3308–>   qONzkgy)o31p   

测试

1
2
3
4
5
6
[root@localhost mysql]mysqld_multi start;
[root@localhost mysql]mysqld_multi report; 
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running

修改密码

1
2
3
mysqladmin -uroot -p"7rp6k2)QdOHM"  password "123456" -S /tmp/mysql3306.sock    
mysqladmin -uroot -p":MWTA=)vQ8re"  password "123456" -S /tmp/mysql3307.sock 
mysqladmin -uroot -p"qONzkgy)o31p"  password "123456" -S /tmp/mysql3308.sock

重启实例

1
2
killall mysqld
ps -ef|grep mysqld 

确认没有运行实例后修改 my.cnf ,将密码写入配置文件

1
2
user = root  
pass = 123456 

再次测试

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
[root@localhost mysql]mysqld_multi start;
[root@localhost mysql]mysqld_multi report;
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
[root@localhost mysql]# mysqld_multi stop
[root@localhost mysql]# mysqld_multi report 
Reporting MySQL servers
MySQL server from group: mysqld3306 is not running
MySQL server from group: mysqld3307 is not running
MySQL server from group: mysqld3308 is not running
[root@localhost mysql]# mysqld_multi start 3306
[root@localhost mysql]# mysqld_multi report 
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is not running
MySQL server from group: mysqld3308 is not running

[root@localhost mysql]# mysqld_multi stop 3306 
[root@localhost mysql]# mysqld_multi report   
Reporting MySQL servers
MySQL server from group: mysqld3306 is not running
MySQL server from group: mysqld3307 is not running
MySQL server from group: mysqld3308 is not running

[root@localhost log]# netstat  -nltp|grep 33
tcp        0      0 :::3306                     :::*                        LISTEN      14990/mysqld        
tcp        0      0 :::3307                     :::*                        LISTEN      3263/mysqld         
tcp        0      0 :::3308                     :::*                        LISTEN      7372/mysqld

完整 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
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
[mysqld_multi]  
mysqld     = /usr/local/mysql/bin/mysqld_safe  
mysqladmin = /usr/local/mysql/bin/mysqladmin  
#user = root  
#pass = 123456 
  
#demo,this is a simple case!  
#[mysqld3306]    
#port = 3306  
#server_id = 3306  
#basedir =/usr/local/mysql  
#datadir =/usr/local/mysql/data3306  
#socket =/tmp/mysql3306.sock    
#log-error =/var/log/mysqld3306.log  
#pid-file =/var/run/mysqld/mysqld3306.pid  


[mysqld3306]  
#
#*basic
#  
port = 3306 
server_id = 3306 
basedir =/usr/local/mysql  
datadir =/usr/local/mysql/data3306
socket =/tmp/mysql3306.sock    
pid-file =/var/run/mysqld/mysqld3306.pid  
transaction_isolation = READ-COMMITTED
sql_mode=''

#
#*undo
#
innodb_undo_log_truncate=1 
innodb_undo_tablespaces=3

#
# * log
#
slow_query_log=1 
slow_query_log_file = /var/log/slow3306.log
long_query_time=5
log-error =/var/log/mysqld3306.log  

#
#* sys
#
connect_timeout = 3600
default_time_zone = SYSTEM
log_queries_not_using_indexes = OFF
max_allowed_packet = 124M
max_connect_errors = 100
max_length_for_sort_data = 1024
max_prepared_stmt_count = 16382
max_write_lock_count = 102400
net_read_timeout = 30
net_retry_count = 10
net_write_timeout = 60
open_files_limit = 65535
query_prealloc_size = 16384
slow_launch_time = 2
#400 + (table_open_cache / 2)
table_definition_cache = 1500
table_open_cache = 2200
wait_timeout = 86400
sort_buffer_size=10M
join_buffer_size=2M
max_heap_table_size = 64M
#tmp_table_size = 200M
key_buffer_size = 8388600
myisam_sort_buffer_size = 262144
innodb_sort_buffer_size=3M

#
# * binlog
#
log_bin=mysql-bin
log-bin-index=mysql-bin.index
binlog-format=row
expire_logs_days=0
max_binlog_size=700M
binlog_cache_size = 256KB
binlog_checksum = CRC32
binlog_row_image = full
binlog_stmt_cache_size = 32768
sync_binlog=1

#
# * innodb
#
default-storage-engine=InnoDB
default_tmp_storage_engine=InnoDB
character_set_server = utf8
innodb_file_per_table=1
innodb_flush_log_at_trx_commit = 1
innodb_data_file_path = ibdata1:12m:autoextend
#80%MEM
#innodb_buffer_pool_size = 200m
innodb_thread_concurrency = 0
#innodb_default_row_format=dynamic
#innodb_log_buffer_size = 16M
#innodb_log_file_size = 800M
innodb_log_files_in_group = 4
innodb_adaptive_hash_index = ON
innodb_max_dirty_pages_pct = 75
innodb_old_blocks_pct = 37
innodb_open_files = 3000
#default value was changed from 1 to 4 in MySQL 5.7.8.
innodb_purge_threads = 4
innodb_stats_on_metadata = OFF
innodb_stats_transient_sample_pages = 15
innodb_strict_mode = ON
innodb_table_locks = ON
innodb_thread_sleep_delay = 10000
innodb_write_io_threads = 4
#the server waits for activity on an interactive connection before closing it 
interactive_timeout = 7200
innodb_flush_method = O_DIRECT
relay_log_info_repository=TABLE  
master_info_repository=TABLE  
relay_log_recovery=on  

#
#* QC
#
query_cache_type = 0
query_alloc_block_size = 8192
query_cache_limit = 1048576
query_cache_size = 3145728

#
#* replication
#
gtid_mode=on
#>5,7,6 enum(OFF,ON,WARN)
enforce_gtid_consistency=on
relay-log=mysql-relay-bin
relay-log-index=mysql-relay-bin.index
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
log_bin_trust_function_creators=1
 
#----------------------------------------------------------------------------------

[mysqld3307]  
#
#*basic
#  
port = 3307  
server_id = 3307  
basedir =/usr/local/mysql  
datadir =/usr/local/mysql/data3307 
socket =/tmp/mysql3307.sock    
pid-file =/var/run/mysqld/mysqld3307.pid  
transaction_isolation = READ-COMMITTED
sql_mode=''

#
#*undo
#
innodb_undo_log_truncate=1 
innodb_undo_tablespaces=3 

#
# * log
#
slow_query_log=1 
slow_query_log_file = /var/log/slow3307.log
long_query_time=5
log-error =/var/log/mysqld3307.log  

#
#* sys
#
connect_timeout = 3600
default_time_zone = SYSTEM
log_queries_not_using_indexes = OFF
max_allowed_packet = 124M
max_connect_errors = 100
max_length_for_sort_data = 1024
max_prepared_stmt_count = 16382
max_write_lock_count = 102400
net_read_timeout = 30
net_retry_count = 10
net_write_timeout = 60
open_files_limit = 65535
query_prealloc_size = 16384
slow_launch_time = 2
#400 + (table_open_cache / 2)
table_definition_cache = 1500
table_open_cache = 2200
wait_timeout = 86400
sort_buffer_size=10M
join_buffer_size=2M
max_heap_table_size = 64M
#tmp_table_size = 200M
key_buffer_size = 8388600
myisam_sort_buffer_size = 262144
innodb_sort_buffer_size=3M

#
# * binlog
#
log_bin=mysql-bin
log-bin-index=mysql-bin.index
binlog-format=row
expire_logs_days=0
max_binlog_size=700M
binlog_cache_size = 256KB
binlog_checksum = CRC32
binlog_row_image = full
binlog_stmt_cache_size = 32768
sync_binlog=1

#
# * innodb
#
default-storage-engine=InnoDB
default_tmp_storage_engine=InnoDB
character_set_server = utf8
innodb_file_per_table=1
innodb_flush_log_at_trx_commit = 1
innodb_data_file_path = ibdata1:12m:autoextend
#80%MEM
#innodb_buffer_pool_size = 200m
innodb_thread_concurrency = 0
#innodb_default_row_format=dynamic
#innodb_log_buffer_size = 16M
#innodb_log_file_size = 800M
innodb_log_files_in_group = 4
innodb_adaptive_hash_index = ON
innodb_max_dirty_pages_pct = 75
innodb_old_blocks_pct = 37
innodb_open_files = 3000
#default value was changed from 1 to 4 in MySQL 5.7.8.
innodb_purge_threads = 4
innodb_stats_on_metadata = OFF
innodb_stats_transient_sample_pages = 15
innodb_strict_mode = ON
innodb_table_locks = ON
innodb_thread_sleep_delay = 10000
innodb_write_io_threads = 4
#the server waits for activity on an interactive connection before closing it 
interactive_timeout = 7200
innodb_flush_method = O_DIRECT
relay_log_info_repository=TABLE  
master_info_repository=TABLE  
relay_log_recovery=on  

#
#* QC
#
query_cache_type = 0
query_alloc_block_size = 8192
query_cache_limit = 1048576
query_cache_size = 3145728

#
#* replication
#
gtid_mode=on
#>5,7,6 enum(OFF,ON,WARN)
enforce_gtid_consistency=on
relay-log=mysql-relay-bin
relay-log-index=mysql-relay-bin.index
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
log_bin_trust_function_creators=1


#------------------------------------------------------------------------
[mysqld3308]  
#
#*basic
#  
port = 3308  
server_id = 3308  
basedir =/usr/local/mysql  
datadir =/usr/local/mysql/data3308 
socket =/tmp/mysql3308.sock    
pid-file =/var/run/mysqld/mysqld3308.pid  
transaction_isolation = READ-COMMITTED
sql_mode=''

#
#*undo
#
innodb_undo_log_truncate=1 
innodb_undo_tablespaces=3

#
# * log
#
slow_query_log=1 
slow_query_log_file = /var/log/slow3308.log
long_query_time=5
log-error =/var/log/mysqld3308.log  

#
#* sys
#
connect_timeout = 3600
default_time_zone = SYSTEM
log_queries_not_using_indexes = OFF
max_allowed_packet = 124M
max_connect_errors = 100
max_length_for_sort_data = 1024
max_prepared_stmt_count = 16382
max_write_lock_count = 102400
net_read_timeout = 30
net_retry_count = 10
net_write_timeout = 60
open_files_limit = 65535
query_prealloc_size = 16384
slow_launch_time = 2
#400 + (table_open_cache / 2)
table_definition_cache = 1500
table_open_cache = 2200
wait_timeout = 86400
sort_buffer_size=10M
join_buffer_size=2M
max_heap_table_size = 64M
#tmp_table_size = 200M
key_buffer_size = 8388600
myisam_sort_buffer_size = 262144
innodb_sort_buffer_size=3M

#
# * binlog
#
log_bin=mysql-bin
log-bin-index=mysql-bin.index
binlog-format=row
expire_logs_days=0
max_binlog_size=700M
binlog_cache_size = 256KB
binlog_checksum = CRC32
binlog_row_image = full
binlog_stmt_cache_size = 32768
sync_binlog=1

#
# * innodb
#
default-storage-engine=InnoDB
default_tmp_storage_engine=InnoDB
character_set_server = utf8
innodb_file_per_table=1
innodb_flush_log_at_trx_commit = 1
innodb_data_file_path = ibdata1:12m:autoextend
#80%MEM
#innodb_buffer_pool_size = 200m
innodb_thread_concurrency = 0
#innodb_default_row_format=dynamic
#innodb_log_buffer_size = 16M
#innodb_log_file_size = 800M
innodb_log_files_in_group = 4
innodb_adaptive_hash_index = ON
innodb_max_dirty_pages_pct = 75
innodb_old_blocks_pct = 37
innodb_open_files = 3000
#default value was changed from 1 to 4 in MySQL 5.7.8.
innodb_purge_threads = 4
innodb_stats_on_metadata = OFF
innodb_stats_transient_sample_pages = 15
innodb_strict_mode = ON
innodb_table_locks = ON
innodb_thread_sleep_delay = 10000
innodb_write_io_threads = 4
#the server waits for activity on an interactive connection before closing it 
interactive_timeout = 7200
innodb_flush_method = O_DIRECT
relay_log_info_repository=TABLE  
master_info_repository=TABLE  
relay_log_recovery=on  

#
#* QC
#
query_cache_type = 0
query_alloc_block_size = 8192
query_cache_limit = 1048576
query_cache_size = 3145728

#
#* replication
#
gtid_mode=on
#>5,7,6 enum(OFF,ON,WARN)
enforce_gtid_consistency=on
relay-log=mysql-relay-bin
relay-log-index=mysql-relay-bin.index
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
log_bin_trust_function_creators=1