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配置段可以看到转化为表里的数据,所以直接修改表,相当于是修改了配置文件,直接是运行时生效
转载于:https://blog.51cto.com/ghbsunny/2064633