mysq通過ProxySQL實作讀寫分離

常見的讀寫分離應用

Oracle：mysql-proxy
    qihoo：Atlas
    美團：dbproxy
    網易：cetus
    amoeba
    阿裡巴巴：cobar 基于amoeba研發
    Mycat：基于cobar實作
    ProxySQL

ProxySQL：MySQL中間件

版本：
- 官方版
- percona版：percona公司基于官方版本用C++語言開發，性能更優
特點：具有中間件所需的絕大多數功能，包括：
- 多種方式的讀/寫分離
- 定制基于使用者、基于schema、基于語句的規則對SQL語句進行路由
- 緩存查詢結果
- 後端節點監控
  官方站點：https://proxysql.com/

ProxySQL安裝：

準備：
- 實作讀寫分離前，先實作主從複制
  注：slave伺服器配置檔案中必須為 read_only=1，ProxySQL通過read_only=1參數，确定哪個是salve伺服器

基于YUM倉庫安裝：

[ root@centos ~]# cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
EOF

基于RPM下載下傳安裝：https://github.com/sysown/proxysql/releases
ProxySQL的組成：
- 服務腳本：/etc/init.d/proxysql
- 配置檔案：/etc/proxysql.cnf
- 主程式：/usr/bin/proxysql
- 基于SQLITE的資料庫檔案：/var/lib/proxysql/
啟動ProxySQL：service proxysql start
- 啟動後會監聽兩個預設端口：
  - 6032：ProxySQL的管理端口
  - 6033：ProxySQL對外提供服務的端口
使用mysql用戶端連接配接到ProxySQL的管理端口6032，預設管理者使用者和密碼都是admin
```
[ root@weiying ~]# mysql -uadmin -padmin -p6032 -hhost           
```
ProxySQL實作讀寫分離:
内置了SQLite小型資料庫，裡面存儲了proxysql的設定
- 内置的資料庫說明：
- main：是預設的資料庫名，表裡面存放後端db執行個體，使用者驗證，路由規則等資訊，表名以runtime_開頭表示ProxySQL目前運作的配置内容，不能通過dml語句修改，隻能修改對應的不以runtime_開頭的表，然後LOAD使其生效，save使其儲存到硬碟一共下次重新開機加載
- disk：是持久化到停盤的配置，sqlite資料檔案
- stats：是ProxySQL運作抓取到的統計資訊，包括到後端各指令的執行次數、流量、processlist、查詢種類彙總/執行時間、等等
- monnitor：庫存儲monitor子產品收集的資訊，主要是對後端db的健康/延遲檢查
  
  注：監控子產品的名額存在log表中
```
- 說明：
    1. 在main和monitor資料庫中的表，runtime_開頭的是運作時的配置，不能修改，隻能修改非runtime_表
    2. 修改後必須執行LOAD … TO RUNTIME才能加載到RUNTIME生效
    3. 執行save … to disk 才将配置持久化儲存到磁盤，即儲存在proxysql.db檔案中
    4. global_variables 有許多變量可以設定，其中就包括監聽的端口、管理賬号等   
    參考: https://github.com/sysown/proxysql/wiki/Global-variables           
```

配置- ProxySQL：

向ProxySQL中的main庫中指定mysql節點(不需要使用use main也可以)：

1. 檢視指定的表結構：
    select * from sqlite_master where name='mysql_servers'\G 
2. 添加所有參與主從複制的主機： 
    insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.22.45.131',3306);  
     insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.22.45.132',3306);  
3. 加載到runtime中使其生效：
    load mysql servers to runtime;
4. 儲存到硬碟中：
    save mysql servers to disk;
字段說明：  
    hostgroup_id：分組id，用來實作區分讀組和寫組，後續可通過ProxySQL程式自動判斷  
    hostname：主從伺服器的位址  
    port：主從伺服器監聽的端口号

master和slave節點操作：
- 添加監控後端節點的使用者，ProxySQL通過每個節點的read_only值來自動調整它們是屬于讀組還是寫組
  - 主從節點建立使用者：
```
grant replication client on *.* to monitor@'172.22.45.%' identified by 'centos'; #用來實作proxysql連接配接主從節點           
```

ProxySQL上配置監控：

set mysql-monitor_username='monitor';
set mysql-monitor_password='centos';
load mysql variables to runtime;
save mysql variables to disk;   

檢視監控連接配接是否正常：
    select * from mysql_server_connect_log;
檢視監控心跳資訊（對ping名額的監控）：
    select * from mysql_server_ping_log;
檢視read_only和replication_lag的監控日志
    select * from mysql_server_read_only_log;
    select * from mysql_server_replication_lag_log;

設定分組資訊：

需要修改的是main庫中的mysql_replication_hostgroups表，該表有3個字段：writer_hostgroup，reader_hostgroup，comment, 指定寫組的id為10，讀組的id為20

insert inot mysql_replication_hostgroups values(10,20,'test');
load mysql servers to runtime;
save mysql servers to disk;
# Monitor子產品監控後端的read_only值，按照read_only的值将節點自動移動到讀/寫組
檢視主從伺服器的分組資訊：
select hostgroup_id,hostname,port,status,weight from mysql_servers; 
+--------------+---------------+------+--------+--------+
|        hostgroup_id | hostname      | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10           | 172.22.45.131 | 3306 | ONLINE | 1      |
| 20           | 172.22.45.132 | 3306 | ONLINE | 1      |
+--------------+---------------+------+--------+--------+

配置發送SQL語句的使用者：

在master節點上建立通路使用者

grant all on *.* to sqluser@'host' identified by 'centos'; #用來讓使用者連接配接proxy使用

在ProxySQL配置，将使用者sqluser添加到mysql_users表中， default_hostgroup預設組設定為寫組10，當讀寫分離的路由規則不符合時，會通路預設組的資料庫
```
insert into mysql_users(username,password,default_hostgroup)values('sqluser','magedu',10);
load mysql servers to runtime;
save mysql servers to disk;           
```

測試：目前由于沒有設定讀寫分離的路由規則，則所有的讀寫語句都到預設的分組10中實作

mysql -usqluser -pcentos -P6033 -h127.0.0.1 -e 'select @@server_id'
+-------------+
| @@server_id |
+-------------+
|         131 |
+-------------+
mysql -usqluser -pcentos -P6033 -h127.0.0.1 -e 'create database testdb'
mysql -usqluser -pcentos testdb -P6033 -h127.0.0.1 -e 'create table t(id int)'

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

測試讀操作是否路由給20的讀組

mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'select @@server_id'
        +-------------+
        | @@server_id |
        +-------------+
        |         131 |
        +-------------+

測試寫操作，以事務方式進行測試

mysql -usqluser -pcentos -P6033 -h172.22.45.133 -e 'start transaction;select @@server_id;commit;select @@server_id'
    +-------------+
    | @@server_id |
    +-------------+
    |         131 |
    +-------------+
    +-------------+
    | @@server_id |
    +-------------+
    |         132 |
    +-------------+
mysql -usqluser -pcentos -P6033 -h127.0.0.1 -e 'insert testdb.t values (1)'
mysql -usqluser -pcentos -P6033 -h127.0.0.1 -e 'select id from testdb.t'

路由的資訊：查詢stats庫中的stats_mysql_query_digest表

SELECT hostgroup hg,sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

mysq通過ProxySQL實作讀寫分離mysq通過ProxySQL實作讀寫分離

mysq通過ProxySQL實作讀寫分離

常見的讀寫分離應用

ProxySQL：MySQL中間件

ProxySQL安裝：

繼續閱讀

基于MaxScale中間件的MySQL讀寫分離

mysql主從複制和mycat讀寫分離1. Mycat讀寫分離

mycat基本概念和配置檔案mycat

MyCat 之路 | 配置 Mysql 讀寫分離+強制走寫節點+根據主從延時的讀寫分離

mycat實作讀寫分離讀寫分離原理一、準備mariadb環境二、部署mariadb（db1和db2）三、mycat實作讀寫分離四、驗證讀寫分離

基于Mycat實作讀寫分離

Mycat簡單入門--安裝、部署、配置、應用

從零開始java資料庫篇（七）：Mycat讀寫分離一：Mycat說明二：Mycat的安裝三：Mycat實作讀寫分離四：熱切換

雲原生分布式 PostgreSQL+Citus 叢集在 Sentry 後端的實踐

《MySQL實戰45講》——學習筆記28 “讀寫分離/主從延遲的解決方案/GTID“

【mycat系列二】Docker安裝mycat并實作MySQL的讀寫分離

Mysql 讀寫分離的 Java 實作

開源的高性能資料庫代理工具ProxySQL安裝及配置

SpringBoot整合Mybatis和ShardingJDBC實作讀寫分離

mysql 主從配置，實作讀寫分離

MySQL Router 實作資料庫讀寫分離配置實踐