技术分享 | innodb-cluster 扫盲-安装篇

于由杨涛涛发布

本文介绍用 MySQL Shell 搭建 MGR 的详细过程。

1、使用前，关掉防火墙，包括 selinux，firewalld，或者 MySQL 企业版的firewall（如果用了企业版的话）

2、两台机器：（4 台 MySQL 实例）
  1. 192.168.2.219 centos-ytt57-1 3311/3312
  2. 192.168.2.229 centos-ytt57-2 3311/3312
3、安装 MySQL（两台都装）, MySQL Shell（任意一台）, mysqlrouter(任意一台，官方建议和应用程序装在一个服务器上)
  1. yum install mysql-community-server mysql-shell mysql-router-community

4、搭建 InnoDB-Cluster（两台都装）

1. 配置文件如下：
  1. shell>vi /etc/my.cnf
  2. master-info-repository=table
  3. relay-log-info-repository=table
  4. gtid_mode=ON
  5. enforce_gtid_consistency=ON
  6. binlog_checksum=NONE
  7. log_slave_updates=ON
  8. binlog_format=ROW
  9. transaction_write_set_extraction=XXHASH64

2. 系统 HOSTS 配置（两台都配）

  1. shell>vi /etc/hosts

  3. 127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
  4. ::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
  5. 192.168.2.219 centos-ytt57-2
  6. 192.168.2.229 centos-ytt57-3
用 MySQL coalesce 函数确认下 report-host 是否设置正确
  1. (root@localhost) : [(none)] >SELECT coalesce(@@report_host, @@hostname) as r;
  2. +----------------+
  3. | r              |
  4. +----------------+
  5. | centos-ytt57-1 |
  6. +----------------+
  7. 1 row in set (0.00 sec)
3. 创建管理员用户搭建 GROUP REPLICATION （四个节点都要）
  1. create user root identified by 'Root@123';
  2. grant all on *.* to root with grant option;
  3. flush privileges;
4. MySQLsh 连接其中一台节点：
  1. [root@centos-ytt57-1 mysql]# mysqlsh root@localhost:3321

5. 检查配置正确性：（如果这里不显示 OK，把对应的参数加到配置文件重启 MySQL 再次检查） dba.checkInstanceConfiguration(“root@centos-ytt57-2:3311”); dba.checkInstanceConfiguration(“root@centos-ytt57-2:3312”); dba.checkInstanceConfiguration(“root@centos-ytt57-3:3311”); dba.checkInstanceConfiguration(“root@centos-ytt57-3:3312”);

mysqlsh 执行检测
  1. [root@centos-ytt57-1 mysql]# mysqlsh --log-level=8 root@localhost:3311

  3. MySQL  localhost:3311 ssl  JS > dba.checkInstanceConfiguration("root@centos-ytt57-2:3311")
  4. {
  5.   "status": "ok"
  6. }
6. 创建集群，节点 1 上创建。（结果显示 Cluster successfully created 表示成功）
  1. MySQL  localhost:3311 ssl  JS > var cluster = dba.createCluster('ytt_mgr');

  3. Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
  4. At least 3 instances are needed for the cluster to be able to withstand up to
  5. one server failure.

7. 添加节点 2，3，4（全部显示 OK，表示添加成功）

  1. MySQL  localhost:3311 ssl  JS >  cluster.addInstance('root@centos-ytt57-2:3312');
  2. MySQL  localhost:3311 ssl  JS >  cluster.addInstance('root@centos-ytt57-3:3311');
  3. MySQL  localhost:3311 ssl  JS >  cluster.addInstance('root@centos-ytt57-3:3312');

8. 查看拓扑图：（describe 简单信息，status 详细描述）

  1. MySQL  localhost:3311 ssl  JS > cluster.describe()
  2. {
  3. "clusterName": "ytt_mgr",
  4. "defaultReplicaSet": {
  5.   "name": "default",
  6.   "topology": [
  7.       {
  8.           "address": "centos-ytt57-2:3311",
  9.           "label": "centos-ytt57-2:3311",
  10.           "role": "HA",
  11.           "version": "8.0.17"
  12.       },
  13.       {
  14.           "address": "centos-ytt57-2:3312",
  15.           "label": "centos-ytt57-2:3312",
  16.           "role": "HA",
  17.           "version": "8.0.17"
  18.       },
  19.       {
  20.           "address": "centos-ytt57-3:3311",
  21.           "label": "centos-ytt57-3:3311",
  22.           "role": "HA",
  23.           "version": "8.0.17"
  24.       },
  25.       {
  26.           "address": "centos-ytt57-3:3312",
  27.           "label": "centos-ytt57-3:3312",
  28.           "role": "HA",
  29.           "version": "8.0.17"
  30.       }
  31.   ],
  32.   "topologyMode": "Single-Primary"
  33. }
  34. }

  36. MySQL  localhost:3311 ssl  JS > cluster.status()

  38. "clusterName": "ytt_mgr",
  39. "defaultReplicaSet": {
  40.   "name": "default",
  41.   "primary": "centos-ytt57-2:3311",
  42.   "ssl": "REQUIRED",
  43.   "status": "OK",
  44.   "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
  45.   "topology": {
  46.       "centos-ytt57-2:3311": {
  47.           "address": "centos-ytt57-2:3311",
  48.           "mode": "R/W",
  49.           "readReplicas": {},
  50.           "role": "HA",
  51.           "status": "ONLINE",
  52.           "version": "8.0.17"
  53.       },
  54.       "centos-ytt57-2:3312": {
  55.           "address": "centos-ytt57-2:3312",
  56.           "mode": "R/O",
  57.           "readReplicas": {},
  58.           "role": "HA",
  59.           "status": "ONLINE",
  60.           "version": "8.0.17"
  61.       },
  62.       "centos-ytt57-3:3311": {
  63.           "address": "centos-ytt57-3:3311",
  64.           "mode": "R/O",
  65.           "readReplicas": {},
  66.           "role": "HA",
  67.           "status": "ONLINE",
  68.           "version": "8.0.17"
  69.       },
  70.       "centos-ytt57-3:3312": {
  71.           "address": "centos-ytt57-3:3312",
  72.           "mode": "R/O",
  73.           "readReplicas": {},
  74.           "role": "HA",
  75.           "status": "ONLINE",
  76.           "version": "8.0.17"
  77.       }
  78.   },
  79.   "topologyMode": "Single-Primary"
  80. },
  81. "groupInformationSourceMember": "centos-ytt57-2:3311"
9. 简单测试下数据是否同步
  1. (root@localhost) : [(none)] >create database ytt;
  2. Query OK, 1 row affected (0.03 sec)

  4. (root@localhost) : [(none)] >use ytt;
  5. Database changed
  6. (root@localhost) : [ytt] >create table p1(id int primary key, log_time datetime);
  7. Query OK, 0 rows affected (0.08 sec)

  9. (root@localhost) : [ytt] >insert into p1 values (1,now());
  10. Query OK, 1 row affected (0.04 sec)

  12. (root@localhost) : [ytt] >show master status;
  13. +---------------+----------+--------------+------------------+-------------------------------------------+
  14. | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
  15. +---------------+----------+--------------+------------------+-------------------------------------------+
  16. | mysql0.000001 |    25496 |              |                  | 6c7bb9db-b759-11e9-a9c0-0800276cf0fc:1-41 |
  17. +---------------+----------+--------------+------------------+-------------------------------------------+
  18. 1 row in set (0.00 sec)
查看其他三个节点
  1. (root@localhost) : [ytt] >show tables;
  2. +---------------+
  3. | Tables_in_ytt |
  4. +---------------+
  5. | p1            |
  6. +---------------+
  7. 1 row in set (0.00 sec)

  9. (root@localhost) : [ytt] >select * from p1;
  10. +----+---------------------+
  11. | id | log_time            |
  12. +----+---------------------+
  13. |  1 | 2019-08-05 16:44:20 |
  14. +----+---------------------+
  15. 1 row in set (0.00 sec)
停掉主节点：
  1. [root@centos-ytt57-2 mysql0]# systemctl stop mysqld@0
现在查看，主节点已经变为本机 3312节点
  1. "centos-ytt57-2:3312": {
  2.    "address": "centos-ytt57-2:3312",
  3.    "mode": "R/W",
  4.    "readReplicas": {},
  5.    "role": "HA",
  6.    "status": "ONLINE"
  7. }

10. 报错处理

错误日志里显示
  1. 2019-08-05T09:01:35.125591Z 0 [ERROR] Plugin group_replication reported: 'The group name option is mandatory'
  2. 2019-08-05T09:01:35.125622Z 0 [ERROR] Plugin group_replication reported: 'Unable to start Group Replication on boot'
同时用 cluster.rescan() 扫描，发现
  1. The instance 'centos-ytt57-2:3311' is no longer part of the ReplicaSet.
重新加入此节点到集群：
  1. cluster.rejoinInstance('centos-ytt57-2:3311')
再次执行cluster.status()查看集群状态：”status”: “OK”,
11. 移除和加入
  1. cluster.removeInstance("centos-ytt57-3:3312");
  2. The instance 'centos-ytt57-3:3312' was successfully removed from the cluster.
  3. cluster.addInstance("centos-ytt57-3:3312");
  4. The instance 'centos-ytt57-3:3312' was successfully added to the cluster.
12. 用 mysqlrouter 生成连接 MGR 相关信息。
涉及到两个用户：
–user=mysqlrouter 是使用mysqlrouter的系统用户
自动创建的MySQL 用户是用来与MGR通信的用户。如果想查看这个用户的用户名以及密码，就加上
–force-password-validation，不过一般也没有必要查看。
  1. [root@centos-ytt57-2 ytt]# mysqlrouter --bootstrap root@centos-ytt57-2:3311 --user=mysqlrouter --force-password-validation --report-host centos-ytt57-2
  2. Please enter MySQL password for root:

  4. # Reconfiguring system MySQL Router instance...

  6. - Checking for old Router accounts
  7. - Found old Router accounts, removing
  8. - Creating mysql account mysql_router1_rdr89tx20r0a@'%' for cluster management
  9. - Storing account in keyring
  10. - Adjusting permissions of generated files
  11.  - Creating configuration /etc/mysqlrouter/mysqlrouter.conf

  13. # MySQL Router configured for the InnoDB cluster 'ytt_mgr'

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

  17.    $ /etc/init.d/mysqlrouter restart
  18. or
  19.    $ systemctl start mysqlrouter
  20. or
  21.    $ mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf

  23. the cluster 'ytt_mgr' can be reached by connecting to:

  25. ## MySQL Classic protocol

  27. - Read/Write Connections: centos-ytt57-2:6446
  28. - Read/Only Connections:  centos-ytt57-2:6447

  30. ## MySQL X protocol

  32. - Read/Write Connections: centos-ytt57-2:64460
  33. - Read/Only Connections:  centos-ytt57-2:64470

13. 启动 mysqlrouter，生效对应服务。

  1. [root@centos-ytt57-2 mysqlrouter]# systemctl start mysqlrouter
14. 使用 ROUTER 连接 MySQL

创建一个普通用户使用 ROUTER (四个节点都建立，具体权限看个人)

  1. [root@centos-ytt57-2 mysqlrouter]# /home/ytt/enter_mysql 80
  2. mysql: [Warning] Using a password on the command line interface can be insecure.
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.
  4. Your MySQL connection id is 1779
  5. Server version: 8.0.17 MySQL Community Server - GPL

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

  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.

  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

  15. (root@centos-ytt57-2) : [(none)] >create user ytt_mysqlrouter;
  16. Query OK, 0 rows affected (0.12 sec)

  18. (root@centos-ytt57-2) : [(none)] >alter user ytt_mysqlrouter identified by 'mysql_router';
  19. Query OK, 0 rows affected (0.09 sec)

  21. (root@centos-ytt57-2) : [(none)] >grant all on ytt.* to ytt_mysqlrouter;
  22. Query OK, 0 rows affected (0.05 sec)

  24. (root@centos-ytt57-2) : [(none)] >flush privileges;
  25. Query OK, 0 rows affected (0.07 sec)
14.1 写入端口连接：
  1. [root@centos-ytt57-2 mysqlrouter]# mysql -uytt_mysqlrouter -pmysql_router -hcentos-ytt57-2 -P6446
  2. mysql: [Warning] Using a password on the command line interface can be insecure.
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.
  4. Your MySQL connection id is 2030
  5. Server version: 8.0.17 MySQL Community Server - GPL

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

  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.

  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

  15. (ytt_mysqlrouter@centos-ytt57-2) : [(none)] >use ytt
  16. Reading table information for completion of table and column names
  17. You can turn off this feature to get a quicker startup with -A

  19. Database changed
  20. (ytt_mysqlrouter@centos-ytt57-2) : [ytt] >insert into p1 values (200,now());
  21. Query OK, 1 row affected (0.04 sec)

  23. (ytt_mysqlrouter@centos-ytt57-2) : [ytt] >select * from p1;
  24. +-----+---------------------+
  25. | id  | log_time            |
  26. +-----+---------------------+
  27. |   1 | 2019-08-05 23:15:38 |
  28. |   2 | 2019-08-05 23:15:42 |
  29. | 100 | 2019-08-05 23:52:58 |
  30. | 200 | 2019-08-05 23:56:23 |
  31. +-----+---------------------+
  32. 4 rows in set (0.00 sec)

  34. (ytt_mysqlrouter@centos-ytt57-2) : [ytt] >\q
  35. Bye
  36. [root@centos-ytt57-2 mysqlrouter]#
14.2 读取端口连接：（默认循环选择读服务节点）
  1. [root@centos-ytt57-2 mysqlrouter]# mysql -uytt_mysqlrouter -pmysql_router -hcentos-ytt57-2 -P6447
  2. mysql: [Warning] Using a password on the command line interface can be insecure.
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.
  4. Your MySQL connection id is 472
  5. Server version: 8.0.17 MySQL Community Server - GPL

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

  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.

  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

  15. (ytt_mysqlrouter@centos-ytt57-2) : [(none)] >use ytt
  16. Reading table information for completion of table and column names
  17. You can turn off this feature to get a quicker startup with -A

  19. Database changed
  20. (ytt_mysqlrouter@centos-ytt57-2) : [ytt] >insert into p1 values (300,now());
  21. ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
  22. (ytt_mysqlrouter@centos-ytt57-2) : [ytt] >select * from p1;
  23. +-----+---------------------+
  24. | id  | log_time            |
  25. +-----+---------------------+
  26. |   1 | 2019-08-05 23:15:38 |
  27. |   2 | 2019-08-05 23:15:42 |
  28. | 100 | 2019-08-05 23:52:58 |
  29. | 200 | 2019-08-05 23:56:23 |
  30. +-----+---------------------+
  31. 4 rows in set (0.00 sec)

  33. ## (ytt_mysqlrouter@centos-ytt57-2) : [ytt] >\s

  35. mysql  Ver 8.0.17 for Linux on x86_64 (MySQL Community Server - GPL)

  37. Connection id:          472
  38. Current database:       ytt
  39. Current user:           ytt_mysqlrouter@centos-ytt57-2
  40. SSL:                    Cipher in use is DHE-RSA-AES128-GCM-SHA256
  41. Current pager:          stdout
  42. Using outfile:          ''
  43. Using delimiter:        ;
  44. Server version:         8.0.17 MySQL Community Server - GPL
  45. Protocol version:       10
  46. Connection:             centos-ytt57-2 via TCP/IP
  47. Server characterset:    utf8mb4
  48. Db     characterset:    utf8mb4
  49. Client characterset:    utf8mb4
  50. Conn.  characterset:    utf8mb4
  51. TCP port:               6447
  52. Uptime:                 45 min 10 sec

  54. ## Threads: 5  Questions: 188  Slow queries: 0  Opens: 257  Flush tables: 3  Open tables: 161  Queries per second avg: 0.069



  58. (ytt_mysqlrouter@centos-ytt57-2) : [ytt] >select @@server_id;
  59. +-------------+
  60. | @@server_id |
  61. +-------------+
  62. |           2 |
  63. +-------------+
  64. 1 row in set (0.01 sec)

  66. (ytt_mysqlrouter@centos-ytt57-2) : [ytt] >\q
  67. Bye

15. 机器全挂了，重启 MGR

  1. MySQL  localhost:3311 ssl  JS > dba.rebootClusterFromCompleteOutage()
  2. Reconfiguring the default cluster from complete outage...

  4. The instance 'centos-ytt57-2:3312' was part of the cluster configuration.
  5. Would you like to rejoin it to the cluster? [y/N]: y

  7. The instance 'centos-ytt57-3:3311' was part of the cluster configuration.
  8. Would you like to rejoin it to the cluster? [y/N]: y

  10. The instance 'centos-ytt57-3:3312' was part of the cluster configuration.
  11. Would you like to rejoin it to the cluster? [y/N]: y

  13. The safest and most convenient way to provision a new instance is through
  14. automatic clone provisioning, which will completely overwrite the state of
  15. 'localhost:3311' with a physical snapshot from an existing cluster member. To
  16. use this method by default, set the 'recoveryMethod' option to 'clone'.

  18. The incremental distributed state recovery may be safely used if you are sure
  19. all updates ever executed in the cluster were done with GTIDs enabled, there
  20. are no purged transactions and the new instance contains the same GTID set as
  21. the cluster or a subset of it. To use this method by default, set the
  22. 'recoveryMethod' option to 'incremental'.

  24. Incremental distributed state recovery was selected because it seems to be safely usable.

  26. ^C
  27. The cluster was successfully rebooted.

  29. Script execution interrupted by user.
  30. MySQL  localhost:3311 ssl  JS > var cluster = dba.getCluster('ytt_mgr');
  31. MySQL  localhost:3311 ssl  JS > cluste.status();
  32. ReferenceError: cluste is not defined
  33. MySQL  localhost:3311 ssl  JS > cluster.status();
  34. {
  35.    "clusterName": "ytt_mgr",
  36.    "defaultReplicaSet": {
  37.        "name": "default",
  38.        "primary": "centos-ytt57-2:3311",
  39.        "ssl": "REQUIRED",
  40.        "status": "OK",
  41.        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
  42.        "topology": {
  43.            "centos-ytt57-2:3311": {
  44.                "address": "centos-ytt57-2:3311",
  45.                "mode": "R/W",
  46.                "readReplicas": {},
  47.                "role": "HA",
  48.                "status": "ONLINE",
  49.                "version": "8.0.17"
  50.            },
  51.            "centos-ytt57-2:3312": {
  52.                "address": "centos-ytt57-2:3312",
  53.                "mode": "R/O",
  54.                "readReplicas": {},
  55.                "role": "HA",
  56.                "status": "ONLINE",
  57.                "version": "8.0.17"
  58.            },
  59.            "centos-ytt57-3:3311": {
  60.                "address": "centos-ytt57-3:3311",
  61.                "mode": "R/O",
  62.                "readReplicas": {},
  63.                "role": "HA",
  64.                "status": "ONLINE",
  65.                "version": "8.0.17"
  66.            },
  67.            "centos-ytt57-3:3312": {
  68.                "address": "centos-ytt57-3:3312",
  69.                "mode": "R/O",
  70.                "readReplicas": {},
  71.                "role": "HA",
  72.                "status": "ONLINE",
  73.                "version": "8.0.17"
  74.            }
  75.        },
  76.        "topologyMode": "Single-Primary"
  77.    },
  78.    "groupInformationSourceMember": "centos-ytt57-2:3311"
  79. }

社区近期动态

分类： 技术分享技术文章

相关文章

技术分享

技术分享 | MySQL 创建表后神秘消失？揭秘零宽字符陷阱

三招教你解决零宽字符陷阱，让你的数据库不再”闹鬼“。 作者：秦福朗，爱 阅读更多…

技术分享

故障分析 | 一则 MySQL 从节点 hung 死问题分析

作者通过 MySQL 从节点的一个 hung 问题，对数据库连接、日志 阅读更多…

技术分享

技术译文 | MySQL InnoDB Online DDL 算法更新

在 MySQL 8.0.12 中，我们引入了一种新的 DDL 算法，该 阅读更多…