Penghala MySQL ialah perisian tengah ringan yang disediakan secara rasmi oleh MySQL dan merupakan sebahagian daripada Kluster InnoDB Ia boleh menyediakan penghalaan telus antara aplikasi dan pelayan MySQL bahagian belakang, ia digunakan terutamanya untuk menyelesaikan masalah hamba MySQL perpustakaan Ketersediaan tinggi, pengimbangan beban, pengembangan mudah dan isu-isu lain bagi kluster. Penghala MySQL menyediakan IP maya untuk kluster pangkalan data sebagai titik sambungan tunggal untuk aplikasi Melalui titik sambungan tunggal ini, pengimbangan beban, pemisahan baca-tulis, failover dan penyelesaian ketersediaan tinggi pangkalan data yang lain boleh dilaksanakan.
Persekitaran pengendalian tutorial ini: sistem windows7, versi mysql8, komputer Dell G3.
MySQL Router ialah perisian pertengahan ringan yang disediakan secara rasmi oleh MySQL , sebahagian Kluster InnoDB, menyediakan penghalaan telus antara aplikasi dan pelayan MySQL bahagian belakang. Ia digunakan terutamanya untuk menyelesaikan masalah ketersediaan tinggi, pengimbangan beban, dan pengembangan mudah kluster pangkalan data tuan-hamba MySQL. Penghala berfungsi sebagai lapisan pemajuan trafik, terletak di antara aplikasi dan pelayan MySQL, dan fungsinya serupa dengan LVS.
Berdasarkan mekanisme replikasi kumpulan (MGR), apabila nod induk turun dan meninggalkan kluster, nod yang tinggal akan memilih nod induk baharu berdasarkan protokol paxos. Terdapat masalah di sini. Jika aplikasi disambungkan ke nod induk, dan nod induk ranap dan meninggalkan kluster, alamat IP pangkalan data yang tersedia berubah Pada masa ini, aplikasi klien masih akan cuba menyambung ke nod yang gagal. walaupun pelanggan boleh diubah suai konfigurasi sambungan aplikasi akhir, tetapi keadaan ini pada dasarnya tidak realistik.
Menggabungkan Replikasi Kumpulan MySQL dan MySQL Shel adalah seperti berikut:
Gambar di atas menggambarkan sepenuhnya peranan Penghala MySQL dalam kelompok InnoDB Fungsi utamanya adalah untuk menyediakan IP maya untuk kluster pangkalan data sebagai titik sambungan tunggal untuk aplikasi ini , pengimbangan beban dan pemisahan baca-tulis dicapai , failover dan penyelesaian ketersediaan tinggi pangkalan data yang lain.
Penghala MySQL disyorkan untuk dipasang pada mesin tempat aplikasi terletak, atas sebab berikut:
Kluster ialah nod maya dalam penyelesaian ketersediaan tinggi ini, yang akan mewujudkan nod maya pada semua ahli MGR Pangkalan data bernama MySQL_innodb_cluster_metadata menyimpan maklumat metadata kluster, termasuk maklumat kluster, ahli kluster, maklumat replikasi kumpulan, Penghala MySQL yang disambungkan dan maklumat lain untuk menyediakan pertanyaan Penghala MySQL. Ia bersamaan dengan lapisan enkapsulasi logik ahli pada replikasi kumpulan, dipaparkan dalam mod kluster Status setiap nod disegerakkan dalam masa nyata dengan status ahli contoh yang sepadan dalam replikasi kumpulan, tetapi nod bagi. kluster dan replikasi kumpulan Ahli hanya disegerakkan apabila kluster dibuat. Perubahan ahli dalam replikasi kumpulan kemudiannya tidak disegerakkan secara automatik ke kluster Anda boleh menambah atau mengalih keluar nod dalam kluster aplikasi berakhir lebih terkawal dan fleksibel.
| 主机名 | IP地址 | 角色 |
|---|---|---|
| oratest51 | 172.16.9.51 | primary |
| oratest52 | 172.16.9.52 | seconde |
| test61 | 172.16.9.61 | seconde |
| node4 | 172.16.8.68 | MySQL Router |
Sistem pengendalian: CentOS Linux keluaran 7.2.1511
Versi MySQL: mysql-5.7.26-linux-glibc2.12-x86_64
Versi Penghala MySQL: mysql-router-8.0.12.linux-glibc 12-x86_64
Versi MySQL Shell: mysql-shell-8.0.17-linux-glibc2.12-x86-64bit
Penghala MySQL mempunyai dua mod penggunaan:
--directory dalam fail konfigurasi ialah 6446 dan 6447. /usr/local/mysqlrouter/share/doc/mysqlrouter/sample_mysqlrouter.conf mengerahkan. Nota: Prasyarat untuk memasang Penghala MySQL dalam mod bootstrap ialah MGR telah dipasang , MySQL Shell telah dipasang, InnoDB Cluster telah dikonfigurasikan
# 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
Anda boleh melihat beberapa laluan lalai melalui mysqlrouter --help:
- Fail konfigurasi: /etc/mysqlrouter/mysqlrouter . conf
- Laluan pemalam: /usr/lib64/mysqlrouter
- Laluan log: /var/log/mysqlrouter
- Laluan data berterusan: /var/lib/mysqlrouter
- Laluan keadaan masa jalan: /run/mysqlrouter
Jika anda menggunakan pilihan --config atau -c pada baris arahan mysqlrouter untuk menghantar pengguna fail konfigurasi definisi, fail konfigurasi lalai tidak akan dimuatkan.
[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
--bootstrap: But dan konfigurasikan Penghala untuk dijalankan dengan kluster MySQL InnoDB.
# 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
Proses permulaan adalah perlahan, dan maklumat permulaan boleh dilihat dalam log
Secara lalai, gunakan 6446 sebagai port RW, gunakan 6447 sebagai port RO
# 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 ##部分省略
/usr/local/mysqlrouter/share/doc/mysqlrouter/ sample_mysqlrouter.conf Ini ialah fail konfigurasi rujukan untuk MySQL Router , jadi salin templat di sini dahulu.
Penghala Mysql akan mencari "mysqlrouter.conf" dalam direktori pemasangan dan ".mysqlrouter.conf" dalam direktori utama secara lalai. Anda juga boleh menggunakan "-c" atau "--config" di bawah arahan mysqlrouter program binari untuk menentukan fail konfigurasi secara manual.
Fail konfigurasi Penghala MySQL adalah berpecah-belah Terdapat tiga serpihan yang biasa digunakan: [DEFAULT], [logger], [routing:NAME]. "#" atau ";" komen.
Jika mod berbilang induk, fail konfigurasi adalah seperti berikut
[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
Tiada skrip berhenti untuk menghentikan Penghala MySQL Jika anda ingin berhenti, anda perlu mematikan proses dengan kill -9
Ujian berikut semuanya berada di bawah Kluster InnoDB, dan mod pemasangan Penghala MySQL ialah bootstrap
[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 | +------------+
测试结果： 使用6446 RW端口会访问到后端InnoDB Cluster的primary
[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 | +------------+ | test61 | +------------+ 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 | +------------+ 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 | +------------+
测试结果： 使用6447 RO端口会连接到后端InnoDB Cluster集群中所有的seconde节点，并且以rr(round-robin)的策略调度
(1) 停掉一台seconde节点
[root@oratest52 ~]# systemctl status mysql [root@oratest52 ~]# systemctl stop mysql [root@oratest52 ~]# systemctl status mysql
(2) 在InnoDB Cluster中查看集群状态，可以看到oratest52已经处于MISSING状态
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"
}(3) 在MGR中查看GR状态，可以看到已经没有oratest52这台机器
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 | +---------------------------+--------------------------------------+-------------+-------------+--------------+
(4) 测试通过6447 RO端口连接，查看是否会自动剔除宕机的oratest52节点
[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 | +------------+
测试结果： MySQL Router会自动剔除宕机的oratest52节点，read请求只会转发到后端正常的MySQL实例，过程无需人工干预。
(1) 启动oratest52的MySQL服务
[root@oratest52 ~]# systemctl start mysql
(2)启动MGR，并确认MGR状态正常
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 | +---------------------------+--------------------------------------+-------------+-------------+--------------+
(3) 检查InnoDB Cluster是否正常
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"
}(4) 查看恢复正常的实例，能不能自动加入MySQL Router进行转发，可以看到oratest52可以被正常调度
[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 | +------------+ | test61 | +------------+ 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 | +------------+ 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 | +------------+
测试结果： 恢复正常的MySQL实例可以自动加入MySQL Router并进行转发，无需人工干预。
(1) 当前primary节点是oratest51，在primary节点上停止MySQL，确认停止成功
[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
(2) 查看InnoDB Cluster状态，可以看到oratest51已经处于MISSING状态，primary节点已经切换到了test61
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"
}(3) 测试连接到RW 6446端口，查看是否可以正常连接到新的primary节点test61，并且写入数据否正常
[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 | +------------+
测试结果： 当primary节点挂掉之后，MySQL Router会自动去连接新的primary节点
(1) 启动oratest51，将状态恢复正常
[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"
}(2) 测试RO端口的Router调度
[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 | +------------+
测试结果： 恢复正常的实例加入集群后，会自动加入到MySQL Router并进行调度
MySQL Router在初始化配置的时候是连接到集群节点读取集群的元数据的。如果在集群中新增或减少节点，需要同步更新MySQL Router的配置，否则无法生效
# mysqlrouter --bootstrap 172.16.9.51:3306 --directory /data/mysqlrouter --user=root --conf-use-sockets --force
更新了MySQL Router的配置的配置，需要重启MySQL Router：
#systemctl restart mysqlrouter #systemctl status mysqlrouter
Unable to connect to the metadata server报错如下
[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;
mysql_innodb_cluster_metadata.schema_version表[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
原因： 没有创建InnoDB Cluster集群，创建InnoDB Cluster集群后会自动生成这张表。mysql_innodb_cluster_metadata.schema_version表的用途是MySQL Router在进行调度分配的时候，需要读取这张表的内容来做调度策略。
解决办法： 创建InnoDB Cluster集群
Error: bootstrap_server_addresses is not allowed when dynamic state file is used具体如下：
[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
原因： 开启了dynamic_state
解决办法： 在配置文件中注释掉dynamic_state所在行，例如下列第11行
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
Unable to fetch live group_replication member data from any server in replicaset 'default'具体如下：
[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'
原因： MySQL Router所在节点没有配置本机DNS解析
解决办法： 在/etc/hosts中添加MGR实例的解析
--directory指定的路径下自动生成安装目录，配置文件里的端口为6446和6447。/usr/local/mysqlrouter/share/doc/mysqlrouter/sample_mysqlrouter.conf这个配置文件部署。bootstrap_server_addresses这一栏里配置就可以了，格式如下：bootstrap_server_addresses=mysql://oratest51:3306,mysql://oratest52:3306,mysql://test61:3306
【相关推荐：mysql视频教程】
Atas ialah kandungan terperinci apakah penghala mysql. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!
![[Web front-end] Permulaan pantas Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
