def get_network_interface_state(network_interface):
client = boto3.client('ec2')
response = client.describe_network_interfaces(
NetworkInterfaceIds=[network_interface])
status = response['NetworkInterfaces'][0]['Attachment']['Status']
LOG.debug('Interface %s, status = %s', network_interface, status)
return status
def server_set_wsrep_desync(cfg, server_ip, port, wsrep_desync='ON'):
"""
Set MySQL server in desync state.
:param cfg: ProxySQL Tools configuration
:type cfg: ConfigParser.ConfigParser
:param server_ip: Server IP address
:param port: Server port
:param wsrep_desync: Value for wsrep_desync
"""
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'))
backends = proxysql.find_backends(writer_hostgroup_id) + \
proxysql.find_backends(reader_hostgroup_id)
for backend in backends:
if backend.hostname == server_ip and backend.port == port:
backend.connect(cfg.get('galera', 'cluster_username'),
cfg.get('galera', 'cluster_password'))
backend.execute("SET GLOBAL wsrep_desync=%s", wsrep_desync)
return
raise ProxySQLBackendNotFound('Could not find server %s:%d' %
(server_ip, port))
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 register_writer(galera_cluster,
proxysql,
writer_hostgroup_id,
reader_hostgroup_id,
ignore_writer=None):
"""
Checks ProxySQL and Galera cluster and makes sure there is one
healthy registered writer.
: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
"""
# Get writer backend. If doesn't exist - add.
# If exists - check state of respective Galera node.
# if the Galera node is not healthy - delete the backend and add a healthy
# one.
LOG.debug('Registering writers')
try:
for backend in proxysql.find_backends(writer_hostgroup_id):
check_backend(backend,
galera_cluster,
proxysql,
writer_hostgroup_id,
'Writer',
limit=1,
ignore_backend=ignore_writer,
recovered_hostgroup_id=reader_hostgroup_id,
recoverd_comment='Reader')
except ProxySQLBackendNotFound:
# add it
register_synced_backends(galera_cluster,
proxysql,
writer_hostgroup_id,
comment='Writer',
limit=1,
ignore_backend=ignore_writer)
try:
proxysql.find_backends(writer_hostgroup_id, BackendStatus.online)
except ProxySQLBackendNotFound:
LOG.warn('No writer backends were registered. '
'Will try to add previously ignored backends')
register_synced_backends(galera_cluster,
proxysql,
writer_hostgroup_id,
comment='Writer',
limit=1)
def docker_pull_image(image):
"""Pull the specified image using docker-py. This function will parse the
result from docker-py and raise an exception if there is an error.
:param str image: Name of the image to pull
"""
client = docker_client()
api = client.api
response = api.pull(image)
LOG.debug('Response: %s', response)
def ping(self):
"""Check health of ProxySQL.
:return: True if ProxySQL healthy and False otherwise.
:rtype: bool"""
try:
result = self.execute('SELECT 1 AS result')
return result[0]['result'] == '1'
except pymysql.err.OperationalError:
LOG.debug('ProxySQL %s:%d is dead', self.host, self.port)
return False
def register_readers(galera_cluster,
proxysql,
writer_hostgroup_id,
reader_hostgroup_id,
ignore_writer=None):
"""
Checks ProxySQL and Galera cluster and makes sure readers are registered.
: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
"""
LOG.debug('Registering readers')
try:
writer = proxysql.find_backends(writer_hostgroup_id)[0]
except ProxySQLBackendNotFound as err:
LOG.warn(err)
register_synced_backends(galera_cluster,
proxysql,
writer_hostgroup_id,
comment='Writer',
limit=1,
ignore_backend=ignore_writer)
writer = proxysql.find_backends(writer_hostgroup_id)[0]
try:
num_readers = 0
readers = proxysql.find_backends(reader_hostgroup_id)
for backend in readers:
LOG.debug('Comparing %s and %s', backend, writer)
if backend == writer:
# Don't register writer as reader for now
continue
LOG.debug('Do not match')
check_backend(backend, galera_cluster, proxysql,
reader_hostgroup_id, 'Reader')
num_readers += 1
if num_readers == 0:
LOG.warn('Did not register any readers')
check_backend(writer, galera_cluster, proxysql,
reader_hostgroup_id, 'Reader')
except ProxySQLBackendNotFound:
# If there are no readers , register writer as reader as well
LOG.debug('Reader backends not found')
writer_as_reader = writer
writer_as_reader.hostgroup_id = reader_hostgroup_id
register_synced_backends(galera_cluster,
proxysql,
reader_hostgroup_id,
comment='Reader',
ignore_backend=writer_as_reader)
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 debian_container():
client = docker_client()
api = client.api
# Pull the image locally
docker_pull_image(DEBIAN_IMAGE)
container = api.create_container(image=DEBIAN_IMAGE,
labels=[CONTAINERS_FOR_TESTING_LABEL],
command='/bin/sleep 36000')
LOG.debug('Starting container %s', container['Id'])
api.start(container['Id'])
container_info = client.containers.get(container['Id'])
yield container_info
api.remove_container(container=container['Id'], force=True)
def proxysql_instance(proxysql_container):
LOG.debug("Container %r", proxysql_container)
connection = ProxySQL(host=proxysql_container['ip'],
port=PROXYSQL_ADMIN_PORT,
user=PROXYSQL_ADMIN_USER,
password=PROXYSQL_ADMIN_PASSWORD)
def check_started():
LOG.debug('Checking if proxysql is up')
ret = connection.ping()
LOG.debug(ret)
return ret
# Allow ProxySQL to startup completely. The problem is that ProxySQL starts
# listening to the admin port before it has initialized completely which
# causes the test to fail with the exception:
# OperationalError: (2013, 'Lost connection to MySQL server during query')
eventually(check_started, retries=15, sleep_time=4)
return connection
def proxysql_container(proxysql_config_contents, tmpdir, container_network):
client = docker_client()
api = client.api
# Pull the container image locally first
docker_pull_image(PROXYSQL_IMAGE)
# Setup the ProxySQL config
config = tmpdir.join('proxysql.cnf')
config.write(proxysql_config_contents)
LOG.debug('ProxySQL Config:\n %s', proxysql_config_contents)
# The ports that the ProxySQL container will be listening on inside the
# container
container_ports = [PROXYSQL_ADMIN_PORT, PROXYSQL_CLIENT_PORT]
container_info = {
'name': 'proxysql01',
'ip': '172.25.3.100',
}
host_config = api.create_host_config(
binds=["{}:/etc/proxysql.cnf".format(str(config))], port_bindings={})
networking_config = api.create_networking_config({
container_network:
api.create_endpoint_config(ipv4_address=container_info['ip'])
})
container = api.create_container(image=PROXYSQL_IMAGE,
name='proxysql01',
labels=[CONTAINERS_FOR_TESTING_LABEL],
ports=container_ports,
host_config=host_config,
networking_config=networking_config)
LOG.debug('Starting container %s', container['Id'])
api.start(container['Id'])
# 1/0
yield container_info
api.remove_container(container=container['Id'], force=True)
def galera_register(cfg):
"""Registers Galera cluster nodes with ProxySQL."""
kwargs = {}
try:
kwargs['user'] = cfg.get('galera', 'cluster_username')
except NoOptionError:
pass
try:
kwargs['password'] = cfg.get('galera', 'cluster_password')
except NoOptionError:
pass
LOG.debug('Galera config %r', kwargs)
galera_cluster = GaleraCluster(cfg.get('galera', 'cluster_host'), **kwargs)
kwargs = get_proxysql_options(cfg)
LOG.debug('ProxySQL config %r', kwargs)
proxysql = ProxySQL(**kwargs)
load_balancing_mode = cfg.get('galera', 'load_balancing_mode')
writer_hostgroup_id = int(cfg.get('galera', 'writer_hostgroup_id'))
reader_hostgroup_id = int(cfg.get('galera', 'reader_hostgroup_id'))
if load_balancing_mode == 'singlewriter':
kwargs = {}
try:
host, port = cfg.get('galera', 'writer_blacklist').split(':')
bcknd = ProxySQLMySQLBackend(host,
hostgroup_id=writer_hostgroup_id,
port=port)
kwargs['ignore_writer'] = bcknd
except NoOptionError:
pass
singlewriter(galera_cluster, proxysql, writer_hostgroup_id,
reader_hostgroup_id, **kwargs)
else:
raise NotImplementedError('Balancing mode %s not implemented yet.' %
load_balancing_mode)
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 check_backend(
backend,
galera_cluster,
proxysql,
hostgroup_id,
comment, # pylint: disable=too-many-arguments
limit=None,
ignore_backend=None,
recovered_hostgroup_id=None,
recoverd_comment=None):
"""
Check health of given backed and if necessary replace it.
:param backend: MySQL backend.
:type backend: ProxySQLMySQLBackend
:param galera_cluster: GaleraCluster instance.
:type galera_cluster: GaleraCluster
:param proxysql: ProxySQL instance.
:type proxysql: ProxySQL
:param hostgroup_id: Hostgroup_id which the backend belongs to.
:type hostgroup_id: int
:param comment: Comment to add to mysql_servers in ProxySQL
: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
:param recovered_hostgroup_id: If backend recovers from OFFLINE_SOFT assign
it to this hostgroup_id. Default hostgroup_id.
:type recovered_hostgroup_id: int
:param recoverd_comment: If backend recovers from OFFLINE_SOFT set
this comment
:type recoverd_comment: str
:return: True if backend successfully registered.
:rtype: bool
"""
# check it
LOG.debug('Backend %s is already registered', backend)
LOG.debug('Checking its health')
try:
node = galera_cluster.find_node(backend.hostname, backend.port)
state = node.wsrep_local_state
LOG.debug('%s state: %d', node, state)
if state == GaleraNodeState.SYNCED:
LOG.debug('Node %s (%s) is healthy', node, backend.status)
if backend.status != BackendStatus.online:
LOG.debug('Deregistering %s (%s)', backend, backend.status)
proxysql.deregister_backend(backend)
backend.status = BackendStatus.online
if not recovered_hostgroup_id:
recovered_hostgroup_id = hostgroup_id
backend.hostgroup_id = recovered_hostgroup_id
if recoverd_comment:
backend.comment = recoverd_comment
LOG.debug('Registering %s (%s)', backend, backend.status)
proxysql.register_backend(backend)
else:
LOG.warn(
'Node %s is reachable but unhealty, '
'setting it OFFLINE_SOFT', node)
proxysql.set_status(backend, BackendStatus.offline_soft)
register_synced_backends(galera_cluster,
proxysql,
hostgroup_id,
comment=comment,
limit=limit,
ignore_backend=ignore_backend)
except GaleraClusterNodeNotFound:
LOG.warn('Backend %s is not a cluster member. Deregistering it.',
backend)
proxysql.deregister_backend(backend)
register_synced_backends(galera_cluster,
proxysql,
hostgroup_id,
comment=comment,
limit=limit,
ignore_backend=ignore_backend)
except OperationalError as err:
LOG.error(err)
LOG.error('Looks like backend %s is unhealthy. Deregistering it.',
backend)
proxysql.deregister_backend(backend)
register_synced_backends(galera_cluster,
proxysql,
hostgroup_id,
comment=comment,
limit=limit,
ignore_backend=ignore_backend)
return True
def check_started():
LOG.debug('Checking if proxysql is up')
ret = connection.ping()
LOG.debug(ret)
return ret
def notify_master(cfg):
"""The notify_master script for keepalived."""
LOG.debug('Switching to master role and executing keepalived '
'notify_master script.')
aws_notify_master(cfg)
