大多网站后台都提供了Mysql备份功能,这里以ThinkPHP为例添加后台SQL在线备份功能
可以显示 表名 记录数 引擎类型 编码 操作(可以优化表,修复表,看表结构,备份表)
HTML文件也一并提供,有效果图
创建一个类 MysqlController.class.php
内容1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179<?php
namespace Admin\Controller;
/**
* [Mysql 数据库备份控制器]
* @Author XiaoFeng
* @Blog www.xfphp.cn
* @DateTime 2015-12-30T09:35:26+0800
* @方法个数 6个(已完成)
*/
class MysqlController extends AdminController{
public function index()
{
$dbName=C('DB_NAME');
$re=M()->query('SHOW TABLE STATUS FROM '.$dbName);
$this->assign("re",$re);
$this->display();
}
public function back()
{
//dump($_POST);exit;
$dbName=C('DB_NAME');
if(empty($_POST['tablearr']))
{
$table=$this->getTable();
}else
{
$table=explode(",",$_POST['tablearr']);
}
$struct=$this->bakStruct($table);
$record=$this->bakRecord($table);
$sqls=$struct.$record;
//这里是备份文件保存的位置,默认为网站项目根目录,自动创建Data
$dir=$_SERVER['DOCUMENT_ROOT'].__ROOT__.'/Data/';
is_dir($dir) or mkdir($dir,0777);
$dir=$_SERVER['DOCUMENT_ROOT'].__ROOT__.'/Data/'.$dbName.date("Y-m-d").'-'.rand(1,99).".sql";
file_put_contents($dir,$sqls);
if(file_exists($dir))
{
$this->success("备份成功");
}else
{
$this->error("备份失败");
}
}
protected function getTable()
{
$dbName=C('DB_NAME');
$result=M()->query('show tables from '.$dbName);
foreach ($result as $v){
$tbArray[]=$v['Tables_in_'.C('DB_NAME')];
}
return $tbArray;
}
protected function bakStruct($array)
{
foreach ($array as $v){
$tbName=$v;
$result=M()->query('show columns from '.$tbName);
$sql.="--\r\n";
$sql.="-- 数据表结构: `$tbName`\r\n";
$sql.="--\r\n\r\n";
$sql.="DROP TABLE IF EXISTS `$tbName`;\r\n";
$sql.="create table `$tbName` (\r\n";
$rsCount=count($result);
foreach ($result as $k=>$v){
$field = $v['field'];
$type = $v['type'];
$default= $v['default'];
$extra = $v['extra'];
$null = $v['null'];
if(!($default=='')){
$default='default '.$default;
}
if($null=='NO'){
$null='not null';
}else{
$null="null";
}
if($v['Key']=='PRI'){
$key = 'primary key';
}else{
$key = '';
}
if($k<($rsCount-1)){
$sql.="`$field` $type $null $default $key $extra ,\r\n";
}else{
//最后一条不需要","号
$sql.="`$field` $type $null $default $key $extra \r\n";
}
}
$sql.=") ENGINE=MyISAM DEFAULT CHARSET=utf8;\r\n\r\n";
}
return str_replace(',)',')',$sql);
}
protected function bakRecord($array)
{
foreach ($array as $v){
$tbName=$v;
$rs=M()->query('select * from '.$tbName);
if(count($rs)<=0){
continue;
}
$sql.="--\r\n";
$sql.="-- 数据表中的数据: `$tbName`\r\n";
$sql.="--\r\n\r\n";
foreach ($rs as $k=>$v){
$sql.="INSERT INTO `$tbName` VALUES (";
foreach ($v as $key=>$value){
if($value==''){
$value='null';
}
$type=gettype($value);
if($type=='string'){
$value="'".addslashes($value)."'";
}
$sql.="$value," ;
}
$sql.=");\r\n\r\n";
}
}
return str_replace(',)',')',$sql);
}
public function click()
{
$url=explode("&",$_GET['zhi']);
$do=$url[0];
$table=$url[1];
switch($do)
{
case optimize://优化
$rs =M()->Query("OPTIMIZE TABLE `$table` ");
if($rs)
{
echo "执行优化表： $table OK！";
}
else
{
echo "执行优化表： $table 失败，原因是：".M()->GetError();
}
break;
case repair://修复
$rs = M()->Query("REPAIR TABLE `$table` ");
if($rs)
{
echo "修复表： $table OK！";
}
else
{
echo "修复表： $table 失败，原因是：".M()->GetError();
}
break;
default://结构
$dsql=M()->Query("SHOW CREATE TABLE ".$table);
foreach($dsql as $k=>$v)
{
foreach($v as $k1=>$v1)
{
$rs=$v1;
}
}
echo trim($rs);
}
}
}
HTML文件内容,位置
E:\www\project\Application\Admin\View\Mysql\index.html
下面代码是我项目里的源码,继承了后台模版,你要是用注意自行修改1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160<extend name="Layout/index" />
<block name="style">
</block>
<block name="breadcrumb" >
<li>
<i class="icon-home home-icon"></i>
<a href="#">首页</a>
</li>
<li class="active">系统配置</li>
</block>
<block name="page_header">
<h1 onselectstart="return false";>
数据库表
<small>
<i class="icon-double-angle-right"></i>
数据库备份
</small>
</h1>
</block>
<block name="content_main">
<script language="javascript">
function LoadUrl(surl){
$.get('__URL__/click',{'zhi':surl},function(data){
alert(data);
});
}
function HideObj(objname){
var obj = document.getElementByIdx_x(objname);
obj.style.display = "none";
}
//获得选中文件的数据表
function getCheckboxItem(){
var myform = document.form1;
var allSel="";
if(myform.tables.value) return myform.tables.value;
for(i=0;i<myform.tables.length;i++)
{
if(myform.tables[i].checked){
if(allSel=="")
allSel=myform.tables[i].value;
else
allSel=allSel+","+myform.tables[i].value;
}
}
return allSel;
}
//反选
function ReSel(){
var myform = document.form1;
for(i=0;i<myform.tables.length;i++){
if(myform.tables[i].checked) myform.tables[i].checked = false;
else myform.tables[i].checked = true;
}
}
//全选
function SelAll(){
var myform = document.form1;
for(i=0;i<myform.tables.length;i++){
myform.tables[i].checked = true;
}
}
//取消
function NoneSel(){
var myform = document.form1;
for(i=0;i<myform.tables.length;i++){
myform.tables[i].checked = false;
}
}
function checkSubmit()
{
var myform = document.form1;
myform.tablearr.value = getCheckboxItem();
return true;
}
</script>
</head>
<body leftmargin='8' topmargin='8'>
<table width="99%" border="0" cellpadding="3" cellspacing="1" bgcolor="#D6D6D6" class="table table-hover">
<!-- <tr>
<td>
<table width="96%" border="0" cellspacing="1" cellpadding="1" >
<tr>
<td width="24%" style="padding-left:10px;text-align: left;" ><strong>数据库管理</strong></td>
<td width="76%" align="right">
</td>
</tr>
</table>
</td>
</tr> -->
<form name="form1" onSubmit="checkSubmit()" action="back" method="post">
<input type='hidden' name='tablearr' value='' />
<tr bgcolor="#F7F8ED">
<td height="24" colspan="8" style="text-align: left;"><strong>默认系统表：</strong></td>
</tr>
<tr bgcolor="#FBFCE2" align="center">
<td height="24" width="5%">选择</td>
<td width="20%">表名</td>
<td width="8%">记录数</td>
<td width="8%">引擎类型</td>
<td width="8%">编码</td>
<td width="17%">操作</td>
</tr>
<volist name="re" id="vo">
<tr bgcolor="#FFFFFF" align="center">
<td height="24" width="5%">
<input type="checkbox" name="tables" value="<{$vo.name}>" class="np" style="width: 16px;height: 16px;vertical-align: -3px;" />
</td>
<td width="20%"><{$vo.name}></td>
<td width="8%"><{$vo.rows}></td>
<td width="8%"><{$vo.engine}></td>
<td width="8%"><{$vo.collation}></td>
<td>
<a href="#" onClick="LoadUrl('optimize&<{$vo.name}>');">优化</a> |
<a href="#" onClick="LoadUrl('repair&<{$vo.name}>');">修复</a> |
<a href="#" onClick="LoadUrl('viewinfo&<{$vo.name}>');">结构</a>
</td>
</tr>
</volist>
<tr bgcolor="#ffffff">
<td height="24" colspan="8">
<input name="b1" type="button" id="b1" class="coolbg np" onClick="SelAll()" value="全选" />
<input name="b2" type="button" id="b2" class="coolbg np" onClick="ReSel()" value="反选" />
<input name="b3" type="button" id="b3" class="coolbg np" onClick="NoneSel()" value="取消" />
<input type="Submit" name="Submit" value="提交" class="coolbg np" id="tijiao" />
</td>
</tr>
</form>
<script>
//提交验证
tijiao.onclick=function(){
//alert(getCheckboxItem());
if(getCheckboxItem()==''){
$(this).attr('type','button');
alert('请选择要备份的表!!!');
}
}
</script>
</table>
</block>
效果图
本文出自 “小风博客” BY:小风 如果你喜欢,转载请务必保留此出处^_^
小风博客 http://www.xfphp.cn 小风网络 http://www.hotxf.com