proxysql_tools.proxysql package

Submodules

proxysql_tools.proxysql.proxysql module

ProxySQL classes

class proxysql_tools.proxysql.proxysql.BackendStatus

Bases: object

Status of ProxySQL backend

offline_hard = 'OFFLINE_HARD'

offline_soft = 'OFFLINE_SOFT'

online = 'ONLINE'

shunned = 'SHUNNED'

class proxysql_tools.proxysql.proxysql.ProxySQL(host='localhost', port=3306, user='root', password=None, socket=None)

Bases: object

ProxySQL describes a single ProxySQL instance.

Parameters:

• host – ProxySQL hostname.
• port – Port on which ProxySQL listens to admin connections.
• user – ProxySQL admin user.
• password – Password for ProxySQL admin.
• socket – Socket to connect to ProxySQL admin interface.

add_user(user)

Add MySQL user

Parameters:user (ProxySQLMySQLUser) – user for add

backend_registered(backend)

Check if backend is registered.

Parameters:backend – ProxySQLMySQLBackend instance Returns:True if registered, False otherwise Return type:bool

delete_user(username)

Delete MySQL user

Parameters:username (str) – username of user

deregister_backend(backend)

Deregister a Galera node from ProxySQL

Parameters:backend (ProxySQLMySQLBackend) – Galera node.

execute(query, *args)

Execute query in ProxySQL.

Parameters:query (str) – Query to execute. Returns:Query result or None if the query is not supposed to return result Return type:dict

find_backends(hostgroup_id, status=None)

Get writer from mysql_servers

Parameters:

• hostgroup_id (int) – writer hostgroup_id
• status (BackendStatus) – Look only for backends in this status

Returns:

Writer MySQL backend or None if doesn’t exist

Return type:

list(ProxySQLMySQLBackend)

Raise:

ProxySQLBackendNotFound

get_user(username)

Get user by username

Parameters:username – Username Returns:User information Return type:ProxySQLMySQLUser Raise:ProxySQLUserNotFound

get_users()

Get mysql users

Returns:List of users or empty list Return type:list(ProxySQLMySQLUser)

ping()

Check health of ProxySQL.

Returns:True if ProxySQL healthy and False otherwise. Return type:bool

register_backend(backend)

Register Galera node in ProxySQL

Parameters:backend (ProxySQLMySQLBackend) – Galera node.

reload_runtime()

Reload the ProxySQL runtime configuration.

save_user()

Save user to on-disk database

set_status(backend, status)

Update status of a backend in ProxySQL

class proxysql_tools.proxysql.proxysql.ProxySQLMySQLBackend(hostname, hostgroup_id=0, port=3306, status='ONLINE', weight=1, compression=0, max_connections=10000, max_replication_lag=0, use_ssl=False, max_latency_ms=0, comment=None)

Bases: object

ProxySQLMySQLBackend describes record in ProxySQL

table mysql_servers.

CREATE TABLE mysql_servers (
    hostgroup_id INT NOT NULL DEFAULT 0,
    hostname VARCHAR NOT NULL,
    port INT NOT NULL DEFAULT 3306,
    status VARCHAR CHECK (UPPER(status) IN
        ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD'))
        NOT NULL DEFAULT 'ONLINE',
    weight INT CHECK (weight >= 0) NOT NULL DEFAULT 1,
    compression INT CHECK (compression >=0 AND compression <= 102400)
        NOT NULL DEFAULT 0,
    max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000,
    max_replication_lag INT CHECK (max_replication_lag >= 0
        AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0,
    use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0,
    max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0)
        NOT NULL DEFAULT 0,
    comment VARCHAR NOT NULL DEFAULT '',
    PRIMARY KEY (hostgroup_id, hostname, port) )

connect(username, password)

Make a MySQL connection to the backend.

Parameters:

• username – MySQL user.
• password – MySQL password.

execute(query, *args)

Execute query in MySQL Backend.

Parameters:query (str) – Query to execute. Returns:Query result or None if the query is not supposed to return result Return type:dict

class proxysql_tools.proxysql.proxysql.ProxySQLMySQLUser(username='root', password=None, active=True, use_ssl=False, default_hostgroup=0, default_schema='information_schema', schema_locked=False, transaction_persistent=False, fast_forward=False, backend=True, frontend=True, max_connections=10000)

Bases: object

ProxySQLMySQLUser describes record in ProxySQL table mysql_users.

CREATE TABLE mysql_users (
    username VARCHAR NOT NULL,
    password VARCHAR,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
    default_hostgroup INT NOT NULL DEFAULT 0,
    default_schema VARCHAR,
    schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,
    transaction_persistent INT CHECK (transaction_persistent IN (0,1))
        NOT NULL DEFAULT 0,
    fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,
    backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,
    frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,
    max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,
    PRIMARY KEY (username, backend),
    UNIQUE (username, frontend))

Parameters:

• username – MySQL username to connect to ProxySQL or Galera node.
• password – MySQL password.
• active (bool) – Users with active = 0 will be tracked in the database, but will be never loaded in the in-memory data structures.
• use_ssl (bool) – Use SSL to connect to MySQL or not
• default_hostgroup – If there is no matching rule for the queries sent by the users, the traffic it generates is sent to the specified hostgroup.
• default_schema – The schema to which the connection should change by default.
• schema_locked (bool) – not supported yet.
• transaction_persistent (bool) – if this is set for the user with which the MySQL client is connecting to ProxySQL (thus a “frontend” user - see below), transactions started within a hostgroup will remain within that hostgroup regardless of any other rules.
• fast_forward (bool) – If set, it bypasses the query processing layer (rewriting, caching) and passes the query directly to the backend server.
• frontend (bool) – If True, this (username, password) pair is used for authenticating to the ProxySQL instance.
• backend – If True, this (username, password) pair is used for authenticating to the mysqld servers against any hostgroup.
• max_connections – Maximum number of connection this user can create to MySQL node.

Module contents