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 test__galera_server_status(percona_xtradb_cluster_three_node,
proxysql_instance, tmpdir):
wait_for_cluster_nodes_to_become_healthy(percona_xtradb_cluster_three_node)
hostgroup_writer = 10
hostgroup_reader = 11
rw_map = {0: hostgroup_writer, 1: hostgroup_reader, 2: hostgroup_reader}
for i in xrange(3):
backend = ProxySQLMySQLBackend(
hostname=percona_xtradb_cluster_three_node[i]['ip'],
port=percona_xtradb_cluster_three_node[0]['mysql_port'],
hostgroup_id=rw_map[i])
proxysql_instance.register_backend(backend)
blacklist = '{}:3306'.format(percona_xtradb_cluster_three_node[2]['ip'])
nodes = [
percona_xtradb_cluster_three_node[0]['ip'] + ':3306',
percona_xtradb_cluster_three_node[1]['ip'] + ':3306',
percona_xtradb_cluster_three_node[2]['ip'] + ':3306'
]
config = proxysql_tools_config_2(proxysql_instance, nodes, 'root', 'r00t',
hostgroup_writer, hostgroup_reader,
blacklist, 'monitor', 'monitor')
config_file = str(tmpdir.join('proxysql-tool.cfg'))
with open(config_file, 'w') as fh:
config.write(fh)
proxysql_tools.LOG.debug('proxysql-tools config: \n%s', config)
runner = CliRunner()
result = runner.invoke(main,
['--config', config_file, 'galera', 'register'])
assert result.exit_code == 0
result = runner.invoke(
main, ['--config', config_file, 'galera', 'server', 'status'])
assert result.exit_code == 0
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 test_deregister_backend(mock_execute, mock_runtime, proxysql):
"""
:param mock_execute:
:param mock_runtime:
:param comment:
:param query:
:param proxysql:
:type proxysql: ProxySQL
"""
backend = ProxySQLMySQLBackend('foo', hostgroup_id=10, port=3307)
proxysql.deregister_backend(backend)
query = "DELETE FROM mysql_servers WHERE hostgroup_id=10 AND hostname='foo' AND port=3307"
mock_execute.assert_called_once_with(query)
mock_runtime.assert_called_once_with()
def test_register_backend(mock_execute, mock_runtime, comment, query,
proxysql):
"""
:param mock_execute:
:param mock_runtime:
:param comment:
:param query:
:param proxysql:
:type proxysql: ProxySQL
"""
backend = ProxySQLMySQLBackend('foo', comment=comment)
proxysql.register_backend(backend)
mock_execute.assert_called_once_with(query)
mock_runtime.assert_called_once_with()
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 test_proxysql_mysql_backend():
be = ProxySQLMySQLBackend('foo',
hostgroup_id='0',
port='3307',
status=BackendStatus.offline_hard,
weight='1',
compression='1',
max_connections='10',
max_replication_lag='20',
use_ssl=100,
max_latency_ms='30',
comment='bar')
assert be.hostgroup_id == 0
assert be.port == 3307
assert be.status == 'OFFLINE_HARD'
assert be.weight == 1
assert be.compression == 1
assert be.max_connections == 10
assert be.max_replication_lag == 20
assert be.use_ssl is True
assert be.max_latency_ms == 30
assert be.comment == 'bar'
mock_pymysql.connect.assert_called_once_with(**kwargs_out)
@pytest.mark.parametrize('response, backend', [([{
u'status': 'ONLINE',
u'comment': '',
u'compression': '0',
u'weight': '1',
u'hostname': '192.168.90.2',
u'hostgroup_id': '10',
u'use_ssl': '0',
u'max_replication_lag': '0',
u'port': '3306',
u'max_latency_ms': '0',
u'max_connections': '10000'
}], ProxySQLMySQLBackend('192.168.90.2', hostgroup_id=10, port=3306))])
@mock.patch.object(ProxySQL, 'execute')
def test_find_backends(mock_execute, proxysql, response, backend):
mock_execute.return_value = response
assert proxysql.find_backends(10)[0] == backend
@mock.patch.object(ProxySQL, 'execute')
def test_find_backends_raises(mock_execute, proxysql):
mock_execute.return_value = ()
with pytest.raises(ProxySQLBackendNotFound):
proxysql.find_backends(10)
@pytest.mark.parametrize('response', [([{
u'username': '******',
def proxysql_mysql_backend():
return ProxySQLMySQLBackend('127.0.0.1')
def test_writer(proxysql):
backend = ProxySQLMySQLBackend('192.168.90.2', hostgroup_id=10)
proxysql.register_backend(backend)
assert backend == proxysql.find_backends(10)[0]
def test_register_backend(proxysql):
backend = ProxySQLMySQLBackend('192.168.90.2', hostgroup_id=10)
proxysql.register_backend(backend)
proxysql.deregister_backend(backend)
def test__galera_user_set_password_if_user_is_exist(
percona_xtradb_cluster_three_node, proxysql_instance, tmpdir):
wait_for_cluster_nodes_to_become_healthy(percona_xtradb_cluster_three_node)
hostgroup_writer = 10
hostgroup_reader = 11
rw_map = {0: hostgroup_writer, 1: hostgroup_reader, 2: hostgroup_reader}
for i in xrange(3):
backend = ProxySQLMySQLBackend(
hostname=percona_xtradb_cluster_three_node[i]['ip'],
port=percona_xtradb_cluster_three_node[0]['mysql_port'],
hostgroup_id=rw_map[i])
proxysql_instance.register_backend(backend)
blacklist = '{}:3306'.format(percona_xtradb_cluster_three_node[2]['ip'])
nodes = [
percona_xtradb_cluster_three_node[0]['ip'] + ':3306',
percona_xtradb_cluster_three_node[1]['ip'] + ':3306',
percona_xtradb_cluster_three_node[2]['ip'] + ':3306'
]
config = proxysql_tools_config_2(proxysql_instance, nodes, 'root', 'r00t',
hostgroup_writer, hostgroup_reader,
blacklist, 'monitor', 'monitor')
config_file = str(tmpdir.join('proxysql-tool.cfg'))
with open(config_file, 'w') as fh:
config.write(fh)
proxysql_tools.LOG.debug('proxysql-tools config: \n%s', config)
runner = CliRunner()
result = runner.invoke(main,
['--config', config_file, 'galera', 'register'])
assert result.exit_code == 0
result = runner.invoke(
main, ['--config', config_file, 'galera', 'user', 'create', 'foo'])
assert result.exit_code == 0
connection = pymysql.connect(host=proxysql_instance.host,
port=proxysql_instance.port,
user=proxysql_instance.user,
passwd=proxysql_instance.password,
connect_timeout=20,
cursorclass=DictCursor)
try:
with connection.cursor() as cursor:
cursor.execute(
"SELECT * FROM mysql_users WHERE username = '******'".
format(username='******'))
assert cursor.fetchall() != ()
finally:
connection.close()
result = runner.invoke(
main,
['--config', config_file, 'galera', 'user', 'set_password', 'foo'],
input='test\ntest\n')
assert result.exit_code == 0
connection = pymysql.connect(host=proxysql_instance.host,
port=proxysql_instance.port,
user=proxysql_instance.user,
passwd=proxysql_instance.password,
connect_timeout=20,
cursorclass=DictCursor)
try:
with connection.cursor() as cursor:
cursor.execute(
"SELECT * FROM mysql_users WHERE username = '******'".
format(username='******'))
row = cursor.fetchall()[0]
user = ProxySQLMySQLUser(
username=row['username'],
password=row['password'],
active=row['active'],
use_ssl=row['use_ssl'],
default_hostgroup=row['default_hostgroup'],
default_schema=row['default_schema'],
schema_locked=row['schema_locked'],
transaction_persistent=row['transaction_persistent'],
fast_forward=row['fast_forward'],
backend=row['backend'],
frontend=row['frontend'],
max_connections=row['max_connections'])
assert user.password
assert user.active
assert not user.use_ssl
assert user.default_hostgroup == 0
assert user.default_schema == 'information_schema'
assert not user.schema_locked
assert not user.transaction_persistent
assert not user.fast_forward
assert user.backend
assert user.frontend
assert user.max_connections == 10000
finally:
connection.close()
def test__galera_server_set_sync(percona_xtradb_cluster_three_node,
proxysql_instance, tmpdir):
wait_for_cluster_nodes_to_become_healthy(percona_xtradb_cluster_three_node)
hostgroup_writer = 10
hostgroup_reader = 11
rw_map = {0: hostgroup_writer, 1: hostgroup_reader, 2: hostgroup_reader}
for i in xrange(3):
backend = ProxySQLMySQLBackend(
hostname=percona_xtradb_cluster_three_node[i]['ip'],
port=percona_xtradb_cluster_three_node[0]['mysql_port'],
hostgroup_id=rw_map[i])
proxysql_instance.register_backend(backend)
blacklist = '{}:3306'.format(percona_xtradb_cluster_three_node[2]['ip'])
nodes = [
percona_xtradb_cluster_three_node[0]['ip'] + ':3306',
percona_xtradb_cluster_three_node[1]['ip'] + ':3306',
percona_xtradb_cluster_three_node[2]['ip'] + ':3306'
]
config = proxysql_tools_config_2(proxysql_instance, nodes, 'root', 'r00t',
hostgroup_writer, hostgroup_reader,
blacklist, 'monitor', 'monitor')
config_file = str(tmpdir.join('proxysql-tool.cfg'))
with open(config_file, 'w') as fh:
config.write(fh)
proxysql_tools.LOG.debug('proxysql-tools config: \n%s', config)
runner = CliRunner()
result = runner.invoke(main,
['--config', config_file, 'galera', 'register'])
assert result.exit_code == 0
result = runner.invoke(main, [
'--config', config_file, 'galera', 'server', 'set_sync',
percona_xtradb_cluster_three_node[1]['ip'], '3306'
])
assert result.exit_code == 0
result = runner.invoke(main,
['--config', config_file, 'galera', 'register'])
assert result.exit_code == 0
connection = pymysql.connect(host=proxysql_instance.host,
port=proxysql_instance.port,
user=proxysql_instance.user,
passwd=proxysql_instance.password,
connect_timeout=20,
cursorclass=DictCursor)
try:
with connection.cursor() as cursor:
cursor.execute(
'SELECT `hostgroup_id`, `hostname`, '
'`port`, `status`, `weight`, `compression`, '
'`max_connections`, `max_replication_lag`, '
'`use_ssl`, `max_latency_ms`, `comment`'
' FROM `mysql_servers`'
' WHERE hostgroup_id = %s'
' AND hostname = %s',
(hostgroup_reader, percona_xtradb_cluster_three_node[1]['ip']))
row = cursor.fetchall()[0]
assert row['status'] == BackendStatus.online
finally:
connection.close()
def test__galera_register_shutdowned_writer_is_deregistered(
percona_xtradb_cluster_three_node, proxysql_instance, tmpdir):
wait_for_cluster_nodes_to_become_healthy(percona_xtradb_cluster_three_node)
hostgroup_writer = 10
hostgroup_reader = 11
backend_unreg = ProxySQLMySQLBackend(
hostname=percona_xtradb_cluster_three_node[0]['ip'],
port=percona_xtradb_cluster_three_node[0]['mysql_port'],
hostgroup_id=hostgroup_writer)
proxysql_instance.register_backend(backend_unreg)
backend = ProxySQLMySQLBackend(
hostname=percona_xtradb_cluster_three_node[1]['ip'],
port=percona_xtradb_cluster_three_node[1]['mysql_port'],
hostgroup_id=hostgroup_reader)
proxysql_instance.register_backend(backend)
backend = ProxySQLMySQLBackend(
hostname=percona_xtradb_cluster_three_node[2]['ip'],
port=percona_xtradb_cluster_three_node[2]['mysql_port'],
hostgroup_id=hostgroup_reader)
proxysql_instance.register_backend(backend)
blacklist = '{}:3306'.format(percona_xtradb_cluster_three_node[2]['ip'])
nodes = [
percona_xtradb_cluster_three_node[0]['ip'] + ':3306',
percona_xtradb_cluster_three_node[1]['ip'] + ':3306',
percona_xtradb_cluster_three_node[2]['ip'] + ':3306'
]
shutdown_container(percona_xtradb_cluster_three_node[0]['id'])
config = proxysql_tools_config_2(proxysql_instance, nodes, 'root', 'r00t',
hostgroup_writer, hostgroup_reader,
blacklist, 'monitor', 'monitor')
config_file = str(tmpdir.join('proxysql-tool.cfg'))
with open(config_file, 'w') as fh:
config.write(fh)
proxysql_tools.LOG.debug('proxysql-tools config: \n%s', config)
runner = CliRunner()
result = runner.invoke(main,
['--config', config_file, 'galera', 'register'])
assert result.exit_code == 0
connection = pymysql.connect(host=proxysql_instance.host,
port=proxysql_instance.port,
user=proxysql_instance.user,
passwd=proxysql_instance.password,
connect_timeout=20,
cursorclass=DictCursor)
try:
with connection.cursor() as cursor:
cursor.execute(
'SELECT `hostgroup_id`, `hostname`, '
'`port`'
' FROM `mysql_servers`'
' WHERE hostgroup_id = %s '
' AND `hostname` = %s '
' AND `port` = %s',
(backend_unreg.hostgroup_id, backend_unreg.hostname,
backend_unreg.port))
assert cursor.fetchall() == ()
cursor.execute(
'SELECT `hostgroup_id`, `hostname`, '
'`port`'
' FROM `mysql_servers`'
' WHERE hostgroup_id = %s ', (backend_unreg.hostgroup_id, ))
assert cursor.fetchall() != ()
finally:
connection.close()