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.

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(*, read_only: bool = False, **kwargs: Any) → collections.abc.Generator[source]

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

Examples

>>> 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()
>>>
>>> count
2
>>> results
[{'a': 'value', 'b': 10, 'c': 1}, {'a': 'value', 'b': 10, 'c': 2}]

Parameters:

read_only (bool, default: False) -- True if this connection should use read-only transactions. Has no effect if in DRY-RUN mode, it forces it to True.
**kwargs (typing.Any) -- See spicerack.mysql.Mysql.connect 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

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() → float[source]

Retrieves the current replication lag.

Return type:: float
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.

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 = 1.0) → None[source]

Waits for replication to catch up.

Parameters:: threshold (float, default: 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.

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