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_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 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 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 bug1258464killer(default_file):
"""
bug1258464killer checks status of a local Galera node
and if a) There are stuck COMMIT queries and b) There is an ALTER TABLE
it will kill the node. This command workarounds a known bug
https://bugs.launchpad.net/percona-xtradb-cluster/+bug/1258464
"""
if default_file:
if os.path.isfile(default_file):
bug1258464(default_file)
else:
LOG.error('File not found : %s', default_file)
else:
bug1258464('/root/.my.cnf')
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 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 main(ctx, cfg, debug, config, version):
"""proxysql-tool entrypoint"""
if ctx.invoked_subcommand is None:
if version:
print(__version__)
exit(0)
else:
print(ctx.get_help())
exit(-1)
setup_logging(LOG, debug=debug)
if os.path.exists(config):
cfg.read(config)
else:
LOG.error("Config file %s doesn't exist", config)
exit(1)
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 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 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 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 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 bug1258464(default_file):
"""Check if node is affected by
https://bugs.launchpad.net/percona-xtradb-cluster/+bug/1258464
and if yes, kill the node.
:param default_file: Path to my.cnf
:return: True if it killed the node.
:rtype: bool
"""
try:
connection = pymysql.connect(read_default_file=default_file)
with connection.cursor() as cursor:
cursor.execute("SELECT COUNT(*)"
"FROM information_schema.processlist "
"WHERE State = 'wsrep in pre-commit stage' "
"AND Info = 'COMMIT'")
count_pre_commit = cursor.fetchone()[0]
cursor.execute("SELECT COUNT(*)"
"FROM information_schema.processlist "
"WHERE State = "
"'Waiting for table metadata lock' "
"AND Info LIKE 'ALTER TABLE%'")
count_waiting = cursor.fetchone()[0]
if count_pre_commit > 100 and count_waiting > 0:
my_cnf_path = get_my_cnf()
pid = get_pid(my_cnf_path)
kill_process(pid)
return True
except OperationalError as err:
LOG.error(str(err))
except OSError as err:
LOG.error(str(err))
except NotImplementedError as err:
LOG.error(str(err))
return False
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 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 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_sync(cfg, ip_address, port):
"""Set server to sync state."""
try:
server_set_wsrep_desync(cfg, ip_address, port, wsrep_desync='OFF')
except NotImplementedError as err:
LOG.error(err)
exit(1)
except (NoOptionError, NoSectionError) as err:
LOG.error('Failed to parse config: %s', err)
exit(1)
except MySQLError as err:
LOG.error('Failed to talk to database: %s', err)
except ProxySQLBackendNotFound as err:
LOG.error(err)
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 register(cfg):
"""Registers Galera cluster nodes with ProxySQL."""
try:
galera_register(cfg)
except NotImplementedError as err:
LOG.error(err)
exit(1)
except (NoOptionError, NoSectionError) as err:
LOG.error('Failed to parse config: %s', err)
exit(1)
except MySQLError as err:
LOG.error('Failed to talk to database: %s', err)
def status(cfg):
"""Show status of MySQL backends."""
try:
server_status(cfg)
except NotImplementedError as err:
LOG.error(err)
exit(1)
except (NoOptionError, NoSectionError) as err:
LOG.error('Failed to parse config: %s', err)
exit(1)
except MySQLError as err:
LOG.error('Failed to talk to database: %s', err)
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 aws_notify_master(cfg):
"""The function moves network interface to local instance and brings it up.
Steps:
- Detach network interface if attached to anywhere.
- Attach the network interface to the local instance.
- Configure IP address on this instance
:param cfg: config object
"""
try:
os.environ['AWS_ACCESS_KEY_ID'] = cfg.get('aws', 'aws_access_key_id')
os.environ['AWS_SECRET_ACCESS_KEY'] = cfg.get('aws',
'aws_secret_access_key')
os.environ['AWS_DEFAULT_REGION'] = cfg.get('aws', 'aws_default_region')
except NoOptionError:
LOG.error('aws_access_key_id, aws_secret_access_key and '
'aws_default_region must be defined in '
'aws section of the config file.')
exit(-1)
instance_id = get_my_instance_id()
try:
ip = cfg.get('proxysql', 'virtual_ip')
netmask = cfg.get('proxysql', 'virtual_netmask')
network_interface = get_network_interface(ip)
if network_interface_attached(network_interface):
detach_network_interface(network_interface)
local_interface = "eth%d" % DEVICE_INDEX
ensure_local_interface_is_gone(local_interface)
ensure_network_interface_is_detached(network_interface)
attach_network_interface(network_interface, instance_id)
configure_local_interface(local_interface, ip, netmask)
except NoOptionError as err:
LOG.error('virtual_ip and virtual_netmask must be defined in '
'proxysql section of the config file.')
LOG.error(err)
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)
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)
def check_started():
LOG.debug('Checking if proxysql is up')
ret = connection.ping()
LOG.debug(ret)
return ret
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
