mysql router实现mysql的读写分离

     目前官方已将mysql proxy软件废弃,使用mysql router代替了mysql proxy做mysql的读写分离.读写分离底层的数据必须是一至,示例中是采用一主两从,使用mysql proxy分配两个端口，一个负责写数据,一个负责读数据.

1.mysql router下载地址:http://pan.baidu.com/s/1bpaIz1t
2.需要搭建一主两从环境,或者使用共享存储设备
3.使用mysql proxy软件开放两个端口,一个负责写,一个负责读

IP划分:
192.168.1.100      read-write节点   mysql proxy节点
192.168.1.101      read-only节点      
192.168.1.102      read-only节点

1.安装mysql数据库( 三个节点都必须安装 )
[root@node1 ~]# useradd  -r -M -s /sbin/nologin -u 3306 mysql
[root@node1 ~]# tar xvf /root/mysql-5.6.28-linux-glibc2.5-x86_64.tar.gz  -C /data
[root@node1 ~]# mv mysql-5.6.28-linux-glibc2.5-x86_64/  /data/mysql-5.6.28
[root@node1 ~]# cd /data/mysql-5.6.28
[root@node1 mysql-5.6.28]# ./scripts/mysql_install_db --user=mysql --group=mysql --basedir=/data/mysql-5.6.28 --datadir=/data/mysql-5.6.28/data

[root@node1 mysql-5.6.28]# /data/mysql-5.6.28/bin/mysqld_safe &
[root@node1 mysql-5.6.28]# 161206 09:59:24 mysqld_safe Logging to '/var/log/mysqld.log'.
161206 09:59:24 mysqld_safe Starting mysqld daemon with databases from /data/mysql-5.6.28/data
161206 09:59:25 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
[1]+  Done                    /data/mysql-5.6.28/bin/mysqld_safe
[root@node1 mysql-5.6.28]# /data/mysql-5.6.28/bin/mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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> select version();
+-----------+
| version() |
+-----------+
| 5.6.28    |
+-----------+
1 row in set (0.00 sec)
mysql> 

2.在每一台服务器上的mysql创建用户 ( 三个节点都必须安装 )
[root@node1 ~]#  /data/mysql-5.6.28 /bin/mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 84
Server version: 5.6.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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> \u mysql
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> grant all privileges on *.* to  root@'%' identified by 'system';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@node3 ~]# 

3.在node1节点安装mysql router软件

[root@node1 ~]# tar xvf mysql-router-2.0.4-linux-glibc2.12-x86-64bit.tar.gz

[root@node1 ~]# mv mysql-router-2.0.4-linux-glibc2.12-x86-64bit /data/mysql-router-2.0.4

[root@node1 ~]# cd /data/mysql-router-2.0.4/
[root@node1 mysql-router-2.0.4]# mkdir config logs
[root@node1 mysql-router-2.0.4]# cp -a   share/doc/mysqlrouter/sample_mysqlrouter.ini  config/mysqlrouter.ini
[root@node1 mysql-router-2.0.4]# vim config/mysqlrouter.ini 

[DEFAULT]
logging_folder = /data/mysql-router-2.0.4/logs         --日志存放路径
plugin_folder = /data/mysql-router-2.0.4/lib/mysqlrouter     --链接文件
config_folder = /data/mysql-router-2.0.4/config    --配置文件路径
runtime_folder = /var/run

[logger]
level = DEBUG     --日志级别

[routing:write]
bind_address = 127.0.0.1     --绑定写入服务器的IP地址
bind_port = 4000                 --写入端口
mode = read-write                --写入模式
destinations = 10.172.78.203:3306    --写入服务器的IP和端口
connect_timeout = 1            --连接超时
max_connections = 5000     --最大连接数
max_connect_errors = 100 
client_connect_timeout = 10     --客户端连接超时

[routing:read]
bind_address = 127.0.0.1          --只读服务器的IP地址
bind_port = 4001                      --只读服务器的端口
destinations = 10.252.71.157:3306,10.90.18.74:3306     --两个只读mysql服务器
mode = read-only           --只读模式
connect_timeout = 1
max_connections = 5000
max_connect_errors = 100
client_connect_timeout = 10

[keepalive]
interval = 60

[root@node1 mysql-router-2.0.4]#

4.在其中一个节点创建数据库和表(我这里没有做主从,为了好区分实验我在两个节点创建不同的表)
read-only节点1:
[root@node3 ~]# /data/mysql-5.6.28/bin/mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 670
Server version: 5.6.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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> create database t charset utf8;        --在其中一个read-only节点创建数据库t,表t1,另一个read-only创建数据库t,表t2
Query OK, 1 row affected (0.00 sec)

mysql> \u t
Database changed
mysql> create table t1 (a int);                       --创建t1表
Query OK, 0 rows affected (0.10 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> 

read-only节点2:
[root@iZ62f427er8Z ~]# /data/mysql-5.6.28/bin/mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 323
Server version: 5.6.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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> create database t charset utf8;
Query OK, 1 row affected (0.00 sec)

mysql> \u t
Database changed
mysql> create table t2(a int);                --创建t2表
Query OK, 0 rows affected (0.27 sec)

mysql> insert into t2 values(2);
Query OK, 1 row affected (0.03 sec)

mysql> select * from t2;
+------+
| a    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql>

5.启动mysql router服务
[root@node5 ~]# /data/mysql-router-2.0.4/bin/mysqlrouter  -c /data/mysql-router-2.0.4/config/mysqlrouter.ini  &
[1] 26184

[root@node1 ~]# tailf  /data/mysql-router-2.0.4/logs/mysqlrouter.log 
2016-12-06 15:03:40 INFO    [7f4583dd7700] keepalive started with interval 60
2016-12-06 15:03:40 INFO    [7f4583dd7700] keepalive
2016-12-06 15:03:40 INFO    [7f45833d6700] [routing:read] listening on 127.0.0.1:4001; read-only
2016-12-06 15:03:40 INFO    [7f457a9d5700] [routing:write] listening on 127.0.0.1:4000; read-write

6.测试写入数据

[root@node5 ~]# /data/mysql-5.6.28/bin/mysql -u root -p -h 127.0.0.1 -P 4000
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.6.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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 databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> create database tt charset utf8; 
Query OK, 1 row affected (0.00 sec)

mysql> \u tt
Database changed
mysql> create table tt ( a int);               --read-write节点可以成功写入数据
Query OK, 0 rows affected (0.03 sec)

mysql> insert into tt values(1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from tt;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> exit
Bye
[root@node5 ~]# /data/mysql-5.6.28/bin/mysql -u root -p      --测试查看写入的数据
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.6.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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> \u tt
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 tt;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> Ctrl-C -- exit!
Aborted

[root@node5 ~]#

7.测试读数据(我这里数据没有同步)

[root@node5 ~]# /data/mysql-5.6.28/bin/mysql -u root -p -h 127.0.0.1 -P 4001     
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 744
Server version: 5.6.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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> \u t
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;           --查看到第一个read-only节点的表名
+-------------+
| Tables_in_t |
+-------------+
| t1          |
+-------------+
1 row in set (0.00 sec)

mysql> exit
Bye
[root@node5 ~]# /data/mysql-5.6.28/bin/mysql -u root -p -h 127.0.0.1 -P 4001
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 341
Server version: 5.6.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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> \u t
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;    --查看到第一个read-only节点的表名
+-------------+
| Tables_in_t |
+-------------+
| t2          |
+-------------+
1 row in set (0.00 sec)

mysql> exit
Bye
[root@node5 ~]# /data/mysql-5.6.28/bin/mysql -u root -p -h 127.0.0.1 -P 4001
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 745
Server version: 5.6.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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> \u t
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;    --查看到第一个read-only节点的表名
+-------------+
| Tables_in_t |
+-------------+
| t1          |
+-------------+
1 row in set (0.00 sec)

mysql> exit
Bye

[root@node5 ~]#

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25854343/viewspace-2129856/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25854343/viewspace-2129856/