devbox
黑客灵魂
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

手把手教你安装 MySQL InnoDB Cluster 8.0.34

作者：黑客灵魂 | 2024-08-20 08:47:58

赞

踩

mysql cluster安装

f9fc7a184a339695e83db1d6a5f7e3e4.gif

作者 | JiekeXu

来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT）

如需转载请联系授权 | (个人微信 ID：JiekeXu_DBA)

大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来手把手教你安装 MySQL InnoDB Cluster 8.0.34，欢迎点击上方蓝字“JiekeXu DBA之路”关注我的公众号，标星或置顶，更多干货第一时间到达！

MySQL InnoDB Cluster 提供完整的高可用性 MySQL 解决方案。通过使用 MySQL Shell 附带的 AdminAPI，您可以轻松配置和管理至少三个 MySQL 服务器的组实例以充当 InnoDB 集群。

InnoDB Cluster 主要使用如下三大主件：
● MySQL Shell
● MySQL服务器和组复制
● MySQL Router

InnoDB 集群中的每个 MySQL 服务器实例都运行 MySQL Group Replication，它提供了在 InnoDB 集群内复制数据的机制，并具有内置的故障切换。Admin API 消除了在 InnoDB 集群中直接使用 Group Replication 的需要，但有关更多信息，请参阅解释详细信息的 Group Replication。从 MySQL 8.0.27 开始，您还可以设置 InnoDB ClusterSet，通过将主 InnoDB 集群与其他位置（如不同的数据中心）的一个或多个副本链接，为 InnoDB 集群部署提供容灾能力。

MySQL Router 可以根据您部署的集群自动配置，将客户端应用程序透明地连接到服务器实例。在服务器实例发生意外故障的情况下，群集会自动重新配置。在默认的单主模式中，InnoDB 集群有一个读写服务器实例-主实例。多个辅助服务器实例是主服务器的副本。如果主服务器出现故障，辅助服务器将自动升级为主服务器。MySQL Router 检测到这一点，并将客户端应用程序转发到新的主应用程序。

如下是官方 MySQL InnoDB Cluster 架构简图：

27debeaab2c8e90739458f5c3002f762.png

下图是我三台 Linux7 部署架构图，IP 分别为 56、57、58，56 Node1 为主节点，并安装 MySQL 和 router，57 Node2、58 Node3 为从节点,也安装 MySQL 和 router，并在 Node3 安装了 shell。Node3 的 shell 用来安装 InnoDB Cluster，其他每个节点的 router 地址则作为高可用地址分配给 K8S 应用容器。当然官方建议将 router 和应用容器一起部署，我这么部署和使用也没有太大的问题。

3a9dd19bfa483b8c734756914c8042f4.png

下面开始使用最传统的方式从零开始安装 MySQL  InnoDB Cluster 8.0.34 单主模式，Linux X86 操作系统已安装好，并且 yum 源配置完成，1T /data 文件系统已挂载，所需软件包已下载并上传到服务器。也可参考去年的主从搭建文档----Linux 环境搭建 MySQL8.0.28 主从同步环境。

  1. --所需的软件包
  2. https://dev.mysql.com/downloads/mysql/8.0.html
  3.  
  4.  
  5. mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz
  6. mysql-shell-8.0.34-1.el7.x86_64.rpm
  7. mysql-router-8.0.34-linux-glibc2.12-x86_64.tar.xz

一、系统准备

以下 6 小节操作需要在三台主机执行。

1、centos7 关闭防火墙
  1. systemctl stop firewalld.service 或者 systemctl stop firewalld
  2. systemctl disable firewalld.service 或者 systemctl disable firewalld
  3. systemctl status firewalld
2、关闭 selinux
  1. getenforce 
  2. setenforce 0
  3.  
  4.  
  5. vim /etc/selinux/config
  6.  
  7.  
  8. SELINUX=disabled
3、绑定 /etc/hosts 解析(示例如下)
  1. vim /etc/hosts
  2.  
  3.  
  4. 192.168.27.56  jiekexu-my-56 
  5. 192.168.27.57  jiekexu-my-57
  6. 192.168.27.58  jiekexu-my-58
4、安装 yum 包
  1. yum -y groupinstall "DeveLopment tools"
  2. yum -y install ncurses ncurses-devel openssl-devel bison gcc gcc-c++ make
5、清理系统自带 MariaDB 环境

CentOS 7 版本的系统默认自带安装了 MariaDB，需要先清理。

  1. ## 查询已安装的mariadb
  2. rpm -qa |grep mariadb
  3. 或
  4. yum list installed | grep mariadb
  5. ## 卸载 mariadb 包，文件名为上述命令查询出来的文件
  6. rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64
  7. yum -y remove mariadb-libs.x86_64
6、内核参数修改
  1. [root@jiekexu-my-56 ~]# cat>>/etc/sysctl.conf <<EOF
  2. fs.aio-max-nr = 1048576
  3. fs.file-max = 681574400
  4. kernel.shmmax = 137438953472
  5. kernel.shmmni = 4096
  6. kernel.sem = 250 32000 100 200
  7. net.ipv4.ip_local_port_range = 9000 65000
  8. net.core.rmem_default = 262144
  9. net.core.rmem_max = 4194304
  10. net.core.wmem_default = 262144
  11. net.core.wmem_max = 1048586
  12. EOF
  13.  
  14.  
  15. [root@jiekexu-my-56  ~]# sysctl -p
  16.  
  17.  
  18. [root@jiekexu-my-56  ~]# cat>>/etc/security/limits.conf <<EOF
  19. mysql soft nproc 65536
  20. mysql hard nproc 65536
  21. mysql soft nofile 65536
  22. mysql hard nofile 65536
  23. EOF
  24.  
  25.  
  26. [root@jiekexu-my-56  ~]# cat>>/etc/pam.d/login <<EOF
  27. session required /lib/security/pam_limits.so
  28. session required pam_limits.so
  29. EOF
  30.  
  31.  
  32. [root@jiekexu-my-56  ~]# cat>>/etc/profile<<EOF
  33. if [ $USER = "mysql" ]; then
  34. ulimit -u 16384 -n 65536
  35. fi
  36. EOF

二、安装 MySQL Server

1、三台主机创建数据库用户，创建实例所需目录

如果没有 /data 文件系统，可按照下面目录结构创建相对应的目录。
root 用户操作：

  1. mkdir -p /app/
  2. mkdir -p /data/mysqldb/conf/
  3. mkdir -p /data/mysqldb/data/
  4. mkdir -p /data/mysqldb/pid/
  5. mkdir -p /data/mysqldb/socket/
  6. mkdir -p /data/mysqldb/log/
  7. mkdir -p /data/mysqldb/binlog/
  8. mkdir -p /data/mysqldb/relaylog/
  9. mkdir -p /data/mysqldb/slowlog/
  10. mkdir -p /data/mysqldb/tmp/
  11.  
  12.  
  13. groupadd mysql
  14. useradd -g mysql mysql
  15. chown -R mysql:mysql /data
  16. chown -R mysql:mysql /app
  17. passwd mysql
  18.  
  19.  
  20. --MySQL
2、三台主机解压 MySQL server 安装程序包
  1. mysql 用户操作
  2. cd /app
  3. tar xvf mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz
  4. mv  mysql-8.0.34-linux-glibc2.12-x86_64 mysql8.0.34
3、三台主机配置 mysql 用户环境变量
  1. vim ~/.bash_profile
  2. MYSQL_HOME=/app/mysql8.0.34
  3. PATH=$PATH:$HOME/.local/bin:$HOME/bin:$MYSQL_HOME/bin
  4. source ~/.bash_profile
  5. which mysql
4、上传 mysql 初始化参数文件并初始化(参数文件名 mysql.conf)

Node1 参数文件如下,Node2 和 Node3 节点需要注意server_id 不能重复，innodb_buffer_pool_size 内存一般设置为 OS 的 50%，report_host 写本机地址，loose_group_replication_local_address 写本机地址和端口号。

  1. vim  /data/mysqldb/conf/mysql.conf
  2.  
  3.  
  4. [mysqld]
  5. # basic settings #
  6. default-storage-engine= InnoDB
  7. server_id = 2756
  8. basedir = /app/mysql8.0.34
  9. datadir = /data/mysqldb/data/
  10. socket = /data/mysqldb/socket/mysql.sock
  11. pid_file = /data/mysqldb/pid/mysqld.pid
  12. port = 33066
  13. default-time_zone = '+8:00'
  14. character_set_server = utf8mb4
  15. explicit_defaults_for_timestamp = 1
  16. autocommit = 1
  17. transaction_isolation = READ-COMMITTED
  18. secure_file_priv = "/data/mysqldb/tmp/"
  19. max_allowed_packet = 64M
  20. lower_case_table_names = 1
  21. default_authentication_plugin = mysql_native_password
  22. sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
  23.  
  24.  
  25. # connection #
  26. back_log = 500
  27. interactive_timeout = 300
  28. wait_timeout = 1800
  29. lock_wait_timeout = 300
  30. max_user_connections = 800
  31. skip_name_resolve = 1
  32. max_connections = 2500
  33. max_connect_errors = 1000
  34.  
  35.  
  36. #table cache performance settings
  37. #table_open_cache = 1024
  38. #table_definition_cache = 1024
  39. #table_open_cache_instances = 16
  40.  
  41.  
  42. #session memory settings #
  43. #read_buffer_size = 16M
  44. #read_rnd_buffer_size = 32M
  45. #sort_buffer_size = 32M
  46. #tmp_table_size = 64M
  47. #join_buffer_size = 128M
  48. #thread_cache_size = 256
  49.  
  50.  
  51. # log settings #
  52. slow_query_log = ON
  53. slow_query_log_file = /data/mysqldb/slowlog/slow.log
  54. log_error = /data/mysqldb/log/mysqld.log
  55. log_error_verbosity = 3
  56. log_bin = /data/mysqldb/binlog/mysql_bin
  57. log_bin_index = /data/mysqldb/binlog/mysql_binlog.index
  58. # general_log_file = /data/mysql/generallog/general.log
  59. log_queries_not_using_indexes = 1
  60. log_slow_admin_statements = 1
  61. #log_slow_slave_statements = 1
  62. #expire_logs_days = 15
  63. binlog_expire_logs_seconds = 2592000
  64. long_query_time = 5
  65. min_examined_row_limit = 100
  66. log_throttle_queries_not_using_indexes = 1000
  67. #log_bin_trust_function_creators = 1
  68. log_replica_updates = 1
  69. mysqlx_port = 33060
  70. mysqlx_socket = /data/mysqldb/socket/mysqlx.sock
  71.  
  72.  
  73. # innodb settings #
  74. innodb_buffer_pool_size = 16G
  75. innodb_buffer_pool_instances = 4
  76. innodb_log_buffer_size = 100M
  77. innodb_buffer_pool_load_at_startup = 1
  78. innodb_buffer_pool_dump_at_shutdown = 1
  79. innodb_lru_scan_depth = 4096
  80. innodb_lock_wait_timeout = 20
  81. innodb_io_capacity = 5000
  82. innodb_io_capacity_max = 10000
  83. innodb_flush_method = O_DIRECT
  84. innodb_log_file_size = 1G
  85. innodb_log_files_in_group = 3
  86. innodb_purge_threads = 4
  87. innodb_thread_concurrency = 200
  88. innodb_print_all_deadlocks = 1
  89. innodb_print_ddl_logs = 1
  90. innodb_strict_mode = 1
  91. innodb_sort_buffer_size = 32M
  92. innodb_write_io_threads = 16
  93. innodb_read_io_threads = 16 
  94. innodb_file_per_table = 1
  95. innodb_stats_persistent_sample_pages = 64
  96. innodb_autoinc_lock_mode = 2
  97. innodb_online_alter_log_max_size = 1G
  98. innodb_open_files = 4096
  99. innodb_buffer_pool_dump_pct = 25
  100. innodb_page_cleaners = 16
  101. innodb_undo_log_truncate = 1
  102. innodb_max_undo_log_size = 2G
  103. innodb_purge_rseg_truncate_frequency = 128
  104. innodb_flush_log_at_trx_commit = 1
  105. log_timestamps = UTC
  106.  
  107.  
  108. # replication settings #
  109. sync_binlog = 1
  110. binlog_format = ROW
  111. gtid_mode = ON
  112. enforce_gtid_consistency = ON
  113. relay_log_recovery = 1
  114. relay_log = /data/mysqldb/relaylog/relay.log
  115. relay_log_index = /data/mysqldb/relaylog/mysql_relay.index
  116. replica_parallel_type = LOGICAL_CLOCK
  117. replica_parallel_workers = 16
  118. binlog_gtid_simple_recovery = 1
  119. replica_preserve_commit_order = 1
  120. binlog_rows_query_log_events = 1
  121. replica_transaction_retries = 10
  122.  
  123.  
  124. # group replication settings
  125. transaction_write_set_extraction = XXHASH64
  126. binlog_checksum = NONE
  127. loose_group_replication_group_name = "957e8af0-bc63-11ea-bb19-005056a52572"
  128. loose_group_replication_start_on_boot = off
  129. loose_group_replication_local_address = "192.168.27.56:24901"
  130. loose_group_replication_group_seeds = "192.168.27.56:24901,192.168.27.57:24901,192.168.27.58:24901"
  131. loose_group_replication_bootstrap_group = off
  132. loose_group_replication_ip_whitelist = "192.168.27.56/24"
  133. loose_group_replication_member_expel_timeout=30
  134. report_host = 192.168.27.56
  135. report_port = 33066
  136. #read_only=1
  137. #super_read_only=1
  138. binlog_transaction_dependency_tracking = WRITESET

Node 2 不同的部分参数

  1. server_id = 2757
  2. report_host = 192.168.27.57
  3. loose_group_replication_ip_whitelist = "192.168.27.57/24"

Node 3 不同的部分参数

  1. server_id = 2758
  2. report_host = 192.168.27.58
  3. loose_group_replication_ip_whitelist = "192.168.27.58/24"

mysql 用户分别初始化三台主机操作

mysqld  --defaults-file=/data/mysqldb/conf/mysql.conf --initialize --user=mysql --basedir=/app/mysql8.0.34  --datadir=/data/mysqldb/data
等待两分钟，查看每台主机初始化日志是否出现错误。
cat /data/mysqldb/log/mysqld.log
5、启动及停止命令
分别启动三个节点 mysql server
  1. mysqld_safe --defaults-file=/data/mysqldb/conf/mysql.conf --user=mysql &
  2.  
  3.  
  4. -----如下关闭命令，本次不需要执行
  5. mysqladmin -uroot -proot123  -S /data/mysqldb/socket/mysql.sock shutdown
6、修改 root@localhost 用户密码

root 密码可以根据需求自己修改,生产环境需使用 8 位以上的强密码。
mysql 用户查看日志中的密码分别登录进去修改 root@localhost 用户密码。

  1. --默认密码在错误日志里
  2. more /data/mysqldb/log/mysqld.log|grep password  
  3.  
  4.  
  5. mysql -uroot -p  -P 33066 -S /data/mysqldb/socket/mysql.sock
  6.  
  7.  
  8. alter user root@'localhost' identified by 'root@123';
  9.  
  10.  
  11. reset master;

三、配置 MGR 组复制

1、所有节点安装组复制插件

SQL 命令

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
或者写入到配置文件 plugin-load = group_replication.so

查看所有插件，注意主复制插件是否为 active。

show plugins;
2、在 Node1 节点上创建复制用户和配置恢复通道
  1. SET SQL_LOG_BIN=0;
  2. CREATE USER rep@'%' IDENTIFIED BY 'Rep1Yh6t';
  3. GRANT REPLICATION SLAVE ON *.* TO rep@'%';
  4. SET SQL_LOG_BIN=1;
  5.  
  6.  
  7. CHANGE MASTER TO MASTER_USER='rep', MASTER_PASSWORD='Rep1Yh6t' FOR CHANNEL 'group_replication_recovery';

使用 SET SQL_LOG_BIN=0; 来保证创建用户的操作不记录到 binlog 中，这么做可以避免其他服务加入时报事务冲突错误。

3、在 Node1 节点启动 MGR(只需在第一个节点执行即可)
  1. SET GLOBAL group_replication_bootstrap_group=ON;
  2.  
  3.  
  4. START GROUP_REPLICATION;
  5.  
  6.  
  7. SET GLOBAL group_replication_bootstrap_group=OFF;

group_replication_bootstrap_group 参数设置为 ON，是为了表示以后加入集群的服务器都以这台服务器为基准。以后加入的就不需要再设置此参数。

  1. --查询当前组成员及状态
  2. mysql> select * from performance_schema.replication_group_members;
  3. +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
  4. | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
  5. +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
  6. | group_replication_applier | b9f52d68-52e2-11ee-953c-fa163e0e1ce8 |192.168.27.56 |       33066 | ONLINE       | PRIMARY     | 8.0.34         | XCom                       |
  7. +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
  8. 1 rows in set (0.00 sec)
4、其他节点加入组复制（其他节点执行）
  1. Node2 和 Node3 执行
  2.  
  3.  
  4. 先执行 reset master
  5.  
  6.  
  7. SET SQL_LOG_BIN=0;
  8. CREATE USER rep@'%' IDENTIFIED BY 'Rep1Yh6t';
  9. GRANT REPLICATION SLAVE ON *.* TO rep@'%';
  10. SET SQL_LOG_BIN=1;
  11.  
  12.  
  13. CHANGE MASTER TO MASTER_USER='rep', MASTER_PASSWORD='Rep1Yh6t' FOR CHANNEL 'group_replication_recovery';
  14.  
  15.  
  16. START GROUP_REPLICATION;
5、启动完成之后在看 Group 信息
  1. SELECT * FROM performance_schema.replication_group_members;
  2.  
  3.  
  4. mysql> SELECT * FROM performance_schema.replication_group_members;
  5.  
  6.  
  7. mysql> select * from performance_schema.replication_group_member_stats\G;
  8.  
  9.  
  10. mysql> select * from performance_schema.replication_group_members;
  11. +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
  12. | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
  13. +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
  14. | group_replication_applier | b9f52d68-52e2-11ee-953c-fa163e0e1ce8 |192.168.27.56 |       33066 | ONLINE       | PRIMARY     | 8.0.34         | XCom                       |
  15. | group_replication_applier | eaae5cec-52e2-11ee-a03c-fa163e0e1c84 |192.168.27.57 |       33066 | ONLINE       | SECONDARY   | 8.0.34         | XCom                       |
  16. | group_replication_applier | eea965a9-52e2-11ee-8027-fa163e0e1c05 |192.168.27.58 |       33066 | ONLINE       | SECONDARY   | 8.0.34         | XCom                       |
  17. +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
  18. 3 rows in set (0.00 sec)
6、连接 Node1 主库创建 root@’%’ 用户

节点1

  1. mysql -uroot -p  -P 33066 -S /data/mysqldb/socket/mysql.sock
  2.  
  3.  
  4. create user root@'%' identified by 'root@123';
  5. grant all  privileges on *.* to root@'%'  with grant option;
  6. flush privileges;

===================================
部署多实例需要添加如下两个参数(否则端口冲突)

  1. mysqlx_port
  2. mysqlx_socket

节点加入集群报错时，节点需要设置如下参数(使用默认密码插件时需要执行)

set global group_replication_recovery_get_public_key=on;

===========================

四、安装 MySQL Shell

初始化实例及创建纳管库，库名 mysql_innodb_cluster_metadata，只需要在一台中间件服务器安装即可，这里选择 Node3。

1、root 用户安装 MySQL Shell
  1. # rpm -ivh mysql-shell-8.0.34-1.el7.x86_64.rpm
  2.  
  3.  
  4. --这里下载的是 rpm 包，如果你下载的是二进制版本，可直接解压配置环境变量即可。
  5. # tar -zxvf mysql-shell-8.0.34-linux-glibc2.12-x86-64bit.tar.gz
  6. # ln -s mysql-shell-8.0.34-linux-glibc2.12-x86-64bit mysql-shell
  7. # export PATH=$PATH:/app/mysql-shell/bin
2、连接数据库，添加纳管

在第一次连接，创建纳管时，需要从 primary 连接进去。否则会破坏 read_only（root 用户会破坏 super_read_only)。

mysqlsh root@192.168.27.56:33066 --py

添加纳管

dba.create_cluster(‘mysqlrouter’)
  1. MySQL  192.168.27.56:33066 ssl  Py > dba.create_cluster('mysqlrouter')
  2. A new InnoDB Cluster will be created on instance '192.168.27.56:33066'.
  3.  
  4.  
  5. You are connected to an instance that belongs to an unmanaged replication group.
  6. Do you want to setup an InnoDB Cluster based on this replication group? [Y/n]: Y
  7. Creating InnoDB Cluster 'mysqlrouter' on '192.168.27.56:33066'...
  8.  
  9.  
  10. Adding Seed Instance...
  11. Adding Instance '192.168.27.56:33066'...
  12. Adding Instance '192.168.27.57:33066'...
  13. Adding Instance '192.168.27.58:33066'...
  14. Resetting distributed recovery credentials across the cluster...
  15. Cluster successfully created based on existing replication group.
  16.  
  17.  
  18. <Cluster:mysqlrouter>
3、查看集群状态
  1. c=dba.get_cluster()
  2. c.status()
  1. MySQL  192.168.27.56:33066 ssl  Py > c.status()
  2. {
  3.     "clusterName": "mysqlrouter",
  4.     "defaultReplicaSet": {
  5.         "name": "default",
  6.         "primary": "192.168.27.56:33066",
  7.         "ssl": "DISABLED",
  8.         "status": "OK",
  9.         "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
  10.         "topology": {
  11.             "192.168.27.56:33066": {
  12.                 "address": "192.168.27.56:33066",
  13.                 "memberRole": "PRIMARY",
  14.                 "mode": "R/W",
  15.                 "readReplicas": {},
  16.                 "replicationLag": "applier_queue_applied",
  17.                 "role": "HA",
  18.                 "status": "ONLINE",
  19.                 "version": "8.0.34"
  20.             },
  21.             "192.168.27.57:33066": {
  22.                 "address": "192.168.27.57:33066",
  23.                 "memberRole": "SECONDARY",
  24.                 "mode": "R/O",
  25.                 "readReplicas": {},
  26.                 "replicationLag": "applier_queue_applied",
  27.                 "role": "HA",
  28.                 "status": "ONLINE",
  29.                 "version": "8.0.34"
  30.             },
  31.             "192.168.27.58:33066": {
  32.                 "address": "192.168.27.58:33066",
  33.                 "memberRole": "SECONDARY",
  34.                 "mode": "R/O",
  35.                 "readReplicas": {},
  36.                 "replicationLag": "applier_queue_applied",
  37.                 "role": "HA",
  38.                 "status": "ONLINE",
  39.                 "version": "8.0.34"
  40.             }
  41.         },
  42.         "topologyMode": "Single-Primary"
  43.     },
  44.     "groupInformationSourceMember": "192.168.27.56:33066"
  45. }
  46.  MySQL  192.168.27.56:33066 ssl  Py >
4、MySQL Shell 的其他方法
  1. dba.help()
  2. \help dba
  3.  
  4.  
  5. 查看方法具体使用：
  6. dba.help('createCluster');
  7.  
  8.  
  9. MySQL Shell 工具集
  10. \help util
5、删除纳管

本次不需要执行，只要删除元数据数据库即可，从 primary 连接。

dba.drop_metadata_schema()
6、查看纳管数据库

mysql_innodb_cluster_metadata

  1. mysql> show databases;
  2. +-------------------------------+
  3. | Database                      |
  4. +-------------------------------+
  5. | information_schema            |
  6. | mysql                         |
  7. | mysql_innodb_cluster_metadata |
  8. | performance_schema            |
  9. | sys                           |
  10. +-------------------------------+
  11. 5 rows in set (0.00 sec)
  12.  
  13.  
  14. mysql> use mysql_innodb_cluster_metadata;
  15. Reading table information for completion of table and column names
  16. You can turn off this feature to get a quicker startup with -A
  17.  
  18.  
  19. Database changed
  20. mysql> show tables;
  21. +-----------------------------------------+
  22. | Tables_in_mysql_innodb_cluster_metadata |
  23. +-----------------------------------------+
  24. | async_cluster_members                   |
  25. | async_cluster_views                     |
  26. | clusters                                |
  27. | clusterset_members                      |
  28. | clusterset_views                        |
  29. | clustersets                             |
  30. | instances                               |
  31. | router_rest_accounts                    |
  32. | routers                                 |
  33. | schema_version                          |
  34. | v2_ar_clusters                          |
  35. | v2_ar_members                           |
  36. | v2_clusters                             |
  37. | v2_cs_clustersets                       |
  38. | v2_cs_members                           |
  39. | v2_cs_router_options                    |
  40. | v2_gr_clusters                          |
  41. | v2_instances                            |
  42. | v2_router_rest_accounts                 |
  43. | v2_routers                              |
  44. | v2_this_instance                        |
  45. +-----------------------------------------+
  46. 21 rows in set (0.00 sec)

五、MySQL Router 安装配置

1、单独三节点安装 MySQL Router

在主节点服务器配置 mysql 用户执行

  1. $ tar -xvf mysql-router-8.0.34-linux-glibc2.12-x86_64.tar.xz
  2. $ mv mysql-router-8.0.34-linux-glibc2.12-x86_64 mysqlrouter
  3. $ mkdir -p /data/mysqlrouter/mysqlrouter6446
  4.  
  5.  
  6. /app/mysqlrouter/bin/mysqlrouter
2、初始化 MySQL Router
  1. /app/mysqlrouter/bin/mysqlrouter  --bootstrap root@192.168.27.56:33066 --directory /data/mysqlrouter/mysqlrouter6446  --conf-base-port 6446  --conf-use-sockets  --force
  2.  
  3.  
  4. (注意：--conf-base-port 6446 这个参数是 mysqlrouter 参数  默认是 6446)
  1. [mysql@jiekexu-my-56 data]$ /app/mysqlrouter/bin/mysqlrouter  --bootstrap root@192.168.27.56:33066 --directory /data/mysqlrouter/mysqlrouter6446  --conf-base-port 6446  --conf-use-sockets  --force
  2. Please enter MySQL password for root:
  3. # Bootstrapping MySQL Router instance at '/data/mysqlrouter/mysqlrouter6446'...
  4.  
  5.  
  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 /data/mysqlrouter/mysqlrouter6446/mysqlrouter.conf
  11.  
  12.  
  13. # MySQL Router configured for the InnoDB Cluster 'mysqlrouter'
  14.  
  15.  
  16. After this MySQL Router has been started with the generated configuration
  17.  
  18.  
  19.     $ /app/mysqlrouter/bin/mysqlrouter -c /data/mysqlrouter/mysqlrouter6446/mysqlrouter.conf
  20.  
  21.  
  22. InnoDB Cluster 'mysqlrouter' can be reached by connecting to:
  23.  
  24.  
  25. ## MySQL Classic protocol
  26.  
  27.  
  28. - Read/Write Connections: jiekexu-my-56:6446, /data/mysqlrouter/mysqlrouter6446/mysql.sock
  29. - Read/Only Connections:  jiekexu-my-56:6447, /data/mysqlrouter/mysqlrouter6446/mysqlro.sock
  30.  
  31.  
  32. ## MySQL X protocol
  33.  
  34.  
  35. - Read/Write Connections: jiekexu-my-56:6448, /data/mysqlrouter/mysqlrouter6446/mysqlx.sock
  36. - Read/Only Connections:  jiekexu-my-56:6449, /data/mysqlrouter/mysqlrouter6446/mysqlxro.sock
  37.  
  38.  
  39. ==========================================================
  40. --./mysqlrouter_passwd set /app/percona/mysqlrouter3306/mysqlrouter.pwd rdsroot 输入 rest 认证密码(curl 命令查询 MGR 集群状态  非必须)

3、启动 MySQL Router

  1. [mysql@jiekexu-my-57 mysqlrouter6446]$ cd /data/mysqlrouter/mysqlrouter6446
  2. [mysql@jiekexu-my-57 mysqlrouter6446]$ ./start.sh
  3. [mysql@jiekexu-my-57 mysqlrouter6446]$ PID 26919 written to '/data/mysqlrouter/mysqlrouter6446/mysqlrouter.pid'
  4. stopping to log to the console. Continuing to log to filelog
  5.  
  6.  
  7. --除了上面脚本启动外，按提示中如下命令也可以启动：
  8. /app/mysqlrouter/bin/mysqlrouter -c /data/mysqlrouter/mysqlrouter6446/mysqlrouter.conf
  1. 查看日志
  2. cat mysqlrouter.log
  1. [mysql@jiekexu-my-56 log]$ cat mysqlrouter.log
  2. 2023-09-14 18:28:12 io INFO [7ff61678e780] starting 16 io-threads, using backend 'linux_epoll'
  3. 2023-09-14 18:28:12 http_server INFO [7ff61678e780] listening on 0.0.0.0:8443
  4. 2023-09-14 18:28:12 metadata_cache_plugin INFO [7ff5d8ff9700] Starting Metadata Cache
  5. 2023-09-14 18:28:12 metadata_cache INFO [7ff5d8ff9700] Connections using ssl_mode 'PREFERRED'
  6. 2023-09-14 18:28:12 metadata_cache INFO [7ff616702700] Starting metadata cache refresh thread
  7. 2023-09-14 18:28:12 routing INFO [7ff5937fe700] [routing:bootstrap_x_rw] started: routing strategy = first-available
  8. 2023-09-14 18:28:12 routing INFO [7ff5937fe700] [routing:bootstrap_x_rw] started: listening using /data/mysqlrouter/mysqlrouter6446/mysqlx.sock
  9. 2023-09-14 18:28:12 routing INFO [7ff5937fe700] Start accepting connections for routing routing:bootstrap_x_rw listening on 6448 and named socket /data/mysqlrouter/mysqlrouter6446/mysqlx.sock
  10. 2023-09-14 18:28:12 routing INFO [7ff598ff9700] [routing:bootstrap_rw] started: routing strategy = first-available
  11. 2023-09-14 18:28:12 routing INFO [7ff5997fa700] [routing:bootstrap_ro] started: routing strategy = round-robin-with-fallback
  12. 2023-09-14 18:28:12 routing INFO [7ff593fff700] [routing:bootstrap_x_ro] started: routing strategy = round-robin-with-fallback
  13. 2023-09-14 18:28:12 routing INFO [7ff598ff9700] [routing:bootstrap_rw] started: listening using /data/mysqlrouter/mysqlrouter6446/mysql.sock
  14. 2023-09-14 18:28:12 routing INFO [7ff5997fa700] [routing:bootstrap_ro] started: listening using /data/mysqlrouter/mysqlrouter6446/mysqlro.sock
  15. 2023-09-14 18:28:12 routing INFO [7ff598ff9700] Start accepting connections for routing routing:bootstrap_rw listening on 6446 and named socket /data/mysqlrouter/mysqlrouter6446/mysql.sock
  16. 2023-09-14 18:28:12 routing INFO [7ff5997fa700] Start accepting connections for routing routing:bootstrap_ro listening on 6447 and named socket /data/mysqlrouter/mysqlrouter6446/mysqlro.sock
  17. 2023-09-14 18:28:12 routing INFO [7ff593fff700] [routing:bootstrap_x_ro] started: listening using /data/mysqlrouter/mysqlrouter6446/mysqlxro.sock
  18. 2023-09-14 18:28:12 routing INFO [7ff593fff700] Start accepting connections for routing routing:bootstrap_x_ro listening on 6449 and named socket /data/mysqlrouter/mysqlrouter6446/mysqlxro.sock
  19. 2023-09-14 18:28:12 metadata_cache INFO [7ff616702700] Connected with metadata server running on 192.168.27.56:33066
  20. 2023-09-14 18:28:12 metadata_cache INFO [7ff616702700] Potential changes detected in cluster after metadata refresh (view_id=0)
  21. 2023-09-14 18:28:12 metadata_cache INFO [7ff616702700] Metadata for cluster 'mysqlrouter' has 3 member(s), single-primary:
  22. 2023-09-14 18:28:12 metadata_cache INFO [7ff616702700]     192.168.27.56:33066 / 33060 - mode=RW
  23. 2023-09-14 18:28:12 metadata_cache INFO [7ff616702700]     192.168.27.57:33066 / 33060 - mode=RO
  24. 2023-09-14 18:28:12 metadata_cache INFO [7ff616702700]     192.168.27.58:33066 / 33060 - mode=RO
4、使用 Router 登录 mysql 测试
  1. mysql -uroot -p -h192.168.27.56 -P 6446（6446是 mysqlrouer 读写端口）
  2. mysql -uroot -p -h192.168.27.56 -P 6447（6447是 mysqlrouer 只读端口）
  3.  
  4.  
  5. ======================================
5、MySQL Router systemctl 启动配置
  1. vim /etc/systemd/system/mysqlrouter@3306.service 
  2.  
  3.  
  4. [Unit]
  5. Description= Mysql router 
  6. Documentation=man:mysqlrouter(8)
  7. After=network.target
  8. After=syslog.target
  9.  
  10.  
  11. [Install]
  12. WantedBy=multi-user.target
  13.  
  14.  
  15. [Service]
  16. User=root
  17. Group=root
  18. Type=forking
  19. CPUQuota=100%
  20.  
  21.  
  22. TimeoutSec=30
  23.  
  24.  
  25. PermissionsStartOnly=true
  26. PIDFile=/data/mysqlrouter/mysqlrouter6446/mysqlrouter.pid
  27. ExecStart=/usr/bin/bash -c "/data/mysqlrouter/mysqlrouter6446/start.sh &"
  28. ExecStop=/usr/bin/bash -c "/data/mysqlrouter/mysqlrouter6446/stop.sh &"
  29.  
  30.  
  31. LimitNOFILE = 102400
  32.  
  33.  
  34. Restart=no
6、只读参数设置

修改参数文件，将三个节点中的参数文件中关于只读的最后两行参数注释去掉。
注意：在《MySQL 实战》一书中说明单主模式下，组复制会自动将 Secondary 节点的 read_only 和 super_read_only 设置为 ON，感兴趣的小伙伴可以测试一下，我这里已经搭建完成了才看到没法测试了。

  1. [mysql@jiekexu-my-56 log]$ ps -ef | grep mysql
  2. root     11252 10549  0 17:37 pts/0    00:00:00 su - mysql
  3. mysql    11253 11252  0 17:37 pts/0    00:00:00 -bash
  4. mysql    11540 11253  0 17:43 pts/0    00:00:00 /bin/sh /app/mysql8.0.34/bin/mysqld_safe --defaults-file=/data/mysqldb/conf/mysql.conf --user=mysql
  5. mysql    12786 11540  0 17:43 pts/0    00:00:26 /app/mysql8.0.34/bin/mysqld --defaults-file=/data/mysqldb/conf/mysql.conf --basedir=/app/mysql8.0.34 --datadir=/data/mysqldb/data --plugin-dir=/app/mysql8.0.34/lib/plugin --log-error=/data/mysqldb/log/mysqld.log --pid-file=/data/mysqldb/pid/mysqld.pid --socket=/data/mysqldb/socket/mysql.sock --port=33066
  6. mysql    14279     1  0 18:28 pts/0    00:00:03 /app/mysqlrouter/bin/mysqlrouter -c /data/mysqlrouter/mysqlrouter6446/mysqlrouter.conf
  7. mysql    14532 11253  0 18:35 pts/0    00:00:00 ps -ef
  8. mysql    14533 11253  0 18:35 pts/0    00:00:00 grep --color=auto mysql
  9. [mysql@jiekexu-my-56 log]$ vim /data/mysqldb/conf/mysql.conf
  10. read_only=1
  11. super_read_only=1

然后登录两个从库，执行从库只读设置（注意只在从库执行，主库不用执行）。

  1. mysql> set global read_only=on;
  2. Query OK, 0 rows affected (0.00 sec)
  3. mysql> set global super_read_only=on;
  4. Query OK, 0 rows affected (0.00 sec)
  5. mysql> show master status;
  6. +------------------+----------+--------------+------------------+--------------------------------------------+
  7. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                          |
  8. +------------------+----------+--------------+------------------+--------------------------------------------+
  9. | mysql_bin.000001 |   397716 |              |                  | 957e8af0-bc63-11ea-bb19-005056a52572:1-438 |
  10. +------------------+----------+--------------+------------------+--------------------------------------------+
  11. 1 row in set (0.00 sec)
7、可能遇到的问题

mysql 高版本可能存在 note 提示
可以看到，集群的状态都有一个问题：

  1. "instanceErrors": [
  2.  
  3.  
  4.                     "NOTE: The required parallel-appliers settings are not enabled on the instance. Use dba.configureInstance() to fix it."

这个错误也提示了修复的方式，只要通过 mysqlshell 单独连上 MGR 中的每个实例，运行下即可修复：dba.configureInstance()

再根据提示，将 mysql 的参数修正下，或者将这个参数加到参数文件中，本文则是直接加到参数文件中了。

set persist binlog_transaction_dependency_tracking='WRITESET';

下面是以前搭建的 MySQL 8.0.30 也是第一次遇到这个问题，特此记录一下。

  1. [mysql@ts-mysql-101 ~]$ mysqlsh
  2. MySQL Shell 8.0.30
  3.  
  4.  
  5. Copyright (c) 2016, 2022, Oracle and/or its affiliates.
  6. Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
  7. Other names may be trademarks of their respective owners.
  8.  
  9.  
  10. Type '\help' or '\?' for help; '\quit' to exit.
  11.  MySQL  JS > shell.connect('root@192.168.221.101:33066')
  12. Creating a session to 'root@192.168.221.101:33066'
  13. Please provide the password for 'root@192.168.221.101:33066': *******
  14. Save password for 'root@192.168.221.101:33066'? [Y]es/[N]o/Ne[v]er (default No): m^C
  15. Shell.connect: Cancelled (RuntimeError)
  16.  MySQL  JS > shell.connect('root@192.168.221.101:33066')
  17. Creating a session to 'root@192.168.221.101:33066'
  18. Please provide the password for 'root@192.168.221.101:33066': *******
  19. Save password for 'root@192.168.221.101:33066'? [Y]es/[N]o/Ne[v]er (default No): n
  20. Fetching schema names for autocompletion... Press ^C to stop.
  21. Your MySQL connection id is 74
  22. Server version: 8.0.30 MySQL Community Server - GPL
  23. No default schema selected; type \use <schema> to set one.
  24. <ClassicSession:root@192.168.221.101:33066>
  25.  MySQL  192.168.221.101:33066 ssl  JS > dba.getCluster()
  26. <Cluster:mysqlrouter>
  27.  MySQL  192.168.221.101:33066 ssl  JS > var cluster = dba.getCluster()
  28.  MySQL  192.168.221.101:33066 ssl  JS > cluster.status()
  29. {
  30.     "clusterName": "mysqlrouter", 
  31.     "defaultReplicaSet": {
  32.         "name": "default", 
  33.         "primary": "192.168.221.101:33066", 
  34.         "ssl": "DISABLED", 
  35.         "status": "OK", 
  36.         "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
  37.         "topology": {
  38.             "192.168.221.101:33066": {
  39.                 "address": "192.168.221.101:33066", 
  40.                 "instanceErrors": [
  41.                     "NOTE: The required parallel-appliers settings are not enabled on the instance. Use dba.configureInstance() to fix it."
  42.                 ], 
  43.                 "memberRole": "PRIMARY", 
  44.                 "mode": "R/W", 
  45.                 "readReplicas": {}, 
  46.                 "replicationLag": "applier_queue_applied", 
  47.                 "role": "HA", 
  48.                 "status": "ONLINE", 
  49.                 "version": "8.0.30"
  50.             }, 
  51.             "192.168.221.102:33066": {
  52.                 "address": "192.168.221.102:33066", 
  53.                 "instanceErrors": [
  54.                     "NOTE: The required parallel-appliers settings are not enabled on the instance. Use dba.configureInstance() to fix it."
  55.                 ], 
  56.                 "memberRole": "SECONDARY", 
  57.                 "mode": "R/O", 
  58.                 "readReplicas": {}, 
  59.                 "replicationLag": "applier_queue_applied", 
  60.                 "role": "HA", 
  61.                 "status": "ONLINE", 
  62.                 "version": "8.0.30"
  63.             }, 
  64.             "192.168.221.103:33066": {
  65.                 "address": "192.168.221.103:33066", 
  66.                 "instanceErrors": [
  67.                     "NOTE: The required parallel-appliers settings are not enabled on the instance. Use dba.configureInstance() to fix it."
  68.                 ], 
  69.                 "memberRole": "SECONDARY", 
  70.                 "mode": "R/O", 
  71.                 "readReplicas": {}, 
  72.                 "replicationLag": "applier_queue_applied", 
  73.                 "role": "HA", 
  74.                 "status": "ONLINE", 
  75.                 "version": "8.0.30"
  76.             }
  77.         }, 
  78.         "topologyMode": "Single-Primary"
  79.     }, 
  80.     "groupInformationSourceMember": "192.168.221.101:33066"
  81. }
  82.  MySQL  192.168.221.101:33066 ssl  JS > dba.configureInstance()
  83. The instance '192.168.221.101:33066' belongs to an InnoDB Cluster.
  84. Configuring local MySQL instance listening at port 33066 for use in an InnoDB cluster...
  85.  
  86.  
  87. This instance reports its own address as 192.168.221.101:33066
  88.  
  89.  
  90. applierWorkerThreads will be set to the default value of 4.
  91.  
  92.  
  93. NOTE: Some configuration options need to be fixed:
  94. +----------------------------------------+---------------+----------------+----------------------------+
  95. | Variable                               | Current Value | Required Value | Note                       |
  96. +----------------------------------------+---------------+----------------+----------------------------+
  97. | binlog_transaction_dependency_tracking | COMMIT_ORDER  | WRITESET       | Update the server variable |
  98. +----------------------------------------+---------------+----------------+----------------------------+
  99.  
  100.  
  101. Do you want to perform the required configuration changes? [y/n]: y
  102. Configuring instance...
  103. The instance '192.168.221.101:33066' was configured to be used in an InnoDB cluster.
  104.  MySQL  192.168.221.101:33066 ssl  JS > cluster.status()
  105. {
  106.     "clusterName": "mysqlrouter", 
  107.     "defaultReplicaSet": {
  108.         "name": "default", 
  109.         "primary": "192.168.221.101:33066", 
  110.         "ssl": "DISABLED", 
  111.         "status": "OK", 
  112.         "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
  113.         "topology": {
  114.             "192.168.221.101:33066": {
  115.                 "address": "192.168.221.101:33066", 
  116.                 "memberRole": "PRIMARY", 
  117.                 "mode": "R/W", 
  118.                 "readReplicas": {}, 
  119.                 "replicationLag": "applier_queue_applied", 
  120.                 "role": "HA", 
  121.                 "status": "ONLINE", 
  122.                 "version": "8.0.30"
  123.             }, 
  124.             "192.168.221.102:33066": {
  125.                 "address": "192.168.221.102:33066", 
  126.                 "instanceErrors": [
  127.                     "NOTE: The required parallel-appliers settings are not enabled on the instance. Use dba.configureInstance() to fix it."
  128.                 ], 
  129.                 "memberRole": "SECONDARY", 
  130.                 "mode": "R/O", 
  131.                 "readReplicas": {}, 
  132.                 "replicationLag": "applier_queue_applied", 
  133.                 "role": "HA", 
  134.                 "status": "ONLINE", 
  135.                 "version": "8.0.30"
  136.             }, 
  137.             "192.168.221.103:33066": {
  138.                 "address": "192.168.221.103:33066", 
  139.                 "instanceErrors": [
  140.                     "NOTE: The required parallel-appliers settings are not enabled on the instance. Use dba.configureInstance() to fix it."
  141.                 ], 
  142.                 "memberRole": "SECONDARY", 
  143.                 "mode": "R/O", 
  144.                 "readReplicas": {}, 
  145.                 "replicationLag": "applier_queue_applied", 
  146.                 "role": "HA", 
  147.                 "status": "ONLINE", 
  148.                 "version": "8.0.30"
  149.             }
  150.         }, 
  151.         "topologyMode": "Single-Primary"
  152.     }, 
  153.     "groupInformationSourceMember": "192.168.221.101:33066"
  154. }

参考链接

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-innodb-cluster.html

全文完，希望可以帮到正在阅读的你，如果觉得此文对你有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~~

欢迎关注我公众号【JiekeXu DBA之路】，第一时间一起学习新知识！以下三个地址可以找到我，其他地址均属于盗版侵权爬取我的文章，而且代码格式、图片等均有错乱，不方便阅读，欢迎来我公众号或者墨天轮地址关注我，第一时间收获最新消息。

————————————————————————————
公众号：JiekeXu DBA之路
CSDN ：https://blog.csdn.net/JiekeXu
墨天轮：https://www.modb.pro/u/4347
————————————————————————————

051caf144a90d3a427184726da7b174f.gif

分享几个数据库备份脚本

Oracle 表碎片检查及整理方案

OGG|Oracle GoldenGate 基础2022 年公众号历史文章合集整理
 
 
Oracle 19c RAC 遇到的几个问题
 
 
OGG|Oracle 数据迁移后比对一致性
 
 
OGG|Oracle GoldenGate 微服务架构
 
 
Oracle 查询表空间使用率超慢问题一则
 
  
 
国产数据库|TiDB 5.4 单机快速安装初体验
 
 
Oracle ADG 备库停启维护流程及增量恢复
 
 
Linux 环境搭建 MySQL8.0.28 主从同步环境
 
 
从国产数据库调研报告中你都能了解哪些信息及我的总结建议
 
 
223020f9779046d411b21a75a2e982ab.png
 


                

        
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/黑客灵魂/article/detail/1006232
推荐阅读
相关标签
Copyright ©  2003-2013 www.wpsshop.cn  版权所有，并保留所有权利。

  
闽ICP备14008679号