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

Solution 1

To export:

 mysqldump -u mysql_user -p DATABASE_NAME > backup.sql

To import:

 mysqldump -u mysql_user -p DATABASE_NAME < backup.sql

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

Solution 3

For example, to dump table definitions and data separately use these commands:

mysqldump -u mysql_user -p --no-data test > dump-defs.sql
mysqldump -u mysql_user -p --no-create-info test > dump-data.sql

Comments

[frazman]

I know how to export the database from terminal.. but I am looking for a solution to export it from the the mysql command line

[Pete Wilson]

Sure. Here's my revised comment: "Read the MySQL docs and find out how to export a database table into a CSV file. Hint: you can do it from the MySQL command line if you want."

[frazman]

then whats the point of stackoverflow... who gets to decide what questions you can ask... though i appreciate your thoughts..and see my long term benefit in following your advice.. :)

[Pete Wilson]

The person who gets to decide what questions you can ask is you. But you also get to apply the neosporin in the case where the question excites some mild flameogenesis because the answer is easily found elsewhere. In other words, RTFM is an acceptable, common answer to some questions.

[naXa stands with Ukraine]

@Fraz please consider accepting the other answer

[frazman]

Umm. I get the error " backup.sql" permission denied.. Also is there a way to do this internally (from mysql) rather than doing it from the terminal.. atleast the export.. I can do the import locally from the terminal

[serhio]

if you are under Windows Vista or later, you may have to launch your command line "as administrator" to have the rights to create a file in the specified folder.

[AlexG]

"Thousands of Google search results" - Google brings me here.

[dr0i]

When you got the error mysqldump: Got error: 1044: Access denied for user ... when using LOCK TABLES add --single-transaction to your mysqldump.

[Daniel G]

There should be any space between -p and password. It should look like mysqldump -u root -pPassword

[Pankkaj]

For import mysqldump does not work. Istead use this version the the import command mysqldump -u mysql_user -p DATABASE_NAME < backup.sql

[Harkály Gergő]

Import with mysqldump runs, but nothing happens. Works only with mysql command: mysql -u mysql_user -p DATABASE_NAME < backup.sql