ProxySQL是靈活強大的MySQL代理層, 是一個能實實在在用在生產環境的MySQL中介軟體，可以實現讀寫分離，支援 Query 路由功能，支援動態指定某個 SQL 進行 cache，支援動態載入配置、故障切換和一些 SQL的過濾功能。還有一些同類產品比如 DBproxy、MyCAT、OneProxy 等。但經過反覆對比和測試之後，還是覺得ProxySQL是一款效能不諳，靠譜穩定的MySQL 中介軟體產品 ！
ProxySQL的亮點所在
- 幾乎所有的配置均可線上更改（其配置資料基於SQLite儲存），無需重啟proxysql
- 基於正則和client_addr的強大和靈活的路由規則
- 詳細的狀態統計，統計結果和pt-query-digest對慢日誌的分析結果類似，相當於有了統一的檢視sql效能和sql語句統計的入口（Designed by a DBA for DBAs）
- 自動重連和重新執行機制(auto-reconnect and automatic re-execution of queries using it’s Connections Pool ): 若一個請求在連結或執行過程中意外中斷，proxysql會根據其內部機制重新執行該操作
- query cache功能：比mysql自帶QC更靈活，可在mysql_query_rules表中依據digest,match_pattern,client_addr等維度控制哪類語句可以快取
- 支援連線池（connection pool）並且支援multiplexing,區別於atlas之流的連線池實現。
ProxySQL的特點
ProxySQL是一個高效能的MySQL中介軟體，擁有強大的規則引擎。具有以下特性：
- 連線池，而且是 multiplexing；
- 主機和使用者的最大連線數限制；
- 自動下線後端DB；
- 延遲超過閥值
- ping 延遲超過閥值
- 網路不通或當機
- 強大的規則路由引擎；
- 實現讀寫分離
- 查詢重寫
- sql流量映象
- 支援prepared statement；
- 支援Query Cache；
- 支援負載均衡，與gelera結合自動failover；
- 將所有配置儲存寫入到SQLit表中。
- 支援動態載入配置，即一般可以線上修改配置，但有少部分引數還是需要重啟來生效。
- 支援query cache。
- 支援對query的路由，可以針對某個語句進行分配去哪個例項執行。
- 不支援分表，可以分庫，但是利用規則配置實現分表。
如上可知，ProxySQL集合了很多優秀特性於一身，那麼它的缺點呢就是專案不夠成熟，好在官方網站一直在及時更新，並且受到 Percona 官方的支援。
ProxySQL多層管理配置設計 (有三層配置)
- runtime：執行中使用的配置檔案
- memory：提供使用者動態修改配置檔案
- disk：將修改的配置儲存到磁碟SQLit表中（即：proxysql.db）
- config：一般不使用它（即：proxysql.cnf）
ProxySQL執行機制草圖如下：
ProxySQL有一個完備的配置系統，配置ProxySQL是基於sql命令的方式完成的。ProxySQL支援配置修改之後的線上儲存、應用，不需要重啟即可生效。整個配置系統分三層設計。
整個配置系統分為三層，如下圖所示:
ProxySQL配置系統分為三層的目的：
1) 自動更新;
2) 儘可能的不重啟proxysql就可以修改配置;
3) 方便回滾錯誤配置;
簡單說就是配置proxysql分為三個級別，RUNTIME是即時生效的，MEMORY是儲存在記憶體中但並不立即生效的，DISK|CONFIG FILE是持久化或寫在配置檔案中的。
這三個級別的配置檔案互不干擾，在某個層級修改了配置檔案，想要載入或儲存到另一個層級，需要額外的LOAD或SAVE操作："LOAD xx_config FROM xx_level | LOAD xx_config TO xx_level | SAVE xx_config TO xx_level | SAVE xx_config FROM xx_level"，達到載入配置或者持久化配置的目的。這三層中每層的功能與含義如下：
- RUNTIME層
代表的是ProxySQL當前生效的配置，包括 global_variables, mysql_servers, mysql_users, mysql_query_rules。無法直接修改這裡的配置，必須要從下一層load進來。該層級的配置時在proxysql管理庫(sqlite)的main庫中以runtime_開頭的表，這些表的資料庫無法直接修改，只能從其他層級載入；該層代表的是ProxySQL當前生效的正在使用的配置，包括global_variables, mysql_servers, mysql_users, mysql_query_rules表。無法直接修改這裡的配置，必須要從下一層load進來。也就是說RUNTIME這個頂級層，是proxysql執行過程中實際使用的那一份配置，這一份配置會直接影響到生產環境的，所以要將配置載入進RUNTIME層時需要三思而行。
- MEMORY層
是平時在mysql命令列修改的 main 裡頭配置，可以認為是SQLite資料庫在記憶體的映象。該層級的配置在main庫中以mysql_開頭的表以及global_variables表，這些表的資料可以直接修改；使用者可以通過MySQL客戶端連線到此介面（admin介面），然後可以在mysql命令列查詢不同的表和資料庫，並修改各種配置，可以認為是SQLite資料庫在記憶體的映象。也就是說MEMORY這個中間層，上面接著生產環境層RUNTIME，下面接著持久化層DISK和CONFIG FILE。MEMORY層是我們修改proxysql的唯一正常入口。一般來說在修改一個配置時，首先修改Memory層，確認無誤後再接入RUNTIME層，最後持久化到DISK和CONFIG FILE層。也就是說memeory層裡面的配置隨便改，不影響生產，也不影響磁碟中儲存的資料。通過admin介面可以修改mysql_servers、mysql_users、mysql_query_rules、global_variables等表的資料。
- DISK|CONFIG FILR層
持久儲存的那份配置，一般在$(DATADIR)/proxysql.db，在重啟的時候會從硬碟裡載入。 /etc/proxysql.cnf檔案只在第一次初始化的時候用到，完了後，如果要修改監聽埠，還是需要在管理命令列裡修改，再 save 到硬碟。該層級的配置在磁碟上的sqlite庫或配置檔案裡。DISK/CONFIG FILE層表示持久儲存的那份配置，持久層對應的磁碟檔案是$(DATADIR)/proxysql.db，在重啟ProxySQL的時候，會從proxysql.db檔案中載入資訊。而 /etc/proxysql.cnf檔案只在第一次初始化的時候使用，之後如果要修改配置，就需要在管理埠的SQL命令列裡進行修改，然後再save到硬碟。 也就是說DISK和CONFIG FILE這一層是持久化層，我們做的任何配置更改，如果不持久化下來，重啟後，配置都將丟失。
需要注意
1) ProxySQL每一個配置項在三層中都存在，但是這三層是互相獨立的，也就是說proxysql可以同時擁有三份配置，每層都是獨立的，可能三份配置都不一樣，也可能三份都一樣。
2) RUNTIME層代表 ProxySQL 當前生效的正在使用的配置，無法直接修改這裡的配置，必須要從下一層 "load" 進來。
3) MEMORY這一層上面連線 RUNTIME 層，下面連線持久化層。在這層可以正常操作 ProxySQL 配置，隨便修改，不會影響生產環境。修改一個配置一般都是先在 MEMORY 層完成，然後確認正常之後再載入到 RUNTIME 和持久化到磁碟上。
4) DISK 和 CONFIG FILE層持久化配置資訊，重啟後記憶體中的配置資訊會丟失，所以需要將配置資訊保留在磁碟中。重啟時，可以從磁碟快速載入回來。
ProxySQL配置檔案的修改流程一般是：
- 啟動時：先修改必要的CONFIG FILE配置，比如管理埠，然後啟動；
- 其他配置：修改MEMORY中的表，然後載入到RUNTIME並持久化。
ProxySQL具有一個複雜但易於使用的配置系統，可以滿足以下需求：
- 允許輕鬆動態更新配置（這是為了讓ProxySQL使用者可以在需要零當機時間配置的大型基礎架構中使用它）。與MySQL相容的管理介面可用於此目的。
- 允許儘可能多的配置專案動態修改，而不需要重新啟動ProxySQL程式
- 可以毫不費力地回滾無效配置
- 這是通過多級配置系統實現的，其中設定從執行時移到記憶體，並根據需要持久儲存到磁碟。
一般，修改的配置都是在memory層。可以load到runtime，使配置在不用重啟proxysql的情況下也可以生效，也可以save到disk，將對配置的修改持久化！
需要修改配置時，直接操作的是 MEMORAY，以下命令可用於載入或儲存 users (mysql_users): (序號對應上圖“執行機制”草圖)
[1]: LOAD MYSQL USERS TO RUNTIME / LOAD MYSQL USERS FROM MEMORY #常用。將修改後的配置(在memory層)用到實際生產 [2]: SAVE MYSQL USERS TO MEMORY / SAVE MYSQL USERS FROM RUNTIME #將生產配置拉一份到memory中 [3]: LOAD MYSQL USERS TO MEMORY / LOAD MYSQL USERS FROM DISK #將磁碟中持久化的配置拉一份到memory中來 [4]: SAVE MYSQL USERS TO DISK / SAVE MYSQL USERS FROM MEMORY #常用。將memoery中的配置儲存到磁碟中去 [5]: LOAD MYSQL USERS FROM CONFIG #將配置檔案中的配置載入到memeory中
個人還是比較習慣用 TO，記住往上層是 LOAD，往下層是 SAVE。以下命令載入或儲存servers (mysql_servers):
[1]: LOAD MYSQL SERVERS TO RUNTIME #常用，讓修改的配置生效 [2]: SAVE MYSQL SERVERS TO MEMORY [3]: LOAD MYSQL SERVERS TO MEMORY [4]: SAVE MYSQL SERVERS TO DISK #常用，將修改的配置持久化 [5]: LOAD MYSQL SERVERS FROM CONFIG
後面的使用方法也基本相同，一併列出。以下命令載入或儲存query rules (mysql_query_rules):
[1]: load mysql query rules to run #常用 [2]: save mysql query rules to mem [3]: load mysql query rules to mem [4]: save mysql query rules to disk #常用 [5]: load mysql query rules from config
以下命令載入或儲存 mysql variables (global_variables):
[1]: load mysql variables to runtime [2]: save mysql variables to memory [3]: load mysql variables to memory [4]: save mysql variables to disk [5]: load mysql variables from config
以下命令載入或儲存admin variables (select * from global_variables where variable_name like 'admin-%'):
[1]: load admin variables to runtime [2]: save admin variables to memory [3]: load admin variables to memory [4]: save admin variables to disk [5]: load admin variables from config
ProxySQL啟動過程總結:
當proxysql啟動時，首先讀取配置檔案CONFIG FILE(/etc/proxysql.cnf)，然後從該配置檔案中獲取datadir，datadir中配置的是sqlite的資料目錄。如果該目錄存在，且sqlite資料檔案存在，那麼正常啟動，將sqlite中的配置項讀進記憶體，並且載入進RUNTIME，用於初始化proxysql的執行。如果datadir目錄下沒有sqlite的資料檔案，proxysql就會使用config file中的配置來初始化proxysql，並且將這些配置儲存至資料庫。sqlite資料檔案可以不存在，/etc/proxysql.cnf檔案也可以為空，但/etc/proxysql.cnf配置檔案必須存在，否則，proxysql無法啟動。
一、 ProxySQL 安裝 (兩種方式)
1) 採用yum方式安裝
[root@mysql-proxy ~]# vim /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
執行安裝
[root@mysql-proxy ~]# yum clean all
[root@mysql-proxy ~]# yum makecache
[root@mysql-proxy ~]# yum -y install proxysql
[root@mysql-proxy ~]# proxysql --version
ProxySQL version 1.4.13-15-g69d4207, codename Truls
啟動ProxySQL
[root@mysql-proxy ~]# chkconfig proxysql on
[root@mysql-proxy ~]# systemctl start proxysql
[root@mysql-proxy ~]# systemctl status proxysql
啟動後會監聽兩個埠，
預設為6032和6033。6032埠是ProxySQL的管理埠，6033是ProxySQL對外提供服務的埠 (即連線到轉發後端的真正資料庫的轉發埠)。
[root@mysql-proxy ~]# netstat -tunlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 23940/proxysql
tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 23940/proxysql
2）採用rpm包方式安裝
proxysql的rpm包下載地址: https://pan.baidu.com/s/1S1_b5DKVCpZSOUNmtCXrrg
提取密碼: 5t1c
[root@mysql-proxy ~]# wget https://github.com/sysown/proxysql/releases/download/v1.4.8/proxysql-1.4.8-1-centos7.x86_64.rpm
[root@mysql-proxy ~]# rpm -ivh proxysql-1.4.8-1-centos7.x86_64.rpm --force
[root@mysql-proxy ~]# /etc/init.d/proxysql start
Starting ProxySQL: DONE!
[root@mysql-proxy ~]# ss -lntup|grep proxy
tcp LISTEN 0 128 *:6032 *:* users:(("proxysql",pid=2943,fd=24))
tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=2943,fd=22))
tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=2943,fd=21))
tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=2943,fd=20))
tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=2943,fd=19))
如上可以看出轉發埠6033是啟動了四個執行緒
==============================================================
以上兩種方式採用任何一種都可以順利安裝proxysql外掛。
另外，記得在proxysql伺服器上安裝mysql客戶端，用於在本機連線到ProxySQL的管理介面
[root@mysql-proxy ~]# vim /etc/yum.repos.d/mariadb.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3.5/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
安裝mysql-clinet客戶端
[root@mysql-proxy ~]# yum install -y MariaDB-client
--------------------------------------------------------------------------------------------------------------------------------------------------------
如果遇到報錯：
Error: MariaDB-compat conflicts with 1:mariadb-libs-5.5.60-1.el7_5.x86_64
You could try using --skip-broken to work around the problem
You could try running: rpm -Va --nofiles --nodigest
解決辦法：
[root@mysql-proxy ~]# rpm -qa|grep mariadb*
mariadb-libs-5.5.56-2.el7.x86_64
[root@mysql-proxy ~]# rpm -e mariadb-libs-5.5.56-2.el7.x86_64 --nodeps
[root@mysql-proxy ~]# yum install -y MariaDB-client
ProxySQL配置
ProxySQL有配置檔案/etc/proxysql.cnf和配置資料庫檔案/var/lib/proxysql/proxysql.db。這裡需要特別注意：如果存在如果存在"proxysql.db"檔案(在/var/lib/proxysql目錄下)，則ProxySQL服務只有在第一次啟動時才會去讀取proxysql.cnf檔案並解析；後面啟動會就不會讀取proxysql.cnf檔案了！如果想要讓proxysql.cnf檔案裡的配置在重啟proxysql服務後生效(即想要讓proxysql重啟時讀取並解析proxysql.cnf配置檔案)，則需要先刪除/var/lib/proxysql/proxysql.db資料庫檔案，然後再重啟proxysql服務。這樣就相當於初始化啟動proxysql服務了，會再次生產一個純淨的proxysql.db資料庫檔案(如果之前配置了proxysql相關路由規則等，則就會被抹掉)。 官方推薦用admin interface方式！(即在proxysql本機使用mysql客戶端連線管理埠)
[root@mysql-proxy ~]# egrep -v "^#|^$" /etc/proxysql.cnf
datadir="/var/lib/proxysql" #資料目錄
admin_variables=
{
admin_credentials="admin:admin" #連線管理端的使用者名稱與密碼
mysql_ifaces="0.0.0.0:6032" #管理埠，用來連線proxysql的管理資料庫
}
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" #指定轉發埠，用於連線後端mysql資料庫的，相當於代理作用
default_schema="information_schema"
stacksize=1048576
server_version="5.5.30" #指定後端mysql的版本
connect_timeout_server=3000
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
}
mysql_servers =
(
)
mysql_users:
(
)
mysql_query_rules:
(
)
scheduler=
(
)
mysql_replication_hostgroups=
(
)
proxysql的資料目錄
[root@mysql-proxy ~]# ll /var/lib/proxysql/
total 1014052
-rw------- 1 root root 122880 Jan 25 14:33 proxysql.db
-rw------- 1 root root 1023288179 Jan 28 12:30 proxysql.log
-rw-r--r-- 1 root root 6 Jan 25 14:20 proxysql.pid
-rw------- 1 root root 1736704 Jan 28 12:29 proxysql_stats.db
檢視main庫（預設登陸後即在此庫）的global_variables表資訊
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.000 sec)
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_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.000 sec)
MySQL [main]> select * from global_variables;
+-----------------------------------------------------+---------------------------+
| variable_name | variable_value |
+-----------------------------------------------------+---------------------------+
| mysql-shun_on_failures | 5 |
| mysql-shun_recovery_time_sec | 10 |
| mysql-query_retries_on_failure | 1 |
| mysql-connect_retries_delay | 1 |
| mysql-connection_delay_multiplex_ms | 0 |
| mysql-connection_max_age_ms | 0 |
| mysql-connect_timeout_server_max | 10000 |
| mysql-eventslog_filename | |
| mysql-eventslog_filesize | 104857600 |
| mysql-default_charset | utf8 |
| mysql-free_connections_pct | 10 |
| mysql-session_idle_ms | 1000 |
| mysql-client_found_rows | true |
| 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_replication_lag_use_percona_heartbeat | |
| mysql-monitor_query_interval | 60000 |
| mysql-monitor_query_timeout | 100 |
| mysql-monitor_slave_lag_when_null | 60 |
| mysql-monitor_wait_timeout | true |
| mysql-monitor_writer_is_also_reader | true |
| mysql-max_allowed_packet | 4194304 |
| mysql-throttle_connections_per_sec_to_hostgroup | 1000000 |
| mysql-max_transaction_time | 14400000 |
| mysql-multiplexing | true |
| mysql-forward_autocommit | false |
| mysql-enforce_autocommit_on_reads | false |
| mysql-autocommit_false_not_reusable | false |
| mysql-autocommit_false_is_transaction | false |
| mysql-verbose_query_error | false |
| mysql-hostgroup_manager_verbose | 1 |
| mysql-threshold_query_length | 524288 |
| mysql-threshold_resultset_size | 4194304 |
| mysql-query_digests_max_digest_length | 2048 |
| mysql-query_digests_max_query_length | 65000 |
| mysql-wait_timeout | 28800000 |
| mysql-throttle_max_bytes_per_second_to_client | 2147483647 |
| mysql-throttle_ratio_server_to_client | 0 |
| mysql-max_stmts_per_connection | 20 |
| mysql-max_stmts_cache | 10000 |
| mysql-mirror_max_concurrency | 16 |
| mysql-mirror_max_queue_length | 32000 |
| mysql-default_max_latency_ms | 1000 |
| mysql-query_processor_iterations | 0 |
| mysql-query_processor_regex | 1 |
| mysql-long_query_time | 1000 |
| mysql-query_cache_size_MB | 256 |
| mysql-poll_timeout_on_failure | 100 |
| mysql-server_capabilities | 45578 |
| mysql-session_idle_show_processlist | true |
| mysql-query_digests | true |
| mysql-query_digests_lowercase | false |
| mysql-servers_stats | true |
| mysql-default_reconnect | true |
| mysql-ssl_p2s_ca | |
| mysql-ssl_p2s_cert | |
| mysql-ssl_p2s_key | |
| mysql-ssl_p2s_cipher | |
| mysql-init_connect | |
| mysql-default_sql_mode | |
| mysql-default_time_zone | SYSTEM |
| mysql-connpoll_reset_queue_length | 50 |
| mysql-stats_time_backend_query | false |
| mysql-stats_time_query_processor | false |
| mysql-threads | 4 |
| mysql-max_connections | 2048 |
| mysql-default_query_delay | 0 |
| mysql-default_query_timeout | 36000000 |
| mysql-have_compress | true |
| mysql-poll_timeout | 2000 |
| mysql-interfaces | 0.0.0.0:6033 |
| mysql-default_schema | information_schema |
| mysql-stacksize | 1048576 |
| mysql-server_version | 5.5.30 |
| mysql-connect_timeout_server | 3000 |
| mysql-monitor_username | proxysql |
| mysql-monitor_password | proxysql |
| 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 |
| mysql-ping_interval_server_msec | 120000 |
| mysql-ping_timeout_server | 500 |
| mysql-commands_stats | true |
| mysql-sessions_sort | true |
| mysql-connect_retries_on_failure | 10 |
| admin-stats_credentials | stats:stats |
| admin-stats_mysql_connections | 60 |
| admin-stats_mysql_connection_pool | 60 |
| admin-stats_mysql_query_cache | 60 |
| admin-stats_system_cpu | 60 |
| admin-stats_system_memory | 60 |
| admin-telnet_admin_ifaces | (null) |
| admin-telnet_stats_ifaces | (null) |
| admin-refresh_interval | 2000 |
| admin-read_only | false |
| admin-hash_passwords | true |
| admin-cluster_username | |
| admin-cluster_password | |
| admin-cluster_check_interval_ms | 1000 |
| admin-cluster_check_status_frequency | 10 |
| admin-cluster_mysql_query_rules_diffs_before_sync | 3 |
| admin-cluster_mysql_servers_diffs_before_sync | 3 |
| admin-cluster_mysql_users_diffs_before_sync | 3 |
| admin-cluster_proxysql_servers_diffs_before_sync | 3 |
| admin-cluster_mysql_query_rules_save_to_disk | true |
| admin-cluster_mysql_servers_save_to_disk | true |
| admin-cluster_mysql_users_save_to_disk | true |
| admin-cluster_proxysql_servers_save_to_disk | true |
| admin-checksum_mysql_query_rules | true |
| admin-checksum_mysql_servers | true |
| admin-checksum_mysql_users | true |
| admin-web_enabled | false |
| admin-web_port | 6080 |
| admin-admin_credentials | admin:admin|
| admin-mysql_ifaces | 0.0.0.0:6032 |
| admin-version | 1.4.8-32-g669c149 |
+-----------------------------------------------------+---------------------------+
125 rows in set (0.003 sec)
登陸成功後，可通過對main庫（預設登陸後即在此庫）的global_variables表中的"admin-admin_credentials" 和 "admin-mysql_ifaces"
兩個變數進行更改來修改登入認證! 比如說修改密碼或定義一個非admin的使用者用於遠端登入(下面會說到)。
proxysql的6032埠是管理入口，賬號密碼是admin(可以動態修改),允許客戶端連線；6033埠就是客戶端入口，賬號密碼通過管理介面去設定。在proxysql本機使用mysql客戶端連線到ProxySQL的管理介面(admin interface), 該介面的預設管理員使用者和密碼都是admin。
mysql_ifaces
也就是說proxysql有一個admin介面專門來做配置，相當於一個mysql shell可以通過sql來讓配置實時生效。
mysql_ifaces配置了允許連線proxysql的ip和port
[root@mysql-proxy ~]# vim /etc/proxysql.cnf
........
# 將admin_variables中的mysql_ifaces修改成允許遠端訪問
# mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
mysql_ifaces="0.0.0.0:6032"
如果ip配置為0.0.0.0表示不限制ip，但是出於安全考慮，admin使用者無論怎麼設定都只能在本機登入!!!
admin_credentials
這個key儲存所有可以操作proxysql的使用者名稱和密碼，格式為：user:pass;user1:pass1，這裡可以修改密碼或定義一個非admin的使用者用於遠端登入。 前提是保證想要管理proxysql的機器安裝有mysql client客戶端！
先在proxysql本機登入 (因為初始賬號密碼是admin:admin，只能在本機登入), 這裡的proxysql本機地址是172.16.60.214 修改遠端連線proxysql管理埠的賬號和密碼radmin:radmin. [root@mysql-proxy ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 34 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)]> update global_variables set variable_value = 'admin:admin;radmin:radmin' where variable_name = 'admin-admin_credentials'; Query OK, 1 row affected (0.002 sec) MySQL [(none)]> LOAD ADMIN VARIABLES TO RUNTIME; Query OK, 0 rows affected (0.000 sec) MySQL [(none)]> SAVE ADMIN VARIABLES TO DISK; Query OK, 31 rows affected (0.077 sec) 這樣就可以使用下面的命令在其他機器上使用radmin使用者登入（其他機器上需要有mysql client） [root@MGR-node3 ~]# mysql -uradmin -pradmin -h172.16.60.214 -P6032 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 35 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> 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)
ProxySQL的庫、表說明 （預設管理埠是6032，客戶端服務埠是6033。預設的使用者名稱密碼都是 admin）
通過管理埠6032去連線的 (注意, 下面連線命令中後面的--prompt 'admin'欄位可以不加，也是可以登入進去的) [root@mysql-proxy ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 或者 [root@mysql-proxy ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 --prompt 'admin> ' Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 33 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. 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.000 sec) ProxySQL提供了幾個庫，每個庫都有各自的意義； - main 記憶體配置資料庫，表裡存放後端db例項、使用者驗證、路由規則等資訊。表名以 runtime_開頭的表示proxysql當前執行的配置內容， 不能通過dml語句修改，只能修改對應的不以 runtime_ 開頭的（在記憶體）裡的表，然後 LOAD 使其生效， SAVE 使其存到硬碟以供下次重啟載入。 - disk 是持久化到硬碟的配置，sqlite資料檔案。 - stats 是proxysql執行抓取的統計資訊，包括到後端各命令的執行次數、流量、processlist、查詢種類彙總/執行時間等等。 - monitor 庫儲存 monitor 模組收集的資訊，主要是對後端db的健康/延遲檢查。 1) main 庫 （disk庫的表欄位和main一樣） 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.001 sec) 常用的幾個表介紹 =============================================== global_variables 設定變數，包括監聽的埠、管理賬號等。 mysql_collations 相關字符集和校驗規則。 mysql_query_rules 定義查詢路由規則。 mysql_replication_hostgroups 監視指定主機組中所有伺服器的read_only值，並且根據read_only的值將伺服器分配給寫入器或讀取器主機組。ProxySQL monitor模組會監控hostgroups 後端所有servers 的read_only 變數，如果發現從庫的read_only變為0、主庫變為1，則認為角色互換了，自動改寫mysql_servers表裡面 hostgroup關係， 達到自動 Failover 效果。 mysql_servers 設定後端MySQL的表 mysql_users 配置後端資料庫的程式賬號和監控賬號。 scheduler 排程器是一個類似於cron的實現，整合在ProxySQL中，具有毫秒的粒度。通過指令碼檢測來設定ProxySQL。 2）stats庫 MySQL [(none)]> show tables from stats; +--------------------------------------+ | tables | +--------------------------------------+ | global_variables | | stats_memory_metrics | | stats_mysql_commands_counters | | stats_mysql_connection_pool | | stats_mysql_connection_pool_reset | | stats_mysql_global | | stats_mysql_prepared_statements_info | | stats_mysql_processlist | | stats_mysql_query_digest | | stats_mysql_query_digest_reset | | stats_mysql_query_rules | | stats_mysql_users | | stats_proxysql_servers_checksums | | stats_proxysql_servers_metrics | | stats_proxysql_servers_status | +--------------------------------------+ 15 rows in set (0.001 sec) 常用的幾個表介紹 =============================================== stats_mysql_commands_counters 統計各種SQL型別的執行次數和時間，通過引數mysql-commands_stats控制開關，預設是ture。 stats_mysql_connection_pool 連線後端MySQL的連線資訊。 stats_mysql_processlist 類似MySQL的show processlist的命令，檢視各執行緒的狀態。 stats_mysql_query_digest 表示SQL的執行次數、時間消耗等。通過變數mysql-query_digests控制開關，預設是開。 stats_mysql_query_rules 路由命中次數統計。 3）monitor庫 MySQL [(none)]> show tables from monitor; +------------------------------------+ | tables | +------------------------------------+ | mysql_server_connect_log | | mysql_server_group_replication_log | | mysql_server_ping_log | | mysql_server_read_only_log | | mysql_server_replication_lag_log | +------------------------------------+ 5 rows in set (0.000 sec) 常用的幾個表介紹 =============================================== mysql_server_connect_log 連線到所有MySQL伺服器以檢查它們是否可用，該表用來存放檢測連線的日誌。 mysql_server_ping_log 使用mysql_ping API ping後端MySQL伺服器，檢查它們是否可用，該表用來存放ping的日誌。 mysql_server_replication_lag_log 後端MySQL服務主從延遲的檢測。 runtime_開頭的是執行時的配置，這些是不能修改的。要修改ProxySQL的配置，需要修改了非runtime_表， 修改後必須執行"LOAD ... TO RUNTIME"才能載入到RUNTIME生效，執行save ... to disk才能將配置持久化儲存到磁碟。
global_variables 有80多個變數可以設定，其中就包括監聽的埠、管理賬號、禁用monitor等
(admin@127.0.0.1:6032) [(none)]> show tables;
+--------------------------------------------+
| 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.001 sec)
(admin@127.0.0.1:6032) [(none)]> show tables from stats;
+--------------------------------------+
| tables |
+--------------------------------------+
| global_variables |
| stats_memory_metrics |
| stats_mysql_commands_counters |
| stats_mysql_connection_pool |
| stats_mysql_connection_pool_reset |
| stats_mysql_global |
| stats_mysql_prepared_statements_info |
| stats_mysql_processlist |
| stats_mysql_query_digest |
| stats_mysql_query_digest_reset |
| stats_mysql_query_rules |
| stats_mysql_users |
| stats_proxysql_servers_checksums |
| stats_proxysql_servers_metrics |
| stats_proxysql_servers_status |
+--------------------------------------+
15 rows in set (0.000 sec)
(admin@127.0.0.1:6032) [(none)]> show create table mysql_servers\G;
*************************** 1. row ***************************
table: mysql_servers
Create Table: CREATE TABLE mysql_servers (
hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0,
hostname VARCHAR NOT NULL,
port INT NOT NULL DEFAULT 3306,
status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',
weight INT CHECK (weight >= 0) NOT NULL DEFAULT 1,
compression INT CHECK (compression >=0 AND compression <= 102400) NOT NULL DEFAULT 0,
max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000,
max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0,
use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0,
max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0,
comment VARCHAR NOT NULL DEFAULT '',
PRIMARY KEY (hostgroup_id, hostname, port) )
1 row in set (0.000 sec)
ERROR: No query specified
(admin@127.0.0.1:6032) [(none)]> select * from mysql_servers;
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 172.16.60.211 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 172.16.60.212 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 172.16.60.213 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.000 sec)
- hostgroup_id: ProxySQL通過 hostgroup (下稱HG) 的形式組織後端db例項。一個 HG 代表同屬於一個角色
- 該表的主鍵是 (hostgroup_id, hostname, port)，可以看到一個 hostname:port 可以在多個hostgroup裡面，如上面的 10.0.100.100:3307，這樣可以避免 HG 1000 的從庫全都不可用時，依然可以把讀請求發到主庫上。
- 一個 HG 可以有多個例項，即多個從庫，可以通過 weight 分配權重
- hostgroup_id 0 是一個特殊的HG，路由查詢的時候，沒有匹配到規則則預設選擇 HG 0
- status:
- ONLINE: 當前後端例項狀態正常
- SHUNNED: 臨時被剔除，可能因為後端 too many connections error，或者超過了可容忍延遲閥值 max_replication_lag
- OFFLINE_SOFT: “軟離線”狀態，不再接受新的連線，但已建立的連線會等待活躍事務完成。
- OFFLINE_HARD: “硬離線”狀態，不再接受新的連線，已建立的連線或被強制中斷。當後端例項當機或網路不可達，會出現。
- max_connections: 允許連線到該後端mysql例項的最大連線數。不要大於MySQL設定的 max_connections，如果後端例項 hostname:port 在多個 hostgroup 裡，以較大者為準，而不是各自獨立允許的最大連線數。
- max_replication_lag: 允許的最大延遲，主庫不受這個影響，預設0。如果 > 0， monitor 模組監控主從延遲大於閥值時，會臨時把它變為 SHUNNED 。
- max_latency_ms: mysql_ping 響應時間，大於這個閥值會把它從連線池剔除（即使是ONLINE）
- comment: 備註，不建議留空。可以通過它的內容如json格式的資料，配合自己寫的check指令碼，完成一些自動化的工作。
表 mysql_users
MySQL [(none)]> show create table mysql_users\G;
*************************** 1. row ***************************
table: mysql_users
Create Table: CREATE TABLE mysql_users (
username VARCHAR NOT NULL,
password VARCHAR,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
default_hostgroup INT NOT NULL DEFAULT 0,
default_schema VARCHAR,
schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,
transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 1,
fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,
backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,
frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,
max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,
PRIMARY KEY (username, backend),
UNIQUE (username, frontend))
1 row in set (0.000 sec)
ERROR: No query specified
MySQL [(none)]> select * from mysql_users;
+-----------+------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+-----------+------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| proxysql | proxysql | 1 | 0 | 2 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |
| root | passwd | 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.000 sec)
MySQL [(none)]> select username,password,transaction_persistent,active,backend,frontend,max_connections from runtime_mysql_users;
+-----------+-------------------------------------------+------------------------+--------+---------+----------+-----------------+
| username | password | transaction_persistent | active | backend | frontend | max_connections |
+-----------+-------------------------------------------+------------------------+--------+---------+----------+-----------------+
| proxysql | *BF27B4C7AAD278126E228AA8427806E870F64F39 | 1 | 1 | 0 | 1 | 10000 |
| root | *59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0 | 1 | 1 | 0 | 1 | 10000 |
| sqlsender | *50572A5FABC7DA9CEE5EB5977EDDE59E38967422 | 1 | 1 | 0 | 1 | 10000 |
| proxysql | *BF27B4C7AAD278126E228AA8427806E870F64F39 | 1 | 1 | 1 | 0 | 10000 |
| root | *59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0 | 1 | 1 | 1 | 0 | 10000 |
| sqlsender | *50572A5FABC7DA9CEE5EB5977EDDE59E38967422 | 1 | 1 | 1 | 0 | 10000 |
+-----------+-------------------------------------------+------------------------+--------+---------+----------+-----------------+
6 rows in set (0.001 sec)
- username, password: 連線後端db的使用者密碼。
這個密碼你可以插入明文，也可以插入hash加密後的密文，proxysql會檢查你插入的時候密碼是否以 * 開頭來判斷，而且密文要在其它地方使用 PASSWORD()生成。但到 runtime_mysql_users 裡，都統一變成了密文，所以可以明文插入，再 SAVE MYSQL USERS TO MEM，此時看到的也是HASH密文。
- active: 是否生效該使用者。
- default_hostgroup: 這個使用者的請求沒有匹配到規則時，預設發到這個 hostgroup，預設0
- default_schema: 這個使用者連線時沒有指定 database name 時，預設使用的schema
注意表面上看預設為NULL，但實際上受到變數 mysql-default_schema 的影響，預設為 information_schema。關於這個參考我所提的 issue #988
- transaction_persistent: 如果設定為1，連線上ProxySQL的會話後，如果在一個hostgroup上開啟了事務，那麼後續的sql都繼續維持在這個hostgroup上，不倫是否會匹配上其它路由規則，直到事務結束。
雖然預設是0，但我建議還是設成1，雖然一般來說由於前段應用的空值，為0出問題的情況幾乎很小。作者也在考慮預設設成 1，refer this issue #793
- frontend, backend: 目前版本這兩個都需要使用預設的1，將來有可能會把 Client -> ProxySQL (frontend) 與 ProxySQL -> BackendDB (backend)的認證分開。從 runtime_mysql_users 表內容看到，記錄數比 mysql_users 多了一倍，就是把前端認證與後端認證獨立出來的結果。
- fast_forward: 忽略查詢重寫/快取層，直接把這個使用者的請求透傳到後端DB。相當於只用它的連線池功能，一般不用，路由規則 .* 就行了。
表 mysql_replication_hostgroups
MySQL [(none)]> 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),
comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup))
1 row in set (0.001 sec)
ERROR: No query specified
MySQL [(none)]> select * from mysql_replication_hostgroups;
+------------------+------------------+---------+
| writer_hostgroup | reader_hostgroup | comment |
+------------------+------------------+---------+
| 10 | 20 | 1 |
+------------------+------------------+---------+
1 row in set (0.000 sec)
定義 hostgroup 的主從關係。ProxySQL monitor 模組會監控 HG 後端所有servers 的 read_only 變數，如果發現從庫的 read_only 變為0、主庫變為1，則認為角色互換了，自動改寫 mysql_servers 表裡面 hostgroup 關係，達到自動 Failover 效果。
表 mysql_query_rules
mysql_query_rules 是ProxySQL非常核心一個表，定義查詢路由規則
MySQL [(none)]> show create table mysql_query_rules\G;
*************************** 1. row ***************************
table: mysql_query_rules
Create Table: CREATE TABLE mysql_query_rules (
rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0,
username VARCHAR,
schemaname VARCHAR,
flagIN INT NOT NULL DEFAULT 0,
client_addr VARCHAR,
proxy_addr VARCHAR,
proxy_port INT,
digest VARCHAR,
match_digest VARCHAR,
match_pattern VARCHAR,
negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,
re_modifiers VARCHAR DEFAULT 'CASELESS',
flagOUT INT,
replace_pattern VARCHAR,
destination_hostgroup INT DEFAULT NULL,
cache_ttl INT CHECK(cache_ttl > 0),
reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,
timeout INT UNSIGNED,
retries INT CHECK (retries>=0 AND retries <=1000),
delay INT UNSIGNED,
next_query_flagIN INT UNSIGNED,
mirror_flagOUT INT UNSIGNED,
mirror_hostgroup INT UNSIGNED,
error_msg VARCHAR,
OK_msg VARCHAR,
sticky_conn INT CHECK (sticky_conn IN (0,1)),
multiplex INT CHECK (multiplex IN (0,1,2)),
log INT CHECK (log IN (0,1)),
apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,
comment VARCHAR)
1 row in set (0.001 sec)
ERROR: No query specified
MySQL [(none)]> select * from mysql_query_rules;
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+
| rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest | match_pattern | negate_match_pattern | re_modifiers | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | log | apply | comment |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+
| 1 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | ^SELECT.*FOR UPDATE$ | NULL | 0 | CASELESS | NULL | NULL | 10 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | NULL |
| 2 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | ^SELECT | NULL | 0 | CASELESS | NULL | NULL | 20 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | NULL |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+
2 rows in set (0.000 sec)
- rule_id: 表主鍵，自增。規則處理是以 rule_id 的順序進行。
- active: 只有 active=1 的規則才會參與匹配。
- username: 如果非 NULL，只有連線使用者是 username 的值才會匹配。
- schemaname: 如果非 NULL，只有查詢連線使用的db是 schemaname 的值才會匹配。
注意如果是 NULL，不代表連線沒有使用schema，而是不倫任何schema都進一步匹配。
- flagIN, flagOUT, apply: 用來定義路由鏈 chains of rules。
- 首先會檢查 flagIN=0 的規則，以rule_id的順序；如果都沒匹配上，則走這個使用者的 default_hostgroup。
- 當匹配一條規則後，會檢查 flagOUT。
- 如果不為NULL，並且 flagIN != flagOUT ，則進入以flagIN為上一個flagOUT值的新規則鏈。
- 如果不為NULL，並且 flagIN = flagOUT，則應用這條規則。
- 如果為NULL，或者 apply=1，則結束，應用這條規則。
- 如果最終沒有匹配到，則找到這個使用者的 default_hostgroup。
- client_addr: 匹配客戶端來源IP
- proxy_addr, proxy_port: 匹配本地proxysql的IP、埠。我目前沒有想到它的應用場景，可能是把proxysql監聽在多個介面上，分發到不同的業務？
- digest: 精確的匹配一類查詢。
- match_digest: 正則匹配一類查詢。query digest 是指對查詢去掉具體值後進行“模糊化”後的查詢，類似 pt-fingerprint / pt-query-digest 的效果。
- match_pattern: 正則匹配查詢。
以上都是匹配查詢的規則，1.3.5版本使用的正則引擎只有 RE2 ，1.4版本可以通過變數 mysql-query_processor_regex 設定 RE2 或者 PCRE，且1.4開始預設是PCRE。
推薦用 match_digest 。關於每條查詢都會計算digest對效能的影響，計算query digest確實會有效能損失，但是這卻是proxysql裡面非常重要的特性，主要是兩點：
- proxysql無法知道連線複用(multipexing)是否必須被自動禁用，比如連線裡面有variables/tmp tables/lock table等特殊命令，是不能複用的。
- 完整的查詢去匹配正則的效率，一般沒有引數化後的查詢匹配效率高，因為有很長的字串內容需要處理。再者，SELECT * FROM randomtable WHERE comment LIKE ‘%INTO sbtest1 % FROM sbtest2 %’字串裡有類似這樣的語句，很難排除誤匹配。
- negate_match_pattern: 反向匹配，相當於對 match_digest/match_pattern 的匹配取反。
- re_modifiers: 修改正則匹配的引數，比如預設的：忽略大小寫CASELESS、禁用GLOBAL.
上面都是匹配規則，下面是匹配後的行為
- replace_pattern: 查詢重寫，預設為空，不rewrite。
- rewrite規則要遵守 RE2::Replace 。
destination_hostgroup: 路由查詢到這個 hostgroup。當然如果使用者顯式 start transaction 且 transaction_persistent=1，那麼即使匹配到了，也依然按照事務裡第一條sql的路由規則去走。
- cache_ttl: 查詢結果快取的毫秒數。
proxysql這個 Query Cache 與 MySQL 自帶的query cache不是同一個。proxysql query cache也不會關心後端資料是否被修改，它所做的就是針對某些特定種類的查詢結果進行快取，比如一些歷史資料的count結果。一般不設。
- timeout: 這一類查詢執行的最大時間（毫秒），超時則自動kill。
這是對後端DB的保護機制，相當於阿里雲RDS loose_max_statement_time 變數的功能，但是注意不同的是，阿里雲這個變數的時間時不包括DML操作出現InnoDB行鎖等待的時間，而ProxySQL的這個 timeout 是計算從傳送sql到等待響應的時間。預設mysql-default_query_timeout給的是 10h .
- retries: 語句在執行時失敗時，重試次數。預設由 mysql-query_retries_on_failure變數指定，為1 。
個人建議把它設成0，即不重試。因為執行失敗，對select而言很少見，主要是dml，但自己重試對資料不放心。
- delay: 查詢延遲執行，這是ProxySQL提供的限流機制，會讓其它的查詢優先執行。
預設值 mysql-default_query_delay，為0。我們一般不用，其實還是要配合應用端使用，比如這邊延遲執行，但上層等待你返回，那前端不就堵住了，沒準出現雪崩效應。
- mirror_flagOUT,mirror_hostgroup
這兩個高階了，目前這部分文件不全，功能是SQL映象。顧名思義，就是把匹配到的SQL除了傳送到 destination_hostgroup，同時映象一份到這裡的hostgroup，比如我們的測試庫。比如這種場景，資料庫要從5.6升級到5.7，要驗證現有查詢語句對5.7的適用情況，就可以把生產流量映象到5.7新庫上驗證。
- error_msg: 預設為NULL，如果指定了則這個查詢直接被 block 掉，馬上返回這個錯誤資訊。
這個功能也很實用，比如線上突然冒出一個 “壞查詢”，應用端不方便馬上發版解決，我們就可以在這配置一個規則，把查詢遮蔽掉，想正常的mysql報錯那樣拋異常。下一篇文章有演示。
- multiplex: 連線是否複用。
- log: 是否記錄查詢日誌。可以看到log是否記錄的物件是根據規則。
要開啟日誌記錄，需要設定變數 mysql-eventslog_filename 來指定檔名，然後這個 log 標記為1。但是目前proxysql記錄的日誌是二進位制格式，需要特定的工具才能讀取： eventslog_reader_sample 。這個工具在原始碼目錄 tools下面。
proxysql對後端server健康檢查
MySQL [monitor]> show variables like "mysql-monitor%"; +-----------------------------------------------------+------------+ | Variable_name | 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_replication_lag_use_percona_heartbeat | | | mysql-monitor_query_interval | 60000 | | mysql-monitor_query_timeout | 100 | | mysql-monitor_slave_lag_when_null | 60 | | mysql-monitor_wait_timeout | true | | mysql-monitor_writer_is_also_reader | true | | mysql-monitor_username | monitor | | mysql-monitor_password | P@ssword1! | | 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 | +-----------------------------------------------------+------------+ 22 rows in set (0.001 sec)
ProxySQL配置後端DB server
兩種方式，區別在於
1) 一種是在往mysql_servers表中新增server時就為其劃分好hostgroup_id（例如0表示寫組，1表示讀組）
2) 另一種往mysql_servers表中新增server時不區分hostgroup_id（例如全部設為0），然後通過mysql_replication_hostgroups表中的值，
根據proxysql檢測到的各server的read_only變數值來自動為後端server設定hostgroup_id
這裡強烈推薦用第一種方式
因為第一種是完全由我們控制的;而第二種假如我們誤將讀server的read_only屬性設定為0，則proxysql會將其重新分配到寫組，這絕對是不期望的。
ProxySQL下新增與修改配置
1) 新增配置
需要新增配置時，直接操作的是MEMORAY，例如：新增一個程式使用者，在mysql_users表中執行一個插入操作：
MySQL [(none)]> insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('myadmin','mypass',1,0,1);
這樣就完成了一個使用者的新增。要讓這個insert生效，還需要執行如下操作：
MySQL [(none)]>load mysql users to runtime;
表示將修改後的配置(MEMORY層)用到實際生產環境（RUNTIME層）
如果想儲存這個設定永久生效，還需要執行如下操作：
MySQL [(none)]>save mysql users to disk;
表示將memoery中的配置儲存到磁碟中去。
除了上面兩個操作，還可以執行如下操作：
MySQL [(none)]>load mysql users to memory;
表示將磁碟中持久化的配置拉一份到memory中來。
MySQL [(none)]>load mysql users from config;
表示將配置檔案中的配置載入到memeory中。
2) 持久化配置
以上SQL命令是對mysql_users進行的操作，同理，還可以對mysql_servers表、mysql_query_rules表、global_variables表等執行類似的操作。
如對mysql_servers表插入完成資料後，要執行儲存和載入操作，可執行如下SQL命令：
MySQL [(none)]> load mysql servers to runtime;
MySQL [(none)]> save mysql servers to disk;
對mysql_query_rules表插入完成資料後，要執行儲存和載入操作，可執行如下SQL命令：
MySQL [(none)]> load mysql query rules to runtime;
MySQL [(none)]> save mysql query rules to disk;
對global_variables表插入完成資料後，要執行儲存和載入操作，可執行如下SQL命令：
以下命令載入或儲存mysql variables（global_variables）:
MySQL [(none)]>load mysql variables to runtime;
MySQL [(none)]>save mysql variables to disk;
以下命令載入或儲存admin variables（select * from global_variables where variable_name like 'admin-%'）:
MySQL [(none)]> load admin variables to runtime;
MySQL [(none)]>save admin variables to disk;
二、ProxySQL功能驗證 （針對GTID模式的主從同步，另兩個從庫都要設定read_only=on）
接下來通過實戰操作來全面瞭解一下 ProxySQL 的特性和使用場景。
1. 實驗環境
172.16.60.211 mysql-master 安裝Mysql5.7 172.16.60.212 mysql-slave1 安裝Mysql5.7 172.16.60.213 mysql-slave2 安裝Mysql5.7 172.16.60.214 mysql-proxy 安裝ProxySQL，Mysql-client 系統都是CentOS7.5，MySQL版本是5.7，準備一主兩從架構(基於GTID的同步,兩個從庫都要開啟read_only=on)來配合ProxySQL。 [root@mysql-master ~]# cat /etc/redhat-release CentOS Linux release 7.5.1804 (Core) 1) 三個節點各自設定主機名 [root@mysql-master ~]# hostnamectl --static set-hostname mysql-master [root@mysql-master ~]# hostname mysql-master [root@mysql-slave1 ~]# hostnamectl --static set-hostname mysql-slave1 [root@mysql-slave1 ~]# hostname mysql-slave [root@mysql-slave2 ~]# hostnamectl --static set-hostname mysql-slave2 [root@mysql-slave2 ~]# hostname mysql-slave [root@mysql-proxy ~]# hostnamectl --static set-hostname mysql-proxy [root@mysql-proxy ~]# hostname mysql-proxy 2) 所有節點關閉selinux和iptables防火牆 [root@mysql-master ~]# setenforce 0 [root@mysql-master ~]# cat /etc/sysconfig/selinux |grep "SELINUX=disabled" SELINUX=disabled [root@mysql-master ~]# iptables -F [root@mysql-master ~]# systemctl disable firewalld [root@mysql-master ~]# systemctl stop firewalld [root@mysql-master ~]# firewall-cmd --state not running
2. 安裝Mysql 5.7 (在三個mysql節點上安裝)
在三個mysql節點機上使用yum方式安裝Mysql5.7，參考：https://www.cnblogs.com/kevingrace/p/8340690.html
安裝MySQL yum資源庫
[root@mysql-master ~]# yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
安裝MySQL 5.7
[root@mysql-master ~]# yum install -y mysql-community-server
啟動MySQL伺服器和MySQL的自動啟動
[root@mysql-master ~]# systemctl start mysqld.service
[root@mysql-master ~]# systemctl enable mysqld.service
設定登入密碼
由於MySQL從5.7開始不允許首次安裝後使用空密碼進行登入！為了加強安全性，系統會隨機生成一個密碼以供管理員首次登入使用，
這個密碼記錄在/var/log/mysqld.log檔案中，使用下面的命令可以檢視此密碼：
[root@mysql-master ~]# cat /var/log/mysqld.log|grep 'A temporary password'
2019-01-11T05:53:17.824073Z 1 [Note] A temporary password is generated for root@localhost: TaN.k:*Qw2xs
使用上面檢視的密碼TaN.k:*Qw2xs 登入mysql，並重置密碼為123456
[root@mysql-master ~]# mysql -p #輸入預設的密碼：TaN.k:*Qw2xs
.............
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
mysql> set password=password("123456");
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
檢視mysql版本
[root@mysql-master ~]# mysql -p123456
........
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.24 |
+-----------+
1 row in set (0.00 sec)
=====================================================================
溫馨提示
mysql5.7通過上面預設安裝後，執行語句可能會報錯：
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
這個報錯與Mysql 密碼安全策略validate_password_policy的值有關，validate_password_policy可以取0、1、2三個值：
解決辦法：
set global validate_password_policy=0;
set global validate_password_length=1;
3. 配置Mysql基於GTID的主從同步 (在mysql-master 和 mysql-slave1、mysql-slave2節點上)
1) 主資料庫mysql-master (172.16.60.211)的配置操作
[root@mysql-master ~]# >/etc/my.cnf
[root@mysql-master ~]# vim /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
#GTID:
server_id = 1
gtid_mode = on
enforce_gtid_consistency = on
#binlog
log_bin = master-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
#relay log
skip_slave_start = 1
配置完成之後，別忘了重啟Mysql
[root@mysql-master ~]# systemctl restart mysqld
登入mysql，檢視一下master狀態， 發現多了一項"Executed_Gtid_Set "
[root@mysql-master ~]# mysql -p123456
.........
mysql> show master status;
+-------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+------------------------------------------+
| master-bin.000002 | 550 | | | fc39b161-22ca-11e9-a638-005056ac6820:1-2 |
+-------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> show global variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | fc39b161-22ca-11e9-a638-005056ac6820 |
+---------------+--------------------------------------+
1 row in set (0.00 sec)
mysql> show global variables like '%gtid%';
+----------------------------------+------------------------------------------+
| Variable_name | Value |
+----------------------------------+------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | fc39b161-22ca-11e9-a638-005056ac6820:1-2 |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+------------------------------------------+
8 rows in set (0.00 sec)
主庫執行從庫複製授權
mysql> grant replication slave,replication client on *.* to slave@'172.16.60.212' identified by "slave@123";
Query OK, 0 rows affected, 1 warning (0.09 sec)
mysql> grant replication slave,replication client on *.* to slave@'172.16.60.213' identified by "slave@123";
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)
mysql> show grants for slave@'172.16.60.212';
+-------------------------------------------------------------------------------+
| Grants for slave@172.16.60.212 |
+-------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'172.16.60.212' |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show grants for slave@'172.16.60.213';
+-------------------------------------------------------------------------------+
| Grants for slave@172.16.60.213 |
+-------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'172.16.60.213' |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
在主資料庫機器上建立一個測試庫kevin（為了測試效果）
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> CREATE DATABASE kevin CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.02 sec)
mysql> use kevin;
Database changed
mysql> create table if not exists haha (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);
Query OK, 0 rows affected (0.17 sec)
mysql> insert into kevin.haha values(1,"congcong"),(2,"huihui"),(3,"grace");
Query OK, 3 rows affected (0.16 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from kevin.haha;
+----+----------+
| id | name |
+----+----------+
| 1 | congcong |
| 2 | huihui |
| 3 | grace |
+----+----------+
3 rows in set (0.00 sec)
2) 從資料庫mysql-slave1 (172.16.60.212)的配置操作
與主伺服器配置大概一致，除了server_id不一致外，從伺服器還可以在配置檔案裡面新增："read_only＝on" ,
使從伺服器只能進行讀取操作，此引數對超級使用者無效，並且不會影響從伺服器的複製；
[root@mysql-slave1 ~]# >/etc/my.cnf
[root@mysql-slave1 ~]# vim /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
#GTID:
server_id = 2
gtid_mode = on
enforce_gtid_consistency = on
#binlog
log_bin = master-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
#relay log
skip_slave_start = 1
read_only = on
配置完成之後，別忘了重啟Mysql
[root@mysql-slave1 ~]# systemctl restart mysqld
接著登入mysql，做主從同步
[root@mysql-slave1 ~]# mysql -p123456
........
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
在從資料庫裡，使用change master 配置主從複製
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='172.16.60.211',master_user='slave',master_password='slave@123',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.24 sec)
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.60.211
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 2069
Relay_Log_File: mysql-slave1-relay-bin.000002
Relay_Log_Pos: 2284
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
............
............
Retrieved_Gtid_Set: fc39b161-22ca-11e9-a638-005056ac6820:1-8
Executed_Gtid_Set: 2afbc2f5-22cb-11e9-b9c0-00505688047c:1-2,
fc39b161-22ca-11e9-a638-005056ac6820:1-8
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
檢視從庫的gtid
mysql> show global variables like '%gtid%';
+----------------------------------+------------------------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------+------------------------------------------------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | 2afbc2f5-22cb-11e9-b9c0-00505688047c:1-2,
fc39b161-22ca-11e9-a638-005056ac6820:1-8 |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | 2afbc2f5-22cb-11e9-b9c0-00505688047c:1-2 |
| session_track_gtids | OFF |
+----------------------------------+------------------------------------------------------------------------------------+
8 rows in set (0.01 sec)
接著檢視從資料庫的資料，發現kevin庫已經同步過來了!
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kevin |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> select * from kevin.haha;
+----+----------+
| id | name |
+----+----------+
| 1 | congcong |
| 2 | huihui |
| 3 | grace |
+----+----------+
3 rows in set (0.00 sec)
3) 從資料庫mysql-slave2 (172.16.60.213)的配置操作
[root@mysql-slave2 ~]# >/etc/my.cnf
[root@mysql-slave2 ~]# vim /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
#GTID:
server_id = 3
gtid_mode = on
enforce_gtid_consistency = on
#binlog
log_bin = master-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
#relay log
skip_slave_start = 1
read_only = on
重啟mysqld
[root@mysql-slave2 ~]# systemctl restart mysqld
登入mysql，做主從複製
[root@mysql-slave2 ~]# mysql -p123456
.........
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='172.16.60.211',master_user='slave',master_password='slave@123',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.17 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.60.211
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 2069
Relay_Log_File: mysql-slave2-relay-bin.000002
Relay_Log_Pos: 2284
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
..........
..........
Retrieved_Gtid_Set: fc39b161-22ca-11e9-a638-005056ac6820:1-8
Executed_Gtid_Set: 26e410b4-22cb-11e9-be44-005056880888:1-2,
fc39b161-22ca-11e9-a638-005056ac6820:1-8
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
檢視從庫的gtid
mysql> show global variables like '%gtid%';
+----------------------------------+------------------------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------+------------------------------------------------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | 26e410b4-22cb-11e9-be44-005056880888:1-2,
fc39b161-22ca-11e9-a638-005056ac6820:1-8 |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | 26e410b4-22cb-11e9-be44-005056880888:1-2 |
| session_track_gtids | OFF |
+----------------------------------+------------------------------------------------------------------------------------+
8 rows in set (0.01 sec)
接著檢視從資料庫的資料，發現kevin庫已經同步過來了!
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kevin |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> select * from kevin.haha;
+----+----------+
| id | name |
+----+----------+
| 1 | congcong |
| 2 | huihui |
| 3 | grace |
+----+----------+
3 rows in set (0.00 sec)
4）再回到主資料庫mysql-master (172.16.60.211)上
檢視master狀態，發現已經有兩個slave節點正常存在同步關係了
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 3 | | 3306 | 1 | 26e410b4-22cb-11e9-be44-005056880888 |
| 2 | | 3306 | 1 | 2afbc2f5-22cb-11e9-b9c0-00505688047c |
+-----------+------+------+-----------+--------------------------------------+
2 rows in set (0.00 sec)
5）測試資料同步
在主資料庫mysql-master (172.16.60.211)上更新資料
mysql> insert into kevin.haha values(10,"heifei"),(11,"huoqiu"),(12,"chengxihu");
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
然後在兩個slave從資料庫上檢視，發現已正常同步過來了
mysql> select * from kevin.haha;
+----+-----------+
| id | name |
+----+-----------+
| 1 | congcong |
| 2 | huihui |
| 3 | grace |
| 10 | heifei |
| 11 | huoqiu |
| 12 | chengxihu |
+----+-----------+
6 rows in set (0.00 sec)
4. 安裝配置ProxySQL
已經在上面第一步中介紹了安裝方法，這裡採用rpm包方式安裝，安裝過程省略........
4.1 ProxySQL實現讀寫分離
向ProxySQL中新增MySQL節點
使用insert語句新增主機到mysql_servers表中，其中：hostgroup_id 為10表示寫組，為20表示讀組。
[root@mysql-proxy ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1
............
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.60.211',3306);
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.60.212',3306);
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.60.213',3306);
Query OK, 1 row affected (0.000 sec)
==========================================================================================================
如果在插入過程中，出現報錯：
ERROR 1045 (#2800): UNIQUE constraint failed: mysql_servers.hostgroup_id, mysql_servers.hostname, mysql_servers.port
說明可能之前就已經定義了其他配置，可以清空這張表 或者 刪除對應host的配置
MySQL [(none)]> select * from mysql_servers;
MySQL [(none)]> delete from mysql_servers;
Query OK, 6 rows affected (0.000 sec)
=========================================================================================================
檢視這3個節點是否插入成功，以及它們的狀態。
MySQL [(none)]> select * from mysql_servers\G;
*************************** 1. row ***************************
hostgroup_id: 10
hostname: 172.16.60.211
port: 3306
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment:
*************************** 2. row ***************************
hostgroup_id: 10
hostname: 172.16.60.212
port: 3306
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment:
*************************** 3. row ***************************
hostgroup_id: 10
hostname: 172.16.60.213
port: 3306
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment:
6 rows in set (0.000 sec)
ERROR: No query specified
如上修改後，載入到RUNTIME，並儲存到disk
MySQL [(none)]> load mysql servers to runtime;
Query OK, 0 rows affected (0.006 sec)
MySQL [(none)]> save mysql servers to disk;
Query OK, 0 rows affected (0.348 sec)
監控後端MySQL節點
新增Mysql節點之後，還需要監控這些後端節點。對於後端是主從複製的環境來說，這是必須的，因為ProxySQL需要通過每個節點的read_only值來自動調整
它們是屬於讀組還是寫組。
首先在後端master主資料節點上建立一個用於監控的使用者名稱(只需在master上建立即可，因為會複製到slave上)，這個使用者名稱只需具有USAGE許可權即可。如果還需
要監控複製結構中slave是否嚴重延遲於master(這個俗語叫做"拖後腿"，術語叫做"replication lag")，則還需具備replication client許可權。
在mysql-master主資料庫節點行執行： [root@mysql-master ~]# mysql -p123456 .......... mysql> create user monitor@'172.16.60.%' identified by 'P@ssword1!'; Query OK, 0 rows affected (0.03 sec) mysql> grant replication client on *.* to monitor@'172.16.60.%'; Query OK, 0 rows affected (0.02 sec) mysql> flush privileges; Query OK, 0 rows affected (0.02 sec) 然後回到mysql-proxy代理層節點上配置監控 [root@mysql-proxy ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 .......... MySQL [(none)]> set mysql-monitor_username='monitor'; Query OK, 1 row affected (0.000 sec) MySQL [(none)]> set mysql-monitor_password='P@ssword1!'; Query OK, 1 row affected (0.000 sec) 修改後，載入到RUNTIME，並儲存到disk MySQL [(none)]> load mysql variables to runtime; Query OK, 0 rows affected (0.001 sec) MySQL [(none)]> save mysql variables to disk; Query OK, 94 rows affected (0.079 sec) 驗證監控結果：ProxySQL監控模組的指標都儲存在monitor庫的log表中。 以下是連線是否正常的監控(對connect指標的監控)： 注意：可能會有很多connect_error，這是因為沒有配置監控資訊時的錯誤，配置後如果connect_error的結果為NULL則表示正常。 MySQL [(none)]> select * from mysql_server_connect_log; +---------------+------+------------------+-------------------------+---------------+ | hostname | port | time_start_us | connect_success_time_us | connect_error | +---------------+------+------------------+-------------------------+---------------+ | 172.16.60.211 | 3306 | 1548665195883957 | 762 | NULL | | 172.16.60.212 | 3306 | 1548665195894099 | 399 | NULL | | 172.16.60.213 | 3306 | 1548665195904266 | 483 | NULL | | 172.16.60.211 | 3306 | 1548665255883715 | 824 | NULL | | 172.16.60.212 | 3306 | 1548665255893942 | 656 | NULL | | 172.16.60.211 | 3306 | 1548665495884125 | 615 | NULL | | 172.16.60.212 | 3306 | 1548665495894254 | 441 | NULL | | 172.16.60.213 | 3306 | 1548665495904479 | 638 | NULL | | 172.16.60.211 | 3306 | 1548665512917846 | 487 | NULL | | 172.16.60.212 | 3306 | 1548665512928071 | 994 | NULL | | 172.16.60.213 | 3306 | 1548665512938268 | 613 | NULL | +---------------+------+------------------+-------------------------+---------------+ 20 rows in set (0.000 sec) 以下是對心跳資訊的監控(對ping指標的監控) MySQL [(none)]> select * from mysql_server_ping_log; +---------------+------+------------------+----------------------+------------+ | hostname | port | time_start_us | ping_success_time_us | ping_error | +---------------+------+------------------+----------------------+------------+ | 172.16.60.211 | 3306 | 1548665195883407 | 98 | NULL | | 172.16.60.212 | 3306 | 1548665195885128 | 119 | NULL | ........... | 172.16.60.213 | 3306 | 1548665415889362 | 106 | NULL | | 172.16.60.213 | 3306 | 1548665562898295 | 97 | NULL | +---------------+------+------------------+----------------------+------------+ 110 rows in set (0.001 sec) read_only日誌此時也為空(正常來說，新環境配置時，這個只讀日誌是為空的) MySQL [(none)]> select * from mysql_server_read_only_log; Empty set (0.000 sec) replication_lag的監控日誌為空 MySQL [(none)]> select * from mysql_server_replication_lag_log; Empty set (0.000 sec) 指定寫組的id為10，讀組的id為20。 MySQL [(none)]> insert into mysql_replication_hostgroups values(10,20,1); Query OK, 1 row affected (0.000 sec) 在該配置載入到RUNTIME生效之前，先檢視下各mysql server所在的組。 MySQL [(none)]> select hostgroup_id,hostname,port,status,weight from mysql_servers; +--------------+---------------+------+--------+--------+ | hostgroup_id | hostname | port | status | weight | +--------------+---------------+------+--------+--------+ | 10 | 172.16.60.211 | 3306 | ONLINE | 1 | | 10 | 172.16.60.212 | 3306 | ONLINE | 1 | | 10 | 172.16.60.213 | 3306 | ONLINE | 1 | +--------------+---------------+------+--------+--------+ 3 rows in set (0.000 sec) 3個節點都在hostgroup_id=10的組中。 現在，將剛才mysql_replication_hostgroups表的修改載入到RUNTIME生效。 MySQL [(none)]> load mysql servers to runtime; Query OK, 0 rows affected (0.003 sec) MySQL [(none)]> save mysql servers to disk; Query OK, 0 rows affected (0.361 sec) 一載入，Monitor模組就會開始監控後端的read_only值，當監控到read_only值後，就會按照read_only的值將某些節點自動移動到讀/寫組。 例如，此處所有節點都在id=10的寫組，slave1和slave2都是slave，它們的read_only=1，這兩個節點將會移動到id=20的組。 如果一開始這3節點都在id=20的讀組，那麼移動的將是Master節點，會移動到id=10的寫組。 現在看結果 MySQL [(none)]> select hostgroup_id,hostname,port,status,weight from mysql_servers; +--------------+---------------+------+--------+--------+ | hostgroup_id | hostname | port | status | weight | +--------------+---------------+------+--------+--------+ | 10 | 172.16.60.211 | 3306 | ONLINE | 1 | | 20 | 172.16.60.212 | 3306 | ONLINE | 1 | | 20 | 172.16.60.213 | 3306 | ONLINE | 1 | +--------------+---------------+------+--------+--------+ 3 rows in set (0.000 sec) MySQL [(none)]> select * from mysql_server_read_only_log; +---------------+------+------------------+-----------------+-----------+-------+ | hostname | port | time_start_us | success_time_us | read_only | error | +---------------+------+------------------+-----------------+-----------+-------+ | 172.16.60.212 | 3306 | 1548665728919212 | 1684 | 1 | NULL | | 172.16.60.211 | 3306 | 1548665728918753 | 3538 | 0 | NULL | | 172.16.60.213 | 3306 | 1548665728919782 | 3071 | 1 | NULL |
配置mysql_users
上面的所有配置都是關於後端MySQL節點的，現在可以配置關於SQL語句的，包括：傳送SQL語句的使用者、SQL語句的路由規則、SQL查詢的快取、SQL語句的重寫等等。本小節是SQL請求所使用的使用者配置，例如root使用者。這要求我們需要先在後端MySQL節點新增好相關使用者。這裡以root和sqlsender兩個使用者名稱為例.
首先，在mysql-master主資料庫節點上執行：(只需master執行即可，會複製給兩個slave)
[root@mysql-master ~]# mysql -p123456
.........
mysql> grant all on *.* to root@'172.16.60.%' identified by 'passwd';
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> grant all on *.* to sqlsender@'172.16.60.%' identified by 'P@ssword1!';
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)
然後回到mysql-proxy代理層節點，配置mysql_users表，將剛才的兩個使用者新增到該表中。
admin> insert into mysql_users(username,password,default_hostgroup) values('root','passwd',10);
Query OK, 1 row affected (0.001 sec)
admin> insert into mysql_users(username,password,default_hostgroup) values('sqlsender','P@ssword1!',10);
Query OK, 1 row affected (0.000 sec)
admin> load mysql users to runtime;
Query OK, 0 rows affected (0.001 sec)
admin> save mysql users to disk;
Query OK, 0 rows affected (0.108 sec)
mysql_users表有不少欄位，最主要的三個欄位為username、password和default_hostgroup：
- username：前端連線ProxySQL，以及ProxySQL將SQL語句路由給MySQL所使用的使用者名稱。
- password：使用者名稱對應的密碼。可以是明文密碼，也可以是hash密碼。如果想使用hash密碼，可以先在某個MySQL節點上執行
select password(PASSWORD)，然後將加密結果複製到該欄位。
- default_hostgroup：該使用者名稱預設的路由目標。例如，指定root使用者的該欄位值為10時，則使用root使用者傳送的SQL語句預設
情況下將路由到hostgroup_id=10組中的某個節點。
admin> select * from mysql_users\G
*************************** 1. row ***************************
username: root
password: passwd
active: 1
use_ssl: 0
default_hostgroup: 10
default_schema: NULL
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 1
frontend: 1
max_connections: 10000
*************************** 2. row ***************************
username: sqlsender
password: P@ssword1!
active: 1
use_ssl: 0
default_hostgroup: 10
default_schema: NULL
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 1
frontend: 1
max_connections: 10000
2 rows in set (0.000 sec)
雖然這裡沒有詳細介紹mysql_users表，但上面標註了"注意本行"的兩個欄位必須要引起注意。只有active=1的使用者才是有效的使用者。
至於transaction_persistent欄位，當它的值為1時，表示事務持久化：當某連線使用該使用者開啟了一個事務後，那麼在事務提交/回滾之前，
所有的語句都路由到同一個組中，避免語句分散到不同組。在以前的版本中，預設值為0，不知道從哪個版本開始，它的預設值為1。
我們期望的值為1，所以在繼續下面的步驟之前，先檢視下這個值，如果為0，則執行下面的語句修改為1。
MySQL [(none)]> update mysql_users set transaction_persistent=1 where username='root';
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> update mysql_users set transaction_persistent=1 where username='sqlsender';
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> load mysql users to runtime;
Query OK, 0 rows affected (0.001 sec)
MySQL [(none)]> save mysql users to disk;
Query OK, 0 rows affected (0.123 sec)
然後，分別使用root使用者和sqlsender使用者測試下它們是否能路由到預設的hostgroup_id=10(它是一個寫組)讀、寫資料。
下面是通過轉發埠6033連線的，連線的是轉發到後端真正的資料庫!
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "create database proxy_test"
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| kevin |
| mysql |
| performance_schema |
| proxy_test |
| sys |
+--------------------+
[root@mysql-proxy ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'use proxy_test;create table t(id int);'
[root@mysql-proxy ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'show tables from proxy_test;'
+----------------------+
| Tables_in_proxy_test |
+----------------------+
| t |
+----------------------+
[root@mysql-proxy ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| kevin |
| mysql |
| performance_schema |
| proxy_test |
| sys |
+--------------------+
然後再刪除上面這個測試庫
[root@mysql-proxy ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'drop database proxy_test;'
[root@mysql-proxy ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| kevin |
| mysql |
| performance_schema |
| sys |
+--------------------+
讀寫分離：配置路由規則
ProxySQL的路由規則非常靈活，可以基於使用者、基於schema以及基於每個語句實現路由規則的定製。本案例作為一個入門配置，實現一個最簡單的語句級路由規則，從而實現讀寫分離。
必須注意: 這只是實驗，實際的路由規則絕不應該僅根據所謂的讀、寫操作進行分離，而是從各項指標中找出壓力大、執行頻繁的語句單獨寫規則、做快取等等。和查詢規則有關的表有兩個:mysql_query_rules和mysql_query_rules_fast_routing，後者是前者的擴充套件表，1.4.7之後才支援該快速路由表。本案例只介紹第一個表。插入兩個規則，目的是將select語句分離到hostgroup_id=20的讀組，但由於select語句中有一個特殊語句SELECT...FOR UPDATE它會申請寫鎖，所以應該路由到hostgroup_id=10的寫組.
[root@mysql-proxy ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 ............ MySQL [(none)]> 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); Query OK, 2 rows affected (0.000 sec) MySQL [(none)]> load mysql query rules to runtime; Query OK, 0 rows affected (0.000 sec) MySQL [(none)]> save mysql query rules to disk; Query OK, 0 rows affected (0.272 sec) 需要注意： select ... for update規則的rule_id必須要小於普通的select規則的rule_id，因為ProxySQL是根據rule_id的順序進行規則匹配的。 再來測試下，讀操作是否路由給了hostgroup_id=20的讀組, 如下發現server_id為2和3的節點 (即slave從節點)在讀組內 [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select @@server_id' +-------------+ | @@server_id | +-------------+ | 3 | +-------------+ [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select @@server_id' +-------------+ | @@server_id | +-------------+ | 3 | +-------------+ [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select @@server_id' +-------------+ | @@server_id | +-------------+ | 2 | +-------------+ 讀操作已經路由給讀組，再看看寫操作。這裡以事務持久化進行測試。 [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'start transaction;select @@server_id;commit;select @@server_id;' +-------------+ | @@server_id | +-------------+ | 1 | +-------------+ +-------------+ | @@server_id | +-------------+ | 3 | +-------------+ [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'start transaction;select @@server_id;commit;select @@server_id;' +-------------+ | @@server_id | +-------------+ | 1 | +-------------+ +-------------+ | @@server_id | +-------------+ | 2 | 顯然，一切都按照預期進行。最後，如果想檢視路由的資訊，可查詢stats庫中的stats_mysql_query_digest表。 以下是該表的一個輸出格式示例(和本案例無關)。 [root@mysql-proxy ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 ............ MySQL [(none)]> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC; +----+----------+------------+----------------------------------+ | hg | sum_time | count_star | digest_text | +----+----------+------------+----------------------------------+ | 10 | 283841 | 1 | drop database proxy_test | | 10 | 161020 | 1 | create table t(id int) | | 10 | 36002 | 1 | create database proxy_test | | 20 | 2719 | 5 | select @@server_id | | 10 | 1250 | 3 | select @@server_id | | 10 | 1102 | 2 | show databases | | 10 | 789 | 2 | start transaction | | 10 | 655 | 1 | SELECT DATABASE() | | 10 | 629 | 1 | show databases | | 10 | 564 | 1 | show tables from proxy_test | | 10 | 286 | 2 | commit | | 10 | 0 | 8 | select @@version_comment limit ? | | 10 | 0 | 5 | select @@version_comment limit ? | +----+----------+------------+----------------------------------+ 13 rows in set (0.002 sec)
測試讀寫分離效果
由於讀寫操作都記錄在proxysql的stats_mysql_query_digest表內。 為了測試讀寫分離的效果，可以先清空此表中之前的記錄 (即之前在實現讀寫分配路由配置之前的記錄) 下面這個命令是專門清空stats_mysql_query_digest表的 (使用"delete from stats_mysql_query_digest" 清空不掉!) MySQL [(none)]> SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.002 sec) MySQL [(none)]> select hostgroup,username,digest_text,count_star from stats_mysql_query_digest; Empty set (0.001 sec) 在mysql-proxy代理層節點，通過proxysql進行資料寫入，並檢視 [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select * from kevin.haha;' +----+-----------+ | id | name | +----+-----------+ | 1 | congcong | | 2 | huihui | | 3 | grace | | 11 | huoqiu | | 12 | chengxihu | | 21 | zhongguo | +----+-----------+ [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'delete from kevin.haha where id > 3;' [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'insert into kevin.haha values(21,"zhongguo"),(22,"xianggang"),(23,"taiwan");' [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'update kevin.haha set name="hangzhou" where id=22 ;' [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select * from kevin.haha;' +----+----------+ | id | name | +----+----------+ | 1 | congcong | | 2 | huihui | | 3 | grace | | 21 | zhongguo | | 22 | hangzhou | | 23 | taiwan | +----+----------+ 在mysql-master主資料庫和mysql-slave1、mysql-slave2從資料上檢視 [root@mysql-master ~]# mysql -p123456 ......... mysql> select * from kevin.haha; +----+----------+ | id | name | +----+----------+ | 1 | congcong | | 2 | huihui | | 3 | grace | | 21 | zhongguo | | 22 | hangzhou | | 23 | taiwan | +----+----------+ 6 rows in set (0.00 sec) 發現在客戶端通過proxysql外掛更新的資料，已經寫到mysql-master主資料庫上，並同步到mysql-slave1和mysql-slave2兩個從資料庫上了！ 最後在proxysql管理端檢視讀寫分離 [root@mysql-proxy ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 ............ ............ MySQL [(none)]> select hostgroup,username,digest_text,count_star from stats_mysql_query_digest; +-----------+----------+------------------------------------------------+------------+ | hostgroup | username | digest_text | count_star | +-----------+----------+------------------------------------------------+------------+ | 10 | root | insert into kevin.haha values(?,?),(?,?),(?,?) | 1 | | 10 | root | delete from kevin.haha where id > ? | 1 | | 10 | root | update kevin.haha set name=? where id=? | 1 | | 20 | root | select * from kevin.haha | 2 | | 10 | root | select @@version_comment limit ? | 5 | +-----------+----------+------------------------------------------------+------------+ 5 rows in set (0.001 sec) 從上述結果就可以看出proxysql實現的讀寫分離配置是成功的，讀請求是轉發到group20的讀組內，寫請求轉發到group10的寫組內!!
4.2 負載均衡測試 (加權輪詢)
如上已經配置好一主(mysql-master，在hostgroup10寫組內)、兩從(mysql-slave1和mysql-slave2，在hostgroup20讀組內) ，並且已經在"mysql_query_rules"表中配置了路由規則，即寫操作轉發到hostgroup10組，讀操作轉發到hostgroup20組.
MySQL [(none)]> select * from mysql_query_rules;
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+
| rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest | match_pattern | negate_match_pattern | re_modifiers | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | log | apply | comment |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+
| 1 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | ^SELECT.*FOR UPDATE$ | NULL | 0 | CASELESS | NULL | NULL | 10 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | NULL |
| 2 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | ^SELECT | NULL | 0 | CASELESS | NULL | NULL | 20 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | NULL |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+
2 rows in set (0.000 sec)
由於hostgroup10寫組內只要一個節點(mysql-master節點)，hostgroup20讀組內有兩個節點(mysql-slave1、mysql-slave2)
所以這裡只能測試讀節點的負載均衡
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname"
+--------------+
| @@hostname |
+--------------+
| mysql-slave1 |
+--------------+
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname"
+--------------+
| @@hostname |
+--------------+
| mysql-slave1 |
+--------------+
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname"
+--------------+
| @@hostname |
+--------------+
| mysql-slave1 |
+--------------+
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname"
+--------------+
| @@hostname |
+--------------+
| mysql-slave2 |
+--------------+
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname"
+--------------+
| @@hostname |
+--------------+
| mysql-slave2 |
+--------------+
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname"
+--------------+
| @@hostname |
+--------------+
| mysql-slave2 |
+--------------+
再實驗下mysql -e跟多條語句，看看如何
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname;select @@hostname;select @@hostname"
+--------------+
| @@hostname |
+--------------+
| mysql-slave1 |
+--------------+
+--------------+
| @@hostname |
+--------------+
| mysql-slave1 |
+--------------+
+--------------+
| @@hostname |
+--------------+
| mysql-slave1 |
+--------------+
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname;select @@hostname;select @@hostname"
+--------------+
| @@hostname |
+--------------+
| mysql-slave2 |
+--------------+
+--------------+
| @@hostname |
+--------------+
| mysql-slave2 |
+--------------+
+--------------+
| @@hostname |
+--------------+
| mysql-slave2 |
+--------------+
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname;select @@hostname;select @@hostname"
+--------------+
| @@hostname |
+--------------+
| mysql-slave1 |
+--------------+
+--------------+
| @@hostname |
+--------------+
| mysql-slave1 |
+--------------+
+--------------+
| @@hostname |
+--------------+
| mysql-slave1 |
+--------------+
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname;select @@hostname;select @@hostname"
+--------------+
| @@hostname |
+--------------+
| mysql-slave1 |
+--------------+
+--------------+
| @@hostname |
+--------------+
| mysql-slave1 |
+--------------+
+--------------+
| @@hostname |
+--------------+
| mysql-slave1 |
+--------------+
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname;select @@hostname;select @@hostname"
+--------------+
| @@hostname |
+--------------+
| mysql-slave2 |
+--------------+
+--------------+
| @@hostname |
+--------------+
| mysql-slave2 |
+--------------+
+--------------+
| @@hostname |
+--------------+
| mysql-slave2 |
+--------------+
由以上結果可能會猜想並可印證：
在一個client的一個連結週期內，所有query路由到同一臺後端!
即在同一個client的連結週期內，query路由不會轉發到同組內的不同後端節點機上，只能轉發到同一臺後端節點機上!
但是這只是個假象!!! 是因為正好用到了select @ 語句。
如官網所介紹: sends a query that implicitly disables multiplexing. For example, if you run “SELECT @a” , ProxySQL will disable
multiplexing for that client and will always use the same backend connection
最後可以知道: proxysql的負載方式目前僅為加權輪詢一種（經驗證所確認），並無其他機制!
===============================================================================
可以編寫一個負載均衡的shell測試指令碼:
[root@mysql-proxy ~]# which mysql
/usr/bin/mysql
[root@mysql-proxy ~]# vim /opt/test_proxysql_lb.sh
#!/bin/bash
i=0
while(($i<200))
do
/usr/bin/mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname;" >> /tmp/test_proxy_sql_lb.txt
let "i++"
echo "$i"
sleep 0.1
done
執行測試指令碼:
[root@mysql-proxy ~]# sh -x /opt/test_proxysql_lb.sh > /dev/null 2>&1
執行後檢查結果
[root@mysql-proxy ~]# grep "mysql-slave1" /tmp/test_proxy_sql_lb.txt|wc -l
86
[root@mysql-proxy ~]# grep "mysql-slave2" /tmp/test_proxy_sql_lb.txt|wc -l
114
以上查詢結果符合預期
4.3 開啟ProxySQL的Web統計功能
首先開啟web功能 [root@mysql-proxy ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 ............ ............ MySQL [(none)]> update global_variables set variable_value='true' where variable_name='admin-web_enabled'; Query OK, 1 row affected (0.001 sec) MySQL [(none)]> LOAD ADMIN VARIABLES TO RUNTIME; Query OK, 0 rows affected (0.001 sec) MySQL [(none)]> SAVE ADMIN VARIABLES TO DISK; Query OK, 31 rows affected (0.070 sec) 然後檢視埠和登入web介面的使用者名稱和密碼，使用者名稱和密碼與stat賬戶一致： MySQL [(none)]> select * from global_variables where variable_name LIKE 'admin-web%' or variable_name LIKE 'admin-stats%'; +-----------------------------------+----------------+ | variable_name | variable_value | +-----------------------------------+----------------+ | admin-stats_credentials | stats:stats | #賬戶密碼 | admin-stats_mysql_connections | 60 | | admin-stats_mysql_connection_pool | 60 | | admin-stats_mysql_query_cache | 60 | | admin-stats_system_cpu | 60 | | admin-stats_system_memory | 60 | | admin-web_enabled | true | | admin-web_port | 6080 | #埠 +-----------------------------------+----------------+ 8 rows in set (0.003 sec)
檢視web埠是否正常開啟
[root@mysql-proxy ~]# lsof -i:6080 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME proxysql 22324 root 27u IPv4 23010645 0t0 TCP *:6080 (LISTEN)
訪問http://172.16.60.214:6080並使用stats:stats登入即可檢視一些統計資訊。
4.4 scheduler列印proxysql狀態到日誌
[root@mysql-proxy ~]# mkdir -p /opt/proxysql/log
[root@mysql-proxy ~]# vim /opt/proxysql/log/status.sh
#!/bin/bash
DATE=`date "+%Y-%m-%d %H:%M:%S"`
echo "{\"dateTime\":\"$DATE\",\"status\":\"running\"}" >> /opt/proxysql/log/status_log
[root@mysql-proxy ~]# chmod 777 /opt/proxysql/log/status.sh
然後在proxysql插入一條scheduler (定義每分鐘列印一次，即60000毫秒)
[root@mysql-proxy ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
............
............
MySQL [(none)]> insert into scheduler(active,interval_ms,filename) values (1,60000,'/opt/proxysql/log/status.sh');
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> LOAD SCHEDULER TO RUNTIME;
Query OK, 0 rows affected (0.001 sec)
MySQL [(none)]> SAVE SCHEDULER TO DISK;
Query OK, 0 rows affected (0.105 sec)
然後檢視日誌就可以看到proxysql 的執行結果了：
[root@mysql-proxy ~]# tail -f /opt/proxysql/log/status_log
{"dateTime":"2019-02-19 14:24:03","status":"running"}
{"dateTime":"2019-02-19 14:25:03","status":"running"}
{"dateTime":"2019-02-19 14:26:03","status":"running"}
{"dateTime":"2019-02-19 14:27:03","status":"running"}