22.16.119. salt.modules.mysql

Module to provide MySQL compatibility to salt.

depends:
  • MySQLdb Python module

Note

On CentOS 5 (and possibly RHEL 5) both MySQL-python and python26-mysqldb need to be installed.

configuration:

In order to connect to MySQL, certain configuration is required in /etc/salt/minion on the relevant minions. 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.

salt.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
salt.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'
salt.modules.mysql.db_exists(name, **connection_args)

Checks if a database exists on the MySQL server.

CLI Example:

salt '*' mysql.db_exists 'dbname'
salt.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
salt.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
salt.modules.mysql.db_remove(name, **connection_args)

Removes a databases from the MySQL server.

CLI Example:

salt '*' mysql.db_remove 'dbname'
salt.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
salt.modules.mysql.db_tables(name, **connection_args)

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

CLI Example:

salt '*' mysql.db_tables 'database'
salt.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
salt.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
salt.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
salt.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'
salt.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'
salt.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'
salt.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
salt.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] }}
salt.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'
salt.modules.mysql.showglobal(**connection_args)

Retrieves the show global variables from the minion.

Returns::
show global variables full dict

CLI Example:

salt '*' mysql.showglobal
salt.modules.mysql.showvariables(**connection_args)

Retrieves the show variables from the minion.

Returns::
show variables full dict

CLI Example:

salt '*' mysql.showvariables
salt.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
salt.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
salt.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'"
salt.modules.mysql.user_chpass(user, host='localhost', password=None, password_hash=None, allow_passwordless=False, unix_socket=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.

New 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
salt.modules.mysql.user_create(user, host='localhost', password=None, password_hash=None, allow_passwordless=False, unix_socket=False, **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.

New 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
salt.modules.mysql.user_exists(user, host='localhost', password=None, password_hash=None, passwordless=False, unix_socket=False, **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.

New 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.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'
salt.modules.mysql.user_info(user, host='localhost', **connection_args)

Get full info on a MySQL user

CLI Example:

salt '*' mysql.user_info root localhost
salt.modules.mysql.user_list(**connection_args)

Return a list of users on a MySQL server

CLI Example:

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

Delete MySQL user

CLI Example:

salt '*' mysql.user_remove frank localhost
salt.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

These docs are for Salt's development version: ac7174f.

Docs for previous releases are available on readthedocs.org.

Latest Salt release: 2014.1.10

Try the shiny new release candidate of Salt, v2014.7.0rc1! More info here.

Previous topic

22.16.118. salt.modules.munin

Next topic

22.16.120. salt.modules.nagios

SaltStack News

Upcoming SaltStack events, webinars and local meet ups and user groups.

SaltStack training