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

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

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.

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