def __init__(self, backup_mode='full', incr_basedir=None):
self._backup_mode = backup_mode
self.backup_record = {}
threading.Thread.__init__(self)
self.zkOpers = Requests_ZkOpers()
self.dba_opers = DBAOpers()
if self._backup_mode == "full":
self.backupOpers = FullBackupOpers()
else:
self.backupOpers = IncrementBackupOpers(incr_basedir)
class Inner_DB_Check_CurConns(APIHandler):
dba_opers = DBAOpers()
def get(self):
zkOper = self.retrieve_zkOper()
if not is_monitoring(get_localhost_ip(), zkOper):
self.finish("true")
return
conn = self.dba_opers.get_mysql_connection()
if conn is None:
self.finish("false")
return
try:
current_connections_rows = self.dba_opers.show_processlist(conn)
max_connections_rows = self.dba_opers.show_variables(
conn, 'max_connections')
finally:
conn.close()
current_connections_count = len(current_connections_rows)
max_connections_rows_dict = dict(max_connections_rows)
max_connections = max_connections_rows_dict.get("max_connections")
if current_connections_count / int(max_connections) < 0.7:
self.finish("true")
return
self.finish("false")
class StatMysqlInfo(APIHandler):
dba_opers = DBAOpers()
@asynchronous
@engine
def post(self):
params = self.get_all_arguments()
result = yield self.dba_opers.retrieve_node_info_stat(params)
self.finish(result)
class DBCreate(APIHandler):
dba_opers = DBAOpers()
conf_opers = ConfigFileOpers()
def post(self):
dbName = self.get_argument("dbName", None)
userName = self.get_argument("userName", None)
ip_address = self.get_argument("ip_address", '%')
max_queries_per_hour = self.get_argument("max_queries_per_hour", 0)
max_updates_per_hour = self.get_argument("max_updates_per_hour", 0)
max_connections_per_hour = self.get_argument(
"max_connections_per_hour", 0)
max_user_connections = self.get_argument("max_user_connections", 200)
userPassword = get_random_password()
conn = self.dba_opers.get_mysql_connection()
try:
self.dba_opers.craete_database(conn, dbName)
self.dba_opers.create_user(conn, userName, userPassword,
ip_address)
self.dba_opers.grant_manager_privileges(
conn, userName, userPassword, dbName, ip_address,
max_queries_per_hour, max_updates_per_hour,
max_connections_per_hour, max_user_connections)
self.dba_opers.flush_privileges(conn)
finally:
conn.close()
# check if exist cluster
dbProps = {'db_name': dbName}
zkOper = self.retrieve_zkOper()
clusterUUID = zkOper.getClusterUUID()
zkOper.write_db_info(clusterUUID, dbName, dbProps)
userProps = {
'role': 'manager',
'max_queries_per_hour': max_queries_per_hour,
'max_updates_per_hour': max_updates_per_hour,
'max_connections_per_hour': max_connections_per_hour,
'max_user_connections': max_user_connections
}
zkOper.write_user_info(clusterUUID, dbName, userName, ip_address,
userProps)
result = {}
result.setdefault("message", "database create successful")
result.setdefault("manager_user_name", userName)
result.setdefault("manager_user_password", userPassword)
self.finish(result)
class Check_Cluster_Available(Check_Status_Base):
dba_opers = DBAOpers()
confOpers = ConfigFileOpers()
def check(self, data_node_info_list):
success_nodes, value, _password = [], {}, ''
value = self.confOpers.getValue(
options.mysql_cnf_file_name)["wsrep_sst_auth"]
_password = value.split(":")[1][:-1]
for data_node_ip in data_node_info_list:
try:
conn = self.dba_opers.get_mysql_connection(data_node_ip,
user="******",
passwd=_password)
if conn is not None:
success_nodes.append(data_node_ip)
finally:
if conn is not None:
conn.close()
message = "no avaliable data node"
if len(success_nodes) >= 1:
message = 'ok'
total_count = len(data_node_info_list)
success_count = len(success_nodes)
failed_count = total_count - success_count
alarm_result = self.retrieve_alarm_level(total_count, success_count,
failed_count)
cluster_available_dict = {}
cluster_available_dict.setdefault("message", message)
cluster_available_dict.setdefault("alarm", alarm_result)
return cluster_available_dict
def retrieve_alarm_level(self, total_count, success_count, failed_count):
if success_count == 0:
return options.alarm_serious
return options.alarm_nothing
class Inner_DB_Check_User_CurConns(APIHandler):
dba_opers = DBAOpers()
def get(self):
zkOper = self.retrieve_zkOper()
if not is_monitoring(get_localhost_ip(), zkOper):
self.finish("true")
return
conn = self.dba_opers.get_mysql_connection()
if conn is None:
self.finish("false")
return
'''
@todo: dbs[0] need to refactor
'''
clusterUUID = zkOper.getClusterUUID()
db_name = None
dbs = zkOper.retrieve_db_list()
if [] != dbs:
db_name = dbs[0]
user_prop_dict = {}
if None is not db_name:
user_prop_dict = zkOper.retrieve_db_user_prop(clusterUUID, db_name)
try:
for user_prop in user_prop_dict:
max_user_connections_rows = self.dba_opers.show_user_max_conn(
conn, user_prop, user_prop_dict[user_prop])
current_user_connections_rows = self.dba_opers.show_user_current_conn(
conn, user_prop, user_prop_dict[user_prop])
if int(current_user_connections_rows
) > int(max_user_connections_rows) * 0.8:
self.finish("false")
return
finally:
conn.close()
self.finish("true")
class DBDelete(APIHandler):
dba_opers = DBAOpers()
conf_opers = ConfigFileOpers()
def delete(self, dbName):
if not dbName:
raise HTTPAPIErrorException(
"when remove the db, no have database name,\
please provide database name you want to removed!",
status_code=417)
zkOper = self.retrieve_zkOper()
clusterUUID = zkOper.getClusterUUID()
user_ipAddress_map = zkOper.retrieve_db_user_prop(clusterUUID, dbName)
conn = self.dba_opers.get_mysql_connection()
try:
if user_ipAddress_map is not None:
for (user_name, ip_address) in user_ipAddress_map.items():
self.dba_opers.delete_user(conn, user_name, ip_address)
self.dba_opers.drop_database(conn, dbName)
finally:
conn.close()
user_name_list = ''
if user_ipAddress_map is not None:
for (user_name, ip_address) in user_ipAddress_map.items():
zkOper.remove_db_user(clusterUUID, dbName, user_name,
ip_address)
user_name_list += user_name + ","
zkOper.remove_db(clusterUUID, dbName)
result = {}
result.setdefault("message", "database remove successful!")
result.setdefault("removed_db_name", dbName)
result.setdefault("removed_user_with_db_name", user_name_list)
self.finish(result)
class Inner_DB_Check_WsrepStatus(APIHandler):
dba_opers = DBAOpers()
def get(self):
zkOper = self.retrieve_zkOper()
if not is_monitoring(get_localhost_ip(), zkOper):
self.finish("true")
return
try:
check_result = self.dba_opers.retrieve_wsrep_status()
logging.info("check_wsrepstatus : %s" % (check_result))
except:
error_message = "connection break down"
raise HTTPAPIErrorException(error_message, status_code=417)
if not check_result:
self.finish("false")
return
self.finish("true")
def post(self, db_name):
body = json.loads(self.request.body, strict=False, encoding='utf-8')
tables = body.get("tables")
# 判断数据库是否存在
db_dir = DIR_MCLUSTER_MYSQ + '/{db_name}'.format(db_name=db_name)
if not os.path.exists(db_dir):
self.set_status(400)
self.finish({"errmsg": "db is not exist", "errcode": 40031})
return
result = DBAOpers.get_tables_rows(db_name, tables)
# 判断是否有不存在的表
for tb, row in result.items():
# 行数为0不能说明表不存在, 以-1作为行数表示不存在的表
if row < 0:
self.set_status(400)
result = {
"errmsg": "table {0} is not exist".format(tb),
"errcode": 40401
}
break
self.finish(result)
class BackupWorkers(threading.Thread):
def __init__(self, backup_mode='full', incr_basedir=None):
self._backup_mode = backup_mode
self.backup_record = {}
threading.Thread.__init__(self)
self.zkOpers = Requests_ZkOpers()
self.dba_opers = DBAOpers()
if self._backup_mode == "full":
self.backupOpers = FullBackupOpers()
else:
self.backupOpers = IncrementBackupOpers(incr_basedir)
def run(self):
isLock, lock = self.zkOpers.lock_backup_action()
if not isLock:
logging.info('zk is not lock')
return
try:
_password = retrieve_monitor_password()
conn = self.dba_opers.get_mysql_connection(user="******",
passwd=_password)
if None == conn:
raise UserVisiableException("Can\'t connect to mysql server")
db_status = self.dba_opers.show_status(conn)
if 'Synced' != db_status[-14][1]:
self.backup_record[
'error: '] = 'Mcluster is not start %s' % datetime.datetime.now(
).strftime(TIME_FORMAT)
self.backupOpers._write_info_to_local(
self.backupOpers.path, self.backupOpers.file_name,
self.backup_record)
self.zkOpers.write_backup_backup_info(self.backup_record)
return
if '0' == self.__run_comm(CHECK_DMP_DATA_CMD):
self.backup_record[
'error: '] = 'No have /data partition %s' % datetime.datetime.now(
).strftime(TIME_FORMAT)
self.backupOpers._write_info_to_local(
self.backupOpers.path, self.backupOpers.file_name,
self.backup_record)
self.zkOpers.write_backup_backup_info(self.backup_record)
return
self.backupOpers.create_backup_directory()
self.backupOpers.remove_expired_backup_file()
self.backupOpers.backup_action(self.zkOpers)
self.backupOpers.trans_backup_file(self.zkOpers)
record = {
"recently_backup_ip: ": str(get_localhost_ip()),
'time: ': datetime.datetime.now().strftime(TIME_FORMAT),
'backup_type: ': self._backup_mode
}
self.zkOpers.write_backup_backup_info(record)
except Exception, e:
record = {
"error: ": 'backup is wrong, please check it!',
'time:': datetime.datetime.now().strftime(TIME_FORMAT),
'backup_type: ': self._backup_mode
}
self.zkOpers.write_backup_backup_info(record)
logging.error(e, exc_info=True)
finally:
class Node_start_action(Abstract_Mysql_Service_Action_Thread):
isNewCluster = False
dba_opers = DBAOpers()
def __init__(self, isNewCluster):
super(Node_start_action, self).__init__()
self.isNewCluster = isNewCluster
self.zkOper = self.retrieve_zkOper()
try:
self.isLock, self.lock = self.zkOper.lock_node_start_stop_action()
except kazoo.exceptions.LockTimeout:
raise CommonException(
"When start node, can't retrieve the start atcion lock!")
if not self.isLock:
raise CommonException(
"When start node, can't retrieve the start atcion lock!")
def start_run(self):
try:
self._issue_start_action(self.isNewCluster)
except:
self.threading_exception_queue.put(sys.exc_info())
finally:
if self.isLock is not None:
self.zkOper.unLock_node_start_stop_action(self.lock)
def _issue_start_action(self, isNewCluster):
dataNodeProKeyValue = self.confOpers.getValue(
options.data_node_property, ['dataNodeIp'])
data_node_ip = dataNodeProKeyValue['dataNodeIp']
finished_flag = self.dba_opers.retrieve_wsrep_status()
if not finished_flag:
self.invokeCommand.remove_mysql_socket()
self.invokeCommand.mysql_service_start(isNewCluster)
finished_flag = self.__check_start_status(data_node_ip)
if finished_flag:
self._send_email(data_node_ip,
" mysql service start operation finished")
def __check_start_status(self, data_node_ip):
finished_flag = False
sh_name = "ps -ef | grep mysqld_safe | grep -iv grep | wc -l"
count = 10
while not finished_flag and count >= 0:
result = self.invokeCommand.run_check_shell(sh_name)
if int(result) == 0:
finished_flag = False
count -= 1
#break
finished_flag = self.dba_opers.retrieve_wsrep_status()
time.sleep(2)
if finished_flag:
self.zkOper.write_started_node(data_node_ip)
return finished_flag
class Check_Database_User(Check_Status_Base):
dba_opers = DBAOpers()
def __init__(self):
super(Check_Database_User, self).__init__()
def _get_check_user_list(self):
conn = self.dba_opers.get_mysql_connection()
user_tuple = self.dba_opers.get_db_users(conn)
user_mysql_src_dict, user_zk_src_list = {}, []
zkOper = Scheduler_ZkOpers()
# We convert origin tuple grabbed from mysql into list,
# then combine the elements subscripted 0 ,1 as key of
# dict and combine the elements subscripted -3, -4 ,-5, -6
# as the value of the dict.Finally we append the dict into list.
for t in user_tuple:
inner_value_list = []
dict_key_str = (list(t)[1] + "|" + list(t)[0])
inner_value_list.append(list(t)[-3])
inner_value_list.append(list(t)[-4])
inner_value_list.append(list(t)[-5])
inner_value_list.append(list(t)[-6])
user_mysql_src_dict.setdefault(dict_key_str, inner_value_list)
db_list = zkOper.retrieve_db_list()
for db_name in db_list:
db_user_list = zkOper.retrieve_db_user_list(db_name)
logging.info("dbName: " + db_name + " db_user_list : " +
str(db_user_list))
for db_user in db_user_list:
inner_list = []
inner_list.append(db_user)
prop = zkOper.get_db_user_prop(db_name, db_user)
inner_list.append(prop)
user_zk_src_list.append(inner_list)
return user_mysql_src_dict, user_zk_src_list
@tornado.gen.engine
def check(self, data_node_info_list):
#url_post = "/dbuser/inner/check"
zkOper = Scheduler_ZkOpers()
if not is_monitoring(get_localhost_ip(), zkOper):
return
monitor_type, monitor_key = "db", "dbuser"
user_mysql_src_dict, user_zk_src_list = self._get_check_user_list()
error_record, differ_dict_set = {}, {}
count_dict_set = dict(total=0, failed=0, success=0)
if len(user_zk_src_list) == 0 and len(user_mysql_src_dict) == 0:
error_record.setdefault(
"msg", "no database users in zk neither in mysql")
differ_dict_set.setdefault("Empty", "")
else:
self.compare_center(user_mysql_src_dict, user_zk_src_list,
differ_dict_set, count_dict_set)
count_dict_set[
"total"] = count_dict_set["success"] + count_dict_set["failed"]
alarm_level = self.retrieve_alarm_level(count_dict_set["total"],
count_dict_set["success"],
count_dict_set["failed"])
total_count = count_dict_set["total"]
failed_count = count_dict_set["failed"]
success_count = count_dict_set["success"]
if differ_dict_set:
error_record.setdefault("dif", differ_dict_set)
super(Check_Database_User,
self).write_status(total_count, success_count, failed_count,
alarm_level, error_record, monitor_type,
monitor_key)
super(Check_Database_User,
self).write_status_to_es(total_count, success_count,
failed_count, alarm_level, error_record,
monitor_type, monitor_key)
def compare_center(self, _user_mysql_src_dict, _user_zk_src_list,
_differ_dict_set, _count_dict):
_user_mysql_src_dict_keys = _user_mysql_src_dict.keys()
logging.info("_user_mysql_src_dict_keys" +
str(_user_mysql_src_dict_keys))
logging.info("_user_mysql_src_dict ::::" + str(_user_mysql_src_dict))
logging.info("_user_zk_src_list: " + str(_user_zk_src_list))
for list_iter in _user_zk_src_list:
if list_iter[0] in _user_mysql_src_dict_keys:
if long(list_iter[1]["max_user_connections"]) == _user_mysql_src_dict[list_iter[0]][0] and \
long(list_iter[1]["max_connections_per_hour"]) == _user_mysql_src_dict[list_iter[0]][1] and \
long(list_iter[1]["max_updates_per_hour"]) == _user_mysql_src_dict[list_iter[0]][2] and \
long(list_iter[1]["max_queries_per_hour"]) == _user_mysql_src_dict[list_iter[0]][3]:
_count_dict["success"] = _count_dict["success"] + 1
continue
else:
inner_dict = {}
inner_dict.setdefault("message", "different")
logging.info("list_iter[0] :" + str(list_iter[0]))
_differ_dict_set.setdefault(list_iter[0], inner_dict)
_count_dict["failed"] = _count_dict["failed"] + 1
else:
inner_dict = {}
inner_dict.setdefault("message", "unknown")
logging.info("list_iter[0] :" + str(list_iter[0]))
_differ_dict_set.setdefault(list_iter[0], inner_dict)
_count_dict["failed"] = _count_dict["failed"] + 1
_user_zk_src_keys_list = []
for i in range(len(_user_zk_src_list)):
_user_zk_src_keys_list.append(_user_zk_src_list[i][0])
logging.info("_user_zk_src_keys_list :" + str(_user_zk_src_keys_list))
for _user_mysql_list_iter in _user_mysql_src_dict_keys:
if _user_mysql_list_iter not in _user_zk_src_keys_list:
inner_dict = {}
inner_dict.setdefault("message", "lost")
_differ_dict_set.setdefault(_user_mysql_list_iter, inner_dict)
_count_dict["failed"] = _count_dict["failed"] + 1
def retrieve_alarm_level(self, total_count, success_count, failed_count):
if failed_count == 0:
return options.alarm_nothing
else:
return options.alarm_general
class Check_DB_Anti_Item(Check_Status_Base):
dba_opers = DBAOpers()
def _anti_item_check(self, conn):
anti_item_count = 0
msg = ""
anti_item_detail = []
anti_item_myisam_count = self.dba_opers.check_existed_myisam_table(
conn)
anti_item_procedure_count = self.dba_opers.check_existed_stored_procedure(
conn)
anti_item_trigger_count = self.dba_opers.check_triggers(conn)
anti_item_nopk_count, anti_item_nopk_detail = self.dba_opers.check_existed_nopk(
conn)
anti_item_fulltext_and_spatial_count = self.dba_opers.check_existed_fulltext_and_spatial(
conn)
if anti_item_myisam_count:
anti_item_count += anti_item_myisam_count
msg += " Myisam,"
on_check_storedprocedure = options.on_check_storedprocedure
if anti_item_procedure_count and on_check_storedprocedure:
anti_item_message = ("check db status, existed stored "
"procedure. Item's count:%s") % (
str(anti_item_procedure_count))
self._send_monitor_email(anti_item_message)
if anti_item_trigger_count:
anti_item_count += anti_item_trigger_count
msg += " Trigger,"
if anti_item_nopk_count:
anti_item_count += anti_item_nopk_count
anti_item_detail += anti_item_nopk_detail
msg += " NOPK,"
if anti_item_fulltext_and_spatial_count:
anti_item_count += anti_item_fulltext_and_spatial_count
msg += " FullText, SPATIAL,"
return anti_item_count, msg, anti_item_detail
def check(self, data_node_info_list):
zkOper = Scheduler_ZkOpers()
if not is_monitoring(get_localhost_ip(), zkOper):
return
conn = self.dba_opers.get_mysql_connection()
monitor_type, monitor_key = "db", "existed_db_anti_item"
error_record = {}
anti_item_count, msg, failed_count = 0, "", 0
_path_value = zkOper.retrieve_monitor_status_value(
monitor_type, monitor_key)
if _path_value != {}:
failed_count = int(
re.findall(r'failed count=(\d)', _path_value['message'])[0])
if conn == None:
failed_count += 1
if failed_count > 4:
anti_item_count = 500
error_record.setdefault("msg", "no way to connect to db")
else:
try:
anti_item_count, msg, anti_item_detail = self._anti_item_check(
conn)
finally:
conn.close()
if anti_item_count > 0:
error_record.setdefault(
"msg",
"mcluster existed on %s please check which db right now." %
(msg))
error_record.setdefault("detail", anti_item_detail)
logging.info(error_record)
alarm_level = self.retrieve_alarm_level(anti_item_count, 0, 0)
logging.info("existed anti_item alarm_level :%s" % (alarm_level))
super(Check_DB_Anti_Item,
self).write_status(anti_item_count, 0, failed_count, alarm_level,
error_record, monitor_type, monitor_key)
super(Check_DB_Anti_Item,
self).write_status_to_es(anti_item_count, 0, failed_count,
alarm_level, error_record, monitor_type,
monitor_key)
def retrieve_alarm_level(self, total_count, success_count, failed_count):
if total_count == 0:
return options.alarm_nothing
return options.alarm_serious
def _send_monitor_email(self, anti_item_content):
local_ip = get_localhost_ip()
# send email
subject = "[%s] Auti-Item existed in MySQL according to Galera way" % options.sitename
body = anti_item_content + "\nip:" + local_ip
if options.send_email_switch:
send_email(options.admins, subject, body)
class Check_Node_Size(Check_Status_Base):
dba_opers = DBAOpers()
def check(self, data_node_info_list):
confOpers = ConfigFileOpers()
false_nodes, value, _password = [], {}, ''
value = confOpers.getValue(
options.mysql_cnf_file_name)["wsrep_sst_auth"]
_password = value.split(":")[1][:-1]
for data_node_ip in data_node_info_list:
conn = self.dba_opers.get_mysql_connection(data_node_ip,
user="******",
passwd=_password)
if conn == None:
false_nodes.append(data_node_ip)
else:
try:
rows = self.dba_opers.show_status(conn)
finally:
conn.close()
key_value = retrieve_kv_from_db_rows(
rows, ['wsrep_incoming_addresses', 'wsrep_cluster_size'])
node_size_dict = self._check_wsrep_incoming_addresses(
key_value, data_node_info_list)
return node_size_dict
if (len(false_nodes) == 3):
exception_dict = {}
exception_dict.setdefault("message", "no way to connect to db")
exception_dict.setdefault("alarm", options.alarm_serious)
return exception_dict
def _check_wsrep_incoming_addresses(self, key_value, data_node_info_list):
if key_value == {}:
return False
lost_ip_list = []
wsrep_incoming_addresses_value = key_value.get(
'wsrep_incoming_addresses')
logging.info(
"[compare Mcluster the count of data node] incoming address(show status):"
+ wsrep_incoming_addresses_value)
wsrep_incoming_addresses_list = wsrep_incoming_addresses_value.split(
',')
address_count = 0
zk_data_node_count = len(data_node_info_list)
for i in range(zk_data_node_count):
zk_incoming_address_port = data_node_info_list[i] + ':3306'
logging.info(
"[compare Mcluster the count of data node] address list(zk store):"
+ zk_incoming_address_port)
if zk_incoming_address_port in wsrep_incoming_addresses_list:
address_count = address_count + 1
else:
lost_ip_list.append(data_node_info_list[i])
total = zk_data_node_count
exist = address_count
lost = zk_data_node_count - address_count
alarm_level = self.retrieve_alarm_level(total, exist, lost)
node_size_dict = {}
lost_count = zk_data_node_count - address_count
format_str = "total=%s, exist=%s, lost=%s"
format_values = (zk_data_node_count, address_count, lost_count)
message = format_str % format_values
node_size_dict.setdefault("lost_ip", lost_ip_list)
node_size_dict.setdefault("message", message)
node_size_dict.setdefault("alarm", alarm_level)
return node_size_dict
class DBStatOpers(Abstract_Stat_Service):
dba_opers = DBAOpers()
confOpers = ConfigFileOpers()
def __init__(self):
'''
Constructor
'''
'''
@todo: why use str_flag?
'''
def stat(self, str_flag=""):
# if str_flag = "", then the request must come from the webport, not peer.
if str_flag == "":
rows_oper_dict = self._stat_rows_oper()
# We check if the local database is in use.
if (rows_oper_dict == {}
or rows_oper_dict["oper_total"]["num_reads"] == 0):
result_dict = self.get_peer_wsrep_status()
logging.info("rows_oper_dict:" + str(rows_oper_dict))
# When local database is in use, we go on processing in this node.
else:
wsrep_status_dict = self._stat_wsrep_status()
innodb_buffer_dict = self._stat_innodb_buffer()
variable_status_dict = self._stat_variable_status()
else:
# This str_flag must be "inner", This process the request from peer nodes.
wsrep_status_dict = self._stat_wsrep_status()
rows_oper_dict = self._stat_rows_oper()
innodb_buffer_dict = self._stat_innodb_buffer()
variable_status_dict = self._stat_variable_status()
# If we find that the local database is not in use, then the all results come from peer node.
if (rows_oper_dict == {}
or rows_oper_dict["oper_total"]["num_reads"] == 0):
return result_dict
result = {}
# Else we know that local database in in use, we return it in the original way.
result.setdefault('wsrep_status_dict', wsrep_status_dict)
result.setdefault('rows_oper', rows_oper_dict)
result.setdefault('innodb_buffer', innodb_buffer_dict)
result.setdefault('variable_status', variable_status_dict)
return result
def get_peer_wsrep_status(self):
logging.info("can not connect to local database site")
cluster_started_nodes = self.zkOper.retrieve_started_nodes()
confDict = self.confOpers.getValue(options.data_node_property,
['dataNodeIp'])
local_ip = confDict['dataNodeIp']
logging.info("local ip:" + str(local_ip))
if cluster_started_nodes.count(local_ip) != 0:
cluster_started_nodes.remove(local_ip)
logging.info("candicates are: " + str(cluster_started_nodes))
result = ""
for ip in cluster_started_nodes:
url_post = "/db/all/stat?inner=true"
result = self.communicate(ip, url_post)
logging.info("origin result: " + str(result))
logging.info(result.replace("\\", ""))
if result.count("wsrep_status_dict") != 0:
break
if result.count("wsrep_status_dict") != 0:
result_dict = json.loads(result)
return result_dict["response"]
else:
raise CommonException("Can\'t connect to mysql server")
def _stat_wsrep_status(self):
conn = self.dba_opers.get_mysql_connection()
if conn is None:
raise CommonException("Can\'t connect to mysql server")
try:
rows = self.dba_opers.show_status(conn)
finally:
conn.close()
key_value = retrieve_kv_from_db_rows(rows,['wsrep_flow_control_paused',\
'wsrep_flow_control_sent',\
'wsrep_local_recv_queue_avg',\
'wsrep_local_send_queue_avg'])
slowest_node_param_dict = {}
slowest_node_param_dict.setdefault(
'wsrep_flow_control_sent',
key_value.get('wsrep_flow_control_sent'))
slowest_node_param_dict.setdefault(
'wsrep_local_recv_queue_avg',
key_value.get('wsrep_local_recv_queue_avg'))
result = {}
result.setdefault('wsrep_flow_control_paused',
key_value.get('wsrep_flow_control_paused'))
result.setdefault('slowest_node_param', slowest_node_param_dict)
result.setdefault('wsrep_local_send_queue_avg',
key_value.get('wsrep_local_send_queue_avg'))
return result
def communicate(self, peer_ip, url):
http_client = tornado.httpclient.HTTPClient()
requesturi = "http://" + peer_ip + ":" + str(options.port) + url
try:
response = http_client.fetch(requesturi)
except tornado.httpclient.HTTPError as e:
logging.error(str(e))
http_client.close()
return "error"
logging.info(str(response.body))
return response.body
def stat_wsrep_status_flow_control_paused(self):
status_dict = self._stat_wsrep_status()
value = status_dict.get('wsrep_flow_control_paused')
return {'wsrep_flow_control_paused': value}
def stat_wsrep_status_slowest_node_param(self):
status_dict = self._stat_wsrep_status()
sub_dict = status_dict.get('slowest_node_param')
return sub_dict
def stat_wsrep_status_slowest_network_param(self):
status_dict = self._stat_wsrep_status()
value = status_dict.get('wsrep_local_send_queue_avg')
return {'wsrep_local_send_queue_avg': value}
@run_on_executor()
@run_callback
def stat_binlog_eng_log_pos(self, params):
if not params:
raise UserVisiableException('params are not given')
conn = self.dba_opers.get_mysql_connection()
if None == conn:
raise UserVisiableException("Can\'t connect to mysql server")
log_pos_info = ''
master_log_file = ''
end_log_pos = ''
try:
cursor = conn.cursor()
cursor.execute('show binary logs')
rows_bin_logs = cursor.fetchall()
assert rows_bin_logs
invokecommand = InvokeCommand()
for i in range(len(rows_bin_logs)):
master_log_file = rows_bin_logs[-i - 1][-2]
ret_str = invokecommand._runSysCmd(
'''mysql -uroot -pMcluster -e "show binlog events IN '%s'"|grep 'xid=%s' '''
% (master_log_file, params['xid']))
assert ret_str
log_pos_info = ret_str[0]
if log_pos_info:
break
end_log_pos = log_pos_info.strip('\n').split('\t')[-2]
finally:
conn.close()
result = {}
result.setdefault('Master_Log_File', master_log_file)
result.setdefault('End_Log_Pos', end_log_pos)
return result
@run_on_executor()
@run_callback
def bin_log_node_stat(self):
conn = self.dba_opers.get_mysql_connection()
if None == conn:
raise UserVisiableException("Can\'t connect to mysql server")
try:
cursor = conn.cursor()
cursor.execute("show variables like 'log_bin'")
rows_stat_log_bin = cursor.fetchall()
stat_log_bin = rows_stat_log_bin[0][1]
finally:
conn.close()
zkOper = self.retrieve_zkOper()
started_node_list = zkOper.retrieve_started_nodes()
local_ip = get_localhost_ip()
if local_ip in started_node_list:
started_node_list.remove(local_ip)
result = {}
result.setdefault('node_list', started_node_list)
result.setdefault('stat_log_bin', stat_log_bin)
return result
def _stat_rows_oper(self):
processor_existed = self._check_mysql_processor_exist()
result = {}
if not processor_existed:
return result
target_dict = self._retrieve_dict_with_result(options.stat_rows_oper)
key_list = ['num_updates', 'num_reads', 'num_deletes', 'num_inserts']
oper_total_dict = self._split_key_value(key_list, target_dict)
key_list = [
'num_reads_sec', 'num_updates_sec', 'num_deletes_sec',
'num_inserts_sec'
]
oper_per_second_dict = self._split_key_value(key_list, target_dict)
result.setdefault("oper_total", oper_total_dict)
result.setdefault("oper_per_second", oper_per_second_dict)
return result
def stat_rows_oper_total(self):
oper_dict = self._stat_rows_oper()
sub_dict = oper_dict.get('oper_total')
return sub_dict
def stat_rows_oper_per_second(self):
oper_dict = self._stat_rows_oper()
sub_dict = oper_dict.get('oper_per_second')
return sub_dict
def _stat_innodb_buffer(self):
processor_existed = self._check_mysql_processor_exist()
result = {}
if not processor_existed:
return result
target_dict = self._retrieve_dict_with_result(
options.stat_innodb_buffer)
key_list = ['total_mem_alloc', 'add_pool_alloc']
mem_alloc_dict = self._split_key_value(key_list, target_dict)
key_list = ['pages_total', 'pages_modified']
page_dict = self._split_key_value(key_list, target_dict)
key_list = ['buf_pool_size', 'buf_pool_hit_rate', 'buf_free']
buffer_pool_dict = self._split_key_value(key_list, target_dict)
value = buffer_pool_dict.get('buf_pool_hit_rate')
if value == '--' or value == '' or value == 0:
value = 0
else:
buf_pool_hit_rate_list = value.split('/')
value = int(buf_pool_hit_rate_list[0]) / int(
buf_pool_hit_rate_list[1])
buffer_pool_dict['buf_pool_hit_rate'] = str(value)
result.setdefault("mem_alloc", mem_alloc_dict)
result.setdefault("page", page_dict)
result.setdefault("buffer_pool", buffer_pool_dict)
return result
def stat_innodb_buffer_mem_alloc(self):
buffer_dict = self._stat_innodb_buffer()
sub_dict = buffer_dict.get('mem_alloc')
return sub_dict
def stat_innodb_buffer_page(self):
buffer_dict = self._stat_innodb_buffer()
sub_dict = buffer_dict.get('page')
return sub_dict
def stat_innodb_buffer_buffer_pool(self):
buffer_dict = self._stat_innodb_buffer()
sub_dict = buffer_dict.get('buffer_pool')
return sub_dict
def _stat_variable_status(self):
processor_existed = self._check_mysql_processor_exist()
result = {}
if not processor_existed:
return result
target_dict = self._retrieve_dict_with_result(
options.stat_variable_status)
key_list = ['Opens_PS', 'QPS', 'Commit_PS', 'Threads_PS']
ps_dict = self._split_key_value(key_list, target_dict)
key_list = [
'Thread_Cache_Used', 'CXN_Used_Ever', 'CXN_Used_Now',
'Table_Cache_Used'
]
used_dict = self._split_key_value(key_list, target_dict)
key_list = ['R_W_Ratio', 'Rollback_Commit', 'Write_Commit']
ratio_dict = self._split_key_value(key_list, target_dict)
result.setdefault("ps", ps_dict)
result.setdefault("used", used_dict)
result.setdefault("ration", ratio_dict)
return result
def stat_variable_status_ps(self):
status_dict = self._stat_variable_status()
sub_dict = status_dict.get('ps')
return sub_dict
def stat_variable_status_used(self):
status_dict = self._stat_variable_status()
sub_dict = status_dict.get('used')
return sub_dict
def stat_variable_status_ration(self):
status_dict = self._stat_variable_status()
sub_dict = status_dict.get('ration')
return sub_dict
class Inner_DB_Check_WR(APIHandler):
dba_opers = DBAOpers()
confOpers = ConfigFileOpers()
invokeCommand = InvokeCommand()
#
def get(self):
conn = self.dba_opers.get_mysql_connection()
try:
dataNodeProKeyValue = self.confOpers.getValue(
options.data_node_property, ['dataNodeIp'])
data_node_ip = dataNodeProKeyValue['dataNodeIp']
zkOper = self.retrieve_zkOper()
started_ip_list = zkOper.retrieve_started_nodes()
identifier = socket.gethostname()
'''
@todo: review the comment code for arbitrator way
'''
# ret_dict = self.confOpers.getValue(options.data_node_property, ['dataNodeName','dataNodeIp'])
# node_name = ret_dict['dataNodeName']
# obj = re.search("-n-2", node_name)
# if obj != None:
# self.finish("true")
# return
if conn is None:
if data_node_ip in started_ip_list:
zkOper.remove_started_node(data_node_ip)
self.invokeCommand.run_check_shell(options.kill_innotop)
self.finish("false")
return
zkOper.write_started_node(data_node_ip)
if not is_monitoring(get_localhost_ip(), zkOper):
self.finish("true")
return
dbName = 'monitor'
n_time = datetime.datetime.now()
h = n_time.hour
min = n_time.minute
offset = h / 6
tbName = ''
prefix_tb_name = 'tem'
mid_tb_name = str(identifier)
mid_tb_name_rps = mid_tb_name.replace("-", "_")
pre_tbname = prefix_tb_name + mid_tb_name_rps
for i in range(4):
tbName = pre_tbname + "_" + str(i)
self.dba_opers.check_create_table(conn, tbName, dbName)
tbName = pre_tbname + "_" + str(offset)
del_tbName = ''
ft = float(time.time())
if h % 6 == 0 and min <= 59 and (1000000 * ft) % 10 == 0:
int_tbName = (offset + 2) % 4
del_tbName = "%s_%s" % (pre_tbname, int_tbName)
self.dba_opers.delete_tb_contents(conn, del_tbName, dbName)
logging.info(
'delete the contents in database (%s) before 12 hours success!'
% (del_tbName))
str_time = n_time.strftime(TIME_FORMAT)
self.dba_opers.insert_record_time(conn, str_time, identifier,
tbName, dbName)
logging.info('Insert time %s into table %s ' % (str_time, tbName))
record_time = self.dba_opers.query_record_time(
conn, identifier, tbName, dbName)
except Exception, e:
return_flag = 'false'
logging.error(e)
self.finish(return_flag)
return
finally:
class DBUser(APIHandler):
dba_opers = DBAOpers()
conf_opers = ConfigFileOpers()
def post(self):
role = self.get_argument("role", None)
dbName = self.get_argument("dbName", None)
userName = self.get_argument("userName", None)
user_password = self.get_argument("user_password", None)
ip_address = self.get_argument("ip_address", '%')
max_queries_per_hour = self.get_argument("max_queries_per_hour", 0)
max_updates_per_hour = self.get_argument("max_updates_per_hour", 0)
max_connections_per_hour = self.get_argument("max_connections_per_hour", 0)
max_user_connections = self.get_argument("max_user_connections", 200)
dict = {}
dict = self.request.arguments
if dict.has_key("user_password"):
del dict["user_password"]
logging.info(str(dict))
if role is None:
raise HTTPAPIErrorException("when create db's user, no specify the user role, please specify the user role.",
status_code=417)
if dbName is None:
raise HTTPAPIErrorException("when create db's user, no specify the database name, please specify the database name.",
status_code=417)
if userName is None:
raise HTTPAPIErrorException("when create db's user, no specify the user name, please specify the user name.",
status_code=417)
if ip_address is None:
raise HTTPAPIErrorException("when create db's user, no specify the ip address, please specify the ip address.",
status_code=417)
if user_password is None:
user_password = get_random_password()
existed_flag = "true"
conn = self.dba_opers.get_mysql_connection()
try:
existed_flag = self.dba_opers.check_if_existed_database(conn, dbName)
if existed_flag == "false":
raise HTTPAPIErrorException("Please create database " + dbName + " first",
status_code=417)
self.dba_opers.create_user(conn, userName, user_password, ip_address)
if 'manager' == role:
self.dba_opers.grant_manager_privileges(conn, userName, user_password, dbName, ip_address,
max_queries_per_hour,
max_updates_per_hour,
max_connections_per_hour,
max_user_connections)
elif 'wr' == role:
self.dba_opers.grant_wr_privileges(conn, userName, user_password, dbName, ip_address,
max_queries_per_hour,
max_updates_per_hour,
max_connections_per_hour,
max_user_connections)
elif 'ro' == role:
max_updates_per_hour = 1
self.dba_opers.grant_readonly_privileges(conn, userName, user_password, dbName, ip_address,
max_queries_per_hour,
max_connections_per_hour,
max_user_connections)
else:
# use try catch to close the conn
# conn.close()
raise HTTPAPIErrorException("please valid the specified role, the type is [manager,ro,wr]", status_code=417)
self.dba_opers.flush_privileges(conn)
finally:
conn.close()
# check if exist cluster
zkOper = self.retrieve_zkOper()
clusterUUID = zkOper.getClusterUUID()
userProps = {'role': role,
'max_queries_per_hour': max_queries_per_hour,
'max_updates_per_hour': max_updates_per_hour,
'max_connections_per_hour': max_connections_per_hour,
'max_user_connections': max_user_connections}
zkOper.write_user_info(clusterUUID, dbName, userName, ip_address, userProps)
result = {}
# dict.setdefault("code", '000000')
result.setdefault("message", "user has been created successful!")
result.setdefault("user_role", role)
result.setdefault("user_name", userName)
result.setdefault("user_password", user_password)
self.finish(result)
def put(self):
dbName = self.get_argument("dbName", None)
userName = self.get_argument("userName", None)
ip_address = self.get_argument("ip_address", '%')
max_queries_per_hour = self.get_argument("max_queries_per_hour", None)
max_updates_per_hour = self.get_argument("max_updates_per_hour", None)
max_connections_per_hour = self.get_argument("max_connections_per_hour", None)
max_user_connections = self.get_argument("max_user_connections", None)
role = self.get_argument("role", None)
if dbName is None:
raise HTTPAPIErrorException("when modify db's user, no specify the database name, please specify the database name.",
status_code=417)
if userName is None:
raise HTTPAPIErrorException("when modify db's user, no specify the user name, please specify the user name.",
status_code=417)
if ip_address is None:
raise HTTPAPIErrorException("when modify db's user, no specify the ip address, please specify the ip address.",
status_code=417)
if max_queries_per_hour is None and max_updates_per_hour is None and max_connections_per_hour is None and max_user_connections is None:
raise HTTPAPIErrorException("when modify db's user, no specify any modified parameter, please specify the ip address.\
please specify any one or all of following parameter:[max_queries_per_hour,\
max_updates_per_hour,max_connections_per_hour,max_user_connections]",
status_code=417)
if role is None:
raise HTTPAPIErrorException("when modify db's user, no specify the role, please specify the role.",
status_code=417)
conn = self.dba_opers.get_mysql_connection()
try:
zkOper = self.retrieve_zkOper()
clusterUUID = zkOper.getClusterUUID()
user_limit_map = {}
if not max_queries_per_hour or not max_updates_per_hour or not max_connections_per_hour or not max_user_connections:
user_limit_map = zkOper.retrieve_user_limit_props(clusterUUID, dbName, userName, ip_address)
if not user_limit_map:
raise HTTPAPIErrorException("when modify db's user, no found specified user!\
please check the valid of the specified user, because the system no found the user!",
status_code=417)
if max_queries_per_hour is None:
max_queries_per_hour = user_limit_map.get('max_queries_per_hour')
if max_updates_per_hour is None:
max_updates_per_hour = user_limit_map.get('max_updates_per_hour')
if max_connections_per_hour is None:
max_connections_per_hour = user_limit_map.get('max_connections_per_hour')
if max_user_connections is None:
max_user_connections = user_limit_map.get('max_user_connections')
self.dba_opers.grant_resource_limit(conn, userName, dbName, ip_address, role,
max_queries_per_hour,
max_updates_per_hour,
max_connections_per_hour,
max_user_connections)
self.dba_opers.flush_privileges(conn)
userProps = {'role': user_limit_map.get('role'),
'max_queries_per_hour': max_queries_per_hour,
'max_updates_per_hour': max_updates_per_hour,
'max_connections_per_hour': max_connections_per_hour,
'max_user_connections': max_user_connections}
zkOper.write_user_info(clusterUUID, dbName, userName, ip_address, userProps)
finally:
conn.close()
result = {}
result.setdefault("message", "modify the user's resource limit successfully!")
result.setdefault("db_name", dbName)
result.setdefault("user_name", userName)
self.finish(result)
def delete(self, dbName, userName, ipAddress):
if not dbName:
raise HTTPAPIErrorException("when remove db's user, no specify the database name,\
please specify the database name.",
status_code=417)
if not userName:
raise HTTPAPIErrorException("when remove db's user, no specify the user name,\
please specify the user name.",
status_code=417)
if not ipAddress:
raise HTTPAPIErrorException("when remove db's user, no specify the ip address,\
please specify the ip address.",
status_code=417)
conn = self.dba_opers.get_mysql_connection()
try:
self.dba_opers.delete_user(conn, userName, ipAddress)
finally:
conn.close()
# check if exist cluster
zkOper = self.retrieve_zkOper()
clusterUUID = zkOper.getClusterUUID()
zkOper.remove_db_user(clusterUUID, dbName, userName, ipAddress)
result = {}
result.setdefault("message", "removed user successfully!")
result.setdefault("user_name", userName)
result.setdefault("ip_address", ipAddress)
self.finish(result)
