mysqldump is not dumping my data

Question

I am running mysqldump on Ubuntu Linux (10.0.4 LTS)

my mySQL version info is:

mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1

I used the following command:

mysql -u username -p dbname > dbname_backup.sql

However when I opened the generated .sql file, I saw that most of the tables had only the schema dumped and in the few cases where the actual data was dumped, only 1 or two records were dumped (there are ATLEAST several tens of records in each table).

Does anyone know what maybe going on?

mysql -u username -p dbname > dbname_backup.sql ? It should be mysqldump -u username .... — Sacx, Mar 19 '11 at 15:38

score 5 · Answer 1 · answered Mar 19 '11 at 15:44

5

I think you expect to see a lot of inserts and probably you see only 1 or 2. Is normal, if you look closer you will see 1 or 2 BIG inserts (extended inserts). Extended inserts are multiple row inserts which includes several VALUES lists.

answered Mar 19 '11 at 15:44

Sacx

2,581
16
13

Out of curiosity, how many rows do most extended inserts contain? – TheLQ Mar 19 '11 at 15:59
I think mysqldump calculate that depending on how many data is in tables. – Sacx Mar 19 '11 at 16:13

score 2 · Answer 2 · answered Apr 03 '22 at 16:20

I also had the problem that data was not dumped, but not for the same reason. Posting an answer here in case someone makes the same mistake. I did:

mysqldump -u user -p -d database_name

thinking that -d database_name specifies the db name. However database_name is considered as last argument (and not an option), while -d seems to be an (undocumented) standalone abbreviation for --no-data. After I removed -d I got the data in the dump.

score 2 · Answer 3 · answered Apr 20 '11 at 21:06

2

Try passing the full command instead:

mysqldump --databases --result-file=filename.sql mydatabase -u mysqluser -p

answered Apr 20 '11 at 21:06

vincew

21
1

mysqldump is not dumping my data

3 Answers3