欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MySQL Group Replication 多机多实例安装配置

程序员文章站 2022-07-13 08:59:24
...

1、配置hosts

 

172.16.90.39 node3-01 node3-01.novalocal
172.16.90.40 node3-02 node3-02.novalocal
172.16.90.41 node3-03 node3-03.novalocal

 

 

2、在每个节点上创建数据库实例

 

mkdir -p /data/mysql/{data,logs}
cd /data/mysql
chown -R mysql:mysql *
cd /usr/local/mysql/
chown -R mysql:mysql *
bin/mysqld --initialize-insecure --basedir=/usr/local/mysql/ --datadir=/data/mysql/data/

 

 

3、配置第一个节点:/etc/my.cnf

 

[mysqld]
#Server Configuration
basedir=/usr/local/mysql
datadir=/data/mysql/data
port = 3306
socket = /usr/local/mysql/mysql.sock
user=mysql


#Replication Framework
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=/data/mysql/logs/binlog
binlog_format=ROW 
binlog_row_image=minimal
relay-log=/data/mysql/logs/relay-bin

#Group Replication
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="0374cfa3-deae-11e6-b0fe-fa163e2d23ab"       
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "node3-01:24901"
loose-group_replication_group_seeds= "node3-01:24901,node3-02:24901,node3-03:24901"
loose-group_replication_bootstrap_group= off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks= TRUE

 参数配置中除了如下的参数在各节点不一样外,其他配置都一样:server_id、loose- group_replication_local_address

 

 

4、启动第一个节点

/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf

 

5、登录第一个节点

mysql -h127.0.0.1 -P3306 -uroot --skip-password
#登录后修改默认密码
SET PASSWORD = PASSWORD('root');
flush privileges;

 

6、创建group replication所需要的用户

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';

 

7、安装组复制插件

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.05 sec)
mysql> SHOW PLUGINS;
+----------------------------+----------+--------------------+
| Name                       | Status   | Type               |
+----------------------------+----------+--------------------+
| binlog                     | ACTIVE   | STORAGE ENGINE     |
...
| group_replication          | ACTIVE   | GROUP REPLICATION  |
+----------------------------+----------+--------------------+
45 rows in set (0.02 sec)

 

8、启动group replication

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
#查看组复制是否启动成功
SELECT * FROM performance_schema.replication_group_members;

 

9、配置第二个节点(仅列出和s1的不同之处)

server_id=2
loose-group_replication_local_address= "db2:24901"

 

10、启动第二个节点(实例)

/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf

 

11、登录第二个节点并修改默认密码

mysql -h127.0.0.1 -P3306 -uroot --skip-password
SET PASSWORD = PASSWORD('root');
flush privileges;

 

12、配置group replication需要的用户

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';

 

13、安装group replication插件

INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Show plugins;
+----------------------------+----------+--------------------+
| Name                       | Status   | Type               |
+----------------------------+----------+--------------------+
| binlog                     | ACTIVE   | STORAGE ENGINE     |
...
| group_replication          | ACTIVE   | GROUP REPLICATION  |
+----------------------------+----------+--------------------+
45 rows in set (0.02 sec)

 

14、将s2添加到组中

set global group_replication_allow_local_disjoint_gtids_join=ON;
start group_replication;
#查看改group replication情况
SELECT * FROM performance_schema.replication_group_members;

 

15、添加第三个节点

#操作同第二个节点,my.cnf的参数略有不同
#添加完成查看各节点的运行状态
SELECT * FROM performance_schema.replication_group_members;

 

16、主从模式(single-primary)下的配置

主从(单主)模式的相关配置除了下面两个配置外,其他和多主都相同

loose-group_replication_single_primary_mode=TRUE
loose-group_replication_enforce_update_everywhere_checks=FALSE

 

17、主从下查看primary_member

SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME= 'group_replication_primary_member';

 

18、mysql在java中的连接配置

多主模式:loadbalance
jdbc.url=jdbc:mysql:loadbalance://172.16.90.39:3306,172.16.90.40:3306,172.16.90.41:3306/ifaes_test?loadBalanceBlacklistTimeout=5000&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
主从模式:replication
jdbc.url=jdbc:mysql:replication://172.16.90.39:3306,172.16.90.40:3306,172.16.90.41:3306/ifaes_test?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true

 

相关标签: mysql