ProxySQL – Sync / Import users

On my last post I gave a gasp about some key concepts of ProxySQL. Today I will demonstrate how we can import all users from MySQL to ProxySQL

To accomplish this, we will make use of,proxysql-admin a tool developed by  Percona to help you manage and install ProxySQL. If you are using ProxySQL provided by Percona, this tool is already installed, otherwise, you can download it directly from Github.

We will use the  --syncusers option, which will connect to the desired MySQL server, read all users from  mysql.user table and import or sync them to ProxySQL:

proxysql-admin --cluster-username=[mysql-user] --cluster-password=[mysql-password] --cluster-port=[mysql-port] --cluster-hostname=[mysql-host] --syncusers

This tool was originally designed to enhance the compatibility between ProxySQL and  Percona XtraDB Cluster, so, many terminology mentions cluster, however, the functionality demonstrate on this post is fully compatible with Master-Slave.

Here is one example of the command and it’s output:

$ proxysql-admin --cluster-username=root --cluster-password=sekret --cluster-port=3306 --cluster-hostname= --syncusers

Syncing user accounts from Percona XtraDB Cluster to ProxySQL

Synced Percona XtraDB Cluster users to the ProxySQL database!

It worth mention that every time a new user is added or an existing user has its password changed, we also need to update the user on ProxySQL. With proxysql-admin, all we need to do on those cases is re-run the tool with  --syncusers  to get everything back in sync.

Leave a Reply

Your email address will not be published. Required fields are marked *