ProxySQL: fire & forget slaves

[gandalf]

Hi to all.
For a project I have to spin-up some slaves (3 at most) connected to a master server.
Would be possible to create a fire&forget configuration on ProxySQL so that I can add these slaves to the configuration and ProxySQL will detect when they are available (and in sync with the master) automatically before using them ?

In example, slave 1 to slave 3 will get IPs 1, .2 and .3
The master has IP .10

What If I configure ProxySQL to proxy connection to the master and pre-configuring these 3 slaves? Whey they are not available, ProxySQL should keep them disconnected and not directly connection thorugh them. When one or more of these slaves will came up, it should automatically bring them in service.

Is this possible ?

[altmannmarcelo]

Hi gandalf.
That is possible yes. You can configure mysql slaves to stop / start receive traffic only if seconds_behind_master is lower than X(Configurable value). If slave is offline or is online but seconds behind master is greater than what you configured, it won’t receive traffic.

All you need to do is:

1. Add your servers to mysql_servers table:

INSERT INTO mysql_servers (hostname,hostgroup_id,port) VALUES ('master',10,3306);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,max_replication_lag) VALUES ('slave1',11,3306, 10);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,max_replication_lag) VALUES ('slave2',11,3306, 10);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,max_replication_lag) VALUES ('slave3',11,3306, 10);
INSERT INTO mysql_replication_hostgroups VALUES (10, 11, 'slave config');
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Above will configure 1 master and 3 slaves. Each slave will tolerate a max of 10 seconds delay before stopping to receive traffic.

Thanks in advance.

[gandalf]

Thank you for the response.
I’m new to ProxySQL, should I create some users for ProxySQL on each slave and on the master ?

[gandalf]

I also have another question: as i’m using a pre-configured proxysql by specifying all of my slaves, even the ones that are offline, i’ve noticed that when proxysql starts, first connections to it are going in timeout.

Is this expected, because proxysql has to try to connect to the offline slave to see if they are online or something strange is happening here ?

after a couple of minutes (may be less), connection are made properly with no timeout and i’m able to poweron and poweroff slaves with no issue, proxysql detects them automatically

[gandalf]

I had an issue when activating the slave. ProxySQL started to direct write queries to it.
This is the current configuration:

datadir="/var/lib/proxysql"

admin_variables=
{
admin_credentials="x:y"
mysql_ifaces="127.0.0.1:6032"
# refresh_interval=2000
debug=true
}

mysql_variables=
{
threads=8
max_connections=1000
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
# interfaces="0.0.0.0:6033;/tmp/proxysql.sock"
interfaces="127.0.0.1:3306"
default_schema="information_schema"
stacksize=1048576
server_version="5.7.23"
connect_timeout_server=3000

monitor_username="x"
monitor_password="y"
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=1500
monitor_read_only_interval=1500
monitor_read_only_timeout=500
monitor_replication_lag_interval=1000
monitor_slave_lag_when_null=86400

ping_interval_server_msec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}


# defines all the MySQL servers
mysql_servers =
(
{ address="10.164.0.4", port=3306, hostgroup=10 },
{ address="10.164.0.4", port=3306, hostgroup=20 },
{ address="10.164.0.11", port=3306, hostgroup=20, max_replication_lag=1 },
{ address="10.164.0.12", port=3306, hostgroup=20, max_replication_lag=1 }
)

mysql_replication_hostgroups = 
(
{ writer_hostgroup=10, reader_hostgroup=20, comment="Master/Slave" }
)

mysql_users:
(
{ username = "x", password = "y", default_hostgroup = 10, active = 1}
)

10.164.0.4 is the master, stick in hostgrup 10 and hostgroup 20
hostgroup 10 is write-only
hostgroup 20 is read-only

10.164.0.11 and 10.164.0.12 are read only slaves (only 10.164.0.11 will be powered on, when needed) and they are both stick to hostgroup 20

When 10.164.0.11 is up, proxysql detect it properly, wait for it to be in sync (replication lag < 1 second) but when ready, it tries to use it for write operations:

2018-11-25 19:53:54 MySQL_Session.cpp:2949:handler(): [WARNING] Error during query on (20,10.164.0.11,3306): 1142, UPDATE command denied to user 'x'@'10.164.0.3' for table 'a'
2018-11-25 19:53:55 MySQL_Session.cpp:2949:handler(): [WARNING] Error during query on (20,10.164.0.11,3306): 1142, UPDATE command denied to user 'x'@'10.164.0.3' for table 'b'
2018-11-25 19:53:55 MySQL_Session.cpp:2949:handler(): [WARNING] Error during query on (20,10.164.0.11,3306): 1142, UPDATE command denied to user 'x'@'10.164.0.3' for table 'a'
2018-11-25 19:53:56 MySQL_Session.cpp:2949:handler(): [WARNING] Error during query on (20,10.164.0.11,3306): 1290, The MySQL server is running with the --read-only option so it cannot execute this statement
2018-11-25 19:53:56 MySQL_Session.cpp:95:kill_query_thread(): [WARNING] KILL CONNECTION 138 on 10.164.0.11:3306
2018-11-25 19:53:56 MySQL_Session.cpp:2949:handler(): [WARNING] Error during query on (20,10.164.0.11,3306): 1142, UPDATE command denied to user 'x'@'10.164.0.3' for table 'a'
2018-11-25 19:53:56 MySQL_Session.cpp:2949:handler(): [WARNING] Error during query on (20,10.164.0.11,3306): 1142, UPDATE command denied to user 'x'@'10.164.0.3' for table 'a'
2018-11-25 19:53:57 MySQL_Session.cpp:2949:handler(): [WARNING] Error during query on (20,10.164.0.11,3306): 1142, UPDATE command denied to user 'x'@'10.164.0.3' for table 'a'
2018-11-25 19:53:57 MySQL_Session.cpp:2949:handler(): [WARNING] Error during query on (20,10.164.0.11,3306): 1290, The MySQL server is running with the --read-only option so it cannot execute this statement
2018-11-25 19:53:57 MySQL_Session.cpp:95:kill_query_thread(): [WARNING] KILL CONNECTION 180 on 10.164.0.11:3306
2018-11-25 19:53:57 MySQL_Session.cpp:2949:handler(): [WARNING] Error during query on (20,10.164.0.11,3306): 1142, UPDATE command denied to user 'x'@'10.164.0.3' for table 'a'
2018-11-25 19:53:57 MySQL_Session.cpp:2949:handler(): [WARNING] Error during query on (20,10.164.0.11,3306): 1142, UPDATE command denied to user 'x'@'10.164.0.3' for table 'a'
2018-11-25 19:53:58 MySQL_Session.cpp:2949:handler(): [WARNING] Error during query on (20,10.164.0.11,3306): 1142, UPDATE command denied to user 'x'@'10.164.0.3' for table 'c'
2018-11-25 19:53:59 MySQL_Session.cpp:2949:handler(): [WARNING] Error during query on (20,10.164.0.11,3306): 1142, UPDATE command denied to user 'x'@'10.164.0.3' for table 'a'
2018-11-25 19:53:59 MySQL_Session.cpp:2949:handler(): [WARNING] Error during query on (20,10.164.0.11,3306): 1142, UPDATE command denied to user 'x'@'10.164.0.3' for table 'a'
2018-11-25 19:54:00 MySQL_Session.cpp:2949:handler(): [WARNING] Error during query on (20,10.164.0.11,3306): 1142, UPDATE command denied to user 'x'@'10.164.0.3' for table 'a'
2018-11-25 19:54:00 MySQL_Session.cpp:2949:handler(): [WARNING] Error during query on (20,10.164.0.11,3306): 1142, UPDATE command denied to user 'x'@'10.164.0.3' for table 'a'
2018-11-25 19:54:01 MySQL_Session.cpp:2949:handler(): [WARNING] Error during query on (20,10.164.0.11,3306): 1142, INSERT command denied to user 'x'@'10.164.0.3' for table 'd'
2018-11-25 19:54:01 MySQL_Session.cpp:2949:handler(): [WARNING] Error during query on (20,10.164.0.11,3306): 1142, UPDATE command denied to user 'x'@'10.164.0.3' for table 'e'

What i’m missing?
Any time we change the configuration from the configuration file, we call

LOAD MYSQL VARIABLES FROM CONFIG;
SAVE MYSQL VARIABLES TO DISK;

Is this enough ?

[gandalf]

Any help? I’m still trying to see what is wrong with this configuration