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;

+---------------------------+--------------------------------------+-------------+-------------+--------------+

+---------------------------+--------------------------------------+-------------+-------------+--------------+

+---------------------------+--------------------------------------+-------------+-------------+--------------+

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;

+---------------------------+--------------------------------------+-------------+-------------+--------------+

+---------------------------+--------------------------------------+-------------+-------------+--------------+

+---------------------------+--------------------------------------+-------------+-------------+--------------+

2 rows in set (0.00 sec)

db3的配置步骤跟db2一样,这里我就不重复写了.只要在三台上看到下面的情况,就说明你已经成功了.

+---------------------------+--------------------------------------+-------------+-------------+--------------+

+---------------------------+--------------------------------------+-------------+-------------+--------------+

+---------------------------+--------------------------------------+-------------+-------------+--------------+

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