Saturday, June 20, 2009

Import MySQL dump with PHP on webhosts

Webhosts like GoDaddy and other similar often limit how much you can import with the online control panel (phpMyAdmin in most cases). So here is a simple PHP script that does this for you. You have to take a mysql dump from your database. Upload it to you host.

Make sure you change the mysql_connect parameters to reflect your database server settings. The following line numbers need change:

  1. line 2: mysql_connect( ) parameters: Database host, username, password.
  2. line 3: mysql_select_db( ) parameters: Database name.
  3. line 4: fopen( ) parameters: path and name of you SQL dump file.
<?php
mysql_connect('host', 'user', 'password');
mysql_select_db('database');
$file = fopen('dump.sql', 'r');
print '<pre>';
print mysql_error();
$temp = '';
$count = 0;

while($line = fgets($file)) {
  if ((substr($line, 0, 2) != '--') && (substr($line, 0, 2) != '/*') && strlen($line) > 1) {
    $last = trim(substr($line, -2, 1));
    $temp .= trim(substr($line, 0, -1));
    if ($last == ';') {
      mysql_query($temp);
      $count++;
      $temp = '';
    }
  }
}
print mysql_error();
print "Total {$count} queries done\n";
print '</pre>';
You may use the code for any purposes

What I have done is simply read the dump line by line. Lines starting with -- or /* are considered comments and left out. Now we need to build full SQL commands which span across many lines in the dump. So we check for the end semicolon (;) and until we find one we just join the lines together to get one SQL statement. Once done we execute it and move on to the next.

If you find any errors please comment. Also if you port this to use PostgreSQL or use other programming languages please share your code's link on this post's comment.

12 comments:

import sql@webunleashed post said...

Nice tips Sumit. Thanks for commenting on my blog WebUnleashed.
Cheers

Anonymous said...

Nice tips.
Two questions:
1. If you have a large table, is it going be inserted with a single SQL?

2. Are UTF8 characters handled by PHP?

Thank you, Sumit.

Than said...

Wow. This is awesome. After 2 days of struggling with phpmyadmin, and talking with Godaddy's support people I found this post and it worked in like 2 seconds. Thanks!

brainless said...

About the 2 questions:

1. If the MySQL dump is a single long SQL then yes. my code does nothing to break a long INSERT.

2. I am sure it will not work with UTF8 chars, although I have never had to do this stuff so I am not sure what the solution is. mb_substr() is one part of the solution?

prasun said...

Its pretty useful ...

Thanks Sumit Da

Paul Stubbs said...

I have tried using this script and when i run it it says no Total no queries done
So what do i do next.

Jodie said...

I can't seem to get it to work... is there suppose to be something other than an 'r' in line 4?

not valid stream resource show host address then line 10

dnp said...

Its gr8.

Matias said...

Great script, i didn't had luck with azarov bigdump and tried your script - fast and easy, just imported 125MB big sql file with no errors. Just put set_time_limit(0); in first line...

Anonymous said...

You forgot the closing php tag :)

?>

brainless said...

@Anonymous: PHP can do without the closing tag, and I prefer not to close it, although I am aware people would debate that.

@Matias: Nice to know it worked on that large a file.

@Jodie: No it should work as is. The 'r' is the last parameter I needed at least.

Jes K said...

regarding UTF8 characters use mysql_set_charset('utf8','link');

eg.

$link = mysql_connect($mysql_server, $mysql_username, $mysql_password);
mysql_select_db($database,$link);
mysql_set_charset('utf8',$link);

Post a Comment