Usually you'll want to use it with download.php (see below) which accepts a
POSTed dump filename to download to the client. Substitute your values for HOST, USER, PWD and DBNAME:
<?php
$host="HOST"; // fill in these values
$usr="USER";
$pwd="PWD";
$db = "DBNAME";
ini_set( "display_errors", 1 );
$conn = mysqli_connect( $host, $usr, $pwd, $db ) or exit( mysqli_connect_error() );
mysqli_set_charset( $conn, "utf8" );
echo mysqldump( $db );
function mysqldump( $db, $qualify=FALSE, $drop=TRUE, $create=TRUE, $nl="n", $target="client" ) {
$a = dbtables( $db, TRUE );
$h = "-- " . str_repeat( "-", 77 );
// HEADER
$sql = $h . $nl . "-- dump of mysql database `$db` " . date( "Y-m-d" ) . $nl . $h. $nl . $nl .
"/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;" . $nl .
"/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;" . $nl .
"/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;" . $nl .
"/*!40101 SET NAMES utf8 */;" . $nl .
"/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;" . $nl .
"/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;" .$nl . $nl .
"USE " . idcleanup( $db ) . $nl . $nl .
$nl . $h. $nl . "-- TABLES, DATA AND VIEWS" . $nl . $h . $nl . $nl;
// SAVE VIEWS FOR LAST AS THEY DEPEND ON BASE TABLES
$viewsql = $nl . $h. $nl . "-- VIEWS" . $nl . $h . $nl . $nl;
// EACH TABLE
foreach( $a as $atbl ) {
$tbl = ( $qualify ) ? idcleanup( $db ) . "." . idcleanup( $atbl[0] ) : idcleanup( $atbl[0] );
if( $atbl[1] == "VIEW" ) {
if( $res = query( "SHOW CREATE VIEW $tbl" )) {
$row = fetchrow( $res );
if( $drop && substr( $row[1], 0, 7 ) == "CREATE " )
$row[1] = "CREATE OR REPLACE " . substr( $row[1], 7 );
$viewsql .= str_replace( "n", $nl, $row[1] );
}
$viewsql .= ";" . $nl . $nl;
continue;
}
// DROP
if( $drop ) $sql .= "DROP TABLE IF EXISTS $tbl;$nl";
// CREATE
if( $create ) {
if( $res = query( "SHOW CREATE TABLE $tbl" )) {
$row = mysqli_fetch_row( $res );
$sql .= str_replace( "n", $nl, $row[1] );
}
$sql .= ";" . $nl . $nl;
}
// DATA
$colobjs = getcolobjs( $tbl );
$results = query( "SELECT * FROM $tbl" );
while ($row = mysqli_fetch_assoc( $results )) {
$sql .= "INSERT INTO $tbl (";
$data = array();
$i = 0;
while( list( $key, $value ) = @each( $row )) {
$data['keys'][] = idcleanup( $key );
$data['values'][] = valstr( $value, $colobjs[$i] );
$i++;
}
$sql .= implode( $data['keys'], ", ") . ") VALUES ( " . implode($data['values'], ", ") . " );" . $nl;
}
$sql .= $nl;
}
// ROUTINES
if( procprivs( $db )) {
$sql .= $nl . $h . $nl . "-- STORED ROUTINES" . $nl . $h. $nl . $nl;
$results = query( "SELECT routine_name, routine_type FROM information_schema.routines WHERE routine_schema='$db'" );
while( $row = mysqli_fetch_row( $results )) {
$txt = scalar_qry_result( "SHOW CREATE {$row[1]} {$row[0]}", 2 );
if( !empty( $txt )) $sql .= routinecode( $drop, $row[1], $row[0], $txt, $nl );
}
}
// TRIGGERS
if( $results = query( "SHOW TRIGGERS" )) {
$sql .= $nl . $h . $nl . "-- TRIGGERS" . $nl . $h. $nl . $nl;
while( $row = mysqli_fetch_row( $results )) {
$txt = scalar_qry_result( "SHOW CREATE TRIGGER {$row[0]}", 2 );
$sql .= routinecode( $drop, "TRIGGER", $row[0], $txt, $nl );
}
}
// ADD VIEWS
$sql .= $viewsql;
// FOOTER
$sql .= "$nl/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;$nl" .
"/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;$nl" .
"/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;$nl" .
"/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;$nl" .
"/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;$nl";
$ret = 0;
switch( $target ) {
case "client":
try {
$fn = $db . "_" . date( "YmdHis" ) . ".sql";
$fd = fopen($fn, 'w');
fwrite( $fd, $sql );
fclose( $fd );
// AUTOSUBMIT FILENAME TO DOWNLOAD SCRIPT download.php
echo "<html><body><FORM name='dumpdl' ACTION='download.php' METHOD='post'>n",
"<input type='hidden' name='path' value=''>n",
"<input type='hidden' name='filename' value='$fn'>n",
"</FORM>n<script>dumpdl.submit();</script></body</html>";
$ret = 1;
}
catch( Exception $e ) {
err_handler( $e );
}
break;
default : echo $sql; break;
}
return $ret;
}
function idcleanup( $idref ) {
$q = "`";
$parts = explode( ".", $idref );
$n = count( $parts );
for( $i=0; $i<$n; $i++ ) {
if( strpos( $parts[$i], $q ) === FALSE )
$parts[$i] = $q . $parts[$i] . $q;
}
return implode( ".", $parts );
}
function dbtables( $db, $bothcols=FALSE, $baseonly=FALSE ) {
$a = array();
$qry = "SHOW FULL TABLES FROM " . idcleanup( $db );
if( $baseonly ) $qry .= " WHERE table_type='BASE TABLE'";
$res = query( $qry, "Cannot retrieve $db table list" );
while( $row = mysqli_fetch_row( $res )) {
$a[] = ( $bothcols ) ? $row : $row[0];
}
return $a;
}
function getcolobjs( $tbl, $cols="*" ) {
$ret = array();
if( is_array( $cols )) $cols = implode( ",", $cols );
$res = query( "SELECT $cols FROM $tbl WHERE 0" );
$n = mysqli_num_fields( $res );
for( $i=0; $i < $n; $i++ ) $ret[] = mysqli_fetch_field( $res );
return $ret;
}
function valstr( $v, $colobj ) {
$q = stristr( "datetime|timestamp", $colobj->type ) || !is_numeric_type( $colobj );
if( $q ) $v = addslashes( $v );
if( empty( $v )) {
if( nullable( $colobj )) $v = "NULL";
else $v = ( $q ? "''" : "0" );
}
elseif( $q ) $v = "'" . $v . "'";
return $v;
}
function is_numeric_type( $colobj ) {
return isset( $colobj ) && array_search( $colobj->type, array(0,1,2,3,4,5,8,9,16,246) ) !== FALSE;
}
function nullable( $colobj ) {
return !column_flag( $colobj->flags, 1 );
}
function column_flag( $f, $which ) {
/*
NOT_NULL_FLAG = 1
PRI_KEY_FLAG = 2
UNIQUE_KEY_FLAG = 4
BLOB_FLAG = 16
UNSIGNED_FLAG = 32
ZEROFILL_FLAG = 64
BINARY_FLAG = 128
ENUM_FLAG = 256
AUTO_INCREMENT_FLAG = 512
TIMESTAMP_FLAG = 1024
SET_FLAG = 2048
NUM_FLAG = 32768
PART_KEY_FLAG = 16384
GROUP_FLAG = 32768
UNIQUE_FLAG = 65536
*/
$div = array(65536,32768,16384,8192,4096,2048,1024,512,256,128,64,32,16,8,4,2,1);
foreach( $div as $divisor ) {
if( floor( $f / $divisor )) {
$f -= $divisor;
if( $which == $divisor ) return true;
}
}
return false;
}
function routinecode( $drop, $type, $name, $txt, $nl ) {
$s = ( $drop ) ? ( "drop " . strtolower($type) . " if exists " . idcleanup($name) . ";" . $nl ) : "" ;
if( substr( trim($txt), -1) != ";" ) $txt .= ";";
return $s . "delimiter go" . $nl . $txt . $nl . "go" . $nl . "delimiter ;" . $nl . $nl;
}
function query( $qry, $msg="", $exitoption=-1 ) {
GLOBAL $conn;
$result = mysqli_query( $conn, $qry );
$err = mysqli_error( $conn );
if( !empty( $err ))
err_handler( (empty($msg) ? "<b>Query:</b> $qry" : $msg ) . ". ", $exitoption );
return $result;
}
function procprivs( $db=NULL ) {
$ret = FALSE;
$res = query( "SHOW GRANTS FOR CURRENT_USER" );
while( $res && $row = mysqli_fetch_row( $res )) {
if( stristr( $row[0], " ALL PRIVILEGES ON *.* " )) { $ret=TRUE; break; }
if( stristr( $row[0], " PRIVILEGES ON mysql.proc" )) { $ret=TRUE; break; }
if( stristr( $row[0], " PRIVILEGES ON mysql" )) { $ret=TRUE; break; }
if( !empty( $db )) {
if( $i = stripos( $row[0], "CREATE ROUTINE ON `" )) {
if( ( $j = strpos( $row[0], "." )) > $i ) {
$i += 19;
$gdb = trim( stripslashes( substr( $row[0], $i, $j-$i-1 )));
if( $gdb == $db ) { $ret=TRUE; break; }
}
}
}
}
return $ret;
}
function err_handler( $errinfo, $setstate=-1 ) {
GLOBAL $conn;
$txt = ( is_object( $errinfo )) ? $errinfo->getmessage() : $errinfo;
$errtxt = mysqli_error( $conn );
echo "<div><TABLE border=0 cellspacing=0 cellpadding=2 width='80%'>",
"<tr><td><b><i>$txt</i>", (empty( $errtxt ) ? "" : ":"), " ",
"<span style='color:#ff0000;'>", $errtxt, "</span>",
"</b></td></tr>n</TABLE><br/><br/></div>n";
switch( $setstate ) {
case 0: break;
case -1: exit();
}
}
?>
download.php:
<?php
if( isset( $_POST['path'] ) && isset( $_POST['filename'] )) {
if( ini_get('zlib.output_compression')) ini_set('zlib.output_compression', 0 ); // NEEDED FOR IE??
$path = $_POST['path'];
$file = $_POST['filename'];
while( @ob_end_clean() );
set_time_limit( 0 );
clearstatcache();
try {
$filetype = "text/plain";
$fsize = filesize( $path . $file );
header( "Pragma: public" ); // required
header( "Cache-Control: must-revalidate, post-check=0, pre-check=0" );
header( "Cache-Control: none" );
header( "Content-type: $filetype" );
header( "Content-Disposition: attachment; filename=$file" );
header( "Content-Length: $fsize" );
$ret = @readfile( $path . $file );
if( $ret == $fsize ) unlink( $path . $file );
$fd = fopen( "dl-log.txt", 'a');
if( $fd ) {
fwrite( $fd, date("Y-m-d H:i:s " ) . $_SERVER['REMOTE_ADDR'] . " " . $path . $file . " SIZE=$fsize RESULT=$retn" );
fclose( $fd );
}
}
catch( Exception $e ) {
echo "<script>alert('Download error: ", $e->getmessage(), "');</script>";
}
}
?>
See http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html for how to manage concurrency while mysqldump is running, and for other backup scripts.
Last updated 5 Mar 2025
