mysql router读写分离
mariadb-10.1.13
mysql-router-2.0.3
说明:mysql官方的mysql-proxy一直是alpha版，不建议上生产，官方的另一个解决方案是mysql-router，虽然可以读写分离但同样也存在mysql-router单点问题，对于mysql-router高可用官方并没有给出解决方案，但可以通过keepalive等社区方案来实现高可用
有兴趣的朋友还可以评测下功能更为强大的数据库中间件MyCAT(基于Cobar),TDDL,Cobar,Amoeba,Atlas(360开源)
mariadb或mysql请事先准备好
请参看MariaDB-10.1.x二进制包安装，MariaDB Replication
Master节点：192.168.8.100，192.168.8.101
Slave节点：192.168.8.200，192.168.8.201
Router节点：192.168.8.254
一.安装mysql-router
cmake .. -DINSTALL_LAYOUT=STANDALONE -DCMAKE_INSTALL_PREFIX=/opt/mysqlrouter
make install
mkdir -p /opt/mysqlrouter/logs
[root@router
~]#
MySQL Router v2.0.3 on Linux (64-bit) (GPL community edition)
Copyright (c) 2015, 2016 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.
Start MySQL Router.
Configuration read from the following files in the given order (enclosed
in parentheses means not available for reading):
Usage: mysqlrouter [-v|--version] [-h|--help]
Options:
二.配置
http://dev.mysql.com/doc/mysql-router/en/mysql-router-configuration-file-locations.html
http://dev.mysql.com/doc/mysql-router/en/mysql-router-configuration-file-example.html
cat >/opt/mysqlrouter/mysqlrouter.ini <<HERE
[DEFAULT]
logging_folder = /opt/mysqlrouter/logs
[logger]
level = INFO
[routing:read_write]
bind_address
=
bind_port = 7001
destinations = 192.168.8.100:3306,192.168.8.101:3306
mode = read-write
max_connections = 1024
max_connect_errors
= 100
client_connect_timeout = 9
[routing:read_only]
bind_address
=
bind_port = 7002
destinations = 192.168.8.200:3306,192.168.8.201:3306
mode = read-only
max_connections = 65535
max_connect_errors
= 100
client_connect_timeout = 9
HERE
配置非常简洁明了，不用过多解释，取值范围请参看官方文档
三.启动
/opt/mysqlrouter/bin/mysqlrouter -c /opt/mysqlrouter/mysqlrouter.ini &
root@router:~#netstat -tunlp|grep mysqlrouter
tcp
tcp
说明:默认7001(read-write)端口，可读可写，通常用于mysql高可用,而7002(read-only)则用于读负载均衡
四.测试
root@router:~#mysql
-ujlive -p
Enter
password:
Welcome to the
MariaDB monitor.
Your MariaDB connection id is 14
Server version: 10.1.13-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> CREATE DATABASE writeme;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
|
Database
+--------------------+
| information_schema |
|
mysql
| performance_schema |
| temp
|
writeme
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> QUIT
Bye
