def ResDatabasePassword(self,get): try: newpassword = get['password'] username = get['username'] id = get['id'] name = public.M('databases').where('id=?',(id,)).getField('name'); rep = "^[\w#@%\.]+$" if len(re.search(rep, newpassword).groups()) > 0: return public.returnMsg(False, 'DATABASE_NAME_ERR_T') #修改MYSQL if '5.7' in public.readFile(web.ctx.session.setupPath + '/mysql/version.pl'): result = panelMysql.panelMysql().execute("update mysql.user set authentication_string=password('" + newpassword + "') where User='******'") else: result = panelMysql.panelMysql().execute("update mysql.user set Password=password('" + newpassword + "') where User='******'") isError=self.IsSqlError(result) if isError != None: return isError panelMysql.panelMysql().execute("flush privileges") #if result==False: return public.returnMsg(False,'DATABASE_PASS_ERR_NOT_EXISTS') #修改SQLITE if int(id) > 0: public.M('databases').where("id=?",(id,)).setField('password',newpassword) else: public.M('config').where("id=?",(id,)).setField('mysql_root',newpassword) web.ctx.session.config['mysql_root'] = newpassword public.WriteLog("TYPE_DATABASE",'DATABASE_PASS_SUCCESS',(name,)) return public.returnMsg(True,'DATABASE_PASS_SUCCESS',(name,)) except Exception,ex: public.WriteLog("TYPE_DATABASE", 'DATABASE_PASS_ERROR',(name,str(ex))) return public.returnMsg(False,'DATABASE_PASS_ERROR',(name,))
def SyncGetDatabases(self,get): data = panelMysql.panelMysql().query("show databases") isError = self.IsSqlError(data) if isError != None: return isError users = panelMysql.panelMysql().query("select User,Host from mysql.user where User!='root' AND Host!='localhost' AND Host!=''") sql = public.M('databases') nameArr = ['information_schema','performance_schema','mysql','sys'] n = 0 for value in data: b = False for key in nameArr: if value[0] == key: b = True break if b:continue if sql.where("name=?",(value[0],)).count(): continue host = '127.0.0.1' for user in users: if value[0] == user[0]: host = user[1] break ps = public.getMsg('INPUT_PS') if value[0] == 'test': ps = public.getMsg('DATABASE_TEST') addTime = time.strftime('%Y-%m-%d %X',time.localtime()) if sql.table('databases').add('name,username,password,accept,ps,addtime',(value[0],value[0],'',host,ps,addTime)): n +=1 return public.returnMsg(True,'DATABASE_GET_SUCCESS',(str(n),))
def DeleteDatabase(self,get): try: id=get['id'] name = get['name'] if os.path.exists('data/recycle_bin_db.pl'): return self.DeleteToRecycleBin(name); find = public.M('databases').where("id=?",(id,)).field('id,pid,name,username,password,accept,ps,addtime').find(); accept = find['accept']; username = find['username']; #删除MYSQL result = panelMysql.panelMysql().execute("drop database `" + name + "`") isError=self.IsSqlError(result) if isError != None: return isError users = panelMysql.panelMysql().query("select Host from mysql.user where User='******' AND Host!='localhost'") panelMysql.panelMysql().execute("drop user '" + username + "'@'localhost'") for us in users: panelMysql.panelMysql().execute("drop user '" + username + "'@'" + us[0] + "'") panelMysql.panelMysql().execute("flush privileges") #删除SQLITE public.M('databases').where("id=?",(id,)).delete() public.WriteLog("TYPE_DATABASE", 'DATABASE_DEL_SUCCESS',(name,)) return public.returnMsg(True, 'DEL_SUCCESS') except Exception,ex: public.WriteLog("TYPE_DATABASE",'DATABASE_DEL_ERR',(get.name , str(ex))) return public.returnMsg(False,'DEL_ERROR')
def RecycleDB(self,filename): import json data = json.loads(public.readFile(filename)) if public.M('databases').where("name=?",( data['name'],)).count(): os.remove(filename); return public.returnMsg(True,'RECYCLEDB'); result = panelMysql.panelMysql().execute("grant all privileges on `" + data['name'] + "`.* to '" + data['username'] + "'@'localhost' identified by '" + data['password'] + "'") isError=self.IsSqlError(result) if isError != None: return isError panelMysql.panelMysql().execute("grant all privileges on `" + data['name'] + "`.* to '" + data['username'] + "'@'" + data['accept'] + "' identified by '" + data['password'] + "'") panelMysql.panelMysql().execute("flush privileges") public.M('databases').add('id,pid,name,username,password,accept,ps,addtime',(data['id'],data['pid'],data['name'],data['username'],data['password'],data['accept'],data['ps'],data['addtime'])) os.remove(filename); return public.returnMsg(True,"RECYCLEDB");
def GetRunStatus(self,get): import time; result = {} data = panelMysql.panelMysql().query('show global status'); gets = ['Max_used_connections','Com_commit','Com_rollback','Questions','Innodb_buffer_pool_reads','Innodb_buffer_pool_read_requests','Key_reads','Key_read_requests','Key_writes','Key_write_requests','Qcache_hits','Qcache_inserts','Bytes_received','Bytes_sent','Aborted_clients','Aborted_connects','Created_tmp_disk_tables','Created_tmp_tables','Innodb_buffer_pool_pages_dirty','Opened_files','Open_tables','Opened_tables','Select_full_join','Select_range_check','Sort_merge_passes','Table_locks_waited','Threads_cached','Threads_connected','Threads_created','Threads_running','Connections','Uptime'] for d in data: for g in gets: if d[0] == g: result[g] = d[1]; result['Run'] = int(time.time()) - int(result['Uptime']) tmp = panelMysql.panelMysql().query('show master status'); try: result['File'] = tmp[0][0]; result['Position'] = tmp[0][1]; except: result['File'] = 'OFF'; result['Position'] = 'OFF'; return result;
def GetDbStatus(self,get): result = {}; data = panelMysql.panelMysql().query('show variables'); gets = ['table_open_cache','thread_cache_size','query_cache_type','key_buffer_size','query_cache_size','tmp_table_size','max_heap_table_size','innodb_buffer_pool_size','innodb_additional_mem_pool_size','innodb_log_buffer_size','max_connections','sort_buffer_size','read_buffer_size','read_rnd_buffer_size','join_buffer_size','thread_stack','binlog_cache_size']; result['mem'] = {} for d in data: for g in gets: if d[0] == g: result['mem'][g] = d[1]; if result['mem']['query_cache_type'] != 'ON': result['mem']['query_cache_size'] = '0'; return result;
def SetDatabaseAccess(self, get): #try: name = get['name'] db_name = public.M('databases').where('username=?', (name, )).getField('name') access = get['access'] password = public.M('databases').where("username=?", (name, )).getField('password') users = panelMysql.panelMysql().query( "select Host from mysql.user where User='******' AND Host!='localhost'") for us in users: panelMysql.panelMysql().execute("drop user '" + name + "'@'" + us[0] + "'") self.__CreateUsers(db_name, name, password, access) #for a in access.split(','): # panelMysql.panelMysql().execute("grant all privileges on " + db_name + ".* to '" + name + "'@'" + a + "' identified by '" + password + "'") #panelMysql.panelMysql().execute("flush privileges") return public.returnMsg(True, 'SET_SUCCESS')
def GetDatabaseAccess(self,get): name = get['name'] users = panelMysql.panelMysql().query("select Host from mysql.user where User='******' AND Host!='localhost'") isError = self.IsSqlError(users) if isError != None: return isError if len(users)<1: return public.returnMsg(True,['','']) accs = [] for c in users: accs.append(c[0]); userStr = ','.join(accs); return public.returnMsg(True,userStr)
def GetDbStatus(self, get): result = {} data = panelMysql.panelMysql().query('show variables') gets = ['table_open_cache', 'thread_cache_size', 'query_cache_type', 'key_buffer_size', 'query_cache_size', 'tmp_table_size', 'max_heap_table_size', 'innodb_buffer_pool_size', 'innodb_additional_mem_pool_size', 'innodb_log_buffer_size', 'max_connections', 'sort_buffer_size', 'read_buffer_size', 'read_rnd_buffer_size', 'join_buffer_size', 'thread_stack', 'binlog_cache_size'] result['mem'] = {} for d in data: for g in gets: if d[0] == g: result['mem'][g] = d[1] if result['mem']['query_cache_type'] != 'ON': result['mem']['query_cache_size'] = '0' return result
def SetupPassword(self,get): password = get['password'].strip() try: rep = "^[\w#@%\.]+$" if not re.match(rep, password): return public.returnMsg(False, 'DATABASE_NAME_ERR_T') mysql_root = public.M('config').where("id=?",(1,)).getField('mysql_root') #修改MYSQL result = panelMysql.panelMysql().query("show databases") isError=self.IsSqlError(result) if isError != None: #尝试使用新密码 public.M('config').where("id=?",(1,)).setField('mysql_root',password) result = panelMysql.panelMysql().query("show databases") isError=self.IsSqlError(result) if isError != None: root_mysql = '''#!/bin/bash PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:~/bin export PATH pwd=$1 /etc/init.d/mysqld stop mysqld_safe --skip-grant-tables& echo '正在修改密码...'; echo 'The set password...'; sleep 6 mysql -uroot -e "insert into mysql.user(Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Reload_priv,Shutdown_priv,Process_priv,File_priv,Grant_priv,References_priv,Index_priv,Alter_priv,Show_db_priv,Super_priv,Create_tmp_table_priv,Lock_tables_priv,Execute_priv,Repl_slave_priv,Repl_client_priv,Create_view_priv,Show_view_priv,Create_routine_priv,Alter_routine_priv,Create_user_priv,Event_priv,Trigger_priv,Create_tablespace_priv,User,Password,host)values('Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','root',password('${pwd}'),'127.0.0.1')" mysql -uroot -e "insert into mysql.user(Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Reload_priv,Shutdown_priv,Process_priv,File_priv,Grant_priv,References_priv,Index_priv,Alter_priv,Show_db_priv,Super_priv,Create_tmp_table_priv,Lock_tables_priv,Execute_priv,Repl_slave_priv,Repl_client_priv,Create_view_priv,Show_view_priv,Create_routine_priv,Alter_routine_priv,Create_user_priv,Event_priv,Trigger_priv,Create_tablespace_priv,User,Password,host)values('Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','root',password('${pwd}'),'localhost')" mysql -uroot -e "UPDATE mysql.user SET password=PASSWORD('${pwd}') WHERE user='******'"; mysql -uroot -e "UPDATE mysql.user SET authentication_string=PASSWORD('${pwd}') WHERE user='******'"; mysql -uroot -e "FLUSH PRIVILEGES"; pkill -9 mysqld_safe pkill -9 mysqld sleep 2 /etc/init.d/mysqld start echo '===========================================' echo "root密码成功修改为: ${pwd}" echo "The root password set ${pwd} successuful"'''; public.writeFile('mysql_root.sh',root_mysql) os.system("bash mysql_root.sh " + password) os.system("rm -f mysql_root.sh") else: if '5.7' in public.readFile(web.ctx.session.setupPath + '/mysql/version.pl'): result = panelMysql.panelMysql().execute("update mysql.user set authentication_string=password('" + password + "') where User='******'") else: result = panelMysql.panelMysql().execute("update mysql.user set Password=password('" + password + "') where User='******'") panelMysql.panelMysql().execute("flush privileges") msg = public.getMsg('DATABASE_ROOT_SUCCESS'); #修改SQLITE public.M('config').where("id=?",(1,)).setField('mysql_root',password) public.WriteLog("TYPE_DATABASE", "DATABASE_ROOT_SUCCESS") web.ctx.session.config['mysql_root']=password return public.returnMsg(True,msg) except Exception,ex: return public.returnMsg(False,'EDIT_ERROR');
def DeleteTo(self,filename): import json data = json.loads(public.readFile(filename)) if public.M('databases').where("name=?",( data['name'],)).count(): os.remove(filename); return public.returnMsg(True,'DEL_SUCCESS'); result = panelMysql.panelMysql().execute("drop database `" + data['name'] + "`") isError=self.IsSqlError(result) if isError != None: return isError panelMysql.panelMysql().execute("drop user '" + data['username'] + "'@'localhost'") users = panelMysql.panelMysql().query("select Host from mysql.user where User='******'username'] + "' AND Host!='localhost'") for us in users: panelMysql.panelMysql().execute("drop user '" + data['username'] + "'@'" + us[0] + "'") panelMysql.panelMysql().execute("flush privileges") os.remove(filename); public.WriteLog("TYPE_DATABASE", 'DATABASE_DEL_SUCCESS',(data['name'],)) return public.returnMsg(True,'DEL_SUCCESS');
def SetDatabaseAccess(self,get): #try: name = get['name'] db_name = public.M('databases').where('username=?',(name,)).getField('name'); access = get['access'] #if access != '%' and filter_var(access, FILTER_VALIDATE_IP) == False: return public.returnMsg(False, '权限格式不合法') password = public.M('databases').where("username=?",(name,)).getField('password') users = panelMysql.panelMysql().query("select Host from mysql.user where User='******' AND Host!='localhost'") for us in users: panelMysql.panelMysql().execute("drop user '" + name + "'@'" + us[0] + "'") for a in access.split(','): panelMysql.panelMysql().execute("grant all privileges on " + db_name + ".* to '" + name + "'@'" + a + "' identified by '" + password + "'") panelMysql.panelMysql().execute("flush privileges") return public.returnMsg(True, 'SET_SUCCESS')
def DeleteToRecycleBin(self,name): import json data = public.M('databases').where("name=?",(name,)).field('id,pid,name,username,password,accept,ps,addtime').find(); username = data['username']; panelMysql.panelMysql().execute("drop user '" + username + "'@'localhost'"); users = panelMysql.panelMysql().query("select Host from mysql.user where User='******' AND Host!='localhost'") for us in users: panelMysql.panelMysql().execute("drop user '" + username + "'@'" + us[0] + "'") panelMysql.panelMysql().execute("flush privileges"); rPath = '/www/Recycle_bin/'; public.writeFile(rPath + 'BTDB_' + name +'_t_' + str(time.time()),json.dumps(data)); public.M('databases').where("name=?",(name,)).delete(); public.WriteLog("TYPE_DATABASE", 'DATABASE_DEL_SUCCESS',(name,)); return public.returnMsg(True,'RECYCLE_BIN_DB');
def GetdataInfo(self, get): ''' 传递一个数据库名称即可 get.databases ''' if not self.DB_MySQL: self.DB_MySQL = panelMysql.panelMysql() db_name = get.db_name if not db_name: return False ret = {} tables = self.map_to_list( self.DB_MySQL.query('show tables from `%s`' % db_name)) if type(tables) == list: try: data = self.map_to_list( self.DB_MySQL.query( "select sum(DATA_LENGTH)+sum(INDEX_LENGTH) from information_schema.tables where table_schema='%s'" % db_name))[0][0] except: data = 0 if not data: data = 0 ret['data_size'] = self.ToSize(data) ret['database'] = db_name ret3 = [] for i in tables: table = self.map_to_list( self.DB_MySQL.query( "show table status from `%s` where name = '%s'" % (db_name, i[0]))) ret2 = {} ret2['type'] = table[0][1] data_size = table[0][6] ret2['rows_count'] = table[0][4] ret2['collation'] = table[0][14] ret2['data_size'] = self.ToSize(int(data_size)) ret2['table_name'] = i[0] ret3.append(ret2) ret['tables'] = (ret3) return ret
def ToDataBase(self,find): if find['username'] == 'bt_default': return 0 if len(find['password']) < 3 : find['username'] = find['name'] find['password'] = public.md5(str(time.time()) + find['name'])[0:10] public.M('databases').where("id=?",(find['id'],)).save('password,username',(find['password'],find['username'])) result = panelMysql.panelMysql().execute("create database " + find['name']) if "using password:"******"Connection refused" in str(result): return -1 panelMysql.panelMysql().execute("drop user '" + find['username'] + "'@'localhost'") panelMysql.panelMysql().execute("drop user '" + find['username'] + "'@'" + find['accept'] + "'") password = find['password'] if find['password']!="" and len(find['password']) > 20: password = find['password'] panelMysql.panelMysql().execute("grant all privileges on " + find['name'] + ".* to '" + find['username'] + "'@'localhost' identified by '" + password + "'") panelMysql.panelMysql().execute("grant all privileges on " + find['name'] + ".* to '" + find['username'] + "'@'" + find['accept'] + "' identified by '" + password + "'") panelMysql.panelMysql().execute("flush privileges") return 1
def AddDatabase(self,get): try: data_name = get['name'].strip() if self.CheckRecycleBin(data_name): return public.returnMsg(False,'数据库['+data_name+']已在回收站,请从回收站恢复!'); if len(data_name) > 16: return public.returnMsg(False, 'DATABASE_NAME_LEN') reg = "^\w+$" if not re.match(reg, data_name): return public.returnMsg(False,'DATABASE_NAME_ERR_T') if not hasattr(get,'db_user'): get.db_user = data_name; username = get.db_user.strip(); checks = ['root','mysql','test','sys','panel_logs'] if username in checks or len(username) < 1: return public.returnMsg(False,'数据库用户名不合法!'); if data_name in checks or len(data_name) < 1: return public.returnMsg(False,'数据库名称不合法!'); data_pwd = get['password'] if len(data_pwd)<1: data_pwd = public.md5(time.time())[0:8] sql = public.M('databases') if sql.where("name=? or username=?",(data_name,username)).count(): return public.returnMsg(False,'DATABASE_NAME_EXISTS') address = get['address'].strip() user = '******' password = data_pwd codeing = get['codeing'] wheres={ 'utf8' : 'utf8_general_ci', 'utf8mb4' : 'utf8mb4_general_ci', 'gbk' : 'gbk_chinese_ci', 'big5' : 'big5_chinese_ci' } codeStr=wheres[codeing] #添加MYSQL result = panelMysql.panelMysql().execute("create database `" + data_name + "` DEFAULT CHARACTER SET " + codeing + " COLLATE " + codeStr) isError = self.IsSqlError(result) if isError != None: return isError panelMysql.panelMysql().execute("drop user '" + username + "'@'localhost'") panelMysql.panelMysql().execute("drop user '" + username + "'@'" + address + "'") panelMysql.panelMysql().execute("grant all privileges on `" + data_name + "`.* to '" + username + "'@'localhost' identified by '" + data_pwd + "'") for a in address.split(','): panelMysql.panelMysql().execute("grant all privileges on `" + data_name + "`.* to '" + username + "'@'" + a + "' identified by '" + data_pwd + "'") panelMysql.panelMysql().execute("flush privileges") if get['ps'] == '': get['ps']=public.getMsg('INPUT_PS'); addTime = time.strftime('%Y-%m-%d %X',time.localtime()) pid = 0 if hasattr(get,'pid'): pid = get.pid #添加入SQLITE sql.add('pid,name,username,password,accept,ps,addtime',(pid,data_name,username,password,address,get['ps'],addTime)) public.WriteLog("TYPE_DATABASE", 'DATABASE_ADD_SUCCESS',(data_name,)) return public.returnMsg(True,'ADD_SUCCESS') except Exception,ex: public.WriteLog("TYPE_DATABASE",'DATABASE_ADD_ERR', (data_name,str(ex))) return public.returnMsg(False,'ADD_ERROR')