on December 29 | in Hosting / Servers, Linux | by Sohail Riaz | 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.

about the author: Sohail Riaz

I am a First Red Hat Certified Architect - RHCA (ID # 110-082-666) from Pakistan with over 14 years industry experience in several disciplines including LINUX/UNIX System Administration, Virtualization, Network, Storage, Load Balances, HA Clusters and High Performance Computing.

related posts

« How To Install Subversion with TRAC on CentOS 5.x How To Fix page is not locked while installing Solaris 10 in VM »