pixelbyter #rich nicholls

Backing up a collection of MyISAM and InnoDB tables within a database via mysqldump can cause problems. MyISAM tables require locking, whereas InnoDB tables need to remain unlocked and can also take advantage of single transactions to speed up the backup process. I decided to write the following PHP script to list all of the databases and tables that the supplied user has permissions to view, which automatically uses the correct mysqldump settings for MyISAM and InnoDB table types, saving each table’s data to a separate SQL file. In the event of a connection error during the mysqldump of a table, the mysqldump command is repeated until it completes successfully.

$user = 'YOUR_USER';
$password = 'YOUR_PASSWORD';
$host = 'YOUR_HOST';
$backup_dir = '/path_to_backup_dir/'.date('YmdHis');

//mysqldump commands
$default_mysqldump_shared_command = 'mysqldump --force --user='.$user.' --password='.$password.' --host='.$host.' --quote-names --default-character-set=utf8 --verbose --compress';
$default_mysqldump_innodb_command = $default_mysqldump_shared_command.' --single-transaction --skip-add-locks --skip-lock-tables';
$default_mysqldump_myisam_command = $default_mysqldump_shared_command.' --opt';

//connect
set_time_limit(0);
if(!mysql_connect($host, $user, $password))
	exit;

//create backup dir
shell_exec('mkdir '.$backup_dir);

//cycle through databases
$query = "SHOW DATABASES";
$database_result = mysql_query($query);
while($database_data = mysql_fetch_array($database_result, MYSQL_ASSOC)) {
	//current db
	$current_database = $database_data['Database'];

	//create dir
	echo shell_exec('mkdir '.$backup_dir.'/'.$current_database);

	//get table list
	mysql_select_db($current_database);
	$query = "SHOW FULL TABLES WHERE Table_Type != 'VIEW'";
	$table_result = mysql_query($query);
	while($table_data = mysql_fetch_array($table_result, MYSQL_ASSOC)) {
		//current table
		$current_table = $table_data['Tables_in_'.$current_database];

		//get table type
		$query = "SHOW TABLE STATUS LIKE '{$current_table}'";
		$table_properties_result = mysql_query($query);
		$table_properties = mysql_fetch_array($table_properties_result, MYSQL_ASSOC);

		echo 'backing up '.$current_database.'.'.$current_table."\r\n";
		$success = false;
		while($success == false) {
			//innodb or not?
			if($table_properties['Engine'] == 'InnoDB')
				$result = shell_exec($default_mysqldump_innodb_command.' '.$current_database.' '.$current_table.'| gzip > '.$backup_dir.'/'.$current_database.'/'.$current_table.'.gz')."\r\n";
			else
				$result = shell_exec($default_mysqldump_myisam_command.' '.$current_database.' '.$current_table.'| gzip > '.$backup_dir.'/'.$current_database.'/'.$current_table.'.gz')."\r\n";

			//error
			if(strpos($result, 'Error 2013') !== false)
				echo 'lost connection, trying again'."\r\n";
			else
				$success = true;
		}
	}
}