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 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 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 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 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 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 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 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 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 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 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 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 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 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