資料庫 之 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可以看到相關的表和庫，但是這些資料是模拟出來的