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)