【MySQL】【ProxySQL】淺析mysql_users表

【MySQL】【ProxySQL】淺析mysql_users表

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,                #默認(rèn)查詢路由組
    default_schema VARCHAR,                                #默認(rèn)數(shù)據(jù)庫
    schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,  #限定用戶在默認(rèn)數(shù)據(jù)庫中
    transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 1,           #事務(wù)路由分配持久性，同一個事務(wù)的語句不會被分配到不同的組
    fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,    #快速回收空閑線程
    backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,             #是否為后端數(shù)據(jù)庫的賬戶
    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最大連接數(shù)
    PRIMARY KEY (username, backend),  #主鍵，后端賬戶用戶名唯一
    UNIQUE (username, frontend))      #唯一性約束，前端中用戶名唯一

參數(shù)的特別說明：

transaction_persistent：

? 對于讀寫分離特別重要，保證了同一個事務(wù)中所有的語句都會路由到同一組示例，防止出現(xiàn)同一個事務(wù)中，上下文數(shù)據(jù)不一致的情況。例如，在不開啟這個屬性的情況下，

“專業(yè)、務(wù)實(shí)、高效、創(chuàng)新、把客戶的事當(dāng)成自己的事”是我們每一個人一直以來堅持追求的企業(yè)文化。 成都創(chuàng)新互聯(lián)公司是您可以信賴的網(wǎng)站建設(shè)服務(wù)商、專業(yè)的互聯(lián)網(wǎng)服務(wù)提供商! 專注于做網(wǎng)站、成都網(wǎng)站制作、軟件開發(fā)、設(shè)計服務(wù)業(yè)務(wù)。我們始終堅持以客戶需求為導(dǎo)向，結(jié)合用戶體驗與視覺傳達(dá)，提供有針對性的項目解決方案，提供專業(yè)性的建議，創(chuàng)新互聯(lián)建站將不斷地超越自我，追逐市場，引領(lǐng)市場！

begin;
insert into t1 values(xxxyyyzzz);
select * from t1;   
commit;

? 很有可能 插入語句被路由到寫組，而查詢語句被路由到讀組（假設(shè)寫組的示例沒有重復(fù)出現(xiàn)在讀組中)。由于在傳統(tǒng)復(fù)制情況下（沒有開啟after sync），事務(wù)只有被提交后才會被傳輸?shù)綇膸焐?，就造成一個事務(wù)中前后不一致，自己讀不到自己的修改的數(shù)據(jù)的問題。

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后，一些處于連接狀態(tài)，但空閑的線程，會被ProxySQL標(biāo)記為不健康的線程，會被立即結(jié)束掉(可能處于節(jié)省線程資源的考慮)。這個參數(shù)默認(rèn)是不開啟的。

backend 與 frontend：

? 以后的版本中可能有前后端賬號分離的操作。

2.用戶表維護(hù)

讓我們先看下和配置有關(guān)的庫和表

mysql> show databases;
+-----+---------------+-------------------------------------+
| seq | name          | file                                |
+-----+---------------+-------------------------------------+
| 0   | main          |                                     |  #常用庫
| 2   | disk          | /var/lib/proxysql/proxysql.db       |  #配置存檔庫
| 3   | stats         |                                     |  #統(tǒng)計信息庫
| 4   | monitor       |                                     |  #監(jiān)控信息庫
| 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |  #統(tǒng)計信息歷史庫
+-----+---------------+-------------------------------------+

ProxySQL自身共有5個 庫，分別為3個保存在內(nèi)存中的庫，和三個保存在磁盤的SQLite庫。

我們平時通過6032管理端口登入后，默認(rèn)就是main庫，所有的配置更改都必須在這個庫中進(jìn)行，disk存檔庫不會直接受到影響。接下來看下

main庫中的表：
mysql> show tables from main;
+--------------------------------------------+
| tables                                     |
+--------------------------------------------+
| global_variables                           |  #ProxySQL的基本配置參數(shù)，類似與MySQL
| mysql_collations                           |  #配置對MySQL字符集的支持
| mysql_group_replication_hostgroups         |  #MGR相關(guān)的表，用于實(shí)例的讀寫組自動分配
| mysql_query_rules                          |  #路由表
| mysql_replication_hostgroups               |  #主從復(fù)制相關(guān)的表，用于實(shí)例的讀寫組自動分配
| mysql_servers                              |  #存儲MySQL實(shí)例的信息
| mysql_users                                |  #現(xiàn)階段存儲MySQL用戶，當(dāng)然以后有前后端賬號分離的設(shè)想
| proxysql_servers                           |  #存儲ProxySQL的信息，用于ProxySQL Cluster同步
| runtime_checksums_values                   |  #運(yùn)行環(huán)境的存儲校驗值
| runtime_global_variables                   |  #
| runtime_mysql_group_replication_hostgroups |  #
| runtime_mysql_query_rules                  |  #
| runtime_mysql_replication_hostgroups       |  #與上面對應(yīng)，但是運(yùn)行環(huán)境正在使用的配置
| runtime_mysql_servers                      |  #
| runtime_mysql_users                        |  #
| runtime_proxysql_servers                   |  #
| runtime_scheduler                          |  #
| scheduler                                  |  #定時任務(wù)表
+--------------------------------------------+
disk庫中的表：
mysql> show tables from disk;
+------------------------------------+
| tables                             |
+------------------------------------+
| global_variables                   |#
| mysql_collations                   |#
| mysql_group_replication_hostgroups |#
| mysql_query_rules                  |#
| mysql_replication_hostgroups       |#基本與上面的表相對應(yīng)
| mysql_replication_hostgroups_v122  |#但是多了兩個老版本的表
| mysql_servers                      |#
| mysql_servers_v122                 |#
| mysql_users                        |#
| proxysql_servers                   |#
| scheduler                          |#
+------------------------------------+

不難觀察出，9張配置表在不同的情況下出現(xiàn)了三次，分別代表：當(dāng)前內(nèi)存中的配置信息，當(dāng)前正在使用的配置信息，當(dāng)前磁盤文件中的配置信息。

這就要求我們在配置時按需對三個地方的配置進(jìn)行分別配置。

如：插入一個新的用戶
insert into mysql_users(username,password,active,default_hostgroup) values ('predecessor_beast','114514',1,69)

這條記錄只會出現(xiàn)在main庫的mysql_users 表中，而運(yùn)行環(huán)境和磁盤上均未發(fā)生變化。

從內(nèi)存加載到運(yùn)行環(huán)境中
LOAD MYSQL USERS TO RUNTIME;
從內(nèi)存保存到磁盤文件中
SAVE MYSQL USERS TO DISK;
從運(yùn)行環(huán)境下載到內(nèi)存中
SAVE MYSQL USERS TO MEMORY;
從磁盤文件加載到內(nèi)存中
LOAD MYSQL USERS TO MEMORY;
配置管理簡圖

【MySQL】【ProxySQL】淺析mysql_users表

從上到下是SAVE XXX TO XXX;

從下到上是LOAD XXX FROM XXX;

3.明文密碼的加密存儲

ProxySQL支持明文和哈希加密兩種密碼保存方式，默認(rèn)的方式一般都是明文。如下圖：

mysql> SELECT username,password FROM mysql_users;
+------------------+-------------------------------------------+
| username         | password                                  |
+------------------+-------------------------------------------+
| proxysql_web     | 123456                                    |
| mgr33061         | 123456                                    |
| mgr33061_backend | 123456                                    |
+------------------+-------------------------------------------+

可以通過兩種辦法將明文密碼加密，

1.在輸入時加密

由于ProxySQL提供的服務(wù)端沒有加密函數(shù)，需要在MySQL中進(jìn)行加密后，然后替換掉插入語句中的明文密碼。

# 原明文插入語句如下：
# ProxySQL
ProxySQL> insert into mysql_users(username,password,active,default_hostgroup) values ('predecessor_beast','114514',1,69);
# 先到MySQL實(shí)例中進(jìn)行加密
# 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特性

在global_variable中開啟admin-hash_passwords后，通過將含有明文密碼的mysql_users表加載到運(yùn)行環(huán)境中，這時表中的所有明文密碼都會被哈希加密后的密碼替換，然后再save到memory最后save到disk即可永久加密保存。

ProxySQL> select * from global_variables where variable_name like "%passwords%";
+----------------------+----------------+
| variable_name        | variable_value |
+----------------------+----------------+
| admin-hash_passwords | true           |   #確認(rèn)開啟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 |  #之前已經(jīng)加密過了
| proxysql_web     | 123456                                    |  #未加密
| mgr33061         | 123456                                    |  #未加密
| mgr33061_backend | 123456                                    |  #未加密
| predecessor_beast| 114514                                 |  #新插入的未加密用戶
+------------------+-------------------------------------------+

# 查看運(yùn)行環(huán)境中的用戶表
ProxySQL> select username,password from  runtime_mysql_users;
+------------------+-------------------------------------------+
| username         | password                                  |
+------------------+-------------------------------------------+
| proxysql         | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | #由于前后端賬戶的原因
| proxysql_web     | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | #原先單個賬戶成對出現(xiàn)
| mgr33061         | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| proxysql         | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | #運(yùn)行環(huán)境中的都是已經(jīng)加密的
| proxysql_web     | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mgr33061_backend | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | #不存在新用戶
+------------------+-------------------------------------------+

# 加載到運(yùn)行環(huán)境中
mysql> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)

# 從運(yùn)行環(huán)境中下載出來
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 |
+-------------------+-------------------------------------------+
# 所有明文密碼已經(jīng)被加密，已經(jīng)加密過的，不會再次加密

分享文章：【MySQL】【ProxySQL】淺析mysql_users表
鏈接分享：http://www.muchs.cn/article0/gcejoo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供營銷型網(wǎng)站建設(shè)、網(wǎng)站導(dǎo)航、云服務(wù)器、面包屑導(dǎo)航、標(biāo)簽優(yōu)化、手機(jī)網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)

成都網(wǎng)頁設(shè)計公司