實踐 Mysql Group Replication 組複製

標籤：wps   oba   man   code   efault   iii   epo   cdn   icc   

實踐過程：

1. 在一台伺服器上安裝3個MySQL（s1,s2,s3）

2. 配置s1，啟動 Group Replication

3. 配置s2，添加到組中

4. 配置s3，添加到組中

5. 測試

內容比較長，可能不方便實際操作，我也做了一個PDF版本，您可以下載查看，發送訊息 ‘gr‘ 會自動回複

詳細配置過程（1）下載 mysql-5.7.17

https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

（2）安裝

解壓

tar zxf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gzmv mysql-5.7.17-linux-glibc2.5-x86_64 /usr/local/mysql-5.7cd /usr/local

初始化3個mysql（s1、s2、s3）

mkdir datamysql-5.7/bin/mysqld --initialize-insecure --basedir=$PWD/mysql-5.7 --datadir=$PWD/data/s1mysql-5.7/bin/mysqld --initialize-insecure --basedir=$PWD/mysql-5.7 --datadir=$PWD/data/s2mysql-5.7/bin/mysqld --initialize-insecure --basedir=$PWD/mysql-5.7 --datadir=$PWD/data/s3

（3）建立mysql使用者

groupadd mysql5.7useradd -g mysql5.7 mysql5.7chown -R mysql5.7:mysql5.7 /usr/local/mysql-5.7chown -R mysql5.7:mysql5.7 /usr/local/datasu mysql5.7

（4）配置 s1 啟動 group replication

建立設定檔 data/s1/s1.cnf，內容：

[mysqld]# server configurationdatadir=/usr/local/data/s1basedir=/usr/local/mysql-5.7/port=24801socket=/usr/local/data/s1/s1.sockserver_id=1gtid_mode=ONenforce_gtid_consistency=ONmaster_info_repository=TABLErelay_log_info_repository=TABLEbinlog_checksum=NONElog_slave_updates=ONlog_bin=binlogbinlog_format=ROWtransaction_write_set_extraction=XXHASH64loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"loose-group_replication_start_on_boot=offloose-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= offloose-group_replication_single_primary_mode=FALSEloose-group_replication_enforce_update_everywhere_checks= TRUE

啟動 s1

nohup mysql-5.7/bin/mysqld --defaults-file=data/s1/s1.cnf >data/s1/nohup.out 2>&1 &

登入 s1

mysql-5.7/bin/mysql -uroot -h127.0.0.1 -P24801 --skip-password

修改 root 登入密碼

mysql> ALTER USER ‘root‘@‘localhost‘ IDENTIFIED BY ‘A123456‘;

建立用於複製的使用者

mysql> SET SQL_LOG_BIN=0;mysql> CREATE USER [email protected]‘%‘;mysql> GRANT REPLICATION SLAVE ON *.* TO [email protected]‘%‘ IDENTIFIED BY ‘rpl_pass‘;mysql> FLUSH PRIVILEGES;mysql> SET SQL_LOG_BIN=1;mysql> CHANGE MASTER TO MASTER_USER=‘rpl_user‘, MASTER_PASSWORD=‘rpl_pass‘ FOR CHANNEL ‘group_replication_recovery‘;

安裝組複製外掛程式

mysql> INSTALL PLUGIN group_replication SONAME ‘group_replication.so‘;

檢驗

mysql> SHOW PLUGINS;

安裝成功的話，在結果資訊底部會看到 group_replication 的記錄

啟動組複製

mysql> SET GLOBAL group_replication_bootstrap_group=ON;mysql> START GROUP_REPLICATION;mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

查看群組成員

mysql> SELECT * FROM performance_schema.replication_group_members;

 

s1 配置完成了，現在建立一個測試表，用來驗證之後添加新成員時複製是否成功

mysql> CREATE DATABASE test;Query OK, 1 row affected (0,00 sec)mysql> use testDatabase changedmysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);Query OK, 0 rows affected (0,00 sec)mysql> INSERT INTO t1 VALUES (1, ‘Luis‘);Query OK, 1 row affected (0,01 sec)mysql> SELECT * FROM t1;+----+------+| c1 | c2   |+----+------+|  1 | Luis |+----+------+

（5）向複製組中添加 s2

建立s2的設定檔 data/s2/s2.cnf，內容：

[mysqld]# server configurationdatadir=/usr/local/data/s1basedir=/usr/local/mysql-5.7/port=24802socket=/usr/local/data/s1/s1.sockserver_id=2gtid_mode=ONenforce_gtid_consistency=ONmaster_info_repository=TABLErelay_log_info_repository=TABLEbinlog_checksum=NONElog_slave_updates=ONlog_bin=binlogbinlog_format=ROWtransaction_write_set_extraction=XXHASH64loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"loose-group_replication_start_on_boot=offloose-group_replication_local_address= "127.0.0.1:24902"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= offloose-group_replication_single_primary_mode=FALSEloose-group_replication_enforce_update_everywhere_checks= TRUE

啟動s2

nohup mysql-5.7/bin/mysqld --defaults-file=data/s2/s2.cnf >data/s2/nohup.out 2>&1 &

登入到s2

mysql-5.7/bin/mysql -uroot -h127.0.0.1 -P24802 --skip-password

執行以下命令，添加複製使用者

mysql> SET SQL_LOG_BIN=0;Query OK, 0 rows affected (0,00 sec)mysql> CREATE USER [email protected]‘%‘;Query OK, 0 rows affected (0,00 sec)mysql> GRANT REPLICATION SLAVE ON *.* TO [email protected]‘%‘ IDENTIFIED BY ‘rpl_pass‘;Query OK, 0 rows affected, 1 warning (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=‘rpl_user‘, MASTER_PASSWORD=‘rpl_pass‘ FOR CHANNEL ‘group_replication_recovery‘;Query OK, 0 rows affected, 2 warnings (0,01 sec)

添加組複製外掛程式

mysql> INSTALL PLUGIN group_replication SONAME ‘group_replication.so‘;Query OK, 0 rows affected (0,01 sec)

啟動複製

mysql> START GROUP_REPLICATION;Query OK, 0 rows affected (44,88 sec)

查看現在組中成員資訊

mysql> SELECT * FROM performance_schema.replication_group_members;

 

可以看到2個成員了，s2 添加成功

查詢一下測試表，看資料是否複製過來了

mysql> SHOW DATABASES LIKE ‘test‘;+-----------------+| Database (test) |+-----------------+| test            |+-----------------+1 row in set (0,00 sec)mysql> SELECT * FROM test.t1;+----+------+| c1 | c2   |+----+------+|  1 | Luis |+----+------+1 row in set (0,00 sec)

（6）添加第3個mysql執行個體 s3

建立設定檔 data/s3/s3.cnf

[mysqld]# server configurationdatadir=/usr/local/data/s3basedir=/usr/local/mysql-5.7/port=24803socket=/usr/local/data/s3/s3.sockserver_id=3gtid_mode=ONenforce_gtid_consistency=ONmaster_info_repository=TABLErelay_log_info_repository=TABLEbinlog_checksum=NONElog_slave_updates=ONlog_bin=binlogbinlog_format=ROWtransaction_write_set_extraction=XXHASH64loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"loose-group_replication_start_on_boot=offloose-group_replication_local_address= "127.0.0.1:24903"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= offloose-group_replication_single_primary_mode=FALSEloose-group_replication_enforce_update_everywhere_checks= TRUE

啟動s3

nohup mysql-5.7/bin/mysqld --defaults-file=data/s3/s3.cnf >data/s3/nohup.out 2>&1 &

登入s3

mysql-5.7/bin/mysql -uroot -h127.0.0.1 -P24803 --skip-password

建立複製使用者

mysql> SET SQL_LOG_BIN=0;mysql> CREATE USER [email protected]‘%‘;mysql> GRANT REPLICATION SLAVE ON *.* TO [email protected]‘%‘ IDENTIFIED BY ‘rpl_pass‘;mysql> FLUSH PRIVILEGES;mysql> SET SQL_LOG_BIN=1;mysql> CHANGE MASTER TO MASTER_USER=‘rpl_user‘, MASTER_PASSWORD=‘rpl_pass‘ FOR CHANNEL ‘group_replication_recovery‘;

安裝組複製外掛程式，並啟動

INSTALL PLUGIN group_replication SONAME ‘group_replication.so‘;START GROUP_REPLICATION;

查看群組成員

mysql> SELECT * FROM performance_schema.replication_group_members;

 

已經3個成員，s3成功加入了

看一下資料是否複製過來了

mysql> SHOW DATABASES LIKE ‘test‘;+-----------------+| Database (test) |+-----------------+| test            |+-----------------+1 row in set (0,00 sec)mysql> SELECT * FROM test.t1;+----+------+| c1 | c2   |+----+------+|  1 | Luis |+----+------+1 row in set (0,00 sec)

沒問題

（7）s3中測試插入資料

s3中執行

mysql> use testmysql> INSERT INTO t1 VALUES (2, ‘s3 test‘);mysql> select * from t1;+----+---------+| c1 | c2      |+----+---------+|  1 | Luis    ||  2 | s3 test |+----+---------+2 rows in set (0.00 sec)

登入s1查看一下資料是否複製成功

mysql-5.7/bin/mysql -uroot -h127.0.0.1 -P24801 -pA123456

查詢

mysql> use testmysql> select * from t1;+----+---------+| c1 | c2      |+----+---------+|  1 | Luis    ||  2 | s3 test |+----+---------+2 rows in set (0.00 sec)

複製成功，group replication 配置完成

 

實踐 Mysql Group Replication 組複製