新特性解读 | MySQL 8.0 新特性-副本集(replicaset)

于由OssActiontech发布

引言

之前，我介绍过一篇《MySQL Innodb Cluster 扫盲篇》。

MySQL Innodb Cluster = MySQL Shell + MySQL Router + MySQL Group Replication(MGR)
全程由 MySQL Shell 来管理操作 MGR 的聚合套件。MySQL 8.0.19 发布后，这种组合延伸到 MySQL Replication（主从复制），也就是 MySQL Shell + MySQL Router + MySQL Replication。

MySQL 主从复制，相信大家已经非常了解了，原理大概如下图：

一、环境准备

举个例子，搭建主从的基本步骤大概如下：
  • 主机 A：192.168.2.171:4010
  • 从机 B：192.168.2.171:4011
  • 从机 C：192.168.2.171:4012
主机和从机经过简单的配置，后期其他配置自己任意搭配（不同的主机要将 server-id 分别改成不同的就行了）
  1. # my.cnf
  2. server-id = 4010

  4. log-bin=ytt

  6. enforce_gtid_consistency=on
  7. gtid_mode=on

  9. log_slave_updates=on

  11. master-info-repository=table
  12. relay-log-info-repository=table
  13. slave_parallel_type=logical
  14. slave_parallel_workers=4
重启所有实例
  1. systemctl restart mysql@{4010,4011,4012}

然后在 A 上创建主从需要的用户，并且分别在 B 和 C 上执行 change master to 命令配置从机即可。

我上面只是涉及到主从从零开始的场景，如果说主机 A 有数据，还得重加步骤。关于这点资料很多，我这里也就不多介绍了。那么对主从的管理没有搭建这么简单，稍微要繁琐些，譬如要实现如下的功能点：
1. 主从级别互换：主机降级为从机；从机升级为主机。
2. 从机由于异常，无法恢复正常；或者恢复正常了，发现数据和主机有差异，必须要重做从机，恢复主从关系。
3. 列出主从关系，可能要自己写个小脚本或者是利用 MySQL Utility 工具集或者是其他的工具集合。

那接下来来看看 MySQL 8.0.19 中 MySQL Shell 新特性：副本集以及管理。

二、副本集

副本集也就是极度简化了对 MySQL 主从的相关管理操作。

那接下来还是用刚才那三个实例举例说明 MySQL Shell 怎么简化了相关操作。

进入 MySQL X 端口 40100
  1. root@ytt-pc:/home/ytt/scripts# mysqlsh mysqlx://ytt@ytt-pc:40100
  2. MySQL Shell 8.0.19

  4. Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved.
  5. Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
  6. Other names may be trademarks of their respective owners.

  8. Type '\help' or '\?' for help; '\quit' to exit.
  9. Creating an X protocol session to 'ytt@ytt-pc:40100'
  10. Fetching schema names for autocompletion... Press ^C to stop.
  11. Your MySQL connection id is 12 (X protocol)
  12. Server version: 8.0.19 MySQL Community Server - GPL
  13. No default schema selected; type \use <schema> to set one.

2.1 创建副本集 ytt_replicaset

  1. MySQL  ytt-pc:40100+ ssl  JS > var c1 = dba.createReplicaSet("ytt_replicaset")
  2. A new replicaset with instance 'ytt-pc:4010' will be created.

  4. * Checking MySQL instance at ytt-pc:4010

  6. This instance reports its own address as ytt-pc:4010
  7. ytt-pc:4010: Instance configuration is suitable.

  9. * Updating metadata...

  11. ReplicaSet object successfully created for ytt-pc:4010.
  12. Use rs.addInstance() to add more asynchronously replicated instances to this replicaset and rs.status() to check its status.

查看帮助

可以看到所有的方法：c1.help()

查看当前副本集的状态

默认 RW，单机运行
  1.  MySQL  ytt-pc:40100+ ssl  JS > c1.status()
  2. {
  3.     "replicaSet": {
  4.         "name": "ytt_replicaset",
  5.         "primary": "ytt-pc:4010",
  6.         "status": "AVAILABLE",
  7.         "statusText": "All instances available.",
  8.         "topology": {
  9.             "ytt-pc:4010": {
  10.                 "address": "ytt-pc:4010",
  11.                 "instanceRole": "PRIMARY",
  12.                 "mode": "R/W",
  13.                 "status": "ONLINE"
  14.             }
  15.         },
  16.         "type": "ASYNC"
  17.     }
  18. }

添加从机

默认用克隆的方式复制数据
  1.   MySQL  ytt-pc:40100+ ssl  JS > c1.addInstance('ytt@ytt-pc:4011')
  2. Adding instance to the replicaset...

  4. * Performing validation checks

  6. This instance reports its own address as ytt-pc:4011
  7. ytt-pc:4011: Instance configuration is suitable.

  9. * Checking async replication topology...

  11. * Checking transaction state of the instance...

  13. WARNING: A GTID set check of the MySQL instance at 'ytt-pc:4011' determined that it contains transactions that do not originate from the replicaset, which must be discarded before it can join the replicaset.

  15. ytt-pc:4011 has the following errant GTIDs that do not exist in the replicaset:
  16. a19a4ac4-3a98-11ea-8f47-9cb6d0e27d15:1-7

  18. WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of ytt-pc:4011 with a physical snapshot from an existing replicaset member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

  20. Having extra GTID events is not expected, and it is recommended to investigate this further and ensure that the data can be removed prior to choosing the clone recovery method.

  22. Please select a recovery method [C]lone/[A]bort (default Abort): C
  23. * Updating topology
  24. Waiting for clone process of the new member to complete. Press ^C to abort the operation.
  25. * Waiting for clone to finish...
  26. NOTE: ytt-pc:4011 is being cloned from ytt-pc:4010
  27. ** Stage DROP DATA: Completed
  28. ** Clone Transfer
  29.     FILE COPY  ############################################################  100%  Completed
  30.     PAGE COPY  ############################################################  100%  Completed
  31.     REDO COPY  ############################################################  100%  Completed

  33. NOTE: ytt-pc:4011 is shutting down...

  35. * Waiting for server restart... ready
  36. * ytt-pc:4011 has restarted, waiting for clone to finish...
  37. * Clone process has finished: 59.64 MB transferred in about 1 second (~1.00 B/s)

  39. ** Configuring ytt-pc:4011 to replicate from ytt-pc:4010
  40. ** Waiting for new instance to synchronize with PRIMARY...

  42. The instance 'ytt-pc:4011' was added to the replicaset and is replicating from ytt-pc:4010.
用同样的方法添加实例 C
  1. MySQL  ytt-pc:40100+ ssl  JS > c1.addInstance('ytt@ytt-pc:4012')
接下来看看新的主从状态，此时 ytt-pc:4010 为主机，ytt-pc:4011 和 ytt-pc:4012 为从机，并且状态都正常
  1.  MySQL  ytt-pc:40100+ ssl  JS > c1.status()
  2. {
  3.     "replicaSet": {
  4.         "name": "ytt_replicaset",
  5.         "primary": "ytt-pc:4010",
  6.         "status": "AVAILABLE",
  7.         "statusText": "All instances available.",
  8.         "topology": {
  9.             "ytt-pc:4010": {
  10.                 "address": "ytt-pc:4010",
  11.                 "instanceRole": "PRIMARY",
  12.                 "mode": "R/W",
  13.                 "status": "ONLINE"
  14.                },
  15.             "ytt-pc:4011": {
  16.                 "address": "ytt-pc:4011",
  17.                 "instanceRole": "SECONDARY",
  18.                 "mode": "R/O",
  19.                 "replication": {
  20.                     "applierStatus": "APPLIED_ALL",
  21.                     "applierThreadState": "Waiting for an event from Coordinator",
  22.                     "applierWorkerThreads": 4,
  23.                     "receiverStatus": "ON",
  24.                     "receiverThreadState": "Waiting for master to send event",
  25.                     "replicationLag": null
  26.                 },
  27.                 "status": "ONLINE"
  28.             },
  29.             "ytt-pc:4012": {
  30.                 "address": "ytt-pc:4012",
  31.                 "instanceRole": "SECONDARY",
  32.                 "mode": "R/O",
  33.                 "replication": {
  34.                     "applierStatus": "APPLIED_ALL",
  35.                     "applierThreadState": "Waiting for an event from Coordinator",
  36.                     "applierWorkerThreads": 4,
  37.                     "receiverStatus": "ON",
  38.                     "receiverThreadState": "Waiting for master to send event",
  39.                     "replicationLag": null
  40.                 },
  41.                 "status": "ONLINE"
  42.             }
  43.         },
  44.         "type": "ASYNC"
  45.     }
  46. }
  47.  MySQL  ytt-pc:40100+ ssl  JS >

2.2 配置 MySQLRouter 路由实例

先用 MySQLrouter 来配置读写分离，并且分配虚拟端口
  1. root@ytt-pc:/home/ytt/scripts# mysqlrouter --bootstrap ytt@ytt-pc:4010 --user=mysqlrouter --force-password-validation --report-host ytt-pc
  2. Please enter MySQL password for ytt:

  4. # Bootstrapping system MySQL Router instance...

  6. - Creating account(s) (only those that are needed, if any)
  7. - Verifying account (using it to run SQL queries that would be run by Router)
  8. - Storing account in keyring
  9. - Adjusting permissions of generated files
  10. - Creating configuration /etc/mysqlrouter/mysqlrouter.conf

  12. Existing configuration backed up to '/etc/mysqlrouter/mysqlrouter.conf.bak'

  14. # MySQL Router configured for the InnoDB ReplicaSet 'ytt_replicaset'

  16. After this MySQL Router has been started with the generated configuration

重启 mysqlrouter

  1. $ /etc/init.d/mysqlrouter restart
  2. or
  3. $ systemctl start mysqlrouter
  4. or
  5. $ mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf
  6. //重启完毕
  7. the cluster 'ytt_replicaset' can be reached by connecting to:

  9. ## MySQL Classic protocol

  11. - Read/Write Connections: ytt-pc:6446
  12. - Read/Only Connections:  ytt-pc:6447

  14. ## MySQL X protocol

  16. - Read/Write Connections: ytt-pc:64460
  17. - Read/Only Connections:  ytt-pc:64470

  19. 重启mysqlrouter
  20. root@ytt-pc:/home/ytt/scripts# systemctl restart mysqlrouter

查看 router 路由

用 listRouters 方法详细的展示出 mysqlrouter 的路由信息，写端口为 6446/64460，读端口为 6447/64470
  1.  MySQL  ytt-pc:40100+ ssl  JS > c1.listRouters()
  2. {
  3.     "replicaSetName": "ytt_replicaset",
  4.     "routers": {
  5.         "ytt-pc::system": {
  6.             "hostname": "ytt-pc",
  7.             "lastCheckIn": "2020-01-19 17:26:08",
  8.             "roPort": 6447,
  9.             "roXPort": 64470,
  10.             "rwPort": 6446,
  11.             "rwXPort": 64460,
  12.             "version": "8.0.19"
  13.         }
  14.     }
  15. }

2.3 测试读写端口

连接 6446，路由到实例 4010
  1. root@ytt-pc:/home/ytt/scripts# mysql -uytt -p -hytt-pc -P6446 -e "select @@port" -ss
  2. Enter password:
  3. 4010
连接到 6447，随机路由到实例 4011 或者 4012
  1. root@ytt-pc:/home/ytt/scripts# mysql -uytt -p -hytt-pc -P6447 -e "select @@port" -ss
  2. Enter password:
  3. 4011
  4. root@ytt-pc:/home/ytt/scripts# mysql -uytt -p -hytt-pc -P6447 -e "select @@port" -ss
  5. Enter password:
  6. 4012

以上简单的演示下 mysql shell 操作主从复制的基本方法，下来分别看看主从级别互换、从实例删除、从实例加入场景。

2.4 场景实验

断开连接

  1. MySQL  ytt-pc:40100+ ssl  JS > c1.disconnect();

重新得到副本集的名称

  1. MySQL  ytt-pc:40100+ ssl  JS > var c1 = dba.getReplicaSet()
  2. You are connected to a member of replicaset 'ytt_replicaset'.
提升从机为新主机，这里操作的实例是 ytt-pc:4012
  1.  MySQL  ytt-pc:40100+ ssl  JS > c1.setPrimaryInstance('ytt@ytt-pc:4012');
  2. ytt-pc:4012 will be promoted to PRIMARY of 'ytt_replicaset'.
  3. The current PRIMARY is ytt-pc:4010.

  5. - Connecting to replicaset instances
  6.   ** Connecting to ytt-pc:4010
  7.   ** Connecting to ytt-pc:4011
  8.   ** Connecting to ytt-pc:4012
  9.   ** Connecting to ytt-pc:4010
  10.   ** Connecting to ytt-pc:4011
  11.   ** Connecting to ytt-pc:4012
  12. - Performing validation checks
  13.   ** Checking async replication topology...
  14.   ** Checking transaction state of the instance...
  15. - Synchronizing transaction backlog at ytt-pc:4012
  16. - Updating metadata
  17. - Acquiring locks in replicaset instances
  18.   ** Pre-synchronizing SECONDARIES
  19.   ** Acquiring global lock at PRIMARY
  20.   ** Acquiring global lock at SECONDARIES
  21. - Updating replication topology
  22.   ** Configuring ytt-pc:4010 to replicate from ytt-pc:4012
  23.   ** Changing replication source of ytt-pc:4011 to ytt-pc:4012

  25. ytt-pc:4012 was promoted to PRIMARY.
查看新状态，4012 为新的主机，4011 和 4010 为从机
  1.  MySQL  ytt-pc:40100+ ssl  JS > c1.status()
  2. {
  3.     "replicaSet": {
  4.         "name": "ytt_replicaset",
  5.         "primary": "ytt-pc:4012",
  6.         "status": "AVAILABLE",
  7.         "statusText": "All instances available.",
  8.         "topology": {
  9.             "ytt-pc:4010": {
  10.                 "address": "ytt-pc:4010",
  11.                 "instanceRole": "SECONDARY",
  12.                 "mode": "R/O",
  13.                 "replication": {
  14.                     "applierStatus": "APPLIED_ALL",
  15.                     "applierThreadState": "Waiting for an event from Coordinator",
  16.                     "applierWorkerThreads": 4,
  17.                     "receiverStatus": "ON",
  18.                     "receiverThreadState": "Waiting for master to send event",
  19.                     "replicationLag": null
  20.                 },
  21.                 "status": "ONLINE"
  22.             },
  23.             "ytt-pc:4011": {
  24.                 "address": "ytt-pc:4011",
  25.                 "instanceRole": "SECONDARY",
  26.                 "mode": "R/O",
  27.                 "replication": {
  28.                     "applierStatus": "APPLIED_ALL",
  29.                     "applierThreadState": "Waiting for an event from Coordinator",
  30.                     "applierWorkerThreads": 4,
  31.                     "receiverStatus": "ON",
  32.                     "receiverThreadState": "Waiting for master to send event",
  33.                     "replicationLag": null
  34.                 },
  35.                 "status": "ONLINE"
  36.             },
  37.             "ytt-pc:4012": {
  38.                 "address": "ytt-pc:4012",
  39.                 "instanceRole": "PRIMARY",
  40.                 "mode": "R/W",
  41.                 "status": "ONLINE"
  42.             }
  43.         },
  44.         "type": "ASYNC"
  45.     }
  46. }
  47.  MySQL  ytt-pc:40100+ ssl  JS >
从副本集中移除实例 4010
  1.   MySQL  ytt-pc:40100+ ssl  JS > c1.removeInstance("ytt@ytt-pc:4010");
  2. The instance 'ytt-pc:4010' was removed from the replicaset.
从结果看到，4010 被成功移除，接下来再把 4010 添加进去
  1.  MySQL  ytt-pc:40100+ ssl  JS > c1.addInstance("ytt@ytt-pc:4010")
  2. Adding instance to the replicaset...
  3. * Updating topology
  4. ** Configuring ytt-pc:4010 to replicate from ytt-pc:4012
  5. ** Waiting for new instance to synchronize with PRIMARY...

  7. The instance 'ytt-pc:4010' was added to the replicaset and is replicating from ytt-pc:4012.

三、总结

这篇介绍了 MySQL Shell 的新特性，管理 MySQL 主从复制，可以看到比传统的方式要简单的多。

分类： MySQL 新特性技术文章
标签：

相关文章

OceanBase

OB 运维 | Oracle 迁移到 OB 过程中有哪些不兼容的 DDL 语法？

本文将总结 Oracle 迁移到 OB 4.x 过程中，那些还不兼容的 阅读更多…

一问一实验

第 58 期：MySQL Slave 异常掉电后主从同步出现异常怎么排查？

社区王牌专栏《一问一实验：AI 版》改版以来已发布多期（51-57）， 阅读更多…

SQLE SQL审核工具

SQLE 3.2407.0 发布，全面提升多项目场景体验！

本周我们发布了 SQLE 3.2407.0 正式版！主要更新概览： 社 阅读更多…