MySQL dump of large database appears to be less than original MySQL database itself

Question

I am backing up a database whose size is about 190 GB. I want to back up the database to a local file. This is the command I am using:

mysqldump -u root -p tradeData > /db_backup/tradeData.sql

I have enough space on my machine. I tried a bunch of times and got no errors, but I am always getting a result file whose size is around 122GB.

Does anyone have experience with backing up large databases? My machine is a Linux one.

You say the DB is `190 GB` in what? Where? How do you know that? The dump is `122GB` but how do you know it’s incomplete? — Giacomo1968, May 10 '14 at 04:40
I use [this material](http://stackoverflow.com/questions/1733507/how-to-get-size-of-mysql-database) for checking the database's size. — Tianyang Che, May 10 '14 at 04:42
That won’t give you a 1 to 1 connection between your local DB dump & what is actually in the system. Actual DBs have indexes and data that only exist when the DB is actually a DB in the database. — Giacomo1968, May 10 '14 at 04:44
Got it. I am importing the backup file into the database and then I will compare their sizes. — Tianyang Che, May 10 '14 at 04:47

Giacomo1968 · Accepted Answer · 2017-05-28T22:30:00.363

Using information like the SQL query here won’t give you a one-to-one connection between your local DB dump and what is actually in the system. Actual DBs have indexes and data that only exist when the DB is actually a DB in the database. As RolandoMySQLDBA explains:

From the dump file size, it is hard to judge because the combined total size of data pages and index pages maybe far less that the size of ibdata1 the dump was created from.

So my guess is your database includes InnoDB tables among other things that bloat the DB when compared to a bare dump.

MySQL dump of large database appears to be less than original MySQL database itself

1 Answers1