1. MySQL Users Management Using ProxySQL Tools

MySQL Users Management Using ProxySQL Tools

Recently, we have implemented a set of new commands in ProxySQL Tools to manage MySQL users. You can create, delete, and modify users from the command line. Following commands are available:

proxysql-tools galera user list
proxysql-tools galera user create username [—-options]
proxysql-tools galera user delete username 
proxysql-tools galera user modify username [—-options]
proxysql-tools galera user set_password username 

You can set a user’s password at the time of creation with —-password option, or later on with the set_password sub-command.

Create user

Let’s add a MySQL user to ProxySQL Tools with the next params:

Username: mysql_user
Password: mysql_user_password
Active: True
Use SSL: True
Default hostgroup: 10
Default schema: information_schema
Schema locked: True
Transaction persistent: True
Fast forward: True
Backend: True
Frontend: True
Max connections: 10000

> proxysql-tools galera user create mysql_user --password mysql_user_password --active --use_ssl --default_hostgroup 10 --default_schema information_schema --schema_locked --transaction_persistent --fast_forward --backend --frontend --max_connections 10000
User mysql_user has created
> proxysql-tools galera user list
-------------+-------------------------------+---------+----------+-----------------+
|  username  |       password      | active | use_ssl | default_hostgroup |   default_schema   | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+------------+---------------------+--------+---------+-------------------+--------------------+---------------+------------------------+--------------+---------+----------+-----------------+
| mysql_user | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |  True  |   True  |         10        | information_schema |      True     |          True          |     True     |   True  |   True   |      10000      |
+------------+---------------------+--------+---------+-------------------+--------------------+---------------+------------------------+--------------+---------+----------+————————+

Modify user

Let’s now modify a user:

> proxysql-tools galera user modify mysql_user ---default_schema custom_schema
User mysql_user has modified
> proxysql-tools galera user list
-------------+--------------+---------+----------+-----------------+
|  username  |       password      | active | use_ssl | default_hostgroup |   default_schema   | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+------------+---------------------+--------+---------+-------------------+--------------------+---------------+------------------------+--------------+---------+----------+-----------------+
| mysql_user | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |  True  |   True  |         10        | custom_schema |      True     |          True          |     True     |   True  |   True   |      10000      |
+------------+---------------------+--------+---------+-------------------+--------------------+---------------+------------------------+--------------+---------+----------+————————+

Delete user

Now, let’s try to delete a user using ProxySQL Tools:

> proxysql-tools galera user delete mysql_user
User mysql_user has deleted
> proxysql-tools galera user list
User list is empty

Conclusion

The new features aim to improve MySQL user management in ProxySQL. This way, you don’t need to manually add or change users via mysql_users table, all changes can be made with ProxySQL Tools.

Previous Post Next Post