Mysql MGR 集群

  • 目录
  • MGR简介
  • MGR安装

数据库Galera集群的笔记做过了,今天来讲一讲MySQL Group Replication组复制技术。

一 MGR简介

MySQL当前存在的三种复制模式有:异步模式、半同步模式和组复制模式。
MySQL Group Replication(简称MGR)官方推出的高可用解决方案, 原生复制技术,基于插件的方式工作。

分为以下两种

  • single primary mode 单主模式只有一个读写,其余都是只读。
  • multi primary mode多主模式全部可读写

不管组复制单主还是多主的故障切换都无法让应用无感知,需要自主实现。

包含以下特性:

  • 复制管理操作更为自动化。
  • 通过Paxos协议提供数据库集群节点数据强一致性保证。
  • 多主模式所有节点都可读写操作。
  • 解决网络分区导致的脑裂问题,提升复制数据的可靠性。

官方引言

1
Quite obviously, regardless the mode Group Replication is deployed, it does not handle client-side fail-over. That must be handled by the application itself, connector or a middleware framework such as a proxy or router.

意思就是说MGR内部没有提供一种机制来实现主节点故障切换对应用的无感知。应用的这种故障无感知需要借助外力实现。

组复制存在以下局限性

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
1. 所有涉及的数据都必须发生在InnoDB存储引擎的表内。
2. 所有的表必须有明确的主键定义。
3. 网络地址只支持IPv4。
4. 需要低延迟,高带宽的网络。
5. 目前集群限制最多允许9个节点。
6. 必须启用binlog。
7. binlog 格式必须是row格式。
8. 必须打开gtid模式。
9. 复制相关信息必须使用表存储。
10.事务写集合(Transaction write set extraction)必须打开。(这个目前与savepoint冲突,这也是导致mysqldump无法备份GR实例的原因)
11. log slave updates必须打开。
12. binlog的checksum目前不支持。
13. 由于事务写集合的干扰,无法使用savepoint。
14. SERIALIZABLE 隔离级别目前不支持。
15. 对同一个对象,在集群中不同的实例上,并行地执行DDL(哪怕是相互冲突的DDL)是可行的,但会导致数据一致性等方面的错误,目前阶段不支持在多节点同时执行同一对象的DDL。
16. 外键的级联约束操作目前的实现并不完全支持,不推荐使用。

二 MGR安装

3.1 软件安装

官方单库安装
官方集群安装

  • 参考链接 :
    1
    2
    3
    4
    https://dev.mysql.com/doc/refman/5.7/en/replace-third-party-yum.html  # 禁用第三方源或安装包
    https://dev.mysql.com/doc/refman/5.7/en/linux-installation.html # 根据您的系统安装相应版本
    https://dev.mysql.com/doc/refman/5.7/en/linux-installation-yum-repo.html #CentOS7.3安装mysql 5.7
    https://dev.mysql.com/doc/refman/5.7/en/group-replication.html # mysql Group Replication安装
1
2
3
# rpm -ivh /opt/mysql57-community-release-el7-11.noarch.rpm
# yum install mysql-community-server
# systemctl start mysqld

3.2 内置策略说明

mysql5.7启动服务后会生成一个随机密码用于临时登录,内置策略强制让你进入数据库后马上更改登录密码,并且有一套密码复杂度管理的策略。我们可以在数据库my.cnf中过滤掉复杂度策略或临时修改全局变量来忽略复杂度。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> select @@validate_password_length;
+----------------------------+
| @@validate_password_length |
+----------------------------+
| 8 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | MEDIUM |
| validate_password_special_char_count | 1 |
+--------------------------------------+--------+
7 rows in set (0.01 sec)

或者/etc/my.cnf添加5行策略来忽略掉。

1
2
3
4
5
validate_password_length = 0
validate_password_mixed_case_count = 0
validate_password_number_count = 0
validate_password_policy =0
validate_password_special_char_count =0

当然也可以类似以下命令临时全局修改

1
set global validate_password_special_char_count =0; # 全部设置为0

查询临时密码

1
# grep 'temporary password' /var/log/mysqld.log

马上修改临时密码用以后续操作。

1
2
3
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';
Query OK, 0 rows affected (0.00 sec)
#否则收到提示:ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

3.3 为复制授权

1
2
3
4
5
6
7
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'rpl_pass';
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';

3.4 启动组复制

1
2
3
4
5
6
7
8
9
10
11
12
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; 
Query OK, 0 rows affected (0.02 sec)

mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+----------------------+---------+
...
| validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL |
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
+----------------------------+----------+--------------------+----------------------+---------+
46 rows in set (0.01 sec)

启动复制通道

1
2
3
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

查看集群成员

1
2
3
4
5
6
7
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| group_replication_applier | f2d4456f-c48b-11e7-94f2-fa163e894684 | 172.18.23.34 | 3306 | ONLINE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
1 row in set (0.00 sec)

创建测试同步数据

1
2
3
4
mysql> CREATE DATABASE test;
mysql> USE test;
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
mysql> INSERT INTO t1 VALUES (1, 'Luis');

检查同步数据和Binlog

1
2
3
4
5
6
7
mysql> select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
+----+------+
1 row in set (0.01 sec)

mysql> show binlog events;

1
2
3
4
5
6
7
8
+---------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+---------------------------------------+
| binlog.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| binlog.000001 | 123 | Previous_gtids | 1 | 150 | |
| binlog.000001 | 150 | Stop | 1 | 169 | |
+---------------+-----+----------------+-----------+-------------+---------------------------------------+
3 rows in set (0.00 sec

3.5 加入集群

其余节点加入集群
1 先定义主配文件与主机hosts
2 建立通道授权

1
2
3
4
5
6
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'rpl_pass' ;
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass'
FOR CHANNEL 'group_replication_recovery';

3 添加插件

1
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

4 启动复制通道

1
mysql> START GROUP_REPLICATION;

如果有失败,尝试

1
2
mysql> set global validate_password_policy=0; 
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;

5 查看集群成员

1
2
3
4
5
6
7
8
9
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| group_replication_applier | 2863475f-c490-11e7-b93e-fa163ebc6e6c | 172.18.23.8 | 3306 | ONLINE |
| group_replication_applier | 9c1ded3e-c495-11e7-8c62-fa163ea91040 | 172.18.23.33 | 3306 | ONLINE |
| group_replication_applier | f2d4456f-c48b-11e7-94f2-fa163e894684 | 172.18.23.34 | 3306 | ONLINE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
3 rows in set (0.01 sec)

6 查看集群中的主节点

1
2
3
4
5
6
7
mysql> SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME= 'group_replication_primary_member';
+--------------------------------------+
| VARIABLE_VALUE |
+--------------------------------------+
| f2d4456f-c48b-11e7-94f2-fa163e894684 |
+--------------------------------------+
1 row in set (0.01 sec)

7 binlog

1
2
3
4
5
6
7
8
9
mysql> show binlog events;
+---------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+---------------------------------------+
| binlog.000001 | 4 | Format_desc | 3 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| binlog.000001 | 123 | Previous_gtids | 3 | 150 | |
| binlog.000001 | 150 | Stop | 3 | 169 | |
+---------------+-----+----------------+-----------+-------------+---------------------------------------+
3 rows in set (0.00 sec)

8 my.cnf优化项

1
2
3
4
max_connections = 4096
max_prepared_stmt_count=100000
query_cache_type = 1
query_cache_size = 10M

9 命令

1
2
3
4
5
6
7
8
9
mysql> show full processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+-----------------------+
| 3 | root | localhost | test | Query | 0 | starting | show full processlist |
| 11 | system user | | NULL | Connect | 490 | executing | NULL |
| 14 | system user | | NULL | Connect | 490 | Slave has read all relay log; waiting for more updates | NULL |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+-----------------------+
3 rows in set (0.00 sec)