def server_status(cfg):
"""Print list of MySQL servers registered in ProxySQL and their status."""
kwargs = get_proxysql_options(cfg)
LOG.debug('ProxySQL config %r', kwargs)
proxysql = ProxySQL(**kwargs)
writer_hostgroup_id = int(cfg.get('galera', 'writer_hostgroup_id'))
reader_hostgroup_id = int(cfg.get('galera', 'reader_hostgroup_id'))
for hostgroup_id, name in [(writer_hostgroup_id, 'Writers'),
(reader_hostgroup_id, 'Readers')]:
servers = PrettyTable([
'hostgroup_id', 'hostname', 'port', 'status', 'weight',
'compression', 'max_connections', 'max_replication_lag', 'use_ssl',
'max_latency_ms', 'comment'
])
servers.align = 'r'
servers.align['hostname'] = 'l' # pylint: disable=unsupported-assignment-operation
servers.align['comment'] = 'l' # pylint: disable=unsupported-assignment-operation
LOG.info('%s:', name)
for backend in proxysql.find_backends(hostgroup_id):
servers.add_row([
backend.hostgroup_id, backend.hostname, backend.port,
backend.status, backend.weight, backend.compression,
backend.max_connections, backend.max_replication_lag,
backend.use_ssl, backend.max_latency_ms, backend.comment
])
print(servers)
def singlewriter(galera_cluster,
proxysql,
writer_hostgroup_id,
reader_hostgroup_id,
ignore_writer=None):
"""
Implements singlewriter balancing mode.
:param galera_cluster: GaleraCluster instance.
:type galera_cluster: GaleraCluster
:param proxysql: ProxySQL instance
:type proxysql: ProxySQL
:param writer_hostgroup_id: Writer hostgroup_id
:type writer_hostgroup_id: int
:param reader_hostgroup_id: Reader hostgroup_id
:type reader_hostgroup_id: int
:param ignore_writer: Do not make this backend writer
:type ignore_writer: ProxySQLMySQLBackend
"""
register_writer(galera_cluster,
proxysql,
writer_hostgroup_id,
reader_hostgroup_id,
ignore_writer=ignore_writer)
register_readers(galera_cluster, proxysql, writer_hostgroup_id,
reader_hostgroup_id)
LOG.debug('Register all missing backends')
for galera_node in galera_cluster.find_synced_nodes():
reader = ProxySQLMySQLBackend(galera_node.host,
hostgroup_id=reader_hostgroup_id,
port=galera_node.port,
comment='Reader')
writer = ProxySQLMySQLBackend(galera_node.host,
hostgroup_id=writer_hostgroup_id,
port=galera_node.port)
if not (proxysql.backend_registered(reader)
or proxysql.backend_registered(writer)):
proxysql.register_backend(reader)
LOG.info('Added backend %s to hostgroup %d', reader,
reader_hostgroup_id)
LOG.debug('Make sure writer is not reader')
writer = proxysql.find_backends(writer_hostgroup_id)[0]
readers = proxysql.find_backends(reader_hostgroup_id)
writer_as_reader = writer
writer_as_reader.hostgroup_id = reader_hostgroup_id
is_readers_offline = False
if writer_as_reader in readers:
readers_without_writer = readers[:]
readers_without_writer.remove(writer_as_reader)
is_readers_offline = all(x.status == BackendStatus.offline_soft
for x in readers_without_writer)
if len(readers) > 2 and proxysql.backend_registered(writer_as_reader) \
and not is_readers_offline:
proxysql.deregister_backend(writer_as_reader)
def delete(cfg, username):
"""Delete MySQL backend user by username"""
try:
delete_user(cfg, username)
LOG.info('User %s has deleted', username)
except MySQLError as err:
LOG.error('Failed to talk to database: %s', err)
except (NoOptionError, NoSectionError) as err:
LOG.error('Failed to parse config: %s', err)
exit(1)
def modify(ctx, cfg, username):
"""Modify MySQL backend user by username"""
try:
modify_user(cfg, username, ctx.args)
LOG.info("User %s has modified", username)
except ProxySQLUserNotFound:
LOG.error("User not found")
exit(1)
except MySQLError as err:
LOG.error('Failed to talk to database: %s', err)
except ValueError:
LOG.error("Invalid input")
exit(1)
def register_synced_backends(
galera_cluster,
proxysql, # pylint: disable=too-many-arguments
hostgroup_id,
comment=None,
limit=None,
ignore_backend=None):
"""
Find SYNCED node and register it as a backend.
:param galera_cluster: GaleraCluster instance.
:type galera_cluster: GaleraCluster
:param proxysql: ProxySQL instance
:type proxysql: ProxySQL
:param hostgroup_id: hostgroup_id
:type hostgroup_id: int
:param comment: Optional comment to add to mysql_server
:type comment: str
:param limit: Register not more than limit number of backends
:type limit: int
:param ignore_backend: Do not register this backend
:type ignore_backend: ProxySQLMySQLBackend
"""
try:
galera_nodes = galera_cluster.find_synced_nodes()
if ignore_backend:
node = GaleraNode(ignore_backend.hostname,
port=ignore_backend.port)
LOG.debug('Ignoring backend %s', ignore_backend)
if node in galera_nodes:
LOG.debug('Remove %s from candidates', ignore_backend)
galera_nodes.remove(node)
if limit:
candidate_nodes = galera_nodes[:limit]
else:
candidate_nodes = galera_nodes
for galera_node in candidate_nodes:
backend = ProxySQLMySQLBackend(galera_node.host,
hostgroup_id=hostgroup_id,
port=galera_node.port,
comment=comment)
proxysql.register_backend(backend)
LOG.info('Added backend %s to hostgroup %d', backend, hostgroup_id)
except GaleraClusterSyncedNodeNotFound as err:
LOG.error(err)
def set_password(cfg, username, password):
"""Change password of exists MySQL user"""
try:
change_password(cfg, username, password)
LOG.info('Password for user %s changed', username)
except ProxySQLUserNotFound:
LOG.error("User not found")
exit(1)
except MySQLError as err:
LOG.error('Failed to talk to database: %s', err)
except (NoOptionError, NoSectionError) as err:
LOG.error('Failed to parse config: %s', err)
exit(1)
except ProxySQLBackendNotFound as err:
LOG.error('ProxySQL backends not found: %s', err)
exit(1)
def eventually(func, *args, **kwargs):
retries = kwargs.pop('retries', 90)
sleep_time = kwargs.pop('sleep_time', 0.5)
for i in xrange(retries):
try:
if func(*args, **kwargs):
return
else:
LOG.info('Waiting for %s to return True', func)
time.sleep(sleep_time)
except Exception:
time.sleep(sleep_time)
continue
raise EnvironmentError('Function %s never returned True' % func)
def get_users(cfg):
"""Print list of MySQL users from mysql_users"""
args = get_proxysql_options(cfg)
users = ProxySQL(**args).get_users()
if not users:
LOG.info('User list is empty')
return
table = PrettyTable([
'username', 'password', 'active', 'use_ssl', 'default_hostgroup',
'default_schema', 'schema_locked', 'transaction_persistent',
'fast_forward', 'backend', 'frontend', 'max_connections'
])
for user in users:
table.add_row([
user.username, user.password, user.active, user.use_ssl,
user.default_hostgroup, user.default_schema, user.schema_locked,
user.transaction_persistent, user.fast_forward, user.backend,
user.frontend, user.max_connections
])
print(table)
def ping(cfg):
"""Checks the health of ProxySQL."""
kwargs_maps = {
'host': 'host',
'port': 'admin_port',
'user': '******',
'password': '******'
}
kwargs = {}
for key in kwargs_maps:
try:
kwargs[key] = cfg.get('proxysql', kwargs_maps[key])
except NoOptionError:
pass
if ProxySQL(**kwargs).ping():
LOG.info('ProxySQL is alive')
exit(0)
else:
LOG.info('ProxySQL is dead')
exit(1)
def find_synced_nodes(self):
"""Find a node in the cluster in SYNCED state.
:return: List of Galera node in SYNCED state.
:rtype: list(GaleraNode)
:raise: GaleraClusterSyncedNodeNotFound
"""
LOG.debug('Looking for a SYNCED node')
nodes = []
for galera_node in self._nodes:
try:
state = galera_node.wsrep_local_state
LOG.debug('%s state: %s', galera_node, state)
if state == GaleraNodeState.SYNCED:
nodes.append(galera_node)
except OperationalError as err:
LOG.error(err)
LOG.info('Skipping node %s', galera_node)
if nodes:
return nodes
else:
raise GaleraClusterSyncedNodeNotFound('Cluster has '
'no SYNCED nodes')
def create(
cfg,
username,
password,
active,
use_ssl, # pylint: disable=too-many-arguments
default_hostgroup,
default_schema,
schema_locked,
transaction_persistent,
fast_forward,
backend,
frontend,
max_connections):
"""Add user of MySQL backend to ProxySQL"""
kwargs = {
'username': username,
'password': password,
'use_ssl': use_ssl,
'active': active,
'default_hostgroup': default_hostgroup,
'default_schema': default_schema,
'schema_locked': schema_locked,
'transaction_persistent': transaction_persistent,
'backend': backend,
'frontend': frontend,
'fast_forward': fast_forward,
'max_connections': max_connections
}
try:
create_user(cfg, kwargs)
LOG.info('User %s successfully created', username)
except MySQLError as err:
LOG.error('Failed to talk to database: %s', err)
except (NoOptionError, NoSectionError) as err:
LOG.error('Failed to parse config: %s', err)
exit(1)
