vlambda博客
学习文章列表

一、MySQL Group Replication

        mysql官方基于组复制概念并充分参考MariaDB Galera Cluster和Percona XtraDB Cluster结合而来的新的高可用集群架构。只支持5.7以上版本

mgr 优点：

高一致性，基于原生复制及paxos协议的组复制技术.

高容错性，有自动检测机制，当出现宕机后,会自动剔除问题节点,其他节点可以正常使用(类似zk集群),当不同节点产生资源争用冲突时,会按照先到先得处理,并且内置了自动化脑裂防护机制.

高扩展性，可随时在线新增和移除节点,会自动同步所有节点上状态，直到新节点和其他节点保持一致，自动维护新的组信息.

高灵活性，直接插件形式安装(5.7.17后自带.so插件),有单主模式和多主模式，单主模式下，只有主库可以读写,其他从库会加上super_read_only状态,只能读取不可写入,出现故障会自动选主.

缺点：目前不太稳定，太新有BUG（如新加入集群宕机,并行复制有不一致bug）、管理不方便（需配合mysql-shell）

注意：多主模式下最好有三台以上的节点,单主模式则视实际情况而定,不过同个Group最多节点数为9.

服务器配置尽量保持一致,因为和PXC一样,也会有"木桶短板效应".

需要特别注意,mysql数据库的服务端口号和MGR的服务端口不是一回事,需要区分开来.

二、MGR搭建

   1、配置三个docker容器，配置之前需要配置一个network方便容器间互相连接

        docker  network create mysql_group

   2、运行容器指令

       master：

      docker run --name master -v /Users/drmac/mysql/master:/var/lib/mysql -v /Users/drmac/mysql/master.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=root -p 3306:3306 --network mysql_group  --network-alias master  -d mysql  

     slave-1 ：

docker run --name slave-1 -v /Users/drmac/mysql/slave-1:/var/lib/mysql -v /Users/drmac/mysql/slave-1.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=root -p 3307:3306 --network mysql_group --network-alias slave-1  -d mysql

     slave-2：

  docker run --name slave-2 -v /Users/drmac/mysql/slave-2:/var/lib/mysql -v /Users/drmac/mysql/slave-2.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=root -p 3308:3306 --network mysql_group --network-alias slave-2  -d mysql

启动三个容器，数据文件和配置文件分别挂载到本地磁盘，方便配置管理

3.my.cnf配置

 master：

[mysqld]

# 开启GTID,必须开启

gtid_mode=on

# 强制GTID的一致性

enforce-gtid-consistency=on

# binlog格式,MGR要求必须是ROW

binlog_format=row

# server-id必须是唯一的

server-id=1

# MGR使用乐观锁,所以官网建议隔离级别是RC,减少锁粒度

transaction_isolation=READ-COMMITTED

# 因为集群会在故障恢复时互相检查binlog的数据,

# 所以需要记录下集群内其他服务器发过来已经执行过的binlog,按GTID来区分是否执行过.

log-slave-updates=1

# binlog校验规则,5.6之后的高版本是CRC32,低版本都是NONE,但是MGR要求使用NONE

binlog_checksum=NONE

# 基于安全的考虑,MGR集群要求复制模式要改成slave记录记录到表中,不然就报错

master_info_repository=TABLE

# 同上配套

relay_log_info_repository=TABLE

# 记录事务的算法,官网建议设置该参数使用 XXHASH64 算法

transaction_write_set_extraction = XXHASH64

# 相当于此GROUP的名字,是UUID值,不能和集群内其他GTID值的UUID混用,可用uuidgen来生成一个新的,

# 主要是用来区分整个内网里边的各个不同的GROUP,而且也是这个group内的GTID值的UUID

loose-group_replication_group_name = 'cc5e2627-2285-451f-86e6-0be21581539f'

#是否随服务器启动而自动启动组复制,不建议直接启动,怕故障恢复时有扰乱数据准确性的特殊情况

loose-group_replication_start_on_boot = OFF

loose-group_replication_local_address = 'master:33066'

loose-group_replication_group_seeds = 'master:33066,salve-1:33066,slave-2:33066'

# 开启引导模式,添加组成员，用于第一次搭建MGR或重建MGR的时候使用,只需要在集群内的其中一台开启,

loose-group_replication_bootstrap_group = OFF

# 是否启动单主模式，如果启动，则本实例是主库，提供读写，其他实例仅提供读,如果为off就是多主模式了

#loose-group_replication_single_primary_mode = off

# 多主模式下,强制检查每一个实例是否允许该操作,如果不是多主,可以关闭

#loose-group_replication_enforce_update_everywhere_checks = on

#mgr需要帮各个节点加入白名单，否则集群不成功

loose-group_replication_ip_whitelist="172.20.0.2/24,172.20.0.3/24,172.20.0.4/24";

log-bin=mysql-bin

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

socket          = /var/run/mysqld/mysqld.sock

datadir         = /var/lib/mysql

secure-file-priv= NULL

default_authentication_plugin=mysql_native_password

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

# Custom config should go here

!includedir /etc/mysql/conf.d/

slave1 ：

[mysqld]

# 开启GTID,必须开启

gtid_mode=on

# 强制GTID的一致性

enforce-gtid-consistency=on

# binlog格式,MGR要求必须是ROW,不过就算不是MGR,也最好用row

binlog_format=row

# server-id必须是唯一的

server-id=2

# MGR使用乐观锁,所以官网建议隔离级别是RC,减少锁粒度

transaction_isolation=READ-COMMITTED

# 因为集群会在故障恢复时互相检查binlog的数据,

# 所以需要记录下集群内其他服务器发过来已经执行过的binlog,按GTID来区分是否执行过.

log-slave-updates=1

# binlog校验规则,5.6之后的高版本是CRC32,低版本都是NONE,但是MGR要求使用NONE

binlog_checksum=NONE

# 基于安全的考虑,MGR集群要求复制模式要改成slave记录记录到表中,不然就报错

master_info_repository=TABLE

# 同上配套

relay_log_info_repository=TABLE

# 记录事务的算法,官网建议设置该参数使用 XXHASH64 算法

transaction_write_set_extraction = XXHASH64

# 相当于此GROUP的名字,是UUID值,不能和集群内其他GTID值的UUID混用,可用uuidgen来生成一个新的,

# 主要是用来区分整个内网里边的各个不同的GROUP,而且也是这个group内的GTID值的UUID

loose-group_replication_group_name = 'cc5e2627-2285-451f-86e6-0be21581539f'

#是否随服务器启动而自动启动组复制,不建议直接启动,怕故障恢复时有扰乱数据准确性的特殊情况

loose-group_replication_start_on_boot = OFF

loose-group_replication_local_address = 'slave-1:33066'

loose-group_replication_group_seeds = 'master:33066,slave-1:33066,slave-2:33066'

# 开启引导模式,添加组成员，用于第一次搭建MGR或重建MGR的时候使用,只需要在集群内的其中一台开启,

#loose-group_replication_bootstrap_group = OFF

# 是否启动单主模式，如果启动，则本实例是主库，提供读写，其他实例仅提供读,如果为off就是多主模式了

#loose-group_replication_single_primary_mode = off

# 多主模式下,强制检查每一个实例是否允许该操作,如果不是多主,可以关闭

#loose-group_replication_enforce_update_everywhere_checks = on

loose-group_replication_ip_whitelist="172.20.0.2/24,172.20.0.3/24,172.20.0.4/24";

log-bin=mysql-bin

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

socket          = /var/run/mysqld/mysqld.sock

datadir         = /var/lib/mysql

secure-file-priv= NULL

default_authentication_plugin=mysql_native_password

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

# Custom config should go here

!includedir /etc/mysql/conf.d/

slave-2配置和slave-1一致 （注意server_id和group_replication_local_address修改即可）

 server_id，group_replication_local_address不同数据库是不同的，其余要保持相同，host后面跟的端口号是自定义的，只要保证和本机的端口不冲突就好

4、配置master

  4.1创建复制账号（每个节点都需要创建账号，并给REPLICATION 、SLAVE角色）

mysql> SET SQL_LOG_BIN=0;

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY '[email protected]';

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';

mysql> FLUSH PRIVILEGES;

mysql> SET SQL_LOG_BIN=1;

  4.2配置频道

 mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='[email protected]'  FOR CHANNEL 'group_replication_recovery';

  4.3安装复制插件 （MGR在mysql5.7.17版本之后就都是自带插件了,只是没有安装上而已,和半同步插件一个套路,所以默认是没有选项。所有集群内的服务器都必须安装MGR插件才能正常使用该功能）

  INSTALL PLUGIN group_replication SONAME 'group_replication.so';

 4.4 开启组复制（master节点）

  SET GLOBAL group_replication_bootstrap_group=ON;

  START GROUP_REPLICATION;

  SET GLOBAL group_replication_bootstrap_group=OFF;

  4.5查看组状态

select * from `performance_schema`.replication_group_members

5、配置slave-1

 5.1创建复制账号（每个节点都需要创建账号，并给REPLICATION 、SLAVE角色）

mysql> SET SQL_LOG_BIN=0;

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY '[email protected]';

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';

mysql> FLUSH PRIVILEGES;

mysql> SET SQL_LOG_BIN=1;

  5.2配置频道

 mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='[email protected]'  FOR CHANNEL 'group_replication_recovery';

  5.3安装复制插件 （MGR在mysql5.7.17版本之后就都是自带插件了,只是没有安装上而已,和半同步插件一个套路,所以默认是没有选项。所有集群内的服务器都必须安装MGR插件才能正常使用该功能）

  INSTALL PLUGIN group_replication SONAME 'group_replication.so';

 5.4 加入节点

注意：加入节点需要开启如下参数

  SET GLOBALgroup_replication_allow_local_lower_version_join=ON

  START GROUP_REPLICATION;

5.5查看组状态

select * from `performance_schema`.replication_group_members

6、配置slave-2

 

 6.1创建复制账号（每个节点都需要创建账号，并给REPLICATION 、SLAVE角色）

mysql> SET SQL_LOG_BIN=0;

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY '[email protected]';

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';

mysql> FLUSH PRIVILEGES;

mysql> SET SQL_LOG_BIN=1;

  6.2配置频道

 mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='[email protected]'  FOR CHANNEL 'group_replication_recovery';

  6.3安装复制插件 （MGR在mysql5.7.17版本之后就都是自带插件了,只是没有安装上而已,和半同步插件一个套路,所以默认是没有选项。所有集群内的服务器都必须安装MGR插件才能正常使用该功能）

  INSTALL PLUGIN group_replication SONAME 'group_replication.so';

 6.4 加入节点

注意：加入节点需要开启如下参数

  SET GLOBALgroup_replication_allow_local_lower_version_join=ON

  START GROUP_REPLICATION;

6.5查看组状态

select * from `performance_schema`.replication_group_members

注意如果master中已经存在数据，需要同步，正常情况需要备份主库数据到从库

在做备份恢复的时候，有时需要恢复出来的 MySQL 实例可以作为从库连上原来的主库继续复制，这就要求从备份恢复出来的 MySQL 实例拥有和主数据库数据一致的 gtid_executed 值。

 备份语句：

mysqldump -h localhost -uroot -proot --all-databases --single-transaction --master-data  --triggers --routines --events > /home/dump.sql

slave-1 slave-2分别恢复数据

mysql -h localhost -uroot -proot  -P 3307

mysql -h localhost -uroot -proot  -P 3308

三、MySQL router中间件

MySQL Router是轻量级的中间件，可在您的应用程序与任何后端MySQL Server之间提供透明的路由。它可用于多种使用案例，例如通过有效地将数据库流量路由到适当的后端MySQL服务器来提供高可用性和可伸缩性。可插拔架构还使开发人员能够针对自定义用例扩展MySQL Router。

故障转移

通常，高可用性的MySQL设置由一个主服务器和多个从服务器组成，如果MySQL主服务器不可用，则由应用程序来处理故障转移。使用MySQL Router，将基于负载平衡策略透明地路由应用程序连接，而无需实现自定义应用程序代码。

负载均衡

MySQL Router通过在服务器池中分布数据库连接来提供额外的可伸缩性和性能。例如，如果您有一组复制的MySQL服务器，则MySQL Router可以循环方式将应用程序连接分配给它们。

可插拔架构

MySQL Router的可插入体系结构使MySQL开发人员可以轻松扩展产品的附加功能，并为MySQL用户提供创建自己的自定义插件的能力，从而提供无限的可能性。MySQL Router当前随附许多核心插件，包括：

l连接路由插件，它执行基于连接的路由，这意味着它将MySQL数据包转发到后端服务器，而无需检查或修改它们，从而提供最大的吞吐量。

l的元数据高速缓存插件，它提供了透明的客户端负载平衡，路由，和故障转移到组复制和InnoDB群集

 

 1.mysqlrouter启动

 mysqlrouter --bootstrap root@localhost:3310 --directory /Users/drmac/mysqlrouter/ --conf-use-sockets

 mysqlrouter -c /Users/drmac/mysqlrouter/mysqlrouter.conf &

DRdeMacBook-Air:3310 drmac$ mysqlrouter --bootstrap root@localhost:3320 --directory /Users/drmac/mysqlrouter/ --conf-use-sockets --force

Please enter MySQL password for root:

# Reconfiguring MySQL Router instance at '/Users/drmac/mysqlrouter'...

- Checking for old Router accounts

 - Found old Router accounts, removing

- Creating mysql account 'mysql_router1_i68kebi6fq9l'@'%' for cluster management

- Storing account in keyring

- Adjusting permissions of generated files

- Creating configuration /Users/drmac/mysqlrouter/mysqlrouter.conf

# MySQL Router configured for the InnoDB cluster 'testCluster'

After this MySQL Router has been started with the generated configuration

    $ mysqlrouter -c /Users/drmac/mysqlrouter/mysqlrouter.conf

the cluster 'testCluster' can be reached by connecting to:

## MySQL Classic protocol

- Read/Write Connections: localhost:6446, /Users/drmac/mysqlrouter/mysql.sock

- Read/Only Connections:  localhost:6447, /Users/drmac/mysqlrouter/mysqlro.sock

## MySQL X protocol

- Read/Write Connections: localhost:64460, /Users/drmac/mysqlrouter/mysqlx.sock

- Read/Only Connections:  localhost:64470, /Users/drmac/mysqlrouter/mysqlxro.sock

2.mysqlrouter 配置

# File automatically generated during MySQL Router bootstrap

[DEFAULT]

logging_folder=/Users/drmac/mysqlrouter/log

runtime_folder=/Users/drmac/mysqlrouter/run

data_folder=/Users/drmac/mysqlrouter/data

keyring_path=/Users/drmac/mysqlrouter/data/keyring

master_key_path=/Users/drmac/mysqlrouter/mysqlrouter.key

connect_timeout=15

read_timeout=30

dynamic_state=/Users/drmac/mysqlrouter/data/state.json

[logger]

level = INFO

[metadata_cache:testCluster]

router_id=1

user=mysql_router1_i68kebi6fq9l

metadata_cluster=testCluster

ttl=0.5

use_gr_notifications=0

[routing:testCluster_default_rw]

bind_address=0.0.0.0

bind_port=6446

socket=/Users/drmac/mysqlrouter/mysql.sock

destinations=metadata-cache://testCluster/default?role=PRIMARY

routing_strategy=first-available

protocol=classic

[routing:testCluster_default_ro]

bind_address=0.0.0.0

bind_port=6447

socket=/Users/drmac/mysqlrouter/mysqlro.sock

destinations=metadata-cache://testCluster/default?role=SECONDARY

routing_strategy=round-robin-with-fallback

protocol=classic

[routing:testCluster_default_x_rw]

bind_address=0.0.0.0

bind_port=64460

socket=/Users/drmac/mysqlrouter/mysqlx.sock

destinations=metadata-cache://testCluster/default?role=PRIMARY

routing_strategy=first-available

protocol=x

[routing:testCluster_default_x_ro]

bind_address=0.0.0.0

bind_port=64470

socket=/Users/drmac/mysqlrouter/mysqlxro.sock

destinations=metadata-cache://testCluster/default?role=SECONDARY

routing_strategy=round-robin-with-fallback

protocol=x

MySQL路由器配置了四个端口和四个套接字。默认情况下添加端口，并通过传入添加套接字 --conf-use-sockets。名为“ testCluster”的InnoDB群集是元数据的来源，并且destinations使用InnoDB群集元数据缓存来动态配置主机信息。相关的命令行选项：

--conf-use-sockets：有选择地为所有四种连接类型启用UNIX域套接字

--conf-skip-tcp：有选择地禁用TCP端口，--conf-use-sockets如果只希望使用套接字，则可以使用该选项 。

--conf-base-port：（可选）更改端口范围，而不使用默认端口。这将为经典读写（PRIMARY）连接设置端口，默认为 6446。

--conf-bind-address：（可选）更改每个路由的bind_address值。

3.登录mysql

四、MySQLshell 管理mgr

模式切换

默认情况下mgr为单主模式，切换可以从mysqlshell直接切换

cluster.switchToMultiPrimaryMode()

自动切换为多主模式

多主模式切换为单主模式：

 cluster.switchToSinglePrimaryMode()

1、创建cluster

  mysql-js> shell.connect('root@localhost:3310')

  mysql-js> var cluster = dba.createCluster('Cluster')

  mysql-js> cluster.status()

  mysql-js> cluster.addInstance('root@localhost:3320')

  mysql-js> cluster.status()

 5.获取一个集群

  mysql-js> var cluster = dba.getCluster();

 

 6.重启一个集群

  mysql-js> 

 7.修复集群

  cluster.forceQuorumUsingPartitionOf("root@localhost:3310")

8.重新加入

  cluster.rejoinInstance('root@localhost:3310');

9.解散集群

  cluster.dissolve({force:true})

四、多主模式思考

1.写请求可以分发多个成员上：

MGR多写内部自带冲突解决方式

show VARIABLES like '%auto_increment_offset%';

show VARIABLES like '%auto_increment_increment%';

auto_increment_increment：控制连续列值之间的间隔。

auto_increment_offset：确定AUTO_INCREMENT列值的起点。

MGR默认配置：

GROUP_REPLICATION_AUTO_INCREMENT_INCREMENT

此变量的默认值为'7'。group_replication_auto_increment_increment

的默认值  选择为7，这是一个有效的折衷，同时考虑到每个可能的auto_increment_increment的最大可用值以及正常的组期望大小。

 auto_increment_increment：7（组的大小为3）

auto_increment_offset：server_id（其中M1 = 1，M2 = 2，M3 = 3）

M1：1、8、15、22、29、36…。

M2：2、9、16、23、30、37…。

M3：3、10、17、24、31、38…。

2.DDL发送到一台机子处理

这个本身很难实现，有中间件代理的时候，可以采用将ddl语句同步到一台节点执行。

create

alter

drop

等