mysql

Module to provide MySQL compatibility to salt.

depends:

• Python module: MySQLdb, mysqlclient, or PyMYSQL

configuration:

In order to connect to MySQL, certain configuration is required in either the relevant minion config (/etc/salt/minion), or pillar.

Some sample configs might look like:

mysql.host: 'localhost'
mysql.port: 3306
mysql.user: 'root'
mysql.pass: ''
mysql.db: 'mysql'
mysql.unix_socket: '/tmp/mysql.sock'
mysql.charset: 'utf8'

You can also use a defaults file:

mysql.default_file: '/etc/mysql/debian.cnf'

Changed in version 2014.1.0: ‘charset’ connection argument added. This is a MySQL charset, not a python one.

Changed in version 0.16.2: Connection arguments from the minion config file can be overridden on the CLI by using the arguments defined here. Additionally, it is now possible to setup a user with no password.

saltext.mysql.modules.mysql.__all_privileges__ = ['ALTER', 'ALTER ROUTINE', 'BACKUP_ADMIN', 'BINLOG_ADMIN', 'CONNECTION_ADMIN', 'CREATE', 'CREATE ROLE', 'CREATE ROUTINE', 'CREATE TABLESPACE', 'CREATE TEMPORARY TABLES', 'CREATE USER', 'CREATE VIEW', 'DELETE', 'DROP', 'DROP ROLE', 'ENCRYPTION_KEY_ADMIN', 'EVENT', 'EXECUTE', 'FILE', 'GROUP_REPLICATION_ADMIN', 'INDEX', 'INSERT', 'LOCK TABLES', 'PERSIST_RO_VARIABLES_ADMIN', 'PROCESS', 'REFERENCES', 'RELOAD', 'REPLICATION CLIENT', 'REPLICATION SLAVE', 'REPLICATION_SLAVE_ADMIN', 'RESOURCE_GROUP_ADMIN', 'RESOURCE_GROUP_USER', 'ROLE_ADMIN', 'SELECT', 'SET_USER_ID', 'SHOW DATABASES', 'SHOW VIEW', 'SHUTDOWN', 'SUPER', 'SYSTEM_VARIABLES_ADMIN', 'TRIGGER', 'UPDATE', 'XA_RECOVER_ADMIN']

saltext.mysql.modules.mysql.__virtual__()

Confirm that a python mysql client is installed.

saltext.mysql.modules.mysql.quote_identifier(identifier, for_grants=False)

Return an identifier name (column, table, database, etc) escaped for MySQL

This means surrounded by “`” character and escaping this character inside. It also means doubling the ‘%’ character for MySQLdb internal usage.

Parameters:

• identifier – the table, column or database identifier

• for_grants – is False by default, when using database names on grant queries you should set it to True to also escape “_” and “%” characters as requested by MySQL. Note that theses characters should only be escaped when requesting grants on the database level (my_%db.*) but not for table level grants (my_%db.`foo`)

CLI Example:

salt '*' mysql.quote_identifier 'foo`bar'

saltext.mysql.modules.mysql.query(database, query, **connection_args)

Run an arbitrary SQL query and return the results or the number of affected rows.

CLI Example:

salt '*' mysql.query mydb "UPDATE mytable set myfield=1 limit 1"

Return data:

{"query time": {"human": "39.0ms", "raw": "0.03899"}, "rows affected": 1l}

CLI Example:

salt '*' mysql.query mydb "SELECT id,name,cash from users limit 3"

Return data:

{
    "columns": ("id", "name", "cash"),
    "query time": {"human": "1.0ms", "raw": "0.001"},
    "results": (
        (1l, "User 1", Decimal("110.000000")),
        (2l, "User 2", Decimal("215.636756")),
        (3l, "User 3", Decimal("0.040000")),
    ),
    "rows returned": 3l,
}

CLI Example:

salt '*' mysql.query mydb 'INSERT into users values (null,"user 4", 5)'

Return data:

{"query time": {"human": "25.6ms", "raw": "0.02563"}, "rows affected": 1l}

CLI Example:

salt '*' mysql.query mydb 'DELETE from users where id = 4 limit 1'

Return data:

{"query time": {"human": "39.0ms", "raw": "0.03899"}, "rows affected": 1l}

Jinja Example: Run a query on mydb and use row 0, column 0’s data.

{{ salt['mysql.query']('mydb', 'SELECT info from mytable limit 1')['results'][0][0] }}

saltext.mysql.modules.mysql.file_query(database, file_name, **connection_args)

Run an arbitrary SQL query from the specified file and return the the number of affected rows.

Added in version 2017.7.0.

database

database to run script inside

file_name

File name of the script. This can be on the minion, or a file that is reachable by the fileserver

CLI Example:

salt '*' mysql.file_query mydb file_name=/tmp/sqlfile.sql
salt '*' mysql.file_query mydb file_name=salt://sqlfile.sql

Return data:

{"query time": {"human": "39.0ms", "raw": "0.03899"}, "rows affected": 1l}

saltext.mysql.modules.mysql.status(**connection_args)

Return the status of a MySQL server using the output from the SHOW STATUS query.

CLI Example:

salt '*' mysql.status

saltext.mysql.modules.mysql.version(**connection_args)

Return the version of a MySQL server using the output from the SELECT VERSION() query.

CLI Example:

salt '*' mysql.version

saltext.mysql.modules.mysql.slave_lag(**connection_args)

Return the number of seconds that a slave SQL server is lagging behind the master, if the host is not a slave it will return -1. If the server is configured to be a slave for replication but slave IO is not running then -2 will be returned. If there was an error connecting to the database or checking the slave status, -3 will be returned.

CLI Example:

salt '*' mysql.slave_lag

saltext.mysql.modules.mysql.free_slave(**connection_args)

Frees a slave from its master. This is a WIP, do not use.

CLI Example:

salt '*' mysql.free_slave

saltext.mysql.modules.mysql.db_list(**connection_args)

Return a list of databases of a MySQL server using the output from the SHOW DATABASES query.

CLI Example:

salt '*' mysql.db_list

saltext.mysql.modules.mysql.alter_db(name, character_set=None, collate=None, **connection_args)

Modify database using ALTER DATABASE %(dbname)s CHARACTER SET %(charset)s COLLATE %(collation)s; query.

CLI Example:

salt '*' mysql.alter_db testdb charset='latin1'

saltext.mysql.modules.mysql.db_get(name, **connection_args)

Return a list of databases of a MySQL server using the output from the SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='dbname'; query.

CLI Example:

salt '*' mysql.db_get test

saltext.mysql.modules.mysql.db_tables(name, **connection_args)

Shows the tables in the given MySQL database (if exists)

CLI Example:

salt '*' mysql.db_tables 'database'

saltext.mysql.modules.mysql.db_exists(name, **connection_args)

Checks if a database exists on the MySQL server.

CLI Example:

salt '*' mysql.db_exists 'dbname'

saltext.mysql.modules.mysql.db_create(name, character_set=None, collate=None, **connection_args)

Adds a databases to the MySQL server.

name

The name of the database to manage

character_set

The character set, if left empty the MySQL default will be used

collate

The collation, if left empty the MySQL default will be used

CLI Example:

salt '*' mysql.db_create 'dbname'
salt '*' mysql.db_create 'dbname' 'utf8' 'utf8_general_ci'

saltext.mysql.modules.mysql.db_remove(name, **connection_args)

Removes a databases from the MySQL server.

CLI Example:

salt '*' mysql.db_remove 'dbname'

saltext.mysql.modules.mysql.user_list(**connection_args)

Return a list of users on a MySQL server

CLI Example:

salt '*' mysql.user_list

saltext.mysql.modules.mysql.user_exists(user, host='localhost', password=None, password_hash=None, passwordless=False, unix_socket=False, password_column=None, **connection_args)

Checks if a user exists on the MySQL server. A login can be checked to see if passwordless login is permitted by omitting password and password_hash, and using passwordless=True.

Added in version 0.16.2: The passwordless option was added.

CLI Example:

salt '*' mysql.user_exists 'username' 'hostname' 'password'
salt '*' mysql.user_exists 'username' 'hostname' password_hash='hash'
salt '*' mysql.user_exists 'username' passwordless=True
salt '*' mysql.user_exists 'username' password_column='authentication_string'

saltext.mysql.modules.mysql.user_info(user, host='localhost', **connection_args)

Get full info on a MySQL user

CLI Example:

salt '*' mysql.user_info root localhost

saltext.mysql.modules.mysql.user_create(user, host='localhost', password=None, password_hash=None, allow_passwordless=False, unix_socket=False, password_column=None, auth_plugin='mysql_native_password', **connection_args)

Creates a MySQL user

host

Host for which this user/password combo applies

password

The password to use for the new user. Will take precedence over the password_hash option if both are specified.

password_hash

The password in hashed form. Be sure to quote the password because YAML doesn’t like the *. A password hash can be obtained from the mysql command-line client like so:

mysql> SELECT PASSWORD('mypass');
+-------------------------------------------+
| PASSWORD('mypass')                        |
+-------------------------------------------+
| *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
+-------------------------------------------+
1 row in set (0.00 sec)

allow_passwordless

If True, then password and password_hash can be omitted (or set to None) to permit a passwordless login.

unix_socket

If True and allow_passwordless is True then will be used unix_socket auth plugin.

password_column

The password column to use in the user table.

auth_plugin

The authentication plugin to use, default is to use the mysql_native_password plugin.

Added in version 0.16.2: The allow_passwordless option was added.

CLI Examples:

salt '*' mysql.user_create 'username' 'hostname' 'password'
salt '*' mysql.user_create 'username' 'hostname' password_hash='hash'
salt '*' mysql.user_create 'username' 'hostname' allow_passwordless=True

saltext.mysql.modules.mysql.user_chpass(user, host='localhost', password=None, password_hash=None, allow_passwordless=False, unix_socket=None, password_column=None, **connection_args)

Change password for a MySQL user

host

Host for which this user/password combo applies

password

The password to set for the new user. Will take precedence over the password_hash option if both are specified.

password_hash

The password in hashed form. Be sure to quote the password because YAML doesn’t like the *. A password hash can be obtained from the mysql command-line client like so:

mysql> SELECT PASSWORD('mypass');
+-------------------------------------------+
| PASSWORD('mypass')                        |
+-------------------------------------------+
| *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
+-------------------------------------------+
1 row in set (0.00 sec)

allow_passwordless

If True, then password and password_hash can be omitted (or set to None) to permit a passwordless login.

Added in version 0.16.2: The allow_passwordless option was added.

CLI Examples:

salt '*' mysql.user_chpass frank localhost newpassword
salt '*' mysql.user_chpass frank localhost password_hash='hash'
salt '*' mysql.user_chpass frank localhost allow_passwordless=True

saltext.mysql.modules.mysql.user_remove(user, host='localhost', **connection_args)

Delete MySQL user

CLI Example:

salt '*' mysql.user_remove frank localhost

saltext.mysql.modules.mysql.tokenize_grant(grant)

External wrapper function :param grant: :return: dict

CLI Example:

salt '*' mysql.tokenize_grant             "GRANT SELECT, INSERT ON testdb.* TO 'testuser'@'localhost'"

saltext.mysql.modules.mysql.db_check(name, table=None, **connection_args)

Repairs the full database or just a given table

CLI Example:

salt '*' mysql.db_check dbname
salt '*' mysql.db_check dbname dbtable

saltext.mysql.modules.mysql.db_repair(name, table=None, **connection_args)

Repairs the full database or just a given table

CLI Example:

salt '*' mysql.db_repair dbname

saltext.mysql.modules.mysql.db_optimize(name, table=None, **connection_args)

Optimizes the full database or just a given table

CLI Example:

salt '*' mysql.db_optimize dbname

saltext.mysql.modules.mysql.user_grants(user, host='localhost', **connection_args)

Shows the grants for the given MySQL user (if it exists)

CLI Example:

salt '*' mysql.user_grants 'frank' 'localhost'

saltext.mysql.modules.mysql.grant_exists(grant, database, user, host='localhost', grant_option=False, escape=True, **connection_args)

Checks to see if a grant exists in the database

escape

Escape the MySQL wildcard characters _ and % in the database name of database-level grants (db.*). Defaults to True. Make sure to use the same value the grant was added with.

MySQL usually interprets these characters as wildcards, meaning unescaped grants apply to all databases matching the pattern, while escaped ones only apply to the literal database name.

Note

On MySQL servers with partial_revokes enabled, database names in grants are always interpreted literally and escaped grants fail to match the intended database. Set this to False there.

CLI Example:

salt '*' mysql.grant_exists              'SELECT,INSERT,UPDATE,...' 'database.*' 'frank' 'localhost'

saltext.mysql.modules.mysql.grant_add(grant, database, user, host='localhost', grant_option=False, escape=True, ssl_option=False, **connection_args)

Adds a grant to the MySQL server.

For database, make sure you specify database.table or database.*

escape

Escape the MySQL wildcard characters _ and % in the database name of database-level grants (db.*). Defaults to True.

MySQL usually interprets these characters as wildcards, meaning unescaped grants apply to all databases matching the pattern, while escaped ones only apply to the literal database name.

Note

On MySQL servers with partial_revokes enabled, database names in grants are always interpreted literally and escaped grants fail to match the intended database. Set this to False there.

CLI Example:

salt '*' mysql.grant_add             'SELECT,INSERT,UPDATE,...' 'database.*' 'frank' 'localhost'

saltext.mysql.modules.mysql.grant_revoke(grant, database, user, host='localhost', grant_option=False, escape=True, **connection_args)

Removes a grant from the MySQL server.

escape

Escape the MySQL wildcard characters _ and % in the database name of database-level grants (db.*). Defaults to True. Make sure to use the same value the grant was added with, otherwise the stored grant will not match and the revocation fails.

MySQL usually interprets these characters as wildcards, meaning unescaped grants apply to all databases matching the pattern, while escaped ones only apply to the literal database name.

Note

On MySQL servers with partial_revokes enabled, database names in grants are always interpreted literally and escaped grants fail to match the intended database. Set this to False there.

CLI Example:

salt '*' mysql.grant_revoke             'SELECT,INSERT,UPDATE' 'database.*' 'frank' 'localhost'

saltext.mysql.modules.mysql.processlist(**connection_args)

Retrieves the processlist from the MySQL server via “SHOW FULL PROCESSLIST”.

Returns: a list of dicts, with each dict representing a process:

{
    "Command": "Query",
    "Host": "localhost",
    "Id": 39,
    "Info": "SHOW FULL PROCESSLIST",
    "Rows_examined": 0,
    "Rows_read": 1,
    "Rows_sent": 0,
    "State": None,
    "Time": 0,
    "User": "root",
    "db": "mysql",
}

CLI Example:

salt '*' mysql.processlist

saltext.mysql.modules.mysql.get_master_status(**connection_args)

Retrieves the master status from the minion.

Returns:

{'host.domain.com': {'Binlog_Do_DB': '',
                 'Binlog_Ignore_DB': '',
                 'File': 'mysql-bin.000021',
                 'Position': 107}}

CLI Example:

salt '*' mysql.get_master_status

saltext.mysql.modules.mysql.get_slave_status(**connection_args)

Retrieves the slave status from the minion.

Returns:

{'host.domain.com': {'Connect_Retry': 60,
               'Exec_Master_Log_Pos': 107,
               'Last_Errno': 0,
               'Last_Error': '',
               'Last_IO_Errno': 0,
               'Last_IO_Error': '',
               'Last_SQL_Errno': 0,
               'Last_SQL_Error': '',
               'Master_Host': 'comet.scion-eng.com',
               'Master_Log_File': 'mysql-bin.000021',
               'Master_Port': 3306,
               'Master_SSL_Allowed': 'No',
               'Master_SSL_CA_File': '',
               'Master_SSL_CA_Path': '',
               'Master_SSL_Cert': '',
               'Master_SSL_Cipher': '',
               'Master_SSL_Key': '',
               'Master_SSL_Verify_Server_Cert': 'No',
               'Master_Server_Id': 1,
               'Master_User': 'replu',
               'Read_Master_Log_Pos': 107,
               'Relay_Log_File': 'klo-relay-bin.000071',
               'Relay_Log_Pos': 253,
               'Relay_Log_Space': 553,
               'Relay_Master_Log_File': 'mysql-bin.000021',
               'Replicate_Do_DB': '',
               'Replicate_Do_Table': '',
               'Replicate_Ignore_DB': '',
               'Replicate_Ignore_Server_Ids': '',
               'Replicate_Ignore_Table': '',
               'Replicate_Wild_Do_Table': '',
               'Replicate_Wild_Ignore_Table': '',
               'Seconds_Behind_Master': 0,
               'Skip_Counter': 0,
               'Slave_IO_Running': 'Yes',
               'Slave_IO_State': 'Waiting for master to send event',
               'Slave_SQL_Running': 'Yes',
               'Until_Condition': 'None',
               'Until_Log_File': '',
               'Until_Log_Pos': 0}}

CLI Example:

salt '*' mysql.get_slave_status

saltext.mysql.modules.mysql.showvariables(**connection_args)

Retrieves the show variables from the minion.

Returns::

show variables full dict

CLI Example:

salt '*' mysql.showvariables

saltext.mysql.modules.mysql.showglobal(**connection_args)

Retrieves the show global variables from the minion.

Returns::

show global variables full dict

CLI Example:

salt '*' mysql.showglobal

saltext.mysql.modules.mysql.verify_login(user, password=None, **connection_args)

Attempt to login using the provided credentials. If successful, return true. Otherwise, return False.

CLI Example:

salt '*' mysql.verify_login root password

saltext.mysql.modules.mysql.plugins_list(**connection_args)

Return a list of plugins and their status from the SHOW PLUGINS query.

CLI Example:

salt '*' mysql.plugins_list

saltext.mysql.modules.mysql.plugin_add(name, soname=None, **connection_args)

Add a plugina.

CLI Example:

salt '*' mysql.plugin_add auth_socket

saltext.mysql.modules.mysql.plugin_remove(name, **connection_args)

Remove a plugin.

CLI Example:

salt '*' mysql.plugin_remove auth_socket

saltext.mysql.modules.mysql.plugin_status(name, **connection_args)

Return the status of a plugin.

CLI Example:

salt '*' mysql.plugin_status auth_socket