Hacking at 0300

All I wanted to do was back up the Young Israel databases, some way more amenable to automation than phpMyAdmin. There are lots of PHP-based solutions on the web, but all seem based on mysqldump. I implemented one and found myself faced with an eyeful of אריאל מאיר יעקב בן דוד אברהם where the Hebrew names should be. Turns out this a known bug; mysqldump can't handle Unicode. There are reports of workarounds, but I spent 8 hours not getting anything to work.

So I had to write my own backup, going through each database on a server then each table in the database, writing the appropriate INSERT INTO commands (thank goodness the SHOW CREATE DATABASE and SHOW CREATE TABLE commands work correctly). It wasn't terribly miserable (not nearly as miserable it was trying to use someone else's tool that doesn't work), and now I get my  אריאל מאיר יעקב בן דוד אברהם just fine, and the backup works. I import the generated file into my local copy of mySQL and it regenerates the databases.

// if this file is called directly from the web, run the backup on the requested databases
if (realpath(__FILE__) == realpath($_SERVER['SCRIPT_FILENAME'])) db_backup($_REQUEST['database']);

function db_backup($databases){
	if (!is_array($databases)) $databases = array($databases);
	$dir = '/backup'; // change this as desired
	if( !is_dir( $dir ) && !mkdir( $dir )) die('<h1>Could not create backup directory</h1>'); 
	foreach ($databases as $database){
		list($host, $name, $user, $password) = explode('/', $database);
		$now = date('Y-m-d'); // change this as desired
		@mysql_connect($host, $user, $password) or die ("<h1>Could not connect to $host</h1>");
		file_put_contents("$dir/$name-$now.sql", dump_dbs());
	}
}

// based on http://davidwalsh.name/backup-mysql-database-php
function dump_dbs(){
	$ret = '';
	$dbs = mysql_list_dbs();
	while ($db_row = mysql_fetch_object($dbs)) {
		$db = $db_row->Database;
		if ($db == 'information_schema') continue;
		mysql_select_db ($db);
		mysql_query("SET NAMES 'utf8'");  // magic command to make mySQL output UTF8 to PHP
		$ret = "DROP DATABASE IF EXISTS `$db`;\n";
		$createdb = mysql_fetch_row(mysql_query("SHOW CREATE DATABASE `$db`"));
		$ret .= $createdb[1].";\nUSE `$db`;\n";
		$tables = mysql_query('SHOW TABLES');
		while($table = mysql_fetch_row($tables)){
			$ret .= dump_table($table[0]);
		}
	} // while
	return $ret;
}

function dump_table($table){
	$ret = "\nDROP TABLE IF EXISTS `$table`;\n";
	$createtable = mysql_fetch_row(mysql_query("SHOW CREATE TABLE `$table`"));
	$ret .= $createtable[1].";\n";
	$dbresult = mysql_query ("SELECT * FROM `$table`");
	while($row = mysql_fetch_assoc($dbresult)) {
		$keys = fixkeys(array_keys($row));
		$values = fixvalues(array_values($row));
		$ret .= "\tINSERT INTO `$table` (".join(',', $keys).') VALUES ('.join(',',$values).");\n";
	}
	return $ret;
}
function fixkeys ($arr){
	foreach ($arr as &$i){
		$i = "`$i`";
	}
	return $arr;
}
function fixvalues($arr){
	foreach ($arr as &$i){
		$i = is_null($i) ? 'NULL' : "'".preg_replace("/\r\n|\n|\r/",'\n',addslashes($i))."'";
	}
	return $arr;
}

The main function db_backup takes a string or an array of strings that describe the databases to be backed up, of the form 'host/name/user/password'. For the databases that my host provides it would look like 'db12345.perfora.net/descriptivename/dbo45678/password', where descriptivename is whatever you want prepended to the backup file, which is named 'descriptivename-2009-02-09.sql' or whatever the date is.

If it is called directly as a web page, the database strings are taken from the query string with key "database". If you want to pass more than one database, don't forget to use database[]= to make it into a PHP array. Thus,

http://example.com/db_backup.php?database=db12345.perfora.net/serverdata/dbo45678/password

or

http://example.com/db_backup.php?database[]=db12345.perfora.net/serverdata/dbo45678/password&database[]=localhost/localdata

I hope this saves someone a whole lot of agony!