MySQL中間件ProxySQL2.x安裝及測(cè)試

MySQL中間件ProxySQL2.x安裝及測(cè)試

文章來(lái)源: 陶老師運(yùn)維筆記- 微信公眾號(hào)

ProxySQL.jpg

1.簡(jiǎn)介

ProxySQL 是一個(gè)較輕量但功能強(qiáng)大的MySQL中間件。可以很好的支持 Master Slave， MGR, PXC等MySQL架構(gòu)，并提供連接池、讀寫分離、日志記錄等功能。
ProxySQL當(dāng)前最新版本是2.0.x,已可以支持MySQL8.0了。

2.安裝

下載軟件： https://github.com/sysown/proxysql/releases

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

#或下載
$wget https://github.com/sysown/proxysql/releases/download/v2.0.8/proxysql-2.0.8-1-centos67.x86_64.rpm
#安裝
$rpm -ivh proxysql-2.0.8-1-centos67.x86_64.rpm 
#查看版本
$proxysql --version
ProxySQL version 2.0.8-67-g877cab1, codename Truls

啟停/狀態(tài)

#查看版本
$proxysql --version
#存儲(chǔ)目錄
#ls  /var/lib/proxysql/
#啟動(dòng)/停止
$service proxysql start
$service proxysql stop
$service proxysql status  # 查看proxysql狀態(tài)
ProxySQL is running (20761).

測(cè)試
成功啟動(dòng)后，就使用原始賬號(hào)admin/admin可以登錄proxy管理接口6032。

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '

3. 配置

可以用配置文件或管理接口進(jìn)行配置。

配置ProxySQL的推薦方式是通過(guò)它的管理接口(admin interface)。
這樣可以通過(guò)SQL查詢它的管理數(shù)據(jù)庫(kù)實(shí)現(xiàn)在線配置(無(wú)需重啟ProxySQL)。這種配置方式非常有效。

3.1 用配置文件配置

配置文件: /etc/proxysql.cnf 說(shuō)明如下：

頂級(jí)sections：

  • admin_variables：包含控制管理接口功能的全局變量。
  • mysql_variables：包含控制"處理MySQL流入流量功能"的全局變量。
  • mysql_servers：包含管理接口中mysql_servers表的行。
  • mysql_users：包含管理接口中mysql_users表的行。它們定義了誰(shuí)可以連接到ProxySQL，以及ProxySQL用哪個(gè)用戶連接后端服務(wù)器。
  • mysql_query_rules：包含管理接口中mysql_query_rules表的行。它們根據(jù)各種標(biāo)準(zhǔn)(pattern匹配，執(zhí)行查詢的用戶身份，等等)定義了對(duì)MySQL流量的分類和路由規(guī)則。
#修改配置
vim /etc/proxysql.cnf 
cat /etc/proxysql.cnf |grep -v '#'|grep -v '^$'

3.2 用admin interface配置

用admin interface配置ProxySQL，ProxySQL的admin管理接口是一個(gè)使用MySQL協(xié)議的接口，所以，可以直接使用mysql客戶端、navicat等工具去連接這個(gè)管理接口。

例如，使用mysql客戶端去連接ProxySQL的管理接口。下面使用的是管理接口的默認(rèn)用戶名和密碼admin:admin。

使用admin憑據(jù)連接到本地6032端口的管理接口：

$mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
Admin> 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 |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)
Admin> show tables from main;
+--------------------------------------------+
| tables                                     |
+--------------------------------------------+
| global_variables                           |
| mysql_collations                           |
| mysql_group_replication_hostgroups         |
| mysql_query_rules                          |
| mysql_query_rules_fast_routing             |
| mysql_replication_hostgroups               |
| mysql_servers                              |
| mysql_users                                |
| proxysql_servers                           |
| runtime_checksums_values                   |
| runtime_global_variables                   |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_query_rules                  |
| runtime_mysql_query_rules_fast_routing     |
| runtime_mysql_replication_hostgroups       |
| runtime_mysql_servers                      |
| runtime_mysql_users                        |
| runtime_proxysql_servers                   |
| runtime_scheduler                          |
| scheduler                                  |
+--------------------------------------------+
20 rows in set (0.00 sec)

注意：如果你的MySQL客戶端版本為8.04或更高版本，需要在上面連接到管理接口的命令行上加上--default-auth=mysql_native_password。

庫(kù)說(shuō)明:

  • main庫(kù)是ProxySQL最主要的庫(kù)，是需要修改配置時(shí)使用的庫(kù)，它其實(shí)是一個(gè)內(nèi)存數(shù)據(jù)庫(kù)系統(tǒng)。所以，修改main庫(kù)中的配置后，必須將其持久化到disk上才能永久保存。
  • disk庫(kù)是磁盤數(shù)據(jù)庫(kù)，該數(shù)據(jù)庫(kù)結(jié)構(gòu)和內(nèi)存數(shù)據(jù)庫(kù)完全一致。當(dāng)持久化內(nèi)存數(shù)據(jù)庫(kù)中的配置時(shí)，其實(shí)就是寫入到disk庫(kù)中。
  • stats庫(kù)是統(tǒng)計(jì)信息庫(kù)。這個(gè)庫(kù)中的數(shù)據(jù)一般是在檢索其內(nèi)數(shù)據(jù)時(shí)臨時(shí)填充的，它保存在內(nèi)存中。因?yàn)闆](méi)有相關(guān)的配置項(xiàng)，所以無(wú)需持久化。
  • monitor庫(kù)是監(jiān)控后端MySQL節(jié)點(diǎn)相關(guān)的庫(kù)，該庫(kù)中只有幾個(gè)log類的表，監(jiān)控模塊收集到的監(jiān)控信息全都存放到對(duì)應(yīng)的log表中。
  • stats_history庫(kù)是1.4.4版新增的庫(kù)，用于存放歷史統(tǒng)計(jì)數(shù)據(jù)。

3.3 配置生效

ProxySQL的配置幾乎都是通過(guò)管理接口來(lái)操作的，通過(guò)Admin管理接口，可以在線修改幾乎所有的配置并使其生效。
生效方法示例：

admin> load xxx  to runtime;  # 使修改立即生效
admin> save xxx to disk;     # 使修改永久保存到磁盤

上面的XXX是什么？這表示要加載/保存的是哪類配置。目前的ProxySQL支持以下幾種：
[x] mysql users
[x] mysql servers
[x] mysql variables
[x] mysql query rules
[x] admin variables
[x] scheduler
[ ] proxysql_servers：

這些從main庫(kù)或disk庫(kù)中就可以查看到。

Admin> show tables from main;

Admin> show tables from disk;
+------------------------------------+
| tables                             |
+------------------------------------+
| global_variables                   |
| mysql_collations                   |
| mysql_group_replication_hostgroups |
| mysql_query_rules                  |
| mysql_query_rules_fast_routing     |
| mysql_replication_hostgroups       |
| mysql_servers                      |
| mysql_users                        |
| proxysql_servers                   |
| scheduler                          |
+------------------------------------+
10 rows in set (0.00 sec)
image.png

4.管理接口

4.1 管理接口介紹

當(dāng)ProxySQL啟動(dòng)后，將監(jiān)聽兩個(gè)端口：
(1).admin管理接口，默認(rèn)端口為6032。該端口用于查看、配置ProxySQL。
(2).接收SQL語(yǔ)句的接口，默認(rèn)端口為6033，這個(gè)接口類似于MySQL的3306端口。

image.png
$mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
Admin> 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 |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)
Admin> show tables from disk;

請(qǐng)注意，ProxySQL數(shù)據(jù)是存儲(chǔ)在SQLite3中，目錄/var/lib/proxy下。SQLite3和MySQL使用的SQL語(yǔ)法有所不同，因此能在MySQL上正常運(yùn)行的命令不一定能在SQLite3上運(yùn)行。例如，盡管admin管理接口接收USE命令，但不會(huì)切換默認(rèn)的schema，因?yàn)镾QLite3不支持USE功能。
ProxySQL會(huì)盡量將MySQL語(yǔ)法自動(dòng)調(diào)整為對(duì)應(yīng)的SQLite3的語(yǔ)法，例如show databases;會(huì)轉(zhuǎn)換為SQLite3上等價(jià)的語(yǔ)句。

4.2 管理接口相關(guān)的變量

ProxySQL的配置幾乎都是通過(guò)管理接口來(lái)操作的，通過(guò)Admin管理接口，可以在線修改幾乎所有的配置并使其生效。只有兩個(gè)變量的配置是必須重啟ProxySQL才能生效的，它們是：mysql-threads和mysql-stacksize。

1. admin-admin_credentials

默認(rèn)的管理員賬戶和密碼為admin:admin，但是這個(gè)默認(rèn)的用戶只能在本地使用。如果想要遠(yuǎn)程連接到ProxySQL，例如用windows上的navicat連接Linux上的ProxySQL管理接口，必須自定義一個(gè)管理員賬戶。

admin> select @@admin-admin_credentials;    # 當(dāng)前用戶名和密碼
+---------------------------+
| @@admin-admin_credentials |
+---------------------------+
| admin:admin               |
+---------------------------+
1 row in set (0.01 sec)

admin> set admin-admin_credentials='admin:admin;myuser:myuser';

admin> select @@admin-admin_credentials;
+---------------------------+
| @@admin-admin_credentials |
+---------------------------+
| admin:admin;myuser:myuser |
+---------------------------+

admin> load admin variables to runtime;  # 使修改立即生效
admin> save admin variables to disk;     # 使修改永久保存到磁盤

所有的配置操作都是在修改main庫(kù)中對(duì)應(yīng)的表。

admin> select * from global_variables ;
admin> select * from global_variables 
 where variable_name='admin-admin_credentials';
+-------------------------+---------------------------+
| variable_name           | variable_value            |
+-------------------------+---------------------------+
| admin-admin_credentials | admin:admin;myuser:myuser |
+-------------------------+---------------------------+
1 row in set (0.00 sec)

前面的set語(yǔ)句和下面的update語(yǔ)句是等價(jià)的：

update global_variables set variable_value='admin:admin;myuser:myuser'
 where variable_name='admin-admin_credentials';

2. admin-stats_credentials

該變量控制admin管理接口的普通用戶，這個(gè)變量中的用戶沒(méi)有超級(jí)管理員權(quán)限，只能查看monitor庫(kù)和main庫(kù)中關(guān)于統(tǒng)計(jì)的數(shù)據(jù)，其它庫(kù)都是不可見(jiàn)的，且沒(méi)有任何寫權(quán)限。
默認(rèn)的普通用戶名和密碼為stats:stats。

admin> select @@admin-stats_credentials;
+---------------------------+
| @@admin-stats_credentials |
+---------------------------+
| stats:stats               |
+---------------------------+

$mysql -ustats -pstats -P6032 -h127.0.0.1 -e 'show databases'
+-----+---------------+-------------------------------------+
| seq | name          | file                                |
+-----+---------------+-------------------------------------+
| 0   | main          |                                     |
| 2   | monitor       |                                     |
| 3   | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+

3. 用戶賬號(hào)

必須要區(qū)分admin,stat管理接口的用戶名和mysql_users中的用戶名。

  • admin管理接口的用戶: 是連接到管理接口(默認(rèn)端口6032)上用來(lái)管理、配置ProxySQL的。
  • mysql_users表中的用戶: 是應(yīng)用程序連接ProxySQL(默認(rèn)端口6033)，以及ProxySQL連接后端MySQL Servers使用的用戶。它的作用是發(fā)送、路由SQL語(yǔ)句，類似于MySQL Server的3306端口。所以，這個(gè)表中的用戶必須已經(jīng)在后端MySQL Server上存在且授權(quán)了! 
#MySQL Master
#grant select,insert,update,delete on *.* to 'mydev'@'%' identified by 'devpass';

#proxy
admin> insert into mysql_users(username,password,active,max_connections) values('mydev','devpass',1,10000);    
admin> select username,password,active,default_hostgroup,max_connections from mysql_users; 
+----------+----------+--------+-------------------+-----------------+
| username | password | active | default_hostgroup | max_connections |
+----------+----------+--------+-------------------+-----------------+
| mydev    | devpass  | 1      | 0                 | 10000           |
+----------+----------+--------+-------------------+-----------------+

4 . admin-mysql_ifaces

該變量指定admin接口的監(jiān)聽地址，格式為分號(hào)分隔的hostname:port列表。默認(rèn)監(jiān)聽在0.0.0.0:6032。
注意，允許使用UNIX的domain socket進(jìn)行監(jiān)聽，這樣本主機(jī)內(nèi)的應(yīng)用程序就可以直接被處理。

admin> select @@admin-mysql_ifaces;
+----------------------+
| @@admin-mysql_ifaces |
+----------------------+
| 0.0.0.0:6032         |
+----------------------+
admin> SET admin-mysql_ifaces='0.0.0.0:6032;/tmp/proxysql_admin.sock'  ;
Query OK, 1 row affected (0.00 sec)
admin> select @@admin-mysql_ifaces;

5.讀寫分離

5.1 環(huán)境準(zhǔn)備

角色 IP port server-id
Proxy 192.110.103.40 - -
MySQL-Master 192.110.103.41 3306 103413306
MySQL-Slave 192.110.103.42 3306 103423306
MySQL-Slave 192.110.103.43 3306 103433306

1)MySQL安裝 (略)

2)MySQL授權(quán)
為了簡(jiǎn)便，對(duì)大網(wǎng)段192.%授權(quán)，密碼也相同。線上不能這樣做！

#主庫(kù)授權(quán)
alter user root@localhost identified by 'P@ssword1!';
grant replication slave on *.* to repl@'192.%' identified by 'P@ssword1!';
grant replication client on *.* to monitor@'192.% ' identified by 'P@ssword1!';

3)主從同步

#在主庫(kù)中
show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       151 |
+------------------+-----------+
# 以下在slave節(jié)點(diǎn)上都執(zhí)行
>change master to 
    master_host='192.110.103.41',
    master_port=3306,
    master_user='repl',
    master_password='P@ssword1!',
    master_log_file='mysql-bin.000001',
    master_log_pos=151;
> start slave;
> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Checking master version
                  Master_Host: 192.110.103.41
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 151
               Relay_Log_File: relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

5.2 ProxySQL添加DB節(jié)點(diǎn)

啟動(dòng)后會(huì)監(jiān)聽兩個(gè)端口，默認(rèn)為6032和6033。6032端口是ProxySQL的管理端口，6033是ProxySQL對(duì)外提供服務(wù)的端口。

$service proxysql start
$netstat -tnlp |grep proxysql -i
tcp        0      0 0.0.0.0:6032                0.0.0.0:*                   LISTEN      20763/proxysql      
tcp        0      0 0.0.0.0:6033                0.0.0.0:*                   LISTEN      20763/proxysql

主要修改main和monitor數(shù)據(jù)庫(kù)中的表。

$mysql -uadmin -padmin -P6032 -h127.0.0.1 --prompt 'admin> '
$show databases;
admin> show tables from monitor;
admin> show tables from main;
+--------------------------------------------+
| tables                                     |
+--------------------------------------------+
| global_variables                           |
| mysql_collations                           |
| mysql_group_replication_hostgroups         |
| mysql_query_rules                          |
| mysql_query_rules_fast_routing             |
| mysql_replication_hostgroups               |
| mysql_servers                              |
| mysql_users                                |
| proxysql_servers                           |
| runtime_checksums_values                   |
| runtime_global_variables                   |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_query_rules                  |
| runtime_mysql_query_rules_fast_routing     |
| runtime_mysql_replication_hostgroups       |
| runtime_mysql_servers                      |
| runtime_mysql_users                        |
| runtime_proxysql_servers                   |
| runtime_scheduler                          |
| scheduler                                  |
+--------------------------------------------+

runtime_開頭的是運(yùn)行時(shí)的配置，這些是不能修改的。要修改ProxySQL的配置，需要修改了非runtime_表，修改后必須執(zhí)行LOAD ... TO RUNTIME才能加載到RUNTIME生效，執(zhí)行save ... to disk才能將配置持久化保存到磁盤。具體操作見(jiàn)后文。

insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.110.103.41',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.110.103.42',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.110.103.43',3306);

Admin> select hostgroup_id,hostname,port,status,weight,max_connections from mysql_servers;
+--------------+---------------+------+--------+--------+-----------------+
| hostgroup_id | hostname      | port | status | weight | max_connections |
+--------------+---------------+------+--------+--------+-----------------+
| 10           | 192.110.103.41 | 3306 | ONLINE | 1      | 1000            |
| 20           | 192.110.103.43 | 3306 | ONLINE | 1      | 1000            |
| 20           | 192.110.103.42 | 3306 | ONLINE | 1      | 1000            |
+--------------+---------------+------+--------+--------+-----------------+
3 rows in set (0.00 sec)

#修改后，加載到RUNTIME，并保存到disk
admin> load mysql servers to runtime;
admin> save mysql servers to disk;

5.3 監(jiān)控后端MySQL節(jié)點(diǎn)

添加節(jié)點(diǎn)之后，還需要監(jiān)控后端節(jié)點(diǎn)。對(duì)于后端是主從復(fù)制的環(huán)境來(lái)說(shuō)，這是必須的，因?yàn)镻roxySQL需要通過(guò)每個(gè)節(jié)點(diǎn)的read_only值來(lái)自動(dòng)調(diào)整它們是屬于讀組還是寫組。
首先在后端master節(jié)點(diǎn)上創(chuàng)建一個(gè)用于監(jiān)控的用戶名(只需在master上創(chuàng)建即可，因?yàn)闀?huì)復(fù)制到slave上)，這個(gè)用戶名只需具有USAGE權(quán)限即可。如果還需要監(jiān)控復(fù)制結(jié)構(gòu)中slave是否嚴(yán)重延遲于master，則還需具備replication client權(quán)限。這里直接賦予這個(gè)權(quán)限。

#master
grant replication client on *.* to monitor@'192.% ' identified by 'P@ssword1!';

然后回到ProxySQL上配置監(jiān)控。

set mysql-monitor_username='monitor'; set mysql-monitor_password='P@ssword1!';
或者:
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='P@ssword1!'  WHERE variable_name='mysql-monitor_password';
#檢查
admin> select * from global_variables where variable_name like 'mysql-monitor%';

修改后，加載到RUNTIME，并保存到disk。

admin> select * from global_variables where variable_name like 'mysql-monitor%'; 
+-----------------------------------------------------+----------------+
| variable_name                                       | variable_value |
+-----------------------------------------------------+----------------+
| mysql-monitor_enabled                               | true           |
...
| mysql-monitor_username                              | monitor        |
| mysql-monitor_password                              | P@ssword1!     |
| mysql-monitor_connect_interval                      | 60000          |
| mysql-monitor_read_only_timeout                     | 500            |
+-----------------------------------------------------+----------------+
22 rows in set (0.00 sec)

admin> load mysql variables to runtime;
admin> save mysql variables to disk;

驗(yàn)證監(jiān)控結(jié)果:

ProxySQL監(jiān)控模塊的指標(biāo)都保存在monitor庫(kù)的log表中。
以下是連接是否正常的監(jiān)控(對(duì)connect指標(biāo)的監(jiān)控)：(在前面可能會(huì)有很多connect_error，這是因?yàn)闆](méi)有配置監(jiān)控信息時(shí)的錯(cuò)誤，配置后如果connect_error的結(jié)果為NULL則表示正常)

##
admin> select * from mysql_server_connect_log;
+---------------+------+------------------+-------------------------+---------------+
| hostname      | port | time_start_us    | connect_success_time_us | connect_error |
+---------------+------+------------------+-------------------------+---------------+
| 192.110.103.42 | 3306 | 1571329677556524 | 2819                    | NULL          |
| 192.110.103.41 | 3306 | 1571329678496044 | 2392                    | NULL          |
| 192.110.103.41 | 3306 | 1571329738369098 | 2707                    | NULL          |
+---------------+------+------------------+-------------------------+---------------+
#心跳信息的監(jiān)控(對(duì)ping指標(biāo)的監(jiān)控)：監(jiān)控
admin> select * from mysql_server_ping_log;     
+---------------+------+------------------+----------------------+-------------------------------------------------------------------------+
| hostname      | port | time_start_us    | ping_success_time_us | ping_error                                                              |
+---------------+------+------------------+----------------------+-------------------------------------------------------------------------+
| 192.110.103.42 | 3306 | 1571329127917343 | 0                    | Access denied for user 'monitor'@'192.110.103.40' (using password: YES) |
| 192.110.103.41 | 3306 | 1571329137776787 | 786                  | NULL                                                                    |
| 192.110.103.42 | 3306 | 1571329137954593 | 805                  | NULL                                                                    |
| 192.110.103.41 | 3306 | 1571329147776863 | 810                  | NULL                                                                    |

#

但是，read_only和replication_lag的監(jiān)控日志都為空。

admin>  select * from mysql_server_read_only_log;
Empty set (0.00 sec)

admin> select * from mysql_server_replication_lag_log;
Empty set (0.00 sec)

這是因?yàn)檫€沒(méi)有對(duì)ProxySQL中的節(jié)點(diǎn)分組：writer_hostgroup、reader_hostgroup。

設(shè)置分組信息，需要修改的是main庫(kù)中的mysql_replication_hostgroups表，該表只有4個(gè)字段：第一個(gè)字段名為writer_hostgroup，第二個(gè)字段為reader_hostgroup，第三個(gè)字段為check_type,有super_read_only,read_only等，默認(rèn)為read_only, 第四字段為注釋字段，可隨意寫。

注意: 1.4x版本和2.x版本相比，少一個(gè)check_type字段!

如下，咱們指定寫組的id為10，讀組的id為20。

#insert into mysql_replication_hostgroups values(10,20,'-'); #1.4x版本
insert into mysql_replication_hostgroups values(10,20,'read_only','-'); #2.x版本
#2.0.x表結(jié)構(gòu)變了。
Admin> select * from sqlite_master where name = "mysql_replication_hostgroups"\G;
Admin> SHOW CREATE TABLE mysql_replication_hostgroups\G
*************************** 1. row ***************************
       table: mysql_replication_hostgroups
Create Table: CREATE TABLE mysql_replication_hostgroups (
    writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
    reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>=0),
    check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only','innodb_read_only','super_read_only','read_only|innodb_read_only','read_only&innodb_read_only')) NOT NULL DEFAULT 'read_only',
    comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup))


Admin> select * from mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 10               | 20               | read_only  | -       |
+------------------+------------------+------------+---------+

admin> select hostgroup_id,hostname,port,status,weight from mysql_servers; 
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname      | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10           | 192.110.103.41 | 3306 | ONLINE | 1      |
| 10           | 192.110.103.42 | 3306 | ONLINE | 1      |
+--------------+---------------+------+--------+--------+

將剛才mysql_replication_hostgroups表的修改加載到RUNTIME生效。

admin> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)

admin> save mysql servers to disk;
Query OK, 0 rows affected (0.01 sec)

一加載，Monitor模塊就會(huì)開始監(jiān)控后端的read_only值，當(dāng)監(jiān)控到read_only值后，就會(huì)按照read_only的值將某些節(jié)點(diǎn)自動(dòng)移動(dòng)到讀/寫組。

例如，此處所有節(jié)點(diǎn)都在id=10的寫組，slave1和slave2都是slave，它們的read_only=1，這兩個(gè)節(jié)點(diǎn)將會(huì)移動(dòng)到id=20的組。如果一開始這3節(jié)點(diǎn)都在id=20的讀組，那么移動(dòng)的將是Master節(jié)點(diǎn)，會(huì)移動(dòng)到id=10的寫組。

結(jié)果：

insert into mysql_replication_hostgroups values(10,20,'read_only','-');
select * from mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 10               | 20               | read_only  | -       |
+------------------+------------------+------------+---------+
1 row in set (0.00 sec)

select hostgroup_id,hostname,port,status,weight from mysql_servers; 
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname      | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10           | 192.110.103.41 | 3306 | ONLINE | 1      |
| 10           | 192.110.103.42 | 3306 | ONLINE | 1      |
| 10           | 192.110.103.43 | 3306 | ONLINE | 1      |
+--------------+---------------+------+--------+--------+
3 rows in set (0.00 sec)

#生效
load mysql servers to runtime;
save mysql servers to disk;

#會(huì)依read only自動(dòng)修改分組
select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname      | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10           | 192.110.103.41 | 3306 | ONLINE | 1      |
| 20           | 192.110.103.43 | 3306 | ONLINE | 1      |
| 20           | 192.110.103.42 | 3306 | ONLINE | 1      |
+--------------+---------------+------+--------+--------+

Admin> select * from mysql_server_read_only_log limit 5; 
+---------------+------+------------------+-----------------+-----------+-------+
| hostname      | port | time_start_us    | success_time_us | read_only | error |
+---------------+------+------------------+-----------------+-----------+-------+
| 192.110.103.43 | 3306 | 1576595040808568 | 364             | 1         | NULL  |
| 192.110.103.41 | 3306 | 1576595040821383 | 247             | 0         | NULL  |
| 192.110.103.42 | 3306 | 1576595040834224 | 203             | 1         | NULL  |
| 192.110.103.41 | 3306 | 1576595042308511 | 237             | 0         | NULL  |
| 192.110.103.43 | 3306 | 1576595042321244 | 305             | 1         | NULL  |
+---------------+------+------------------+-----------------+-----------+-------+

5.4 配置mysql_users

mysql_users表中的用戶名是應(yīng)用程序連接ProxySQL(默認(rèn)端口6033)，以及ProxySQL連接后端MySQL Servers使用的用戶。

mysql_users配置都是關(guān)于后端MySQL節(jié)點(diǎn)的，現(xiàn)在可以配置：發(fā)送SQL語(yǔ)句的用戶、SQL語(yǔ)句的路由規(guī)則、SQL查詢的緩存、SQL語(yǔ)句的重寫等等。

本小節(jié)是SQL請(qǐng)求所使用的用戶配置，例如root用戶。這要求我們需要先在后端MySQL節(jié)點(diǎn)添加好相關(guān)用戶。這里以root和sqlsender兩個(gè)用戶名為例。

#在master節(jié)點(diǎn)上執(zhí)行
grant all on *.* to root@'192.%' identified by 'P@ssword1!';
grant all on *.* to sqlsender@'192.%' identified by 'P@ssword1!';

#然后回到ProxySQL，配置mysql_users表，將剛才的兩個(gè)用戶添加到該表中。
insert into mysql_users(username,password,default_hostgroup) values('root','P@ssword1!',10);
insert into mysql_users(username,password,default_hostgroup) values('sqlsender','P@ssword1!',10);
load mysql users to runtime;
save mysql users to disk;

mysql_users表有不少字段，最主要的三個(gè)字段為username、password和default_hostgroup：

  • username：前端連接ProxySQL，以及ProxySQL將SQL語(yǔ)句路由給MySQL所使用的用戶名。
  • password：用戶名對(duì)應(yīng)的密碼?？梢允敲魑拿艽a，也可以是hash密碼。如果想使用hash密碼，可以先在某個(gè)MySQL節(jié)點(diǎn)上執(zhí)行select password(PASSWORD)，然后將加密結(jié)果復(fù)制到該字段。
  • default_hostgroup：該用戶名默認(rèn)的路由目標(biāo)。例如，指定root用戶的該字段值為10時(shí)，則使用root用戶發(fā)送的SQL語(yǔ)句默認(rèn)情況下將路由到hostgroup_id=10組中的某個(gè)節(jié)點(diǎn)。
admin> select * from mysql_users;
+-----------+------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username  | password   | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+-----------+------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| mydev     | devpass    | 1      | 0       | 0                 | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |
| root      | P@ssword1! | 1      | 0       | 10                | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |
| sqlsender | P@ssword1! | 1      | 0       | 10                | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |
+-----------+------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
3 rows in set (0.00 sec)

注意transaction_persistent，active 字段狀態(tài)

  • 只有active=1的用戶才是有效的用戶。
  • transaction_persistent字段，當(dāng)它的值為1時(shí)，表示事務(wù)持久化：當(dāng)某連接使用該用戶開啟了一個(gè)事務(wù)后，那么在事務(wù)提交/回滾之前，所有的語(yǔ)句都路由到同一個(gè)組中，避免語(yǔ)句分散到不同組(更進(jìn)一步的，它會(huì)自動(dòng)禁用multiplexing，讓同一個(gè)事務(wù)的語(yǔ)句從同一個(gè)連接路由出去，保證路由到同一個(gè)組的同一個(gè)節(jié)點(diǎn))。我們期望的值為1。
#若不是1就可修改為1
update mysql_users set transaction_persistent=1 where username='root';
update mysql_users set transaction_persistent=1 where username='sqlsender';
load mysql users to runtime;
save mysql users to disk;

5.5 讀寫分離測(cè)試

另開一個(gè)終端，分別使用root用戶和sqlsender用戶測(cè)試下它們是否能路由到默認(rèn)的hostgroup_id=10(它是一個(gè)寫組)讀、寫數(shù)據(jù)。

mysql -uroot -pP@ssword1! -P6033 -h127.0.0.1 -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|   103413306 |
+-------------+
mysql -uroot -pP@ssword1! -P6033 -h127.0.0.1 -e "create database proxy_test"
mysql -uroot -pP@ssword1! -P6033 -h127.0.0.1 -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| proxy_test         |
| test               |
+--------------------+
mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 proxy_test -e 'create table t(id int);' 
mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 proxy_test -e "show tables;select @@server_id;"
+----------------------+
| Tables_in_proxy_test |
+----------------------+
| t                    |
+----------------------+
+-------------+
| @@server_id |
+-------------+
|   103413306 |
+-------------+

6. 讀寫分離

6.1 配置路由規(guī)則

ProxySQL的路由規(guī)則非常靈活，可以基于用戶、基于schema以及基于每個(gè)語(yǔ)句實(shí)現(xiàn)路由規(guī)則的定制。

說(shuō)明： 實(shí)際的路由規(guī)則絕不應(yīng)該僅根據(jù)所謂的讀、寫操作進(jìn)行分離，而是從各項(xiàng)指標(biāo)中找出壓力大、執(zhí)行頻繁的語(yǔ)句單獨(dú)寫規(guī)則、做緩存等等。

和查詢規(guī)則有關(guān)的表有兩個(gè)：mysql_query_rules和mysql_query_rules_fast_routing，后者是前者的擴(kuò)展表。

插入兩個(gè)規(guī)則，目的是將select語(yǔ)句分離到hostgroup_id=20的讀組，但由于select語(yǔ)句中有一個(gè)特殊語(yǔ)句SELECT...FOR UPDATE它會(huì)申請(qǐng)寫鎖，所以應(yīng)該路由到hostgroup_id=10的寫組。

mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 proxy_test -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);

load mysql query rules to runtime;
save mysql query rules to disk;
admin> select * from runtime_mysql_query_rules\G

select ... for update規(guī)則的rule_id必須要小于普通的select規(guī)則的rule_id，因?yàn)镻roxySQL是根據(jù)rule_id的順序進(jìn)行規(guī)則匹配。

再來(lái)測(cè)試下，讀操作是否路由給了hostgroup_id=20的讀組。

#測(cè)試發(fā)現(xiàn)會(huì)讀從庫(kù). serverid=103423306
mysql -uroot -pP@ssword1! -P6033 -h127.0.0.1 -e 'select @@server_id'
+-------------+
| @@server_id |
+-------------+
|   103423306 |
+-------------+

讀操作已經(jīng)路由給讀組，再看看寫操作。這里以事務(wù)持久化進(jìn)行測(cè)試。

#測(cè)試寫
mysql -uroot -pP@ssword1! -P6033 -h127.0.0.1 -e 'start transaction;select @@server_id;commit; select @@server_id;'

+-------------+
| @@server_id |
+-------------+
|   103413306 |
+-------------+
+-------------+
| @@server_id |
+-------------+
|   103423306 |
+-------------+

最后，如果想查看路由的信息，可查詢stats庫(kù)中的stats_mysql_query_digest表。

admin> select * from stats_mysql_query_digest ORDER BY sum_time DESC limit 10;
+-----------+--------------------+-----------+--------------------+----------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname         | username  | digest             | digest_text                | count_star | first_seen | last_seen  | sum_time | min_time | max_time |
+-----------+--------------------+-----------+--------------------+----------------------------+------------+------------+------------+----------+----------+----------+
| 10        | information_schema | root      | 0xDA65260DF35B8D13 | select @@server_id         | 5          | 1571367177 | 1571380746 | 10598    | 999      | 4438     |
| 10        | proxy_test         | sqlsender | 0x4E6025FB1E51A2E5 | create table t(id int)     | 1          | 1571367516 | 1571367516 | 8773     | 8773     | 8773     |
| 20        | information_schema | root      | 0xC5C9CBEB12CE8379 | select now()               | 1          | 1571380799 | 1571380799 | 8461     | 8461     | 8461     |
| 10        | proxy_test         | sqlsender | 0x99531AEFF718C501 | show tables                | 3          | 1571367527 | 1571380007 | 5740     | 1215     | 3290     |
| 20        | information_schema | root      | 0xDA65260DF35B8D13 | select @@server_id         | 4          | 1571380562 | 1571380799 | 5473     | 714      | 3142     |
| 10        | information_schema | root      | 0x02033E45904D3DF0 | show databases             | 2          | 1571367466 | 1571379999 | 4969     | 1363     | 3606     |
| 10        | information_schema | root      | 0x326F4F2B935EC266 | start transaction          | 2          | 1571380704 | 1571380746 | 2162     | 1058     | 1104     |
| 10        | proxy_test         | sqlsender | 0xDA65260DF35B8D13 | select @@server_id         | 2          | 1571367546 | 1571380007 | 2123     | 1054     | 1069     |
| 10        | information_schema | root      | 0xDB3A841EF5443C35 | commit                     | 2          | 1571380704 | 1571380746 | 1994     | 973      | 1021     |
| 10        | information_schema | root      | 0x871B1AB1144F72C8 | create database proxy_test | 1          | 1571367436 | 1571367436 | 1233     | 1233     | 1233     |
+-----------+--------------------+-----------+--------------------+----------------------------+------------+------------+------------+----------+----------+----------+
10 rows in set (0.00 sec)

參考:

陶老師運(yùn)維筆記
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容