in reply to Importing MySQL dump file remotely

I got into similar problems in my old PHP days - I only had FTP access and ability to run PHP; dump import in PHPMyAdmin was failing because of the execution time limit.

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 :-)

<?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'); ?>
header.inc and footer.inc are generic HTML fragments. I hope no error sneaked in when I translated the names and texts into english.

Replies are listed 'Best First'.
Re^2: Importing MySQL dump file remotely
by Lawliet (Curate) on Aug 09, 2008 at 12:14 UTC

    AH! PHP CODE! Lynch him! :P

    <(^.^-<) <(-^.^<) <(-^.^-)> (>^.^-)> (>-^.^)>