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.
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.