MySQL middleware proxysql (3): admin Management interface

Admin management interface for 1.ProxySQL

When Proxysql is started, it listens on two ports:

• (1). Admin management interface, default port is 6032. This port is used to view, configure Proxysql.
• (2). The interface that receives the SQL statement, the default port is 6033, and this interface is similar to the 3306 port of MySQL.

Proxysql Admin Interface is a MySQL protocol interface, so you can directly use the MySQL client, Navicat and other tools to connect this management interface.

For example, use the MySQL client to connect to the Proxysql management interface. The following uses the default user name and password for the management interface admin:admin .

mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt 'admin> 'mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  

Because the configuration of Proxysql is all saved in several libraries, it is very convenient to change the configuration of proxysql by sending some SQL commands through the management interface. proxysql resolves certain specific commands sent through the interface that are valid for proxysql and translates them into an embedded SQLite3 database engine to run.

For example, view a table in a library or library.

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 |+-----+---------------+-------------------------------------+admin> Show Tables from disk;+------------------------------------+| 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 | | Scheduler |+------------------------------------+

Note that SQLite3 and MySQL use different SQL syntax, so commands that work correctly on MySQL do not necessarily run on SQLite3. For example, although the Admin management interface receives the use command, it does not switch to the default schema because SQLite3 does not support the use feature.
Proxysql will try to automatically adjust the MySQL syntax to the corresponding SQLITE3 syntax, such as a show databases; statement that translates to a SQLite3 premium.

The configuration of proxysql is almost always done through the management interface, and through the admin interface, you can modify almost all of the configuration and make it effective. only the configuration of two variables is required to restart Proxysql to take effect , they are: mysql-threads and mysql-stacksize .

2. Variables related to admin management interface

In general, the admin interface basically does not require additional configuration, most likely configured is the Admin interface Administrator user name, password.

2.1 Admin-admin_credentials

This variable controls the admin account of the admin management interface. The default administrator account and password are admin:admin , but this default user can only be used locally. If you want to connect remotely to Proxysql, such as using Navicat on Windows to connect to the Proxysql management interface on Linux, you must customize an administrator account.

For example, add a myuser:myuser user password pair.

admin> select @@admin-admin_credentials;    # 当前用户名和密码+---------------------------+| @@admin-admin_credentials |+---------------------------+| admin:admin               |+---------------------------+admin> set admin-admin_credentials='admin:admin;myuser:myuser';admin> select @@admin-admin_credentials;+---------------------------+| @@admin-admin_credentials |+---------------------------+| admin:admin;myuser:myuser |+---------------------------+admin> load admin variables to runtime;  # 使修改立即生效admin> save admin variables to disk;     # 使修改永久保存到磁盘

Once modified, you can use the user name and password to connect to the management interface.

mysql -umyuser -pmyuser -P6032 -h127.0.0.1 --prompt 'admin> '

All of the configuration operations are modifying the corresponding tables in the main library.

select * from global_variables  where variable_name='admin-admin_credentials';+-------------------------+---------------------------+| variable_name           | variable_value            |+-------------------------+---------------------------+| admin-admin_credentials | admin:admin;myuser:myuser |+-------------------------+---------------------------+

Therefore, the preceding set statement is equivalent to the following UPDATE statement:

update global_variables set variable_value='admin:admin;myuser:myuser' where variable_name='admin-admin_credentials';

You must differentiate between the username of the admin management interface and the user name in Mysql_users.

• The user of the Admin management interface is connected to the management interface (default port 6032) for managing, configuring Proxysql.
  
• The user name in the Mysql_users table is the application connection proxysql (default port 6033), and the Proxysql connection to the backend MySQL servers used by the user. Its purpose is to send and route SQL statements, similar to the 3306 port of MySQL server. Therefore, the user in this table must already exist and be authorized on the backend MySQL server.

At present, the users in the Proxysql Mysql_users are responsible for the Proxysql front-end, but also responsible for the proxysql back end of the connection, (the author said) in the future version of the two connected user systems may be separated to make proxysql more secure.

The user of the Admin management interface must not exist in Mysql_users, for security reasons, to prevent users from guessing the user in Mysql_users through admin interface.

2.2 Admin-stats_credentials

This variable controls the admin interface of the ordinary user, the user in this variable does not have Super administrator privileges, can only view the monitor library and main library about the statistics, the other libraries are not visible, and there is no write permission.

The default normal user name and password are stats:stats .

mysql> select @@admin-stats_credentials;+---------------------------+| @@admin-stats_credentials |+---------------------------+| stats:stats               |+---------------------------+mysql> set admin-stats_credentials='stats:stats;mystats:mystats';

[[email protected] ~]# mysql-ustats-pstats-p6032-h127.0.0.1 mysql> Show databases;+-----+---------------+-- -----------------------------------+| Seq | name | File |+-----+---------------+-------------------------------------+| 0 |                                     Main | || 2 |                                     Monitor | || 3 | Stats_history | /var/lib/proxysql/proxysql_stats.db |+-----+---------------+-------------------------------------+mysql> Show Tables from main;+--------------------------------------+| 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 |+--------------------------------------+

Similarly, the user in this variable must not exist in the Mysql_users table.

2.3 admin-mysql_ifaces

This variable specifies the listener address for the admin interface, in the form of a semicolon-delimited hostname:port list. The default listener is in the 0.0.0.0:6032 .

Note that the UNIX domain socket is allowed to listen so that applications within the host can be processed directly.

For example:

SET admin-mysql_ifaces='127.0.0.1:6032;/tmp/proxysql_admin.sock'

MySQL middleware proxysql (3): admin Management interface