mysql router学习-1 （简单的配置方式）

-- 参考文档https://docs.oracle.com/cd/E17952_01/mysql-shell-8.4-en/admin-api-deploy-router.htmlhttps://dev.mysql.com/doc/mysql-router/8.0/en/mysql-router-deploying-basic-routing.html-- info mysql版本8.4 三个节点的mgr 单主模式。mysql router在192.168.2.100上192.168.2.11 mgr01192.168.2.12 mgr02192.168.2.13 mgr03192.168.2.100 19ctest-- install这里安装了mysql router是8.居然用这种配置模式可以管理mysql8.4 。按照官网的意思mysql版本不能高于mysql router的版本[root19ctest mysql]# yum install mysql-router-community-8.0.46-1.el8.x86_64.rpm-- 把mgr启动起来mysql SELECT * FROM performance_schema.replication_group_members; ------------------------------------------------------------------------------------------------------------------------------------------------------------------ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ | group_replication_applier | 47b589a3-b3c3-11ef-ba63-000c297e6af7 | mgr02 | 3306 | ONLINE | SECONDARY | 8.4.3 | XCom | | group_replication_applier | 7bf19e8c-b3d1-11ef-b688-000c29e5c624 | mgr03 | 3306 | ONLINE | SECONDARY | 8.4.3 | XCom | | group_replication_applier | e558e52f-b327-11ef-8266-000c291c3ef6 | mgr01 | 3306 | ONLINE | PRIMARY | 8.4.3 | XCom | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ 3 rows in set (0.00 sec) mysql-- mysqlrouter.conf配置文件这里采用比较简单的配置直接写死了[routing:primary] bind_address 0.0.0.0 bind_port 8001 destinations 192.168.2.11:3306,192.168.2.12:3306,192.168.2.13:3306 routing_strategy first-available [routing:secondary] bind_address 0.0.0.0 bind_port 8002 destinations 192.168.2.12:3306 ,192.168.2.13:3306 routing_strategy round-robin--启动mysqlrouter --config/etc/mysqlrouter/mysqlrouter.conf --查看端口[root19ctest mysqlrouter]# netstat -ntlp |grep mysqlrouter tcp 0 0 0.0.0.0:8001 0.0.0.0:* LISTEN 7118/mysqlrouter tcp 0 0 0.0.0.0:8002 0.0.0.0:* LISTEN 7118/mysqlrouter [root19ctest mysqlrouter]#--创建一个用户用来测试连接mysql routercreate user route_user% identified by mysql;--登录到8002端口 mysql route配置中8002是primaryprimary应对的hostname是mgr02 、mgr03./mysql -uroute_user -h192.168.2.100 -pmysql -P8002 -e select hostname [rootmgr01 bin]# [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8002 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr03 | ------------ [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8002 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr02 | ------------ [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8002 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr03 | ------------ [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8002 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr02 | ------------ [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8002 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr03 | ------------ [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8002 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr02 | ------------ [rootmgr01 bin]#--登录到8001端口 mysql route配置中8001是primaryprimary应对的hostname是mgr01./mysql -uroute_user -h192.168.2.100 -pmysql -P8001 -e select hostname [rootmgr01 bin]# [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8001 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr01 | ------------ [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8001 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr01 | ------------ [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8001 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr01 | ------------ [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8001 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr01 | ------------ [rootmgr01 bin]#-- 关闭掉节点1进行测试这个时候节点2提升为primarymysql select hostname; ------------ | hostname | ------------ | mgr01 | ------------ 1 row in set (0.00 sec) mysql shutdown; Query OK, 0 rows affected (0.00 sec) mysql mysql SELECT * FROM performance_schema.replication_group_members; ------------------------------------------------------------------------------------------------------------------------------------------------------------------ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ | group_replication_applier | 47b589a3-b3c3-11ef-ba63-000c297e6af7 | mgr02 | 3306 | ONLINE | PRIMARY | 8.4.3 | XCom | | group_replication_applier | 7bf19e8c-b3d1-11ef-b688-000c29e5c624 | mgr03 | 3306 | ONLINE | SECONDARY | 8.4.3 | XCom | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ 2 rows in set (0.01 sec) mysql-- 连接到8001端口 发现连接到的是节点2 mgr02也就是新的primary[rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8001 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr02 | ------------ [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8001 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr02 | ------------ [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8001 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr02 | ------------ [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8001 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr02 | ------------ [rootmgr01 bin]#-- 连接到8002发现连接到的是节点2和节点3mgr02mgr03 。即在节点2和节点3之间轮询[rootmgr01 bin]# [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8002 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr03 | ------------ [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8002 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr02 | ------------ [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8002 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr03 | ------------ [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8002 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr02 | ------------ [rootmgr01 bin]#-- 把节点1启动加入到mgr中mysql SELECT * FROM performance_schema.replication_group_members; ------------------------------------------------------------------------------------------------------------------------------------------------------------------ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ | group_replication_applier | 47b589a3-b3c3-11ef-ba63-000c297e6af7 | mgr02 | 3306 | ONLINE | PRIMARY | 8.4.3 | XCom | | group_replication_applier | 7bf19e8c-b3d1-11ef-b688-000c29e5c624 | mgr03 | 3306 | ONLINE | SECONDARY | 8.4.3 | XCom | | group_replication_applier | e558e52f-b327-11ef-8266-000c291c3ef6 | mgr01 | 3306 | ONLINE | SECONDARY | 8.4.3 | XCom | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ 3 rows in set (0.00 sec) mysql-- 连接到8001 是节点2 也就是primary节点[rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8001 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr02 | ------------ [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8001 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr02 | ------------ [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8001 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr02 | ------------ [rootmgr01 bin]#--连接到8002 是节点2和节点3 即mgr02mgr03. 看起来是根据配置文件中的配置进行轮询的[rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8002 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr03 | ------------ [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8002 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr02 | ------------ [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8002 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr03 | ------------ [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8002 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr02 | ------------ [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8002 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr03 | ------------ [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8002 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr02 | ------------ [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8002 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr03 | ------------ [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8002 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr02 | ------------ [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8002 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr03 | ------------ [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8002 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr02 | ------------ [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8002 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr03 | ------------ [rootmgr01 bin]# ./mysql -uroute_user -h192.168.2.100 -pmysql -P8002 -e select hostname mysql: [Warning] Using a password on the command line interface can be insecure. ------------ | hostname | ------------ | mgr02 | ------------ [rootmgr01 bin]#-- 把节点1提升为primary 任意节点执行SELECT group_replication_set_as_primary(e558e52f-b327-11ef-8266-000c291c3ef6); mysql select hostname; ------------ | hostname | ------------ | mgr03 | ------------ 1 row in set (0.00 sec) mysql SELECT group_replication_set_as_primary(e558e52f-b327-11ef-8266-000c291c3ef6); -------------------------------------------------------------------------- | group_replication_set_as_primary(e558e52f-b327-11ef-8266-000c291c3ef6) | -------------------------------------------------------------------------- | Primary server switched to: e558e52f-b327-11ef-8266-000c291c3ef6 | -------------------------------------------------------------------------- 1 row in set (0.01 sec) mysql mysql SELECT * FROM performance_schema.replication_group_members; ------------------------------------------------------------------------------------------------------------------------------------------------------------------ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ | group_replication_applier | 47b589a3-b3c3-11ef-ba63-000c297e6af7 | mgr02 | 3306 | ONLINE | SECONDARY | 8.4.3 | XCom | | group_replication_applier | 7bf19e8c-b3d1-11ef-b688-000c29e5c624 | mgr03 | 3306 | ONLINE | SECONDARY | 8.4.3 | XCom | | group_replication_applier | e558e52f-b327-11ef-8266-000c291c3ef6 | mgr01 | 3306 | ONLINE | PRIMARY | 8.4.3 | XCom | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ 3 rows in set (0.00 sec) mysql结论这种配置方式在conf配置文件中写死了primary和secondary当实际的primary和secondary有变化的时候不够灵活可能和实际想象的效果不一样不建议生产用测试可以。END