Import/Export Large MYSQL Databases

When working with MYSQL I often use phpMyAdmin, which is a nice GUI way to manipulate my database. But some operations won't work in phpMyAdmin when the database is too large. In particular, you can't import or export really large databases using phpMyAdmin. So sometimes you need to do things on the command line.
So I thought I'd document some of the command line snippets we use frequently. In the following, replace [USERNAME] with your mysql username, [DBNAME] with your database name, [/path_to_file/DBNAME] with the path and name of the file used for the database dump, and [/path_to_mysql/] with the path to mysql bin (like /Applications/MAMP/Library/bin/).

Copy/Export a Large Database

MYSQL has no 'Copy' function. You create a copy by dumping the database with mysqldump.
To dump the database and gzip it at the same time, use the following. This will prompt you for your password.




mysqldump -u [USERNAME] -p [DBNAME] | gzip > [/path_to_file/DBNAME].sql.gz 




Source Link:

http://www.lullabot.com/blog/importexport-large-mysql-databases