33

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

sebkur
  • 658
  • 2
  • 9
  • 18
frazman
  • 32,081
  • 75
  • 184
  • 269
  • 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 – frazman Sep 14 '11 at 22:23
  • 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." – Pete Wilson Sep 14 '11 at 22:38
  • 2
    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.. :) – frazman Sep 14 '11 at 22:45
  • 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. – Pete Wilson Sep 14 '11 at 23:10
  • @Fraz please consider accepting the other answer – naXa stands with Ukraine Aug 18 '16 at 15:29

4 Answers4

135

To export:

 mysqldump -u mysql_user -p DATABASE_NAME > backup.sql

To import:

 mysqldump -u mysql_user -p DATABASE_NAME < backup.sql
Anfuca
  • 1,329
  • 1
  • 14
  • 27
Mohit Jain
  • 43,139
  • 57
  • 169
  • 274
  • 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 – frazman Sep 14 '11 at 22:22
  • 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. – serhio May 27 '14 at 14:57
  • "Thousands of Google search results" - Google brings me here. – AlexG Sep 27 '16 at 10:04
  • When you got the error `mysqldump: Got error: 1044: Access denied for user ... when using LOCK TABLES` add `--single-transaction` to your `mysqldump`. – dr0i Oct 24 '17 at 13:46
  • For import mysqldump does not work. Istead use this version the the import command `mysqldump -u mysql_user -p DATABASE_NAME < backup.sql` – Pankkaj Jan 15 '22 at 05:00
  • 1
    Import with mysqldump runs, but nothing happens. Works only with mysql command: ```mysql -u mysql_user -p DATABASE_NAME < backup.sql``` – Harkály Gergő Apr 18 '22 at 12:48
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.

Kenny
  • 1,090
  • 7
  • 9
1

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
Senthilkumar Ramasamy
  • 101
  • 3
  • 14
0

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"
manian
  • 1,418
  • 2
  • 16
  • 32
  • 1
    There should be any space between -p and password. It should look like mysqldump -u root -pPassword – Daniel G Sep 07 '20 at 13:37