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

Undirected

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 – Undirected

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." – Calisa

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.. :) – Undirected

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. – Calisa

@Fraz please consider accepting the other answer – Weinberger

Answer 1

To export:

 mysqldump -u mysql_user -p DATABASE_NAME > backup.sql

To import (note not mysqldump but mysql:

 mysql -u mysql_user -p DATABASE_NAME < backup.sql

Garbanzo

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 – Undirected

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. – Haematin

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

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

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

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

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.

Mutule

Answer 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

Thymol

Answer 4

For Exporting a database from WAMP in windows, please follow the below steps
1. Open Command Prompt
2. Change Directory to the bin folder of mysql(eg., CD C:\wamp64\bin\mysql\mysql5.7.14\bin)
3. Run the below command where 'password' is your mysql root password, 'dbname' is your database name & path within the doubles quotes is your directory where you want to save your file.

Command:

mysqldump -u root -p password dbname > "D:\db\db_backup.sql"

Haggi

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