mysql-router设置_mysql-router配置
os: centos 7.4
db: mysql 8.0.18
innodb cluster 是 mysql group replication + mysql-shell + mysql-router ,其中 mysql-router 是和应用安装在一起提供高可用.
mysqlrouter 生成配置文件
# /usr/local/mysql-router-8.0.18-linux-glibc2.12-x86_64/bin/mysqlrouter --bootstrap innodbclusteradmin@192.168.56.51:3306 --directory /usr/local/mysql-router-8.0.18-linux-glibc2.12-x86_64/3306 --conf-use-sockets --user=root
Please enter MySQL password for innodbclusteradmin:
# Bootstrapping MySQL Router instance at '/usr/local/mysql-router-8.0.18-linux-glibc2.12-x86_64/3306'...
- Checking for old Router accounts
- No prior Router accounts found
- Creating mysql account 'mysql_router1_fucrtwhosizh'@'%' for cluster management
- Storing account in keyring
- Adjusting permissions of generated files
- Creating configuration /usr/local/mysql-router-8.0.18-linux-glibc2.12-x86_64/3306/mysqlrouter.conf
# MySQL Router configured for the InnoDB cluster 'production'
After this MySQL Router has been started with the generated configuration
$ /usr/local/mysql-router-8.0.18-linux-glibc2.12-x86_64/bin/mysqlrouter -c /usr/local/mysql-router-8.0.18-linux-glibc2.12-x86_64/3306/mysqlrouter.conf
the cluster 'production' can be reached by connecting to:
## MySQL Classic protocol
- Read/Write Connections: localhost:6446, /usr/local/mysql-router-8.0.18-linux-glibc2.12-x86_64/3306/mysql.sock
- Read/Only Connections: localhost:6447, /usr/local/mysql-router-8.0.18-linux-glibc2.12-x86_64/3306/mysqlro.sock
## MySQL X protocol
- Read/Write Connections: localhost:64460, /usr/local/mysql-router-8.0.18-linux-glibc2.12-x86_64/3306/mysqlx.sock
- Read/Only Connections: localhost:64470, /usr/local/mysql-router-8.0.18-linux-glibc2.12-x86_64/3306/mysqlxro.sock
查看 mysqlrouter 配置文件
# tree ./3306
./3306
├── data
│ ├── keyring
│ └── state.json
├── log
│ └── mysqlrouter.log
├── mysqlro.sock
├── mysqlrouter.conf
├── mysqlrouter.key
├── mysql.sock
├── mysqlxro.sock
├── mysqlx.sock
├── run
├── start.sh
└── stop.sh
# cat /usr/local/mysql-router-8.0.18-linux-glibc2.12-x86_64/3306/mysqlrouter.conf
# File automatically generated during MySQL Router bootstrap
[DEFAULT]
user=root
logging_folder=/usr/local/mysql-router-8.0.18-linux-glibc2.12-x86_64/3306/log
runtime_folder=/usr/local/mysql-router-8.0.18-linux-glibc2.12-x86_64/3306/run
data_folder=/usr/local/mysql-router-8.0.18-linux-glibc2.12-x86_64/3306/data
keyring_path=/usr/local/mysql-router-8.0.18-linux-glibc2.12-x86_64/3306/data/keyring
master_key_path=/usr/local/mysql-router-8.0.18-linux-glibc2.12-x86_64/3306/mysqlrouter.key
connect_timeout=15
read_timeout=30
dynamic_state=/usr/local/mysql-router-8.0.18-linux-glibc2.12-x86_64/3306/data/state.json
[logger]
level = INFO
[metadata_cache:production]
router_id=1
user=mysql_router1_fucrtwhosizh
metadata_cluster=production
ttl=0.5
use_gr_notifications=0
[routing:production_default_rw]
bind_address=0.0.0.0
bind_port=6446
socket=/usr/local/mysql-router-8.0.18-linux-glibc2.12-x86_64/3306/mysql.sock
destinations=metadata-cache://production/default?role=PRIMARY
routing_strategy=first-available
protocol=classic
[routing:production_default_ro]
bind_address=0.0.0.0
bind_port=6447
socket=/usr/local/mysql-router-8.0.18-linux-glibc2.12-x86_64/3306/mysqlro.sock
destinations=metadata-cache://production/default?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=classic
[routing:production_default_x_rw]
bind_address=0.0.0.0
bind_port=64460
socket=/usr/local/mysql-router-8.0.18-linux-glibc2.12-x86_64/3306/mysqlx.sock
destinations=metadata-cache://production/default?role=PRIMARY
routing_strategy=first-available
protocol=x
[routing:production_default_x_ro]
bind_address=0.0.0.0
bind_port=64470
socket=/usr/local/mysql-router-8.0.18-linux-glibc2.12-x86_64/3306/mysqlxro.sock
destinations=metadata-cache://production/default?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=x
注意最后几个配置块 role=PRIMARY ，role=SECONDARY
启动 mysqlrouter
# /usr/local/mysql-router-8.0.18-linux-glibc2.12-x86_64/bin/mysqlrouter -c /usr/local/mysql-router-8.0.18-linux-glibc2.12-x86_64/3306/mysqlrouter.conf &
# ps -ef|grep -i mysqlrouter
root 5153 2405 0 18:34 pts/1 00:00:00 /usr/local/mysql-router-8.0.18-linux-glibc2.12-x86_64/bin/mysqlrouter -c /usr/local/mysql-router-8.0.18-linux-glibc2.12-x86_64/3306/mysqlrouter.conf
root 5173 2405 0 18:35 pts/1 00:00:00 grep --color=auto -i mysqlrouter
# netstat -lntp |grep -i mysqlrouter
tcp 0 0 0.0.0.0:64460 0.0.0.0:* LISTEN 5153/mysqlrouter
tcp 0 0 0.0.0.0:6446 0.0.0.0:* LISTEN 5153/mysqlrouter
tcp 0 0 0.0.0.0:6447 0.0.0.0:* LISTEN 5153/mysqlrouter
tcp 0 0 0.0.0.0:64470 0.0.0.0:* LISTEN 5153/mysqlrouter
连接测试
innodb cluster 查看
# mysql -h127.0.0.1 -P3306 -uroot -pdbma@0352 -e"select * from performance_schema.replication_group_members;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 25d6fd7d-1a65-11ea-9bf5-08002789e450 | mgr1 | 3306 | ONLINE | PRIMARY | 8.0.18 |
| group_replication_applier | 750e318b-1a65-11ea-8dc7-080027d02a4f | mgr2 | 3306 | ONLINE | SECONDARY | 8.0.18 |
| group_replication_applier | c5345a03-1a65-11ea-bbaf-08002792140b | mgr3 | 3306 | ONLINE | SECONDARY | 8.0.18 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
在 PRIMARY 创建 数据库及用户
mysql> create database peiybdb;
Query OK, 1 row affected (0.09 sec)
mysql> create user peiyb@'%' identified by 'mysql';
Query OK, 0 rows affected (0.36 sec)
# mysqlsh --sql -upeiyb -pmysql -P6446 -e"select @@hostname"
WARNING: Using a password on the command line interface can be insecure.
@@hostname
mgr1
# mysqlsh --sql -upeiyb -pmysql -P6447 -e"select @@hostname"
WARNING: Using a password on the command line interface can be insecure.
@@hostname
mgr3
参考: