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