This is a series post about learning mysql group replication (including deployment ,operations, ha, performance testing, internals).This is the first post and i will share the way to build a MGR cluster in docker enviorement.
About MySQL Group Replication
Group Replication is a technique that can be used to implement fault-tolerant systems. The replication group is a set of servers that interact with each other through message passing. The communication layer provides a set of guarantees such as atomic message and total order message delivery. These are very powerful properties that translate into very useful abstractions that one can resort to build more advanced database replication solutions.
[mysql] prompt=\\u@\\d \\r:\\m:\\s> default-character-set=utf8mb4 [mysqld] server_id = 1 log_timestamps=SYSTEM # # 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 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 default-storage-engine=INNODB log_timestamps = SYSTEM #dir innodb_data_file_path=ibdata1:10M:autoextend:max:10G datadir=/var/lib/mysql slow_query_log_file=/var/lib/mysql/slow.log log-error=/var/log/mysqld.log log_bin=/var/lib/mysql/binlog relay-log=/var/lib/mysql/relaylog #innodb innodb_buffer_pool_size = 64M innodb_log_files_in_group=2 innodb_flush_log_at_trx_commit=1 innodb_io_capacity=100 innodb_read_io_threads=4 innodb_write_io_threads=4 innodb_file_per_table=1 innodb_adaptive_flushing=1 innodb_stats_on_metadata=0 innodb_flush_method=O_DIRECT innodb_log_buffer_size=10M transaction-isolation = READ-COMMITTED #table table_definition_cache=512 table_open_cache=512 table_open_cache_instances = 2 #thread thread_stack=262144 thread_cache_size=256 thread_handling=one-thread-per-connection #gtid&repl gtid_mode=ON enforce_gtid_consistency=ON master_info_repository=TABLE relay_log_info_repository=TABLE binlog_checksum=NONE log_slave_updates=ON #binlog max_binlog_size=50M binlog_cache_size=5M binlog-format=ROW sync_binlog=1 long_query_time=0.2 slow_query_log=1 skip-slave-start #coms query_cache_type=0 query_cache_size=0 local-infile=0 port=3306 max_connections=110 max_user_connections=100 character-set-server=utf8mb4 skip-name-resolve # GR plugin_load = "group_replication.so" transaction_write_set_extraction = XXHASH64 group_replication_start_on_boot = OFF group_replication_bootstrap_group = OFF group_replication_group_name = 716483bc-c763-11e6-93c0-0242ac110002 group_replication_local_address = '' group_replication_group_seeds = '' group_replication_single_primary_mode=FALSE group_replication_enforce_update_everywhere_checks=TRUE
docker run -it --name mgr0x0 -e MYSQL_ROOT_PASSWORD=root -d ruiaylin/mysql57-group-replication docker run -it --name mgr0x1 -e MYSQL_ROOT_PASSWORD=root -d ruiaylin/mysql57-group-replication docker run -it --name mgr0x2 -e MYSQL_ROOT_PASSWORD=root -d ruiaylin/mysql57-group-replication
查看容器:
1 2 3 4 5
➜ ~ docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 23361ae481fa ruiaylin/mysql57-group-replication "/entrypoint.sh " About an hour ago Up About an hour 3306/tcp mgr0x2 2e8a57225332 ruiaylin/mysql57-group-replication "/entrypoint.sh " About an hour ago Up About an hour 3306/tcp mgr0x1 1cb5c7fb1505 ruiaylin/mysql57-group-replication "/entrypoint.sh " About an hour ago Up About an hour 3306/tcp mgr0x0
获取容器的IP(去掉 NetworkSettings.IPAddress 左右大括号的反斜杠 ):
1 2 3 4 5
for x in ` docker ps |grep mgr | awk '{print $1}'` do ip=`docker inspect --format='\{\{.NetworkSettings.IPAddress\}\}' $x` echo $ip $x done
set global group_replication_ip_whitelist="127.0.0.1/32,10.0.0.0/8,172.16.0.0/12,192.168.0.0/16,172.17.0.0/16"
否则会无法访问group, 报错如下:
1 2 3
2016-12-20T05:11:32.220173-00:00 0 [Warning] Plugin group_replication reported: '[GCS] Connection attempt from IP address 172.17.0.5 refused. Address is not in the IP whitelist.' 2016-12-20T05:11:32.220240-00:00 0 [ERROR] Plugin group_replication reported 2016-12-20T05:12:32.203122-00:00 4 [ERROR] Plugin group_replication reported: '[GCS] The member is leaving a group without being on one.'
set global transaction_write_set_extraction = XXHASH64 set global group_replication_start_on_boot = OFF set global group_replication_bootstrap_group = OFF set global group_replication_group_name = 716483bc-c763-11e6-93c0-0242ac110002 set global group_replication_local_address = '172.17.0.5:3316' set global group_replication_group_seeds = '172.17.0.5:3316,172.17.0.6:3316,172.17.0.7:3316' set global group_replication_single_primary_mode=FALSE set global group_replication_enforce_update_everywhere_checks=TRUE
创建复制账户(每个节点都要执行)
1 2 3 4 5 6
SET SQL_LOG_BIN=0; CREATE USER repl@'%'; GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'repl'; SET SQL_LOG_BIN=1; # 进行复制调整 CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
启动组复制
第一个节点需要执行:
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
其余节点都应该执行如下:
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
