def __init__(self, host = "bpcweb7", db = "test", read_default_file = ""):
# , read_default_file=os.path.expanduser("~/.my.cnf"), port = 3306
self.conn = None
self.cursor = None
self.cursorD = None
self.rows = 0
self.new_id = None
self.lastrowid = None
port_env = 3306
try:
print("host = " + str(host) + ", db = " + str(db))
print("=" * 40)
read_default_file = os.path.expanduser("~/.my.cnf_node")
if is_local():
host = "127.0.0.1"
read_default_file = "~/.my.cnf_local"
self.conn = mysql.connect(host = host, db = db, read_default_file = read_default_file, port = port_env)
self.cursor = self.conn.cursor()
self.cursorD = self.conn.cursor(mysql.cursors.DictCursor)
except (AttributeError, mysql.OperationalError):
self.conn = mysql.connect(host = host, db = db, read_default_file = read_default_file, port = port_env)
self.cursor = self.conn.cursor()
except mysql.Error:
e = sys.exc_info()[1]
print("Error %d: %s" % (e.args[0], e.args[1]))
raise
except: # catch everything
print("Unexpected:")
print(sys.exc_info()[0])
raise # re-throw caught exception
def __init__(self, host="vampsdev", db="test", read_default_group="client"):
self.conn = None
self.cursor = None
self.rows = 0
self.new_id = None
self.lastrowid = None
try:
print("=" * 40)
print("host = " + str(host) + ", db = " + str(db) + ", group = " + str(read_default_group))
print("=" * 40)
self.conn = mysql.connect(host=host, db=db, read_default_group=read_default_group, read_default_file="~/.my.cnf")
self.cursor = self.conn.cursor()
except mysql.OperationalError:
e = sys.exc_info()[1]
print("Error %d: %s" % (e.args[0], e.args[1]))
print("=" * 40)
# print("No read_default_group, use host and db. (mysql.OperationalError)")
print("host = " + str(host) + ", db = " + str(db))
print("=" * 40)
self.conn = mysql.connect(host=host, db=db, read_default_file="~/.my.cnf")
self.cursor = self.conn.cursor()
except mysql.Error:
raise
except: # catch everything
print("Unexpected:") # handle unexpected exceptions)
raise # re-throw caught exception
def check_mysql_connection(host, user, password=''):
"""
A function used to check the ability to login to MySQL/Mariadb
:param host: ie. 'localhost' - :type String
:param user: mysql user ie. 'root' - :type String
:param password: mysql user's password - :type String
:return: True||False
"""
try:
mysql.connect(host=host, user=user, passwd=password)
return True
except mysql.Error:
return False
def __init__(self, host = "vampsdev", db = "test", read_default_group = "client", read_default_file = os.path.expanduser("~/.my.cnf")):
self.utils = Utils()
self.conn = None
self.cursor = None
self.rows = 0
self.new_id = None
self.lastrowid = None
self.rowcount = None
self.dict_cursor = None
if read_default_file == "":
if self.utils.is_local():
read_default_file = os.path.expanduser("~/.my.cnf_local")
# print("read_default_file = %s" % read_default_file)
try:
self.utils.print_both("=" * 40)
self.utils.print_both("host = " + str(host) + ", db = " + str(db) + ", read_default_group = " + str(read_default_group) + ", read_default_file = " + str(read_default_file))
self.utils.print_both("=" * 40)
# self.conn = mysql.connect(host = host, db = db, read_default_file = read_default_file, port = port)
self.conn = mysql.connect(host = host, db = db, read_default_group = read_default_group, read_default_file = read_default_file)
# print("host = %s, db = %s, read_default_file = %s" % (host, db, read_default_file))
self.cursor = self.conn.cursor()
self.dict_cursor = self.conn.cursor(mysql.cursors.DictCursor)
except mysql.Error as e:
self.utils.print_both("Error %d: %s" % (e.args[0], e.args[1]))
raise
except: # catch everything
self.utils.print_both("Unexpected:")
self.utils.print_both(sys.exc_info()[0])
raise # re-throw caught exception
def __init__(self, host = "vampsdev", db = "test", read_default_group = "client", read_default_file = os.path.expanduser("~/.my.cnf")):
self.utils = Utils()
self.conn = None
self.cursor = None
self.rows = 0
self.new_id = None
self.lastrowid = None
self.rowcount = None
self.dict_cursor = None
if read_default_file == "":
if self.utils.is_local():
read_default_file = os.path.expanduser("~/.my.cnf_local")
# print("read_default_file = %s" % read_default_file)
try:
self.utils.print_both("=" * 40)
self.utils.print_both("host = " + str(host) + ", db = " + str(db) + ", read_default_group = " + str(read_default_group) + ", read_default_file = " + str(read_default_file))
self.utils.print_both("=" * 40)
# self.conn = mysql.connect(host = host, db = db, read_default_file = read_default_file, port = port)
self.conn = mysql.connect(host = host, db = db, read_default_group = read_default_group, read_default_file = read_default_file)
# print("host = %s, db = %s, read_default_file = %s" % (host, db, read_default_file))
self.cursor = self.conn.cursor()
self.dict_cursor = self.conn.cursor(mysql.cursors.DictCursor)
except mysql.Error as e:
self.utils.print_both("Error %d: %s" % (e.args[0], e.args[1]))
raise
except: # catch everything
self.utils.print_both("Unexpected:")
self.utils.print_both(sys.exc_info()[0])
raise # re-throw caught exception
def renewMySQLconnection(self):
try:
if hasattr(self,'db') and self.db:
self.db.close()
del self.db
if mysql_api == MySQLdb:
self.db = mysql_api.connect(db=self.db_name,
host=self.host, user=self.user, passwd=self.passwd)
else: #if mysql_api == mysql.connector:
self.db = mysql_api.connect(database=self.db_name,
host=self.host, user=self.user, password=self.passwd)
self.setAutocommit()
except Exception,e:
self.error('Unable to create a mysql_api connection to '
'"%s"@%s.%s: %s'%(self.user,self.host,self.db_name,str(e)))
traceback.print_exc()
raise Exception,e
def get_new_connection(self, conn_params):
return Database.connect(**conn_params)
def mysql_secure_installation(login_password, new_password, user='******',login_host='localhost', hosts=['hostname'], change_root_password= True, remove_anonymous_user= True, disallow_root_login_remotely= False, remove_test_db= True, mysql_version_8=False):
"""
A function to perform the steps of mysql_secure_installation script
:param login_password: Root's password to login to MySQL
:param new_password: New desired Root password :type String
:param user: MySQL user - default: 'root' :type String
:param login_host: host to connect to - default: 'localhost' :type String
:param hosts: List of hosts for the provided user i.e ['localhost', '127.0.0.1', '::1'] :type List
:param change_root_password: default: True - :type Boolean
:param remove_anonymous_user: default: True - :type: Boolean
:param disallow_root_login_remotely: default: False - :type Boolean
:param remove_test_db: default: True - :type: Boolean
:param mysql_version_8: default: False - :type Boolean
:return:
"""
if isinstance(hosts, str):
hosts = hosts.split(',')
info = {'change_root_pwd': None, 'hosts_failed': [], 'hosts_success': [],'remove_anonymous_user': None, 'remove_test_db': None, 'disallow_root_remotely': None }
def remove_anon_user(cursor):
if remove_anonymous_user:
cursor.execute("select user, host from mysql.user where user='';")
anon_user = cursor.fetchall()
if len(anon_user) >= 1:
cursor.execute('use mysql;')
cursor.execute("DELETE FROM user WHERE user='';")
cursor.execute("update mysql.user set plugin=null where user='******';")
cursor.execute("select user, host from mysql.user where user='';")
check = cursor.fetchall()
if len(check) >= 1:
info['remove_anonymous_user'] = 1
else:
info['remove_anonymous_user'] = 0
else:
info['remove_anonymous_user'] = 0
def remove_testdb(cursor):
if remove_test_db:
cursor.execute("show databases;")
testdb = cursor.fetchall()
if 'test' in list(chain.from_iterable(testdb)): # if database "test" exists in the "db's list"
cursor.execute("drop database test;")
cursor.execute("show databases;") # Test if the "test" db deleted
check_test_db = cursor.fetchall()
if 'test' in list(chain.from_iterable(check_test_db)): # return 1 if the db still exists
info['remove_test_db'] = 1
else:
info['remove_test_db'] = 0
else: # means "test" db does not exist
info['remove_test_db'] = 0
def disallow_root_remotely(cursor):
if disallow_root_login_remotely:
cursor.execute("select user, host from mysql.user WHERE User='******' AND Host NOT IN ('localhost', '127.0.0.1', '::1');")
remote = cursor.fetchall()
if len(remote) >= 1:
cursor.execute("DELETE FROM mysql.user WHERE User='******' AND Host NOT IN ('localhost', '127.0.0.1', '::1');")
cursor.execute("flush privileges;")
cursor.execute("select user, host from mysql.user WHERE User='******' AND Host NOT IN ('localhost', '127.0.0.1', '::1');")
check_remote = cursor.fetchall()
if len(check_remote) >= 1: # test
info['disallow_root_remotely'] = 1
else:
info['disallow_root_remotely'] = 0
else:
info['disallow_root_remotely'] = 0
if check_mysql_connection(host=login_host, user=user, password=login_password):
try:
connection = mysql.connect(host=login_host, user=user, passwd=login_password, db='mysql')
cursor = connection.cursor()
remove_anon_user(cursor)
remove_testdb(cursor)
disallow_root_remotely(cursor)
if change_root_password:
pwd = {}
for host in hosts:
cursor.execute('use mysql;')
if mysql_version_8:
cursor.execute(
"alter user '{}'@'{}' IDENTIFIED WITH caching_sha2_password BY '{}';".format(user, host,
new_password))
else:
cursor.execute('update user set password=PASSWORD("{}") where User="******" AND Host="{}";'.format(new_password, user,
host))
cursor.execute('flush privileges;')
if mysql_version_8:
cursor.execute('select user, host, authentication_string from mysql.user where user="******";'.format(user))
else:
cursor.execute('select user, host, password from mysql.user where user="******";'.format(user))
data = cursor.fetchall()
for d in data:
if d[1] == host:
pwd['{}'.format(d[1])] = d[2]
out = set(hosts).symmetric_difference(set(pwd.keys()))
info['hosts_failed'] = list(out)
hosts_ = list(set(hosts) - set(list(out)))
for host in hosts_:
if pwd[host] == pwd[login_host]:
info['hosts_success'].append(host)
else:
info['hosts_failed'].append(login_host)
if len(info['hosts_success']) >= 1:
info['stdout'] = 'Password for user: {} @ Hosts: {} changed to the desired state'.format(user, info['hosts_success'])
if len(info['hosts_failed']) >= 1:
info['change_root_pwd'] = 1
# info['stderr'] = 'Could NOT change password for User: {} @ Hosts: {}'.format(user,info['hosts_failed'])
else:
info['change_root_pwd'] = 0
connection.close()
except mysql.Error as e:
info['change_root_pwd'] = 1
info['stderr'] = e
elif check_mysql_connection(host=login_host, user=user, password=new_password):
connection = mysql.connect(host=login_host, user=user, passwd=new_password, db='mysql')
cursor_ = connection.cursor()
remove_anon_user(cursor_)
remove_testdb(cursor_)
disallow_root_remotely(cursor_)
info['change_root_pwd'] = 0
info['stdout'] = 'Password of {}@{} Already meets the desired state'.format(user, login_host)
else:
info['change_root_pwd'] = 1
info['stdout'] = 'Neither the provided old passwd nor the new passwd are correct'
return info
def connect(host, db, read_default_file, port_env):
return mysql.connect(host = host, db = db, read_default_file = read_default_file, port = port_env)
elif args.dbhost == 'vampsdev' or args.dbhost == 'bpcweb7':
args.json_file_path = '/groups/vampsweb/vampsdev/nodejs/json'
args.NODE_DATABASE = 'vamps2'
hostname = 'bpcweb7'
else:
args.json_file_path = os.path.join(args.process_dir,'public','json')
args.NODE_DATABASE = args.db
hostname = 'localhost'
if args.infile_type == 'json':
if os.path.isdir(args.json_file_path):
print('json_file_path VALIDATED')
else:
print('json_file_path DID NOT VALIDATE:', args.json_file_path)
sys.exit('Exiting')
db = mysql.connect(db = args.NODE_DATABASE, host=hostname, read_default_file=os.path.expanduser("~/.my.cnf_node") )
cur = db.cursor()
cur.execute("SHOW databases")
dbs = []
db_str = ''
i = 0
if args.NODE_DATABASE:
NODE_DATABASE = args.NODE_DATABASE
else:
for row in cur.fetchall():
if row[0] != 'mysql' and row[0] != 'information_schema':
dbs.append(row[0])
db_str += str(i)+'-'+row[0]+'; '
print(str(i)+' - '+row[0]+'; ')
i += 1