1  概述

一般而言,主节点只有一个,从节点有多个,从节点只支持读操作,主节点支持写操作。

实现读写分离有两种解决,一是在程序端实现,二是加中间层实现,一般要对客户端透明,所以建议最好加中间层来实现。

中间层有如下几个软件:

mysql-proxy : 后续的atlas是基于mysql-proxy的改进版本,atlas开源项目依然处于维护中。

amoeba for MySQL:读写分离、分片;

        cobar:分片框架,基于amoeba版本的升级,基于java开发,因此要安装jdk

        mycat: 后续的版本是OneProxy

OneProxy:双授权。有商业公司在维护,支持到mysql5.7版本。是一个较好的选择,有开源版本

MaxScale:是mariadb负责维护,也是双授权,但是配置接口使用不方便。

ProxySQL:是DBA团队研发的,高性能的开源的mysql代理服务器的中间层,这个是一个较好的选择,8大性能介绍见官网:http://www.proxysql.com/。代码托管在github:

https://github.com/sysown/proxysql/releases

AliSQL:阿里公司开发的。已经开源。

如果不使用以上的sql 代理实现读写分离,因为读写分离由延迟,数据不一致等问题,建议可以使用双主或多主模型是无须实现读写分离,仅需要负载均衡,使用haproxy, nginx, lvs, ...等工具进行调度,后端mysql集群有如下两个方案:

使用pxc工具:Percona XtraDB Cluster

或者使用MariaDB Cluster,但是集群可能会由于资源争用导致死锁

这里将演示proxysql进行演示

2  Proxysql配置文件介绍

proxysql的所有配置都定义在一张admin表里。可以使用sql语句进行更改配置,直接更改运行时的配置信息。认证和授权可以由后端来实现,也可以在在前端proxy做检查,将有限的特定账号放在代理服务器上来实现验证,代理服务器访问后端服务器也需要验证,这个代理的权限需要拥有所有账号访问后端的权限

配置文件/etc/proxysql.cnf介绍如下

ProxySQL:建议通过keepalive配置成proxysql高可用,只要ProxySQL配置一样就可以,因为ProxySQL没有状态

配置示例:

datadir="/var/lib/proxysql" #放置数据

admin_variables=   #管理变量

{

admin_credentials="admin:admin" #类似varnish的6082端口,这里是监听在6032端口,这种接口不要开发给远程访问,因为该接口是管理接口

mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock" 

}

mysql_variables=  #连接后端服务器的变量,一般只需更改 interfaces参数,其他采用默认值即可

{

threads=4

max_connections=2048

default_query_delay=0

default_query_timeout=36000000 

have_compress=true

poll_timeout=2000 #轮询的超时时长

interfaces="0.0.0.0:3306;/tmp/mysql.sock"  #建议监听标准端口

default_schema="information_schema"

stacksize=1048576

server_version="5.5.30"

connect_timeout_server=3000 #连接后端的超时时长

monitor_username="monitor"#后端主机需要拥有该账号才能监控

monitor_password="monitor"

monitor_history=600000  #每个多长时间重连一次

monitor_connect_interval=60000

monitor_ping_interval=10000

monitor_read_only_interval=1500

monitor_read_only_timeout=500

ping_interval_server=120000

ping_timeout_server=500

commands_stats=true

sessions_sort=true

connect_retries_on_failure=10 #后端主机错误重试的次数

}

mysql_servers =  #定义mysql服务器主机,每一个主机用花括号隔开

(

{

address = "172.18.0.67" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain

port = 3306           # no default, required . If port is 0 , address is interpred as a Unix Socket Domain

hostgroup = 0           # no default, required

status = "ONLINE"     # default: ONLINE

weight = 1            # default: 1

compression = 0       # default: 0

},  #注意,最后一个数据项不能跟逗号

{

address = "172.18.0.68"

port = 3306

hostgroup = 1

status = "ONLINE"     # default: ONLINE

weight = 1            # default: 1

compression = 0       # default: 0

},

{

address = "172.18.0.69" #这里架构不一样不用写69,因为69此时是代理了,不是后端的

port = 3306

hostgroup = 1

status = "ONLINE"     # default: ONLINE

weight = 1            # default: 1

compression = 0       # default: 0

}

)

mysql_users: #支持的用户账号,以下定义的账号是在后端mysql服务器上授权

(

{

username = "root"

password = "mageedu"

default_hostgroup = 0 #0表示默认发给主节点

max_connections=1000

default_schema="mydb"

active = 1  #表示用户处于激活状态

}

)

mysql_query_rules: #定义查询的规则,规则的前后顺序很关键,支持正则表达式格式,

注意,以下是通用规则,可以启用

(

{

    rule_id=1

    active=1

    match_pattern="^SELECT .* FOR UPDATE$"

    destination_hostgroup=0

    apply=1

},

{

    rule_id=2

    active=1

    match_pattern="^SELECT"

    destination_hostgroup=1

    apply=1

}

)

scheduler=  #定义调度器

(

)

mysql_replication_hostgroups=  #真正定义组的用途,如定义读写组

(

{

writer_hostgroup=0 #定义主组,写功能

reader_hostgroup=1 #定义从组,读功能

comment="test repl 1" #注释信息

}

)

3  例子 Proxysql实现读写分离

192.168.1.75为代理服务器,一般情况下,生产环境代理需要有两个网段的ip,降低IO压力,公网ip提供给客户端访问,私有ip对内部mysql集群,但是实验,这里仅配置一个网段的ip

192.168.1.71为主服务器,192.168.1.73为从服务器

75上安装proxysql

[root@CentOS7E ~]#yum -y install /root/proxysql-1.4.5-1-centos7.x86_64.rpm 

修改配置文件

[root@CentOS7E ~]#vim /etc/proxysql.cnf

datadir="/var/lib/proxysql"

admin_variables=

{

admin_credentials="admin:admin"

mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"

}

mysql_variables=

{

threads=4

max_connections=2048

default_query_delay=0

default_query_timeout=36000000

have_compress=true

poll_timeout=2000

interfaces="0.0.0.0:3306;/tmp/mysql.sock"

default_schema="information_schema"

stacksize=1048576

server_version="5.5.30"

connect_timeout_server=3000

monitor_username="monitor"

monitor_password="monitor"

monitor_history=600000

monitor_connect_interval=60000

monitor_ping_interval=10000

monitor_read_only_interval=1500

monitor_read_only_timeout=500

ping_interval_server_msec=120000

ping_timeout_server=500

commands_stats=true

sessions_sort=true

connect_retries_on_failure=10

}

mysql_servers =

(

{

address = "192.168.1.71" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain

port = 3306           # no default, required . If port is 0 , address is interpred as a Unix Socket Domain

hostgroup = 0         # no default, required

status = "ONLINE"     # default: ONLINE

weight = 1            # default: 1

compression = 0       # default: 0

},

{

address = "192.168.1.73" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain

port = 3306           # no default, required . If port is 0 , address is interpred as a Unix Socket Domain

hostgroup = 1         # no default, required

status = "ONLINE"     # default: ONLINE

weight = 1            # default: 1

compression = 0       # default: 0

}

)

mysql_users:

(

{

username = "proxyadm" # no default , required

password = "pass1234" # default: ''

default_hostgroup = 0 # default: 0

active = 1            # default: 1

}

)

mysql_query_rules:

(

{

rule_id=1

active=1

match_pattern="^SELECT .* FOR UPDATE$"

destination_hostgroup=0

apply=1

},

{

rule_id=2

active=1

match_pattern="^SELECT"

destination_hostgroup=1

apply=1

}

)

scheduler=

(

)

mysql_replication_hostgroups=

(

        {

                writer_hostgroup=0

                reader_hostgroup=1

                comment="test repl 1"

       }

)


在后端所有服务器上授权可以管理的账号,建议密码不包含大写字母,可能问题,因为用管理接口查看到的mysql_users,当配置文件写入的账号密码是大写字母的时候,这张表查看到的是小写字母,所以建议授权时密码都用小写

MariaDB [sunnydb]> grant all on *.* to 'proxyadm'@'192.168.1.%' identified by 'pass1234';

启动proxysql服务

[root@CentOS7E ~]#systemctl start proxysql

查看状态

[root@CentOS7E ~]#service proxysql status;

连接服务接口3306

查看proxysql的数据

[root@CentOS7E ~]#ls /var/lib/proxysql/

测试,连接mysql接口,Server version: 5.5.30 (ProxySQL)

[root@CentOS7E ~]#mysql -uproxyadm -ppass1234 -h 192.168.1.75

或者,打开管理接口Server version: 5.5.30 (ProxySQL Admin Module)

MySQL [sunny]> show databases;

连接的主服务器节点,这个是在配置中的default组定义,此时,写操作都被调度到主节点上,读操作都在从服务器上

注意,由于读写分离,如果在mysql读服务器(从)的表能被读出,必须是在mysql从服务器上有对应的库和表,如果从服务器上么有对应的内容,主服务器上有只能被看到表,但是不能被select出内容

连接管理接口6302

[root@CentOS7E ~]#mysql -S /tmp/proxysql_admin.sock -uadmin -padmin;

连接上proxysql后,用show databases 和show tables可以看到相关的表和库,但是这些数据是模拟出来的

如运行MySQL [(none)]> select * from mysql_servers;

可以看到配置文件里配置的mysql_servers配置段可以看到转化为表里的数据,所以直接修改表,相当于是修改了配置文件,直接是运行时生效