• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1006
  • Last Modified:

IMPORT LARGE AMOUNT OF DATA INTO MYSQL

I have a large drupal db that I'm moving from one installation of MySQL to an installation of WAMPSERVER -  I get errors from the phpmyadmin saying that the data is too large for it to handle.

What do I do to import the data? OR is there a better way to basically move the drupal database?
I've tried to hack WAMPServer to access the original mysql, but no luck.

Thanks!
"This ain't rocket science, so why won't it fly" - unknown.
0
PhotoMan2000
Asked:
PhotoMan2000
2 Solutions
 
theGhost_k8Database ConsultantCommented:
Try using command prompt:
mysql -uroot -p DATABASENAME < FILE.SQL

If you're loading it to remote machine then you may use -h option:
mysql -uroot -p -hIPADDRESS/HOSTNAME DATABASENAME < FILE.SQL
0
 
PhotoMan2000Author Commented:
Hi theGhost....

I generally use the MySQL Query browser tools that come with MySQL -- is there a difference in the resulting file?
0
 
webwyzsystemsCommented:
Not sure how you are moving data around - or if you are at the console...could some default limits be affecting things?
upload_max_filesize (Default 2M)
memory_limit (Default 16M)
post_max_size (Default 8M)
You can edit php.ini to increase all if necessary.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
PhotoMan2000Author Commented:
so far nothing is working - I increased those limits to 120MB (my data file is 106mb)

Do know if the wamserver can be hacked to read the datafile of the orginal db? (both are local and on the same computer)
0
 
hieloCommented:
>>I generally use the MySQL Query browser tools that come with MySQL
If I am not mistaken that utility has a sub menu option (under "File" I believe) that allows you to select a .sql file and will execute it for you. Try that.
0
 
theGhost_k8Database ConsultantCommented:
You shall not find any issue while by loading sql through command prompt.
Meanwhile documentation for wampserver says:

1.16 I cannot upload big dump files (memory, HTTP or timeout problems).

Starting with version 2.7.0, the import engine has been re?written and these
problems should not occur. If possible, upgrade your phpMyAdmin to the latest
version to take advantage of the new import features.

The first things to check (or ask your host provider to check) are the values
of upload_max_filesize, memory_limit and post_max_size in the php.ini
configuration file. All of these three settings limit the maximum size of data
that can be submitted and handled by PHP. One user also said that post_max_size
and memory_limit need to be larger than upload_max_filesize.

There exist several workarounds if your upload is too big or your hosting
provider is unwilling to change the settings:

  * Look at the $cfg['UploadDir'] feature. This allows one to upload a file to
    the server via scp, ftp, or your favorite file transfer method. PhpMyAdmin
    is then able to import the files from the temporary directory. More
    information is available in the Configuration section of this document.
  * Using a utility (such as BigDump) to split the files before uploading. We
    cannot support this or any third party applications, but are aware of users
    having success with it.
  * If you have shell (command line) access, use MySQL to import the files
    directly. You can do this by issuing the "source" command from within
    MySQL: source filename.sql.
0
 
PhotoMan2000Author Commented:
none.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now