MySQL Group Replication

Group replication is a new high-availability, high-expansion MySQL Cluster service.
High consistency, based on the native replication and Paxos protocol group replication Technology, plug-in to provide consistent data security assurance;
High fault tolerance, the majority of services can continue to work, automatic detection of different nodes of resource acquisition conflict, priority in order, built-in automatic anti-brain crack mechanism;
High scalability, automatically add remove nodes, and update group information;
High flexibility, single-master mode and multi-master mode. Single main mode auto-select master, all update operations are performed in the main, multi-master mode, all servers are updated simultaneously.

0, modify the hosts
Vim/etc/hosts
localhost 127.0.0.1

1, experimental environment
m1:127.0.0.1 3307
m2:127.0.0.1 3308

2, install MySQL
/usr/local/mysql57/bin/mysqld--initialize-insecure--user=dba--basedir=/usr/local/mysql57--datadir=/data1/ mysql3307
/usr/local/mysql57/bin/mysqld--initialize-insecure--user=dba--basedir=/usr/local/mysql57--datadir=/data1/ mysql3308

3, configuration
M1:
[Mysqld]

# General con#
user = dBA
Port = 3307
Default_storage_engine = InnoDB
Socket =/tmp/mysql3307.sock
Pid_file =/data1/mysql3307/mysql.pid

# SAFETY #
Max_allowed_packet = 64M
Max_connect_errors = 1000000

# DATA STORAGE #binlog-format
DataDir =/data1/mysql3307/

# BINARY LOGGING #
Log_bin =/data1/mysql3307/3307-binlog
Expire_logs_days = 10
#sync_binlog = 1
relay-log=/data1/mysql3307/3307-relaylog
#replicate-wild-do-table=hostility_url.%
#replicate-wild-do-table=guards.%

# CACHES and LIMITS #
Tmp_table_size = 32M
Max_heap_table_size = 32M
Query_cache_type = 1
Query_cache_size = 0
Max_connections = 5000
#max_user_connections = 200
Thread_cache_size = 512
Open_files_limit = 65535
Table_definition_cache = 4096
Table_open_cache = 4096
wait_timeout=7500
interactive_timeout=7500
Binlog-format=row
Character-set-server=utf8
Skip-name-resolve
Skip-character-set-client-handshake
back_log=1024

# INNODB #
#innodb_flush_method = O_direct
Innodb_data_home_dir =/data1/mysql3307/
#innodb_data_file_path = Ibdata1:1g:autoextend
innodb_log_group_home_dir=/data1/mysql3307/
Innodb_log_files_in_group = 3
Innodb_log_file_size = 1G
Innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1
Innodb_file_format=barracuda
Innodb_support_xa=0

innodb_io_capacity=500
Innodb_max_dirty_pages_pct=90
Innodb_read_io_threads=16
Innodb_write_io_threads=8
Innodb_buffer_pool_instances=4
Innodb_thread_concurrency=0

#GTID

#gtid_mode = On
#enforce_gtid_consistency = On

# LOGGING #
Log_error =/data1/mysql3307/error.log
#log_queries_not_using_indexes = 1
Slow_query_log = 1
Slow_query_log_file =/data1/mysql3307/mysql-slow.log
long_query_time=0.05
server_id=3307
Innodb_buffer_pool_size = 1G
Report-host=localhost #此处的localhost一定要存在与hosts文件里, i.e.: localhost 127.0.0.1
report-port=3307
Log_slave_updates = On

#group replication
Gtid_mode = On
Enforce_gtid_consistency = On
Master_info_repository = table
Relay_log_info_repository = table
Binlog_checksum=none
Log_slave_updates=on

Transaction_write_set_extraction=xxhash64
Loose-group_replication_group_name= "AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA"
Loose-group_replication_start_on_boot=off
Loose-group_replication_local_address= "localhost:24901"
Loose-group_replication_group_seeds= "localhost:24901,localhost:24902"
Loose-group_replication_single_primary_mode = True
loose-group_replication_bootstrap_group= off
Loose-group_replication_enforce_update_everywhere_checks = False

# # MYSQL_VERSION=MYSQL57
[MySQL]
prompt = \[email protected]\h:\p [\d]>

4. Start MySQL
Nohup/usr/local/mysql57/bin/mysqld_safe--defaults-file=/data1/mysql3307/my3307.cnf 2>/dev/null &
Mysql-uroot-s/tmp/mysql3307.sock-p123456

Set sql_log_bin=0;
Create user [email protected] '% ';
Grant Replication Slave on * * to [e-mail protected] '% ' identified by ' rpl_pass ';
Flush privileges;
Set sql_log_bin=1;
Change Master to master_user= ' Rpl_user ', master_password= ' Rpl_pass ' for Channel ' group_replication_recovery ';

5, Start group replication
Install plugin group_replication soname ' group_replication.so ';
Show plugins;
Set global group_replication_bootstrap_group=on;
Start group_replication;
Set global Group_replication_bootstrap_group=off;
SELECT * from Performance_schema.replication_group_members;

6, test (Create a database, a new instance is added to the group replication and will be automatically synced to the new instance)
Create DATABASE test;
Use test;
CREATE TABLE t (ID int primary key auto_increment,name text);
INSERT into t values (1, ' Lucas ');
Show Binlog evnets;

7, add a new instance
M2:

[Mysqld]

# General con#
user = dBA
Port = 3308
Default_storage_engine = InnoDB
Socket =/tmp/mysql3308.sock
Pid_file =/data1/mysql3308/mysql.pid

# SAFETY #
Max_allowed_packet = 64M
Max_connect_errors = 1000000

# DATA STORAGE #binlog-format
DataDir =/data1/mysql3308/

# BINARY LOGGING #
Log_bin =/data1/mysql3308/3308-binlog
Expire_logs_days = 10
#sync_binlog = 1
relay-log=/data1/mysql3308/3308relaylog
#replicate-wild-do-table=hostility_url.%
#replicate-wild-do-table=guards.%

# CACHES and LIMITS #
Tmp_table_size = 32M
Max_heap_table_size = 32M
Query_cache_type = 1
Query_cache_size = 0
Max_connections = 5000
#max_user_connections = 200
Thread_cache_size = 512
Open_files_limit = 65535
Table_definition_cache = 4096
Table_open_cache = 4096
wait_timeout=7500
interactive_timeout=7500
Binlog-format=row
Character-set-server=utf8
Skip-name-resolve
Skip-character-set-client-handshake
back_log=1024

# INNODB #
#innodb_flush_method = O_direct
Innodb_data_home_dir =/data1/mysql3308/
#innodb_data_file_path = Ibdata1:1g:autoextend
innodb_log_group_home_dir=/data1/mysql3308/
Innodb_log_files_in_group = 3
Innodb_log_file_size = 1G
Innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1
Innodb_file_format=barracuda
Innodb_support_xa=0

innodb_io_capacity=500
Innodb_max_dirty_pages_pct=90
Innodb_read_io_threads=16
Innodb_write_io_threads=8
Innodb_buffer_pool_instances=4
Innodb_thread_concurrency=0

#GTID

#gtid_mode = On
#enforce_gtid_consistency = On

# LOGGING #
Log_error =/data1/mysql3308/error.log
#log_queries_not_using_indexes = 1
Slow_query_log = 1
Slow_query_log_file =/data1/mysql3308/mysql-slow.log
long_query_time=0.05
server_id=3307
Innodb_buffer_pool_size = 1G
Report-host=localhost #此处的localhost一定要存在与hosts文件里, i.e.: localhost 127.0.0.1
report-port=3307
Log_slave_updates = On

#group replication
Gtid_mode = On
Enforce_gtid_consistency = On
Master_info_repository = table
Relay_log_info_repository = table
Binlog_checksum=none
Log_slave_updates=on

Transaction_write_set_extraction=xxhash64
Loose-group_replication_group_name= "AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA"
Loose-group_replication_start_on_boot=off
Loose-group_replication_local_address= "localhost:24901"
Loose-group_replication_group_seeds= "localhost:24901,localhost:24902"
Loose-group_replication_single_primary_mode = True
loose-group_replication_bootstrap_group= off
Loose-group_replication_enforce_update_everywhere_checks = False

# # MYSQL_VERSION=MYSQL57
[MySQL]
prompt = \[email protected]\h:\p [\d]>

8, launch instance & Configure Group replication
Nohup/usr/local/mysql57/bin/mysqld_safe--defaults-file=/data1/mysql3308/my3308.cnf 2>/dev/null &
Mysql-uroot-s/tmp/mysql3308.sock-p123456

Set sql_log_bin=0;
Create user [email protected] '% ';
Grant Replication Slave on * * to [e-mail protected] '% ' identified by ' rpl_pass ';
Flush privileges;
Set sql_log_bin=1;
Change Master to master_user= ' Rpl_user ', master_password= ' Rpl_pass ' for Channel ' group_replication_recovery ';

9, Start group replication
Install plugin group_replication soname ' group_replication.so ';
Show plugins;
Start group_replication;
SELECT * from Performance_schema.replication_group_members;

10, test the synchronization is normal
/usr/local/mysql57/bin/mysql-uroot-p123456-s/tmp/mysql3308.sock

show databases;

Note: The new instance has been reported in the process of recovering, mainly in the configuration of MySQL Report-host is not configured, or the value of report-host configuration is not in the system hosts

MySQL Group Replication