mysql_legacy

MySQL shell module.

exception spicerack.mysql_legacy.MysqlLegacyError[source]

Bases: SpicerackError

Custom exception class for errors of this module.

exception spicerack.mysql_legacy.MysqlLegacyReplagError[source]

Bases: MysqlLegacyError

Custom exception class for errors related to replag in this module.

class spicerack.mysql_legacy.Instance(host: spicerack.remote.RemoteHosts, *, name: str = '') → None[source]

Bases: object

Class to manage MariaDB single intances and multiinstances.

Initialize the instance.

Parameters:

host (spicerack.remote.RemoteHosts) -- the RemoteHosts instance that contains this MariaDB SingleInstance.
name (str, default: '') -- the name of the instance in a multiinstance context. Leave it empty for single instances.

check_warnings(cursor: pymysql.cursors.DictCursor) → None[source]

It will check if there is any warning in the cursor for the last query and ask the user what to do.

If any warning is found they will be logged to console and logfile and the user will be prompted what to do.

Parameters:: cursor (pymysql.cursors.DictCursor) -- the cursor object with which the last query was made.
Return type:: None

clean_data_dir(*, skip_confirmation: bool = False) → None[source]

Removes everything contained in the data directory.

Parameters:: skip_confirmation (bool, default: False) -- execute the operation without any user confirmation.
Return type:: None

cursor(**kwargs: Any) → collections.abc.Generator[source]

Context manager to get an open connection and cursor against the current instance.

Caution

DRY-RUN and read-only support is limited to DML sql operations.

Examples

Iterate directly the cursor

>>> with instance.cursor(database="mydb") as (connection, cursor):
>>>     count = cursor.execute("SELECT * FROM mytable WHERE a = %s and b = %s", ("value", 10))
>>>     for row in cursor:
>>>         # example row: {'a': 'value', 'b': 10, 'c': 1}
>>>
>>>     instance.check_warnings(cursor)

Get all the results

>>> with instance.cursor(database="mydb") as (connection, cursor):
>>>     count = cursor.execute("SELECT * FROM mytable WHERE a = %s and b = %s", ("value", 10))
>>>     results = cursor.fetchall()
>>>     instance.check_warnings(cursor)
>>>
>>> count
2
>>> results
[{'a': 'value', 'b': 10, 'c': 1}, {'a': 'value', 'b': 10, 'c': 2}]

Parameters:: **kwargs (typing.Any) -- arbitrary arguments that are passed to the spicerack.mysql_legacy.MysqlClient.connect method. See its documentation for the available arguments and their default values.
Yields:: A two-element tuple with the pymysql.connections.Connection as first element and the cursor object as second element. The cursor object is one of pymysql.cursors.
Raises:: pymysql.err.MySQLError -- or any more specific exception that inherits from this one on error.
Return type:: collections.abc.Generator

execute(query: str, query_parameters: None | tuple | list | dict = None, *, is_safe: bool = False, **kwargs: Any) → int[source]

Execute a query that returns no data without giving access to the connection or cursor objects.

Caution

DRY-RUN and read-only support is limited to DML sql operations when is_safe is set to True.

Note

If any warning is issued by the database they will be logged and the user prompted what to do.

Examples

>>> query = "INSERT INTO mytable VALUES (%s(a), %s(b))"
>>> params = {"a": "value", "b": 10}
>>> num_rows = instance.execute(query, params, database="mydb")

Parameters:

query (str) -- the query to execute, with eventual placeholders (%s or %s(name)).
query_parameters (typing.Union[None, tuple, list, dict], default: None) -- the query parameters to inject into the query, a tuple or list in case %s placeholders were used or a dict in case %s(name) placeholders were used. Leave the default value None if there are no placeholders in the query.
is_safe (bool, default: False) -- set to True if the query can be safely run also in DRY-RUN mode. By default all queries are considered unsafe. If False the query will not be run in DRY-RUN mode and the return value will be 0.
**kwargs (typing.Any) -- arbitrary arguments that are passed to the spicerack.mysql_legacy.MysqlClient.connect method. See its documentation for the available arguments and their default values.

Return type:

int

Returns:

the number of affected rows.

Raises:

spicerack.mysql_legacy.MysqlLegacyError -- if the query returned more than one row.
pymysql.err.MySQLError -- on query errors.

fetch_one_row(query: str, query_parameters: None | tuple | list | dict = None, **kwargs: Any) → dict | None[source]

Execute the given query and returns one row. It sets the connection as read only.

Caution

DRY-RUN and read-only support is limited to DML sql operations. By default all queries are considered read-only due to the nature of the method (retrieve one row).

Note

If any warning is issued by the database they will be logged and the user prompted what to do.

Examples

>>> query = "SELECT * FROM mytable WHERE a = %s(a) and b = %s(b)"
>>> params = {"a": "value", "b": 10}
>>> row = instance.fetch_one_row(query, params, database="mydb")
>>> row
{'a': 'value', 'b': 10, 'c': 1}

Parameters:

query (str) -- the query to execute, with eventual placeholders (%s or %s(name)).
query_parameters (typing.Union[None, tuple, list, dict], default: None) -- the query parameters to inject into the query, a tuple or list in case %s placeholders were used or a dict in case %s(name) placeholders were used. Leave the default value None if there are no placeholders in the query.
**kwargs (typing.Any) -- arbitrary arguments that are passed to the spicerack.mysql_legacy.MysqlClient.connect method. See its documentation for the available arguments and their default values.

Return type:

typing.Optional[dict]

Returns:

the fetched row or None if the query returned no rows.

Raises:

spicerack.mysql_legacy.MysqlLegacyError -- if the query returned more than one row.
pymysql.err.MySQLError -- on query errors.

get_replication_info() → spicerack.mysql_legacy.ReplicationInfo[source]

Get the replication information suitable to set a new node's replication.

Return type:: spicerack.mysql_legacy.ReplicationInfo
Returns:: The replication information object, useful to setup a new instance's replication to resume from the same position.

is_running() → bool[source]

Check if the systemd service for the instance is active and running.

Return type:: bool
Returns:: True if the service is active and running, False otherwise.

post_clone_reset_with_slave_stopped() → None[source]

Prepares the MySQL instance for the first pooling operation.

Return type:: None

prep_src_for_cloning() → spicerack.mysql_legacy.ReplicationInfo[source]

Helper that prepares source instance to be cloned.

Return type:: spicerack.mysql_legacy.ReplicationInfo
Returns:: The replication information object, useful to setup a new instance's replication to resume from the same position.

replication_lag() → decimal.Decimal[source]

Retrieves the current replication lag.

Return type:: decimal.Decimal
Returns:: The replication lag in seconds.
Raises:: spicerack.mysql_legacy.MysqlLegacyError -- if no lag information is present or unable to parse the it.

restart_mysql() → collections.abc.Iterator[tuple[ClusterShell.NodeSet.NodeSet, ClusterShell.MsgTree.MsgTreeElem]][source]

Restarts mariadb service.

Return type:: collections.abc.Iterator[tuple[ClusterShell.NodeSet.NodeSet, ClusterShell.MsgTree.MsgTreeElem]]
Returns:: The results of the remote restart command.

resume_replication() → None[source]

Resumes replication on the source MySQL instance.

Return type:: None

run_query(query: str, database: str = '', **kwargs: Any) → Any[source]

Execute the query via Remote.

Parameters:

query (str) -- the mysql query to be executed. Double quotes must be already escaped.
database (str, default: '') -- the optional database to use for the query execution.
**kwargs (typing.Any) -- any additional argument is passed to spicerack.remote.RemoteHosts.run_sync(). By default the print_progress_bars and print_output arguments are set to False.

Return type:

typing.Any

Returns:

The result of the remote command execution.

Raises:

spicerack.remote.RemoteExecutionError -- if the query execution via SSH returns a non-zero exit code.

run_vertical_query(query: str, database: str = '', **kwargs: Any) → list[dict[str, str]][source]

Run a query with vertical output (terminating it with \G) and parse its results.

The \G at the end of the query is automatically added. Each returned row is converted to a dictionary with keys that are the column names and values that are the column values.

Warning

The parsing of the output of queries from the CLI, even with vertical format (\G), is a very brittle operation that could fail or have misleading data, for example if any of the values queried are multi-lines. This could potentially happen also with a SHOW SLAVE STATUS query if the replication is broken and the last error contains a newline.

:param According to spicerack.mysql_legacy.Instance.run_query().:

Return type:: list[dict[str, str]]
Returns:: the parsed query as a list of dictionaries, one per returned row.

set_master_use_gtid(setting: spicerack.mysql_legacy.MasterUseGTID) → None[source]

Runs MASTER_USE_GTID with the given value.

Return type:: None

set_replication_parameters(*, replication_info: spicerack.mysql_legacy.ReplicationInfo, user: str, password: str) → None[source]

Sets the replication parameters for the MySQL instance.

Return type:: None

show_master_status() → dict[source]

Returns the output of show master status formatted as a dict.

Return type:: dict
Returns:: the current master status for the instance.

show_slave_status() → dict[source]

Returns the output of show slave status formatted as a dict.

Return type:: dict
Returns:: the current slave status for the instance.

start_mysql() → collections.abc.Iterator[tuple[ClusterShell.NodeSet.NodeSet, ClusterShell.MsgTree.MsgTreeElem]][source]

Starts mariadb service.

Return type:: collections.abc.Iterator[tuple[ClusterShell.NodeSet.NodeSet, ClusterShell.MsgTree.MsgTreeElem]]
Returns:: The results of the remote start command.

start_slave() → None[source]

Starts mariadb replication and sleeps for 1 second afterwards.

Return type:: None

status_mysql() → collections.abc.Iterator[tuple[ClusterShell.NodeSet.NodeSet, ClusterShell.MsgTree.MsgTreeElem]][source]

Stops mariadb service.

Return type:: collections.abc.Iterator[tuple[ClusterShell.NodeSet.NodeSet, ClusterShell.MsgTree.MsgTreeElem]]
Returns:: The results of the remote status command. It does not raise on exit codes different from zero.

stop_mysql() → collections.abc.Iterator[tuple[ClusterShell.NodeSet.NodeSet, ClusterShell.MsgTree.MsgTreeElem]][source]

Stops mariadb service.

Return type:: collections.abc.Iterator[tuple[ClusterShell.NodeSet.NodeSet, ClusterShell.MsgTree.MsgTreeElem]]
Returns:: The results of the remote status command.

stop_slave() → None[source]

Stops mariadb replication.

Return type:: None

upgrade() → collections.abc.Iterator[tuple[ClusterShell.NodeSet.NodeSet, ClusterShell.MsgTree.MsgTreeElem]][source]

Runs the relevant mysql_upgrade command to upgrade the instance content.

Return type:: collections.abc.Iterator[tuple[ClusterShell.NodeSet.NodeSet, ClusterShell.MsgTree.MsgTreeElem]]
Returns:: The results of the remote upgrade command.

wait_for_replication(threshold: float | decimal.Decimal = Decimal('1.0')) → None[source]

Waits for replication to catch up.

Parameters:: threshold (typing.Union[float, decimal.Decimal], default: Decimal('1.0')) -- the replication lag threshold in seconds under which the replication is considered in sync.
Raises:: spicerack.mysql_legacy.MysqlLegacyReplagError -- if the replication lag is still too high after all the retries.
Return type:: None

property data_dir: str

Get the data directory of this instance.

Returns:: the data directory path for mariadb for this specific instance.

property primary: str

Retrieves the replication source of this cluster.

Raises:: spicerack.mysql_legacy.MysqlLegacyError -- if unable to find the master host of the current instance.

property socket: str

Getter for the socket path of the instance.

Returns:: the instance specific socket path to use.

class spicerack.mysql_legacy.MasterUseGTID(value)[source]

Bases: Enum

Describe the possible values for the MASTER_USE_GTID option.

CURRENT_POS = 'current_pos': Replicate in GTID mode and use gtid_current_pos as the position to start downloading transactions.

NO = 'no': Don't replicate in GTID mode.

SLAVE_POS = 'slave_pos': Replicate in GTID mode and use gtid_slave_pos as the position to start downloading transactions.

class spicerack.mysql_legacy.MysqlClient(*, dry_run: bool = True) → None[source]

Bases: object

Connect to Mysql instances with a native Mysql client (pymysql).

Caution

This class only has DRY-RUN support for DML sql operations.

Initialize the instance.

Parameters:: dry_run (bool, default: True) -- whether this is a DRY-RUN.

connect(*, read_only: bool = False, **kwargs: Any) → collections.abc.Generator[source]

Context-manager for a mysql connection to a remote host.

Caution

Read-only support is limited to DML sql operations.

Important

By default autocommit is off and the commit of changes is the caller's responsibility.
The caller should also take care of rolling back transactions on error as appropriate.

Parameters:

read_only (bool, default: False) -- True if this connection should use read-only transactions. Note: This parameter has no effect if DRY-RUN is set, it will be forced to True.
**kwargs (typing.Any) --
Options passed directly to pymysql.connections.Connection. If not set some settings will be set to default values:
- read_default_file: ~/.my.cnf. Set to None to disable it.
- read_default_group: If not specified, it will be set to client or labsdbclient based on the hostname.
- ssl: uses the puppet CA. Set to {} to disable.
- cursorclass: pymysql.cursors.DictCursor

Yields:

pymysql.connections.Connection -- a context-managed mysql connection.

Raises:

pymysql.err.MySQLError -- if unable to create the connection or set the connection to read only.

Return type:

collections.abc.Generator

class spicerack.mysql_legacy.MysqlLegacy(remote: spicerack.remote.Remote, dry_run: bool = True) → None[source]

Bases: object

Class to manage MySQL servers.

Initialize the instance.

Parameters:

remote (spicerack.remote.Remote) -- the Remote instance.
dry_run (bool, default: True) -- whether this is a DRY-RUN.

check_core_masters_heartbeats(datacenter: str, heartbeat_dc: str, heartbeats: dict[str, datetime.datetime]) → None[source]

Check the current heartbeat values in the core DB masters in DC are in sync with the provided heartbeats.

Parameters:

datacenter (str) -- the name of the datacenter from where to get the heartbeat values.
heartbeat_dc (str) -- the name of the datacenter for which to filter the heartbeat query.
heartbeats (dict[str, datetime.datetime]) -- a dictionary with the section name str as keys and heartbeat datetime.datetime for each core section as values.

Raises:

spicerack.mysql_legacy.MysqlLegacyError -- on failure to gather the heartbeat or convert it into a datetime.

Return type:

None

check_core_masters_in_sync(dc_from: str, dc_to: str) → None[source]

Check that all core masters in dc_to are in sync with the core masters in dc_from.

Parameters:

dc_from (str) -- the name of the datacenter from where to get the master positions.
dc_to (str) -- the name of the datacenter where to check that they are in sync.

Raises:

spicerack.remote.RemoteExecutionError -- on failure.

Return type:

None

get_core_dbs(*, datacenter: str | None = None, section: str | None = None, replication_role: str | None = None, excludes: tuple[str, ...] = ()) → spicerack.mysql_legacy.MysqlLegacyRemoteHosts[source]

Get an instance to operated on the core databases matching the parameters.

Parameters:

datacenter (typing.Optional[str], default: None) -- the name of the datacenter to filter for, accepted values are those specified in spicerack.constants.CORE_DATACENTERS.
replication_role (typing.Optional[str], default: None) -- the repication role to filter for, accepted values are those specified in spicerack.mysql_legacy.REPLICATION_ROLES.
section (typing.Optional[str], default: None) -- a specific section to filter for, accepted values are those specified in spicerack.mysql_legacy.CORE_SECTIONS.
excludes (tuple[str, ...], default: ()) -- sections to exclude from getting.

Raises:

spicerack.mysql_legacy.MysqlLegacyError -- on invalid data or unexpected matching hosts.

Return type:

spicerack.mysql_legacy.MysqlLegacyRemoteHosts

get_core_masters_heartbeats(datacenter: str, heartbeat_dc: str) → dict[str, datetime.datetime][source]

Get the current heartbeat values from core DB masters in DC for a given heartbeat DC.

Parameters:

datacenter (str) -- the name of the datacenter from where to get the heartbeat values.
heartbeat_dc (str) -- the name of the datacenter for which to filter the heartbeat query.

Return type:

dict[str, datetime.datetime]

Returns:

A dictionary with the section name str as keys and their heartbeat datetime.datetime as values. For example:

{'s1': datetime.datetime(2018, 1, 2, 11, 22, 33, 123456)}

Raises:

spicerack.mysql_legacy.MysqlLegacyError -- on failure to gather the heartbeat or convert it into a datetime.

get_dbs(query: str) → spicerack.mysql_legacy.MysqlLegacyRemoteHosts[source]

Get a MysqlLegacyRemoteHosts instance for the matching hosts.

Parameters:: query (str) -- the Remote query to use to fetch the DB hosts.
Return type:: spicerack.mysql_legacy.MysqlLegacyRemoteHosts

set_core_masters_readonly(datacenter: str) → None[source]

Set the core masters in read-only.

Parameters:

datacenter (str) -- the name of the datacenter to filter for.

Raises:

spicerack.remote.RemoteExecutionError -- on Remote failures.
spicerack.mysql_legacy.MysqlLegacyError -- on failing to verify the modified value.

Return type:

None

set_core_masters_readwrite(datacenter: str) → None[source]

Set the core masters in read-write.

Parameters:

datacenter (str) -- the name of the datacenter to filter for.

Raises:

spicerack.remote.RemoteExecutionError -- on Remote failures.
spicerack.mysql_legacy.MysqlLegacyError -- on failing to verify the modified value.

Return type:

None

verify_core_masters_readonly(datacenter: str, is_read_only: bool) → None[source]

Verify that the core masters are in read-only or read-write mode.

Parameters:

datacenter (str) -- the name of the datacenter to filter for.
is_read_only (bool) -- whether the read-only mode should be set or not.

Raises:

spicerack.mysql_legacy.MysqlLegacyError -- on failure.

Return type:

None

heartbeat_query: str = "SELECT ts FROM heartbeat.heartbeat WHERE datacenter = '{dc}' and shard = '{section}' ORDER BY ts DESC LIMIT 1": Query pattern to check the heartbeat for a given datacenter and section.

class spicerack.mysql_legacy.MysqlLegacyRemoteHosts(remote_hosts: spicerack.remote.RemoteHosts) → None[source]

Bases: RemoteHostsAdapter

Custom RemoteHosts class for executing MySQL queries.

Initialize the instance.

Parameters:: remote_hosts (spicerack.remote.RemoteHosts) -- the instance to act on the remote hosts.

list_hosts_instances(*, group: bool = False) → list[spicerack.mysql_legacy.Instance][source]

List MariaDB instances on the host.

Parameters:

group (bool, default: False) -- not yet implemented feature to allow parallelization.

Raises:

spicerack.remote.RemoteExecutionError -- if the Cumin execution returns a non-zero exit code.
NotImplementedError -- if the replag is not fully caught on.

Return type:

list[spicerack.mysql_legacy.Instance]

run_query(query: str, database: str = '', **kwargs: Any) → collections.abc.Iterator[tuple[ClusterShell.NodeSet.NodeSet, ClusterShell.MsgTree.MsgTreeElem]][source]

Execute the query via Remote.

Parameters:

query (str) -- the mysql query to be executed. Double quotes must be already escaped.
database (str, default: '') -- an optional MySQL database to connect to before executing the query.
**kwargs (typing.Any) -- any additional argument is passed to spicerack.remote.RemoteHosts.run_sync(). By default the print_progress_bars and print_output arguments are set to False.

Raises:

spicerack.remote.RemoteExecutionError -- if the Cumin execution returns a non-zero exit code.

Return type:

collections.abc.Iterator[tuple[ClusterShell.NodeSet.NodeSet, ClusterShell.MsgTree.MsgTreeElem]]

class spicerack.mysql_legacy.ReplicationInfo(primary: str, binlog: str, position: int, port: int) → None[source]

Bases: object

Represent the minimum replication information needed to restore a replication from a given point.

Parameters:

primary (str) -- the FQDN of the primary host from where to replicate from.
binlog (str) -- the binlog file to replicate from.
position (int) -- the binlog position to replicate from.
port (int) -- the port of the master from where to replicate from.

spicerack.mysql_legacy.CORE_SECTIONS: tuple[str, ...] = ('s6', 's5', 's2', 's7', 's3', 's8', 's4', 's1', 'x1', 'es6', 'es7'): Valid MySQL RW core sections (external storage RO, parser cache, x2 and misc sections are not included here). They are ordered from less impactful if anything goes wrong to most impactful.

spicerack.mysql_legacy.REPLICATION_ROLES: tuple[str, ...] = ('master', 'slave', 'standalone'): Valid replication roles.