Save and/or import database into other server

db-save-import.php

<?php

$config = array(
	'name' => 'Project Name',
	'id' => 'project-id',
	
	// (bool) If false, will only generate the sql file and will not import to destination server
	'import' => true,
	
	// Usually the remote connection
	'source' => array(
		'host' => 'localhost',
		'dbname' => '',
		'username' => 'root',
		'password' => 'root',
		'charset' => 'utf8'
	),
	
	// Usually the local connection
	'destination' => array(
		'host' => 'localhost',
		'dbname' => '',
		'username' => 'root',
		'password' => 'root',
		'charset' => 'utf8'
	),
);

$debug = true;
$step = 0;
$development = $_SERVER['HTTP_HOST'] === 'localhost';

echo str_pad('',1024);  // minimum start for Safari

if ($debug)
	echo '<pre>';

// Database connections
try
{
	$source = new PDO(sprintf('mysql:host=%s;charset=%s;dbname=%s', $config['source']['host'], $config['source']['charset'], $config['source']['dbname']), $config['source']['username'], $config['source']['password'], array(PDO::MYSQL_ATTR_INIT_COMMAND => sprintf('SET NAMES "%s"', $config['source']['charset'])));
}
catch (PDOException $e)
{
	echo sprintf("Error on line %s: %s \n", $e->getLine(), $e->getMessage());
	echo '<strong>You need at least the source connection working.</strong>';
	exit;
}

// Test destination connection, if not successfull, exit
$destination = null;

if ( $config['import'] === true ) {
	try
	{
		$destination = new PDO(sprintf('mysql:host=%s;charset=%s;dbname=%s', $config['destination']['host'], $config['destination']['charset'], $config['destination']['dbname']), $config['destination']['username'], $config['destination']['password'], array(PDO::MYSQL_ATTR_INIT_COMMAND => sprintf('SET NAMES "%s"', $config['destination']['charset'])));
	}
	catch (PDOException $e)
	{
		echo sprintf("Error on line %s: %s \n", $e->getLine(), $e->getMessage());
		echo '<strong>You need the destination connection working to import correctly, or set "import" as false.</strong>';
		exit;
	}
}
// set $destination to null to save file only

// Assign pdo to source
$pdo =& $source;

// Change execution time to prevent timeout
$default_execution_time = ini_get('max_execution_time');
$default_memory_limit = ini_get('memory_limit');
ini_set('max_execution_time', (60 * 20)); // 60 seconds times 12
ini_set('memory_limit', '512M');

$now = date('Y-m-d H:i:s');
$now_safe = date('Y-m-d_H-i');

// Create file
$backup_dir = sprintf('%s/db-backup-%s/', __DIR__, $config['id']);
if (!is_dir($backup_dir))
	mkdir($backup_dir);
$file = $backup_dir.'db-'.$config['id'].'-'.$now_safe.'.sql';
$fh = fopen($file, 'w');

$output = '';

// Add header
$output .= '# ************************************************************'."\n";
$output .= '# '.$config['name']."\n";
$output .= '# Generated on: '.$now."\n";
$output .= '# ************************************************************';

$output .= "\n\n"; // Add spacing

// Save header
fwrite($fh, $output);

if ($debug) {
	echo "     Fetching tables \n";
	flush();
}

// Array with the tables
$get_tables = $pdo->query('SHOW TABLES');
$get_tables->setFetchMode(PDO::FETCH_ASSOC);
$get_tables = $get_tables->fetchAll();

$tables = array();
foreach ($get_tables as $row) {
	$tables[] = current($row);
}

if ($debug) {
	echo "     Disabling foreign key checks \n";
	flush();
}

if ($debug) {
	echo "     Beginning transaction \n";
	flush();
}

$table_output = '';
$table_output .= 'SET FOREIGN_KEY_CHECKS=0;';
$table_output .= "\n\n"; // Add spacing
$table_output .= 'START TRANSACTION;';
$table_output .= "\n\n"; // Add spacing

fwrite($fh, $table_output);

// Begin transaction
if ( $destination instanceof PDO ) {
	$destination->exec($table_output);
}

foreach ($tables as $table) {

	if ($debug) {
		++$step;
		$step_prepared = str_pad($step, 2, " ", STR_PAD_LEFT);
		echo "{$step_prepared} - {$table}";
		flush();
	}

	if ($debug) {
		echo " - Processing";
		flush();
	}

	$table_output = '';

	$table_output .= '# Dump of '.$table."\n";
	$table_output .= '# ------------------------------------------------------------';

	$table_output .= "\n\n"; // Add spacing

	// Delete table if exists
	$table_output .= 'DROP TABLE IF EXISTS `'.$table.'`;';
	$table_output .= "\n\n"; // Add spacing

	// Add create table syntax
	$create_table = $pdo->query('SHOW CREATE TABLE `'.$table.'`');
	$create_table = $create_table->fetch();
	$table_output .= $create_table[1].';';
	$table_output .= "\n\n"; // Add spacing

	// Get table columns
	$get_table_columns = $pdo->query('SHOW COLUMNS FROM `'.$table.'`');
	$get_table_columns->setFetchMode(PDO::FETCH_ASSOC);
	$get_table_columns = $get_table_columns->fetchAll();

	// Organize/prepare table columns
	$table_columns = array();
	foreach ($get_table_columns as $column) {
		$table_columns[] = $column['Field'];
	}

	// Get table data
	$get_table_data = $pdo->query('SELECT * FROM `'.$table.'`');
	$get_table_data->setFetchMode(PDO::FETCH_ASSOC);
	//$table_data = $get_table_data->fetchAll();
	//$table_data = $get_table_data->fetch();
	$row_count = $get_table_data->rowCount();

	// If there are any results
	if (!empty($row_count)) {

		$table_output .= 'LOCK TABLES `'.$table.'` WRITE;'; // Prevent table from being written
		$table_output .= "\n\n"; // Add spacing

		$insert_data = '';
		$insert_data .= 'INSERT INTO `'.$table.'`';
		$insert_data .= "\n"; // Add spacing

		$insert_data .= '(`'.implode('`,`', $table_columns).'`)';
		$insert_data .= "\n"; // Add spacing

		$insert_data .= "VALUES"; // Add spacing
		$insert_data .= "\n"; // Add spacing

		$table_rows = array();

		while ( $row = $get_table_data->fetch() ) {
			$row = array_map(function($value) use ($pdo) {
				if ( $value === null )
					return 'NULL';
				return call_user_func(array($pdo, 'quote'), $value);
			}, $row);
			$table_rows[] = '('.implode(',', $row).')';
		}

		$insert_data .= implode(",\n", $table_rows);

		$insert_data .= ';'; // Close insert

		$table_output .= $insert_data;
		$table_output .= "\n\n"; // Add spacing

		$table_output .= 'UNLOCK TABLES;'; // Prevent table from being written
		$table_output .= "\n\n"; // Add spacing

	}

	//$output .= $table_output;

	// Import to destination
	if ( $destination instanceof PDO ) {
		if ($debug) {
			echo " - Importing";
			flush();
		}
		$destination->exec($table_output);
	}

	// Save to file
	if ($debug) {
		echo " - Saving";
		flush();
	}
	fwrite($fh, $table_output);

	echo "\n";

}

if ($debug) {
	echo "     Enabling foreign key checks \n";
	flush();
}

if ($debug) {
	echo "     Committing transaction \n";
	flush();
}

$table_output = '';
$table_output .= 'COMMIT;';
$table_output .= "\n\n"; // Add spacing
$table_output .= 'SET FOREIGN_KEY_CHECKS=1;';
$table_output .= "\n\n"; // Add spacing

fwrite($fh, $table_output);

// Committing transaction
if ( $destination instanceof PDO ) {
	$destination->exec($table_output);
}

if ($debug) {
	echo "     Saved file - {$file} \n";
	flush();
}

// Close file
fclose($fh);

if ($debug)
	echo "     <strong>Done</strong>!";

if ($debug)
	echo '</pre>';