1.MYSQL配置文件
[root@win1 3306]# cat my.cnf [client] default-character-set = uft8 port = 3306 socket = /data/3306/tmp/mysql.sock [mysql] prompt="u@mysqldb R:m:s [d]> " [mysqld] basedir=/usr/local/mysql datadir=/data/3306/data port=3306 server_id=71 socket=/data/3306/tmp/mysql.sock tmpdir=/data/3306/tmp character-set-server=utf8 log-error=/data/3306/logs/alert_3306.log pid-file=/data/3306/tmp/win1.mysql_3306.pid gtid_mode=ON log-slave-updates=ON enforce-gtid-consistency=ON binlog_format = ROW binlog_checksum=NONE skip-slave-start=1 log-bin=/data/3306/binlog/mysql-bin master_info_repository=TABLE relay_log_purge=0 relay_log_info_repository=TABLE transaction_write_set_extraction=XXHASH64 secure_file_priv=/data/export slow_query_log=on long_query_time=1 slow_query_log_file=/data/3306/logs/slow.log log_queries_not_using_indexes=on log_output=FILE general_log = on innodb_data_file_path=ibdata1:2G:autoextend innodb_log_group_home_dir=/data/3306/redo innodb_file_per_table=1 plugin_load_add='group_replication.so' transaction_write_set_extraction=XXHASH64 group_replication_group_name="b665d136-2a50-11eb-ac56-0050563156dd" group_replication_start_on_boot=off group_replication_local_address= "win1:33061" group_replication_group_seeds= "win1:33061,win2:33061,win3:33061" group_replication_bootstrap_group=off
2.第一台设定，标红的部分只在第一个节点执行
mysql> reset master; Query OK, 0 rows affected (0.01 sec) mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> SET GLOBAL group_replication_bootstrap_group=ON; Query OK, 0 rows affected (0.00 sec) mysql> START GROUP_REPLICATION; Query OK, 0 rows affected (2.04 sec) mysql> SET GLOBAL group_replication_bootstrap_group=OFF; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+---------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+---------------+-------------+--------------+ | group_replication_applier | 18075979-2a5e-11eb-953b-0050563156dd | win1.inno.com | 3306 | ONLINE | +---------------------------+--------------------------------------+---------------+-------------+--------------+ 1 row in set (0.00 sec)
设定mgr 账号，在第一个节点
mysql> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER repl@'win1.inno.com' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER repl@'win2.inno.com' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER repl@'win3.inno.com' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'win2.inno.com'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'win1.inno.com'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'win3.inno.com'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0.00 sec)
加入第二台
mysql> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0.00 sec) mysql> set password='123456'; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER repl@'win1.inno.com' IDENTIFIED BY '123456'; n2.inno.com'; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER repl@'win2.inno.com' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER repl@'win3.inno.com' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'win2.inno.com'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'win1.inno.com'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'win3.inno.com'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0.00 sec) mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> START GROUP_REPLICATION; Query OK, 0 rows affected (4.93 sec) mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+---------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+---------------+-------------+--------------+ | group_replication_applier | 094939ff-2a61-11eb-b0fb-00505625c159 | win2.inno.com | 3306 | ONLINE | | group_replication_applier | 18075979-2a5e-11eb-953b-0050563156dd | win1.inno.com | 3306 | ONLINE | +---------------------------+--------------------------------------+---------------+-------------+--------------+ 2 rows in set (0.00 sec)
加入第三台
mysql> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0.00 sec) mysql> set password='123456'; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER repl@'win1.inno.com' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER repl@'win2.inno.com' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER repl@'win3.inno.com' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'win2.inno.com'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'win1.inno.com'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'win3.inno.com'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0.00 sec) mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> START GROUP_REPLICATION; Query OK, 0 rows affected (3.05 sec) mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+---------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+---------------+-------------+--------------+ | group_replication_applier | 094939ff-2a61-11eb-b0fb-00505625c159 | win2.inno.com | 3306 | ONLINE | | group_replication_applier | 18075979-2a5e-11eb-953b-0050563156dd | win1.inno.com | 3306 | ONLINE | | group_replication_applier | ed0a0135-2a61-11eb-af60-005056353553 | win3.inno.com | 3306 | ONLINE | +---------------------------+--------------------------------------+---------------+-------------+--------------+ 3 rows in set (0.00 sec)