作者:Hemant Dangi 译:徐轶韬
在MySQL 8.0.22中发布的有关自动异步复制连接故障转移功能的博客文章中,我们向您展示了其用法和优点。在这一篇,我们将介绍在MySQL 8.0.23中添加的增强功能,从服务器可以获知复制源的成员身份变化(复制源使用组复制)。
MySQL 8.0.23增强了如下功能:
该功能还添加了许多其他更改,请查看“https://dev.mysql.com/worklog/task/?id=14019#tabs-14019-2”以了解详细信息。
现在,让我们在“用法”部分中通过示例查看有关这些增强功能的更多信息。
为了解释增强的功能,让我们考虑以下情形:
在副本服务器R1上,使用源服务器的复制凭据创建复制通道“ C1”。
R1> CHANGE REPLICATION SOURCE TO
SOURCE_USER='rpl_user', SOURCE_PASSWORD='password',
SOURCE_HOST='S1', SOURCE_PORT=3306,
SOURCE_AUTO_POSITION=1, SOURCE_CONNECT_RETRY=10,
SOURCE_RETRY_COUNT=3 FOR CHANNEL 'C1';
注意: SOURCE_RETRY_COUNT (86400次)和SOURCE_CONNECT_RETRY(60秒)的默认值是 为连接到单个源而设计的,它将使副本在60天之内重试相同的连接。这些尝试用尽后,异步连接故障转移机制将接管工作。因此, 为确保可以迅速激活异步连接故障转移机制,请将SOURCE_RETRY_COUNT和SOURCE_CONNECT_RETRY 设置为最小值 ,以允许对同一源进行多次重试尝试,以防连接失败是由短暂的网络中断引起的。
异步_connection_failover_add_managed()UDF可用于添加源配置详细信息。
R1> SELECT asynchronous_connection_failover_add_managed(
channel, managed_type, managed_name, host, port,
network_namespace, primary_weight, secondary_weight);
UDF的参数具有以下含义:
R1> SELECT asynchronous_connection_failover_add_managed(
'C1', 'GroupReplication',
'8a10aa2w-1235-65bt-8749-8211a9411581',
'S1', 3306, '', 80, 60);
+------------------------------------------------------------------------------------------------------------------------------------------+
| asynchronous_connection_failover_add_managed('C1', 'GroupReplication', '8a10aa2w-1235-65bt-8749-8211a9411581', 'S1', 3310, '', 80, 60) |
+------------------------------------------------------------------------------------------------------------------------------------------+
| Source managed configuration details successfully inserted. |
+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,01 sec)
用户/ DBA只需要执行sync_connection_failover_add_managed,其余的潜在源(组成员)将由功能部件自动收集和添加,可以使用performance_schema.replication_asynchronous_connection_failover表进行验证。最初,该表只有一个条目,其余的潜在源(组成员)将在以后收集和添加。
R1> SELECT CHANNEL_NAME, HOST, PORT, WEIGHT, MANAGED_NAME
FROM
performance_schema.replication_asynchronous_connection_failover;
+---------------+-------+-------+----------+---------------------------------------+
| CHANNEL_NAME | HOST | PORT | WEIGHT | MANAGED_NAME |
+---------------+-------+-------+----------+---------------------------------------+
| C1 | S1 | 3306 | 80 | 8a10aa2w-1235-65bt-8749-8211a9411581 |
+---------------+-------+-------+----------+---------------------------------------+
1 row in set (0.00 sec)
使用CHANGE REPLICATION SOURCE TO SOURCE_CONNECTION_AUTO_FAILOVER = 1启用复制通道'C1'的功能。
R1> CHANGE REPLICATION SOURCE TO
SOURCE_CONNECTION_AUTO_FAILOVER=1 FOR CHANNEL 'C1';
Query OK, 0 rows affected (0.12 sec)
R1> SELECT CHANNEL_NAME, SOURCE_CONNECTION_AUTO_FAILOVER
FROM
performance_schema.replication_connection_configuration
WHERE CHANNEL_NAME = 'C1';
+--------------------------+----------------------------------+
| CHANNEL_NAME | SOURCE_CONNECTION_AUTO_FAILOVER |
+--------------------------+----------------------------------+
| C1 | 1 |
+--------------------------+----------------------------------+
1 row in set (0.04 sec)
在副本服务器上启动复制通道“ C1”,并检查其I / O线程状态:
R1> START REPLICA FOR CHANNEL 'C1';
Query OK, 0 rows affected, 1 warning (0.03 sec)
R1> SELECT CHANNEL_NAME, SOURCE_UUID, SERVICE_STATE
FROM performance_schema.replication_connection_status\G
*************************** 1. row ***************************
CHANNEL_NAME: C1
SOURCE_UUID: 21d659ca-870a-21ad-4a91-1224d152964
SERVICE_STATE: ON
1 row in set (0.00 sec)
启用该功能后,它将自动将新的源添加到源列表,并在需要时重新连接异步复制。可以在performance_schema.replication_asynchronous_connection_failover表中进行验证。
R1> SELECT CHANNEL_NAME, HOST, PORT, WEIGHT, MANAGED_NAME
FROM
performance_schema.replication_asynchronous_connection_failover;
+---------------+-------+-------+----------+---------------------------------------+
| CHANNEL_NAME | HOST | PORT | WEIGHT | MANAGED_NAME |
+---------------+-------+-------+----------+---------------------------------------+
| C1 | S1 | 3306 | 80 | 8a10aa2w-1235-65bt-8749-8211a9411581 |
| C1 | S2 | 3306 | 60 | 8a10aa2w-1235-65bt-8749-8211a9411581 |
| C1 | S3 | 3306 | 60 | 8a10aa2w-1235-65bt-8749-8211a9411581 |
+---------------+-------+-------+----------+---------------------------------------+
2 rows in set (0.00 sec)
当添加新源或现有源发生故障或被删除时,源列表会在接收者上自动更新,无需用户/ DBA干预。
S1发生故障后,接收者将自动连接到故障转移权重最高的下一个可用源,在这种情况下为S2。
R1> SELECT CHANNEL_NAME, HOST, PORT, WEIGHT, MANAGED_NAME
FROM
performance_schema.replication_asynchronous_connection_failover;
+---------------+-------+-------+----------+----------------------------------------+
| CHANNEL_NAME | HOST | PORT | WEIGHT | MANAGED_NAME |
+---------------+-------+-------+----------+----------------------------------------+
| C1 | S2 | 3306 | 80 | 8a10aa2w-1235-65bt-8749-8211a9411581 |
| C1 | S3 | 3306 | 60 | 8a10aa2w-1235-65bt-8749-8211a9411581 |
+---------------+-------+-------+----------+----------------------------------------+
2 rows in set (0.00 sec)
每当添加/更新具有更高故障转移权重的源,然后更新当前连接的源时,异步复制通道就会重新连接到具有最大故障转移权重的源,并且接收者上的源列表也会相应地更新。
如下图3所示,添加了一个具有SOURCE_UUID:37d345fs-341d-66hf-5f54-2345e247680的新源S4,并使用异步复制通道C2将其与其余源连接。
使用具有最高故障转移权重(即90)的asynchronous_connection_failover_add_source()UDF将新的源S4添加到源列表中。
R1> SELECT asynchronous_connection_failover_add_source(
'C1', 'S4', 3306, '', 90);
+------------------------------------------------------------------------+
| asynchronous_connection_failover_add_source('C1', 'S4', 3306, '', 90) |
+------------------------------------------------------------------------+
| Source configuration details successfully inserted. |
+------------------------------------------------------------------------+
1 row in set (0,01 sec)
在接收者R1上的performance_schema.replication_asynchronous_connection_failover表中可以清楚地看出S4具有最高的故障转移权重。
R1> SELECT CHANNEL_NAME, HOST, PORT, WEIGHT, MANAGED_NAME
FROM
performance_schema.replication_asynchronous_connection_failover;
+---------------+-------+-------+----------+---------------------------------------+
| CHANNEL_NAME | HOST | PORT | WEIGHT | MANAGED_NAME |
+---------------+-------+-------+----------+---------------------------------------+
| C1 | S2 | 3306 | 80 | 8a10aa2w-1235-65bt-8749-8211a9411581 |
| C1 | S3 | 3306 | 60 | 8a10aa2w-1235-65bt-8749-8211a9411581 |
| C1 | S4 | 3306 | 90 | |
+---------------+-------+-------+----------+---------------------------------------+
2 rows in set (0.00 sec)
异步复制通道C1应该重新连接到S4,可以通过接收者R1上的performance_schema.replication_connection_status对其进行验证。
R1> SELECT CHANNEL_NAME, SOURCE_UUID, SERVICE_STATE
FROM
performance_schema.replication_connection_status\G
*************************** 1. row ***************************
CHANNEL_NAME: C1
SOURCE_UUID: 37d345fs-341d-66hf-5f54-2345e247680
SERVICE_STATE: ON
当源的角色发生更改时(即组复制,切换主要成员时),将为新的“主要成员”分配一个Primary_weight变量的权重,如果该权重高于该通道的其余源,则异步复制连接重新连接到这个新的主要成员。对于希望接收者始终保持连接到组复制的主要成员的用户/ DBA,以最小的延迟使接收者上的二进制日志保持同步,此更改将非常有用。
最初,performance_schema.replication_asynchronous_connection_failover表具有通道C1的以下配置。主通道S1的通道C1的故障转移权重最高,为80。
R1> SELECT CHANNEL_NAME, HOST, PORT, WEIGHT, MANAGED_NAME
FROM
performance_schema.replication_asynchronous_connection_failover;
+---------------+-------+-------+----------+---------------------------------------+
| CHANNEL_NAME | HOST | PORT | WEIGHT | MANAGED_NAME |
+---------------+-------+-------+----------+---------------------------------------+
| C1 | S1 | 3306 | 80 | 8a10aa2w-1235-65bt-8749-8211a9411581 |
| C1 | S2 | 3306 | 60 | 8a10aa2w-1235-65bt-8749-8211a9411581 |
| C1 | S3 | 3306 | 60 | 8a10aa2w-1235-65bt-8749-8211a9411581 |
+---------------+-------+-------+----------+---------------------------------------+
2 rows in set (0.00 sec)
组的主要成员更改为S2。
S2> SELECT group_replication_set_as_primary(
'c727957e-4cb6-11e9-abd5-f80a484a9c32');
+--------------------------------------------------------------------------+
| group_replication_set_as_primary('c727957e-4cb6-11e9-abd5-f80a484a9c32') |
+--------------------------------------------------------------------------+
| Primary server switched to: c727957e-4cb6-11e9-abd5-f80a484a9c32 |
+--------------------------------------------------------------------------+
1 row in set (0.02 sec)
异步复制连接C1以其现在具有最高的故障转移权重重新连接到S2,这可以在下面的performance_schema.replication_asynchronous_connection_failover表中看到。
R1> SELECT CHANNEL_NAME, HOST, PORT, WEIGHT, MANAGED_NAME
FROM
performance_schema.replication_asynchronous_connection_failover;
+---------------+-------+-------+----------+---------------------------------------+
| CHANNEL_NAME | HOST | PORT | WEIGHT | MANAGED_NAME |
+---------------+-------+-------+----------+---------------------------------------+
| C1 | S1 | 3306 | 60 | 8a10aa2w-1235-65bt-8749-8211a9411581 |
| C1 | S2 | 3306 | 80 | 8a10aa2w-1235-65bt-8749-8211a9411581 |
| C1 | S3 | 3306 | 60 | 8a10aa2w-1235-65bt-8749-8211a9411581 |
+---------------+-------+-------+----------+---------------------------------------+
2 rows in set (0.00 sec)
异步_connection_failover_delete_managed()UDF可用于删除管理配置详细信息。
SELECT asynchronous_connection_failover_delete_managed(
channel, managed_name);
例子:
R1> SELECT asynchronous_connection_failover_delete_managed(
'C1', '8a10aa2w-1235-65bt-8749-8211a9411581');
+-----------------------------------------------------------------------------------------------+
| asynchronous_connection_failover_delete_managed(C1', '8a10aa2w-1235-65bt-8749-8211a9411581') |
+-----------------------------------------------------------------------------------------------+
| Source managed configuration details successfully deleted. |
+-----------------------------------------------------------------------------------------------+
1 row in set (0,01 sec)
我们通过示例向您说明了添加到“异步复制连接故障转移”功能中的新增强功能。展示了源的组成员身份配置中何时发生状态或角色更改,接收者如何同步这些更改,并在需要时自动重新连接,而无需任何用户干预。这些增强功能将通过自动添加和删除潜在资源的过程来帮助提高MySQL系统的可用性,并简化维护。希望您会喜欢此功能的这些增强功能,并希望这种自动化可以改善并简化系统设置并减少错误,从而提供更高的容错能力和更好的系统可用性。