0

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

Improve this question
2
  • how did you find the size in mysql prompt? data_length+index_length in information_schema.tables? If yes, then first you need to understand concepts of fragmentation and garbage collector.
    – Mannoj
    Oct 17, 2014 at 1:11
  • I used this SQL query to find the sizes : SELECT table_schema "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema;
    – user1805458
    Oct 17, 2014 at 15:27

1 Answer 1

Reset to default
0

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.

Improve this answer
1
  • That's a good advice, Manny, I will try that and let you know if that explains the 1 gig difference, thanks
    – user1805458
    Oct 17, 2014 at 15:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.