Importing Large Databases into PHPMyAdmin

I just had the problem of installing a database that was 16MB into PHPMyAdmin. The easiest way to fix this was to add an upload directory to PHPMyAdmin.

Steps to do this:

1. Go to your PHPMyAdmin folder (under apps if using WAMP) and create an upload directory.
2. Under the PHPMyAdmin folder open config.inc.php and change the variable $cfg['UploadDir'] = ''; to $cfg['UploadDir'] = 'upload';
3. Copy the database sql file into the upload directory.
4. Create a new database and go to import.
5. There will be a new dropdown box next to web server upload directory. There you can select the file you want to import.
6. Hit go!

Now the file that you wanted to import will be in the database.

i used big dump, which is much more easier :)

Quote:

---

Originally Posted by SonnyCooL

i used big dump, which is much more easier :)

---

I've tried that before and had problems. Might be better these days.

ah I always struggled with this! thanks a lot man

Quote:

---

Originally Posted by Nathan

I've tried that before and had problems. Might be better these days.

---

BigDump ( BigDump: The Staggered MySQL Dump Importer )is great, I've used it in the past to upload large databases (over 500Mb) and didn't have a single issue.

Quote:

---

Originally Posted by Nathan

I've tried that before and had problems. Might be better these days.

---

i think so ...... i just migrate few site and it work perfectly :)

I use the source command from the MySQL shell. :D

Code:

---

root@voyager# mysql dbname
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1069872
Server version: 5.1.37-1ubuntu5.1-log (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> source filename.sql;

---

Quote:

---

Originally Posted by Will.Spencer

I use the source command from the MySQL shell. :D

---

I use mysql commands as well. Here's example syntax:

This is ideal if you'd like to migrate a site DB (WordPress, Drupal, etc) and have access to a shell account.

STEP 1: Download/dump your DB contents into a file:

Code:

---

mysqldump -h<yourhostname> -u<dbusername> -p'<enterpasswordhere>' <olddbname> > <thefilenameyouwant>.sql

---

STEP 2: Restore/source your old DB contents to the new DB:

Code:

---

mysql -h<your(new)hostname> -u<dbusername> -p'<enterpasswordhere>' <newdbname>

---

The command will drop you into mysql. At the mysql prompt enter:

Code:

---

source filename.sql;

---

NOTE: filename.sql is the value for <thefilenameyouwant>.sql

With STEP 2, you should see the contents of your DB file scroll by while it imports. This should allow you to quickly verify your dump has worked without feeling the need to leave the prompt.

NOTE: If you are migrating the site to a new machine/hostname:

After STEP 1 (DB Dump), use a text editor (vi, emacs, etc) to open your newly created file:

Example:

Code:

---

vi <thefilenameyouwant>.sql

---

You should see something like this:

Code:

---

-- MySQL dump 10.x
--
-- Host: localhost    Database: <olddbname>
-- ------------------------------------------------------
-- Server version        5.x.x

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
<snip>

---

If your Hostname is changing, do a search and replace to update your hostname in the file. If you are using "localhost" you should be fine. Unless you have a special configuration, the hostname normally only appears at the top.

If your Database name is changing, do a search and replace to update the olddbname to the newdbname. For simple configurations, the database also usually only appears at the top of the file.

For large SQL files as big as hundreds of MB I would just upload them to my host and import them from command line.