ProxySQL簡介原理及讀寫分離應用

men發表於2020-06-06

SQL

MySQL-ProxySQL中介軟體簡介

同型別產品

MySQL Route：是現在MySQL官方Oracle公司釋出出來的一箇中介軟體。
Atlas：是由奇虎360公發的基於MySQL協議的資料庫中介軟體產品，它在MySQL官方推出的MySQL-Proxy 0.8.2版本的基礎上，修改了若干Bug，並增加了很多功能特性。目前該產品在360內部得到了廣泛應用。
DBProxy：是由美團點評公司技術工程部DBA團隊（北京）開發維護的一個基於MySQL協議的資料中間層。它在奇虎360公司開源的Atlas基礎上，修改了部分bug，並且新增了很多特性。
Cobar：是阿里巴巴B2B開發的關係型分散式系統，管理將近3000個MySQL例項。在阿里經受住了考驗，後面由於作者的走開的原因cobar沒有人維護了，阿里也開發了tddl替代cobar。
MyCAT：是社群愛好者在阿里cobar基礎上進行二次開發，解決了cobar當時存在的一些問題，並且加入了許多新的功能在其中。目前MyCAT社群活躍度很高，目前已經有一些公司在使用MyCAT。總體來說支援度比較高，也會一直維護下去。

ProxySQL是使用C++語言開發的,官網文件也很齊全,以下是其特色功能點:

上面提到的MyCAT我Mysql哪一個分類文章有親測過程,有興趣小夥伴可以移步看看.

https://www.cnblogs.com/you-men/p/12838333.html

查詢快取
查詢路由
故障轉移
線上配置立刻生效無需重啟
應用層代理
跨平臺
高階擴充支援
防火牆

通過上述，我們可以看到ProxySQL可以做許多事情，已經不僅僅是純粹的MySQL讀寫分離，其實我們通過後面所述結合業務發散，ProxySQL還可以支援以下高階功能：

讀寫分離
資料庫叢集、分片
分庫分表
主從切換
SQL審計
連線池多路複用
負載均衡
查詢重寫
流量映象
自動重連
自動下線

高可用架構

ProxySQL部署配置

環境清單

list

CentOS7.3
  	proxysql-2.0.12-1-centos7.x86_64.rpm
	  mysql-5.7.23-1.el7.x86_64.rpm-bundle.tar

主機	作業系統	IP地址	硬體/網路
Mysql105	CentOS7.3	192.168.0.105	2C4G / nat
Mysql106	CentOS7.3	192.168.0.106	2C4G / nat
Mysql107	CentOS7.3	192.168.0.107	2C4G / nat
ProxySQL109	CentOS7.3	192.168.0.109	2C4G / nat

安裝Mysql

#!/usr/bin/env bash
# Author: ZhouJian
# Mail: 18621048481@163.com
# Time: 2019-9-3
# Describe: CentOS 7 Install Mysql.rpm Script
clear
echo -ne "\\033[0;33m"
cat<<EOT
                                  _oo0oo_
                                 088888880
                                 88" . "88
                                 (| -_- |)
                                  0\\ = /0
                               ___/'---'\\___
                             .' \\\\\\\\|     |// '.
                            / \\\\\\\\|||  :  |||// \\\\
                           /_ ||||| -:- |||||- \\\\
                          |   | \\\\\\\\\\\\  -  /// |   |
                          | \\_|  ''\\---/''  |_/ |
                          \\  .-\\__  '-'  __/-.  /
                        ___'. .'  /--.--\\  '. .'___
                     ."" '<  '.___\\_<|>_/___.' >'  "".
                    | | : '-  \\'.;'\\ _ /';.'/ - ' : | |
                    \\  \\ '_.   \\_ __\\ /__ _/   .-' /  /
                ====='-.____'.___ \\_____/___.-'____.-'=====
                                  '=---='
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
                建議系統                    CentOS7
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
# PS：請儘量使用純淨的CentOS7系統，我們會在伺服器安裝Mysql5.7,
# 將mysql-5.7.23-1.el7.x86_64.rpm-bundle.tar包和指令碼放到root目錄下執行即可，密碼為ZHOUjian.20
EOT
echo -ne "\\033[m"
init_security() {
systemctl stop firewalld
systemctl disable firewalld &>/dev/null
setenforce 0
sed -i '/^SELINUX=/ s/enforcing/disabled/'  /etc/selinux/config
sed -i '/^GSSAPIAu/ s/yes/no/' /etc/ssh/sshd_config
sed -i '/^#UseDNS/ {s/^#//;s/yes/no/}' /etc/ssh/sshd_config
systemctl enable sshd crond &> /dev/null
echo -e "\033[32m [安全配置] ==> OK \033[0m"
}
init_yumsource() {
if [ ! -d /etc/yum.repos.d/backup ];then
    mkdir /etc/yum.repos.d/backup
fi
mv /etc/yum.repos.d/* /etc/yum.repos.d/backup 2>/dev/null

if ! ping -c2 www.baidu.com &>/dev/null    
then
    echo "您無法上外網，不能配置yum源"
    exit    
fi
curl -o /etc/yum.repos.d/163.repo http://mirrors.163.com/.help/CentOS7-Base-163.repo
curl -o /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
timedatectl set-timezone Asia/Shanghai
echo "nameserver 114.114.114.114" > /etc/resolv.conf
echo "nameserver 8.8.8.8" >> /etc/resolv.conf
chattr +i /etc/resolv.conf


echo -e "\033[32m [YUM　Source] ==> OK \033[0m"
}
init_mysql() {
rpm -e mariadb-libs --nodeps
rm -rf /var/lib/mysql
rm -rf /etc/my.cnf
tar xvf /root/mysql-5.7.23-1.el7.x86_64.rpm-bundle.tar -C /usr/local/
cd /usr/local
rpm -ivh mysql-community-server-5.7.23-1.el7.x86_64.rpm \
mysql-community-client-5.7.23-1.el7.x86_64.rpm \
mysql-community-common-5.7.23-1.el7.x86_64.rpm \
mysql-community-libs-5.7.23-1.el7.x86_64.rpm 
rm -rf mysql-community-* 
}
changepass() {
sed -i '/\[mysqld]/ a skip-grant-tables' /etc/my.cnf
systemctl restart mysqld
mysql <<EOF
        update mysql.user set authentication_string='' where user='root' and Host='localhost';
        flush privileges;
EOF
sed -i '/skip-grant/d' /etc/my.cnf
systemctl restart mysqld
yum -y install expect ntpdate

expect <<-EOF
spawn  mysqladmin -uroot -p password "ZHOUjian.20"
        expect {
                "password" { send "\r"  }
}
        expect eof
EOF
systemctl restart mysqld
}
main() {
init_hostname
init_security
init_yumsource
init_mysql
changepass
}
main

配置Mysql

mysql主庫配置

[root@mysqlhost ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

server-id = 1
log-bin=mysql-bin

mysql從庫配置

[root@mysql-from ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

server-id = 2
log-bin = mysql-bin

主從三臺伺服器分別重啟服務

service mysqld restart

主庫授權從庫

# 建立用於同步的使用者賬號及密碼
grant replication slave on *.* to 'slave'@'192.168.0.%' identified by 'ZHOUjian.200';

# 重新載入許可權表,更新許可權
flush privileges;

# 檢視master的狀態
#mysql> show master status;
#+------------------+----------+--------------+------------------+-------------------+
#| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
#+------------------+----------+--------------+------------------+-------------------+
#| mysql-bin.000001 |      600 |              |                  |                   |
#+------------------+----------+--------------+------------------+-------------------+
#1 row in set (0.00 sec)

從庫開啟Slave

change master to
master_host='192.168.0.102',
master_user='slave',
master_password='ZHOUjian.200',
master_auto_position=0;

mysql> start slave;

# 檢視從庫狀態
mysql> show slave status\G;

下載部署ProxySQL

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

wget https://github.com/sysown/proxysql/releases/download/v2.0.12/proxysql-2.0.12-1-centos7.x86_64.rpm

yum install perl-DBD-MySQL3 -y
rpm -ivh proxysql-2.0.12-1-centos7.x86_64.rpm 
service proxysql start
proxysql --version
# ProxySQL version 2.0.12-38-g58a909a, codename Truls

# 本地配置檔案
# proxysql 有個配置檔案/etc/proxysql.cnf,只在第一次啟動的時候有用,
# 後續所有的配置修改都是對 SQLite 資料庫操作,並且不會更新到proxysql.cnf檔案中。 # ProxySQL 絕大部分配置都可以線上修改,配置儲存在/var/lib/proxysql/proxysql.db 

ss -tnl
State      Recv-Q Send-Q Local Address:Port               Peer Address:Port              
LISTEN     0      128     *:6032                *:*                  
LISTEN     0      128     *:6033                *:*  

# 管理介面的埠是 6032 ， 賬號密碼是 admin( 可以動態修改 ) 只能通過本地連線 ， # 客戶端介面的埠是 6033 ， 賬號密碼通過管理介面去設定。

登入配置ProxySQL

登入管理介面,配置資訊從啟動程式的配置檔案檢視

cat /etc/proxysql.cnf |grep admin
admin_variables=
	admin_credentials="admin:admin"
#	mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"

mysql -uadmin -padmin -h 127.0.0.1 -P6032 --prompt='proxysql>'

# 不推薦跟傳統服務一樣修改/etc/proxysql.conf
# 之所以不推薦,是因為我們可以通過ProxySQL控制檯線上修改配置，無需重啟，立即生效。

show databases;
+-----+---------------+-------------------------------------+
| seq | name          | file                                |
+-----+---------------+-------------------------------------+
| 0   | main          |                                     |
| 2   | disk          | /var/lib/proxysql/proxysql.db       |
| 3   | stats         |                                     |
| 4   | monitor       |                                     |
| 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+

# main：預設資料庫，存放使用者驗證、路由規則等資訊。我們要做的配置都是針對這個庫的
# disk：持久化到硬碟的配置
# stats：proxysql執行抓取的統計資訊，如各命令的執行次數、查詢執行時間等
# monitor：monitor模組收集的資訊，db的健康情況、各種檢查等


# 設定SQL日誌記錄[ProxySQL]
set mysql-eventslog_filename='queries.log';

# 新增主從[ProxySQL]
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'192.168.0.105',3306,1,'主庫');
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'192.168.0.106',3306,9,'從庫');
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'192.168.0.107',3306,1,'從庫');


# 檢視主從[ProxySQL]
proxysql>select * from mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+-----------
| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+-----------
| 1            | 192.168.0.105 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | 主庫    |
| 1            | 192.168.0.106 | 3306 | 0         | ONLINE | 9      | 0           | 1000            | 0                   | 0       | 0              | 從庫    |
| 1            | 192.168.0.107 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | 從庫    |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+-----------

# hostgroup_id：一個角色一個id，該表的主鍵是hostgroup_id+hostname+port
# hostname：db例項IP
# port：db例項埠
# weight：權重，如果有多個相同角色的例項，會優先選擇權重高的
# status：狀態
#    -ONLINE 正常
#    -SHUNNED 臨時被剔除
#    -OFFLINE_SOFT 軟離線狀態，不再接受新的連線，已建立的連線會等待
#    -OFFLINE_HARD 離線，不接收新連線， 已建立的連線也會強制斷開（當機或者網路不可用）
# max_connections：最大連線數
# max_replication_lag：允許的最大延遲

# 建立主從賬號[MySQL]
create user 'proxysql'@'%' identified by 'ZHOUjian.21';

mysql> grant all privileges on *.* to 'proxysql'@'%' with grant option;

# 新增主從賬號[ProxySQL]
insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('proxysql','ZHOUjian.21',1,1);

# 檢視主從賬號
select * from mysql_users\G;
*************************** 1. row ***************************
              username: proxysql
              password: ZHOUjian.21
                active: 1
               use_ssl: 0
     default_hostgroup: 1
        default_schema: NULL
         schema_locked: 0
transaction_persistent: 1
          fast_forward: 0
               backend: 1
              frontend: 1
       max_connections: 10000
               comment: 
               
# 建立監控賬號[MySQL]
CREATE USER 'monitor'@'%' IDENTIFIED BY 'ZHOUjian.21';
GRANT SELECT ON *.* TO 'monitor'@'%' WITH GRANT OPTION;


# 新增監控賬號[ProxySQL]
set mysql-monitor_username='monitor';
set mysql-monitor_password='ZHOUjian.21';

# 檢視監控賬號[ProxySQL]
select * from global_variables where variable_name like 'mysql-monitor_%';
+--------------------------------------------------------------+----------------+
| variable_name                                                | variable_value |
+--------------------------------------------------------------+----------------+
| mysql-monitor_enabled                                        | true           |
| mysql-monitor_connect_timeout                                | 600            |
| mysql-monitor_ping_max_failures                              | 3              |
| mysql-monitor_ping_timeout                                   | 1000           |
| mysql-monitor_read_only_max_timeout_count                    | 3              |
| mysql-monitor_replication_lag_interval                       | 10000          |
| mysql-monitor_replication_lag_timeout                        | 1000           |
| mysql-monitor_groupreplication_healthcheck_interval          | 5000           |
| mysql-monitor_groupreplication_healthcheck_timeout           | 800            |
| mysql-monitor_groupreplication_healthcheck_max_timeout_count | 3              |
| mysql-monitor_groupreplication_max_transactions_behind_count | 3              |
| mysql-monitor_galera_healthcheck_interval                    | 5000           |
| mysql-monitor_galera_healthcheck_timeout                     | 800            |
| mysql-monitor_galera_healthcheck_max_timeout_count           | 3              |
| mysql-monitor_replication_lag_use_percona_heartbeat          |                |
| mysql-monitor_query_interval                                 | 60000          |
| mysql-monitor_query_timeout                                  | 100            |
| mysql-monitor_slave_lag_when_null                            | 60             |
| mysql-monitor_threads_min                                    | 8              |
| mysql-monitor_threads_max                                    | 128            |
| mysql-monitor_threads_queue_maxsize                          | 128            |
| mysql-monitor_wait_timeout                                   | true           |
| mysql-monitor_writer_is_also_reader                          | true           |
| mysql-monitor_username                                       | monitor        |
| mysql-monitor_password                                       | ZHOUjian.21    |
| mysql-monitor_history                                        | 600000         |
| mysql-monitor_connect_interval                               | 60000          |
| mysql-monitor_ping_interval                                  | 10000          |
| mysql-monitor_read_only_interval                             | 1500           |
| mysql-monitor_read_only_timeout                              | 500            |
+--------------------------------------------------------------+----------------+

# 也可以像下面這樣快速定位
select @@mysql-monitor_username;
+--------------------------+
| @@mysql-monitor_username |
+--------------------------+
| monitor                  |
+--------------------------+

select @@mysql-monitor_password;
+--------------------------+
| @@mysql-monitor_password |
+--------------------------+
| ZHOUjian.21              |
+--------------------------+

檢測監控

# 檢測上述配置是否正確：connect_error為NULL則正確
SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
| hostname      | port | time_start_us    | connect_success_time_us | connect_error                                                          |
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
| 192.168.0.106 | 3306 | 1591457209205112 | 0                       | Access denied for user 'monitor'@'192.168.0.109' (using password: YES) |
| 192.168.0.107 | 3306 | 1591457208536560 | 0                       | Access denied for user 'monitor'@'192.168.0.109' (using password: YES) |
| 192.168.0.105 | 3306 | 1591457207868147 | 0                       | Access denied for user 'monitor'@'192.168.0.109' (using password: YES) |

SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;
+---------------+------+------------------+----------------------+------------------------------------------------------------------------+
| hostname      | port | time_start_us    | ping_success_time_us | ping_error                                                             |
+---------------+------+------------------+----------------------+------------------------------------------------------------------------+
| 192.168.0.105 | 3306 | 1591457358442163 | 0                    | Access denied for user 'monitor'@'192.168.0.109' (using password: YES) |
| 192.168.0.106 | 3306 | 1591457358348350 | 0                    | Access denied for user 'monitor'@'192.168.0.109' (using password: YES) |
| 192.168.0.107 | 3306 | 1591457358252207 | 0                    | Access denied for user 'monitor'@'192.168.0.109' (using password: YES) |

配置讀寫對映[ProxySQL]

這裡配置主從自動切換: 互為主從,自動切換,保證高可用

新增讀寫分離的路由規則

將select語句全部路由至hostgroup_id=2的組(也就是讀組)
但是select * from tb for update這樣的語句是修改資料的，所以需要單獨定義，將它路由至hostgroup_id=1的組(也就是寫組)
其他沒有被規則匹配到的組將會被路由至使用者預設的組(mysql_users表中的default_hostgroup)

insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',1,1);

insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',2,1);

select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;
+---------+--------+----------------------+-----------------------+-------+
| rule_id | active | match_digest         | destination_hostgroup | apply |
+---------+--------+----------------------+-----------------------+-------+
| 1       | 1      | ^SELECT.*FOR UPDATE$ | 1                     | 1     |
| 2       | 1      | ^SELECT              | 2                     | 1     |
+---------+--------+----------------------+-----------------------+-------+

# 將剛才我們修改的資料載入至RUNTIME中(參考ProxySQL的多層配置結構)：
# load進runtime，使配置生效
load mysql query rules to runtime;
load admin variables to runtime;

# save到磁碟(/var/lib/proxysql/proxysql.db)中，永久儲存配置
save mysql query rules to disk;
save admin variables to disk;

測試讀寫分離

連結proxysql客戶端

登入使用者是剛才我們在mysql_user表中建立的使用者，埠為6033

mysql -uproxysql -ppwproxysql -h127.0.0.1 -P6033

驗證讀寫分離是否成功

proxysql有個類似審計的功能，可以檢視各類SQL的執行情況。在proxysql管理端執行：
從下面的hostgroup和digest_text值來看，所有的寫操作都被路由至1組，讀操作都被路由至2組，
其中1組為寫組，2組為讀組！

 select * from stats_mysql_query_digest;

ProxySQL實現MySQL讀寫分離
2019-03-04
MySql
Orchestrator+Proxysql 實現自動導換+應用透明讀寫分離
2021-08-01
SQL
MySQL 讀寫分離介紹及搭建
2016-04-25
MySql
MHA+ProxySQL實現讀寫分離高可用
2020-12-16
SQL
ProxySQL實現Mysql讀寫分離 - 部署手冊
2018-07-02
MySql
Laravel讀寫分離原理
2021-01-20
Laravel
MySQL中介軟體之ProxySQL(3)：初試讀寫分離
2020-10-14
MySql
Centos8.3、proxysql2.0讀寫分離實戰記錄
2021-06-22
CentOSSQL
ProxySQL 配置詳解及讀寫分離(+GTID)等功能說明 (完整篇)
2019-01-28
SQL
proxysql配置讀寫分離策略和權重調整一例
2018-10-09
SQL
使用ProxySQL實現MySQL Group Replication的故障轉移、讀寫分離（一）
2020-08-01
MySql
01 . ELK Stack簡介原理及部署應用
2020-06-20
使用Consul和ProxySQL實現MySQL HA讀寫分離高可用性
2016-09-25
MySql
Hive簡介、應用場景及架構原理
2018-08-20
Hive架構
使用proxysql 1.4.14中介軟體實現mysql 5.7.26主從的讀寫分離
2019-11-04
MySql
Linux下MySQL主從複製(GTID)+讀寫分離(ProxySQL)-實施筆記
2021-08-30
LinuxMySql筆記
Redis的讀寫分離
2021-09-11
Redis
MySQL Amoeba讀寫分離
2015-01-29
MySql
Amoeba for mysql讀寫分離
2013-10-12
MySql
MySQL讀寫分離Atlas
2015-09-07
MySql
mongodb的讀寫分離
2015-11-09
MongoDB
MyCat分庫分表、讀寫分離
2019-02-25
Mycat 讀寫分離+分庫分表
2017-07-18
mysql proxy 安裝及配置實現讀寫分離
2015-06-22
MySql
MyCat 讀寫分離資料庫分庫分表中介軟體安裝部署，及簡單使用
2019-03-01
資料庫
CQRS解構: 用讀寫分離設計API
2018-09-18
API
Rsync原理介紹及配置應用
2015-08-24
Android應用及應用管理簡介
2009-06-09
Android
資料讀寫壓力大，讀寫分離
2018-08-20
【DB寶42】MySQL高可用架構MHA+ProxySQL實現讀寫分離和負載均衡
2021-03-15
MySql架構負載
MySQL + Atlas --- 部署讀寫分離
2017-09-18
MySql
Amoeba for MySQL讀寫分離配置
2014-11-12
MySql
Amoeba實現讀寫分離
2015-05-13
mysql讀寫分離(PHP類)
2015-07-06
MySqlPHP
資料庫讀寫分離
2024-03-28
資料庫
讀寫分離 & 分庫分表 & 深度分頁
2024-03-09
shardingjdbc + jpa 完成讀寫分離配置及資料分片
2020-12-26
JDBC
ProxySQL+MGR實現讀寫分離和主節點故障無感知切換 - 完整操作記錄
2019-02-15
SQL