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