Managing multiple proxysql instances ?

[Jeff Racer]

Hi,

With a view to running an instance of proxysql on each web server - what methods would you recommend for administration of these instances ?

For example, if we needed to add a new mysql user to the mysql cluster, we would also need to add this user to each proxysql install - how might this be managed centrally, rather than logging in to the admin interface of each proxysql install.

Or if caching rules needed changing across all proxysql installations simultaneously  - could we overwrite the sqllite config file and issue a service restart on all instances, or is there a better way to achieve these changes ?

[René Cannaò]

Hi Jeff,

ProxySQL doesn't have any built-in solution to synchronize an army of proxies, and this is intentional: I preferred to make it easy to reconfigure (SQL!), so that anybody can manage it with the tools they are already using. In fact, it is so easy to reconfigure ProxySQL at runtime that you could use a variety of approaches, like using a configuration management tool (Puppet, Chef, Ansible, Salt, etc) or a service discovery tools (Consul, Etcd, zookeeper) to automatically reconfigure ProxySQL if needed.
Configuration management tools and/or service discovery tools can get the configuration from a centralized database/repository/other-source-of-truth and push the change to all the proxies: this solution scales well, and avoid adding more complexity within proxysql.

For your convenience, there are already public modules for:

* Ansible: see https://github.com/ansible/ansible/pull/19872

* Puppet: https://forge.puppet.com/puppet/proxysql

About "could we overwrite the sqllite config file and issue a service restart on all instances" : this is possible but I do not recommend it, because it voids the whole purpose of having a highly available proxy if you have to restart it. The best solution is to reconfigure it at runtime.

Thanks

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Todd Bryant]

Hi Jeff,

We run 4 ProxySQL servers in front of our DB cluster and all servers can respond for the same IP's using BGP AnyCast, this means that if any ProxySQL server goes offline the clients connecting to it will reconnect through another server without needing any failover handling in their code as the IP will now route to a different server. Similar things can be done using CARP/VRRP/Keepalived etc.

I have created the below PHP script which we run from the command line as a cron on each of the ProxySQL servers every 15 minutes to keep it all updated, this way we simply manage the users in the backend DB and ProxySQL updates accordingly.

Features:

Add user to ProxySQL if added in Percona / MySQL backed

Update password in ProxySQL if changed in Percona /MySQL backend

Delete user from ProxySQL if removed from MySQL backend

Load users into memory and save to disk

Can run in verbose, silent or debug modes

Limitations

Only works with a single backend DB cluster

Import user must be manually added to proxysql prior to use if you wish for the script to keep working when a backend db server is offline, alternatively you could connect directly to the backend for the first run and then change it to use the local ProxySQL server

Requirements:

User with select rights to the User and Password columns in mysql.user

Admin user credentials for ProxySQL DB

PHP and PHP-MySQL Packages

ProxySQL User Sync

------------------

This script synchronises users from the backend MySQL DB with ProxySQL

USAGE:

php proxysql_user_sync.php help              - This help menu

php proxysql_user_sync.php test_sync         - Outputs all the changes the script would make but doesnt actually do them

php proxysql_user_sync.php import_only       - Synchronise users to ProxySQL but do NOT load them into memory or save to disk

php proxysql_user_sync.php sync              - Synchronise users to ProxySQL, load them into memory and save to disk

php proxysql_user_sync.php load              - Load existing users in ProxySQL mysql_users table to memory and save to disk

php proxysql_user_sync.php <command> debug   - Display PHP and MySQL errors for the requested task

php proxysql_user_sync.php <command> silent  - No output

<?php

error_reporting(E_ERROR | E_PARSE);

// Create connection to ProxySQL DB

// Modify the username / password / port as required for connecting to the local ProxySQL DB

$proxy_conn = mysqli_connect('127.0.0.1', 'admin', 'admin_password', '', 6032);

// Check connection

if (!$proxy_conn) {

die("Connection failed to ProxySQL: " . mysqli_connect_error() . PHP_EOL);

}

// Create connection to ProxySQL DB

// Modify the username / password / port as required for connecting to the backend MySQL DB (Recommend connecting through ProxySQL)

$db_conn = mysqli_connect('127.0.0.1', 'import_user', 'import_user_password', 'mysql');

// Check connection

if (!$db_conn) {

die("Connection failed to Percona via ProxySQL: " . mysqli_connect_error() . PHP_EOL);

}

// Defaults - DO NOT MODIFY

$changes = 0;

$failed = 0;

$debug = false;

$silent = false;

$action = 'help';

if (isset($argv[1])) {

if ($argv[1] == 'load') {

$action = 'load';

} elseif ($argv[1] == 'sync') {

$action = 'sync';

} elseif ($argv[1] == 'import_only') {

$action = 'import_only';

} elseif ($argv[1] == 'test_sync') {

$action = 'test_sync';

} 

}

if (isset($argv[2])) {

if ($argv[2] == 'debug') {

$debug = true;

error_reporting(E_ALL);

} elseif ($argv[2] == 'silent') {

$silent = true;

}

}

if ($action == 'help') {

echo PHP_EOL;

echo PHP_EOL;

echo 'ProxySQL User Sync' . PHP_EOL;

echo '------------------' . PHP_EOL;

echo 'This script synchronises users from the backend MySQL DB with ProxySQL' . PHP_EOL . PHP_EOL;

echo 'USAGE:' . PHP_EOL;

echo 'php proxysql_user_sync.php help              - This help menu' . PHP_EOL;

echo 'php proxysql_user_sync.php test_sync         - Outputs all the changes the script would make but doesnt actually do them' . PHP_EOL;

echo 'php proxysql_user_sync.php import_only       - Synchronise users to ProxySQL but do NOT load them into memory or save to disk' . PHP_EOL;

echo 'php proxysql_user_sync.php sync              - Synchronise users to ProxySQL, load them into memory and save to disk' . PHP_EOL;

echo 'php proxysql_user_sync.php load              - Load existing users in ProxySQL mysql_users table to memory and save to disk' . PHP_EOL;

echo 'php proxysql_user_sync.php <command> debug   - Display PHP and MySQL errors for the requested task' . PHP_EOL;

echo 'php proxysql_user_sync.php <command> silent  - No output' . PHP_EOL;

} elseif ($action == 'sync' || $action == 'test_sync' || $action == 'import_only') {

$db_sql = "SELECT DISTINCT User, Password FROM user WHERE Password <> '' ORDER BY User";

$db_result = mysqli_query($db_conn, $db_sql);

if (!$db_result && $debug) { echo mysqli_error($db_conn) . PHP_EOL; }

if (mysqli_num_rows($db_result) > 0) {

$prev_username = "";

while($db_row = mysqli_fetch_assoc($db_result)) {

// Ensure this is not a duplicate user

if ($prev_username != $db_row["User"]) {

// Lookup Percona user in ProxySQL

$proxy_result = mysqli_query($proxy_conn, "SELECT username, password FROM main.mysql_users WHERE username='" . $db_row["User"] . "'");

if (!$proxy_result && $debug) { echo mysqli_error($proxy_conn) . PHP_EOL; }

// User doesn't exist in ProxySQL .. Add it

if (mysqli_num_rows($proxy_result) == 0) {

if ($action == 'sync' || $action == 'import_only') {

if (mysqli_query($proxy_conn, "INSERT INTO main.mysql_users (username,password,default_hostgroup) VALUES ('" . $db_row["User"] . "','" . $db_row["Password"] . "', 10)")) {

if (!$silent) { echo $db_row["User"] . ' - Added to ProxySQL' . PHP_EOL; }

$changes++;

} else {

if (!$silent) { echo $db_row["User"] . ' - Failed to add user to ProxySQL, users out of sync' . PHP_EOL; }

if ($debug) { echo mysqli_error($proxy_conn) . PHP_EOL; }

$failed++;

}

} else {

if (!$silent) { echo $db_row["User"] . ' - needs to be ADDED to ProxySQL' . PHP_EOL; }

$changes++;

}

// User exists in ProxySQL .. check passwords match

} elseif (mysqli_num_rows($proxy_result) == 1) {

// Get returned row from ProxySQL DB

$proxy_row = mysqli_fetch_assoc($proxy_result);

// If Passwords don't match .. Update ProxySQL

if ($proxy_row["password"] != $db_row["Password"]) {

if ($action == 'sync' || $action == 'import_only') {

if (mysqli_query($proxy_conn, "UPDATE main.mysql_users SET password='" . $db_row["Password"] . "' WHERE username='" . $db_row["User"] . "'")) {

if (!$silent) { echo $db_row["User"] . ' - Password Updated ' . PHP_EOL; }

$changes++;

} else {

if (!$silent) { echo $db_row["User"] . ' - Failed to update password, passwords out of sync' . PHP_EOL; }

if ($debug) { echo mysqli_error($proxy_conn) . PHP_EOL; }

$failed++;

}

} else {

if (!$silent) { echo $db_row["User"] . ' - PASSWORD needs to be UPDATED in ProxySQL' . PHP_EOL; }

$changes++;

}

}

}

}

$prev_username = $db_row["User"]; // Take note of the last username processed to prevent duplicates

}

} else {

if (!$silent) { echo "No users found in Percona" . PHP_EOL; }

}

// Lookup users in ProxySQL to check they exist in Percona

$proxy_sql = "SELECT username, password FROM main.mysql_users ORDER BY username";

$proxy_result = mysqli_query($proxy_conn, $proxy_sql);

if (!$proxy_result && $debug) { echo mysqli_error($proxy_conn) . PHP_EOL; }

if (mysqli_num_rows($proxy_result) > 0) {

while($proxy_row = mysqli_fetch_assoc($proxy_result)) {

// Lookup ProxySQL user in Percona

$db_result = mysqli_query($db_conn, "SELECT DISTINCT User, Password FROM user WHERE User='" . $proxy_row["username"] . "' ORDER BY User LIMIT 1");

if (!$db_result && $debug) { echo mysqli_error($db_conn) . PHP_EOL; }

// If user doesn't exist in Percona .. Remove them from ProxySQL

if (mysqli_num_rows($db_result) == 0) {

if ($action == 'sync' || $action == 'import_only') {

if (mysqli_query($proxy_conn, "DELETE FROM main.mysql_users WHERE username='" . $proxy_row["username"] . "'")) {

if (!$silent) { echo $proxy_row["username"] . ' - Removed from ProxySQL' . PHP_EOL; }

$changes++;

} else {

if (!$silent) { echo $proxy_row["username"] . ' - Failed to remove user from ProxySQL, users out of sync' . PHP_EOL; }

if ($debug) { echo mysqli_error($proxy_conn) . PHP_EOL; }

$failed++;

}

} else {

if (!$silent) { echo $proxy_row["username"] . ' - Needs to be REMOVED from ProxySQL' . PHP_EOL; }

$changes++;

}

}

}

}

if (!$silent) { 

if ($action == 'test_sync') {

echo $changes . ' change(s) required' . PHP_EOL;

} else {

echo $changes . ' User(s) modified.' . PHP_EOL;

echo $failed . ' Failed' . PHP_EOL;

}

}

}

if (($changes > 0 && $action == 'sync') || $action == 'load') {

if (mysqli_query($proxy_conn, "LOAD MYSQL USERS TO RUNTIME")) {

if (!$silent) { echo 'LOAD MYSQL USERS TO RUNTIME        - SUCCESS' . PHP_EOL; }

} else {

if (!$silent) { echo 'LOAD MYSQL USERS TO RUNTIME        - FAILED!!!' . PHP_EOL; }

if ($debug) { echo mysqli_error($proxy_conn) . PHP_EOL; }

}

if (mysqli_query($proxy_conn, "SAVE MYSQL USERS TO DISK")) {

if (!$silent) { echo 'SAVE MYSQL USERS TO DISK           - SUCCESS' . PHP_EOL; }

} else {

if (!$silent) { echo 'SAVE MYSQL USERS TO DISK           - FAILED!!!' . PHP_EOL; }

if ($debug) { echo mysqli_error($proxy_conn) . PHP_EOL; }

}

}

mysqli_close($proxy_conn);

mysqli_close($db_conn);

[Todd Bryant]

Forgot to mention a few other limitations

Only supports a single host group, there are many ways to add support for multiple groups but it was not a requirement for our situation so I did not bother.

Host group is hard coded to 10, you can modify it if required on Line 81 as part of the INSERT INTO main.mysql_users statement

Users must have a password else they will be skipped (can modify this on line 63 by removing the WHERE clause)

If multiple users with the same username exist in MySQL only the first instance of it will be imported into ProxySQL