mysql router是什么

# wget -p /software/ https://repo.mysql.com//mysql80-community-release-el7-3.noarch.rpm
# rpm -uvh /software/mysql80-community-release-el7-3.noarch.rpm
# yum -y install mysql-router

[root@node4 yum.repos.d]# mysqlrouter --help
mysql router  ver 8.0.16 for linux on x86_64 (mysql community - gpl)
copyright (c) 2015, 2019, oracle and/or its affiliates. all rights reserved.

oracle is a registered trademark of oracle corporation and/or its
affiliates. other names may be trademarks of their respective
owners.

configuration read from the following files in the given order (enclosed
in parentheses means not available for reading):
  /etc/mysqlrouter/mysqlrouter.conf
  (/root/.mysqlrouter.conf)
plugins path:
  /usr/lib64/mysqlrouter

default log directory:
  /var/log/mysqlrouter

default persistent data directory:
  /var/lib/mysqlrouter

default runtime state directory:
  /run/mysqlrouter

[root@node4 tmp]# mysqlrouter --bootstrap root@172.16.9.51:3306 --directory /data/mysqlrouter --conf-use-sockets --user=root
please enter mysql password for root: 
warning: the mysql server does not have ssl configured and metadata used by the router may be transmitted unencrypted.
# bootstrapping mysql router instance at '/data/mysqlrouter'...

- checking for old router accounts
  - no prior router accounts found
- creating mysql account mysql_router1_zhi3m2uhudci@'%' for cluster management
- storing account in keyring
- adjusting permissions of generated files
- creating configuration /data/mysqlrouter/mysqlrouter.conf

# mysql router configured for the innodb cluster 'st'    #innodb集群名

after this mysql router has been started with the generated configuration

    $ mysqlrouter -c /data/mysqlrouter/mysqlrouter.conf

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

## mysql classic protocol

- read/write connections: localhost:6446, /data/mysqlrouter/mysql.sock
- read/only connections:  localhost:6447, /data/mysqlrouter/mysqlro.sock

## mysql x protocol

- read/write connections: localhost:64460, /data/mysqlrouter/mysqlx.sock
- read/only connections:  localhost:64470, /data/mysqlrouter/mysqlxro.sock

# file automatically generated during mysql router bootstrap
[default]
user=root
logging_folder=/data/mysqlrouter/log
runtime_folder=/data/mysqlrouter/run
data_folder=/data/mysqlrouter/data
keyring_path=/data/mysqlrouter/data/keyring
master_key_path=/data/mysqlrouter/mysqlrouter.key
connect_timeout=15
read_timeout=30
#dynamic_state=/data/mysqlrouter/data/state.json

[logger]
level = info

[metadata_cache:st]
router_id=14
bootstrap_server_addresses=mysql://oratest51:3306,mysql://oratest52:3306,mysql://test61:3306
user=mysql_router14_ebhje7bsnckc
metadata_cluster=st
ttl=300
use_gr_notifications=0

[routing:st_default_rw]
bind_address=0.0.0.0
bind_port=6446
socket=/data/mysqlrouter/mysql.sock
destinations=metadata-cache://st/default?role=primary
routing_strategy=first-available
protocol=classic

[routing:st_default_ro]
bind_address=0.0.0.0
bind_port=6447
socket=/data/mysqlrouter/mysqlro.sock
destinations=metadata-cache://st/default?role=secondary
routing_strategy=round-robin-with-fallback
protocol=classic

[routing:st_default_x_rw]
bind_address=0.0.0.0
bind_port=64460
socket=/data/mysqlrouter/mysqlx.sock
destinations=metadata-cache://st/default?role=primary
routing_strategy=first-available
protocol=x

[routing:st_default_x_ro]
bind_address=0.0.0.0
bind_port=64470
socket=/data/mysqlrouter/mysqlxro.sock
destinations=metadata-cache://st/default?role=secondary
routing_strategy=round-robin-with-fallback
protocol=x

[root@node2 mysqlrouter]# cd /data/mysqlrouter/
[root@node2 mysqlrouter]# sh start.sh

# mysql -uroot -p123456 -h172.16.8.68 -p6446
# mysql -uroot -p123456 -h172.16.8.68 -p6447
# netstat -ntlp |grep mysqlrouter

[root@node4 etc]# mkdir /software;cd /software
[root@node4 etc]# wget https://cdn.mysql.com//downloads/mysql-router/mysql-router-8.0.17-linux-glibc2.12-x86_64.tar.xz

[root@node4 etc]# tar -jxvf /software/mysql-router-8.0.17-linux-glibc2.12-x86_64.tar.xz -c /usr/local/
[root@node4 etc]# cd /usr/local
[root@node4 etc]# mv mysql-router-8.0.17-linux-glibc2.12-x86_64/ mysqlrouter

[root@node4 etc]# echo 'export path=/usr/local/mysqlrouter/bin/:$path' >> /etc/profile
[root@node4 etc]# source /etc/profile

# mkdir -p /usr/local/mysqlrouter/etc
# cp /usr/local/mysqlrouter/share/doc/mysqlrouter/sample_mysqlrouter.conf /usr/local/mysqlrouter/etc
# mv /usr/local/mysqlrouter/etc/sample_mysqlrouter.conf /usr/local/mysqlrouter/etc/mysqlrouter.conf
# vim /usr/local/mysqlrouter/etc/mysqlrouter.conf
##部分省略
[default]
logging_folder =/usr/local/mysqlrouter/log/
plugin_folder = /usr/local/mysqlrouter/lib/mysqlrouter/
config_folder = /usr/local/mysqlrouter/etc/
runtime_folder = /usr/local/mysqlrouter/run/
data_folder = /usr/local/mysqlrouter/data/
keyring_path = /var/lib/keyring-data
master_key_path = /var/lib/keyring-key

[logger]
level = info

[routing:masters]
bind_address = 172.16.8.68:7002
destinations = 172.16.9.61:3306
mode = read-write
connect_timeout = 2

[routing:slaves]
bind_address = 172.16.8.68:7001
destinations = 172.16.9.51:3306,172.16.9.52:3306
mode = read-only
connect_timeout = 1
##部分省略

[routing:mutili_rw]
bind_address=172.16.8.68
bind_port=7003
destinations=172.16.9.51:3306,172.16.9.52:3306,172.16.9.61:3306
mode=read-write
connect_timeout=2
protocol=classic

[root@node4 etc]# mkdir -p /usr/local/mysqlrouter/run/
[root@node4 etc]# mkdir -p /usr/local/lib/mysqlrouter
[root@node4 etc]# mkdir -p /usr/local/mysqlrouter/etc/
[root@node4 etc]# mkdir -p /usr/local/mysqlrouter/run/
[root@node4 etc]# mkdir -p /usr/local/mysqlrouter/data/
[root@node4 etc]# mkdir -p /usr/local/mysqlrouter/log/

[root@node4 etc]# mysqlrouter --config /usr/local/mysqlrouter/etc/mysqlrouter.conf &

[root@node4 etc]# netstat -ntlp |grep mysqlrouter
tcp        0      0 172.16.8.68:7001        0.0.0.0:*               listen      9221/mysqlrouter    
tcp        0      0 172.16.8.68:7002        0.0.0.0:*               listen      9221/mysqlrouter

[root@node4 mysqlrouter]# for ((i=0;i<=5;i++));do mysql -h172.16.8.101 -uroot -p123456 -p6446 -e"select @@hostname;";done;
mysql: [warning] using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| oratest51  |
+------------+
mysql: [warning] using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| oratest51  |
+------------+
mysql: [warning] using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| oratest51  |
+------------+
mysql: [warning] using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| oratest51  |
+------------+
mysql: [warning] using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| oratest51  |
+------------+
mysql: [warning] using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| oratest51  |
+------------+

[root@oratest52 ~]# systemctl status mysql
[root@oratest52 ~]# systemctl stop mysql
[root@oratest52 ~]# systemctl status mysql

 mysql  172.16.9.51:3306  js > cluster.status()
{
    "clustername": "st", 
    "defaultreplicaset": {
        "name": "default", 
        "primary": "oratest51:3306", 
        "ssl": "disabled", 
        "status": "ok_no_tolerance", 
        "statustext": "cluster is not tolerant to any failures. 1 member is     not active", 
        "topology": {
            "oratest51:3306": {
                "address": "oratest61:3306", 
                "mode": "r/o", 
                "readreplicas": {}, 
                "role": "ha", 
                "status": "online"
            }, 
            "oratest52:3306": {
                "address": "oratest52:3306", 
                "mode": "n/a", 
                "readreplicas": {}, 
                "role": "ha", 
                "shellconnecterror": "mysql error 2003 (hy000): can't connect     to mysql server on 'oratest52' (111)", 
                "status": "(missing)"
            }, 
            "test61:3306": {
                "address": "test51:3306", 
                "mode": "r/w", 
                "readreplicas": {}, 
                "role": "ha", 
                "status": "online"
            }
        }, 
        "topologymode": "single-primary"
    }, 
    "groupinformationsourcemember": "test61:3306"
}

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| channel_name              | member_id                            | member_host | member_port | member_state |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 48cdca42-a386-11e9-95f1-0050569d2587 | test61      |        3306 | online       |
| group_replication_applier | e9ee5267-a386-11e9-ac35-0050569d9ef5 | oratest51   |        3306 | online       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+

[root@node4 ~]# for ((i=0;i<=5;i++));do mysql -h172.16.8.101 -uroot -p123456 -p6447 -e"select @@hostname;";done;
mysql: [warning] using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| test61  |
+------------+
mysql: [warning] using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| test61  |
+------------+
mysql: [warning] using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| test61  |
+------------+
mysql: [warning] using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| test61     |
+------------+
mysql: [warning] using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| test61     |
+------------+

[root@oratest52 ~]# systemctl start mysql

mysql> start group_replication;
query ok, 0 rows affected (2.92 sec)

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| channel_name              | member_id                            | member_host | member_port | member_state |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 48cdca42-a386-11e9-95f1-0050569d2587 | test61      |        3306 | online       |
| group_replication_applier | 50bc1160-a386-11e9-92c9-0050569dc0da | oratest52   |        3306 | online       |
| group_replication_applier | e9ee5267-a386-11e9-ac35-0050569d9ef5 | oratest51   |        3306 | online       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+

 mysql  172.16.9.51:3306  js > cluster.status()
{
    "clustername": "st", 
    "defaultreplicaset": {
        "name": "default", 
        "primary": "oratest51:3306", 
        "ssl": "disabled", 
        "status": "ok", 
        "statustext": "cluster is online and can tolerate up to one failure.", 
        "topology": {
            "oratest51:3306": {
                "address": "oratest61:3306", 
                "mode": "r/o", 
                "readreplicas": {}, 
                "role": "ha", 
                "status": "online"
            }, 
            "oratest52:3306": {
                "address": "oratest52:3306", 
                "mode": "r/o", 
                "readreplicas": {}, 
                "role": "ha", 
                "status": "online"
            }, 
            "test61:3306": {
                "address": "test51:3306", 
                "mode": "r/w", 
                "readreplicas": {}, 
                "role": "ha", 
                "status": "online"
            }
        }, 
        "topologymode": "single-primary"
    }, 
    "groupinformationsourcemember": "test61:3306"
}

[root@oratest51 data]# systemctl status mysql
[root@oratest51 data]# systemctl stop mysql
[root@oratest51 data]# systemctl status mysql
[root@oratest51 data]# ps -ef |grep mysql

 mysql  172.16.9.61:3306  js > cluster.status();
{
    "clustername": "st", 
    "defaultreplicaset": {
        "name": "default", 
        "primary": "test61:3306", 
        "ssl": "disabled", 
        "status": "ok_no_tolerance", 
        "statustext": "cluster is not tolerant to any failures. 1 member is not active", 
        "topology": {
            "oratest51:3306": {
                "address": "oratest51:3306", 
                "mode": "n/a", 
                "readreplicas": {}, 
                "role": "ha", 
                "shellconnecterror": "mysql error 2003 (hy000): can't connect to mysql server on 'oratest51' (111)", 
                "status": "(missing)"
            }, 
            "oratest52:3306": {
                "address": "oratest52:3306", 
                "mode": "r/o", 
                "readreplicas": {}, 
                "role": "ha", 
                "status": "online"
            }, 
            "test61:3306": {
                "address": "test61:3306", 
                "mode": "r/w", 
                "readreplicas": {}, 
                "role": "ha", 
                "status": "online"
            }
        }, 
        "topologymode": "single-primary"
    }, 
    "groupinformationsourcemember": "test61:3306"
}

[root@node2 mysqlrouter]# for ((i=0;i<=5;i++));do mysql -h172.16.8.101 -uroot -p123456 -p6446 -e"select @@hostname;";done;
mysql: [warning] using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| test61     |
+------------+
mysql: [warning] using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| test61     |
+------------+
mysql: [warning] using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| test61     |
+------------+
mysql: [warning] using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| test61     |
+------------+
mysql: [warning] using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| test61     |
+------------+
mysql: [warning] using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| test61     |
+------------+

[root@oratest51 ~]# systemctl start mysql
[root@oratest51 ~]# systemctl status mysql
[root@oratest51 ~]# mysql -uroot -p123456
mysql> start group_replication;
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------+-------------+--------------+
| channel_name              | member_id                            |member_host | member_port | member_state |
+---------------------------+--------------------------------------+------------+-------------+--------------+
| group_replication_applier | 48cdca42-a386-11e9-95f1-0050569d2587 |test61      |        3306 | online       |
| group_replication_applier | 50bc1160-a386-11e9-92c9-0050569dc0da |oratest52   |        3306 | online       |
| group_replication_applier | e9ee5267-a386-11e9-ac35-0050569d9ef5 |oratest51   |        3306 | online       |
+---------------------------+--------------------------------------+------------+-------------+--------------+
3 rows in set (0.00 sec)
mysql> quit
[root@oratest51 local]# mysqlsh
 mysql  js > shell.connect('root@172.16.9.51:3306')
 mysql  172.16.9.51:3306  js > var cluster = dba.getcluster()
 mysql  172.16.9.51:3306  js > cluster.status();
{
    "clustername": "st", 
    "defaultreplicaset": {
        "name": "default", 
        "primary": "test61:3306", 
        "ssl": "disabled", 
        "status": "ok", 
        "statustext": "cluster is online and can tolerate up to one failure.", 
        "topology": {
            "oratest51:3306": {
                "address": "oratest51:3306", 
                "mode": "r/o", 
                "readreplicas": {}, 
                "role": "ha", 
                "status": "online"
            }, 
            "oratest52:3306": {
                "address": "oratest52:3306", 
                "mode": "r/o", 
                "readreplicas": {}, 
                "role": "ha", 
                "status": "online"
            }, 
            "test61:3306": {
                "address": "test61:3306", 
                "mode": "r/w", 
                "readreplicas": {}, 
                "role": "ha", 
                "status": "online"
            }
        }, 
        "topologymode": "single-primary"
    }, 
    "groupinformationsourcemember": "test61:3306"
}

[root@node4 mysqlrouter]# for ((i=0;i<=5;i++));do mysql -h172.16.8.101 -uroot -p123456 -p6447 -e"select @@hostname;";done;
mysql: [warning] using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| oratest52  |
+------------+
mysql: [warning] using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| oratest51     |
+------------+
mysql: [warning] using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| oratest52  |
+------------+
mysql: [warning] using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| oratest51     |
+------------+
mysql: [warning] using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| oratest52  |
+------------+
mysql: [warning] using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| test61     |
+------------+

# mysqlrouter --bootstrap 172.16.9.51:3306 --directory /data/mysqlrouter --user=root --conf-use-sockets --force

#systemctl restart mysqlrouter
#systemctl status mysqlrouter

[root@oratest51 data]# mysqlrouter --bootstrap root@172.16.9.51:3310 --directory /data/mysqlrouter --conf-use-sockets --user=root
please enter mysql password for root: 
error: unable to connect to the metadata server: error connecting to mysql server at 172.16.9.51:3310: can't connect to mysql server on '172.16.9.51' (111) (2003)

mysql> grant all privileges  on *.* to root@'%' identified by "123456";
mysql> flush privileges;

[root@oratest51 data]# mysqlrouter --bootstrap root@172.16.9.51:3306 --directory /data/mysqlrouter --conf-use-sockets --user=root
please enter mysql password for root: 
error: expected mysql server '172.16.9.51:3306' to contain the metadata of mysql innodb cluster, but the schema does not exist.
checking version of the metadata schema failed with: error executing mysql query: table 'mysql_innodb_cluster_metadata.schema_version' doesn't exist (1146)

see https://dev.mysql.com/doc/refman/en/mysql-innodb-cluster-creating.html for instructions on setting up a mysql server to act as an innodb cluster metadata server

[root@node2 log]# tailf /root/mysqlrouter/log/mysqlrouter.log 
2019-08-02 15:37:52 routing info [7f9721e2d700] [routing:st_default_ro] started: listening using /root/mysqlrouter/mysqlro.sock
2019-08-02 15:37:52 routing info [7f972162c700] [routing:st_default_rw] started: listening using /root/mysqlrouter/mysql.sock
2019-08-02 15:37:52 routing info [7f9720e2b700] [routing:st_default_x_ro] started: listening using /root/mysqlrouter/mysqlxro.sock
2019-08-02 15:37:52 routing info [7f9713fff700] [routing:st_default_x_rw] started: listening on 0.0.0.0:64460, routing strategy = first-available
2019-08-02 15:37:52 routing info [7f9713fff700] [routing:st_default_x_rw] started: listening using /root/mysqlrouter/mysqlx.sock
2019-08-02 15:37:52 routing info [7f9713fff700] [routing:st_default_x_rw] stopped
2019-08-02 15:37:52 routing info [7f9721e2d700] [routing:st_default_ro] stopped
2019-08-02 15:37:52 routing info [7f972162c700] [routing:st_default_rw] stopped
2019-08-02 15:37:52 routing info [7f9720e2b700] [routing:st_default_x_ro] stopped
2019-08-02 15:37:52 main error [7f9726f1b880] error: bootstrap_server_addresses is not allowed when dynamic state file is used

 1 # file automatically generated during mysql router bootstrap
 2 [default]
 3 user=root
 4 logging_folder=/root/mysqlrouter/log
 5 runtime_folder=/root/mysqlrouter/run
 6 data_folder=/root/mysqlrouter/data
 7 keyring_path=/root/mysqlrouter/data/keyring
 8 master_key_path=/root/mysqlrouter/mysqlrouter.key
 9 connect_timeout=15
10 read_timeout=30
11 #dynamic_state=/root/mysqlrouter/data/state.json

[root@node2 log]# tailf /root/mysqlrouter/log/mysqlrouter.log 
2019-08-02 15:46:41 metadata_cache warning [7f3030405700] while updating metadata, could not establish a connection to replicaset 'default' through test61:3306
2019-08-02 15:46:51 metadata_cache warning [7f3030405700] while updating metadata, could not establish a connection to replicaset 'default' through oratest52:3306
2019-08-02 15:47:01 metadata_cache warning [7f3030405700] while updating metadata, could not establish a connection to replicaset 'default' through oratest51:3306
2019-08-02 15:47:01 metadata_cache error [7f3030405700] unable to fetch live group_replication member data from any server in replicaset 'default'