導讀
作者:張銳志
1.表定義與字段說明
表的DDL定義:
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,
#是否使用SSL連接配接
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,
#是否為ProxySQL本身的賬戶(通過6033端口接入ProxySQL)
max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,
#該使用者對ProxysSQL最大連接配接數
PRIMARY KEY (username, backend),
#主鍵,後端賬戶使用者名唯一
UNIQUE (username, frontend))
#唯一性限制,前端中使用者名唯一
參數的特别說明:
transaction_persistent:
對于讀寫分離特别重要,保證了同一個事務中所有的語句都會路由到同一組示例,防止出現同一個事務中,上下文資料不一緻的情況。例如,在不開啟這個屬性的情況下:
begin;
insert into t1 values(xxxyyyzzz);
select * from t1;
commit;
很有可能 插入語句被路由到寫組,而查詢語句被路由到讀組(假設寫組的示例沒有重複出現在讀組中)。由于在傳統複制情況下(沒有開啟after sync),事務隻有被送出後才會被傳輸到從庫上,就造成一個事務中前後不一緻,自己讀不到自己的修改的資料的問題。
fast_forward:
看下源碼(lib\MySQL_Thread.cpp):
if (myds->myds_type==MYDS_BACKEND && myds->sess->status!=FAST_FORWARD) {
if (mypolls.fds[n].revents) {
// this part of the code fixes an important bug
// if a connection in use but idle (ex: running a transaction)
// get data, immediately destroy the session
//
// this can happen, for example, with a low wait_timeout and running transaction
if (myds->sess->status==WAITING_CLIENT_DATA) {
if (myds->myconn->async_state_machine==ASYNC_IDLE) {
proxy_warning("Detected broken idle connection on %s:%d\n", myds->myconn->parent->address, myds->myconn->parent->port);
myds->destroy_MySQL_Connection_From_Pool(false);
myds->sess->set_unhealthy();
return false;
return true;
}
..............................
} else {
// if this is a backend with fast_forward, set unhealthy
// if this is a backend without fast_forward, do not set unhealthy: it will be handled by client library
if (myds->sess->session_fast_forward) { // if fast forward
if (myds->myds_type==MYDS_BACKEND) { // and backend
myds->sess->set_unhealthy(); // set unhealthy
}
}
}
}
return true;
}
例如在開啟fast_forward後,一些處于連接配接狀态,但空閑的線程,會被ProxySQL标記為不健康的線程,會被立即結束掉(可能處于節省線程資源的考慮)。這個參數預設是不開啟的。
backend 與 frontend:以後的版本中可能有前後端賬号分離的操作。
2.使用者表維護
讓我們先看下和配置有關的庫和表
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 | #統計資訊曆史庫
+-----+---------------+-------------------------------------+
ProxySQL自身共有5個 庫,分别為3個儲存在記憶體中的庫,和三個儲存在磁盤的SQLite庫。
我們平時通過6032管理端口登入後,預設就是main庫,所有的配置更改都必須在這個庫中進行,disk存檔庫不會直接受到影響。接下來看下
main庫中的表:
mysql> show tables from main;
+--------------------------------------------+
| tables |
+--------------------------------------------+
| global_variables | #ProxySQL的基本配置參數,類似與MySQL
| mysql_collations | #配置對MySQL字元集的支援
| mysql_group_replication_hostgroups | #MGR相關的表,用于執行個體的讀寫組自動配置設定
| mysql_query_rules | #路由表
| mysql_replication_hostgroups | #主從複制相關的表,用于執行個體的讀寫組自動配置設定
| mysql_servers | #存儲MySQL執行個體的資訊
| mysql_users | #現階段存儲MySQL使用者,當然以後有前後端賬号分離的設想
| proxysql_servers | #存儲ProxySQL的資訊,用于ProxySQL Cluster同步
| runtime_checksums_values | #運作環境的存儲校驗值
| runtime_global_variables | #
| runtime_mysql_group_replication_hostgroups | #
| runtime_mysql_query_rules | #
| runtime_mysql_replication_hostgroups | #與上面對應,但是運作環境正在使用的配置
| runtime_mysql_servers | #
| runtime_mysql_users | #
| runtime_proxysql_servers | #
| runtime_scheduler | #
| scheduler | #定時任務表
+--------------------------------------------+
disk庫中的表:
mysql> show tables from disk;
+------------------------------------+
| tables |
+------------------------------------+
| global_variables |#
| mysql_collations |#
| mysql_group_replication_hostgroups |#
| mysql_query_rules |#
| mysql_replication_hostgroups |#基本與上面的表相對應
| mysql_replication_hostgroups_v122 |#但是多了兩個老版本的表
| mysql_servers |#
| mysql_servers_v122 |#
| mysql_users |#
| proxysql_servers |#
| scheduler |#
+------------------------------------+
不難觀察出,9張配置表在不同的情況下出現了三次,分别代表:目前記憶體中的配置資訊,目前正在使用的配置資訊,目前磁盤檔案中的配置資訊。
這就要求我們在配置時按需對三個地方的配置進行分别配置。
如:插入一個新的使用者
insert into mysql_users(username,password,active,default_hostgroup) values ('predecessor_beast','114514',1,69)
這條記錄隻會出現在main庫的mysql_users 表中,而運作環境和磁盤上均未發生變化。
從記憶體加載到運作環境中
LOAD MYSQL USERS TO RUNTIME;
從記憶體儲存到磁盤檔案中
SAVE MYSQL USERS TO DISK;
從運作環境下載下傳到記憶體中
SAVE MYSQL USERS TO MEMORY;
從磁盤檔案加載到記憶體中
LOAD MYSQL USERS TO MEMORY;
配置管理簡圖
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsISPrdEZwZ1Rh5WNXp1bwNjW1ZUba9VZwlHdsATOfd3bkFGazxCMx8VesATMfhHLlN3XnxCMwEzX0xiRGZkRGZ0Xy9GbvNGLpZTY1EmMZVDUSFTU4VFRR9Fd4VGdsYTMfVmepNHLrJXYtJXZ0F2dvwVZnFWbp1zczV2YvJHctM3cv1Ce-cmbw5yN3MWZyYTM3IWO3cDM1UjY4UmNihzM0UGOlJWN1UDMz8CXyEzLcdDMxIDMy8CXn9Gbi9CXzV2Zh1WavwVbvNmLvR3YxUjLzM3Lc9CX6MHc0RHaiojIsJye.png)
從上到下是SAVE XXX TO XXX;
從下到上是LOAD XXX FROM XXX;
3.明文密碼的加密存儲
ProxySQL支援明文和哈希加密兩種密碼儲存方式,預設的方式一般都是明文。如下圖:
mysql> SELECT username,password FROM mysql_users;
+------------------+-------------------------------------------+
| username | password |
+------------------+-------------------------------------------+
| proxysql_web | 123456 |
| mgr33061 | 123456 |
| mgr33061_backend | 123456 |
+------------------+-------------------------------------------+
可以通過兩種辦法将明文密碼加密:
(1)在輸入時加密:
# 原明文插入語句如下:
# ProxySQL
ProxySQL> insert into mysql_users(username,password,active,default_hostgroup) values ('predecessor_beast','114514',1,69);
# 先到MySQL執行個體中進行加密
# MySQL
root@localhost 16:53: [(none)]> select PASSWORD('114514');
+-------------------------------------------+
| PASSWORD('114514') |
+-------------------------------------------+
| *D9050F2D99C3DDD8138912B7BDF8F4BACBE3A8E7 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
# 替換插入語句中的明文密碼
ProxySQL> insert into mysql_users(username,password,active,default_hostgroup) values ('predecessor_beast','114514',1,69);
(2)使用admin-hash_passwords特性
ProxySQL> select * from global_variables where variable_name like "%passwords%";
+----------------------+----------------+
| variable_name | variable_value |
+----------------------+----------------+
| admin-hash_passwords | true | #确認開啟admin-hash_passwords特性
+----------------------+----------------+
1 row in set (0.00 sec)
# 插入新的使用者(明文密碼)
ProxySQL> insert into mysql_users(username,password,active,default_hostgroup) values ('predecessor_beast','114514',1,69);
Query OK, 1 row affected (0.00 sec)
# 檢視明文密碼的使用者表
ProxySQL> SELECT username,password FROM mysql_users;
+------------------+-------------------------------------------+
| username | password |
+------------------+-------------------------------------------+
| proxysql | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | #之前已經加密過了
| proxysql_web | 123456 | #未加密
| mgr33061 | 123456 | #未加密
| mgr33061_backend | 123456 | #未加密
| predecessor_beast| 114514 | #新插入的未加密使用者
+------------------+-------------------------------------------+
# 檢視運作環境中的使用者表
ProxySQL> select username,password from runtime_mysql_users;
+------------------+-------------------------------------------+
| username | password |
+------------------+-------------------------------------------+
| proxysql | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | #由于前後端賬戶的原因
| proxysql_web | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | #原先單個賬戶成對出現
| mgr33061 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| proxysql | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | #運作環境中的都是已經加密的
| proxysql_web | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mgr33061_backend | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | #不存在新使用者
+------------------+-------------------------------------------+
# 加載到運作環境中
mysql> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)
# 從運作環境中下載下傳出來
mysql> save mysql users to memory;
Query OK, 0 rows affected (0.00 sec)
mysql> save mysql users to disk;
Query OK, 0 rows affected (0.00 sec)
# 檢查下載下傳出來的使用者表
mysql> SELECT username,password FROM mysql_users;
+-------------------+-------------------------------------------+
| username | password |
+-------------------+-------------------------------------------+
| mgr33061 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| proxysql | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| proxysql_web | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mgr33061_backend | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| predecessor_beast | *D9050F2D99C3DDD8138912B7BDF8F4BACBE3A8E7 |
+-------------------+-------------------------------------------+
# 所有明文密碼已經被加密,已經加密過的,不會再次加密