1. 首页
  2. 文章中心
  3. Mysql

MySQL Group Replication [Single-Primary Mode]单主模式搭建安装环境

63 阅读 0 评论
我是靠谱客的博主 高大美女，最近开发中收集的这篇文章主要介绍MySQL Group Replication [Single-Primary Mode]单主模式搭建安装环境，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

安装环境

配置Single-Primary Mode复制单主模式，将PS02设置为主，PS03,PS04为从库

1.首先安装三台MYSQL

主机名IPserver_id端口
PS02192.168.199.1521523306
PS03192.168.199.1531533306
PS04192.168.199.1541543306

之后依次启动PS02,PS03,PS04

2.设置hostname和IP的映射

vim /etc/hosts
192.168.199.152 PS02
192.168.199.153 PS03
192.168.199.154 PS04

3.配置文件

PS2 my.cnf

[client]
port            = 3306
socket          = /tmp/mysql.sock

[mysql]
prompt="\u@\h:\p  [\d]>
#pager="less -i -n -S"
##tee=/home/mysql/query.log
no-auto-rehash

[mysqld]

#misc
user = mysql
basedir = /usr/local/percona-server
datadir = /usr/local/percona-server/data
port = 3306
socket = /tmp/mysql.sock
event_scheduler = 1
binlog_format = row
server-id = 152
log-bin = /usr/local/percona-server/data/mysql-bin
general_log=1
log-slave-updates
#
symbolic-links=0
binlog_checksum = NONE
slave-parallel-type=LOGICAL_CLOCK
slave-preserve-commit-order=1
#group_replication_ssl_mode=enabled

# Rpl Sync Master
#rpl_semi_sync_master_enabled=1
#rpl_semi_sync_master_timeout=1000

#gtid
gtid-mode=on
enforce-gtid-consistency=1
master-info-repository=table
relay-log-info-repository=table
binlog_row_image = full
#
slave_parallel_workers=4
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%

transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856'
loose-group_replication_start_on_boot = off
loose-group_replication_local_address = '192.168.199.152:33061'
loose-group_replication_group_seeds ='192.168.199.152:33061,192.168.199.153:33062,192.168.199.154:33063'
loose-group_replication_bootstrap_group = off

## EVENT
#event_scheduler=1
#binlog_rows_query_log_events=1
#log_bin_use_v1_row_events=1

[mysqld_safe]
malloc-lib=/usr/lib64/libjemalloc.so.1

PS03 my.cnf

[client]
port            = 3306
socket          = /tmp/mysql.sock

[mysql]
prompt="\u@\h:\p  [\d]>
#pager="less -i -n -S"
##tee=/home/mysql/query.log
no-auto-rehash

[mysqld]

#misc
user = mysql
basedir = /usr/local/percona-server
datadir = /usr/local/percona-server/data
port = 3306
socket = /tmp/mysql.sock
event_scheduler = 0
binlog_format = row
server-id = 153
log-bin = /usr/local/percona-server/data/mysql-bin
general_log=1
log-slave-updates
#
symbolic-links=0
binlog_checksum = NONE
slave-parallel-type=LOGICAL_CLOCK
slave-preserve-commit-order=1

# Rpl Sync Master
#rpl_semi_sync_master_enabled=1
#rpl_semi_sync_master_timeout=1000

#gtid
gtid-mode=on
enforce-gtid-consistency=1
master-info-repository=table
relay-log-info-repository=table
binlog_row_image = full
#
slave_parallel_workers=4
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%

transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856'
loose-group_replication_start_on_boot = off
loose-group_replication_local_address = '192.168.199.153:33062'
loose-group_replication_group_seeds ='192.168.199.152:33061,192.168.199.153:33062,192.168.199.154:33063'
loose-group_replication_bootstrap_group = off

## EVENT
#event_scheduler=1
"/etc/my.cnf" 68L, 1657C                                                                                                                                                   53,1          Top
# Rpl Sync Master
#rpl_semi_sync_master_enabled=1
#rpl_semi_sync_master_timeout=1000

#gtid
gtid-mode=on
enforce-gtid-consistency=1
master-info-repository=table
relay-log-info-repository=table
binlog_row_image = full
#
slave_parallel_workers=4
#auto_increment_increment=2
#auto_increment_offset=1
#
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%

transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856'
loose-group_replication_start_on_boot = off
loose-group_replication_local_address = '192.168.199.153:33062'
loose-group_replication_group_seeds ='192.168.199.152:33061,192.168.199.153:33062,192.168.199.154:33063'
loose-group_replication_bootstrap_group = off

## EVENT
#event_scheduler=1
#binlog_rows_query_log_events=1
#log_bin_use_v1_row_events=1

[mysqld_safe]
malloc-lib=/usr/lib64/libjemalloc.so.1

PS04 my.cnf

[client]
port            = 3306
socket          = /tmp/mysql.sock

[mysql]
prompt="\u@\h:\p  [\d]>
#pager="less -i -n -S"
##tee=/home/mysql/query.log
no-auto-rehash

[mysqld]

#misc
user = mysql
basedir = /usr/local/percona-server
datadir = /usr/local/percona-server/data
port = 3306
socket = /tmp/mysql.sock
event_scheduler = 0
binlog_format = row
server-id = 154
log-bin = /usr/local/percona-server/data/mysql-bin
general_log=1
log-slave-updates
#
symbolic-links=0
binlog_checksum = NONE
slave-parallel-type=LOGICAL_CLOCK
slave-preserve-commit-order=1

# Rpl Sync Master
#rpl_semi_sync_master_enabled=1
#rpl_semi_sync_master_timeout=1000

#gtid
gtid-mode=on
enforce-gtid-consistency=1
master-info-repository=table
relay-log-info-repository=table
binlog_row_image = full
#
slave_parallel_workers=4
#auto_increment_increment=2
#auto_increment_offset=1
#
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%

transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856'
loose-group_replication_start_on_boot = off
loose-group_replication_local_address = '192.168.199.154:33063'
loose-group_replication_group_seeds ='192.168.199.152:33061,192.168.199.153:33062,192.168.199.154:33063'
loose-group_replication_bootstrap_group = off

## EVENT
#event_scheduler=1
                                                                                                                                                                           63,1          Top
# Rpl Sync Master
#rpl_semi_sync_master_enabled=1
#rpl_semi_sync_master_timeout=1000

#gtid
gtid-mode=on
enforce-gtid-consistency=1
master-info-repository=table
relay-log-info-repository=table
binlog_row_image = full
#
slave_parallel_workers=4
#auto_increment_increment=2
#auto_increment_offset=1
#
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%

transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856'
loose-group_replication_start_on_boot = off
loose-group_replication_local_address = '192.168.199.154:33063'
loose-group_replication_group_seeds ='192.168.199.152:33061,192.168.199.153:33062,192.168.199.154:33063'
loose-group_replication_bootstrap_group = off

## EVENT
#event_scheduler=1
#binlog_rows_query_log_events=1
#log_bin_use_v1_row_events=1

[mysqld_safe]
malloc-lib=/usr/lib64/libjemalloc.so.1

4.配置Group Replication

4.1 PS02配置Group Replication

4.1.1 在PS02中创建账号

mysql> SET SQL_BIN_LOG=0;
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'slave';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_BIN_LOG=1;

4.1.2 构建group replication集群

mysql> CHANGE MASTER TO MASTER_USER='slave', MASTER_PASSWORD='slave' FOR CHANNEL 'group_replication_recovery';

4.1.3 安装group replication插件

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

4.1.4 查看是否有group replication组件

mysql>SHOW PLUGINS;
+-----------------------------+----------+--------------------+----------------------+---------+
| Name                        | Status   | Type               | Library              | License |
+-----------------------------+----------+--------------------+----------------------+---------+
| rpl_semi_sync_master        | ACTIVE   | REPLICATION        | semisync_master.so   | GPL     |
+-----------------------------+----------+--------------------+----------------------+---------+

4.1.5 启动PS02上的group replication服务

 -- 设置group_replication_bootstrap_group为ON是为了标示以后加入集群的服务器都以这台服务器为基准，以后加入的就不需要设置了
 mysql> set global group_replication_bootstrap_group=ON;
 -- 启动收个集群节点
 mysql> start group_replication;
 mysql> set global group_replication_bootstrap_group=OFF;

4.1.6 查看MGR的状态

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 18c01a46-df39-11e7-b98f-000c2989d7c7 | PS02        |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+

4.2 PS03配置Group Replication

4.2.1 在PS02中创建账号

mysql> SET SQL_BIN_LOG=0;
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'slave';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_BIN_LOG=1;

4.2.2 构建group replication集群

mysql> CHANGE MASTER TO MASTER_USER='slave', MASTER_PASSWORD='slave' FOR CHANNEL 'group_replication_recovery';

4.2.3 安装group replication插件

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

4.2.4 查看是否有group replication组件

mysql>SHOW PLUGINS;
+-----------------------------+----------+--------------------+----------------------+---------+
| Name                        | Status   | Type               | Library              | License |
+-----------------------------+----------+--------------------+----------------------+---------+
| rpl_semi_sync_master        | ACTIVE   | REPLICATION        | semisync_master.so   | GPL     |
+-----------------------------+----------+--------------------+----------------------+---------+

4.2.5 启动PS03上的group replication服务

 -- 兼容加入组
 mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
 -- 启动收个集群节点
 mysql> start group_replication;

4.2.6 查看PS02和PS03的MGR的状态
PS03:

>select * from performance_schema.replication_group_members;                                              
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 18c01a46-df39-11e7-b98f-000c2989d7c7 | PS02        |        3306 | ONLINE       |
| group_replication_applier | 97347859-dfe0-11e7-90cf-000c2911d0be | PS03        |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.01 sec)

PS02:

>select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 18c01a46-df39-11e7-b98f-000c2989d7c7 | PS02        |        3306 | ONLINE       |
| group_replication_applier | 97347859-dfe0-11e7-90cf-000c2911d0be | PS03        |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

4.3 PS04配置Group Replication

PS04配置和PS03一样…

5. 测试

创建数据库和表
1.PS02中创建数据库和表
PS02中创建数据库和表
PS03中查看数据库和表
PS03中查看数据库和表
没有问题
2.插入数据
PS02中插入数据
PS02中插入数据报错
报错：查看日志
这里写图片描述
前两条错误是test表没有使用InnoDB引擎，而且表中没有主键
后面一条错误是test1表没有主键
在组复制中，有一些必须的条件，表InnoDB引擎和表主键就是其中之二
3.将两个表改为Innodb引擎，并添加主键，再测试插入
这里写图片描述
这里写图片描述

最后

以上就是高大美女为你收集整理的MySQL Group Replication [Single-Primary Mode]单主模式搭建安装环境的全部内容，希望文章能够帮你解决MySQL Group Replication [Single-Primary Mode]单主模式搭建安装环境所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
点赞(42)

相关文章

MGR原理及集群搭建一、MySQL MGR演化二、MGR原理
MGR原理及集群搭建一、MySQL MGR演化二、MGR原理
Mysql Group Replication 简介及单主模式组复制配置【转】
Mysql Group Replication 简介及单主模式组复制配置【转】
MySQL复制介绍及MGR安装与配置MGR的安装遇到问题
MySQL复制介绍及MGR安装与配置MGR的安装遇到问题
MGR 架构 ~ DBA相关运维管理
MGR 架构 ~ DBA相关运维管理
MySQL Group Replication [Single-Primary Mode]单主模式搭建安装环境
MySQL Group Replication [Single-Primary Mode]单主模式搭建安装环境
mysql group replication 单写模式 (single-primary mode) 搭建
mysql group replication 单写模式 (single-primary mode) 搭建
MYSQL--架构--MGR--部署--03--配置MYSQL–架构–MGR–部署–03–配置1、配置说明2、单主配置3、多主配置
MYSQL--架构--MGR--部署--03--配置MYSQL–架构–MGR–部署–03–配置1、配置说明2、单主配置3、多主配置
Android 6.0 adb remount后没有写权限，提示Read-only file system
Android 6.0 adb remount后没有写权限，提示Read-only file system

评论列表共有 0 条评论

立即
投稿
返回
顶部