How To Import Large MySQL Dump Files Using BigDump

on December 29 | in Hosting / Servers, Linux | by | with 8 Comments

In this howto i will describe how to import large mysql dump files without PHPMyAdmin and Shell Access. I am writing this howto because my hosting company (well most of them) wont allow to import very large files using MySQL dump files. Like Godaddy.com limit you to import only 2MB file using PHPMyAdmin. This also goes to large mysql dump files which has extremely high queries like i have more than 146000 records in one table.

BigDump is created by Alexey Ozerov, a brilliant script executes only a small part of the huge dump and restarts itself. The next session starts where the last was stopped.

So how much big file is and how much limitation (size, script execution time, no shell) from hosting on your account. You can just use  BigDump to import your MySQLDump.

1) Take dump of your MySQL Database

First thing is you to have your MySQL Database dump. You can have it using PHPMyAdmin or mysqldump command.

When taking dump using PHPMyAdmin enable Add Drop Table option and remove Extended Insert option.

Same if you are taking dump using mysqldump command use following options.

–opt –skip-extended-insert

The full command would be

mysqldump -u <username> -p<password> –opt –skip-extended-insert <database] > database_name.sql

2) Upload Your MySQL Dump File

You can use following ways to upload your file.

a. FTP to your account

b. Using your hosting control panel

3) Upload bigdump.php File

First of all download bigdump.zip file and extract bigdump.php.

Download http://www.ozerov.de/bigdump.zip

Edit file with your database host, database name, database user and database password.

The lines looks like this

// Database configuration

$db_server   = ‘localhost’;
$db_name     = ‘yourDBname’;
$db_username = ‘yourDBUsername’;
$db_password = ‘yourDBpass’

Most of the db_server or database hostname are localhost but correct value can be get from your hosting account.

After editing upload the file to your hosting account using same FTP or your hosting control panel. Be remember both bigdump.php and mysql dump should be in same directory.

Browse bigdump.php on your site

http://yoursite.com/bigdump.php

It will show your sql file and mentioned to Start Import into your database which you define in bigdump.php. You can upload file but again it was limit to upload size. So better to have your large mysql dump file using FTP.

When you click on Start Import, a beautiful interface will show you processing of your dump file and in end you will recieve congratulation of dumping full.

If you have any question please use comments.

pixelstats trackingpixel
Pin It

related posts

8 Responses to How To Import Large MySQL Dump Files Using BigDump

  1. Armaani says:

    I got this error when dumping

    Error at the line 21: DROP TABLE IF EXISTS `access`;

    Query: # MySQL-Front 5.0 (Build 1.68)
    DROP TABLE IF EXISTS `access`;

    MySQL: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘# MySQL-Front 5.0 (Build 1.68) DROP TABLE IF EXISTS `access`’ at line 1

  2. @Armaani: The issue is with your MySQL dump file, it has some unusual characters which are “”.
    You can check the file itself and edit accordingly or take another backup using mysqldump command or phpmyadmin.
    —————-
    Regards,

  3. huntsville says:

    You can also use the source command to import large files via shell. It is a more efficient way regardless of size. bigdump sometimes do not work. Find out about source command here http://www.huntsvillepr.com/importing-large-files-in-mysql-using-source-code-406.html

  4. @huntsville: I think you didnt saw what i wrote in description. I clearly says if you dont have shell access or have limitation on phpmyadmin upload then you have to use this. I am full time administrator and I know how to use shell and i have done what you said dozen times.
    My blog is for those who doesn’t have shell access or any limitation for big file to restore using phpmyadmin.
    —————–
    Regards,

  5. end user says:

    Thanks

    Big Dump works great for shared hosts and saved me tons of time wasted on contacting my host and getting them to do it for me. Yes its not necessary if you have shell access but not everyone is so lucky or knowledgeable.

  6. […] How To Import Large MySQL Dump Files Using BigDump […]

  7. Nilay says:

    hi
    can i use in linux server this script ?

  8. Muhammad Usman says:

    Nice Work Sohail. it solved my Biggest problem 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

« »