Databases
StackOverflow2013
Postsdbo
PO
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    text
    copied!<p>There are some nifty and still simple backup scripts out there; the one from PHPMyAdmin is probably a very good one because well tested, the problem is to extract that from the whole package to be able to call it automatically.</p> <p>For the exact same task I developed my own small script, which iterates over all tables in a database and sends the contents in a bzipped sql file (meaning that it can easily be imported again if the backup should be needed).</p> <p>Because it is so simple, this script of course also has its limitations, as pointed out in the comment below: It only backs up the table content. Not even the table structure is preserved, the assumption here is that the table structure changes very seldomly and also could be easily reconstructed if needed. But if your software changes the schema rather often and automatically, or if it uses functions or views, you might have to look for a more advanced script!</p> <p>So far for the "disclaimers", here we go with the simple script:</p> <pre><code>&lt;?php $db_host = "localhost"; $db_name = $_GET["db"]; $db_user = $_GET["user"]; $db_pass = $_GET["pass"]; $do_truncate = $_GET["truncate"]; function datadump ($table, $do_truncate) { $result .= "-- -------- TABLE '$table' ----------\n"; $query = mysql_query("SELECT * FROM ".$table); $numrow = mysql_num_rows($query); $numfields = mysql_num_fields($query); if ($numrow &gt; 0) { if (strcmp($do_truncate, "yes") == 0) { $result .= "TRUNCATE TABLE ".$table.";\n"; } $result .= "INSERT INTO `".$table."` ("; $i = 0; for($k=0; $k&lt;$numfields; $k++ ) { $result .= "`".mysql_field_name($query, $k)."`"; if ($k &lt; ($numfields-1)) { $result .= ", "; } } $result .= ") VALUES "; while ($row = mysql_fetch_row($query)) { $result .= " ("; for($j=0; $j&lt;$numfields; $j++) { if (mysql_field_type($query, $j) == "string" || mysql_field_type($query, $j) == "timestamp" || mysql_field_type($query, $j) == "time" || mysql_field_type($query, $j) == "datetime" || mysql_field_type($query, $j) == "blob") { $row[$j] = addslashes($row[$j]); $row[$j] = ereg_replace("\n","\\n",$row[$j]); $row[$j] = ereg_replace("\r","",$row[$j]); $result .= "'$row[$j]'"; } else if (is_null($row[$j])) { $result .= "NULL"; else { $result .= $row[$j]; } if ( $j&lt;($numfields-1)) { $result .= ", "; } } $result .= ")"; $i++; if ($i &lt; $numrow) { $result .= ","; } else { $result .= ";"; } $result .= "\n"; } } else { $result .= "-- table is empty"; } return $result . "\n\n"; } mysql_connect($db_host,$db_user,$db_pass); @mysql_select_db($db_name) or die("Unable to select database."); $tableQ = mysql_list_tables ($db_name); $i = 0; $content = "-- --------------------------------\n"; $content .= "-- DATABASE DUMP\n"; $content .= "-- DATE: ".date("d-M-Y")."\n"; $content .= "-- DB NAME: ".$db_name."\n"; $content .= "-- TRUNCATE: ".((strcmp($do_truncate, "yes") == 0)? "YES" : "NO")."\n"; $content .= "-- ---------------------------------\n\n"; while ($i &lt; mysql_num_rows ($tableQ)) { $tb_names[$i] = mysql_tablename ($tableQ, $i); $content .= datadump($tb_names[$i], $do_truncate); $i++; } $file_name = "MySQL_Database_Backup.sql.bz2"; Header("Content-type: application/octet-stream"); Header("Content-Disposition: attachment; filename=$file_name"); echo(bzcompress(utf8_encode($content))); exit; ?&gt; </code></pre> <p>I guess it still could need a few improvements (security e.g.), but it serves the purpose quite well. To use it, upload it to your webspace, let's say as 'backup-db.php' (preferredly in some hidden folder), and call it like this</p> <pre><code>http://yoursite/backup-db.php?host=yourdbhost&amp;name=&lt;yourdbname&gt;&amp;user=&lt;dbuser&gt;&amp;pass=&lt;dbpass&gt;&amp;truncate=&lt;whethertoaddtruncatestatementforeachtable&gt; </code></pre> <p>You of course first have to adapt the values in brackets &lt;>. Feel free to use and adapt it; should you make any improvements, I'd be very glad to hear about it! </p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload