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

引言

之前，我介绍过一篇《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

3. log-bin=ytt

4. enforce_gtid_consistency=on

5. gtid_mode=on

6. log_slave_updates=on

7. master-info-repository=table

8. relay-log-info-repository=table

9. slave_parallel_type=logical

10. 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

3. Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved.

4. Oracle is a registered trademark of Oracle Corporation and/or its affiliates.

5. Other names may be trademarks of their respective owners.

6. Type '\help' or '\?' for help; '\quit' to exit.

7. Creating an X protocol session to 'ytt@ytt-pc:40100'

8. Fetching schema names for autocompletion... Press ^C to stop.

9. Your MySQL connection id is 12 (X protocol)

10. Server version: 8.0.19 MySQL Community Server - GPL

11. 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.

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

4. This instance reports its own address as ytt-pc:4010

5. ytt-pc:4010: Instance configuration is suitable.

6. * Updating metadata...

7. ReplicaSet object successfully created for ytt-pc:4010.

8. 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...

3. * Performing validation checks

4. This instance reports its own address as ytt-pc:4011

5. ytt-pc:4011: Instance configuration is suitable.

6. * Checking async replication topology...

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

8. 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.

9. ytt-pc:4011 has the following errant GTIDs that do not exist in the replicaset:

10. a19a4ac4-3a98-11ea-8f47-9cb6d0e27d15:1-7

11. 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'.

12. 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.

13. Please select a recovery method [C]lone/[A]bort (default Abort): C

14. * Updating topology

15. Waiting for clone process of the new member to complete. Press ^C to abort the operation.

16. * Waiting for clone to finish...

17. NOTE: ytt-pc:4011 is being cloned from ytt-pc:4010

18. ** Stage DROP DATA: Completed

19. ** Clone Transfer

20. FILE COPY ############################################################ 100% Completed

21. PAGE COPY ############################################################ 100% Completed

22. REDO COPY ############################################################ 100% Completed

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

24. * Waiting for server restart... ready

25. * ytt-pc:4011 has restarted, waiting for clone to finish...

26. * Clone process has finished: 59.64 MB transferred in about 1 second (~1.00 B/s)

27. ** Configuring ytt-pc:4011 to replicate from ytt-pc:4010

28. ** Waiting for new instance to synchronize with PRIMARY...

29. 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:

3. # Bootstrapping system MySQL Router instance...

4. - Creating account(s) (only those that are needed, if any)

5. - Verifying account (using it to run SQL queries that would be run by Router)

6. - Storing account in keyring

7. - Adjusting permissions of generated files

8. - Creating configuration /etc/mysqlrouter/mysqlrouter.conf

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

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

11. 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:

8. ## MySQL Classic protocol

9. - Read/Write Connections: ytt-pc:6446

10. - Read/Only Connections: ytt-pc:6447

11. ## MySQL X protocol

12. - Read/Write Connections: ytt-pc:64460

13. - Read/Only Connections: ytt-pc:64470

14. 重启mysqlrouter

15. 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.

4. - Connecting to replicaset instances

5. ** Connecting to ytt-pc:4010

6. ** Connecting to ytt-pc:4011

7. ** Connecting to ytt-pc:4012

8. ** Connecting to ytt-pc:4010

9. ** Connecting to ytt-pc:4011

10. ** Connecting to ytt-pc:4012

11. - Performing validation checks

12. ** Checking async replication topology...

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

14. - Synchronizing transaction backlog at ytt-pc:4012

15. - Updating metadata

16. - Acquiring locks in replicaset instances

17. ** Pre-synchronizing SECONDARIES

18. ** Acquiring global lock at PRIMARY

19. ** Acquiring global lock at SECONDARIES

20. - Updating replication topology

21. ** Configuring ytt-pc:4010 to replicate from ytt-pc:4012

22. ** Changing replication source of ytt-pc:4011 to ytt-pc:4012

23. 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...

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

三、总结

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

社区近期动态

 点一下“阅读原文”了解更多资讯