直接上效果图
提交之后可以看到
点击SQL详情
点击处理详情记录了inception审核的内容，然后可以点击手动，执行，打回功能，回滚暂时没写
结果如上都已经很清楚了，那么下面我们看实现过程，因为这是个人自己玩的项目，并没有上线，所以就本着简单的原则设计了下
django涉及的models几张表如下
from __future__ import unicode_literals
from django.db import models
from utils.basemodels import BaseModel
from crm.models import User
class Instance(models.Model):
ENVS=(('dev','开发'),('test','测试'),('prod','生产'))
name=models.CharField(max_length=100,verbose_name='实例名称')
host=models.CharField(max_length=100,verbose_name='实例对应的IP地址')
port=models.IntegerField(verbose_name='实例端口')
create_user=models.ForeignKey(User,verbose_name='实例创建用户',on_delete=models.SET_NULL,blank=True,null=True)
dbrole=models.CharField(max_length=100,choices=((1,'master'),(0,'slave')),verbose_name='主从角色关系')
cluster=models.IntegerField(choices=((0,'单实例'),(1,'集群')),verbose_name='是否为集群0:否 1:是')
env=models.CharField(max_length=100,choices=ENVS,verbose_name='实例环境')
createtime = models.DateTimeField(auto_now_add=True, verbose_name='创建时间')
updatetime = models.DateTimeField(auto_now=True, verbose_name='修改时间')
remark = models.TextField(default='', null=True, blank=True, verbose_name='备注')
def __str__(self):
return self.name
class Meta:
db_table='instance'
verbose_name='MySQL实例表'
verbose_name_plural=verbose_name
managed = True
permissions = (('installsinglemysql','安装单实例MySQL'),
('installmultimysql','安装多实例MySQL'),
)
class DbInfo(models.Model):
ENVS = (('dev', '开发'), ('test', '测试'), ('prod', '生产'))
name=models.CharField(max_length=100,verbose_name='实例名称')
host = models.CharField(max_length=100, verbose_name='实例对应的IP地址')
port = models.IntegerField(verbose_name='实例端口')
dbname=models.CharField(max_length=100,verbose_name='数据库名')
dbrole = models.CharField(max_length=100,choices=((1,'master'),(0,'slave')), verbose_name='主从角色关系')
env = models.CharField(max_length=100,choices=ENVS, verbose_name='实例环境')
createtime = models.DateTimeField(auto_now_add=True, verbose_name='创建时间')
def __str__(self):
return '%s-%s-%s-%s'%(self.get_env_display(),self.dbname,self.get_dbrole_display(),self.host)
class Meta:
db_table='dbinfo'
verbose_name='数据库表'
verbose_name_plural=verbose_name
def get_env(self):
return self.get_env_display()
from django.contrib.auth.models import Permission
class DbAssign(models.Model):
ENVS = (('dev', '开发'), ('test', '测试'), ('prod', '生产'))
name = models.CharField(max_length=100, verbose_name='实例名称')
host = models.CharField(max_length=100, verbose_name='实例对应的IP地址')
port = models.IntegerField(verbose_name='实例端口')
dbname = models.CharField(max_length=100,verbose_name='数据库名')
dbrole = models.CharField(max_length=100,choices=(('master', '主'), ('slave', '从')), verbose_name='主从角色关系')
env = models.CharField(max_length=100,choices=ENVS, verbose_name='实例环境')
group=models.ManyToManyField(Permission,verbose_name='可使用此数据库的组')
def __str__(self):
return self.dbname
class Meta:
db_table='dbassign'
verbose_name='数据库权限分配'
verbose_name_plural=verbose_name
class InceptSql(models.Model):
STATUS = (
(-3, u'已打回'),
(-2, u'已回滚'),
(-1, u'待执行'),
(0, u'已执行'),
(1, u'执行失败'),
(2, u'已手工执行'),
)
ENVS = (('dev', '开发'), ('test', '测试'), ('prod', '生产'))
db = models.ForeignKey(DbInfo, on_delete=models.CASCADE)
commiter = models.ForeignKey(User,on_delete=models.CASCADE,verbose_name='代码提交人',related_name='commiter')
sql_content = models.TextField(verbose_name='提交的SQL代码')
env = models.CharField(max_length=8, choices=ENVS,verbose_name='SQL应用环境')
treater = models.ForeignKey(User,null=True,blank=True,on_delete=models.CASCADE,verbose_name='代码执行人',related_name='treater')
status = models.IntegerField(default=-1, choices=STATUS)
execute_errors = models.TextField(default='', null=True, blank=True)
exe_affected_rows = models.CharField(max_length=10, null=True, blank=True)
roll_affected_rows = models.CharField(max_length=10, null=True, blank=True)
rollback_opid = models.TextField(null=True, blank=True)
rollback_db = models.CharField(max_length=100, null=True, blank=True)
inception_detail = models.TextField(default='', null=True, blank=True, verbose_name='inception详情')
createtime = models.DateTimeField(auto_now_add=True, verbose_name='创建时间')
updatetime = models.DateTimeField(auto_now=True, verbose_name='修改时间')
remark = models.TextField(default='', null=True, blank=True, verbose_name='备注')
class Meta:
db_table = 'inceptsql'
verbose_name = '数据库Inception'
verbose_name_plural = verbose_name
managed = True
permissions = (('sqlcheck', 'MySQL脚本上线审核'),
('sqlexecute', 'MySQL脚本普通执行'),
('pt-osc', 'pt-osc执行'),
('gh-ost', 'gh-ost执行'),
('sqlrollback', 'MySQL脚本回退'),
('sqlmanual', 'MySQL脚本手工执行'),)
def __str__(self):
return '%s-%s-%s'%(self.id,self.get_env_display(),self.db)
class InceptStep(models.Model):
work_order = models.ForeignKey(InceptSql, on_delete=models.CASCADE)
user = models.ForeignKey(User, null=True, blank=True, on_delete=models.CASCADE,verbose_name='工单处理人员')
createtime = models.DateTimeField(auto_now_add=True, verbose_name='创建时间')
updatetime = models.DateTimeField(auto_now=True, verbose_name='修改时间')
remark = models.TextField(default='', null=True, blank=True, verbose_name='备注')
目前dbassign这张表没有使用
数据库inception审核需要安装inception，但是由于inception需要安装很多组件，我安装了goInception，很好移植安装，安装完成之后按照配置要求启动goInception,链接
goinception
vue前端展示页面code:
在这里插入代码片<template>
<div>
<el-table :data="tableformData" style="width: 100%;text-align:center" :header-cell-style="{textAlign: 'center'}" :cell-style="{ textAlign: 'center' }" border>
<el-table-column prop="id" label="ID" width="50"></el-table-column>
<el-table-column prop="createtime" label="提交时间" :formatter="formatCreatetime" width="200"></el-table-column>
<el-table-column prop="commitername" label="提交人" width="70"></el-table-column>
<el-table-column prop="env" label="环境" width="100">
<template slot-scope="scope">
<el-tag
:type="envtype(scope.row)"
disable-transitions>{{scope.row.envdisplay}}</el-tag>
</template>
</el-table-column>
<!-- <el-table-column prop="sql_content" label="工单SQL" width="300" show-overflow-tooltip>
</el-table-column> -->
<el-table-column label="工单SQL" align="center" width="150">
<!-- <template slot-scope="scope">
<el-popover placement="top-start" title="工单SQL" width="250" trigger="hover" >
<div style="white-space: pre-line;">{{scope.row.sql_content}}</div>
<span slot="reference">{{ scope.row.sql_content.substr(0,25)+'...' }}</span>
</el-popover>
</template> -->
<template slot-scope="scope">
<el-button
size="mini"
type=""
@click="getSQLContent(scope.$index, scope.row)">SQL详情</el-button>
</template>
</el-table-column>
<el-table-column label="数据库信息" width="200" >
<template slot-scope="scope">
<el-popover placement="top-start" width="400" trigger="hover" >
<div style="white-space: pre-line;">{{scope.row.dbinfo}}</div>
<span slot="reference">{{ scope.row.dbinfo.substr(0,10)+'...' }}</span>
</el-popover>
</template>
</el-table-column>
<el-table-column prop="updatetime" label="更新时间" :formatter="formatUpdatetime" width="180"></el-table-column>
<el-table-column prop="statusdisplay" label="工单状态" width="150"></el-table-column>
<el-table-column label="流程" width="100">
<template slot-scope="scope">
<el-button
size="mini"
type=""
@click="handleFlow(scope.$index, scope.row)">流程</el-button>
</template>
</el-table-column>
<el-table-column label="操作" width="300">
<template slot-scope="scope">
<div style="display: flex;justify-content: space-around;">
<!-- <el-dropdown>
<el-button type="primary" size="mini">
执行<i class="el-icon-arrow-down el-icon--right"></i>
</el-button>
<el-dropdown-menu slot="dropdown">
<el-dropdown-item @click.native="handleExec(scope.$index, scope.row)">原生执行</el-dropdown-item>
<el-dropdown-item @click.native="handlePtOsc(scope.$index, scope.row)">PT-OSC</el-dropdown-item>
<el-dropdown-item @click.native="handleGhOst(scope.$index, scope.row)">GH-OST</el-dropdown-item>
<el-dropdown-item @click.native="handleManual(scope.$index, scope.row)">手动执行</el-dropdown-item>
</el-dropdown-menu>
</el-dropdown> -->
<el-button
size="mini"
type="success"
:disabled="disshow(scope.row)"
@click="handleManual(scope.$index, scope.row)">手动</el-button>
<el-button
size="mini"
type="primary"
:disabled="disshow(scope.row)"
@click="handleExec(scope.$index, scope.row)">执行</el-button>
<el-button
size="mini"
type="warning"
:disabled="disshow(scope.row)"
@click="handleCancel(scope.$index, scope.row)">打回</el-button>
<el-button
size="mini"
type="danger"
@click="handleRollback(scope.$index, scope.row)">回滚</el-button>
</div>
</template>
</el-table-column>
<el-table-column label="inception" >
<template slot-scope="scope">
<el-button
size="mini"
type=""
@click="ShowInceptionDetail(scope.$index, scope.row)">处理详情</el-button>
</template>
</el-table-column>
</el-table>
<el-dialog center title="工单流程" :visible.sync="WorkFlowVisible" width="50%">
<el-timeline>
<el-timeline-item
v-for="wf in workflow"
:key="wf.id"
:index="wf.id"
:timestamp="wf.createtime|fdatetime">
用户:{{wf.username}}-{{ wf.remark }}
</el-timeline-item>
</el-timeline>
</el-dialog>
<el-dialog center title="Inception详情" :visible.sync="InceptionVisible" width="50%">
<div
style="border:0px;padding:3px;width:100%;height:300px;white-space: nowrap;overflow-x:auto;overflow-y:auto;">
<strong v-for="icr in inceptionres"
:key="icr.order_id"
:index="icr.order_id">
[{{icr}}]
</strong>
</div>
</el-dialog>
<el-dialog center title="工单SQL内容" :visible.sync="sqlconentVisible" width="50%">
<div
style="border:0px;padding:3px;width:100%;height:300px;white-space:pre-line;overflow:scroll; width:100%;">
<strong>{{sqlcontent}}</strong>
</div>
</el-dialog>
<el-dialog center title="SQL回滚语句" :visible.sync="sqlRollbackVisible" width="50%">
<div
style="border:0px;padding:3px;width:100%;height:300px;white-space:pre-line;overflow:scroll; width:100%;">
<strong>
{{rollbackres}}
</strong>
</div>
</el-dialog>
<el-pagination style="text-align: center; "
@size-change="handleSizeChange"
@current-change="handleCurrentChange"
:current-page.sync="currentPage"
:page-sizes="pagesizes"
:page-size="pagesize"
layout="total,sizes, prev, pager, next"
:total=total>
</el-pagination>
</div>
</template>
<script>
import moment from 'moment';
import {InceptionCheck,InceptionExecute,InceptionRollback,InceptionManual,InceptionPtOsc,InceptionGhOst,Inception,InceptionDetail,InceptionStepDetail} from '@/api/MySQL/goInception'
export default {
name: "inception",
data() {
return {
workflow:null,
WorkFlowVisible:false,
InceptionVisible:false,
sqlconentVisible:false,
sqlRollbackVisible:false,
inceptionres:null,
rollbackres:null,
sqlcontent:null,
tableData:null,
loading:false,
total:null,
currentPage:1,
pagesize:10,
pagesizes:[5,10, 20, 30, 50,100],
formInline: {
name: '',
host: ''
},
};
},
async created(){
await this.getInceptionList()
// console.log(this)
},
filters:{
fdatetime(value){
return moment(value).format('YYYY-MM-DD HH:mm:ss')
}
},
methods: {
disshow(row){
if(row.status===2||row.status==='-3'||row.status===0){
return true
}
else{
return false
}
},
handleClick(index,row) {
console.log(row,index)
alert('button click'+JSON.stringify(row));
},
handleSizeChange(pagesize) {
this.pagesize = pagesize;
},
handleCurrentChange(currentPage) {
this.currentPage = currentPage;
},
getIndex(index) {
let currentIndex = (this.currentPage - 1) * this.pagesize + index + 1;
if (currentIndex < 10) {
return 0${index + 1};
}
return (this.currentPage - 1) * this.pagesize + index + 1;
},
formatDbrole(row){
return row.dbrole === 0 ? "从" : row.dbrole === 1 ? "主" : "未填写";
},
formatCreatetime(row){
return moment(row.createtime).format('YYYY-MM-DD HH:mm:ss')
},
formatUpdatetime(row){
return moment(row.updatetime).format('YYYY-MM-DD HH:mm:ss')
},
statustype(row){
if(row.status===0||row.status===2||row.status===3||row.status===4){
return 'success'
}
else if(row.status===-1){
return 'info'
}
else if(row.status===-2){
return 'warning'
}
else{
return 'danger'
}
},
envtype(row){
if(row.env==='dev'){
return 'dev'
}
else if(row.env==='test'){
return 'success'
}
else if(row.env==='prod'){
return 'danger'
}
},
handleFlow(index,row){
this.WorkFlowVisible=!this.WorkFlowVisible
let pdata={work_order:row.id}
InceptionStepDetail(pdata).then(res=>{
// console.log(res)
this.workflow=res
})
},
getSQLContent(index,row){
this.sqlcontent=row.sql_content
this.sqlconentVisible=!this.sqlconentVisible
},
ShowInceptionDetail(index,row){
if(row.inception_detail.length>0){
this.inceptionres=JSON.parse(row.inception_detail)
this.InceptionVisible=!this.InceptionVisible
}
else{
this.$message({
type: 'error',
message: '此工单没有inception相关信息'
});
}
},
handleExec(index, row) {
this.$prompt('请输入处理意见', '', {
inputType:'Text',
confirmButtonText: '确定',
cancelButtonText: '取消',
}).then(async({ value }) => {
this.remark=value
this.remark=this.remark!==null ?this.remark :'MySQL原生执行器执行'
let pdata={id:row.id,userid:this.$store.state.permission.userid,remark:this.remark}
await InceptionExecute(pdata).then(res=>{
console.log("res的值是:"+res.status)
if(res.status===0){
this.disexec=true
this.discanc=true
this.dismanu=true
this.$message({
type: 'success',
message: 'MySQL原生命令执行成功!'
});
}
else{
this.$message({
type: 'error',
message: 'MySQL原生命令执行失败'
});
}
}).catch(err=>{
this.$message({
type: 'error',
message: 'API接口InceptionExecute调用报错'+err
});
})
await this.getInceptionList()
}).catch(() => {
this.$message({
type: 'info',
message: '暂时放弃'
});
});
},
handleCancel(index, row) {
this.disexec=true
this.discanc=true
this.dismanu=true
console.log(index, row);
},
async handleManual(index, row) {
this.$prompt('请输入处理意见', '', {
inputType:'Text',
confirmButtonText: '确定',
cancelButtonText: '取消',
}).then(async({ value }) => {
this.remark=value
this.remark=this.remark!==null ?this.remark :'SQL语句已线下执行'
let pdata={id:row.id,userid:this.$store.state.permission.userid,remark:this.remark}
await InceptionManual(pdata).then(res=>{
if(res.status===0){
this.disexec=true
this.discanc=true
this.dismanu=true
this.$message({
type: 'success',
message: 'MySQL命令已手动执行'
});
}
else{
this.$message({
type: 'error',
message: 'MySQL手动执行失败'
});
}
}).catch(err=>{
this.$message({
type: 'error',
message: 'API接口InceptionManual调用报错'+err
});
})
await this.getInceptionList()
}).catch(() => {
this.$message({
type: 'info',
message: '暂时放弃'
});
});
}
,
handleRollback(index, row) {
let pdata={id:row.id,inception_detail:JSON.parse(row.inception_detail)}
InceptionRollback(pdata).then(res=>{
console.log(res)
this.rollbackres=res
this.sqlRollbackVisible=!this.sqlRollbackVisible
}).catch(err=>{
console.log(err)
})
},
async getInceptionList(){
let res= await Inception()
this.tableData=res
this.total=this.tableData.length
},
async onSubmit() {
// console.log('submit!',!this.formInline.host,!this.formInline.name);
let params={name:this.formInline.name,host:this.formInline.host}
await DbInfoSearch(params).then(res=>{
this.tableData=res
this.total=this.tableData.length
}).catch(err=>{
alert("报错了,报错信息为",err)
})
}
},
computed:{
tableformData(){
if(this.tableData===null){
}
else{
return this.tableData.slice(
(this.currentPage - 1) * this.pagesize,
this.currentPage * this.pagesize
);
}
},
}
};
</script>
<style>
.el-table .cell {
white-space: pre-line;
}
</style>
sqlcheck.vue：
<template>
<div>
<el-form ref="form" :model="form" label-width="80px">
<div style="display: flex;
justify-content: space-around;">
<div class="in-coder-panel" style="width:60%">
<div style="width:100%">
<h1 style="margin:20px">请输入上线要上线的SQL语句:</h1>
<el-form-item label="SQL" prop="sqlcontent" required>
<el-input type="textarea" rows="10" cols="140" v-model="form.sqlcontent"></el-input>
</el-form-item>
<el-form-item label="备注" prop="comment" >
<el-input type="textarea" rows="2" cols="140" v-model="form.comment"></el-input>
</el-form-item>
</div>
</div>
<div style="width:40%">
<div style="width:100%">
<h1 style="margin:20px">选择执行条件:</h1>
<el-form-item label="环境" prop="env">
<el-radio-group v-model="form.env">
<el-radio label="开发"></el-radio>
<el-radio label="测试"></el-radio>
<el-radio label="生产"></el-radio>
</el-radio-group>
</el-form-item>
<el-form-item label="数据库" required>
<el-select v-model="form.dbtag" placeholder="请选择数据库" >
<el-option v-for="item in form.dblist" :label="item.label" :value="item.value" :key="item.id"></el-option>
</el-select>
</el-form-item>
</div>
</div>
</div>
<el-form-item label="操作">
<el-button style="margin-left:200px" type="primary" @click="submitForm('form')">审核</el-button>
<el-button style="margin-left:200px" @click="resetForm('form')">重置</el-button>
</el-form-item>
</el-form>
<el-dialog center title="失败信息" :visible.sync="CheckFailVisible" width="50%">
<div
v-for="cfr in checkfailres"
:key="cfr.id"
:index="cfr.id">
<strong v-if="cfr.error_level!==0">
SQL语句:{{cfr.sql}}-----报错信息:{{ cfr.error_message }}
</strong>
</div>
</el-dialog>
</div>
</template>
<script type="text/ecmascript-6">
import {DbInfoEnvSearch} from '@/api/MySQL/dbinfo'
import {InceptionCheck} from '@/api/MySQL/goInception'
export default {
name: 'sql-check',
data () {
return {
checkfailres:null,
CheckFailVisible:false,
form: {
dblist: '',
comment: '',
sqlcontent: '',
env: '开发',
dbtag:''
},
}
},
mounted () {
},
watch:{
'form.env':{
async handler(newenv, oldenv) {
this.form.dbtag=''
// console.log('form.env changed',newenv);
let envsel=newenv === '开发' ? "dev" : newenv === '测试' ? "test" : "prod";
let params={env:envsel}
let res=await DbInfoEnvSearch(params)
let arraydblist=[]
let concatvalue=''
let concatlabel=''
res.forEach((i)=>{
if(i.dbrole===1){
concatlabel=i.name+'-'+i.host+'-'+i.port+'-'+i.dbname
concatvalue={host:i.host,port:i.port,db:i.id,dbname:i.dbname}
arraydblist.push({value:concatvalue,label:concatlabel,id:i.id,host:i.host,port:i.port,dbname:i.dbname})
}
})
// console.log(arraydblist)
this.form.dblist=arraydblist
},
immediate: true,
deep: true
},
},
methods: {
submitForm(form) {
this.$refs[form].validate(async (valid) => {
if (valid) {
console.log('fortag的值是:'+this.form.dbtag,this.form.dbtag.length)
if(this.form.dbtag.length===0){
this.$message({
type: 'error',
message: '请选择执行数据库!'
});
}
// alert(JSON.stringify(this.form));
// console.log(this.$store.state.permission.userid)
else{
let subform={comment:this.form.comment, sqlcontent:this.form.sqlcontent,
env: this.form.env,dbtag:this.form.dbtag,
userid:this.$store.state.permission.userid}
await InceptionCheck(subform).then(res=>{
if(res.status===0){
this.$message({
type: 'success',
message: '工单添加成功!'
});
}
else{
this.checkfailres=res.failreason
this.CheckFailVisible=!this.CheckFailVisible
console.log(this.checkfailres)
this.$message({
type: 'error',
message: '工单添加失败，请检查SQL语句!'
});
}
})
}
} else {
console.log('error submit!!');
return false;
}
});
},
resetForm(form) {
this.$refs[form].resetFields();
}
}
}
</script>
goinception.js
import axios from '@/config/httpConfig'
export function InceptionCheck(data){
return axios.post('/inception/check/',data)
}
export function InceptionExecute(data){
return axios.post('/inception/'+data.id+'/execute/',data)
}
export function InceptionRollback(data){
return axios.post('/inception/'+data.id+'/rollback/',data)
}
export function InceptionManual(data){
return axios.post('/inception/'+data.id+'/manual/',data)
}
export function Inception(){
return axios.get('/inception/')
}
export function InceptionDetail(id){
return axios.get('/inception/'+id+'/')
}
export function InceptionStepDetail(data){
return axios.post('/inceptstep/workflow/',data)
}
instance.js
import axios from '@/config/httpConfig'
export function InstanceList(){
return axios.get('/instance/')
}
export function InstanceDetail(id){
return axios.get('/instance/'+id+'/')
}
export function InstanceAdd(data){
return axios.post('/instance/',data)
}
export function InstanceDel(id){
return axios.delete('/instance/'+id+'/')
}
export function InstancePut(data){
return axios.put('/instance/',data)
}
export function installsinglemysql(data){
return axios.post('/singleinstall/',data)
}
export function installmultimysql(data){
return axios.post('/multiinstall/',data)
}
后端django实现代码：
from __future__ import unicode_literals
from django.http import JsonResponse
from django.shortcuts import render
from sqlmng.serializers import InstanceModelSerializer,DbAssignModelSerializer,DbInfoModelSerializer,InceptSqlModelSerializer,InceptStepModelSerializer
from sqlmng.models import Instance,DbInfo,DbAssign,InceptSql,InceptStep
from utils.dbtools.mysql_single_install import full_single_install
from utils.dbtools.mysql_multi_install import full_multil_install
from utils.dbtools.MySQLdb import MysqlHelper
from crm.models import User
from django.db.models.signals import post_save
from django.dispatch import receiver
import configparser
cnf = configparser.ConfigParser()
from restful_test.settings import INCEPTION_DIR
cnf.read(INCEPTION_DIR)
inception_host=cnf.get('inception','inception_host')
inception_backup_host=cnf.get('inception','inception_backup_host')
inception_backup_port=cnf.get('inception','inception_backup_port')
inception_backup_user=cnf.get('inception','inception_backup_user')
inception_backup_password=cnf.get('inception','inception_backup_password')
from django.core.exceptions import PermissionDenied
from rest_framework.response import Response
from utils.jwt_auth import parse_payload
from rest_framework.decorators import action
from rest_framework.viewsets import ModelViewSet
from rest_framework.views import APIView
import pymysql
class InstanceModelViewSet(ModelViewSet):
queryset = Instance.objects.all()
serializer_class = InstanceModelSerializer
class DbInfoModelViewSet(ModelViewSet):
queryset = DbInfo.objects.all()
serializer_class = DbInfoModelSerializer
filter_fields = ('name', 'host','env')
import json
class InceptSqlModelViewSet(ModelViewSet):
queryset =InceptSql.objects.all()
serializer_class = InceptSqlModelSerializer
@action(detail=False,methods=['POST'])
def check(self,request):
data=request.data
print("data的值是",data)
host=data['dbtag']['host']
port=data['dbtag']['port']
dbname = data['dbtag']['dbname']
db=data['dbtag']['db']
env=''
if data['env']=='开发':
env='dev'
if data['env']=='测试':
env='test'
if data['env']=='生产':
env='prod'
comment=data['comment']
userid=data['userid']
username=User.objects.get(id=userid).username
sqlcontent=data['sqlcontent']
sql = '''/*--user=dbadmin;--password=dbadmin;--host={};--checked=1;--port={};*/
inception_magic_start;
use {};
{}
inception_magic_commit;'''.format(host,port,dbname,sqlcontent)
conn = pymysql.connect(host='%s'%(inception_host), user='', passwd='',
db='', port=4000, charset="utf8mb4")
cur = conn.cursor()
cur.execute(sql)
result = cur.fetchall()
print("=========================")
res_list = [dict(zip([i[0] for i in cur.description], j)) for j in result]
print("+++++++++++++++++++++++++")
print(res_list)
errlen=len([i for i in res_list if i['error_level'] != 0])
cur.close()
conn.close()
if errlen==0:
sdata={'db':db,'commiter':userid,'sql_content':sqlcontent,
'env':env,'remark':comment,'inception_detail':json.dumps(res_list)}
serializer =InceptSqlModelSerializer(data=sdata)
res = serializer.is_valid()
if res:
serializer.save()
return Response({'status': 0})
else:
print(serializer.errors)
return Response({'status': 1})
else:
return Response({'status':1,'failreason':res_list})
@action(detail=True, methods=['POST'])
def execute(self, request,pk):
'''
检查用户是否有对inception执行的权限
'''
if request.user.is_anonymous:
token = request.META.get('HTTP_AUTHORIZATION', '')
user = parse_payload(token)
request.user = user
res = request.user.roles.values_list('permissions__permission__content_type__app_label',
'permissions__permission__codename')
perms_list = ['.'.join(i) for i in res if i[0] != None]
perms = ('sqlmng.sqlexecute',)
if set(perms) set(perms_list):
obj = self.get_object()
print(pk, obj.sql_content, obj.pk)
data = request.data
userid = data['userid']
remark = data['remark']
sql = '''/*--user=dbadmin;--password=dbadmin;--host={};--execute=1;--backup=1;--port={};*/
inception_magic_start;
use {};
{}
inception_magic_commit;'''.format(obj.db.host, obj.db.port, obj.db.dbname, obj.sql_content)
conn = pymysql.connect(host='%s'%(inception_host), user='', passwd='',
db='', port=4000, charset="utf8mb4")
cur = conn.cursor()
cur.execute(sql)
result = cur.fetchall()
print("=========================")
res_list = [dict(zip([i[0] for i in cur.description], j)) for j in result]
print("+++++++++++++++++++++++++")
print(res_list)
errlen = len([i for i in res_list if i['error_level'] != 0])
cur.close()
conn.close()
if errlen == 0:
sdata = {'work_order': obj.pk, 'user': userid, 'remark': remark}
serializer = InceptStepModelSerializer(data=sdata)
res = serializer.is_valid()
if res:
serializer.save()
obj.inception_detail = json.dumps(res_list)
obj.treater = User.objects.get(id=userid)
obj.status = 0
obj.save()
return Response({'status': 0})
else:
print(serializer.errors)
obj.treater = User.objects.get(id=userid)
obj.inception_detail = json.dumps(res_list)
obj.status = 1
obj.save()
return Response({'status': 1})
else:
return Response({'status': 1})
else:
raise PermissionDenied
@action(detail=True, methods=['POST'])
def rollback(self, request,pk):
data = request.data
print(type(data['inception_detail']))
try:
backup_list = [dict(zip(['sequence','backup_dbname'],[j['sequence'],j['backup_dbname']])) for j in data['inception_detail'] if j['backup_dbname']]
dbname=backup_list[0]['backup_dbname']
condition=[(i['sequence']) for i in backup_list]
print(condition)
mydb=MysqlHelper(inception_backup_host,inception_backup_user,inception_backup_password,dbname,inception_backup_port)
sql='''
select opid_time,tablename from $_$inception_backup_information$_$;
'''
res=mydb.get_all(sql)
res_filter=[i for i in res if i[0] in condition]
print(res_filter)
sqlset=['''
select b.rollback_statement from $_$inception_backup_information$_$ a,%s b where a.opid_time='%s' and a.opid_time=b.opid_time and a.tablename='%s';
'''%(j[1],j[0],j[1]) for j in res_filter ]
resset=[mydb.get_all(stam) for stam in sqlset]
print(len(resset))
sqlres=''
for i in resset:
for j in i:
sqlres+='\n'
sqlres+=j[0]
return Response(sqlres)
except Exception as e:
return Response("回滚语句生成失败,请检查是否进行备份,联系DBA大数据开发组")
@action(detail=True, methods=['POST'])
def manual(self, request,pk):
if request.user.is_anonymous:
token = request.META.get('HTTP_AUTHORIZATION', '')
user = parse_payload(token)
request.user = user
res = request.user.roles.values_list('permissions__permission__content_type__app_label',
'permissions__permission__codename')
perms_list = ['.'.join(i) for i in res if i[0] != None]
perms = ('sqlmng.sqlmanual',)
if set(perms) set(perms_list):
data = request.data
print(data)
obj = self.get_object()
userid = data['userid']
remark = data['remark']
sdata = {'work_order': obj.pk, 'user': userid, 'remark': remark}
serializer = InceptStepModelSerializer(data=sdata)
obj.treater = User.objects.get(id=userid)
obj.status = 2
obj.save()
res = serializer.is_valid()
if res:
serializer.save()
return Response({'status': 0})
else:
return Response({'status': 1})
else:
raise PermissionDenied
@receiver(post_save,sender=InceptSql)
def inceptionflow_handler(sender,**kwargs):
work_order=kwargs.get('instance').id
user=kwargs.get('instance').commiter.id
treater = kwargs.get('instance').treater
remark = kwargs.get('instance').remark
if treater:
sdata={'work_order':work_order,'user':treater,'remark':remark}
else:
sdata={'work_order': work_order, 'user': user, 'remark': remark}
serializer=InceptStepModelSerializer(data=sdata)
if serializer.is_valid():
serializer.save()
else:
print(serializer.errors)
class InceptStepModelViewSet(ModelViewSet):
queryset = InceptStep.objects.all()
serializer_class = InceptStepModelSerializer
@action(detail=False, methods=['POST'])
def workflow(self,request):
data=request.data
print(data)
work_order=data['work_order']
res=self.get_queryset().filter(work_order=work_order).order_by('-createtime')
ser=InceptStepModelSerializer(instance=res,many=True)
return Response(ser.data)
class DbAssignModelViewSet(ModelViewSet):
queryset = DbAssign.objects.all()
serializer_class = DbAssignModelSerializer
class SingleInstanceInstall(APIView):
permission_classes = ()
def post(self,request):
if request.user.is_anonymous:
token = request.META.get('HTTP_AUTHORIZATION', '')
user = parse_payload(token)
request.user = user
res = request.user.roles.values_list('permissions__permission__content_type__app_label',
'permissions__permission__codename')
perms_list = ['.'.join(i) for i in res if i[0] != None]
perms = ('sqlmng.installsinglemysql',)
if set(perms) set(perms_list):
res=request.data
print('res的结果结果集是:',res)
full_single_install(res['host'],res['port'])
return Response(res)
else:
raise PermissionDenied
def get(self,request):
print(request.user)
print(request.user.has_perm('sqlmng.installsinglemysql'))
perms=('sqlmng.installsinglemysql',)
res = request.user.roles.values_list('permissions__permission__content_type__app_label',
'permissions__permission__codename')
perms_list = ['.'.join(i) for i in res if i[0] != None]
if set(perms) set(perms_list):
return Response("可以调用installsinglemysql方法")
else:
raise PermissionDenied
class MultiInstanceInstall(APIView):
permission_classes = ()
def post(self,request):
if request.user.is_anonymous:
token = request.META.get('HTTP_AUTHORIZATION', '')
user = parse_payload(token)
request.user = user
res = request.user.roles.values_list('permissions__permission__content_type__app_label',
'permissions__permission__codename')
perms_list = ['.'.join(i) for i in res if i[0] != None]
perms = ('sqlmng.installmultimysql',)
if set(perms) set(perms_list):
res=request.data
print('res的结果结果集是:',res)
full_multil_install(res['master'],res['slave'],res['port'])
return Response(res)
else:
raise PermissionDenied
def get(self,request):
print(request.user)
print(request.user.has_perm('sqlmng.installmultimysql'))
perms=('sqlmng.installmultimysql',)
res = request.user.roles.values_list('permissions__permission__content_type__app_label',
'permissions__permission__codename')
perms_list = ['.'.join(i) for i in res if i[0] != None]
if set(perms) set(perms_list):
return Response("可以调用installmultimysql方法")
else:
raise PermissionDenied
基于restframework的基础上我们需要自己定义一些url，作为api接口是用，action价值体现在这，可以基于detail也可以不基于，receiver可以理解成触发器，触发某种事件的时候会自动执行
url.py
from rest_framework import routers
router=routers.DefaultRouter()
from .views import DbInfoModelViewSet,DbAssignModelViewSet,InstanceModelViewSet,SingleInstanceInstall,MultiInstanceInstall,InceptSqlModelViewSet,InceptStepModelViewSet
router.register('dbinfo',DbInfoModelViewSet)
router.register('dbassign',DbAssignModelViewSet)
router.register('instance',InstanceModelViewSet)
router.register('inception',InceptSqlModelViewSet)
router.register('inceptstep',InceptStepModelViewSet)
from django.conf.urls import url,include
urlpatterns = [
url(r'^',include(router.urls)),
url('^singleinstall',SingleInstanceInstall.as_view()),
url('^multiinstall', MultiInstanceInstall.as_view())
]
以上为这个项目实施的关键代码
- 技术无止境
Original: https://blog.csdn.net/laoli815/article/details/123134554
Author: laoli815
Title: MySQL审核神器Inception
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/736173/
转载文章受原作者版权保护。转载请注明原作者出处！