当前位置：Gxlcms > 数据库问题 > 实践 Mysql Group Replication 组复制

实践 Mysql Group Replication 组复制

时间：2021-07-01 10:21:17 帮助过：12人阅读 

[mysqld]

# server configuration
datadir=/usr/local/data/s1
basedir=/usr/local/mysql-5.7/

port=24801
socket=/usr/local/data/s1/s1.sock

server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
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=FALSE
loose-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 rpl_user@‘%‘;
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@‘%‘ 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 test
Database changed
mysql> 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 configuration
datadir=/usr/local/data/s1
basedir=/usr/local/mysql-5.7/

port=24802
socket=/usr/local/data/s1/s1.sock

server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-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= off
loose-group_replication_single_primary_mode=FALSE
loose-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 rpl_user@‘%‘;
Query OK, 0 rows affected (0,00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@‘%‘ 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 configuration
datadir=/usr/local/data/s3
basedir=/usr/local/mysql-5.7/

port=24803
socket=/usr/local/data/s3/s3.sock

server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-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= off
loose-group_replication_single_primary_mode=FALSE
loose-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 rpl_user@‘%‘;
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@‘%‘ 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 test
mysql> 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 test
mysql> select * from t1;
+----+---------+
| c1 | c2      |
+----+---------+
|  1 | Luis    |
|  2 | s3 test |
+----+---------+
2 rows in set (0.00 sec)

复制成功，group replication 配置完成

 

实践 Mysql Group Replication 组复制

标签：wps   oba   man   code   efault   iii   epo   cdn   icc   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！