ADDING DATABASE FILE INTO MYSQL USING COMMAND PROMPT

If you been working on wamp server, then sometimes you may not want to enter all the database commands manually and enter into the database all the values,when you have a text file of the database with you, especially when your database is a quite large file..  So, here's how you can enter all the entries into the database in just few clicks..


 METHOD 1:
you can simply open the text file of the databse you have got and select all the data and copy it. just go to your mysql console and right click there.. Then select paste and press enter.. Here you go.. You have all your data in your database.
NOTE:: It must be noted that ctrl +v will not work as paste command in your mysql console.


METHOD 2:
If you text file for the database is quite large, then the paste option will hang your mysql console and not enter the database values. This is becuase by default your wamp server will not be able to load files of more than 2Mb onto your server. so you need to change just some settings and you can upload files of any size.

•  Stop all services in wamp. 
• Then go to your wamp manager in the taskbar. click on the wamp and select PHP.
• Here you have to make changes to php.ini.
• just open the file and search for the following texts using ctrl+f. And replace the corresponding values with the new values given below:
  
  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 to upload large files also..

METHOD 3: 

But sometimes, changing the php.ini doesn't work quite well for some users. In such case you can do it through command prompt also.

For this you just have to follow some simple steps. Here they are::

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