Mysqlグループコピー構成MGR(シングルマスター)
MySQLグループレプリケーション構成(シングルマスター)
-MySQLグループコピーmysql 5.7.17、グループ内のN/2+1個のノードがrelay logを受け取り、master才commit–2018年7月29日、18:00:42
-MySQLグループコピーmysql 5.7.17、グループ内のN/2+1個のノードがrelay logを受け取り、master才commit–2018年7月29日、18:00:42
-- 1
-- 1.1 （ ）
-- Windows 3 （ 3306,3307,3308）， ：MySQL 5.7.17
-- 1.2 ：
mkdir -p D:\mysql\mgr\3306\data
mkdir -p D:\mysql\mgr\3306\logs
mkdir -p D:\mysql\mgr\3307\data
mkdir -p D:\mysql\mgr\3307\logs
mkdir -p D:\mysql\mgr\3308\data
mkdir -p D:\mysql\mgr\3308\logs
mkdir -p /data/mysql57/mysql3306
mkdir -p /data/mysql57/mysql3307
mkdir -p /data/mysql57/mysql3308
mkdir -p /data/mysql57/logs/3306
mkdir -p /data/mysql57/logs/3307
mkdir -p /data/mysql57/logs/3308
-- 2. ：
-- 2.1 3306 ：
-- windows
[mysql]
mysql = "(\u@\h) [\d]> "
[mysqld]
server-id = 3306
user = root
port = 3306
socket = D:/mysql/mgr/3306/logsmysql3306.sock
basedir = D:/mysql/mysql-5.7.17-winx64
datadir = D:/mysql/mgr/3306/data
tmpdir = D:/mysql/mgr/3306/tmp
bind-address = 0.0.0.0
log-error = D:/mysql/mgr/3306/logs/error-log
pid-file = D:/mysql/mgr/3306/data/3306.pid
log-bin = D:/mysql/mgr/3306/logs/binlog
log-bin-index = D:/mysql/mgr/3306/logs/binlog.index
relay-log = D:/mysql/mgr/3306/logs/relaylog
relay-log-index = D:/mysql/mgr/3306/logs/relaylog.index
master_info_repository = TABLE
relay_log_info_repository = TABLE
log_slave_updates = ON
binlog_checksum = NONE
binlog_format = ROW
transaction_isolation = READ-COMMITTED
gtid_mode = ON
enforce_gtid_consistency = ON
# GR loose Group Replication plugin mysql server
transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" # ， select uuid();
loose-group_replication_start_on_boot = off # mysqld
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 #
loose-group_replication_single_primary_mode = ON #
loose-group_replication_enforce_update_everywhere_checks = OFF #
-- 2.2 3306
mysqld -- defaults-file=D:/mysql/mgr/3306/my.ini -- initialize-insecure
-- 2.3 3306 （ ）：
mysqld -- install mysql3306 -- defaults-file=D:/mysql/mgr/3306/my.ini
-- 2.4 MySQL Client （ ）
mysql -uroot -p -P3306
--
mysqladmin -uroot -p -P3306 password 123asd
-- 2.5 ， group ：
SET SQL_LOG_BIN=0;
CREATE USER repl@'%';
GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'repl@123';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl@123' FOR CHANNEL 'group_replication_recovery';
-- 2.6 GR ：(root@localhost) [(none)]>
INSTALL PLUGIN group_replication SONAME 'group_replication.dll';
--Linux ：group_replication.so
-- ：
show plugins;
SELECT * FROM information_schema.plugins WHERE PLUGIN_NAME LIKE '%group%'\G
*************************** 1. row ***************************
PLUGIN_NAME: group_replication
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: GROUP REPLICATION
PLUGIN_TYPE_VERSION: 1.1
PLUGIN_LIBRARY: group_replication.so
PLUGIN_LIBRARY_VERSION: 1.7
PLUGIN_AUTHOR: Oracle
PLUGIN_DESCRIPTION: Group Replication (1.0.0)
PLUGIN_LICENSE: GPL
LOAD_OPTION: ON
1 row in set (0.00 sec)
-- 2.7 ：(root@localhost) [(none)]>
-- GLOBAL group_replication_bootstrap_group=ON
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
-- group_replication_bootstrap_group OFF
SET GLOBAL group_replication_bootstrap_group=OFF;
-- ， member_id @@server_uuid
SELECT * FROM performance_schema.replication_group_members;
+-------------------------- -+---------- -+-------------+-------------+-------------- +
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+-------------------------- -+---------- -+-------------+-------------+-------------- +
| group_replication_applier | | | NULL | OFFLINE |
+-------------------------- -+---------- -+-------------+-------------+-------------- +
1 row in set (0.00 sec)
+-------------------------- -+-------------------------------------- +-------------+-------------+-------------- +
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+-------------------------- -+-------------------------------------- +-------------+-------------+-------------- +
| group_replication_applier | 3dc9cf4a-ec61-11e6-bdf3-000c297f23b7 | sAno1y | 3306 | ONLINE |
+-------------------------- -+-------------------------------------- +-------------+-------------+-------------- +
1 row in set (0.00 sec)
-- （ ， ）：
CREATE DATABASE test;
USE test;
CREATE TABLE tb_test(id int PRIMARY KEY, name varchar(20)) CHARACTER SET utf8;
...
SELECT * FROM test.tb_test;
-- 3 （3307）
-- 3.1 3307 ， 3306 3307 ， loose-group_replication_local_address 3306 3307：
-- 3.2 3307 ：
mysqld -- defaults-file=D:/mysql/mgr/3307/my.ini -- initialize-insecure
-- 3.3 3307 （ ）：
mysqld -- install mysql3307 -- defaults-file=D:/mysql/mgr/3307/my.ini
-- 3.4 MySQL Client 3307 ：
mysql -uroot -p -P3307
mysqladmin -uroot -p -P3307 password 123asd
-- 3.5 3306 ：
SET SQL_LOG_BIN=0;
CREATE USER repl@'%';
GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'repl@123';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl@123' FOR CHANNEL 'group_replication_recovery';
-- 3.6 3307 GR ：
INSTALL PLUGIN group_replication SONAME 'group_replication.dll';
-- 3.7 （ ， ， ， ）：
SET GLOBAL group_replication_allow_local_disjoint_gtids_join=ON;
START GROUP_REPLICATION;
-- ：
SELECT * FROM performance_schema.replication_group_members;
+-------------------------- -+-------------------------------------- +-------------+-------------+-------------- +
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+-------------------------- -+-------------------------------------- +-------------+-------------+-------------- +
| group_replication_applier | 3dc9cf4a-ec61-11e6-bdf3-000c297f23b7 | sAno1y | 3306 | ONLINE |
| group_replication_applier | 6bddae0d-eca1-11e6-b9d5-000c297f23b7 | sAno1y | 3307 | RECOVERING |
+-------------------------- -+-------------------------------------- +-------------+-------------+-------------- +
2 rows in set (0.00 sec)
-- ， ONLINE 。
-- ， ：
STOP GROUP_REPLICATION;
START GROUP_REPLICATION;
-- 3307 ：
SELECT * FROM test.tb_test;
-- 4 3308 ：
-- 4.1 3308 ： ，3306 3308
-- 4.2 3308 ：
mysqld -- defaults-file=D:/mysql/mgr/3308/my.ini -- initialize-insecure
-- 4.3 3308 （ ）：
mysqld -- install mysql3308 -- defaults-file=D:/mysql/mgr/3308/my.ini
-- 4.4 3308 ：
mysql -uroot -p -P3308
mysqladmin -uroot -p -P3308 password 123asd
-- 4.5 3308 ：
SET SQL_LOG_BIN=0;
CREATE USER repl@'%';
GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'repl@123';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl@123' FOR CHANNEL 'group_replication_recovery';
-- 4.6 3308 GR ，
INSTALL PLUGIN group_replication SONAME 'group_replication.dll';
-- 4.7 ：
SET GLOBAL group_replication_allow_local_disjoint_gtids_join=ON;
START GROUP_REPLICATION;
-- ：
SELECT * FROM performance_schema.replication_group_members;
+-------------------------- -+-------------------------------------- +-------------+-------------+-------------- +
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+-------------------------- -+-------------------------------------- +-------------+-------------+-------------- +
| group_replication_applier | 3dc9cf4a-ec61-11e6-bdf3-000c297f23b7 | sAno1y | 3306 | ONLINE |
| group_replication_applier | 6bddae0d-eca1-11e6-b9d5-000c297f23b7 | sAno1y | 3307 | ONLINE |
| group_replication_applier | 6ce8c980-ed4a-11e6-bf00-000c297f23b7 | sAno1y | 3308 | ONLINE |
+-------------------------- -+-------------------------------------- +-------------+-------------+-------------- +
3 rows in set (0.00 sec)
-- 4.8 3308 3306 apply ：
SELECT * FROM test.tb_test;
-- 、 ， MGR...
--5 MGR
-- read_only super-read-only
SELECT @@read_only, @@super_read_only;
--
SELECT * FROM performance_schema.replication_group_members;
-- primary， ：
SELECT
b.member_id, b.member_host, b.member_port
FROM performance_schema.global_status a
INNER JOIN performance_schema.replication_group_members b
ON a.variable_value = b.member_id
WHERE a.variable_name= 'group_replication_primary_member';