salt.modules.mysql

Module to provide MySQL compatibility to salt.

depends:

• MySQLdb Python module

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'

You can also use a defaults file:

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

salt.modules.mysql.db_check(name, table=None)

Repairs the full database or just a given table

CLI Example:

salt '*' mysql.db_check dbname

salt.modules.mysql.db_create(name)

Adds a databases to the MySQL server.

CLI Example:

salt '*' mysql.db_create 'dbname'

salt.modules.mysql.db_exists(name)

Checks if a database exists on the MySQL server.

CLI Example:

salt '*' mysql.db_exists 'dbname'

salt.modules.mysql.db_list()

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)

Optimizes the full database or just a given table

CLI Example:

salt '*' mysql.db_optimize dbname

salt.modules.mysql.db_remove(name)

Removes a databases from the MySQL server.

CLI Example:

salt '*' mysql.db_remove 'dbname'

salt.modules.mysql.db_repair(name, table=None)

Repairs the full database or just a given table

CLI Example:

salt '*' mysql.db_repair dbname

salt.modules.mysql.db_tables(name)

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

CLI Example:

salt '*' mysql.db_tables 'database'

salt.modules.mysql.free_slave()

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()

Retrieves the master status from the mimion.

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()

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)

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)

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)

Removes a grant from the MySQL server.

CLI Example:

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

salt.modules.mysql.processlist()

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)

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

CLI Examples:

salt '*' mysql.query mydb "UPDATE mytable set myfield=1 limit 1"
returns: {'query time': {'human': '39.0ms', 'raw': '0.03899'},
'rows affected': 1L}

salt '*' mysql.query mydb "SELECT id,name,cash from users limit 3"
returns: {'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}

salt '*' mysql.query mydb "INSERT into users values (null,'user 4', 5)"
returns: {'query time': {'human': '25.6ms', 'raw': '0.02563'},
   'rows affected': 1L}

salt '*' mysql.query mydb "DELETE from users where id = 4 limit 1"
returns: {'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.slave_lag()

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.

CLI Example:

salt '*' mysql.slave_lag

salt.modules.mysql.status()

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

CLI Example:

salt '*' mysql.status

salt.modules.mysql.user_chpass(user, host='localhost', password=None, password_hash=None)

Change password for MySQL user

CLI Examples:

salt '*' mysql.user_chpass frank localhost newpassword

salt '*' mysql.user_chpass frank localhost password_hash='hash'

salt.modules.mysql.user_create(user, host='localhost', password=None, password_hash=None)

Creates a MySQL user.

CLI Examples:

salt '*' mysql.user_create 'username' 'hostname' 'password'

salt '*' mysql.user_create 'username' 'hostname' password_hash='hash'

salt.modules.mysql.user_exists(user, host='localhost')

Checks if a user exists on the MySQL server.

CLI Example:

salt '*' mysql.user_exists 'username' 'hostname'

salt.modules.mysql.user_grants(user, host='localhost')

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')

Get full info on a MySQL user

CLI Example:

salt '*' mysql.user_info root localhost

salt.modules.mysql.user_list()

Return a list of users on a MySQL server

CLI Example:

salt '*' mysql.user_list

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

Delete MySQL user

CLI Example:

salt '*' mysql.user_remove frank localhost

salt.modules.mysql.version()

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

CLI Example:

salt '*' mysql.version