in reply to Importing MySQL dump file remotely
I wrote a simple script that basically read the dump statement by statement and ended with a refresh header while remembering the offset, when getting too close to execution limit. It's definitely not 100 percent errorproof, but it worked for the dumps I needed it for.
I hope I'm not gonna get lynched for posting PHP code here :-)
header.inc and footer.inc are generic HTML fragments. I hope no error sneaked in when I translated the names and texts into english.<?php define('CUSHION', 5); define('FILENAME', 'dump.sql'); define('BLOCK_SIZE', 4000); define('LINE_SEP', "\n"); define('STMT_SEP', ";\n"); define('COMMENT', "--"); error_reporting(E_ALL); ini_set('display_errors', 1); $time_to_end = time() + intval(ini_get('max_execution_time')) - CUSHIO +N; $db = mysql_connect('localhost', 'username', 'passwd'); mysql_select_db('dbname', $db); mysql_query('SET NAMES utf8'); mysql_query('SET CHARACTER SET utf8'); $file = fopen(FILENAME, 'r'); $offset = (empty($_GET['o']) ? 0 : $_GET['o']); fseek($file, $offset, SEEK_SET); $counter = 0; $block = ''; $chopped = ''; $errors = array(); while (time() < $time_to_end) { $block .= fread($file, BLOCK_SIZE); $start = 0; while(($end = strpos($block, STMT_SEP, $start)) !== false) { $stmt = $chopped . substr($block, $start, $end-$start); $start = $end + 2; while (strpos($stmt, COMMENT) === 0) { $new_start = strpos($stmt, LINE_SEP); $stmt = substr($stmt, $new_start + 1); } if (mysql_query($stmt, $db)) { $counter++; $chopped = ''; } else { $errors[] = mysql_error(); $errors[] = $stmt; if (!mysql_ping()) { $offset = ftell($file) - STMT_SEP + $start - 2 - strlen($stmt) +; header('Refresh: 0; URL=loader.php?o='.$offset); include('header.inc'); echo 'Connection to db was interrupted, couldn\'t finish state +ment: '.$stmt; include('footer.inc'); exit; } $chopped = $stmt . STMT_SEP; } } $block = substr($block, $start); } $offset = ftell($file) - strlen($block) - strlen($chopped); if (!feof($file)) header('Refresh: 0; URL=loader.php?o='.$offset); include('header.inc'); echo 'Successfully executed ' . $counter . ' statements, execution end +ed at position ' . $offset; if (!empty($errors)) echo '<br>There were errors:<br>' . implode('<br>', $errors); if (feof($file)) echo '<br>And it\'s finished.'; include('footer.inc'); ?>
|
|---|
| Replies are listed 'Best First'. | |
|---|---|
|
Re^2: Importing MySQL dump file remotely
by Lawliet (Curate) on Aug 09, 2008 at 12:14 UTC |