I noticed that my MySQL slave’s DB size is smaller than the Master’s DB

Posted on

Question :

I have a very simple setup of a master MySQL DB with one slave MySQL DB replicating from it.

Initially, when setting up the slave, I used mysqldump to get an initial DB dump from the master like this :

mysqldump --single-transaction --master-data  some_db > someSQL.sql

Then I go to my slave DB, created a brand new DB named “some_db” and then tried to reverse dump:

 mysql -uroot --one-database some_db < someSQL.sql

But then I did a comparison of the DB sizes on the MySQL prompt and noticed that the master’s some_db’s size is around 7.6 gigs, but that of the slave’s is only 6.4gigs.

I find this is concerning because I am wondering if certain data got lost. From what I understand, the DB does not contain blobs or any triggers, so where did that missing 1.2 gigs probably go?

Please let me know what your thoughts are.

Thanks
IS

Answer :

As tables go through many INSERT/UPDATE/DELETE cycle, mysql doesn’t reclaim the space from deleted rows; instead, it internally flags the row as ‘deleted’ and leaves it in the tablespace. 1G size difference is not uncommon and shouldn’t cause alarm.

You can try and run optimize table command on master and verify the table size get reduced. It is a good practice to routinely optimize table that does a lot of DELETE operation.

Leave a Reply

Your email address will not be published. Required fields are marked *