MySQL 8.0.17 简单搭建group replication

1.环境准备

操作系统：centos6.8 x64

mysql版本：mysql8.0.17

mysql介质：mysql-8.0.17-linux-glibc2.12-x86_64.tar.xz

机器ip：172.16.60.6/7/8

主机名：

172.16.60.6 172-16-60-6
172.16.60.7 172-16-60-7
172.16.60.8 172-16-60-8

2.搭建步骤

• 配置文件模板

[mysql]
no-auto-rehash
prompt="\\u@\\h:\\d \\r\\m\\s>"

[mysqld_safe]
open-files-limit	= 65535

[client]

[mysqldump]
quick

[mysqld]
####Mysql8.0 base config######
user=mysql
skip-external-locking
skip-name-resolve
user            	= mysql
basedir         	= /usr/local/mysql-8.0.17-linux-glibc2.12-x86_64
back_log                = 210
collation-server 		= utf8mb4_general_ci
init_connect = 'SET NAMES utf8mb4;'
binlog_direct_non_transactional_updates = 1
keep_files_on_create = 1
optimizer_switch='mrr_cost_based=off'
group_concat_max_len = 102400
log_error_verbosity = 3
relay_log_purge=1
myisam_repair_threads = 1
log_slave_updates = 1
max_allowed_packet = 512M
port = 3306
lower_case_table_names = 1
event_scheduler = OFF
server-id = 6063306
character-set-server = utf8mb4
binlog_format = row
sync_binlog = 1
max_binlog_size = 512M
binlog_cache_size = 8M
binlog_stmt_cache_size = 1M
master_info_repository=TABLE
relay_log_info_repository=TABLE
default_authentication_plugin=mysql_native_password

#### directory config######
socket = /data/mysql/gr_test/mysql.sock
datadir = /data/mysql/gr_test/data/
innodb_data_home_dir = /data/mysql/gr_test/data/
innodb_log_group_home_dir = /data/mysql/gr_test/innodb_log/
pid-file = /data/mysql/gr_test/mysql.pid
tmpdir = /data/mysql/gr_test/tmp/
log-bin = /data/mysql/gr_test/binlog/mysql-bin
slow_query_log_file = /data/mysql/gr_test/slowlog/slow.log
log-error = /data/mysql/gr_test/log/error.log
relay_log = /data/mysql/gr_test/relaylog/mysql-relay-bin
innodb_undo_directory = /data/mysql/gr_test/undo_log
####per thread buffers######
max_connections = 210
max_user_connections = 200
wait_timeout = 1800
connect_timeout = 20
interactive_timeout = 1800
max_connect_errors = 1000000
key_buffer_size = 32M
table_open_cache = 4096
table_definition_cache = 4096
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 512K
read_rnd_buffer_size = 512K
thread_stack = 512K
max_heap_table_size = 128M
tmp_table_size = 128M
thread_cache_size = 256
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 64M

####Innodb#################
innodb_print_all_deadlocks      = 1
innodb_file_per_table = 1
innodb_rollback_on_timeout	= ON
innodb_data_file_path           = ibdata1:32M:autoextend
innodb_flush_method             = O_DIRECT
innodb_buffer_pool_size = 1G
innodb_flush_log_at_trx_commit = 2
transaction-isolation = READ-COMMITTED
innodb_lock_wait_timeout = 10
innodb_read_io_threads =     1
innodb_write_io_threads =     1
innodb_change_buffering = all
innodb_adaptive_hash_index = 1
innodb_autoinc_lock_mode = 2
innodb_log_file_size = 32M
innodb_log_files_in_group = 8
innodb_io_capacity =     2000
innodb_purge_threads = 2
innodb_old_blocks_pct = 80
log_bin_trust_function_creators = 1
innodb_log_buffer_size = 64M
innodb_stats_on_metadata = 0

####slow log#####
slow_query_log = 0
long_query_time = 1
log_queries_not_using_indexes = 1

####Replicate####
slave_parallel_workers =   4
slave_parallel_type=LOGICAL_CLOCK
slave_preserve_commit_order=1
slave_net_timeout=20
binlog_expire_logs_seconds = 0

####Group replication#########
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
plugin_load_add='group_replication.so'
group_replication_group_name="3afdd295-be6b-11e9-ace7-549f350fcb14"
group_replication_start_on_boot=off
group_replication_local_address= "172.16.60.6:33061"
group_replication_group_seeds= "172.16.60.6:33061,172.16.60.7:33061,172.16.60.8:33061"
group_replication_bootstrap_group=off

保证三台机器的server-id不同即可。

• mysql中的group replication默认为单主模式，我们在172.16.60.6上进行初始化工作

/usr/local/mysql-8.0.17-linux-glibc2.12-x86_64/bin/mysql -S /data/mysql/gr_test/mysql.sock
#配置root密码：
alter user 'root'@'localhost' identified by '123456';
#创建组复制同步账号：
CREATE USER rpl_user@'%' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
#开启组复制
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

 

• 查看组复制状态

mysql> use performance_schema;
mysql> select * from replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 55f92d75-c254-11e9-b129-005056350c6c | 172-16-60-6 |        3306 | ONLINE       | PRIMARY     | 8.0.17         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)

 

• 在172.16.60.6主实例上插入测试数据

  mysql> CREATE DATABASE test;
  mysql> USE test;
  mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
  mysql> INSERT INTO t1 VALUES (1, 'Luis');

   

• 将其他两个实例加入到组复制当中，直接在172.16.60.7/8两个实例中执行

/usr/local/mysql-8.0.17-linux-glibc2.12-x86_64/bin/mysql -S /data/mysql/gr_test/mysql.sock
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';
mysql> start group_replication;

• 再次查看组复制状态

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 55f92d75-c254-11e9-b129-005056350c6c | 172-16-60-6 |        3306 | ONLINE       | PRIMARY     | 8.0.17         |
| group_replication_applier | 5822e42f-c254-11e9-a272-00505633ea90 | 172-16-60-7 |        3306 | ONLINE       | SECONDARY   | 8.0.17         |
| group_replication_applier | 5aa6bf0b-c254-11e9-9e1d-000c29b1454d | 172-16-60-8 |        3306 | ONLINE       | SECONDARY   | 8.0.17         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

•  在从实例上查看数据

  mysql> show databases;
  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | mysql              |
  | performance_schema |
  | sys                |
  | test               |
  +--------------------+
  5 rows in set (0.00 sec)
  
  mysql> use test;
  Database changed
  mysql> show tables;
  +----------------+
  | Tables_in_test |
  +----------------+
  | t1             |
  +----------------+
  1 row in set (0.00 sec)
  
  mysql> select * from t1;
  +----+------+
  | c1 | c2   |
  +----+------+
  |  1 | Luis |
  +----+------+
  1 row in set (0.00 sec)

  数据已经同步过来。