Mysql read-Write separation-mysql router

Principle: MySQL router reads and writes according to the port, sends all requests that connect the read-write port to master, and all requests to the read-only port are polled to multiple slave for read-write separation

Master: Sql-master 192.168.200.101/24

From: Sql-slave 192.168.200.102/24,192.168.200.103/24

Mysql-router 192.168.200.104/24

Installing Mysql-router with RPM

RPM-IVH https://dev.mysql.com/get/Downloads/MySQL-Router/mysql-router-2.1.4-1.el7.x86_64.rpm

Vim/etc/mysqlrouter/mysqlrouter.conf

[DEFAULT]

Logging_folder =/var/log/mysqlrouter/

Plugin_folder =/usr/lib64/mysqlrouter

Runtime_folder =/var/run/mysqlrouter

Config_folder =/etc/mysqlrouter

[Logger]

Level = info

[KeepAlive]

Interval = 60

[Routing:read_write]

bind_address=192.168.200.104 #监听地址

bind_port=7001 #监听端口

destinations=192.168.200.101:3306 #mysql列表

Mode=read-write #指定读写

max_connections=1024

max_connect_errors=100

Client_connect_timeout=9

[Routing:read_only]

bind_address=192.168.200.104

bind_port=7002

destinations=192.168.200.102:3306,192.168.200.103:3306

Mode=read-only #指定只读

max_connections=65535

max_connect_errors=100

Client_connect_timeout=9

Systemctl Start Mysqlrouter

Systemctl Enable Mysqlrouter

Ss-ntlu

TCP LISTEN 0 192.168.200.106:7001 *:*

TCP LISTEN 0 192.168.200.106:7002 *:*

Primary Database configuration:

Mysql-u root-p123456

Create user test settings password 123456

MariaDB [(None)]> grant all on * * to [e-mail protected] ' 127.0.0.1 ' identified by "Hello";

MariaDB [(none)]> use MySQL;

Modify Host Permissions

MariaDB [(None)]> Update user Set host = '% ' where user = ' test ';

MariaDB [mysql]> Select User, host from user;

+------+---------------+

| user | Host |

+------+---------------+

| Test | %             |

Refreshing the database

MariaDB [mysql]> flush Privileges;

mysql-utest-p123456-h192.168.200.104-p7001

Mysql read-Write separation-mysql router