Analysis of "MySQL" and "Proxysql" mysql_users table

A brief analysis of "MySQL" and "Proxysql" mysql_users table 1. Table definition and the DDL definition of the field description table:

CREATE TABLE mysql_users (username varchar not NULL, #用户名 password varchar, #密码 active INT CHECK (AC tive in (0,1)) is not NULL for default 1, #是否启用 Use_ssl INT CHECK (Use_ssl in (0,1)) is not null default 0, #是否使用SSL连接 def Ault_hostgroup INT not NULL DEFAULT 0, #默认查询路由组 Default_schema VARCHAR, # Default database schema_locked int CHECK (schema_locked in (0,1)) is not NULL default 0, #限定用户在默认数据库中 transaction_persistent int CHECK (Transaction_persistent in (0,1)) is not NULL DEFAULT 1, #事务路由分配持久性, the statement of the same transaction is not assigned to a different group Fast_forward INT CH             ECK (Fast_forward in (0,1)) is not NULL for default 0, #快速回收空闲线程 backend INT CHECK (backend in (0,1)) is not null default 1,                                     #是否为后端数据库的账户 frontend INT CHECK (frontend in (0,1)) is not NULL DEFAULT 1,                         #是否为ProxySQL本身的账户 (Access Proxysql via 6033 port) max_connections INT CHECK (max_connections >=0) not NULL DEFAULT 10000, #该用户对PRoxyssql Maximum number of connections PRIMARY KEY (username, backend), #主键, backend account username unique (username, frontend)) #唯一性约束, the user name is unique in the front end 

Special description of the parameter: transaction_persistent:

? It is particularly important for read-write separation, which ensures that all statements in the same transaction are routed to the same set of examples, preventing inconsistencies in the context data in the same transaction. For example, if this property is not turned on,

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

? It is very likely that the INSERT statement is routed to the write group, and the query statement is routed to the read group (assuming that the example of the write group does not recur in the read group). Because in a traditional replication scenario (after sync is not turned on), the transaction is transmitted to the library only after it has been committed, resulting in a transaction that is inconsistent and cannot read its own modified data.

Fast_forward:

Look at the source code (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 Sessio N///This can happen, for example, with a low wait_timeout and running Transactio N if (myds->sess->status==waiting_client_data) {if (Myds->myco  Nn->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 I s a backend without fast_forward, do not set unhealthy:it'll be handled by client library if (my Ds->sess->session_fast_forward) {//If Fast forward if (myds->myds_type==myds_backend                            ) {//And backend myds->sess->set_unhealthy ();//Set unhealthy }}}} return true;

? For example, when Fast_forward is turned on, some threads that are connected, but idle, are flagged as unhealthy by Proxysql and are immediately terminated (perhaps in the consideration of saving thread resources). This parameter is not turned on by default.

Backend and Frontend:

? In future versions, there may be separate operations for the front and back end accounts.

2. User Table Maintenance

Let's look at the libraries and tables that are related to the configuration

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 |  #统计信息历史库+-----+---------------+-------------------------------------+

The Proxysql itself has 5 libraries, 3 in-memory libraries, and three SQLite libraries saved on disk.

We usually log in through the 6032 management port, the default is the main library, all configuration changes must be in this library, disk Archive library is not directly affected. Now look at the

Tables in the main library:

Mysql> Show tables from main;+--------------------------------------------+| Tables |+--------------------------------------------+|  Global_variables | #ProxySQL的基本配置参数, similar to mysql|  mysql_collations | #配置对MySQL字符集的支持 |  mysql_group_replication_hostgroups | #MGR相关的表, automatic allocation of read and write groups for instances |  Mysql_query_rules | #路由表 |  mysql_replication_hostgroups | #主从复制相关的表, automatic allocation of read and write groups for instances |  Mysql_servers | #存储MySQL实例的信息 |  Mysql_users | #现阶段存储MySQL用户, of course, the idea of a separate front-end account |  Proxysql_servers | #存储ProxySQL的信息, for proxysql cluster synchronization |  Runtime_checksums_values | #运行环境的存储校验值 |  Runtime_global_variables | #|  runtime_mysql_group_replication_hostgroups | #|  Runtime_mysql_query_rules | #|  runtime_mysql_replication_hostgroups | #与上面对应, but the configuration the running environment is using | RuntimE_mysql_servers | #|  Runtime_mysql_users | #|  Runtime_proxysql_servers | #|  Runtime_scheduler | #|  Scheduler | #定时任务表 +--------------------------------------------+

Tables in Disk library:

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                          |#+------------------------------------+

It is not difficult to see that 9 configuration tables appear three times in different situations, representing: Current in-memory configuration information, configuration information currently in use, and configuration information in the current disk file.

This requires that we configure the configuration of the three locations as needed separately.

For example, insert a new user

insert into mysql_users(username,password,active,default_hostgroup) values (‘predecessor_beast‘,‘114514‘,1,69)

This record only appears in the main Library's mysql_users table, and the running environment and disk are not changed.

Load from memory into the running environment

LOAD MYSQL USERS TO RUNTIME;

Save from memory to disk file

SAVE MYSQL USERS TO DISK;

Download to memory from the runtime environment

SAVE MYSQL USERS TO MEMORY;

Loading from disk files into memory

LOAD MYSQL USERS TO MEMORY;

Configuration Management diagram

From top to bottom isSAVE XXX TO XXX;

From bottom to top YesLOAD XXX FROM XXX;

3. Encrypted storage of plaintext passwords

Proxysql supports both plaintext and hash encryption, and the default is generally clear. Such as:

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

There are two ways to encrypt plaintext passwords.

1. Encrypt at input

Since the service side provided by Proxysql does not have cryptographic functions, it needs to be encrypted in MySQL and then replace the plaintext password in the INSERT statement.

# 原明文插入语句如下：# ProxySQLProxySQL> insert into mysql_users(username,password,active,default_hostgroup) values (‘predecessor_beast‘,‘114514‘,1,69);# 先到MySQL实例中进行加密# MySQL[email protected] 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. Use admin-hash_passwordsCharacteristics

global_variableafter Admin-hash_passwords is turned on, all plaintext passwords in the table are replaced with hashed passwords by loading the mysql_users table containing the plaintext password into the running environment. Then save to memory last save to disk can be permanently encrypted save.

Proxysql> SELECT * from Global_variables where variable_name like "%passwords%"; +----------------------+----------- -----+| variable_name | Variable_value |+----------------------+----------------+| Admin-hash_passwords |   true | #确认开启admin-hash_passwords features +----------------------+----------------+1 row in Set (0.00 sec) # Insert a new user (clear text password) proxysql > INSERT into Mysql_users (Username,password,active,default_hostgroup) VALUES (' Predecessor_beast ', ' 114514 ', 1,69) ; Query OK, 1 row Affected (0.00 sec) # View User table for clear text password proxysql> SELECT username,password from mysql_users;+------------------ +-------------------------------------------+| Username | Password |+------------------+-------------------------------------------+| Proxysql |  *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 | #之前已经加密过了 | Proxysql_web |  123456 | #未加密 | mgr33061 |  123456 | #未加密 | Mgr33061_bAckend |  123456 | #未加密 | Predecessor_beast|  114514 | #新插入的未加密用户 +------------------+-------------------------------------------+# View user tables in the running environment 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 | #不存在新用户 +------------------+-------------------------------------------+# load into the running environment mysql> load MySQL users to RUntime; Query OK, 0 rows Affected (0.00 sec) # Downloaded from the running environment 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) # Check the downloaded user table mysql> SELECT username,password from mysql_users;+-------------------+ -------------------------------------------+| Username | Password |+-------------------+-------------------------------------------+| mgr33061 | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 | | Proxysql | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 | | Proxysql_web | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 | | Mgr33061_backend | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 | | Predecessor_beast | *d9050f2d99c3ddd8138912b7bdf8f4bacbe3a8e7 |+-------------------+-------------------------------------------+# All plaintext passwords have been encrypted, encrypted, and will not be encrypted again

"MySQL" "Proxysql" analysis Mysql_users table