LNMP架构之Mysql组复制与Mysql Router

组复制介绍

The most common way to create a fault-tolerant system is to resort to making components redundant, in other words the component can be removed and the system should continue to operate as expected. This creates a set of challenges that raise complexity of such systems to a whole different level. Specifically, replicated databases have to deal with the fact that they require maintenance and administration of several servers instead of just one. Moreover, as servers are cooperating together to create the group several other classic distributed systems problems have to be dealt with, such as network partitioning or split brain scenarios.

创建容错系统的最常见方法是诉诸于使组件冗余，换句话说，可以删除组件，并且系统应继续按预期运行。这就带来了一系列挑战，将此类系统的复杂性提高到了一个完全不同的水平。具体来说，复制的数据库必须处理以下事实：它们需要维护和管理多台服务器，而不仅仅是一台服务器。此外，随着服务器一起协作以创建该组，还必须解决其他几个经典的分布式系统问题，例如网络分区或大脑分裂情况。
异步复制

半同步复制

组复制
组复制是一种可用于实施容错系统的技术。复制组是一组服务器，每个服务器都有自己的完整数据副本（无共享复制方案），并通过消息传递相互交互

单主模式

多主模式

MGR

MySQL Group Replication是一个MySQL插件，它基于现有的MySQL复制基础结构，利用了二进制日志，基于行的日志记录和全局事务标识符等功能。它与当前的MySQL框架集成，例如性能模式或插件和服务基础结构

实现步骤

实验环境：server1 server2 server3

• 1 修改配置文件 /etc/my.cnf
  在配置好GTID的情况下 加入

  1 [mysqld]
  2 basedir=/usr/local/lnmp/mysql
  3 datadir=/data/mysql
  4 socket=/data/mysql/mysql.sock
  5 #skip-grant-tables
  6 server-id=1
  7 log-bin=mysql-bin
  8 gtid_mode=ON
  9 enforce-gtid-consistency=ON
 10 
 11 disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" ##对于组复制，数据必须存储在InnoDB事务存储引擎中
 12 default_authentication_plugin=mysql_native_password	
 13 plugin_load_add='group_replication.so' ##plugin-load-add将组复制插件添加到服务器在启动时加载的插件列表中。
 14 group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"	#告诉插件将其加入或创建的组
 15 group_replication_start_on_boot=off #确保可以在手动启动插件之前配置服务器
 16 group_replication_local_address="172.25.254.101:33061" #设置成员用于与组中其他成员进行内部通信的网络地址和端口
 17 group_replication_group_seeds="172.25.254.101:33061,172.25.254.102:33061,172.25.254.103:33061" #设置组成员的主机名和端口
 18 group_replication_bootstrap_group=off #指示插件是否引导组
 19 group_replication_ip_whitelist="172.25.254.0/24,127.0.0.1/8"
 20 group_replication_single_primary_mode=OFF
 21 group_replication_enforce_update_everywhere_checks=ON
 22 # include all files from the config directory
 23 #
 24 #
 25 !includedir /etc/my.cnf.d

• 启动并设置server1

mysql>  SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.14 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.51 sec)

mysql> GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.13 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 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='password' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (1.38 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=ON; ##只在server1上做
Query OK, 0 rows affected (0.00 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (4.71 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=OFF; ##只在server1上做
Query OK, 0 rows affected (0.00 sec)

mysql>  SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 24aadb40-e1f9-11ea-8354-5254009bde89 | server1     |        3306 | ONLINE       | PRIMARY     | 8.0.21         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
1 row in set (0.01 sec)

server2 和server3 其他设置同上
效果：

mysql>  CREATE DATABASE test;
Query OK, 1 row affected (0.50 sec)

mysql> USE test;
Database changed
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL); ##必须要有主键
Query OK, 0 rows affected (0.95 sec)

mysql> INSERT INTO t1 VALUES (1, 'Luis');
Query OK, 1 row affected (1.10 sec)

Mysql Router

MySQL路由器是InnoDB Cluster的一部分，是轻量级的中间件，可在应用程序和后端MySQL服务器之间提供透明的路由。

安装使用

• 下载
  官方下载
• 修改配置文件/etc/mysqlrouter/mysqlrouter.conf

# If no plugin is configured which starts a service, keepalive
# will make sure MySQL Router will not immediately exit. It is
# safe to remove once Router is configured.
[keepalive]
interval = 60

[routing:ro]
bind_address= 0.0.0.0
bind_port= 7001
destinations = 172.25.254.101:3306,172.25.254.102:3306,172.25.254.103:3306
routing_strategy = round-robin

[routing:rw]
bind_address= 0.0.0.0
bind_port= 7002
destinations = 172.25.254.101:3306,172.25.254.102:3306,172.25.254.103:3306
routing_strategy = first-available

• 测试
  安装mariad作为客户端
  
  访问的是本地 7001 端口