Results 1 to 9 of 9

Thread: Importing Large Databases into PHPMyAdmin

  1. #1

    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. #2
    i used big dump, which is much more easier

  3. #3
    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.

  4. #4
    ah I always struggled with this! thanks a lot man

  5. #5
    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.

  6. #6
    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

  7. 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.

  8. #8
    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

  9. #9
    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, 15:51 PM
  3. Install MySQL and PHPMyAdmin
    By chatterbox in forum Databases
    Replies: 4
    Last Post: 26 August, 2009, 12:42 PM
  4. Backup of Mysql database using PhpMyAdmin
    By mega in forum Databases
    Replies: 2
    Last Post: 14 May, 2009, 09: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
  •