Not the answer you need?
Register and ask your own question!

ProxySQL: fire & forget slaves

gandalfgandalf ContributorCurrent User Role Beginner
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 ?

Comments

  • altmannmarceloaltmannmarcelo Percona Current User Role Novice
    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.
  • gandalfgandalf Contributor Current User Role Beginner
    Thank you for the response.
    I'm new to ProxySQL, should I create some users for ProxySQL on each slave and on the master ?
  • gandalfgandalf Contributor Current User Role Beginner
    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
  • gandalfgandalf Contributor Current User Role Beginner
    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'&#64;'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'&#64;'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'&#64;'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'&#64;'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'&#64;'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'&#64;'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'&#64;'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'&#64;'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'&#64;'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'&#64;'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'&#64;'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'&#64;'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'&#64;'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'&#64;'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'&#64;'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 ?
  • gandalfgandalf Contributor Current User Role Beginner
    Any help? I'm still trying to see what is wrong with this configuration
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.