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?
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!
Here is the simple doc that explains password storing (and hashing) within ProxySQL: https://github.com/sysown/proxysql/wiki/Passwords-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!
I've improved a bit based on the answer from IcarusNM.
This script expects two files with credentials in the current directory.
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
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;