MySQL-Group-Replication 是mysql-5.7.17版本開發出來的新特性；它在master-slave 之間實現了強一致性，
但是就目前來說主要是性能不太好。
【1】確定當前的mysql數據庫版本為5.7.17及以上
|
1
2
|
/usr/local/mysql/bin/mysqld --version/usr/local/mysql/bin/mysqld Ver 5.7.17 for linux-glibc2.5 on x86_64 (MySQL Community Server (GPL)) |
【2】實驗環境為一臺主機上安裝3臺mysql，它們三個組成一個group-replication 組
/tmp/4406.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
|
[mysqld]####: for globaluser =jianglexing # mysqlbasedir =/usr/local/mysql # /usr/local/mysql/datadir =/tmp/4406/ # /usr/local/mysql/dataserver_id =4406 # 0port =4406 # 3306socket =/tmp/4406/mysql.sock # /tmp/mysql.sockauto_increment_increment =1 # 1auto_increment_offset =1 # 1lower_case_table_names =1 # 0secure_file_priv = # null####: for binlogbinlog_format =row # rowlog_bin =mysql-bin # offbinlog_rows_query_log_events =on # offlog_slave_updates =on # offexpire_logs_days =4 # 0binlog_cache_size =32768 # 32768(32k)binlog_checksum =none # CRC32sync_binlog =1 # 1####: for error-loglog_error =mysql-err.log # /usr/local/mysql/data/localhost.localdomain.err####: for slow query log####: for gtidgtid_mode =on # offenforce_gtid_consistency =on # off####: for replicationmaster_info_repository =table # filerelay_log_info_repository =table # file####: for group replicationtransaction_write_set_extraction =XXHASH64 # offloose-group_replication_group_name ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" # loose-group_replication_start_on_boot =off # offloose-group_replication_local_address ="127.0.0.1:24901" #loose-group_replication_group_seeds ="127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903"loose-group_replication_bootstrap_group =off # off####: for innodbdefault_storage_engine =innodb # innodbdefault_tmp_storage_engine =innodb # innodbinnodb_data_file_path =ibdata1:12M:autoextend # ibdata1:12M:autoextendinnodb_temp_data_file_path =ibtmp1:12M:autoextend # ibtmp1:12M:autoextendinnodb_log_group_home_dir =./ # ./innodb_log_files_in_group =2 # 2innodb_log_file_size =48M # 50331648(48M)innodb_file_format =Barracuda # Barracudainnodb_file_per_table =on # oninnodb_page_size =16k # 16384(16k)innodb_thread_concurrency =0 # 0innodb_read_io_threads =4 # 4innodb_write_io_threads =4 # 4innodb_purge_threads =4 # 4innodb_print_all_deadlocks =on # offinnodb_deadlock_detect =on # oninnodb_lock_wait_timeout =50 # 50innodb_spin_wait_delay =6 # 6innodb_autoinc_lock_mode =2 # 1innodb_stats_persistent =on # oninnodb_stats_persistent_sample_pages =20 # 20innodb_adaptive_hash_index =on # oninnodb_change_buffering =all # allinnodb_change_buffer_max_size =25 # 25innodb_flush_neighbors =1 # 1innodb_flush_method =O_DIRECT # innodb_doublewrite =on # oninnodb_log_buffer_size =16M # 16777216(16M)innodb_flush_log_at_timeout =1 # 1innodb_flush_log_at_trx_commit =1 # 1autocommit =1 # 1[client]auto-rehash |
/tmp/5506.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
|
[mysqld]####: for globaluser =jianglexing # mysqlbasedir =/usr/local/mysql # /usr/local/mysql/datadir =/tmp/5506 # /usr/local/mysql/dataserver_id =5506 # 0port =5506 # 3306socket =/tmp/5506/mysql.sock # /tmp/mysql.sockauto_increment_increment =1 # 1auto_increment_offset =1 # 1lower_case_table_names =1 # 0secure_file_priv = # null####: for binlogbinlog_format =row # rowlog_bin =mysql-bin # offbinlog_rows_query_log_events =on # offlog_slave_updates =on # offexpire_logs_days =4 # 0binlog_cache_size =32768 # 32768(32k)binlog_checksum =none # CRC32sync_binlog =1 # 1####: for error-loglog_error =mysql-err.log # /usr/local/mysql/data/localhost.localdomain.err####: for slow query log####: for gtidgtid_mode =on # offenforce_gtid_consistency =on # off####: for replicationmaster_info_repository =table # filerelay_log_info_repository =table # file####: for group replicationtransaction_write_set_extraction =XXHASH64 # offloose-group_replication_group_name ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" # loose-group_replication_start_on_boot =off # offloose-group_replication_local_address ="127.0.0.1:24902" #loose-group_replication_group_seeds ="127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903"loose-group_replication_bootstrap_group =off # off####: for innodbdefault_storage_engine =innodb # innodbdefault_tmp_storage_engine =innodb # innodbinnodb_data_file_path =ibdata1:12M:autoextend # ibdata1:12M:autoextendinnodb_temp_data_file_path =ibtmp1:12M:autoextend # ibtmp1:12M:autoextendinnodb_log_group_home_dir =./ # ./innodb_log_files_in_group =2 # 2innodb_log_file_size =48M # 50331648(48M)innodb_file_format =Barracuda # Barracudainnodb_file_per_table =on # oninnodb_page_size =16k # 16384(16k)innodb_thread_concurrency =0 # 0innodb_read_io_threads =4 # 4innodb_write_io_threads =4 # 4innodb_purge_threads =4 # 4innodb_print_all_deadlocks =on # offinnodb_deadlock_detect =on # oninnodb_lock_wait_timeout =50 # 50innodb_spin_wait_delay =6 # 6innodb_autoinc_lock_mode =2 # 1innodb_stats_persistent =on # oninnodb_stats_persistent_sample_pages =20 # 20innodb_adaptive_hash_index =on # oninnodb_change_buffering =all # allinnodb_change_buffer_max_size =25 # 25innodb_flush_neighbors =1 # 1innodb_flush_method =O_DIRECT # innodb_doublewrite =on # oninnodb_log_buffer_size =16M # 16777216(16M)innodb_flush_log_at_timeout =1 # 1innodb_flush_log_at_trx_commit =1 # 1autocommit =1 # 1 |
/tmp/6606.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
|
[mysqld]####: for globaluser =jianglexing # mysqlbasedir =/usr/local/mysql # /usr/local/mysql/datadir =/tmp/6606/ # /usr/local/mysql/dataserver_id =6606 # 0port =6606 # 3306socket =/tmp/6606/mysql.sock # /tmp/mysql.sockauto_increment_increment =1 # 1auto_increment_offset =1 # 1lower_case_table_names =1 # 0secure_file_priv = # null####: for binlogbinlog_format =row # rowlog_bin =mysql-bin # offbinlog_rows_query_log_events =on # offlog_slave_updates =on # offexpire_logs_days =4 # 0binlog_cache_size =32768 # 32768(32k)binlog_checksum =none # CRC32sync_binlog =1 # 1####: for error-loglog_error =mysql-err.log # /usr/local/mysql/data/localhost.localdomain.err####: for slow query log####: for gtidgtid_mode =on # offenforce_gtid_consistency =on # off####: for replicationmaster_info_repository =table # filerelay_log_info_repository =table # file####: for group replicationtransaction_write_set_extraction =XXHASH64 # offloose-group_replication_group_name ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" # loose-group_replication_start_on_boot =off # offloose-group_replication_local_address ="127.0.0.1:24903" #loose-group_replication_group_seeds ="127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903"loose-group_replication_bootstrap_group =off # off####: for innodbdefault_storage_engine =innodb # innodbdefault_tmp_storage_engine =innodb # innodbinnodb_data_file_path =ibdata1:12M:autoextend # ibdata1:12M:autoextendinnodb_temp_data_file_path =ibtmp1:12M:autoextend # ibtmp1:12M:autoextendinnodb_log_group_home_dir =./ # ./innodb_log_files_in_group =2 # 2innodb_log_file_size =48M # 50331648(48M)innodb_file_format =Barracuda # Barracudainnodb_file_per_table =on # oninnodb_page_size =16k # 16384(16k)innodb_thread_concurrency =0 # 0innodb_read_io_threads =4 # 4innodb_write_io_threads =4 # 4innodb_purge_threads =4 # 4innodb_print_all_deadlocks =on # offinnodb_deadlock_detect =on # oninnodb_lock_wait_timeout =50 # 50innodb_spin_wait_delay =6 # 6innodb_autoinc_lock_mode =2 # 1innodb_stats_persistent =on # oninnodb_stats_persistent_sample_pages =20 # 20innodb_adaptive_hash_index =on # oninnodb_change_buffering =all # allinnodb_change_buffer_max_size =25 # 25innodb_flush_neighbors =1 # 1innodb_flush_method =O_DIRECT # innodb_doublewrite =on # oninnodb_log_buffer_size =16M # 16777216(16M)innodb_flush_log_at_timeout =1 # 1innodb_flush_log_at_trx_commit =1 # 1autocommit =1 # 1 |
【3】初始化三個數據庫實例
|
1
2
3
4
5
6
|
cd /usr/local/mysql/./bin/mysqld --defautls-file=/tmp/4406.cnf --datadir=/tmp/4406 --initialize-insecrue./bin/mysqld --defautls-file=/tmp/5506.cnf --datadir=/tmp/5506 --initialize-insecrue./bin/mysqld --defautls-file=/tmp/6606.cnf --datadir=/tmp/6606 --initialize-insecrue |
【4】配置group-replication 的初始實例
|
1
|
/usr/local/mysql/bin/mysqld --defaults-file=/tmp/4406.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
|
mysql -h127.0.0.1 -uroot -P4406-- 增加用戶 set sql_log_bin=0; create user rpl_user@'%' identified by '123456'; grant replication slave,replication client on *.* to rpl_user@'%'; create user rpl_user@'127.0.0.1' identified by '123456'; grant replication slave,replication client on *.* to rpl_user@'127.0.0.1'; create user rpl_user@'localhost' identified by '123456'; grant replication slave,replication client on *.* to rpl_user@'localhost'; set sql_log_bin=1;-- 增加復制憑證 change master to master_user='rpl_user', master_password='123456' for channel 'group_replication_recovery';-- 安裝組復制物件 install plugin group_replication soname 'group_replication.so';-- 啟動組復制 set global group_replication_bootstrap_group=on; start group_replication; set global group_replication_bootstrap_group=off; |
【5】5506 實例的配置過程如下：
|
1
|
/usr/local/mysql/bin/mysqld --defaults-file=/tmp/5506.cnf & |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
mysql -h127.0.0.1 -uroot -P5506-- 增加用戶 set sql_log_bin=0; create user rpl_user@'%' identified by '123456'; grant replication slave,replication client on *.* to rpl_user@'%'; create user rpl_user@'127.0.0.1' identified by '123456'; grant replication slave,replication client on *.* to rpl_user@'127.0.0.1'; create user rpl_user@'localhost' identified by '123456'; grant replication slave,replication client on *.* to rpl_user@'localhost'; set sql_log_bin=1;-- 增加復制憑證 change master to master_user='rpl_user', master_password='123456' for channel 'group_replication_recovery';-- 安裝組復制物件 install plugin group_replication soname 'group_replication.so';-- 啟動組復制 start group_replication; # 注意這里不是初始化了，只要加入就行 |
【6】6606 實例的操作與5506的操作一樣，這樣group replication 的配置就完成了。
以上這篇MySQL-group-replication 配置步驟(推薦)就是小編分享給大家的全部內容了，希望能給大家一個參考，也希望大家多多支持服務器之家。
