MySQL-group-replication 配置步骤(推荐)

MySQL-Group-Replication 是mysql-5.7.17版本开发出来的新特性；它在master-slave 之间实现了强一致性，

但是就目前来说主要是性能不太好。

【1】确定当前的mysql数据库版本为5.7.17及以上

/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 内容如下：

[mysqld]
####: for global
user          =jianglexing          #  mysql
basedir          =/usr/local/mysql        #  /usr/local/mysql/
datadir          =/tmp/4406/          #  /usr/local/mysql/data
server_id        =4406            #  0
port          =4406            #  3306
socket          =/tmp/4406/mysql.sock        #  /tmp/mysql.sock
auto_increment_increment    =1            #  1
auto_increment_offset      =1            #  1
lower_case_table_names      =1            #  0
secure_file_priv      =            #  null


####: for binlog
binlog_format        =row            #  row
log_bin          =mysql-bin          #  off
binlog_rows_query_log_events    =on            #  off
log_slave_updates      =on            #  off
expire_logs_days      =4            #  0
binlog_cache_size      =32768            #  32768(32k)
binlog_checksum        =none            #  CRC32
sync_binlog        =1            #  1


####: for error-log
log_error        =mysql-err.log          #  /usr/local/mysql/data/localhost.localdomain.err


####: for slow query log


####: for gtid
gtid_mode        =on            #  off
enforce_gtid_consistency    =on            #  off


####: for replication
master_info_repository      =table            #  file
relay_log_info_repository    =table            #  file


####: for group replication
transaction_write_set_extraction  =XXHASH64          #  off
loose-group_replication_group_name  ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"    #  
loose-group_replication_start_on_boot  =off            #  off
loose-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 innodb
default_storage_engine      =innodb            #  innodb
default_tmp_storage_engine    =innodb            #  innodb
innodb_data_file_path      =ibdata1:12M:autoextend        #  ibdata1:12M:autoextend
innodb_temp_data_file_path    =ibtmp1:12M:autoextend        #  ibtmp1:12M:autoextend
innodb_log_group_home_dir    =./            #  ./
innodb_log_files_in_group    =2            #  2
innodb_log_file_size      =48M            #  50331648(48M)
innodb_file_format      =Barracuda          #  Barracuda
innodb_file_per_table      =on             #  on
innodb_page_size      =16k            #  16384(16k)
innodb_thread_concurrency    =0            #  0
innodb_read_io_threads      =4            #  4
innodb_write_io_threads      =4            #  4
innodb_purge_threads      =4            #  4
innodb_print_all_deadlocks    =on            #  off
innodb_deadlock_detect      =on            #  on
innodb_lock_wait_timeout    =50            #  50
innodb_spin_wait_delay      =6            #  6
innodb_autoinc_lock_mode    =2            #  1
innodb_stats_persistent      =on            #  on
innodb_stats_persistent_sample_pages  =20            #  20
innodb_adaptive_hash_index    =on            #  on
innodb_change_buffering      =all            #  all
innodb_change_buffer_max_size    =25            #  25
innodb_flush_neighbors      =1            #  1
innodb_flush_method      =O_DIRECT          #  
innodb_doublewrite      =on            #  on
innodb_log_buffer_size      =16M            #  16777216(16M)
innodb_flush_log_at_timeout    =1            #  1
innodb_flush_log_at_trx_commit    =1            #  1
autocommit        =1            #  1

[client]
auto-rehash

/tmp/5506.cnf 内容如下：

[mysqld]
####: for global
user          =jianglexing          #  mysql
basedir          =/usr/local/mysql        #  /usr/local/mysql/
datadir          =/tmp/5506        #  /usr/local/mysql/data
server_id        =5506            #  0
port          =5506            #  3306
socket          =/tmp/5506/mysql.sock        #  /tmp/mysql.sock
auto_increment_increment    =1            #  1
auto_increment_offset      =1            #  1
lower_case_table_names      =1            #  0
secure_file_priv      =            #  null


####: for binlog
binlog_format        =row            #  row
log_bin          =mysql-bin          #  off
binlog_rows_query_log_events    =on            #  off
log_slave_updates      =on            #  off
expire_logs_days      =4            #  0
binlog_cache_size      =32768            #  32768(32k)
binlog_checksum        =none            #  CRC32
sync_binlog        =1            #  1


####: for error-log
log_error        =mysql-err.log          #  /usr/local/mysql/data/localhost.localdomain.err


####: for slow query log


####: for gtid
gtid_mode        =on            #  off
enforce_gtid_consistency    =on            #  off

[OH'yi%."y.*l9kn[B]\HHBH\H\[B[\[\KYY]][OK\
KY]Y\K\
KZ[]X[^KZ[YCBB[\KYY]][OK\L
KY]Y\K\L
KZ[]X[^KZ[YCBB[\KYY]][OK\
KY]Y\K\
KZ[]X[^KZ[YOOB]B[[OH8acy\\X[9b'yi/[B]\HHBH\H\[B[\[\KYY][[OK\
	[\OB]B]\HHBH\H\[B^\ZLH]\T


BBKH9hb9-[LX]H\	Y[YYYH	MB[\X[]K\X[Y[
	BX]H\	IY[YYYH	MB[\X[]K\X[Y[
	IBX]H\	[	Y[YYYH	MB[\X[]K\X[Y[
	[	B[LNBKH9hb9i#yb-a[X\\BX\\IBX\\\IM[[	\X[X\IBBKH9kz(#yb-.[[Y\X[[YH	\X[BBKH9d+#yb-B[\X[\[\\X[[\X[\[OB]B[[OHxML
9k/cy/""[B]\HHBH\H\[B[\[\KYY][[OK\L
	[\OB]B]\HHBH\H\[B^\ZLH]\T
ML
BBKH9hb9-[LX]H\	Y[YYYH	MB[\X[]K\X[Y[
	BX]H\	IY[YYYH	MB[\X[]K\X[Y[
	IBX]H\	[	Y[YYYH	MB[\X[]K\X[Y[
	[	B[LNBKH9hb9i#yb-a[X\\BX\\IBX\\\IM[[	\X[X\IBBKH9kz(#yb-.[[Y\X[[YH	\X[BBKH9d+#yb-B\\X[##y+%.cyb9aiyl,z(cOB]B[[OH
9k/g9.#ML
g9. 9--\X[9acyl,yk9$9.[B."/TY\\X[:acyizj
9:#d
yl,y+%b!.)9aj:`yky.n#9&) 9.*c g&i&i&+/c.