mysql 5.7搭建MGR多主模式
mysql有很多复制模式,今天给大家讲下mysql的组复制(MySQL Group Replication).
系统:centos 7.x(64位)
系统: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,就表示你已经是多主模式了.
rocdk890
Michael
ade
西门堵车
朱祁镇
r1p
milton010
评论: