Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

[MySQL] [ProxySQL] Analysis of mysql_ users Table

2025-01-26 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

[MySQL] [ProxySQL] Analysis of mysql_users Table 1. Table definition and field description table DDL definition: CREATE TABLE mysql_users (username VARCHAR NOT NULL, # username password VARCHAR, # password active INT CHECK (active IN (0Magne1)) NOT NULL DEFAULT 1, # whether to enable use_ssl INT CHECK (use_ssl IN (0Magne1)) NOT NULL DEFAULT 0, # whether to use SSL to connect to default_hostgroup INT NOT NULL DEFAULT 0 # default query routing group default_schema VARCHAR, # default database schema_locked INT CHECK (schema_locked IN (0Magne1)) NOT NULL DEFAULT 0, # limit users to transaction_persistent INT CHECK (transaction_persistent IN (0meme 1)) NOT NULL DEFAULT 1, # transaction route assignment persistence Statements of the same transaction will not be assigned to different groups fast_forward INT CHECK (fast_forward IN (0L1)) NOT NULL DEFAULT 0, # Quick recovery of idle threads backend INT CHECK (backend IN (0L1)) NOT NULL DEFAULT 1, # whether it is the account frontend INT CHECK (frontend IN (0L1)) NOT NULL DEFAULT 1 of the back-end database # whether it is the account of ProxySQL itself (access to ProxySQL through port 6033) max_connections INT CHECK (max_connections > = 0) NOT NULL DEFAULT 10000, # the maximum number of connections to ProxysSQL by the user PRIMARY KEY (username, backend), # Primary key Backend account user name unique UNIQUE (username, frontend)) # uniqueness constraint. Special description of the unique parameters of the user name in the frontend: transaction_persistent:

​ is particularly important for read-write separation, ensuring that all statements in the same transaction are routed to the same set of examples to prevent contextual data inconsistency 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 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 is not repeated in the read group). Because in the case of traditional replication (after sync is not enabled), the transaction will not be transferred to the slave database until it is committed, resulting in an inconsistency in the transaction and the problem that one cannot read his own modified data.

Fast_forward:

Look at the source code (lib\ MySQL_Thread.cpp):

If (myds- > myds_type==MYDS_BACKEND & & myds- > sess- > statusoverview 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% SV% 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;}

​ for example, when fast_forward is turned on, some threads that are connected but idle will be marked by ProxySQL as unhealthy threads and will be terminated immediately (perhaps for thread-saving considerations). This parameter is not enabled by default.

Backend and frontend:

There may be a separation of front and rear accounts in future versions of ​.

two。 User table maintenance

Let's first take a look at the libraries and tables related to configuration

Mysql > show databases +-+ | seq | name | file | +- -+ | 0 | main | | # Common Library | 2 | disk | / var/lib/proxysql/proxysql.db | # configure Archive Library | 3 | stats | | # Statistical Information Base | 4 | monitor | | # Monitoring Information Database | 5 | stats_history | / var/lib/proxysql/proxysql_stats.db | # Statistical History Database +-- +

ProxySQL itself has five libraries, three libraries stored in memory and three SQLite libraries saved on disk.

After we log in through the 6032 management port, the default is the main library. All configuration changes must be made in this library, and the disk archive library will not be directly affected. Let's take a look next.

Tables in the main library: mysql > show tables from main +-+ | tables | +-+ | global_variables | | # basic configuration parameters of ProxySQL | Similar to MySQL | mysql_collations | # configure support for MySQL character set | mysql_group_replication_hostgroups | # MGR-related tables for automatic allocation of read and write groups of instances | mysql_query_rules | # routing table | mysql_replication_hostgroups | # tables related to master-slave replication Automatic allocation of read and write groups for instances | mysql_servers | # Storage of MySQL instance information | mysql_users | # Storage of MySQL users at this stage, of course, there is a plan to separate front and rear accounts in the future | proxysql_servers | # Storage ProxySQL information Used for ProxySQL Cluster synchronization | runtime_checksums_values | # Storage check value of the running environment | runtime_global_variables | # | runtime_mysql_group_replication_hostgroups | # | runtime_mysql_query_rules | # | runtime_mysql_replication_hostgroups | # corresponds to the above But the configuration being used by the running environment | runtime_mysql_servers | # | runtime_mysql_users | # | runtime_proxysql_servers | # | runtime_scheduler | # | scheduler | # scheduled task table + -- + tables in the disk library: mysql > show tables from disk +-- + | tables | +-+ | global_variables | # | mysql_collations | # | | mysql_group_replication_hostgroups | # | mysql_query_rules | # | mysql_replication_hostgroups | # basically corresponds to the above table | mysql_replication_hostgroups_v122 | # but two older tables are added | mysql_servers | # | mysql_servers_v122 | # | mysql_users | # | proxysql_ | Servers | # | scheduler | # +-+

It is not difficult to observe that the nine configuration tables appear three times in different situations, representing the configuration information in the current memory, the configuration information currently in use, and the configuration information in the current disk file.

This requires us to configure the configurations of the three places separately as needed.

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 mysql_users table of the main library, and there is no change in the running environment or on disk.

Load from memory to running environment LOAD MYSQL USERS TO RUNTIME; is saved from memory to disk file SAVE MYSQL USERS TO DISK; is downloaded from running environment to memory SAVE MYSQL USERS TO MEMORY; is loaded from disk file to memory LOAD MYSQL USERS TO MEMORY; configuration management diagram

From top to bottom is SAVE XXX TO XXX.

From bottom to top, it's LOAD XXX FROM XXX.

3. Encrypted storage of plaintext passwords

ProxySQL supports plaintext and hash encryption to save passwords. The default method is generally plaintext. As shown below:

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

Plaintext passwords can be encrypted in two ways

1. Encrypt on input

Since the server provided by ProxySQL does not have an encryption function, it needs to be encrypted in MySQL, and then replace the plaintext password in the insert statement.

# the original plaintext insert sentence is as follows: # ProxySQLProxySQL > insert into mysql_users (username,password,active,default_hostgroup) values ('predecessor_beast','114514',1,69); # encrypt the MySQL instance first # MySQLroot@localhost 16:53: [(none)] > select PASSWORD (' 114514') +-- + | PASSWORD ('114514') | +-+ | * D9050F2D99C3DDD8138912B7BDF8F4BACBE3A8E7 | +- -- + 1 row in set 1 warning (0.00 sec) # replace the plaintext password ProxySQL > insert into mysql_users (username,password,active,default_hostgroup) values ('predecessor_beast','114514',1,69) in the insert statement two。 Use the admin-hash_passwords feature

After admin-hash_passwords is enabled in global_variable, by loading the mysql_users table containing plaintext passwords into the running environment, all plaintext passwords in the table will be replaced by hash encrypted passwords, and then save to memory and finally save to disk can be permanently encrypted and saved.

ProxySQL > select * from global_variables where variable_name like "passwords%" +-- +-+ | variable_name | variable_value | +-- +-+ | admin-hash_passwords | true | # confirm that the admin-hash_passwords feature is enabled +- -+-+ 1 row in set (0.00 sec) # insert new user (plaintext 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 the user table of plaintext passwords ProxySQL > SELECT username,password FROM mysql_users +-+-+ | username | password | +-+- -- + | proxysql | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | # has been encrypted before | proxysql_web | 123456 | # unencrypted | mgr33061 | 123456 | # unencrypted | mgr33061_backend | 123456 | | # unencrypted | predecessor_beast | 114514 | # newly inserted unencrypted user +-+-+ # View the user table in the running environment ProxySQL > select username Password from runtime_mysql_users +-+-+ | username | password | +-+- -- + | proxysql | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | # due to frontend account | proxysql_web | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | # previously single account appears in pairs | mgr33061 | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | proxysql | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | # everything in the runtime environment is encrypted | proxysql_web | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | mgr33061_backend | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | # there is no new user +-+-- + # loaded 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 Those that have already been encrypted will not be encrypted again.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report