mysql 5.7搭建MGR多主模式

post by rocdk890 / 2019-7-31 17:18 Wednesday linux技术
  mysql有很多复制模式,今天给大家讲下mysql的组复制(MySQL Group Replication).
        系统:centos 7.x(64位)
软件版本:mysql 5.7.25
环境:
db1 10.0.2.246
db2 10.0.2.247
db3 10.0.2.248

1.安装mysql
这里不多说了,mysql都安装不来的,我觉得也不用看下面了.

2.配置db1(10.0.2.246)节点.
vim /etc/my.cnf
[client]
socket = /data/mysql/mysql.sock

[mysqld]
socket = /data/mysql/mysql.sock
pid-file = /data/mysql/mysql.pid
basedir = /usr/local/mysql
datadir = /data/mysql
log-error = /var/log/mysql/mysql-err.log

symbolic-links=0

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="5db40c3c-180c-11e9-afbf-005056ac6820"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "10.0.2.246:33061"
loose-group_replication_group_seeds= "10.0.2.246:33061,10.0.2.247:33061,10.0.2.248:33061"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode = off    
loose-group_replication_enforce_update_everywhere_checks = on

report_host=10.0.2.246

保存后重启mysql进程.

进mysql里进行配置:

#安装组复制插件
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

#重置master bin_log
mysql> reset master;

#关闭日志记录
mysql> SET SQL_LOG_BIN=0;

#创建复制用户并赋予权限
mysql> GRANT REPLICATION SLAVE ON *.* TO 'dbmysql'@'%' IDENTIFIED BY 'SOJV6kpjgE4L';

#开启日志记录
mysql> SET SQL_LOG_BIN=1;

#设置master
mysql> CHANGE MASTER TO MASTER_USER='dbmysql',MASTER_PASSWORD='SOJV6kpjgE4L' FOR CHANNEL 'group_replication_recovery';

#只在第一个节点执行这个步骤,设置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;

#查看组复制成员
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 38381da1-b36b-11e9-852e-000c2971fadb | 10.0.2.246  |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

3.配置db2(10.0.2.247)节点
vim /etc/my.cnf
[client]
socket = /data/mysql/mysql.sock

[mysqld]
socket = /data/mysql/mysql.sock
pid-file = /data/mysql/mysql.pid
basedir = /usr/local/mysql
datadir = /data/mysql
log-error = /var/log/mysql/mysql-err.log

symbolic-links=0

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="5db40c3c-180c-11e9-afbf-005056ac6820"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "10.0.2.247:33061"
loose-group_replication_group_seeds= "10.0.2.246:33061,10.0.2.247:33061,10.0.2.248:33061"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode = off
loose-group_replication_enforce_update_everywhere_checks = on

report_host=10.0.2.247

保存后重启mysql进程.

进mysql里进行配置:
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

mysql> reset master;

mysql> SET SQL_LOG_BIN=0;

mysql> GRANT REPLICATION SLAVE ON *.* TO 'dbmysql'@'%' IDENTIFIED BY 'SOJV6kpjgE4L';

mysql> SET SQL_LOG_BIN=1;

#这一步和db1节点的配置不一样
mysql>set global group_replication_allow_local_disjoint_gtids_join=ON;

mysql> start group_replication;

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 38381da1-b36b-11e9-852e-000c2971fadb | 10.0.2.246  |        3306 | ONLINE       |
| group_replication_applier | 4f6b96b7-b36b-11e9-9db8-000c2918e9ad | 10.0.2.247  |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

db3的配置步骤跟db2一样,这里我就不重复写了.只要在三台上看到下面的情况,就说明你已经成功了.
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 38381da1-b36b-11e9-852e-000c2971fadb | 10.0.2.246  |        3306 | ONLINE       |
| group_replication_applier | 4f6b96b7-b36b-11e9-9db8-000c2918e9ad | 10.0.2.247  |        3306 | ONLINE       |
| group_replication_applier | 9d969483-b361-11e9-9d62-000c29aeaeef | 10.0.2.248  |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

mysql> show variables like 'group_replication_single_primary_mode';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| group_replication_single_primary_mode | OFF   |
+---------------------------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'group_replication_enforce_update_everywhere_checks';
+----------------------------------------------------+-------+
| Variable_name                                      | Value |
+----------------------------------------------------+-------+
| group_replication_enforce_update_everywhere_checks | ON    |
+----------------------------------------------------+-------+
1 row in set (0.00 sec)

如果在mysql里查到group_replication_single_primary_mode是off,而group_replication_enforce_update_everywhere_checks是on,就表示你已经是多主模式了.
夜空- 本站版权
1、本站所有主题由该文章作者发表,该文章作者与夜空享有文章相关版权
2、其他单位或个人使用、转载或引用本文时必须同时征得该文章作者和夜空的同意
3、本帖部分内容转载自其它媒体,但并不代表本站赞同其观点和对其真实性负责
4、如本帖侵犯到任何版权问题,请立即告知本站,本站将及时予与删除并致以最深的歉意
5、原文链接:blog.slogra.com/post-761.html

标签: 配置 mysql 搭建 MGR 多主

评论: