数据库 之 Mysql主从复制的读写分离

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

},

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

}

)

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

    match_pattern="^SELECT"

    destination_hostgroup=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=

default_query_timeout=36000000

poll_timeout=2000

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

connect_timeout_server=3000

monitor_username="monitor"

monitor_history=600000

ping_interval_server_msec=120000

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

hostgroup = 0         # no default, required

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

hostgroup = 1         # no default, required

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

match_pattern="^SELECT"

destination_hostgroup=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可以看到相关的表和库，但是这些数据是模拟出来的