7.利用MySQL Router构建读写分离MGR集群 | 深入浅出MGR（文末有

#
#参数解释
# 参数 --bootstrap 表示开始初始化
# 参数 GreatSQL@172.16.16.10:3306 是MGR服务专用账号
# --user=mysqlrouter 是运行mysqlrouter进程的系统用户名
#
$ mysqlrouter --bootstrap GreatSQL@172.16.16.10:3306 --user=mysqlrouter
Please enter MySQL password for GreatSQL:   <-- 输入密码
# 然后mysqlrouter开始自动进行初始化
# 它会自动读取MGR的元数据信息，自动生成配置文件
Please enter MySQL password for GreatSQL:
# Bootstrapping system MySQL Router instance...

- Creating account(s) (only those that are needed, if any)
- Using existing certificates from the '/var/lib/mysqlrouter' directory
- Verifying account (using it to run SQL queries that would be run by Router)
- Storing account in keyring
- Adjusting permissions of generated files
- Creating configuration /etc/mysqlrouter/mysqlrouter.conf

# MySQL Router configured for the InnoDB Cluster 'MGR1'

After this MySQL Router has been started with the generated configuration

    $ /etc/init.d/mysqlrouter restart
or
    $ systemctl start mysqlrouter
or
    $ mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf

the cluster 'MGR1' can be reached by connecting to:

## MySQL Classic protocol  <-- MySQL协议的两个端口

- Read/Write Connections: localhost:6446
- Read/Only Connections:  localhost:6447

## MySQL X protocol  <-- MySQL X协议的两个端口

- Read/Write Connections: localhost:6448
- Read/Only Connections:  localhost:6449
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.

[root@greatsql]# systemctl start mysqlrouter

[root@greatsql]# ps -ef | grep -v grep | grep mysqlrouter
mysqlro+  6026     1  5 09:28 ?        00:00:00 /usr/bin/mysqlrouter

[root@greatsql]# netstat -lntp | grep mysqlrouter
tcp        0      0 0.0.0.0:6446            0.0.0.0:*               LISTEN      6026/mysqlrouter
tcp        0      0 0.0.0.0:6447            0.0.0.0:*               LISTEN      6026/mysqlrouter
tcp        0      0 0.0.0.0:6448            0.0.0.0:*               LISTEN      6026/mysqlrouter
tcp        0      0 0.0.0.0:6449            0.0.0.0:*               LISTEN      6026/mysqlrouter
tcp        0      0 0.0.0.0:8443            0.0.0.0:*               LISTEN      6026/mysqlrouter
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

[routing:greatsqlMGR_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://greatsqlMGR/?role=PRIMARY
routing_strategy=first-available
protocol=classic
1.
2.
3.
4.
5.
6.

$ mysql -h172.16.16.10 -u GreatSQL -p -P6446
Enter password:
...
#记住下面几个 MEMBER_ID
mysql> select MEMBER_ID,MEMBER_ROLE from performance_schema.replication_group_members;
+--------------------------------------+-------------+
| MEMBER_ID                            | MEMBER_ROLE |
+--------------------------------------+-------------+
| 4ebd3504-11d9-11ec-8f92-70b5e873a570 | PRIMARY     |
| 549b92bf-11d9-11ec-88e1-70b5e873a570 | SECONDARY   |
| 5596116c-11d9-11ec-8624-70b5e873a570 | SECONDARY   |
+--------------------------------------+-------------+

mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 4ebd3504-11d9-11ec-8f92-70b5e873a570 |
+--------------------------------------+
# 确实是连接的PRIMARY节点
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

$ mysql -h172.16.16.10 -u GreatSQL -p -P6447
Enter password:
...
mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 549b92bf-11d9-11ec-88e1-70b5e873a570 |
+--------------------------------------+
# 确实是连接的SECONDARY节点
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

$ mysqlsh --uri GreatSQL@172.16.16.10:3306
...
MySQL  172.16.16.10:3306 ssl  JS >  var c=dba.getCluster();
MySQL  172.16.16.10:3306 ssl  JS >  c.setPrimaryInstance('172.16.16.11:3306');   <-- 切换PRIMARY节点
Setting instance '172.16.16.11:3306' as the primary instance of cluster 'MGR1'...

Instance '172.16.16.10:3306' was switched from PRIMARY to SECONDARY.   <-- 切换了，从PRIMARY到SECONDARY
Instance '172.16.16.11:3306' was switched from SECONDARY to PRIMARY.   <-- 切换了，从SECONDARY到PRIMARY
Instance '172.16.16.12:3306' remains SECONDARY.   <-- 保持不变

WARNING: The cluster internal session is not the primary member anymore. For cluster management operations please obtain a fresh cluster handle using dba.getCluster().

The instance '172.16.16.11:3306' was successfully elected as primary.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

mysql> select @@server_uuid;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> select @@server_uuid;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    157990
Current database: *** NONE ***

+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 549b92bf-11d9-11ec-88e1-70b5e873a570 |   <-- 确认server_uuid变成新的
+--------------------------------------+
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.