DBS ProxySQL Admin: User Synchronisation for MySQL Shell
DBS ProxySQL Admin is a MySQL Shell plugin built by DBS Medya. It reads the user accounts and password hashes on the MySQL server and reconciles them with the user table in the ProxySQL admin interface.
Every command lives in the dbs.proxysql.admin namespace. It runs from Python and JavaScript in the interactive shell, and from scheduled jobs straight on the operating system command line. It is the first tool in the DBS MySQL Shell plugin collection.
What it solves
In an architecture that uses ProxySQL, clients connect to ProxySQL rather than to the database server, so every account has to be defined on the ProxySQL side as well. When an account is created, a password changes, or an account is dropped, someone has to connect to the admin interface, update the table by hand, and then write the change to runtime and to disk. The plugin reduces all of that to a single command.
Carries modern authentication across
It transfers caching_sha2_password β the default algorithm in MySQL 8.0 and 8.4 β and mysql_native_password hashes to ProxySQL as they are. Plaintext passwords are never needed at any stage. The transfer relies on capabilities present in ProxySQL 2.6 and later.
Decides which account is synchronised
A MySQL account is identified by a username and host pair, while ProxySQL keeps only the username. Once the same name has different passwords on different hosts, which one applies becomes ambiguous. The required_host setting pins the row to be synchronised to a single host.
Rolls back a half-finished change
The change is written to disk first, then loaded to runtime. When one step fails, the clean table in runtime is copied back to disk. A half-written user record is left neither in memory nor on disk.
Manages more than one ProxySQL target
Each target keeps its own configuration file. Switching the active configuration within a session moves the work to another ProxySQL server. Architectures that keep service accounts and application accounts on separate proxies are managed this way.
Cleans up orphaned accounts
Accounts dropped from MySQL but left behind in ProxySQL leave an access gap. The plugin looks for a name across every host in MySQL, and removes it from ProxySQL when it finds it on none of them. Excluded accounts stay outside that scan.
Generates the configuration with a wizard
Rather than writing the file by hand, it builds one through questions in the shell. Because the file holds a password, it is written with permissions that let only its owner read it.
Commands and usage
Command names follow the active language. Python uses the underscored form, while JavaScript and the command line use the capitalised one. The functions are the same.
| Operation | Python | JavaScript |
|---|---|---|
| Synchronise users | dbs.proxysql.admin.user_sync() | dbs.proxysql.admin.userSync() |
| Update passwords only | dbs.proxysql.admin.update_passwords() | dbs.proxysql.admin.updatePasswords() |
| Delete orphaned accounts | dbs.proxysql.admin.delete_orphans() | dbs.proxysql.admin.deleteOrphans() |
| Show the active configuration | dbs.proxysql.admin.status() | dbs.proxysql.admin.status() |
| Switch configuration | dbs.proxysql.admin.use_config("/path.ini") | dbs.proxysql.admin.useConfig("/path.ini") |
| Generate a file with the wizard | dbs.proxysql.admin.create_config() | dbs.proxysql.admin.createConfig() |
The same commands run without entering the shell. Scheduled jobs and continuous integration pipelines use this form.
mysqlsh root@mysql-host:3306 -- dbs proxysql admin user-sync
mysqlsh root@mysql-host:3306 -- dbs proxysql admin user-sync /etc/proxysql/app.ini
mysqlsh root@mysql-host:3306 -- dbs proxysql admin delete-orphans
mysqlsh root@mysql-host:3306 -- dbs proxysql admin status
The configuration file carries the address of the ProxySQL admin interface, the accounts to keep out of synchronisation, and the host group new accounts are assigned to.
[proxysql]
host = 127.0.0.1
port = 6032
user = radmin
password = radmin
default_hostgroup = 0
excluded_users = root, admin, mysql.sys
required_host = %
Note: when required_host is changed after the fact, synchronised passwords
can be rewritten with the values from the new host row. Assess the effect before
changing this setting after installation.
Operating scope
Dependencies
The plugin code uses only the Python standard library and MySQL Shell's own session. No extra package is needed to install it, which lets it enter restricted production environments.
Supported authentication
caching_sha2_password and mysql_native_password. The receiving side of the transfer is ProxySQL 2.6 and later.
Protected accounts
root, admin, mysql.sys, mysql.session and mysql.infoschema are excluded from synchronisation by default. The list is changed from the configuration.
Configuration order
The path given to the command, the session's active configuration, the PROXYSQL_SYNC_CONFIG environment variable, then the default files in the home directory and on the system, tried in that order.
Ways to run it
Python and JavaScript in the interactive shell, and the command line without entering the shell. The same commands and the same configuration apply on all three paths.
Licence
Published under the GNU General Public License version 2. The complete source is on GitHub.