mysql 集群与读写分离_mysqlRouter与MySQL集群结合实现读写分离

、、安装mysql Router

======================================================================================================

[root@vm701 111]# rpm -ivh

mysql-router-community-8.0.15-1.el7.x86_64.rpm

warning: mysql-router-community-8.0.15-1.el7.x86_64.rpm:

Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing... ################################# [100%]

package

mysql-router-community-8.0.15-1.el7.x86_64 is already

installed

[root@vm701 111]#

、、、 配置rw和ro段参数

======================================================================================================

[root@vm701 ~]# cat

/etc/mysqlrouter/mysqlrouter.conf

# Copyright (c) 2015, 2018, Oracle and/or its affiliates. All

rights reserved.

#

# This program is free software; you can redistribute it

and/or modify

# it under the terms of the GNU General Public License,

version 2.0,

# as published by the Free Software Foundation.

#

######### ##############################

## 省略了一些描述

######### ##############################

#

# MySQL Router configuration file

#

# Documentation is available at

# http://dev.mysql.com/doc/mysql-router/en/

### DEFAULT 段没必要变化，都是规范的目录

[DEFAULT]

logging_folder = /var/log/mysqlrouter

runtime_folder = /var/run/mysqlrouter

config_folder = /etc/mysqlrouter

[logger]

level = INFO

# If no plugin is configured which starts a service,

keepalive

# will make sure MySQL Router will not immediately exit. It

is

# safe to remove once Router is configured.

[routing:read_write]

bind_address = 192.168.1.112

bind_port = 8001

mode = read-write

destinations = 192.168.1.112:3306

max_connections = 65535

max_connect_errors = 100

client_connect_timeout = 9

[routing:read_only]

bind_address = 192.168.1.112

bind_port = 7002

mode = read-only

destinations = 192.168.1.114:3306,192.168.1.115:3306

max_connections = 65535

max_connect_errors = 100

client_connect_timeout = 9

[keepalive]

interval = 30

、、、启动router

/usr/bin/mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf &

======================================================================================================

[root@vm701 mysqlrouter]#

[root@vm701 mysqlrouter]#

[root@vm701 mysqlrouter]#

/usr/bin/mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf &

[1] 4763

[root@vm701 mysqlrouter]#

[root@vm701 mysqlrouter]# pwd

/var/log/mysqlrouter

[root@vm701 mysqlrouter]# ll

total 4

-rw-r--r-- 1 root root 3241 Jul 4 16:56

mysqlrouter.log

[root@vm701 mysqlrouter]# cat *

2019-07-04 16:40:10 main INFO [7f7183ba9880] Loading all

plugins.

2019-07-04 16:40:10 main INFO [7f7183ba9880] plugin 'keepalive:' loading

2019-07-04 16:40:10 main INFO [7f7183ba9880] plugin 'routing:read_only' loading

2019-07-04 16:40:10 main INFO [7f7183ba9880] plugin 'routing:read_write' loading

2019-07-04 16:40:10 main INFO [7f7183ba9880] Initializing all

plugins.

2019-07-04 16:40:10 main INFO [7f7183ba9880] plugin 'keepalive' initializing

2019-07-04 16:40:10 main INFO [7f7183ba9880] plugin 'routing' initializing

2019-07-04 16:40:10 main INFO [7f7183ba9880] Starting all

plugins.

2019-07-04 16:40:10 main INFO [7f717f513700] plugin 'keepalive:' starting

2019-07-04 16:40:10 keepalive INFO [7f717f513700] keepalive

started with interval 30

2019-07-04 16:40:10 keepalive INFO [7f717f513700]

keepalive

2019-07-04 16:40:10 main INFO [7f717ed12700] plugin 'routing:read_only' starting

2019-07-04 16:40:10 routing INFO [7f717ed12700]

[routing:read_only] started: listening on 192.168.1.112:7002

2019-07-04 16:40:10 main INFO [7f717e511700] plugin 'routing:read_write' starting

2019-07-04 16:40:10 routing INFO [7f717e511700]

[routing:read_write] started: listening on 192.168.1.112:8001

2019-07-04 16:40:10 main INFO [7f7183ba9880] Running.

2019-07-04 16:40:40 keepalive INFO [7f717f513700]

keepalive

2019-07-04 16:41:10 keepalive INFO [7f717f513700]

keepalive

2019-07-04 16:41:40 keepalive INFO [7f717f513700]

keepalive

、、、测试脚本SQL

======================================================================================================

create table ttid ( xid int , primary key (xid

)) ;

insert into ttid select 101 ;

insert into ttid select 107;

insert into ttid select 102;

-hvm701 8001 测试 RW

端口连接 8001

==============================================================================

从8003 。。。 探测到 8001

[root@vm703 ~]# mysql -uroot -pRoot123! -hvm701 -P8003

mysql: [Warning] Using a password on the command line

interface can be insecure.

ERROR 2003 (HY000): Can't connect to MySQL server on 'vm701'

(111)

[root@vm703 ~]#

[root@vm703 ~]#

[root@vm703 ~]# mysql -uroot -pRoot123! -hvm701 -P8002

mysql: [Warning] Using a password on the command line

interface can be insecure.

ERROR 2003 (HY000): Can't connect to MySQL server on 'vm701'

(111)

[root@vm703 ~]#

[root@vm703 ~]#

[root@vm703 ~]# mysql -uroot -pRoot123! -hvm701 -P8000

mysql: [Warning] Using a password on the command line

interface can be insecure.

ERROR 2003 (HY000): Can't connect to MySQL server on 'vm701'

(111)

[root@vm703 ~]#

[root@vm703 ~]#

[root@vm703 ~]# mysql -uroot -pRoot123! -hvm701 -P8001

mysql: [Warning] Using a password on the command line

interface can be insecure.

Welcome to the MySQL monitor. Commands end

with ; or \g.

Your MySQL connection id is 49

Server version: 8.0.14 MySQL Community Server - GPL

Copyright (c) 2000, 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.

Type 'help;' or '\h' for help. Type '\c' to clear the current

input statement.

mysql>

、、、 SQL 测试

============================================================================

[root@vm703 ~]#

[root@vm703 ~]# mysql -uroot -pRoot123! -hvm701 -P8001

mysql: [Warning] Using a password on the command line

interface can be insecure.

Welcome to the MySQL monitor. Commands end

with ; or \g.

Type 'help;' or '\h' for help. Type '\c' to clear the current

input statement.

mysql> use ISAAC

Reading table information for completion of table and column

names

You can turn off this feature to get a quicker startup with

-A

Database changed

mysql> show tables;

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

| Tables_in_ISAAC |

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

| customer |

| order |

| store |

| store_sales |

| ticket_order |

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

5 rows in set (0.00 sec)

mysql>

mysql>

mysql>

mysql> create table ttid ( xid int , primary key (xid

)) ;

insert into ttid select 101 ;Query OK, 0 rows affected (0.07

sec)

mysql>

mysql> insert into ttid select 101 ;

Query OK, 1 row affected (0.07 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into ttid select 107;

Query OK, 1 row affected (0.51 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into ttid select 102;

Query OK, 1 row affected (0.04 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql>

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql>

、、、-hvm703 从节点连数据库 对比 不用

-P

================================================================================================

[root@vm703 ~]# mysql -uroot -pRoot123! -hvm703

mysql: [Warning] Using a password on the command line

interface can be insecure.

Welcome to the MySQL monitor. Commands end

with ; or \g.

Your MySQL connection id is 35

Server version: 8.0.14 MySQL Community Server - GPL

Copyright (c) 2000, 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.

Type 'help;' or '\h' for help. Type '\c' to clear the current

input statement.

mysql> show variables like '%read_on%'

;

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

| Variable_name | Value

|

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

| innodb_read_only | OFF |

| read_only | ON |

| super_read_only | ON |

| transaction_read_only | OFF |

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

4 rows in set (0.04 sec)

mysql>

mysql>

mysql>

mysql> use ISAAC

Reading table information for completion of table and column

names

You can turn off this feature to get a quicker startup with

-A

Database changed

mysql>

mysql>

mysql>

mysql> show tables;

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

| Tables_in_ISAAC |

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

| customer |

| order |

| store |

| store_sales |

| ticket_order |

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

5 rows in set (0.00 sec)

mysql> select * from ttid ;

ERROR 1146 (42S02): Table 'ISAAC.ttid' doesn't exist

mysql>

mysql>

mysql>

mysql> select * from ttid ;

+-----+

| xid |

+-----+

| 101 |

+-----+

1 row in set (0.00 sec)

mysql> exit

Bye

[root@vm703 ~]# mysql -uroot -pRoot123! -hvm703 -P7000

mysql: [Warning] Using a password on the command line

interface can be insecure.

ERROR 2003 (HY000): Can't connect to MySQL server on 'vm703'

(111)

[root@vm703 ~]#

[root@vm703 ~]#

[root@vm703 ~]# mysql -uroot -pRoot123! -hvm703 -P7002

mysql: [Warning] Using a password on the command line

interface can be insecure.

、、、-hvm70 7002 router的ro端口 7002 后面改为 8008

================================================================================================

[root@vm703 ~]#

[root@vm703 ~]#

[root@vm703 ~]# mysql -uroot -pRoot123! -hvm701 -P7001

mysql: [Warning] Using a password on the command line

interface can be insecure.

ERROR 2003 (HY000): Can't connect to MySQL server on 'vm701'

(111)

[root@vm703 ~]#

[root@vm703 ~]#

[root@vm703 ~]# mysql -uroot -pRoot123! -hvm701 -P7003

mysql: [Warning] Using a password on the command line

interface can be insecure.

ERROR 2003 (HY000): Can't connect to MySQL server on 'vm701'

(111)

[root@vm703 ~]#

[root@vm703 ~]#

[root@vm703 ~]# mysql -uroot -pRoot123! -hvm701 -P7002

mysql: [Warning] Using a password on the command line

interface can be insecure.

Welcome to the MySQL monitor. Commands end

with ; or \g.

Your MySQL connection id is 41

Server version: 8.0.14 MySQL Community Server - GPL

Copyright (c) 2000, 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.

Type 'help;' or '\h' for help. Type '\c' to clear the current

input statement.

mysql> show variables like '%read_on%'

;

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

| Variable_name | Value

|

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

| innodb_read_only | OFF |

| read_only | ON |

| super_read_only | ON |

| transaction_read_only | OFF |

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

4 rows in set (0.02 sec)

mysql>

mysql>

mysql> use ISAAC

Reading table information for completion of table and column

names

You can turn off this feature to get a quicker startup with

-A

Database changed

mysql> select * from ttid;

+-----+

| xid |

+-----+

| 101 |

+-----+

1 row in set (0.00 sec)

mysql>

mysql>

mysql> select * from ttid;

+-----+

| xid |

+-----+

| 101 |

| 102 |

| 107 |

+-----+

3 rows in set (0.01 sec)

mysql>

、、、-hvm70 7002 router的ro端口 ，从7002 改为

8008，绑定所有IP

================================================================================================

[root@vm701 mysqlrouter]# cat

/etc/mysqlrouter/mysqlrouter.conf

# Copyright (c) 2015, 2018, Oracle and/or its affiliates. All

rights reserved.

#

#

# You should have received a copy of the GNU General Public

License

# along with this program; if not, write to the Free

Software

# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA

02110-1301 USA

#

# MySQL Router configuration file

#

# Documentation is available at

# http://dev.mysql.com/doc/mysql-router/en/

[DEFAULT]

logging_folder = /var/log/mysqlrouter

runtime_folder = /var/run/mysqlrouter

config_folder = /etc/mysqlrouter

[logger]

level = INFO

# If no plugin is configured which starts a service,

keepalive

# will make sure MySQL Router will not immediately exit. It

is

# safe to remove once Router is configured.

[routing:read_write]

bind_address = 192.168.1.112

bind_port = 8001

mode = read-write

destinations = 192.168.1.112:3306

max_connections = 65535

max_connect_errors = 100

client_connect_timeout = 9

[routing:read_only]

bind_address = 0.0.0.0

bind_port = 8008

mode = read-only

destinations = 192.168.1.114:3306,192.168.1.115:3306

max_connections = 65535

max_connect_errors = 100

client_connect_timeout = 9

[keepalive]

interval = 30

-P8001 与 P8008 端口 对比

-P8001

==========================================================================================

[root@vm703 ~]# mysql -uroot -pRoot123! -hvm701 -P8001

mysql: [Warning] Using a password on the command line

interface can be insecure.

Welcome to the MySQL monitor. Commands end

with ; or \g.

Your MySQL connection id is 52

Server version: 8.0.14 MySQL Community Server - GPL

Copyright (c) 2000, 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.

Type 'help;' or '\h' for help. Type '\c' to clear the current

input statement.

mysql> use ISAAC

Reading table information for completion of table and column

names

You can turn off this feature to get a quicker startup with

-A

Database changed

mysql> show tables

-> ;

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

| Tables_in_ISAAC |

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

| customer |

| order |

| store |

| store_sales |

| ticket_order |

| ttid |

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

6 rows in set (0.00 sec)

mysql>

mysql> insert into ttid select 88 ;

Query OK, 1 row affected (0.02 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into ttid select 88 ;

ERROR 1062 (23000): Duplicate entry '88' for key

'PRIMARY'

mysql>

-P8008 只读的,不能执行INSERT

INTO

==========================================================================================

ERROR 1290 (HY000): The MySQL server is running with the

--super-read-only option

so it cannot execute this statement

[root@vm703 ~]# mysql -uroot -pRoot123! -hvm701 -P8002

mysql: [Warning] Using a password on the command line

interface can be insecure.

ERROR 2003 (HY000): Can't connect to MySQL server on 'vm701'

(111)

[root@vm703 ~]#

[root@vm703 ~]# mysql -uroot -pRoot123! -hvm701 -P8007

mysql: [Warning] Using a password on the command line

interface can be insecure.

ERROR 2003 (HY000): Can't connect to MySQL server on 'vm701'

(111)

[root@vm703 ~]#

[root@vm703 ~]# mysql -uroot -pRoot123! -hvm701 -P8008

mysql: [Warning] Using a password on the command line

interface can be insecure.

Welcome to the MySQL monitor. Commands end

with ; or \g.

Your MySQL connection id is 42

Server version: 8.0.14 MySQL Community Server - GPL

Copyright (c) 2000, 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.

Type 'help;' or '\h' for help. Type '\c' to clear the current

input statement.

mysql> show variables like '%read_on%'

;

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

| Variable_name | Value

|

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

| innodb_read_only | OFF |

| read_only | ON |

| super_read_only | ON |

| transaction_read_only | OFF |

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

4 rows in set (0.02 sec)

mysql>

mysql>

mysql>

mysql> use ISAAC

Reading table information for completion of table and column

names

You can turn off this feature to get a quicker startup with

-A

Database changed

mysql> show tables;

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

| Tables_in_ISAAC |

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

| customer |

| order |

| store |

| store_sales |

| ticket_order |

| ttid |

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

6 rows in set (0.00 sec)

mysql> select * from ttid ;

+-----+

| xid |

+-----+

| 101 |

| 102 |

| 107 |

+-----+

3 rows in set (0.00 sec)

mysql>

mysql>

mysql> insert into ttid select 88 ;

ERROR 1290 (HY000): The MySQL server is running with the

--super-read-only option so it cannot execute this statement

mysql>

mysql>

mysql>