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.
-
Modify database using
ALTER DATABASE %(dbname)s CHARACTER SET %(charset)s COLLATE %(collation)s;
query.CLI Example:
salt '*' mysql.alter_db testdb charset='latin1'
salt.modules.mysql.alter_db(name, character_set=None, collate=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_check(name, table=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_create(name, character_set=None, collate=None, **connection_args)
-
Checks if a database exists on the MySQL server.
CLI Example:
salt '*' mysql.db_exists 'dbname'
salt.modules.mysql.db_exists(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
salt.modules.mysql.db_get(name, **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_list(**connection_args)
-
Optimizes the full database or just a given table
CLI Example:
salt '*' mysql.db_optimize dbname
salt.modules.mysql.db_optimize(name, table=None, **connection_args)
-
Removes a databases from the MySQL server.
CLI Example:
salt '*' mysql.db_remove 'dbname'
salt.modules.mysql.db_remove(name, **connection_args)
-
Repairs the full database or just a given table
CLI Example:
salt '*' mysql.db_repair dbname
salt.modules.mysql.db_repair(name, table=None, **connection_args)
-
Shows the tables in the given MySQL database (if exists)
CLI Example:
salt '*' mysql.db_tables 'database'
salt.modules.mysql.db_tables(name, **connection_args)
-
Run an arbitrary SQL query from the specified file and return the the number of affected rows.
New 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}
salt.modules.mysql.file_query(database, file_name, **connection_args)
-
Frees a slave from its master. This is a WIP, do not use.
CLI Example:
salt '*' mysql.free_slave
salt.modules.mysql.free_slave(**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_master_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.get_slave_status(**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_add(grant, database, user, host='localhost', grant_option=False, escape=True, ssl_option=False, **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_exists(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.grant_revoke(grant, database, user, host='localhost', grant_option=False, escape=True, **connection_args)
-
Add a plugina.
CLI Example:
salt '*' mysql.plugin_add auth_socket
salt.modules.mysql.plugin_add(name, soname=None, **connection_args)
-
Remove a plugin.
CLI Example:
salt '*' mysql.plugin_remove auth_socket
salt.modules.mysql.plugin_remove(name, **connection_args)
-
Return the status of a plugin.
CLI Example:
salt '*' mysql.plugin_status auth_socket
salt.modules.mysql.plugin_status(name, **connection_args)
-
Return a list of plugins and their status from the
SHOW PLUGINS
query.CLI Example:
salt '*' mysql.plugins_list
salt.modules.mysql.plugins_list(**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.processlist(**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.query(database, query, **connection_args)
-
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.quote_identifier(identifier, for_grants=False)
-
Retrieves the show global variables from the minion.
- Returns::
-
show global variables full dict
CLI Example:
salt '*' mysql.showglobal
salt.modules.mysql.showglobal(**connection_args)
-
Retrieves the show variables from the minion.
- Returns::
-
show variables full dict
CLI Example:
salt '*' mysql.showvariables
salt.modules.mysql.showvariables(**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.slave_lag(**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.status(**connection_args)
-
External wrapper function :param grant: :return: dict
CLI Example:
salt '*' mysql.tokenize_grant "GRANT SELECT, INSERT ON testdb.* TO 'testuser'@'localhost'"
salt.modules.mysql.tokenize_grant(grant)
-
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
, thenpassword
andpassword_hash
can be omitted (or set toNone
) 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_chpass(user, host='localhost', password=None, password_hash=None, allow_passwordless=False, unix_socket=None, password_column=None, **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
, thenpassword
andpassword_hash
can be omitted (or set toNone
) to permit a passwordless login. - unix_socket
-
If
True
and allow_passwordless isTrue
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.
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_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)
-
Checks if a user exists on the MySQL server. A login can be checked to see if passwordless login is permitted by omitting
password
andpassword_hash
, and usingpasswordless=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 '*' mysql.user_exists 'username' password_column='authentication_string'
salt.modules.mysql.user_exists(user, host='localhost', password=None, password_hash=None, passwordless=False, unix_socket=False, password_column=None, **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_grants(user, host='localhost', **connection_args)
-
Get full info on a MySQL user
CLI Example:
salt '*' mysql.user_info root localhost
salt.modules.mysql.user_info(user, host='localhost', **connection_args)
-
Return a list of users on a MySQL server
CLI Example:
salt '*' mysql.user_list
salt.modules.mysql.user_list(**connection_args)
-
Delete MySQL user
CLI Example:
salt '*' mysql.user_remove frank localhost
salt.modules.mysql.user_remove(user, host='localhost', **connection_args)
-
Attempt to login using the provided credentials. If successful, return true. Otherwise, return False.
CLI Example:
salt '*' mysql.verify_login root password
salt.modules.mysql.verify_login(user, password=None, **connection_args)
-
Return the version of a MySQL server using the output from the
SELECT VERSION()
query.CLI Example:
salt '*' mysql.version
salt.modules.mysql.version(**connection_args)
© 2021 SaltStack.
Licensed under the Apache License, Version 2.0.
https://docs.saltproject.io/en/latest/ref/modules/all/salt.modules.mysql.html