Export dump file from MySql

Question

I want to create a dump file of a table in the database. So database --> king, tablename --> castle So what I want to do is create a dump file.. and then the next part is to import it in my local host. database name --> king_local. Any ideas on how to go about this!! Thanks

Answer 1

To export:

 mysqldump -u mysql_user -p DATABASE_NAME > backup.sql 

To import:

 mysqldump -u mysql_user -p DATABASE_NAME < backup.sql 

Answer 2

Since you now seem to want to export the data from within the MySQL monitor, you want to look at the documentation from MySQL on SELECT ... INTO OUTFILE:

http://dev.mysql.com/doc/refman/5.0/en/select.html

The INTO OUTFILE stuff is what you'd use to dump data in to said "outfile", and you'd want to specify the full path to a location that MySQL can write to, for example /tmp/dump.sql.

You'll then want to pay attention to the docs so you can specify how to end lines, delimit fields etc:

FIELDS TERMINATED BY, ENCLOSED BY, ESCAPED BY, LINES TERMINATED

And then to load it back in, LOAD DATA INFILE seems to be what you want. Go to that URL I posted, it has everything you seem to want.