Backup MySQL website databases without mysqldump
To backup the underlying MySQL databases for websites (Joomla! WordPress etc) I usually ssh into the hosting server, run mysqldump, then use rsync to copy the resulting sql file to the backup server. However, I’ve recently had to set up a site on a host without ssh or rsync available.
Instead, I wrote a php script to generate the same sort of SQL as mysqldump. I put the script on the hosting server (in a location that requires basic authentication), and I use cron to launch a
bash script which runs wget:
|
1 2 |
ID=`date +"%d%H%M"` wget -q -O- "http://uname:pword@www.xxx.com.au/admin/export.php"|gzip >/bak/xxx_$ID.sql.gz |
Fortunately MySQL has the “show create table” statement to generate the “create table” SQL to recreate an existing table. The other trick was to use an array of the numeric data types to determine if the content should be enclosed in
single quotes within the INSERT statement.
|
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 |
<?php // Connect to the MySQL server and select the database to be dumped require_once '../connect.php'; // Set up an array of the numeric MySQL types $num_types=array('INT', 'TINYINT', 'SMALLINT', 'MEDIUMINT', 'BIGINT', 'FLOAT', 'DOUBLE', 'DECIMAL'); // Get a list of the tables in the database // and process each of them $sql="show tables"; $tbl_result=mysql_query($sql,$readconn) or die ("$sql failed.<P>".mysql_error()); while ($tbl=mysql_fetch_array($tbl_result)) { // Generate the SQL to recreate the table $sql="show create table $tbl[0]"; $sql_result=mysql_query($sql,$readconn) or die ("$sql failed.<P>".mysql_error()); $row=mysql_fetch_array($sql_result); echo "$row[1];\n\n"; // Create an array of the column names and their type $columns=''; $sql="show columns from $tbl[0]"; $sql_result=mysql_query($sql,$readconn) or die ("$sql failed.<P>".mysql_error()); while ($row=mysql_fetch_assoc($sql_result)) { $field=$row[Field]; $type=explode('(',$row[Type]); $columns[$field]=strtoupper($type[0]); } // Retrieve the data in the table $sql="select * from $tbl[0]"; $sql_result=mysql_query($sql,$readconn) or die ("$sql failed.<P>".mysql_error()); // If there is data in the table, set up the insert statement // using the column names from the first row if (mysql_num_rows($sql_result)>0) { $row=mysql_fetch_assoc($sql_result); $ins="INSERT INTO `$tbl[0]` ("; $col='1'; foreach ($row as $key=>$value) { if ($col>1) $ins=$ins.', '; $ins=$ins."`$key`"; $col=$col+1; } $ins=$ins.") VALUES"; $col='1'; // and add the data from the first row $ins=$ins."\n("; foreach ($row as $key=>$value) { if ($col>1) $ins=$ins.', '; // Only enclose data in quotes if column is not a numeric type if (in_array($columns[$key],$num_types)) $ins=$ins.mysql_escape_string($value); else $ins=$ins."'".mysql_escape_string($value)."'"; $col=$col+1; } $ins=$ins.")"; // Add the data from the rest of the rows while ($row=mysql_fetch_assoc($sql_result)) { $col='1'; $ins=$ins.",\n("; foreach ($row as $key=>$value) { if ($col>1) $ins=$ins.', '; if (in_array($columns[$key],$num_types)) $ins=$ins.mysql_escape_string($value); else $ins=$ins."'".mysql_escape_string($value)."'"; $col=$col+1; } $ins=$ins.")"; } $ins=$ins.";\n"; echo $ins; } echo "\n"; } ?> |