CODEWITHSUNDEEP
mysql
Rubendob
Rubendob

Reputation: 1694

Why mysqldump is going so big?

Scenario:

MySQL database with mostly MyISAM tables. Current weight of MySQL filesystem:

80K ./casin_emails
212K    ./performance_schema
371M    ./casin_info
32K ./percona
572K    ./casin
1.1M    ./mysql
67G .

as you noticed most of the space comes from ibdata files

17956960  67G -rw-rw---- 1 mysql mysql  67G Nov 11 11:45 ibdata1
17956940 5.0M -rw-rw---- 1 mysql mysql 5.0M Nov 11 11:45 ib_logfile0
17956939 5.0M -rw-rw---- 1 mysql mysql 5.0M Nov 11 10:44 ib_logfile1

at the moment of write this question the still running process of mysqldump is writing a file of 14 GBytes.

So my question maybe is obvious. Why mysqldump is creating so big file if the current spaces of databases are so small? is ibdata guilty about that?

I suppose I cannot avoid the fact that mysqldump needs to generate some kind of backup of the data on ibadata but I just want to be sure and know why this is happening.

Thanks!

PS: I've searched previously about this question but all posts I found were about how to shrink databases which are not my current question.

Upvotes: 0

Views: 268

Answers (1)

Shadow
Shadow

Reputation: 34232

As MySQL documentation on mysqldump says (highlighting is mine):

The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data.

So, mysqldump does not simply export the data, but wrps them into sql statements. Moreover, all data is exported as text, so every numeric data and nulls will take up a lot more space, than in the database.

Upvotes: 3

Related Questions