企业官网建设流程全解析

-- 参考文档：
https://docs.oracle.com/cd/E17952_01/mysql-shell-8.4-en/admin-api-deploy-router.html

https://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 mgr01
192.168.2.12 mgr02
192.168.2.13 mgr03

192.168.2.100 19ctest

-- install，这里安装了mysql router是8.居然用这种配置模式，可以管理mysql8.4 。（按照官网的意思，mysql版本不能高于mysql router的版本）
[root@19ctest 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 &

--查看端口

[root@19ctest 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 [root@19ctest mysqlrouter]#

--创建一个用户，用来测试连接mysql router

create user route_user@'%' identified by 'mysql';

--登录到8002端口， mysql route配置中，8002是primary，primary应对的hostname是mgr02 、mgr03

./mysql -uroute_user -h192.168.2.100 -pmysql -P8002 -e "select @@hostname" [root@mgr01 bin]# [root@mgr01 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 | +------------+ [root@mgr01 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 | +------------+ [root@mgr01 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 | +------------+ [root@mgr01 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 | +------------+ [root@mgr01 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 | +------------+ [root@mgr01 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 | +------------+ [root@mgr01 bin]#

--登录到8001端口， mysql route配置中，8001是primary，primary应对的hostname是mgr01

./mysql -uroute_user -h192.168.2.100 -pmysql -P8001 -e "select @@hostname" [root@mgr01 bin]# [root@mgr01 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 | +------------+ [root@mgr01 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 | +------------+ [root@mgr01 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 | +------------+ [root@mgr01 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 | +------------+ [root@mgr01 bin]#

-- 关闭掉节点1，进行测试，这个时候，节点2提升为primary

mysql> 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

[root@mgr01 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 | +------------+ [root@mgr01 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 | +------------+ [root@mgr01 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 | +------------+ [root@mgr01 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 | +------------+ [root@mgr01 bin]#

-- 连接到8002，发现连接到的是节点2和节点3，mgr02，mgr03 。即在节点2和节点3之间轮询

[root@mgr01 bin]# [root@mgr01 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 | +------------+ [root@mgr01 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 | +------------+ [root@mgr01 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 | +------------+ [root@mgr01 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 | +------------+ [root@mgr01 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节点

[root@mgr01 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 | +------------+ [root@mgr01 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 | +------------+ [root@mgr01 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 | +------------+ [root@mgr01 bin]#

--连接到8002， 是节点2和节点3 ，即mgr02，mgr03. 看起来是根据配置文件中的配置进行轮询的

[root@mgr01 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 | +------------+ [root@mgr01 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 | +------------+ [root@mgr01 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 | +------------+ [root@mgr01 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 | +------------+ [root@mgr01 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 | +------------+ [root@mgr01 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 | +------------+ [root@mgr01 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 | +------------+ [root@mgr01 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 | +------------+ [root@mgr01 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 | +------------+ [root@mgr01 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 | +------------+ [root@mgr01 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 | +------------+ [root@mgr01 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 | +------------+ [root@mgr01 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