In wamp how can I import 35MB db - too big for phpmyadmin
Posted by: ozstar (---.livrp2.nsw.optusnet.com.au)
Date: April 15, 2010 03:10AM

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(

Options: ReplyQuote
Re: In wamp how can I import 35MB db - too big for phpmyadmin
Posted by: Xajel (95.84.76.---)
Date: April 15, 2010 03:45PM

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

Options: ReplyQuote
Re: In wamp how can I import 35MB db - too big for phpmyadmin
Posted by: c2dan (---.15-1.cable.virginmedia.com)
Date: April 15, 2010 07:23PM

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

Options: ReplyQuote
Re: In wamp how can I import 35MB db - too big for phpmyadmin
Posted by: ozstar (---.livrp2.nsw.optusnet.com.au)
Date: April 16, 2010 12:16AM

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.

Options: ReplyQuote
Re: In wamp how can I import 35MB db - too big for phpmyadmin
Posted by: darkroomsource (---.or.wavecable.com)
Date: April 23, 2010 07:04PM

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

Options: ReplyQuote
Re: In wamp how can I import 35MB db - too big for phpmyadmin
Posted by: dibyadel (164.100.31.---)
Date: August 07, 2013 10:01AM

thanks a lot

Options: ReplyQuote
Re: In wamp how can I import 35MB db - too big for phpmyadmin
Posted by: RiggsFolly (---.as13285.net)
Date: August 07, 2013 10:07AM

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.

Options: ReplyQuote
Re: In wamp how can I import 35MB db - too big for phpmyadmin
Posted by: dibyadel (---.178.179.148.bol.net.in)
Date: August 08, 2013 04:07PM

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

Options: ReplyQuote
Re: In wamp how can I import 35MB db - too big for phpmyadmin
Posted by: ajaybihani (120.59.99.---)
Date: August 08, 2013 05:02PM

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.

Options: ReplyQuote
Re: In wamp how can I import 35MB db - too big for phpmyadmin
Posted by: JimKomara (---.direcpc.com)
Date: September 20, 2013 03:13PM

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.

Options: ReplyQuote
Re: In wamp how can I import 35MB db - too big for phpmyadmin
Posted by: Otomatic (Moderator)
Date: September 20, 2013 03:48PM

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>

------------------------------------------------------------------------------------------------------------
Wampserver 3.2.10 32 bit - Apache 2.4.54 - PHP 8.2.0…5.6.40 - MySQL 5.7.39 - MariaDB 10.5.13/10.6.5
Wampserver 3.2.10 64 bit - Apache 2.4.54 - PHP 8.2.0…5.6.40 - MySQL 5.7.39/8.0.30 - MariaDB 10.5.17/10.9.2
PhPMyadmin 5.2.0 - MysqlDumper 1.24.5
on W10 and W7 Pro 64 bit
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons
« Ce n'est pas parce qu'ils sont nombreux à avoir tort, qu'ils ont forcément raison. Coluche »
« It's not because they are many to be wrong, they are necessarily right. Coluche »

Options: ReplyQuote
Re: In wamp how can I import 35MB db - too big for phpmyadmin
Posted by: RiggsFolly (---.as13285.net)
Date: September 21, 2013 03:16PM

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.

Options: ReplyQuote
Re: In wamp how can I import 35MB db - too big for phpmyadmin
Posted by: abhi6146 (124.124.205.---)
Date: October 20, 2014 08:20AM

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

Options: ReplyQuote
Re: In wamp how can I import 35MB db - too big for phpmyadmin
Posted by: karsho (27.34.246.---)
Date: April 12, 2015 08:01AM

Cause and best ever solution with explanation is here,

[www.steptoinstall.com]

Options: ReplyQuote
Re: In wamp how can I import 35MB db - too big for phpmyadmin
Posted by: Otomatic (Moderator)
Date: April 12, 2015 09:58AM

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.

------------------------------------------------------------------------------------------------------------
Wampserver 3.2.10 32 bit - Apache 2.4.54 - PHP 8.2.0…5.6.40 - MySQL 5.7.39 - MariaDB 10.5.13/10.6.5
Wampserver 3.2.10 64 bit - Apache 2.4.54 - PHP 8.2.0…5.6.40 - MySQL 5.7.39/8.0.30 - MariaDB 10.5.17/10.9.2
PhPMyadmin 5.2.0 - MysqlDumper 1.24.5
on W10 and W7 Pro 64 bit
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons
« Ce n'est pas parce qu'ils sont nombreux à avoir tort, qu'ils ont forcément raison. Coluche »
« It's not because they are many to be wrong, they are necessarily right. Coluche »

Options: ReplyQuote


Sorry, only registered users may post in this forum.