Best way to easily import heavy amount of data in MySQL database?

Dear friends,

We usually use phpmyadmin tool to do all types of database related operations. But, sometimes when we have large sql dump which PhpMyadmin failed to execute due to upload size and execution time limit of PHP script.
Then either you have to use MySQL GUI tools like SQLyog, Navicat or MySQL query browser or use MySQL command line.
Best way to export the DB is below:

E:\MySQL\MySQL Server 5.1\bin>mysqldump -u jroot -pjoomla1 -h hostname dbname > e:\backupfile.sql

If we want to export only table structure, execute below command:
mysqldump -ujroot -pjoomla1 -hlocalhsot --no-data prodtest  > e:\MySqlBkups\19112013jprod.sql
If we want to export only data of tables in a database, execute below command:
mysqldump -ujroot -pjoomla1 -hlocalhost --no-create-info prodtest  > e:\MySqlBkups\19112013jprod_data.sql
If we want to ignore a table during export a database, execute below command:
mysqldump -ujroot -pjoomla1 -hlocalhost jprodtest --ignore-table=jprod.sessions > e:\MySqlBkups\19112013jprod_data.sql

There are 3 ways to import the data using command line.

1. The LOAD DATA statement
2. The source Command
3. The mysql command

Second way, source command is the best way among these. Below is the way to import :

• First, you should logged-in to the MySQL and put the sql file wherever you want. To login on mysql command prompt, you have to go to the inner directory of mysql. For example,                                    c:>cd wamp/bin/mysql/mysql5.0.45/bin
• A) c:/wamp/bin/mysql/mysql5.0.45/bin>mysql -u root -p***** -hipaddressofhost
• mysql> source <path>filename.sql;
• B) C:\wamp\bin\mysql\mysql5.5.24\bin>mysql -u <username> -p<password>  -D<dbname> <c:\filetoimport.sql

Note:  there should not be any space between -p and password. It will be same for -h and hostname. Space will be between -u and username.

These are 3 simple steps to execute a sql dump file. It will take very less time to execute heavy queries.




Feel free to comment on my post, if you find any difficulty to implement.