如何一键导出MySQL数据库

迷途d书童 发布于 2012/04/21 16:03
阅读 9K+
收藏 81

有时候,你的用户要求添加一个选项,导出整个数据库到一个SQL文件。虽然phpMyAdmin,以及Navicat有这个功能,但你的用户想要更简单点怎么办?

以下是如何一键导出MySQL数据库的php代码。

新建一个名为backup.php的文件,复制粘贴以下代码,然后编辑数据库连接设置和mysqldump的路径。有必要的话,你还可以添加一个backup.php超链接到你的程序里:

<A href="backup.php">导出整个数据库</A>

请注意,第一个php代码执行的时候,会导出zip压缩后的sql文件,所以此代码所在文件夹需要可写的权限。
如果你没有写的权限,请使用第二个php代码,缺点是导出的sql文件不会被zip压缩。

此代码需要可写权限:

<?php 
  
$username = "root";  
$password = "";  
$hostname = "localhost";  
$dbname   = "cars"; 
  
// if mysqldump is on the system path you do not need to specify the full path 
// simply use "mysqldump --add-drop-table ..." in this case 
$dumpfname = $dbname . "_" . date("Y-m-d_H-i-s").".sql"; 
$command = "C:\\xampp\\mysql\\bin\\mysqldump --add-drop-table --host=$hostname 
    --user=$username "; 
if ($password)  
        $command.= "--password=". $password ." ";  
$command.= $dbname; 
$command.= " > " . $dumpfname; 
system($command); 
  
// zip the dump file 
$zipfname = $dbname . "_" . date("Y-m-d_H-i-s").".zip"; 
$zip = new ZipArchive(); 
if($zip->open($zipfname,ZIPARCHIVE::CREATE))  
{ 
   $zip->addFile($dumpfname,$dumpfname); 
   $zip->close(); 
} 
  
// read zip file and send it to standard output 
if (file_exists($zipfname)) { 
    header('Content-Description: File Transfer'); 
    header('Content-Type: application/octet-stream'); 
    header('Content-Disposition: attachment; filename='.basename($zipfname)); 
    flush(); 
    readfile($zipfname); 
    exit; 
} 
?> 

此代码不需要可写权限:

<?php 
ob_start(); 
  
$username = "root";  
$password = "";  
$hostname = "localhost";  
$dbname   = "cars"; 
  
// if mysqldump is on the system path you do not need to specify the full path 
// simply use "mysqldump --add-drop-table ..." in this case 
$command = "C:\\xampp\\mysql\\bin\\mysqldump --add-drop-table --host=$hostname 
    --user=$username "; 
if ($password)  
        $command.= "--password=". $password ." ";  
$command.= $dbname; 
system($command); 
  
$dump = ob_get_contents();  
ob_end_clean(); 
  
// send dump file to the output 
header('Content-Description: File Transfer'); 
header('Content-Type: application/octet-stream'); 
header('Content-Disposition: attachment; filename='.basename($dbname . "_" .  
    date("Y-m-d_H-i-s").".sql")); 
flush(); 
echo $dump; 
exit();]]> 
?>

原文链接OSChina.NET 编译

加载中
0
用户已屏蔽
用户已屏蔽
怕是得 循环备份库了
0
BossKiller
BossKiller
写个批处理干脆。
0
小小胖
小小胖

批处理干脆多了,这是我的每日批处理

set "Ymd=%date:~,4%-%date:~5,2%-%date:~8,2%"
set "cmd=D:\Apache\MySQL\bin\mysqldump.exe --opt -Q -h127.0.0.1 -uroot -p12356 --default-character-set=utf8"
set "path=D:\Apache\backup\local\%Ymd%"
md "%path%"

%cmd% article> "%path%\article"
%cmd% product > "%path%\product"
%cmd% deal > "%path%\deal"
%cmd% core > "%path%\core"
%cmd% basic > "%path%\basic"
%cmd% provider > "%path%\provider"
%cmd% travel > "%path%\travel"
%cmd% team > "%path%\team"

del "F:\data\%Ymd%.7z"
del "F:\data\%Ymd%res.7z"
del "F:\data\%Ymd%project.7z"
del "F:\data\%Ymd%document.7z"

"D:\Program Files\7-Zip\7z.exe" a -t7z "%path%.7z" "%path%\*"
"D:\Program Files\7-Zip\7z.exe" a -t7z "%path%res.7z" "D:\Projects\Jabinfo\Web\res\*"
"D:\Program Files\7-Zip\7z.exe" a -t7z "%path%project.7z" "D:\Projects\Jabinfo\Project\*"
"D:\Program Files\7-Zip\7z.exe" a -t7z "%path%document.7z" "D:\Projects\Jabinfo\document\*"

淘醒迷乐
淘醒迷乐
你这是备份数据库的么怎么只备份出一些没有用的文件
0
我给你传答案
我给你传答案
楼上的很清爽啊
0
mark35
mark35
用脚本来跑比较可靠些,并且占用资源也小
0
fengyqf
fengyqf
set d=%date:~0,10%
set d=%d:-=%
set t=%time:~0,8%
set t=%t::=%
set dzxpath=dzx%p%%d%%t%.sql
set ucpath=uc%p%%d%%t%.sql
set folder=E:\dataBackup\MySQL\
echo off"D:\Program Files\MySQL\MySQL Server 5.0\bin\mysqldump.exe" -hlocalhost --opt -uroot -pyoumysqlrootpassword discuzx > "%folder%%dzxpath%" "C:\Program Files\WinRAR\RAR.exe" a -ep1 -r -o+ -m5 -s -df "%folder%%dzxpath%".rar "%folder%%dzxpath%" "D:\Program Files\MySQL\MySQL Server 5.0\bin\mysqldump.exe" -hlocalhost --opt -uroot -pyoumysqlrootpassword discuzuc uc_admins uc_applications uc_badwords uc_domains uc_failedlogins uc_feeds uc_friends uc_mailqueue uc_memberfields uc_members uc_mergemembers uc_newpm uc_notelist uc_pms uc_protectedmembers uc_settings uc_sqlcache uc_tags uc_vars > "%folder%%ucpath%" "C:\Program Files\WinRAR\RAR.exe" a -ep1 -r -o+ -m5 -s -df "%folder%%ucpath%".rar "%folder%%dzxpath%" rem echo "D:\Program Files\MySQL\MySQL Server 5.0\data\discuzx\%filepath%" "D:\Program Files\MySQL\MySQL Server 5.0\bin\mysqldump.exe" -hlocalhost --opt -e --max_allowed_packet=1048576 --net_buffer_length=16384 -uroot -pyourrootpasswd discuzx > "%folder%%dzxpath%"
rem "C:\Program Files\WinRAR\RAR.exe" a -ep1 -r -o+ -m5 -s -df "%folder%%dzxpath%".rar "%folder%%dzxpath%" 

"D:\Program Files\MySQL\MySQL Server 5.0\bin\mysqldump.exe" -hlocalhost --opt -e --max_allowed_packet=1048576 --net_buffer_length=16384 -uroot -pyourrootpassw mydb > "%folder%%mydbpath%"

"C:\Program Files\WinRAR\RAR.exe" a -ep1 -r -o+ -m5 -s -df "%folder%%mydbpath%".rar "%folder%%mydbpath%"  "%folder%%dzxpath%"
rem echo "D:\Program Files\MySQL\MySQL Server 5.0\data\discuzx\%filepath%"
rem pause

完整代码参看这里 http://blog.path8.net/archives/4049.html

这也是windows下的备份方案,linux下类似,使用mysqldump xxxx |gzip -c >bakup.sql.gz 这样的形式更好。

正如楼上所说,shell脚本备份更好,效率高,不易出错。而使用php执行,可靠性低了点,尤其数据库比较大时,比如1G的数据库,使用php备份,多半是要超时而失败的。

另外,php的一些函数一般来说最好禁用,不然容易造成安全隐患,如system(), exec()等

0
firmy
firmy

一直在linux下用mysql,直接crontab+shell,表示很简单 

mysqldump -uroot -ppass DB [TABLE] > backup/mydb.sql

0
雾渺
雾渺

引用来自“小胖-KooTeam.com”的答案

批处理干脆多了,这是我的每日批处理

set "Ymd=%date:~,4%-%date:~5,2%-%date:~8,2%"
set "cmd=D:\Apache\MySQL\bin\mysqldump.exe --opt -Q -h127.0.0.1 -uroot -p12356 --default-character-set=utf8"
set "path=D:\Apache\backup\local\%Ymd%"
md "%path%"

%cmd% article> "%path%\article"
%cmd% product > "%path%\product"
%cmd% deal > "%path%\deal"
%cmd% core > "%path%\core"
%cmd% basic > "%path%\basic"
%cmd% provider > "%path%\provider"
%cmd% travel > "%path%\travel"
%cmd% team > "%path%\team"

del "F:\data\%Ymd%.7z"
del "F:\data\%Ymd%res.7z"
del "F:\data\%Ymd%project.7z"
del "F:\data\%Ymd%document.7z"

"D:\Program Files\7-Zip\7z.exe" a -t7z "%path%.7z" "%path%\*"
"D:\Program Files\7-Zip\7z.exe" a -t7z "%path%res.7z" "D:\Projects\Jabinfo\Web\res\*"
"D:\Program Files\7-Zip\7z.exe" a -t7z "%path%project.7z" "D:\Projects\Jabinfo\Project\*"
"D:\Program Files\7-Zip\7z.exe" a -t7z "%path%document.7z" "D:\Projects\Jabinfo\document\*"

这个不错,值得借鉴。
0
晨小龙
晨小龙

每日的增量备份,怎么解决。

用程序

返回顶部
顶部