在mariadb的主從複制叢集中，讀的能力被擴充了，而寫的能力始終沒有被擴充；這樣一來對于主伺服器就存在單點的問題，通常除了做雙主可解決主節點單點的問題，我們還可以給主節點做高可用；而對于mariadb的主從複制叢集來講，雖然讀的能力提升了，但通常情況後端資料庫伺服器是直接面向程式，這意味着程式要知道讀請求和寫請求該發往不同的資料庫伺服器上；在使用者發來讀請求，這個程式它會分析使用者的請求，然後把使用者的請求代理到後端server上；也就是說我們需要一個程式能夠解析使用者的讀寫操作，把對應的操作代理到後端不同的節點上；這樣一來使用者的讀操作始終均衡的被排程到從節點，寫操作排程到主節點；
首先我們來回顧下代理的概念，所謂代理就是指的是一端面向用戶端，另外一端面向服務端，代理用戶端通路服務端，我們把這種代理叫正向代理；代理服務端響應用戶端我們叫做反向代理，這個我們在之前nginx系列部落格中闡述過這樣的概念；不管是正向代理還是反向代理他們都是代理，他們都有一個共同點就是代表一端（用戶端/服務端）通路或響應另一端；簡單講代理就是即充當服務端角色又充當用戶端角色；在mariadb的主從複制叢集中，讀的能力被擴充了，而寫的能力始終沒有被擴充；這樣一來對于主伺服器就存在單點的問題，通常除了做雙主可解決主節點單點的問題，我們還可以給主節點做高可用；而對于mariadb的主從複制叢集來講，雖然讀的能力提升了，但通常情況後端資料庫伺服器是直接面向程式，這意味着程式要知道讀請求和寫請求該發往不同的資料庫伺服器上；在使用者發來讀請求，這個程式它會分析使用者的請求，然後把使用者的請求代理到後端server上；也就是說我們需要一個程式能夠解析使用者的讀寫操作，把對應的操作代理到後端不同的節點上；這樣一來使用者的讀操作始終均衡的被排程到從節點，寫操作排程到主節點；proxysql這款軟體就有我們上面說的功能，它能夠将使用者發來的讀寫操作，通過proxysql的語句路由，把對應請求分别發送到不同節點執行；如下圖所示：
從上面的圖檔可以看到，proxysql就是一代理，面向程式它就是一資料庫伺服器，程式把讀操作和寫操作都發送給它，然後proxysql通過我們定義的路由規則，把對應語句再代理到不同的後端主從架構節點上執行；接下來我們來看看proxysql的配置和使用吧；以下實驗室基于mariadb的主從複制叢集上做的，有關主從複制的配置請參考https://www.cnblogs.com/qiuhom-1874/tag/mariadb%E4%B8%BB%E4%BB%8E%E5%A4%8D%E5%88%B6/；
1、安裝proxysql(添加yum源，直接用yum安裝)
2、檢視proxysql的簡介
[root@lxc ~]# yum info proxysql
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirrors.aliyun.com
* extras: mirrors.aliyun.com
* updates: mirrors.aliyun.com
Available Packages
Name : proxysql
Arch : x86_64
Version : 2.0.12
Release : 1
Size : 9.8 M
Repo : proxysql_repo/7
Summary : A high-performance MySQL proxy
URL : https://proxysql.com/
License : GPL+
Description : A high-performance MySQL proxy
[root@lxc ~]# 提示：如果添加了yum直接yum info proxysql能夠看到以上資訊，表示我們添加到yum源已經生效；
3、安裝proxysql
[root@lxc ~]# yum install proxysql
Loaded plugins: fastestmirror
proxysql_repo | 2.9 kB 00:00:00
Loading mirror speeds from cached hostfile
* base: mirrors.aliyun.com
* extras: mirrors.aliyun.com
* updates: mirrors.aliyun.com
Resolving Dependencies
--> Running transaction check
---> Package proxysql.x86_64 0:2.0.12-1 will be installed
--> Processing Dependency: libgnutls.so.28(GNUTLS_3_1_0)(64bit) for package: proxysql-2.0.12-1.x86_64
--> Processing Dependency: libgnutls.so.28(GNUTLS_3_0_0)(64bit) for package: proxysql-2.0.12-1.x86_64
--> Processing Dependency: gnutls for package: proxysql-2.0.12-1.x86_64
--> Processing Dependency: libgnutls.so.28(GNUTLS_1_4)(64bit) for package: proxysql-2.0.12-1.x86_64
--> Processing Dependency: libgnutls.so.28()(64bit) for package: proxysql-2.0.12-1.x86_64
--> Running transaction check
---> Package gnutls.x86_64 0:3.3.29-9.el7_6 will be installed
--> Processing Dependency: trousers >= 0.3.11.2 for package: gnutls-3.3.29-9.el7_6.x86_64
--> Processing Dependency: libnettle.so.4()(64bit) for package: gnutls-3.3.29-9.el7_6.x86_64
--> Processing Dependency: libhogweed.so.2()(64bit) for package: gnutls-3.3.29-9.el7_6.x86_64
--> Running transaction check
---> Package nettle.x86_64 0:2.7.1-8.el7 will be installed
---> Package trousers.x86_64 0:0.3.14-2.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
=====================================================================================================================================================================
Package Arch Version Repository Size
=====================================================================================================================================================================
Installing:
proxysql x86_64 2.0.12-1 proxysql_repo 9.8 M
Installing for dependencies:
gnutls x86_64 3.3.29-9.el7_6 base 680 k
nettle x86_64 2.7.1-8.el7 base 327 k
trousers x86_64 0.3.14-2.el7 base 289 k
Transaction Summary
=====================================================================================================================================================================
Install 1 Package (+3 Dependent packages)
Total download size: 11 M
Installed size: 41 M
Is this ok [y/d/N]: y
Downloading packages:
(1/4): gnutls-3.3.29-9.el7_6.x86_64.rpm | 680 kB 00:00:00
(2/4): nettle-2.7.1-8.el7.x86_64.rpm | 327 kB 00:00:00
(3/4): trousers-0.3.14-2.el7.x86_64.rpm | 289 kB 00:00:00
warning: /var/cache/yum/x86_64/7/proxysql_repo/packages/proxysql-2.0.12-1-centos7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 79953b49: NOKEYMB 00:00:04 ETA
Public key for proxysql-2.0.12-1-centos7.x86_64.rpm is not installed
(4/4): proxysql-2.0.12-1-centos7.x86_64.rpm | 9.8 MB 00:28:05
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 6.7 kB/s | 11 MB 00:28:05
Retrieving key from https://repo.proxysql.com/ProxySQL/repo_pub_key
Importing GPG key 0x79953B49:
Userid : "rene cannnao (Proxysql Repository) <[email protected]>"
Fingerprint: 1448 bf69 3ca6 00c7 99eb 9358 04a5 62fb 7995 3b49
From : https://repo.proxysql.com/ProxySQL/repo_pub_key
Is this ok [y/N]: y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : trousers-0.3.14-2.el7.x86_64 1/4
Installing : nettle-2.7.1-8.el7.x86_64 2/4
Installing : gnutls-3.3.29-9.el7_6.x86_64 3/4
Installing : proxysql-2.0.12-1.x86_64 4/4
warning: group proxysql does not exist - using root
warning: group proxysql does not exist - using root
Created symlink from /etc/systemd/system/multi-user.target.wants/proxysql.service to /etc/systemd/system/proxysql.service.
Verifying : gnutls-3.3.29-9.el7_6.x86_64 1/4
Verifying : proxysql-2.0.12-1.x86_64 2/4
Verifying : nettle-2.7.1-8.el7.x86_64 3/4
Verifying : trousers-0.3.14-2.el7.x86_64 4/4
Installed:
proxysql.x86_64 0:2.0.12-1
Dependency Installed:
gnutls.x86_64 0:3.3.29-9.el7_6 nettle.x86_64 0:2.7.1-8.el7 trousers.x86_64 0:0.3.14-2.el7
Complete!
[root@lxc ~]#
提示：除此以上方式安裝proxysql，當然也可以直接使用wget 去github上的項目位址下載下傳
[root@lxc ~]# wget https://github.com/sysown/proxysql/releases/download/v2.0.12/proxysql-2.0.12-1-centos7.x86_64.rpm
提示：下載下傳好proxysql 包後，然後通過yum直接安裝即可；推薦使用yum來安裝下載下傳好的包，它可以解決依賴關系，不推薦使用rpm 安裝；
4、檢視proxysql包安裝的檔案清單
[root@lxc ~]# rpm -ql proxysql
/etc/logrotate.d/proxysql
/etc/proxysql.cnf
/etc/systemd/system/proxysql-initial.service
/etc/systemd/system/proxysql.service
/usr/bin/proxysql
/usr/share/proxysql/tools/proxysql_galera_checker.sh
/usr/share/proxysql/tools/proxysql_galera_writer.pl
[root@lxc ~]#
提示：從上面的資訊可以看到proxysql的配置檔案是/etc/proxysql.cnf,二進制檔案是/usr/bin/proxysql，unit file是/etc/systemd/system/proxysql.service
5、配置proxysql
[root@lxc ~]# cat /etc/proxysql.cnf
########################################################################################
datadir="/var/lib/proxysql"
errorlog="/var/lib/proxysql/proxysql.log"
admin_variables=
{
admin_credentials="admin:admin"
# mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
mysql_ifaces="0.0.0.0:6033"
# refresh_interval=2000
# debug=true
}
mysql_variables=
{
threads=4
max_connections=2048
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
# interfaces="0.0.0.0:6033;/tmp/proxysql.sock"
interfaces="0.0.0.0:3306"
default_schema="information_schema"
stacksize=1048576
server_version="5.5.30"
connect_timeout_server=3000
# make sure to configure monitor username and password
# https://github.com/sysown/proxysql/wiki/Global-variables#mysql-monitor_username-mysql-monitor_password
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_msec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}
# defines all the MySQL servers
mysql_servers =
(
{
address = "192.168.0.22" # 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 = 3 # no default, required
status = "ONLINE" # default: ONLINE
weight = 1 # default: 1
compression = 0 # default: 0
max_replication_lag = 10 # default 0 . If greater than 0 and replication lag passes such threshold, the server is shunned
},
{
address = "192.168.0.23" # 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 = 4 # no default, required
status = "ONLINE" # default: ONLINE
weight = 1 # default: 1
compression = 0 # default: 0
max_replication_lag = 10 # default 0 . If greater than 0 and replication lag passes such threshold, the server is shunned
}
)
# defines all the MySQL users
mysql_users:
(
{
username = "root" # no default , required
password = "admin123.com" # default: ''
default_hostgroup = 3 # default: 0
active = 1 # default: 1
}
)
#defines MySQL Query Rules
mysql_query_rules:
(
{
rule_id=1
active=1
match_pattern="^SELECT .* FOR UPDATE$"
destination_hostgroup=3
apply=1
},
{
rule_id=2
active=1
match_pattern="^SELECT"
destination_hostgroup=4
apply=1
}
)
mysql_replication_hostgroups=
(
{
writer_hostgroup=3
reader_hostgroup=4
comment="test repl 1"
}
)
[root@lxc ~]#
提示：proxysql配置檔案分幾個大段，admin_variables這個段裡主要配置管理相關變量，比如管理使用者名和密碼，監聽的位址和端口等等；mysql_variables配置proxysql連接配接後端mysql相關配置，裡面有連接配接後端的server監聽的端口，線程數，最大連接配接數，以及監控相關參數；mysql_servers配置後端mysql/mariadb伺服器的位址端口，以及權重，所屬組等；mysql_users配置連接配接後端mysql/mariadb所需的賬号和密碼以及預設連接配接到的組；mysql_query_rules配置查詢規則，那些語句是寫操作，那些語句是讀操作；mysql_replication_hostgroups配置寫操作對應的組号和讀操作對應組号；這裡需要注意一點，如果一個配置段中有多個大括号，互相之間用逗号隔離，如果是最後一個大括号需要把後面的逗号去掉；
6、在主從複制節點建立上面配置的賬号資訊
建立root賬号
[root@docker_node01 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 5.5.65-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select user,host,password from mysql.user;
+---------+-------------------+-------------------------------------------+
| user | host | password |
+---------+-------------------+-------------------------------------------+
| root | localhost | |
| root | docker\_node01.io | |
| root | 127.0.0.1 | |
| root | ::1 | |
| rpluser | 192.168.0.% | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
+---------+-------------------+-------------------------------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> grant all on *.* to 'root'@'192.168.0.%' identified by 'admin123.com' with grant option;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> select user,host,password from mysql.user;
+---------+-------------------+-------------------------------------------+
| user | host | password |
+---------+-------------------+-------------------------------------------+
| root | localhost | |
| root | docker\_node01.io | |
| root | 127.0.0.1 | |
| root | ::1 | |
| rpluser | 192.168.0.% | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
| root | 192.168.0.% | *2D9AC2437F9E59A51BE8BA89A3D59E76F32F55E8 |
+---------+-------------------+-------------------------------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]>
提示：在主節點建立賬号會自動同步到從節點，是以從節點可以不用再建立；
建立monitor賬号
MariaDB [(none)]> grant all on *.* to 'monitor'@'192.168.0.%' identified by 'monitor' with grant option;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> select user,host,password from mysql.user;
+---------+-------------------+-------------------------------------------+
| user | host | password |
+---------+-------------------+-------------------------------------------+
| root | localhost | |
| root | docker\_node01.io | |
| root | 127.0.0.1 | |
| root | ::1 | |
| rpluser | 192.168.0.% | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
| root | 192.168.0.% | *2D9AC2437F9E59A51BE8BA89A3D59E76F32F55E8 |
| monitor | 192.168.0.% | *1975D095AC033CAF4E1BF94F7202A9BBFEEB66F1 |
+---------+-------------------+-------------------------------------------+
7 rows in set (0.00 sec)
MariaDB [(none)]>
7、啟動proxysql
提示：可以看到我們配置的proxysql管理端監聽在6033，啟動4個線程來連接配接後端mariadb伺服器，對應的端口都已經處于監聽狀态了；
8、連接配接管理端口
[root@lxc ~]# mysql -uadmin -padmin -h192.168.0.21 -P6033
ERROR 1040 (42000): User 'admin' can only connect locally
[root@lxc ~]# mysql -uadmin -padmin -h127.0.0.1 -P6033
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> 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.001 sec)
MySQL [(none)]>
提示：管理接口預設隻能本地連接配接，但是我們配置管理接口監聽在本機所有位址上，是以連接配接時指定主機位址要使用127.0.0.1才可以，如果使用localhost，它預設會通過sock檔案去連接配接；從上面的結果可以看到，在proxysql上有5個庫，其中main庫主要存放我們剛才配置相關的表在裡面；如下
MySQL [(none)]> use main
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 [main]> show tables;
+----------------------------------------------------+
| tables |
+----------------------------------------------------+
| global_variables |
| mysql_aws_aurora_hostgroups |
| mysql_collations |
| mysql_firewall_whitelist_rules |
| mysql_firewall_whitelist_sqli_fingerprints |
| mysql_firewall_whitelist_users |
| mysql_galera_hostgroups |
| mysql_group_replication_hostgroups |
| mysql_query_rules |
| mysql_query_rules_fast_routing |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| proxysql_servers |
| restapi_routes |
| runtime_checksums_values |
| runtime_global_variables |
| runtime_mysql_aws_aurora_hostgroups |
| runtime_mysql_firewall_whitelist_rules |
| runtime_mysql_firewall_whitelist_sqli_fingerprints |
| runtime_mysql_firewall_whitelist_users |
| runtime_mysql_galera_hostgroups |
| 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_restapi_routes |
| runtime_scheduler |
| scheduler |
+----------------------------------------------------+
32 rows in set (0.001 sec)
MySQL [main]> select * from mysql_servers;
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 3 | 192.168.0.22 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
| 4 | 192.168.0.23 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.001 sec)
MySQL [main]>
提示：main庫中的表以runtime開頭的表示目前生效的配置，如果我們要運作時修改配置，我們需要先修改不是runtime開頭的表，然後通過load 加載到runtime開頭的表中，然後在save存儲到檔案；
proxysql的多層配置系統
+-------------------------+
| RUNTIME |
+-------------------------+
/|\ |
| |
[1] | [2] |
| \|/
+-------------------------+
| MEMORY |
+-------------------------+ _
/|\ | |\
| | \
[3] | [4] | \ [5]
| \|/ \
+-------------------------+ +-------------------------+
| DISK | | CONFIG FILE |
+-------------------------+ +-------------------------+
提示：從上面的圖可以看到proxysql的配置系統分三層，最上面一層是runtime層，該層主要是說通過讀取第二層的memory層，而memory主要通過disk或configfile層來擷取配置；第一次啟動proxysql時，它會從config file層讀取配置檔案内容到memory層，然後runtime層讀取memory層，随後我們就可以通過修改memory層，然後通過load加載到runtime層，或者save存儲到disk層；
在各層間移動配置
MySQL user相關操作：
LOAD MYSQL USERS FROM MEMORY / LOAD MYSQL USERS TO RUNTIME
将MySQL user從記憶體資料庫加載到運作時資料結構，反之亦然
SAVE MYSQL USERS TO MEMORY / SAVE MYSQL USERS FROM RUNTIME
将MySQL user從運作時持久化到記憶體資料庫
LOAD MYSQL USERS TO MEMORY / LOAD MYSQL USERS FROM DISK
将持久化的MySQL user從磁盤資料庫加載到記憶體資料庫
SAVE MYSQL USERS FROM MEMORY / SAVE MYSQL USERS TO DISK
将MySQL user從記憶體資料庫持久化到磁盤資料庫
LOAD MYSQL USERS FROM CONFIG
從配置檔案中将MySQL user加載到記憶體資料庫中
MySQL server相關操作：
LOAD MYSQL SERVERS FROM MEMORY / LOAD MYSQL SERVERS TO RUNTIME
将MySQL server從記憶體資料庫加載到運作時
SAVE MYSQL SERVERS TO MEMORY / SAVE MYSQL SERVERS FROM RUNTIME
将MySQL server從運作時持久化到記憶體資料庫
LOAD MYSQL SERVERS TO MEMORY / LOAD MYSQL SERVERS FROM DISK
将MySQL server從磁盤資料庫加載到記憶體資料庫
SAVE MYSQL SERVERS FROM MEMORY / SAVE MYSQL SERVERS TO DISK
将MySQL server從記憶體資料庫持久化到磁盤資料庫
LOAD MYSQL SERVERS FROM CONFIG
從配置檔案将伺服器加載到記憶體資料庫中
MySQL query rules相關操作：
LOAD MYSQL QUERY RULES FROM MEMORY / LOAD MYSQL QUERY RULES TO RUNTIME
将MySQL查詢規則從記憶體資料庫加載到運作時資料結構
SAVE MYSQL QUERY RULES TO MEMORY / SAVE MYSQL QUERY RULES FROM RUNTIME
将MySQL查詢規則從運作時資料結構持久化到記憶體資料庫
LOAD MYSQL QUERY RULES TO MEMORY / LOAD MYSQL QUERY RULES FROM DISK
将MySQL查詢規則從磁盤資料庫加載到記憶體資料庫
SAVE MYSQL QUERY RULES FROM MEMORY / SAVE MYSQL QUERY RULES TO DISK
将MySQL查詢規則從記憶體資料庫持久化到磁盤資料庫
LOAD MYSQL QUERY RULES FROM CONFIG
從配置檔案查詢規則加載到記憶體資料庫中
MySQL variables相關操作：
LOAD MYSQL VARIABLES FROM MEMORY / LOAD MYSQL VARIABLES TO RUNTIME
将MySQL變量從記憶體資料庫加載到運作時資料結構
SAVE MYSQL VARIABLES FROM MEMORY / SAVE MYSQL VARIABLES TO DISK
将MySQL變量從記憶體資料庫持久化到磁盤資料庫
LOAD MYSQL VARIABLES TO MEMORY / LOAD MYSQL VARIABLES FROM DISK
将MySQL變量從磁盤資料庫加載到記憶體資料庫
SAVE MYSQL VARIABLES TO MEMORY / SAVE MYSQL VARIABLES FROM RUNTIME
将MySQL變量從運作時資料結構持久存儲到記憶體資料庫中
LOAD MYSQL VARIABLES FROM CONFIG
将配置檔案變量加載到記憶體資料庫中
admin variables相關操作：
LOAD ADMIN VARIABLES FROM MEMORY / LOAD ADMIN VARIABLES TO RUNTIME
将記憶體資料庫中的管理變量加載到運作時
SAVE ADMIN VARIABLES TO MEMORY / SAVE ADMIN VARIABLES FROM RUNTIME
将管理變量從運作時持久化到記憶體資料庫
LOAD ADMIN VARIABLES TO MEMORY / LOAD ADMIN VARIABLES FROM DISK
将管理變量從磁盤資料庫加載到記憶體資料庫
SAVE ADMIN VARIABLES FROM MEMORY / SAVE ADMIN VARIABLES TO DISK
将管理變量從記憶體資料庫持久化到磁盤資料庫
LOAD ADMIN VARIABLES FROM CONFIG
将配置檔案管理變量加載到記憶體資料庫中
注意：以上指令允許使用以下快捷方式：
MEM for MEMORY
RUN for RUNTIME
例如，這兩個指令是等效的：
SAVE ADMIN VARIABLES TO MEMORY
SAVE ADMIN VARIABLES TO MEM
提示：以上指令在配置更改加載到RUNTIME之前不會激活任何修改，也就是說隻有runtime中的配置生效；
測試：線上增加後端mysql server的位址
[root@lxc ~]# mysql -uadmin -padmin -h127.0.0.1 -P6033
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> select * from mysql_servers;
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 3 | 192.168.0.22 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
| 4 | 192.168.0.23 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.001 sec)
MySQL [(none)]> insert into mysql_servers values (4,'192.168.0.21',3307,0,'ONLINE',1,0,1000,10,0,0,'');
Query OK, 1 row affected (0.001 sec)
MySQL [(none)]> select * from mysql_servers;
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 3 | 192.168.0.22 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
| 4 | 192.168.0.23 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
| 4 | 192.168.0.21 | 3307 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.001 sec)
MySQL [(none)]>
提示：以上操作隻在memory層中修改，并未加載到runtime，是以在runtime_mysql_servers表中還是兩台後端server
加載mysql server memory層到runtime層
MySQL [(none)]> show tables;
+----------------------------------------------------+
| tables |
+----------------------------------------------------+
| global_variables |
| mysql_aws_aurora_hostgroups |
| mysql_collations |
| mysql_firewall_whitelist_rules |
| mysql_firewall_whitelist_sqli_fingerprints |
| mysql_firewall_whitelist_users |
| mysql_galera_hostgroups |
| mysql_group_replication_hostgroups |
| mysql_query_rules |
| mysql_query_rules_fast_routing |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| proxysql_servers |
| restapi_routes |
| runtime_checksums_values |
| runtime_global_variables |
| runtime_mysql_aws_aurora_hostgroups |
| runtime_mysql_firewall_whitelist_rules |
| runtime_mysql_firewall_whitelist_sqli_fingerprints |
| runtime_mysql_firewall_whitelist_users |
| runtime_mysql_galera_hostgroups |
| 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_restapi_routes |
| runtime_scheduler |
| scheduler |
+----------------------------------------------------+
32 rows in set (0.001 sec)
MySQL [(none)]> select * from runtime_mysql_servers;
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 3 | 192.168.0.22 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
| 4 | 192.168.0.23 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.006 sec)
MySQL [(none)]> select * from mysql_servers;
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 3 | 192.168.0.22 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
| 4 | 192.168.0.23 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
| 4 | 192.168.0.21 | 3307 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.001 sec)
MySQL [(none)]> load mysql servers to runtime;
Query OK, 0 rows affected (0.015 sec)
MySQL [(none)]> select * from runtime_mysql_servers;
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 3 | 192.168.0.22 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
| 4 | 192.168.0.21 | 3307 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
| 4 | 192.168.0.23 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.006 sec)
MySQL [(none)]>
提示：可以看到執行load操作後，runtime_mysql_servers就加載進去了；此時我們就把memory層的資訊加載到runtime層生效了；
驗證：通過連接配接proxysql所在主機的3306端口，執行寫操作，看看是否能夠同步到其他從節點？
提示：可以看到在proxysql所在主機連接配接3306線程，是可以連接配接到主庫，因為預設配置連接配接是到主庫，然後在其上建立一個mydb的資料庫，也能夠及時的同步到其他從庫；
測試：讀寫操作是否分别排程到不同的節點上？
[root@lxc ~]# for i in {1..10} ;do mysql -uroot -padmin123.com -P3306 -h192.168.0.21 -e "select @@server_id" ; done
+-------------+
| @@server_id |
+-------------+
| 2 |
+-------------+
+-------------+
| @@server_id |
+-------------+
| 2 |
+-------------+
+-------------+
| @@server_id |
+-------------+
| 2 |
+-------------+
+-------------+
| @@server_id |
+-------------+
| 3 |
+-------------+
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
+-------------+
| @@server_id |
+-------------+
| 3 |
+-------------+
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
+-------------+
| @@server_id |
+-------------+
| 3 |
+-------------+
[root@lxc ~]# for i in {1..10} ;do mysql -uroot -padmin123.com -P3306 -h192.168.0.21 -e "select @@server_id for update" ; done
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
[root@lxc ~]#
提示：可以看到當執行有寫鎖時，就立刻把操作排程到server_id=1的主庫上去操作；讀操作是随機排程到各個節點上進行處理；
作者：Linux-1874
出處：https://www.cnblogs.com/qiuhom-1874/
本文版權歸作者和部落格園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接配接，否則保留追究法律責任的權利.