Mysql : dump database along data

Question

I want to dump my database along the table schema and the table data also using the unix command line .

I used .

mysqldump -d -u root  -p frontend > frontend.sql

But above command is dumping only schema not the data from the database .

Please help me out how can i dump the database along the data also.

-d from man mysql dump `Do not write any table row information (that is, do not dump table contents). This is useful if you want to dump only the CREATE TABLE statement for the table (for example, to create an empty copy of the table by loading the dump file). ` — Jonathan de M., Dec 24 '12 at 15:11

Mariappan Subramanian · Accepted Answer · 2012-12-24T15:12:32.683

48

backup: # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

This will do,

If your requirement is to dump data alone then go for this,

To export to file (data only)

mysqldump -u [user] -p[pass] --no-create-db --no-create-info mydb > mydb.sql

edited Dec 24 '12 at 15:12

answered Dec 24 '12 at 15:03

Mariappan Subramanian

9,527
8
32
33

@masterofdestiny can you try this and get back to me if you face any issues – Mariappan Subramanian Dec 24 '12 at 15:13
if your database engine is InnoDB, you will prefer to use `--single-transaction` option. Read more [here](http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html). – FelikZ Dec 03 '13 at 10:16
I guess this method create sql with only create table statements without **create dataabse statement**. – EightT Oct 21 '22 at 16:08

score 21 · Answer 2 · answered Dec 24 '12 at 15:13

21

backup: #

mysqldump -u root -p[password] [database_name] > dumpfilename.sql

restore:#

mysql -u root -p[password] [database_name] < dumpfilename.sql

[ref:] http://www.thegeekstuff.com/2008/09/backup-and-restore-mysql-database-using-mysqldump/

answered Dec 24 '12 at 15:13

Raghvendra Parashar

3,883
1
23
36

score 14 · Answer 3 · answered Dec 07 '17 at 09:16

14

You just need to remove -d from your mysqldump command

answered Dec 07 '17 at 09:16

Bennet Joseph

308
3
6

1

I was struggling to find this answer which just worked. – Siva Tumma Oct 09 '22 at 13:57
1

That's the solution! +1 – Manuel Quintana Jan 09 '23 at 15:31

score 3 · Answer 4 · answered Oct 10 '14 at 09:20

mysqldump offers plenty of options to build a proper backup from your database:

mysqldump [options] db_name [tbl_name ...]
mysqldump [options] --databases db_name ...
mysqldump [options] --all-databases

Here you can find more detail about how to mysqldump:

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

score 1 · Answer 5 · answered Mar 30 '20 at 21:18

1

You're missing --no-data=False

e.g.

mysqldump -d -u root frontend --no-data=False -p > frontend.sql

answered Mar 30 '20 at 21:18

ktang

349
2
10

Yeah that is the ideal answer. thank you – PHPFan Aug 27 '22 at 07:56

score 0 · Answer 6 · answered Nov 14 '17 at 07:04

To restore database from .sql dumpfile

mysql -u [yourusername] -p [password] [nameofthedatabase] < /pathtoyoursqldumpfile.sql

Use mysqldump to create backup from database.

mysqldump -u [yourusername] -p [password] [nameofthedatabase] > /pathtoyoursqldumpfile.sql

score -2 · Answer 7 · answered Dec 24 '12 at 15:02

-2

I think the command you would need will be mysqldump. Try the following:

mysqldump -u root -p -h localhost frontend < frontend.sql

answered Dec 24 '12 at 15:02

Kevin Coyle

11

Why is `frontend.sql` directed to stdin? Does `mysqldump` read from stdin? – eggyal Dec 24 '12 at 15:03
please check updated i tried that but it is dumping only schema – masterofdestiny Dec 24 '12 at 15:04
2

the only bad thing with this answer is the direciton of the data redirection.. it should be ">", not "<".. > redirects the output from mysqldump into frontend.sql.. < READS the frontend.sql and redirects it to the stdin of mysqldump.. wich wil do nothing with it. – KnF Jul 10 '15 at 16:13

Mysql : dump database along data

7 Answers7

Linked