MYSQL 5.7 搭建MGR 后使用MySQL SHELL接管的一些疑问
MySQL的版本是5.7.26MySQL SHELL的版本是MySQL Shell 8.0.37
在单机上做多个实例的方式做的测试
测试最终目的：希望从mysql 5.7.26 搭建mgr集群，然后滚动升级MGR到mysql 8的mgr集群
大致过程如下：
1、先用手工方式创建了3个节点MGR（3306,3307,3308），此时这里用的 同步用户是repl@'%' 密码是repl ，版本都是mysql 5.7.26
2、用MYSQL SHELL接管这3个节点，这时候发现接管MGR的时候，多了一个mysql_innodb_cluster_metadata数据库，接管集群的时候发现，他有提示说用test用户去接管。
当时的日志没留下来。
3、经过多次折腾加MGR，删MGR，会发现创建了'mysql_innodb_cluster_10330X的用户
MySQLlocalhost:3306JS > dba.createCluster('MGR');
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL Shell
A new InnoDB Cluster will be created on instance '192.168.101.77:3306'.
You are connected to an instance that belongs to an unmanaged replication group.
Do you want to setup an InnoDB Cluster based on this replication group? : yes
Creating InnoDB Cluster 'MGR' on '192.168.101.77:3306'...
Adding Seed Instance...
Adding Instance '192.168.101.77:3307'...
Adding Instance '192.168.101.77:3308'...
Adding Instance '192.168.101.77:3306'...
Resetting distributed recovery credentials across the cluster...
NOTE: User 'mysql_innodb_cluster_103307'@'%' already existed at instance '192.168.101.77:3306'. It will be deleted and created again with a new password.
NOTE: User 'mysql_innodb_cluster_103308'@'%' already existed at instance '192.168.101.77:3306'. It will be deleted and created again with a new password.
NOTE: User 'mysql_innodb_cluster_103306'@'%' already existed at instance '192.168.101.77:3306'. It will be deleted and created again with a new password.
WARNING: Instance '192.168.101.77:3307' cannot persist configuration since MySQL version 5.7.26 does not support the SET PERSIST command (MySQL version >= 8.0.11 required). Please use the dba.configureLocalInstance() command locally to persist the changes.
WARNING: Instance '192.168.101.77:3308' cannot persist configuration since MySQL version 5.7.26 does not support the SET PERSIST command (MySQL version >= 8.0.11 required). Please use the dba.configureLocalInstance() command locally to persist the changes.
WARNING: Instance '192.168.101.77:3306' cannot persist configuration since MySQL version 5.7.26 does not support the SET PERSIST command (MySQL version >= 8.0.11 required). Please use the dba.configureLocalInstance() command locally to persist the changes.
Cluster successfully created based on existing replication group.
<Cluster:MGR>
问题如下：
1、为什么我用的是repl的用户去同步mgr的3个节点，使用mysql shell接管的时候会创建mysql_innodb_cluster_10330X 用户呢？
2、查询replication_connection_configuration的时候为啥发现用户是test? 这个是不是bug?
mysql> select * from performance_schema.replication_connection_configuration\G;
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
HOST: <NULL>
PORT: 0
USER: test
NETWORK_INTERFACE:
AUTO_POSITION: 1
SSL_ALLOWED: NO
SSL_CA_FILE:
SSL_CA_PATH:
SSL_CERTIFICATE:
SSL_CIPHER:
SSL_KEY:
SSL_VERIFY_SERVER_CERTIFICATE: NO
SSL_CRL_FILE:
SSL_CRL_PATH:
CONNECTION_RETRY_INTERVAL: 60
CONNECTION_RETRY_COUNT: 86400
HEARTBEAT_INTERVAL: 30.000
TLS_VERSION:
1 row in set (0.00 sec)
ERROR:
No query specified
1、为什么我用的是repl的用户去同步mgr的3个节点，使用mysql shell接管的时候会创建mysql_innodb_cluster_10330X 用户呢？
------
这是MySQLShell 接管时候自动创建的
文档解释：
As part of using Group Replication, InnoDB Cluster creates internal recovery users which enable connections between the servers in the cluster. These users are internal to the cluster, and the user name of the generated users follows a naming scheme of mysql_innodb_cluster_server_id@%, where server_id is unique to the instance. In versions earlier than 8.0.17 the user name of the generated users followed a naming scheme of mysql_innodb_cluster_r.link:MySQL :: MySQL Shell 8.0 :: 7.3 User Accounts for InnoDB Cluster
2、查询replication_connection_configuration的时候为啥发现用户是test? 这个是不是bug?
------
看了你的大致过程，看到其中这样一句话“接管集群的时候发现，他有提示说用test用户去接管。当时的日志没留下来。”这就是为什么显示test用户的原因
3、附带一个叶老师写的文章
【新提醒】手动构建的GreatSQL MGR集群（含仲裁节点）如何用shell接管 - GreatSQL - GreatSQL社区 - Powered by Discuz!（万里开源技术社区）
KAiTO 发表于 2024-5-16 10:19
1、为什么我用的是repl的用户去同步mgr的3个节点，使用mysql shell接管的时候会创建mysql_innodb_cluster_1 ...
搭建好MGR后，使用MYSQL SHELL接管，用户是：mysql_innodb_cluster_10330X ，那为什么replication_connection_configuration 里面还是显示test呢？ 起飞小宇 发表于 2024-5-16 10:33
搭建好MGR后，使用MYSQL SHELL接管，用户是：mysql_innodb_cluster_10330X ，那为什么replication_connec ...
test是MySQL Shell连接使用的账户
mysql_innodb_cluster_10330X是MGR各节点间通信使用的账户 yejr 发表于 2024-5-16 12:37
test是MySQL Shell连接使用的账户
mysql_innodb_cluster_10330X是MGR各节点间通信使用的账户 ...
但是我并没有创建test账号
mysql> select user,host from mysql.user\G;
*************************** 1. row ***************************
user: mysql_innodb_cluster_103306
host: %
*************************** 2. row ***************************
user: mysql_innodb_cluster_103307
host: %
*************************** 3. row ***************************
user: mysql_innodb_cluster_103308
host: %
*************************** 4. row ***************************
user: mysql_innodb_cluster_103309
host: %
*************************** 5. row ***************************
user: repl
host: %
*************************** 6. row ***************************
user: mysql.session
host: localhost
*************************** 7. row ***************************
user: mysql.sys
host: localhost
*************************** 8. row ***************************
user: root
host: localhost
8 rows in set (0.00 sec)
ERROR:
No query specified
起飞小宇 发表于 2024-5-16 12:46
但是我并没有创建test账号
估计是你某个误操作导致的，正常 group_replication_applier 线程的 USER 应该是空的，例如
greatsql> select * from performance_schema.replication_connection_configuration\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
HOST: <NULL>
PORT: 0
USER:
NETWORK_INTERFACE:
AUTO_POSITION: 1
SSL_ALLOWED: NO
SSL_CA_FILE:
SSL_CA_PATH:
SSL_CERTIFICATE:
SSL_CIPHER:
SSL_KEY:
SSL_VERIFY_SERVER_CERTIFICATE: NO
SSL_CRL_FILE:
SSL_CRL_PATH:
CONNECTION_RETRY_INTERVAL: 60
CONNECTION_RETRY_COUNT: 86400
HEARTBEAT_INTERVAL: 30.000
TLS_VERSION:
PUBLIC_KEY_PATH:
GET_PUBLIC_KEY: NO
NETWORK_NAMESPACE:
COMPRESSION_ALGORITHM: uncompressed
ZSTD_COMPRESSION_LEVEL: 3
TLS_CIPHERSUITES: NULL
SOURCE_CONNECTION_AUTO_FAILOVER: 0
GTID_ONLY: 1
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_recovery
HOST: <NULL>
PORT: 0
USER: mysql_innodb_cluster_1233306
NETWORK_INTERFACE:
AUTO_POSITION: 1
SSL_ALLOWED: YES
SSL_CA_FILE:
SSL_CA_PATH:
SSL_CERTIFICATE:
SSL_CIPHER:
SSL_KEY:
SSL_VERIFY_SERVER_CERTIFICATE: NO
SSL_CRL_FILE:
SSL_CRL_PATH:
CONNECTION_RETRY_INTERVAL: 60
CONNECTION_RETRY_COUNT: 1
HEARTBEAT_INTERVAL: 30.000
TLS_VERSION: TLSv1.2,TLSv1.3
PUBLIC_KEY_PATH:
GET_PUBLIC_KEY: NO
NETWORK_NAMESPACE:
COMPRESSION_ALGORITHM: uncompressed
ZSTD_COMPRESSION_LEVEL: 3
TLS_CIPHERSUITES: NULL
SOURCE_CONNECTION_AUTO_FAILOVER: 0
GTID_ONLY: 1
2 rows in set (0.00 sec)
构建 MGR 的过程可参考 https://greatsql.cn/docs/8.0.32-25/4-install-guide/2-install-with-rpm.html#%E5%87%86%E5%A4%87%E6%9E%84%E5%BB%BAmgr%E9%9B%86%E7%BE%A4
页:
[1]