Results 1 to 9 of 9

Thread: Importing Large Databases into PHPMyAdmin

  1. #1
    Nathan's Avatar
    Nathan is offline Work!
    Join Date
    Dec 2008
    Location
    New Zealand
    Posts
    392
    Blog Entries
    1
    Thanks
    37
    Thanked 120 Times in 68 Posts

    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.
    Programmer, web developer and entrepreneur.

  2. Thanked by:

    Aziz (11 October, 2010), Charles (10 October, 2010), m42 (10 October, 2010), Will.Spencer (10 October, 2010)

  3. #2
    SonnyCooL's Avatar
    SonnyCooL is offline HeeHa
    Join Date
    Jan 2010
    Location
    Melb/Malaysia
    Posts
    920
    Thanks
    250
    Thanked 92 Times in 78 Posts
    i used big dump, which is much more easier

  4. #3
    Nathan's Avatar
    Nathan is offline Work!
    Join Date
    Dec 2008
    Location
    New Zealand
    Posts
    392
    Blog Entries
    1
    Thanks
    37
    Thanked 120 Times in 68 Posts
    Quote Originally Posted by SonnyCooL View Post
    i used big dump, which is much more easier
    I've tried that before and had problems. Might be better these days.
    Programmer, web developer and entrepreneur.

  5. #4
    Aziz's Avatar
    Aziz is offline no investment, no glory
    Join Date
    May 2009
    Location
    IL
    Posts
    736
    Thanks
    588
    Thanked 243 Times in 168 Posts
    ah I always struggled with this! thanks a lot man

  6. #5
    Shenron's Avatar
    Shenron is offline Administrator
    Join Date
    Dec 2008
    Location
    Portugal
    Posts
    1,900
    Blog Entries
    2
    Thanks
    561
    Thanked 548 Times in 368 Posts
    Quote Originally Posted by Nathan View Post
    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.

  7. #6
    SonnyCooL's Avatar
    SonnyCooL is offline HeeHa
    Join Date
    Jan 2010
    Location
    Melb/Malaysia
    Posts
    920
    Thanks
    250
    Thanked 92 Times in 78 Posts
    Quote Originally Posted by Nathan View Post
    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

  8. #7
    Will.Spencer's Avatar
    Will.Spencer is offline Retired
    Join Date
    Dec 2008
    Posts
    5,033
    Blog Entries
    1
    Thanks
    1,010
    Thanked 2,329 Times in 1,259 Posts
    I use the source command from the MySQL shell.

    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;
    Submit Your Webmaster Related Sites to the NB Directory
    I swear, by my life and my love of it, that I will never live for the sake of another man, nor ask another man to live for mine.

  9. Thanked by:

    m42 (11 October, 2010), Nathan (12 October, 2010)

  10. #8
    m42's Avatar
    m42
    m42 is offline Administrator
    Join Date
    Dec 2008
    Posts
    725
    Thanks
    686
    Thanked 493 Times in 299 Posts
    Quote Originally Posted by Will.Spencer View Post
    I use the source command from the MySQL shell.
    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.
    (

    Diet Fads

  11. Thanked by:

    Charles (12 October, 2010), Shenron (11 October, 2010), tmongy (12 October, 2010)

  12. #9
    yangyang's Avatar
    yangyang is offline Sky Marshal
    Join Date
    Dec 2008
    Location
    kavoir.com
    Posts
    69
    Blog Entries
    4
    Thanks
    1
    Thanked 3 Times in 3 Posts
    For large SQL files as big as hundreds of MB I would just upload them to my host and import them from command line.
    I'm an entrepreneur at Kavoir LLC. My latest venture is about useful data sets.

Similar Threads

  1. Replies: 3
    Last Post: 1 January, 2011, 11:27 AM
  2. importing .xml error
    By garfish in forum Wordpress
    Replies: 8
    Last Post: 26 July, 2010, 16:51 PM
  3. Install MySQL and PHPMyAdmin
    By chatterbox in forum Databases
    Replies: 4
    Last Post: 26 August, 2009, 13:42 PM
  4. Backup of Mysql database using PhpMyAdmin
    By mega in forum Databases
    Replies: 2
    Last Post: 14 May, 2009, 10:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •