WampServer

HI,

I have a large 35MB mysql db file which I need to import.

I have tried thru phpmyadmin however it was too large.

What is the best way for me to handle this please?

oz :0(

you can do it throught command prompt...

first, copy the database file ( .sql ) to this path

C:\wamp\bin\mysql\mysql5.1.36\bin

the number after mysql maychange depending on which version of wamp/mysql do you have.. but in normal cases, it will be only one folder there in mysql folder. so enter it and then go to 'bin' folder and copy the .sql there...


then. open the command prompt from Start>All Programs>Command Prompt.. you may have to right click on it's icon and click Run As Administrator so you just be in safe without problems...

if you opened the command prompt, type there

CD C:\wamp\bin\mysql\mysql5.1.36\bin

Note the space after CD in the begining, then hit enter

this should change the directory on the command prompt to that directory

now prepare for the command, you will need the datebase name ( you must created it before ), and the database username ( default for wamp is root ) and database password ( default for wamp is nothing, no password is set )

type the following command

mysql -u [DATABASE_USERNAME] -p [DATABASE_NAME] < [DATABASE_FILE.SQL]

replace the brackets with the correct values, for example if your database name is listnames and the sql file is latestdb.sql and you use the default username and password for the database then the line will be

mysql -u root -p listnames < latestdb.sql

after that hit enter.. it will ask you the password, if it's empty just hit enter again or type it then enter.. then it will restore the db.. for a db this size it will take seconds only...

I always use this way for restoring databases from tens of MB's to hundreds and some time even larger than 1GB... no problem at all...

phpMyadmin can handle any file size. The problem is with PHP. By default PHP is configured to have a file size upload limit of 2MB. To change this left click the wamp taskbar icon and go to PHP > php.ini

Now find this line

upload_max_filesize 2M

Change 2M to a higher value for example, 10M for a limit of 10MB

Thank guys for going to this trouble.

It is appreciated and I am sure others will also benefit as they get confroted with the larger db files.

In my rumbling around I did find the php.ini answer but it also involved thanging these things.

Not sure where I got it from now but also thanks to them who gave it to the world!!


Stop all services in wamp.

Then make changes to php.ini

post_max_size = 750M
upload_max_filesize = 750M
max_execution_time = 5000
max_input_time = 5000
memory_limit = 1000M
max_allowed_packet = 200M (in my.ini)

Restart all services and it should be okay, it did my 35Mb very quickly.



oz :-)



Edited 1 time(s). Last edit at 04/16/2010 12:21AM by ozstar.

For any who've got the same situation as me,
I'm developing on WAMP, but uploading to an ISP where I can NOT change the configuration of PHP / MYSQL / Apache. Nor can I run the Mysql command line.

Open the import file in your favorite text editor, and break it up into pieces.
If you break between tables, you just save each section, and import each section separately.
If you have to break it in the middle of a table, you may need to change the "comma (,)" at the end of a line to a semi-colon, and copy the "INSERT". Like this:
-----------------------------------
INSERT INTO `country` (`countrycode`, `country`) VALUES
('ACA', 'Acadia'),
('AFG', 'Afghanistan'),
('ALB', 'Albania'),
('DZA', 'Algeria'),
('YEM', 'Yemen, Republic of'),
('YUG', 'Yugoslavia'),
('ZAR', 'Zaire'),
('ZMB', 'Zambia'),
('ZWE', 'Zimbabwe');
-----------------
becomes
-----------------
INSERT INTO `country` (`countrycode`, `country`) VALUES
('ACA', 'Acadia'),
('AFG', 'Afghanistan'),
('ALB', 'Albania'),
('DZA', 'Algeria');
INSERT INTO `country` (`countrycode`, `country`) VALUES
('YEM', 'Yemen, Republic of'),
('YUG', 'Yugoslavia'),
('ZAR', 'Zaire'),
('ZMB', 'Zambia'),
('ZWE', 'Zimbabwe');
-----------------------------------
I've only ever had to do this a few times in the past 15 years, but if I had to do it on a regular basis I would write some code to do it for me.

Hope this helps,
--Andy
<><

thanks a lot

Its much easier to use the 'MySQL Console' to reload large database files.


left click wampmanager icon in the system tray -> MySQL -> MySQL Console

Enter the 'root' password which is nothing ( just press enter key ) unless you changed it.

Select the database you want to reload like this

USE Database_name;

Then load the dump file like this

SOURCE C:\folder\where\you_saved\your\dumpfile.sql

If you get any problems its all documented on the MYSQL Web Site.

ow..amazing.It worked perfectly for me. Thanks a lot.

There are some external software you can use them for database design and also import bigger data base
Website Development Company in Delhi



Edited 1 time(s). Last edit at 08/08/2013 05:04PM by ajaybihani.

I am trying to restore a 500 meg database from a dump file, and it is taking hours and hours, so never finishing. My disk has activity, and I don't see anything stuck or hanging. On my Linux server this usually takes about 10 minutes, but I can restore my database using the command line or phpmyadmin. Using Windows 8 with a brand new wamp install.

Hi,

It is possible to import big databases from SQL export file. I did some tests with SQL files larger than 250 MB, with success!
Just only change phpmyadmin.conf file without having to edit the php.ini options.
In wamp\alias\phpmyadmin.conf just before </Directory> add :

  php_admin_value upload_max_filesize 256M
  php_admin_value post_max_size 256M
  php_admin_value max_execution_time 600
  php_admin_value max_input_time 600
</Directory>

---------------------------------------------------------------
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons

Jim,

What type of database are you trying to restore? MyISAM or INNODB?


Simplest and quickest way is do do as I suggested above, and use the mysql.exe command line processor.

If its a INNODB database check the my.ini file, by default parameters for INNODB will need uncommenting and probably tweeking. Compare tham to your unix setting and optimize a little for your needs.



Edited 1 time(s). Last edit at 09/21/2013 04:28PM by RiggsFolly.

@ozstar.... thanks friend, u really solved one of my big headache in seconds. Those configuration worked for me

Cause and best ever solution with explanation is here,

[www.steptoinstall.com]

Hi,

NO, it is absolutely not the best solution. What you are proposing not only affects PhpMyAdmin but all other PHP scripts.
Simply apply the limit changes, only for phpMyAdmin, and to do this, simply change the alias that launch phpMyAdmin.
In the file wamp\alias\phpmyadmin.conf, just before </Directory> insert some directives to obtain:

  php_admin_value upload_max_filesize 128M
  php_admin_value post_max_size 128M
  php_admin_value max_execution_time 360
  php_admin_value max_input_time 360
</Directory>

And you can increase the values if necessary.
With these changes, it is possible (I did) to import a size of 260 MB.

---------------------------------------------------------------
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons