133 salt/modules/mysql.py
View file
@@ -29,12 +29,11 @@
log = logging.getLogger(__name__)
__opts__ = {}


def __virtual__():
'''
Only load this module if the mysql config is set
'''
if any(k.startswith('mysql.') for k in list(__opts__)):
if any(k.startswith('mysql.') for k in __opts__.keys()):
if has_mysqldb:
return 'mysql'
return False
@@ -43,42 +42,38 @@ def __virtual__():
def __check_table(name, table):
db = connect()
cur = db.cursor(MySQLdb.cursors.DictCursor)
query = "CHECK TABLE `%s`.`%s`" % (name, table, )
query = "CHECK TABLE `%s`.`%s`" % (name,table,)
log.debug("Doing query: {0}".format(query,))
cur.execute(query)
results = cur.fetchall()
log.debug(results)
return results


def __repair_table(name, table):
db = connect()
cur = db.cursor(MySQLdb.cursors.DictCursor)
query = "REPAIR TABLE `%s`.`%s`" % (name, table, )
query = "REPAIR TABLE `%s`.`%s`" % (name,table,)
log.debug("Doing query: {0}".format(query,))
cur.execute(query)
results = cur.fetchall()
log.debug(results)
return results


def __optimize_table(name, table):
db = connect()
cur = db.cursor(MySQLdb.cursors.DictCursor)
query = "OPTIMIZE TABLE `%s`.`%s`" % (name, table, )
query = "OPTIMIZE TABLE `%s`.`%s`" % (name,table,)
log.debug("Doing query: {0}".format(query,))
cur.execute(query)
results = cur.fetchall()
log.debug(results)
return results


def connect(**kwargs):
'''
wrap authentication credentials here
'''
connargs = dict()

def _connarg(name, key=None):
'''
Add key to connargs, only if name exists in our
@@ -116,7 +111,7 @@ def status():
db = connect()
cur = db.cursor()
cur.execute('SHOW STATUS')
for i in range(cur.rowcount):
for i in xrange(cur.rowcount):
row = cur.fetchone()
ret[row[0]] = row[1]
return ret
@@ -137,7 +132,6 @@ def version():
row = cur.fetchone()
return row


def slave_lag():
'''
Return the number of seconds that a slave SQL server is lagging behind the
@@ -204,8 +198,6 @@ def free_slave():
'''
Database related actions
'''


def db_list():
'''
Return a list of databases of a MySQL server using the output
@@ -226,7 +218,6 @@ def db_list():
log.debug(ret)
return ret


def db_tables(name):
'''
Shows the tables in the given MySQL database (if exists)
@@ -252,7 +243,6 @@ def db_tables(name):
log.debug(ret)
return ret


def db_exists(name):
'''
Checks if a database exists on the MySQL server.
@@ -265,7 +255,7 @@ def db_exists(name):
cur = db.cursor()
query = "SHOW DATABASES LIKE '%s'" % name
log.debug("Doing query: {0}".format(query,))
cur.execute(query)
cur.execute( query )
result_set = cur.fetchall()
return cur.rowcount == 1

@@ -288,12 +278,11 @@ def db_create(name):
cur = db.cursor()
query = "CREATE DATABASE `%s`;" % name
log.debug("Query: {0}".format(query,))
if cur.execute(query):
if cur.execute( query ):
log.info("DB '{0}' created".format(name,))
return True
return False


def db_remove(name):
'''
Removes a databases from the MySQL server.
@@ -307,7 +296,7 @@ def db_remove(name):
log.info("DB '{0}' does not exist".format(name,))
return False

if name in ('mysql', 'information_scheme'):
if name in ('mysql','information_scheme'):
log.info("DB '{0}' may not be removed".format(name,))
return False

@@ -316,7 +305,7 @@ def db_remove(name):
cur = db.cursor()
query = "DROP DATABASE `%s`;" % name
log.debug("Doing query: {0}".format(query,))
cur.execute(query)
cur.execute( query )

if not db_exists(name):
log.info("Database '{0}' has been removed".format(name,))
@@ -328,8 +317,6 @@ def db_remove(name):
'''
User related actions
'''


def user_list():
'''
Return a list of users on a MySQL server
@@ -345,7 +332,6 @@ def user_list():
log.debug(results)
return results


def user_exists(user,
host='localhost'):
'''
@@ -359,10 +345,9 @@ def user_exists(user,
cur = db.cursor()
query = "SELECT User,Host FROM mysql.user WHERE User = '%s' AND Host = '%s'" % (user, host,)
log.debug("Doing query: {0}".format(query,))
cur.execute(query)
cur.execute( query )
return cur.rowcount == 1


def user_info(user,
host='localhost'):
'''
@@ -373,15 +358,14 @@ def user_info(user,
salt '*' mysql.user_info root localhost
'''
db = connect()
cur = db.cursor(MySQLdb.cursors.DictCursor)
cur = db.cursor (MySQLdb.cursors.DictCursor)
query = "SELECT * FROM mysql.user WHERE User = '%s' AND Host = '%s'" % (user, host,)
log.debug("Query: {0}".format(query,))
cur.execute(query)
result = cur.fetchone()
log.debug(result)
log.debug( result )
return result


def user_create(user,
host='localhost',
password=None,
@@ -395,29 +379,28 @@ def user_create(user,
salt '*' mysql.user_create 'username' 'hostname' password_hash='hash'
'''
if user_exists(user, host):
log.info("User '{0}'@'{1}' already exists".format(user, host, ))
return False
if user_exists(user,host):
log.info("User '{0}'@'{1}' already exists".format(user,host,))
return False

db = connect()
cur = db.cursor()
cur = db.cursor ()
query = "CREATE USER '%s'@'%s'" % (user, host,)
if password is not None:
query = query + " IDENTIFIED BY '%s'" % password
elif password_hash is not None:
query = query + " IDENTIFIED BY PASSWORD '%s'" % password_hash

log.debug("Query: {0}".format(query,))
cur.execute(query)
cur.execute( query )

if user_exists(user, host):
log.info("User '{0}'@'{1}' has been created".format(user, host, ))
if user_exists(user,host):
log.info("User '{0}'@'{1}' has been created".format(user,host,))
return True

log.info("User '{0}'@'{1}' is not created".format(user, host, ))
log.info("User '{0}'@'{1}' is not created".format(user,host,))
return False


def user_chpass(user,
host='localhost',
password=None,
@@ -440,17 +423,16 @@ def user_chpass(user,
password_sql = "\"%s\"" % password_hash

db = connect()
cur = db.cursor()
query = "UPDATE mysql.user SET password=%s WHERE User='%s' AND Host = '%s';" % (password_sql, user, host, )
cur = db.cursor ()
query = "UPDATE mysql.user SET password=%s WHERE User='%s' AND Host = '%s';" % (password_sql,user,host,)
log.debug("Query: {0}".format(query,))
if cur.execute(query):
log.info("Password for user '{0}'@'{1}' has been changed".format(user, host, ))
if cur.execute( query ):
log.info("Password for user '{0}'@'{1}' has been changed".format(user,host,))
return True

log.info("Password for user '{0}'@'{1}' is not changed".format(user, host, ))
log.info("Password for user '{0}'@'{1}' is not changed".format(user,host,))
return False


def user_remove(user,
host='localhost'):
'''
@@ -461,23 +443,20 @@ def user_remove(user,
salt '*' mysql.user_remove frank localhost
'''
db = connect()
cur = db.cursor()
cur = db.cursor ()
query = "DROP USER '%s'@'%s'" % (user, host,)
log.debug("Query: {0}".format(query,))
cur.execute(query)
result = cur.fetchone()
if not user_exists(user, host):
log.info("User '{0}'@'{1}' has been removed".format(user, host, ))
if not user_exists(user,host):
log.info("User '{0}'@'{1}' has been removed".format(user,host,))
return True

log.info("User '{0}'@'{1}' has NOT been removed".format(user, host, ))
log.info("User '{0}'@'{1}' has NOT been removed".format(user,host,))
return False

'''
Maintenance
'''


def db_check(name,
table=None):
'''
@@ -492,14 +471,13 @@ def db_check(name,
# we need to check all tables
tables = db_tables(name)
for table in tables:
log.info("Checking table '%s' in db '%s..'".format(name, table, ))
ret.append(__check_table(name, table))
log.info("Checking table '%s' in db '%s..'".format(name,table,))
ret.append( __check_table(name,table) )
else:
log.info("Checking table '%s' in db '%s'..".format(name, table, ))
ret = __check_table(name, table)
log.info("Checking table '%s' in db '%s'..".format(name,table,))
ret = __check_table(name,table)
return ret


def db_repair(name,
table=None):
'''
@@ -514,14 +492,13 @@ def db_repair(name,
# we need to repair all tables
tables = db_tables(name)
for table in tables:
log.info("Repairing table '%s' in db '%s..'".format(name, table, ))
ret.append(__repair_table(name, table))
log.info("Repairing table '%s' in db '%s..'".format(name,table,))
ret.append( __repair_table(name,table) )
else:
log.info("Repairing table '%s' in db '%s'..".format(name, table, ))
ret = __repair_table(name, table)
log.info("Repairing table '%s' in db '%s'..".format(name,table,))
ret = __repair_table(name,table)
return ret


def db_optimize(name,
table=None):
'''
@@ -536,18 +513,16 @@ def db_optimize(name,
# we need to optimize all tables
tables = db_tables(name)
for table in tables:
log.info("Optimizing table '%s' in db '%s..'".format(name, table, ))
ret.append(__optimize_table(name, table))
log.info("Optimizing table '%s' in db '%s..'".format(name,table,))
ret.append( __optimize_table(name,table) )
else:
log.info("Optimizing table '%s' in db '%s'..".format(name, table, ))
ret = __optimize_table(name, table)
log.info("Optimizing table '%s' in db '%s'..".format(name,table,))
ret = __optimize_table(name,table)
return ret

'''
Grants
'''


def __grant_generate(grant,
database,
user,
@@ -572,7 +547,6 @@ def __grant_generate(grant,
log.debug("Query generated: {0}".format(query,))
return query


def user_grants(user,
host='localhost'):
'''
@@ -582,14 +556,14 @@ def user_grants(user,
salt '*' mysql.user_grants 'frank' 'localhost'
'''
if not user_exists(user, host):
log.info("User '{0}'@'{1}' does not exist".format(user, host, ))
return False
if not user_exists(user,host):
log.info("User '{0}'@'{1}' does not exist".format(user,host,))
return False

ret = []
db = connect()
cur = db.cursor()
query = "SHOW GRANTS FOR '%s'@'%s'" % (user, host, )
query = "SHOW GRANTS FOR '%s'@'%s'" % (user,host,)
log.debug("Doing query: {0}".format(query,))

cur.execute(query)
@@ -599,7 +573,6 @@ def user_grants(user,
log.debug(ret)
return ret


def grant_exists(grant,
database,
user,
@@ -617,7 +590,6 @@ def grant_exists(grant,
log.debug("Grant does not exist, or is perhaps not ordered properly?")
return False


def grant_add(grant,
database,
user,
@@ -637,11 +609,13 @@ def grant_add(grant,

query = __grant_generate(grant, database, user, host, grant_option, escape)
log.debug("Query: {0}".format(query,))
if cur.execute(query):
log.info("Grant '{0}' created")
cur.execute( query )
if grant_exists(grant,database,user,host,grant_option,escape):
log.info("Grant '{0}' on '{1}' for user '{2}' has been added".format(grant,database,user,))
return True
return False

log.info("Grant '{0}' on '{1}' for user '{2}' has NOT been added".format(grant,database,user,))
return False

def grant_revoke(grant,
database,
@@ -663,7 +637,10 @@ def grant_revoke(grant,
grant += ", GRANT OPTION"
query = "REVOKE %s ON %s FROM '%s'@'%s';" % (grant, database, user, host,)
log.debug("Query: {0}".format(query,))
if cur.execute(query):
log.info("Grant '{0}' revoked")
cur.execute( query )
if not grant_exists(grant,database,user,host,grant_option,escape):
log.info("Grant '{0}' on '{1}' for user '{2}' has been revoked".format(grant,database,user,))
return True

log.info("Grant '{0}' on '{1}' for user '{2}' has NOT been revoked".format(grant,database,user,))
return False