def GetSpeed(self,get):
return public.getSpeed();
def SetSlave(self, get):
if not self.GetPort(get):
return public.returnMsg(False, '请确定数据库是否已经开启')
if not self.CheckBinLog():
return public.returnMsg(False, '请先开启Mysql二进制日志')
sconf = public.readFile(self.__mfile)
# 备份需要同步的数据库
masterinfo = self.BackUpMasterDbs(get)
try:
bkstatus = masterinfo["msg"]
except:
bkstatus = False
if bkstatus:
return bkstatus
__dbpass = public.M('config').where('id=?',
(1, )).getField('mysql_root')
slave_version = masterinfo["slave_version"]
create_replicate_sql = ""
# Mysql5.5版本
if "5.5" in slave_version:
create_replicate_sql += "CHANGE MASTER TO MASTER_HOST='%s',MASTER_PORT=%s,MASTER_USER='******',MASTER_PASSWORD='******',MASTER_LOG_FILE='%s',MASTER_LOG_POS=%s" % (
masterinfo["master_ip"], masterinfo["master_port"],
masterinfo["slave_user"], masterinfo["slave_pass"],
masterinfo["logfile"], masterinfo["logpos"])
# Mysql5.6+版本
addconf = """
log-slave-updates=true
enforce-gtid-consistency=true
gtid-mode=on"""
if "5.5" not in slave_version and not re.match("10", slave_version):
if not re.search("gtid-mode=on", sconf):
sconf = re.sub("\[mysqld\]", "[mysqld]" + addconf, sconf)
create_replicate_sql += "CHANGE MASTER TO MASTER_HOST='%s',MASTER_PORT=%s,MASTER_USER='******',MASTER_PASSWORD='******',MASTER_AUTO_POSITION = 1" % (
masterinfo["master_ip"], masterinfo["master_port"],
masterinfo["slave_user"], masterinfo["slave_pass"])
# 构造要同步的数据库配置
replicate_dbs = ""
if masterinfo["replicate_dbs"][0] != "alldatabases":
for d in masterinfo["replicate_dbs"]:
replicate_dbs += "\nreplicate-wild-do-table = %s.%s" % (d, "%")
else:
sconf = re.sub("replicate-wild-do-table\s*=\s*[\w\%\.\_\-]+", "",
sconf)
print(replicate_dbs)
try:
serverid = masterinfo["slave_id"]
except:
serverid = [int(masterinfo["master_id"]) + 1]
localip = public.ExecShell("ip a")[0]
netip = public.readFile("%s/data/iplist.txt")
index = 0
try:
for i in masterinfo["slave_ip"]:
if i in localip or i in netip:
index += masterinfo["slave_ip"].index("i")
break
if not index:
return public.returnMsg(False, '主库没有设置该主机为从服务器，请先设置主服务器后再配置从库')
except:
pass
serverid = serverid[index]
if not re.search("replicate-wild-do-table", sconf):
sconf = re.sub("server-id\s*=\s*\d+",
"server-id = %s%s" % (serverid, replicate_dbs),
sconf)
public.writeFile(self.__mfile, sconf)
# 导入主库数据库
try:
pid_old = public.ExecShell(
"ps aux|grep 'mysql.sock'|awk 'NR==1 {print $2}'")[0].split(
"\n")[0]
self.WriteLog("旧PID %s" % pid_old)
pid_new = pid_old
public.writeFile("/tmp/mysqlpid", "")
for i in range(10):
if i == 1:
os.system("/etc/init.d/mysqld restart &")
time.sleep(10)
pid_new = public.ExecShell(
"ps aux|grep 'mysql.sock'|awk 'NR==1 {print $2}'"
)[0].split("\n")[0]
self.WriteLog("新PID %s" % pid_new)
if pid_old == pid_new:
time.sleep(10)
pid_new = public.ExecShell(
"ps aux|grep 'mysql.sock'|awk 'NR==1 {print $2}'"
)[0].split("\n")[0]
else:
public.writeFile("/tmp/mysqlpid", "ok")
break
pm.panelMysql().execute("stop slave")
pm.panelMysql().execute("reset master")
pm.panelMysql().execute("reset slave all")
self.WriteLog("开始导入数据库")
speed = public.getSpeed()
public.writeSpeed("导入数据库", 1, speed["total"])
error = public.ExecShell(
"nohup /usr/bin/mysql -uroot -p%s < %s &" %
(__dbpass, masterinfo["backsqlpath"]))
self.WriteLog(str(error))
except Exception as e:
self.WriteLog("导入数据库失败 %s" % e)
return public.ReturnMsg(False, "导入失败")
threading.Thread(target=self.SetSpeed()).start()
self.WriteLog("导入数据库完成")
self.WriteLog("删除备份的数据库文件")
os.system("rm -f %s" % masterinfo["backsqlpath"])
os.system("rm -f /tmp/mysqlpid")
self.WriteLog("重启mysql")
pid_old = public.ExecShell(
"ps aux|grep 'mysql.sock'|awk 'NR==1 {print $2}'")[0].split(
"\n")[0]
self.WriteLog("旧PID %s" % pid_old)
pid_new = ""
public.writeFile("/tmp/mysqlpid", "")
restart = 0
for i in range(10):
if i == 1:
os.system("/etc/init.d/mysqld restart &")
time.sleep(10)
pid_new = public.ExecShell(
"ps aux|grep 'mysql.sock'|awk 'NR==1 {print $2}'"
)[0].split("\n")[0]
self.WriteLog("新PID %s" % pid_new)
if pid_old == pid_new:
time.sleep(10)
pid_new = public.ExecShell(
"ps aux|grep 'mysql.sock'|awk 'NR==1 {print $2}'"
)[0].split("\n")[0]
else:
public.writeFile("/tmp/mysqlpid", "ok")
restart += 1
break
if restart == 0:
return public.ReturnMsg(False, "导入数据后重启失败")
public.writeSpeed("重启数据库", int(1), int(2))
threading.Thread(target=self.SetSpeed()).start()
self.WriteLog("mysql重启完成")
# 写入同步的数据库到面板数据库
for i in masterinfo["replicate_dbs_info"]:
if not i:
continue
localdb = public.M('databases').where('name=?', (i[2], )).select()
if not localdb:
public.M('databases').add(("name,username,password,accept,ps"),
(i[2], i[3], i[4], i[5], i[6]))
# 完整复制将主root密码写入到从的面板
if masterinfo["replicate_dbs"][0] == "alldatabases":
self.WriteLog("因为是完整同步，修改从库面板密码为主库")
public.M('config').where('id=?',
(1, )).setField('mysql_root',
masterinfo["slave_ips"])
result = str(pm.panelMysql().query("select version()")[0])
self.WriteLog(result)
if result == "1045":
public.M('config').where('id=?', (1, )).setField(
'mysql_root', __dbpass)
# Mairadb10.*版本
if re.match("10", slave_version):
set_slave_pos_sql = "SET GLOBAL gtid_slave_pos='%s'" % masterinfo[
"gtid"]
# 需要在数据重启后配置
pm.panelMysql().query(set_slave_pos_sql)
create_replicate_sql += "CHANGE MASTER TO MASTER_HOST='%s',MASTER_PORT=%s,MASTER_USER='******',MASTER_PASSWORD='******',master_use_gtid=slave_pos" % (
masterinfo["master_ip"], masterinfo["master_port"],
masterinfo["slave_user"], masterinfo["slave_pass"])
self.WriteLog("停止从服务")
pm.panelMysql().query("stop slave")
self.WriteLog("修改从服务器的主服务器信息")
pm.panelMysql().query(create_replicate_sql)
self.WriteLog("启动从服务")
pm.panelMysql().query("start slave")
time.sleep(2)
self.WriteLog("获取从状态")
slavestatus = pm.panelMysql().query("show slave status")[0]
self.WriteLog(str(slavestatus))
self.WriteLog("创建Slave监控用户")
create_status_user = "******" % (
"user" + masterinfo["slave_user"], masterinfo["master_ip"],
"pass" + masterinfo["slave_pass"])
grant_status_user = "******" % (
"user" + masterinfo["slave_user"], masterinfo["master_ip"])
pm.panelMysql().execute(create_status_user)
pm.panelMysql().execute(grant_status_user)
n = 0
try:
for i in slavestatus:
if i == "Yes":
n += 1
except:
return public.returnMsg(False, '获取主从状态失败')
if n == 2:
print("设置成功")
self.WriteLog("删除btmysql用户")
self.__ExceSql('delete from mysql.user where user="******"',
masterinfo=masterinfo)
if masterinfo["replicate_dbs"][0] != "alldatabases":
self.WriteLog("删除从btmysql用户")
pm.panelMysql().execute(
"delete from mysql.user where user='******'")
self.WriteLog("设置成功")
os.system("rm -f %s" % self.totalspeedfile)
return public.returnMsg(True, '设置成功')
else:
self.WriteLog("设置失败")
os.system("rm -f %s" % self.totalspeedfile)
return public.returnMsg(True, '设置失败')
def GetSpeed(self,get):
return public.getSpeed();
def SetSpeed(self):
data = public.getSpeed()
bakpath = "/www/backup/database/masterslave.sql"
if data["title"] == "导入数据库":
self.SetTotalSpeed({"total": "80"})
conffile = "%s/plugin/masterslave/data.json" % self.setpath
masterinfo = json.loads(public.readFile(conffile))
public.writeSpeed(data["title"], 1, data["total"])
while True:
data = public.getSpeed()
time.sleep(1)
if public.ExecShell("ps aux|grep 'mysql -uroot -p'|wc -l"
)[0].split("\n")[0] == "2":
public.writeSpeed(data["title"], data["total"],
data["total"])
break
if data["used"] < data["total"]:
if masterinfo["replicate_dbs"][0] == "alldatabases":
used = float(pm.panelMysql().query(
"select concat(round(sum(data_length/1024/1024),2),'MB') as data from information_schema.tables"
)[0][0].split("M")[0])
else:
used = 0
try:
for i in masterinfo["replicate_dbs"]:
used += float(pm.panelMysql().query(
"select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='%s'"
% i)[0][0].split("M")[0])
except:
used = data["total"]
public.writeSpeed(data["title"], int(used), data["total"])
else:
public.writeSpeed(data["title"], data["total"],
data["total"])
break
if data["title"] == "备份数据库":
self.SetTotalSpeed({"total": "30"})
while True:
data = public.getSpeed()
time.sleep(1)
if public.ExecShell("ps aux|grep 'mysqldump -h'|wc -l"
)[0].split("\n")[0] == "2":
public.writeSpeed(data["title"], data["total"],
data["total"])
break
if data["used"] < data["total"]:
# used = int(public.ExecShell("du -sm |awk '{print $1}'" % bakpath)[0].split("M")[0])
used = int(
public.ExecShell("du -sm %s|awk '{print $1}'" %
bakpath)[0].split("\n")[0])
if used > data["total"]:
used = data["total"]
public.writeSpeed(data["title"], int(used), data["total"])
else:
public.writeSpeed(data["title"], data["total"],
data["total"])
if data["title"] == "重启数据库":
self.SetTotalSpeed({"total": "99"})
n = 0
while True:
data = public.getSpeed()
time.sleep(1)
if n == 100:
break
if public.readFile("/tmp/mysqlpid"):
public.writeSpeed(data["title"], data["total"],
data["total"])
self.SetTotalSpeed({"total": "100"})
os.system("rm -f /tmp/mysqlpid")
break
n += 1
def getSpeedApi(self):
data = public.getSpeed()
return public.returnJson(True, '已清空回收站!', data)
