How to easily bring 80 MySQL users into ProxySQL?

Question

I am setting up ProxySQL in front of a Percona MySQL server with 88 user accounts. I don't even know most of their passwords. Passwords are stored in MySQL as hashes but are stored in ProxySQL in plain text. How can I easily bring in all 88 accounts into ProxySQL at once? Or is there a pass-through authentication switch I can turn on?

Answer 1

Since you're using ProxySQL with Percona, there's a nifty tool made by the good people at Percona that does exactly what you're looking for (sync's mysql users to proxysql).

If you install ProxySQL using the Percona RPM, it comes with a nifty script that helps with managing your ProxySQL instance called "proxysql-admin". That script has the option to "--syncusers". Here's an excerpt of the help info for the script:

[root@localhost ~]# /usr/bin/proxysql-admin
Usage: [ options ]
Options:
  --config-file=<config-file>        Read login credentials from a configuration file (overrides any login credentials specified on the command line)
  --quick-demo                       Setup a quick demo with no authentication
  --proxysql-datadir=<datadir>       Specify proxysql data directory location
  --proxysql-username=user_name      Username for connecting to the ProxySQL service
  --proxysql-password[=password]     Password for connecting to the ProxySQL service
  --proxysql-port=port_num           Port Nr. for connecting to the ProxySQL service
  --proxysql-hostname=host_name      Hostname for connecting to the ProxySQL service
  --cluster-username=user_name       Username for connecting to the Percona XtraDB Cluster node
  --cluster-password[=password]      Password for connecting to the Percona XtraDB Cluster node
  --cluster-port=port_num            Port Nr. for connecting to the Percona XtraDB Cluster node
  --cluster-hostname=host_name       Hostname for connecting to the Percona XtraDB Cluster node
  --cluster-app-username=user_name   Application username for connecting to the Percona XtraDB Cluster node
  --cluster-app-password[=password]  Application password for connecting to the Percona XtraDB Cluster node
  --without-cluster-app-user         Configure Percona XtraDB Cluster without application user
  --monitor-username=user_name       Username for monitoring Percona XtraDB Cluster nodes through ProxySQL
  --monitor-password[=password]      Password for monitoring Percona XtraDB Cluster nodes through ProxySQL
  --without-check-monitor-user       Configure ProxySQL without checking/attempting to create monitor user
  --enable, -e                       Auto-configure Percona XtraDB Cluster nodes into ProxySQL
  --disable, -d                      Remove any Percona XtraDB Cluster configurations from ProxySQL
  --node-check-interval=3000         Interval for monitoring node checker script (in milliseconds)
  --mode=[loadbal|singlewrite]       ProxySQL read/write configuration mode, currently supporting: 'loadbal' and 'singlewrite' (the default) modes
  --write-node=host_name:port        Writer node to accept write statments. This option is supported only when using --mode=singlewrite
                                     Can accept comma delimited list with the first listed being the highest priority.
  --include-slaves=host_name:port    Add specified slave node(s) to ProxySQL, these nodes will go into the reader hostgroup and will only be put into
                                     the writer hostgroup if all cluster nodes are down.  Slaves must be read only.  Can accept comma delimited list.
                                     If this is used make sure 'read_only=1' is in the slave's my.cnf
  --adduser                          Adds the Percona XtraDB Cluster application user to the ProxySQL database
  --syncusers                        Sync user accounts currently configured in MySQL to ProxySQL (deletes ProxySQL users not in MySQL)
  --version, -v                      Print version info
[root@localhost ~]#

Notice the "--syncusers" option down there at the bottom. That's what you're looking for, yes? ... and it's made and distributed by Percona. :)

Hope this helps!

Answer 2

Here is the official doc that explains password storing (and hashing) within ProxySQL: Password management

Now the task is to export all usernames and SHA1-hashed passwords from mysql into the proxysql backend, which is a sqlite3 file. Here is how I did it.

A. Create a file like /tmp/user.sql with this in it:

select "INSERT INTO mysql_users (username,password) VALUES (", CONCAT("'",User,"'"), ",", CONCAT("'",Password,"'"), ");" from user WHERE Password LIKE "*%" order by User;

B. Run it against your mysqld server, as root or with whatever you have with permissions to at least view the mysql.user table. Add -h or -p or whatever you need to the mysql command:

linux# mysql -N mysql < /tmp/user.sql > /tmp/input.sql

C. Edit /tmp/input.sql to carefully review the users you have, omit duplicate lines, etc. You may have the same users appearing with different password hashes. The same password always maps to the same hash, here, so it may take some sorting out if you have old users defined in mysql, perhaps from different client IP's with different passwords. Don't worry about the rest of the formatting and tabs in that file. Just delete lines you don't want.

D. On your proxysql server (same as mysqld, maybe), copy /var/lib/proxysql/proxysql.db to /tmp, get your input.sql file in place, and try the import:

linux# sqlite3 /tmp/proxysql.db < /tmp/input.sql

It may warn about existing user accounts, which is harmless but worth reviewing in your input file on any such lines:

Error: near line 34: columns username, frontend are not unique
Error: near line 35: columns username, frontend are not unique

E. View your new achievement in sqlite3 directly:

linux# sqlite3 /tmp/proxysql.db
sqlite> select * from mysql_users;
...
glpi|*F210F7D92AE10C493DDA19A33D4BC50C6E82517|1|0|0||0|0|0|1|1|10000
icinga|*79BAD610D871E3323F655960E4BD2399FAAF7C1|1|0|0||0|0|0|1|1|10000
icinga_check|*B87C5B0712F247F7632A450A36D1285C8666496|1|0|0||0|0|0|1|1|10000
ipplan|*EE50B7BF86175E257803509C4B04B51DF557D19|1|0|0||0|0|0|1|1|10000
...

F. Once you are confident it looks right, make the same edits for real in the production proxysql.db file, log in as admin to proxysql, and bring the new users to life:

proxysql> LOAD MYSQL USERS FROM DISK;

G. Now you're on your own. Bring them into RUNTIME, test, set their other preferences (like default schema and hostgroup), etc.

I make no warranties or claims on what damage you do here. This was just how I brought in a long list of existing users into ProxySQL. I was trying to slip in proxysql on port 3306 on my database server and quietly move mysql to 2206. No dice. That's never going to happen in my environment. Maybe you are lucky and have something simpler to manage. At least this will get you started and you will learn something about the ProxySQL internals. Good luck!

Answer 3

I've improved a bit based on the answer from IcarusNM.

This script expects two files with credentials in the current directory.

• proxyadmin.cnf
• backend.cnf

It simply loads a list of user accounts from each backend, and replaces that in the proxy.

#!/bin/bash

TMPFILE=$(mktemp)

USER_BLACKLIST="'root', 'phpmyadmin'"
GET_QUERY="select \"REPLACE INTO mysql_users (username,password,default_hostgroup) VALUES (\", CONCAT(\"\'\",user.User,\"\'\"), \",\", CONCAT(\"\'\",Password,\"\'\"), \",\", %s, \");\" FROM user LEFT JOIN db on (user.User=db.User) WHERE db.db IS NOT NULL AND Password LIKE \"*%%\" AND user.User NOT IN (%b) order by user.User;"

# First backend
BACKEND=192.168.1.100
HOSTGROUP=1

printf "$GET_QUERY" "$HOSTGROUP" "$USER_BLACKLIST" \
        | mysql --defaults-file=backend.cnf --host="$BACKEND" --skip-column-names mysql \
        | tee $TMPFILE

# Second backend, duplicate to get more.
BACKEND=192.168.66.101
HOSTGROUP=2

printf "$GET_QUERY" "$HOSTGROUP" "$USER_BLACKLIST" \
        | mysql --defaults-file=backend.cnf --host="$BACKEND" --skip-column-names mysql \
        | tee -a $TMPFILE


# Load users into ProxySQL.
cat $TMPFILE | mysql --defaults-file=proxyadmin.cnf

# Enable new config.
echo "LOAD MYSQL USERS TO RUNTIME" | mysql --defaults-file=proxyadmin.cnf

# Cleanup
rm -v $TMPFILE

Answer 4

I've improved the query of IcarusNM to work with MySql 5.7 and ProxySQL 2.0

SELECT DISTINCT 
CONCAT("INSERT INTO mysql_users (username,password, default_hostgroup) VALUES ("
  ,CONCAT("'",User,"'")
  ,","
  ,CONCAT("'",authentication_string,"'",",10")
  ,");"
) 
from mysql.user 
WHERE authentication_string LIKE "*%" 
order by User;