MySQL MGR-组复制安装配置

MGR介绍

组复制是基于分布式一致性协议Paxos实现数据最终一致性的MySQL插件,通过这种插件可以实现弹性、高可用、容错复制拓扑结构。
MySQL组复制提供分布式状态机复制,在服务器之间具有强协调。当数据库服务器是属于同一组时,组复制机制可以自动协调它们。该组可以在具有自动选择新主库功能的单主模式下操作,这种情况下一个组只有主节点才可以做写操作。或者,对于更高级的用户,该组可以以多主模式部署,即多个节点都可以做写操作,即使它们是同时发过来的写请求。不过这种情况下,应用层会有部分额外的限制。

1.数据一致性保证

对于只读(RO)事务,组间实例无需进行通讯,就可以处理事务;但是对于读写(RW)事务,需要经过组内大多数节点决议,来决定该事务是否可以提交。

2.事务并发冲突处理

在高并发的多写模式(MGR的一种运行模式)下,节点间事务的提交可能会产生冲突,比如,两个不同的事务在两个节点上操作了同一行数据,这个时候就会产生冲突。首先,Group Replication(GR)能够识别到这个冲突,然后对此的处理采用乐观策略:依赖事务提交的时间先后顺序,先发起提交的节点能够正确提交,而后面的提交,会失败。

3.节点故障自动检测

GR自带故障检测机制,可以识别组内成员是否挂掉(组内节点心跳检测)。当一个节点失效,将由其他节点决定是否将这个失效的节点从group里面剔除。当然,这是建立在满足大多数节点存活并且可以进行决议的前提上的。

4.组成员自动管理

GR自动维护组内节点的状态(在线?存活?挂掉?),对于失效的节点,由其他节点决定是否剔除。对于新加入的节点,GR会自动维护它的视图与其他节点的视图保持一致。
关于集群内节点的状态,可以通performance_schema.replication_group_members表查看。

5.容错能力

GR基于分布式一致性算法实现,一个组允许部分节点挂掉,只要保证大多数节点仍然存活并且之间的通讯是没有问题的,那么这个组对外仍然能够提供服务。

6.两种模式

GR提供了single-primary和multi-primary两种模式。single-primary模式下,组内只有一个节点负责写入,读可以从任意一个节点读取,组内数据保持最终一致;而multi-primary模式即为多写方案,即写操作会下发到组内所有节点,组内所有节点同时可读可写,该模式也是能够保证组内数据最终一致性。

注意,一个GR的所有节点必须配置使用同一种模式,不可混用。比如说A、B、C三个节点组成一个GR组,那么要么都运行在single-primary模式下,要么都运行在multi-primary模式下。

由my.cnf里的配置项group_replication_single_primary_mode来配置节点到底是运行在single-primary模式还是multi-primary模式

MGR配置

实验环境:(虚拟机rhel6.5.x86_64)
server1:192.168.67.153
server2:192.168.67.152
server3:192.168.67.151

server1,server2,server3都停止mysql服务


server1:

1.清空缓存

[root@server1 ~]# cd /var/lib/mysql

[root@server1 mysql]# rm -fr *

2.修改配置文件

[root@server1 mysql]# vim /etc/my.cnf

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

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="8e2b8a90-9c7d-11e8-969e-525400efdb91"  ##UUID

loose-group_replication_start_on_boot=off

loose-group_replication_local_address="192.168.67.153:24901"

loose-group_replication_group_seeds="192.168.67.153:24901,192.168.67.152:24901,192.168.67.151:24901"

loose-group_replication_bootstrap_group= off

loose-group_replication_single_primary_mode=off

loose-group_replication_enforce_update_everywhere_checks=on

loose-group_replication_ip_whitelist="192.168.67.0/24,127.0.0.1/8"

 

3.初始化mysql 

[root@server1 mysql]# /etc/init.d/mysqld start

[root@server1 mysql]# cat /var/log/mysqld.log |greppassword   #查看原始密码

[root@server1 mysql]# mysql -p                #利用初始密码登陆mysql

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 5

Server version: 5.7.18-log

 

Copyright (c) 2000, 2016, Oracle and/orits affiliates. All rights reserved.

 

Oracle is a registered trademark ofOracle Corporation and/or its

affiliates. Other names may betrademarks of their respective

owners.

 

Type 'help;' or'\h' for help. Type '\c' to clear thecurrent input statement.

 

4.组复制配置

mysql> alter user root@localhost identified by'Westos+123';   #更改密码

Query OK, 0 rows affected (0.15 sec)

 

mysql> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| sys               |

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

4 rows in set (0.00 sec)

 

mysql> set sql_log_bin=0;        #关闭二进制日志

Query OK, 0 rows affected (0.00 sec)

 

mysql> grant replication slave on *.* torpl_user@'%' identified by'Westos+123';       #授权

Query OK, 0 rows affected, 1 warning (0.00 sec)

 

mysql> flush privileges;        #刷新

Query OK, 0 rows affected (0.00 sec)

 

mysql> reset master;            #重置

Query OK, 0 rows affected (0.32 sec)

 

mysql> set sql_log_bin=1;        #开启二进制日志

Query OK, 0 rows affected (0.00 sec)

 

mysql> change master tomaster_user='rpl_user',master_password='Westos+123'for channel 'group_replication_recovery';

Query OK, 0 rows affected, 2 warnings (0.75 sec)

 

mysql> install plugin group_replication soname'group_replication.so';      #安装组复制插件

Query OK, 0 rows affected (0.59 sec)

 

mysql> show plugins;

 

mysql> set globalgroup_replication_bootstrap_group=ON;

Query OK, 0 rows affected (0.00 sec)

 

mysql> stop group_replication;

Query OK, 0 rows affected (0.00 sec)

 

mysql> set globalgroup_replication_ip_whitelist='192.168.67.0/24,127.0.0.1/8';

Query OK, 0 rows affected (0.00 sec)

 

mysql> start group_replication;           #开启组复制

Query OK, 0 rows affected (1.54 sec)

 

mysql> select * fromperformance_schema.replication_group_members;        #查看组复制信息

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

| CHANNEL_NAME             | MEMBER_ID                           | MEMBER_HOST | MEMBER_PORT |MEMBER_STATE |

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

| group_replication_applier |47a0c94d-9c7c-11e8-9f77-525400cfed2c | server1     |       3306 | ONLINE       |

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

1 row in set (0.00 sec)

server1组复制信息,状态为ONLINE

测试:

mysql> create database test;

Query OK, 1 row affected (0.14 sec)

 

mysql> use test;

Database changed

mysql> create table t1 (c1 int primary key,c2 text notnull);

Query OK, 0 rows affected (0.61 sec)

 

mysql> insert into t1 values(1,'Luis');

Query OK, 1 row affected (0.16 sec)

 

mysql> select * from t1;

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

| c1 | c2   |

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

|  1 | Luis |

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

1 row in set (0.00 sec)

 

server2

1.清空缓存

[root@server2 ~]# cd /var/lib/mysql

[root@server2 mysql]# rm -fr *

2.修改配置文件

[root@server2 mysql]# vim /etc/my.cnf

 server-id=2            #修改为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="8e2b8a90-9c7d-11e8-969e-525400efdb91"

 loose-group_replication_start_on_boot=off

 loose-group_replication_local_address="192.168.67.152:24901"   #更改ip

 loose-group_replication_group_seeds="192.168.67.153:24901,192.168.67.152:24901,192.168.67.151:24901"

 loose-group_replication_bootstrap_group= off

 loose-group_replication_single_primary_mode=off

 loose-group_replication_enforce_update_everywhere_checks=on

 loose-group_replication_ip_whitelist="192.168.67.0/24,127.0.0.1/8"

 

3.初始化mysql

[root@server2 mysql]# /etc/init.d/mysqld start

[root@server2 mysql]# cat /var/log/mysqld.log |greppassword

 

[root@server2 ~]# mysql -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 15

Server version: 5.7.18-log

 

Copyright (c) 2000, 2016, Oracle and/orits affiliates. All rights reserved.

 

Oracle is a registered trademark ofOracle Corporation and/or its

affiliates. Other names may betrademarks of their respective

owners.

Type 'help;' or '\h'for help. Type '\c' to clear the current input statement.

4.组复制配置

mysql> alter user root@localhostidentified by 'Westos+123';

Query OK, 0 rows affected (0.63 sec)

mysql> show databases;

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

| Database          |

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

| information_schema |

| mysql             |

| performance_schema |

| sys               |

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

4 rows in set (0.00 sec)

mysql> set sql_log_bin=0;

Query OK, 0 rows affected (0.00 sec)

 

mysql> grant replication slave on*.* to rpl_user@'%' identified by 'Westos+123';

Query OK, 0 rows affected, 1 warning(0.00 sec)

 

mysql> reset master;

Query OK, 0 rows affected (0.58 sec)

 

mysql> set sql_log_bin=1;

Query OK, 0 rows affected (0.00 sec)

 

mysql> change master tomaster_user='rpl_user',master_password='Westos+123'for channel 'group_replication_recovery';

Query OK, 0 rows affected, 2 warnings(0.80 sec)

 

mysql> install plugingroup_replication soname 'group_replication.so';

Query OK, 0 rows affected (0.58 sec)

 

mysql> start group_replication;

Query OK, 0 rows affected (6.75 sec)

 

mysql> select * fromperformance_schema.replication_group_members;

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

| CHANNEL_NAME             | MEMBER_ID                           | MEMBER_HOST |MEMBER_PORT | MEMBER_STATE |

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

| group_replication_applier |25d01958-9c8b-11e8-ba1f-525400fd9fef | server2     |       3306 | ONLINE       |

| group_replication_applier |dd9bf855-9c89-11e8-b06b-525400cfed2c | server1     |       3306 | ONLINE       |

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

2 rows in set (0.00 sec)

server1,server2组复制信息,状态为ONLINE


server3
与server2配置相同

1.清空缓存

[root@server3 ~]# cd /var/lib/mysql

[root@server3 mysql]# rm -fr *

 

2.修改配置文件

 

[root@server2 mysql]# vim /etc/my.cnf

 server-id=3            #修改为3

 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="8e2b8a90-9c7d-11e8-969e-525400efdb91"

 loose-group_replication_start_on_boot=off

 loose-group_replication_local_address="192.168.67.151:24901"   #更改ip

 loose-group_replication_group_seeds="192.168.67.153:24901,192.168.67.152:24901,192.168.67.151:24901"

 loose-group_replication_bootstrap_group= off

 loose-group_replication_single_primary_mode=off

 loose-group_replication_enforce_update_everywhere_checks=on

 loose-group_replication_ip_whitelist="192.168.67.0/24,127.0.0.1/8"

 

[root@server2 mysql]# /etc/init.d/mysqld start

 

3.初始化mysql

 

[root@server3 mysql]# cat /var/log/mysqld.log |greppassword

[root@server3 ~]# mysql -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ;or \g.

Your MySQL connection id is 4

Server version: 5.7.18-log

 

Copyright (c) 2000, 2016, Oracle and/or its affiliates.All rights reserved.

 

Oracle is a registered trademark of Oracle Corporationand/or its

affiliates. Other names may be trademarks of theirrespective

owners.

 

Type 'help;' or '\h'for help. Type '\c' to clear the current input statement.

 

4.组复制配置

mysql> alter user root@localhost identified by'Westos+123';

Query OK, 0 rows affected (0.11 sec)

 

mysql> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| sys               |

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

4 rows in set (0.00 sec)

 

mysql> set sql_log_bin=0;

Query OK, 0 rows affected (0.00 sec)

 

mysql> grant replication slave on *.* torpl_user@'%' identified by 'Westos+123';

Query OK, 0 rows affected, 1 warning (0.00 sec)

 

mysql> reset master;

Query OK, 0 rows affected (0.44 sec)

 

mysql> set sql_log_bin=1;

Query OK, 0 rows affected (0.00 sec)

 

mysql> change master to master_user='rpl_user',master_password='Westos+123'for channel 'group_replication_recovery';

Query OK, 0 rows affected, 2 warnings (0.90 sec)

 

mysql> install plugin group_replication soname'group_replication.so';

Query OK, 0 rows affected (0.29 sec)

 

mysql> start group_replication;

Query OK, 0 rows affected (3.40 sec)

 

mysql> select * fromperformance_schema.replication_group_members;

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

| CHANNEL_NAME             | MEMBER_ID                           | MEMBER_HOST | MEMBER_PORT |MEMBER_STATE |

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

| group_replication_applier |25d01958-9c8b-11e8-ba1f-525400fd9fef | server2     |       3306 | ONLINE       |

| group_replication_applier |36acd5f1-9c8c-11e8-b1d8-525400efdb91 | server3     |       3306 | ONLINE       |

| group_replication_applier |dd9bf855-9c89-11e8-b06b-525400cfed2c | server1     |       3306 | ONLINE       |

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

server1,server2,server3组复制信息,状态为ONLINE

3 rows in set (0.00 sec)

 

MYSQL组复制配置成功

 



分割线
打赏
YWSOS.COM 平台代运维解决方案
 评论
 发表评论
姓   名:

Powered by AKCMS