Technology Bites

You are here: Home / Internet / Import Large MySQL dumps with BigDump

Import Large MySQL dumps with BigDump

As a website owner or blogger you may want to import your MySQL dump from one server to another server database, but what you do if it exceeds the limit offered by phpMyAdmin. By default phpMyAdmin provides the upload limit specified in the php.ini file. You can increase this upload limit size by editing the php.ini file if you have access to it, generally if you are in a shared hosting you won’t be able to edit this file becoz it will effect all the accounts on that server and your webhost won’t allow this. If you have shell access to your account you can import easily, see below for how to import using SSH

Using BigDump

BigDump is a script written by Alexey Ozerov for importing large mysql dumps. It’s a staggered MySQL dump importer, meaning the script executes only small part of the huge dump and restarts itself, the next session starts where the last was stopped. This process continues until it imports the whole database dump.

First you need to export your dump using phpMyAdmin, you can select the options like ‘Add DROP TABLE’, ‘Add IF NOT EXISTS’, if you plan to overwrite an existing database. Remember not to select ‘Extended Inserts’ while creating dump, if you select this option BigDump will fail. After creating the dump upload it to your server using your favorite FTP, download bigdump script and set the values like username, password, db name etc and upload it to the server. Now execute the script by pointing your browser to the bigdump.php file (ex: http://www.yourdomain.com/dump/bigdump.php). The file takes sometime to complete if you are importing a large database, be patient. Once it imports the dump successfully it will show you a message.

I had imported a database of size 160MB using this script, above this size i nevr tried but it should work properly.

Using SSH

If you have shell acess you can use the following commands to dump your mysql database.
mysql -u [user] -p [password] -h [hostname] [databasename] <[file.sql] Download BigDump

Comments

  1. That’s a great help!

    But what about the export? Any idea how to export a database if its too big to do via phpMyAdmin? Short of breaking it up and exporting a few tables at a time?

  2. @Pete S
    You can use the mysqldump command to get a backup of the database and then download through FTP.
    mysqldump -u username -ppassword database_name > dump.sql

  3. Thanks ram!

    But I don’t have shell access to this host. Only phpMyAdmin. And my database is 900 megs, so phpMyAdmin can’t export it all at once.

    However sometimes I ask the ISP to do a dump, and for times when I do that, BigDump will be pretty handy.

  4. @Pete
    Its not a good idea to download that large files with phpMyAdmin.

  5. I think SSH is the best way to do huge database migration, even if you are not familiar with the commands those you need to migrate your database from server to server, it is worth it to spend some time learning this, that will save you a lot of complain in future

  6. Yeah, but the problem is a lot of ISP’s done offer shell access, so the *only* way to manipulate your database is via PHPMyAdmin or writing a custom PHP script or something.

    If I could get shell access, all my problems would melt away. 🙂

  7. Yaah SSH is the best way i agree, Pete S has a good point.

  8. Anyone tried this with a 5gb+ dump file or can offer alternative suggestions?