當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

《童虎学习笔记》20分钟实战ProxySQL MGR高可用及读写分离架构

發布時間：2024/1/8 数据库 24 豆豆

生活随笔收集整理的這篇文章主要介紹了《童虎学习笔记》20分钟实战ProxySQL MGR高可用及读写分离架构小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

本文章配套視頻	https://www.ixigua.com/7086085500540289572?id=7087546160079962660
本專欄全部文章	https://blog.csdn.net/tonghu_note/category_11755726.html
總目錄	https://blog.csdn.net/tonghu_note/article/details/124333034

來我的dou音 aa10246666，看配套視頻

一、實戰環境

MGR實現了服務端高可用，ProxySQL實現了集群的客戶端高可用，二者結合來用才是完美的。

Primary 節點(node1)	mysql 8.0.28	10.211.55.9
Secondary1 節點(node2)	mysql 8.0.28	10.211.55.4
Secondary2 節點(node3)	mysql 8.0.28	10.211.55.6
ProxySQL 節點(node4)	2.2.0	10.211.55.7

監控腳本 addition_to_sys_8.0.28.sql 需要先導入Primary節點，因為ProxySQL依賴這個監控框架，下面是下載地址

點霹《童虎學習筆記》mysql8.0.28mgr監控視圖gr_member_routing_ca更多下載資源、學習資料請訪問CSDN下載頻道.https://download.csdn.net/download/tonghu_note/85108757如果想了解如何監控MGR可參考下面這篇文檔《童虎學習筆記》2分鐘學會如何監控MySQL MGR集群_童虎學習筆記的博客-CSDN博客童虎學習筆記的博客_CSDN博客-領域博主https://blog.csdn.net/tonghu_note來我的西瓜視頻，看免費配套視頻https://www.ixigua.com/home/2058760810138187來我的dou音 aa10246666，看配套視頻一、實戰環境Primary 節點(node1)mysql 8.0.2810.211.55.9Secondary1 節點(node2)mysql 8.0.2810....https://blog.csdn.net/tonghu_note/article/details/124088561

二、下載及安裝ProxySQL

1、下載

我的環境是mac m1芯片，所以采用arm的包來安裝

wget https://github.com/sysown/proxysql/releases/download/v2.2.0/proxysql_2.2.0-debian10_arm64.deb

具體要根據自已服務器的情況來決定下載哪個包?，官方安裝包下載地址如下：Download and Install ProxySQL - ProxySQLhttps://proxysql.com/documentation/installing-proxysql/

?2、安裝

dpkg -i proxysql_2.2.0-debian10_arm64.deb

3、核心配置文件地址路徑

/etc/proxysql.cnf?

三、配置ProxySQL

1、在MGR?Primary節點新建ProxySQL監控MGR集群狀態的用戶proxysql_user

create user proxysql_user@'%' identified with mysql_native_password by 'proxysql_pwd';

grant select on sys.* to proxysql_user@'%';

2、在MGR?Primary節點新建ProxySQL操作MGR集群數據庫的用戶app_user

create user app_user@'%' identified with mysql_native_password by 'app_pwd';

grant all on d1.* to app_user@'%';

3、在ProxySQL節點上啟動服務

systemctl start proxysql

4、在ProxySQL節點上新建管理員用戶root，6032是管理端口

先在本地連進ProxySQL服務

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

由于默認的admin管理員帳號只能本地連接，所以我們新建一個管理員用戶root以便可以遠程連接ProxySQL?，其中1234是root帳號對應的密碼

set admin-admin_credentials='admin:admin;root:1234';
load admin variables to runtime;
save admin variables to disk;

可以通過以下命令查看參數配置情況

select * from global_variables;?

5、在ProxySQL節點上配置MGR監控用戶，用于監控MGR集群狀態

set mysql-monitor_username='proxysql_user';
set mysql-monitor_password='proxysql_pwd';
load mysql variables to runtime;
save mysql variables to disk;

6、在ProxySQL節點上配置連接MGR的程序帳號

INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('app_user','app_pwd',1);

load mysql users to runtime;
save mysql users to disk;

select * from mysql_users;
select * from runtime_mysql_users;

7、在ProxySQL節點上配置MGR主機組信息，用于區分MGR成員狀態等情況

insert into mysql_group_replication_hostgroups(writer_hostgroup, backup_writer_hostgroup, reader_hostgroup, offline_hostgroup, active, max_writers, writer_is_also_reader, max_transactions_behind) values(1, 2, 3, 4, 1, 1, 0, 100);

load mysql servers to runtime;
save mysql servers to disk;

select * from mysql_group_replication_hostgroups;
select * from runtime_mysql_group_replication_hostgroups;

?接下來對每一個字段進行介紹：

writer_hostgroup	默認情況下會將所有流量發送到這個組。具有read_only=0的節點也將分配到這個組
backup_writer_hostgroup	用于多寫模式，如果集群有多個寫節點(read_only=0)且超過了max_writers規定數量，則會把多出來的寫節點放到備用寫組里面
reader_hostgroup	讀取的流量應該發送到該組，只讀節點(read_only=1)會被分配到該組
offline_hostgroup	當ProxySQL監視到某個節點不正常時，會被放入該組
active	是否啟用主機組，當啟用時，ProxySQL將監視主機在各族之間移動
max_writers	用于多寫模式，最大寫節點的數量，超過該值的節點應該被放入backup_write_hostgroup
writer_is_also_reader	一個寫節點是否也做讀節點。主模型的MGR時，必須設置writer_is_also_reader=1。如果該值為2，則backup_writer_hostgroup的節點做讀寫點，但是writer_hostgroup不會做讀節點
max_transactions_behind	類似主從延遲流量停用功能。設置一個節點落后的事務數量，達到這個數量后，節點狀態被設置為 shunned ，被完全處理完后，再變更為正常狀態。

8、在ProxySQL節點上配置MGR成員信息

insert into mysql_servers(hostgroup_id,hostname,port) values
(1, '10.211.55.9' ,3306),
(1, '10.211.55.4' ,3306),
(1, '10.211.55.6' ,3306);

load mysql servers to runtime;
save mysql servers to disk;

select * from mysql_servers;
select * from runtime_mysql_servers;

9、在ProxySQL節點上配置讀寫分離路由規則

寫走1號組，讀走3號組

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

load mysql query rules to runtime;
save mysql query rules to disk;

select * from mysql_query_rules \G
select * from runtime_mysql_query_rules \G

四、測試ProxySQL讀寫分離

1、在 Secondary2 節點上去連接?ProxySQL 節點進行測試，6033是程序端口

mysql -uapp_user -p'app_pwd' -h 10.211.55.7 -P6033

?發起一些sql

use d1
create table t11(id int primary key);
insert into t11 select 1;
insert into t11 select 2;
select * from t11;
select * from t11;
select * from t11;
select * from t11;
select * from t11;
select * from t11;
select * from t11;
。。。

2、在ProxySQL 節點在查看讀寫分離情況

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

結果如下，可以看到讀寫請求已經分散到不同步服務器組1和3了

+----+----------+------------+--------------------------------------+
| hg | sum_time | count_star | digest_text ? ? ? ? ? ? ? ? ? ? ? ? ?|
+----+----------+------------+--------------------------------------+
| 1 ?| 33409 ? ?| 1 ? ? ? ? ?| create table t11(id int) ? ? ? ? ? ? |
| 1 ?| 29276 ? ?| 3 ? ? ? ? ?| insert into t11 select ? ? ? ? ? ? ? |
| 1 ?| 26688 ? ?| 1 ? ? ? ? ?| create table t11(id int primary key) |
| 1 ?| 20873 ? ?| 1 ? ? ? ? ?| drop table t11 ? ? ? ? ? ? ? ? ? ? ? |
| 3 ?| 9430 ? ? | 7 ? ? ? ? ?| select * from t11 ? ? ? ? ? ? ? ? ? ?|
| 1 ?| 8535 ? ? | 1 ? ? ? ? ?| show databases ? ? ? ? ? ? ? ? ? ? ? |
| 3 ?| 5819 ? ? | 1 ? ? ? ? ?| SELECT DATABASE() ? ? ? ? ? ? ? ? ? ?|
| 3 ?| 5503 ? ? | 1 ? ? ? ? ?| SELECT DATABASE() ? ? ? ? ? ? ? ? ? ?|
| 1 ?| 4757 ? ? | 1 ? ? ? ? ?| show databases ? ? ? ? ? ? ? ? ? ? ? |
| 1 ?| 2622 ? ? | 1 ? ? ? ? ?| show tables ? ? ? ? ? ? ? ? ? ? ? ? ?|
| 3 ?| 1643 ? ? | 1 ? ? ? ? ?| SELECT * FROM `t2` WHERE ?=? ? ? ? ? |
| 1 ?| 0 ? ? ? ?| 1 ? ? ? ? ?| select @@version_comment limit ? ? ? |
+----+----------+------------+--------------------------------------+
12 rows in set (0.00 sec)?

五、測試ProxySQL高可用HA

1、停止Primary 節點(node1)上的MySQL服務器，模擬寫節點故障

root@node1:~# mysqladmin -uroot -proot shutdown

2、在Secondary2 節點(node3)查看MGR集群狀態

可以看到node1已經補踢出集群，node2現在為主節點了

root@node3:~# mysql -uroot -proot

mysql> select member_host, member_state, member_role from performance_schema.replication_group_members;
+------------------------+------------------------+------------------------+
| MEMBER_HOST | MEMBER_STATE | MEMBER_ROLE |
+------------------------+------------------------+------------------------+
| node2? ? ? ? ? ? ? ? ? ?| ONLINE? ? ? ? ? ? ? ? | PRIMARY? ? ? ? ? ? ?|
| node3? ? ? ? ? ? ? ? ? ?| ONLINE? ? ? ? ? ? ? ? | SECONDARY? ? ? |
+------------------------+--------------+-------------+
2 rows in set (0.00 sec)

3、在node3上連接ProxySQL看是否可以自動識別新的主節點并繼續正常使用讀寫分離

通過程序端口6033確認讀寫是正常的

root@node3:~# mysql -uapp_user -p'app_pwd' -h 10.211.55.7 -P6033

mysql> use d1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------+
| Tables_in_d1 |
+--------------+
| t11 ? ? ? ? ?|
| t2 ? ? ? ? ? |
+--------------+
2 rows in set (0.01 sec)

mysql> select * from t11;
+----+
| id |
+----+
| ?1 |
| ?2 |
+----+
2 rows in set (0.00 sec)

mysql> insert into t11 select 3;
Query OK, 1 row affected (0.01 sec)
Records: 1 ?Duplicates: 0 ?Warnings: 0

mysql> insert into t11 select 4;
Query OK, 1 row affected (0.02 sec)
Records: 1 ?Duplicates: 0 ?Warnings: 0

通過管理端口6032確認成員節點狀態，可以看到node1節點狀態是下線狀態，同時他也被移到了4號組（即故障組）里面

root@node3:~# mysql -uroot -p1234 -h 10.211.55.7 -P6032

mysql> select hostgroup_id, hostname, status from runtime_mysql_servers;
+------------------+----------------+---------+
| hostgroup_id | hostname ? ?| status ?|
+-----------------+-----------------+---------+
| 1? ? ? ? ? ? ? ? ???| 10.211.55.4 | ONLINE ?|
| 4? ? ? ? ? ? ? ? ? ?| 10.211.55.9 | SHUNNED |
| 3? ? ? ? ? ? ? ? ? ?| 10.211.55.6 | ONLINE ?|
+-----------------+-----------------+---------+
3 rows in set (0.01 sec)

mysql>?

4、我們把故障節點node1啟動后再加回MGR集群，確認ProxySQL是否正常工作

將故障節點加回MGR集群

root@node1:~# mysqld_safe --user=mysql &
[1] 103812

root@node1:~# mysql -uroot -proot

mysql> start group_replication;
Query OK, 0 rows affected (1.95 sec)

在node1上通過管理端口6032確認成員節點狀態，可以看到node1節點狀態是在線狀態，同時他也被移到了3號組（即讀組）里面

root@node1:~# mysql -uroot -p1234 -h 10.211.55.7 -P6032

mysql> select hostgroup_id, hostname, status from runtime_mysql_servers;
+------------------+----------------+---------+
| hostgroup_id | hostname ? ?| status ?|
+-----------------+-----------------+---------+
| 1? ? ? ? ? ? ? ? ???| 10.211.55.4 | ONLINE ?|
| 3? ? ? ? ? ? ? ? ? ?| 10.211.55.9 | ONLINE ?|
| 3? ? ? ? ? ? ? ? ? ?| 10.211.55.6 | ONLINE ?|
+-----------------+-----------------+---------+
3 rows in set (0.01 sec)