MySQL – Why is MySQL Slave’s DB Size Smaller than Master’s DB?

MySQLmysql-5.1mysql-5.6mysqldumpreplication

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

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

Related Solutions

MySQL Configuration – What max_allowed_packet Size is Big Enough and Why Change It

It's OK to max out the max_allowed_packet to 1G. Whenever a MySQL Packet is constructed, it will not jump to 1G from the start. Why?

First you need to know what a MySQL Packet. Page 99 of the Book

Understanding MySQL Internals

explains it in paragraphs 1-3 as follows:

MySQL network communication code was written under the assumption that queries are always reasonably short, and therefore can be sent to and processed by the server in one chunk, which is called a packet in MySQL terminology. The server allocates the memory for a temporary buffer to store the packet, and it requests enough to fit it entirely. This architecture requires a precaution to avoid having the server run out of memory---a cap on the size of the packet, which this option accomplishes.

The code of interest in relation to this option is found in sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay particular attention to net_realloc().

This variable also limits the length of a result of many string functons. See sql/field.cc and sql/intem_strfunc.cc for details.

Compare that with the MySQL Documentation on max_allowed_packet:

The maximum size of one packet or any generated/intermediate string, or any parameter sent by the mysql_stmt_send_long_data() C API function. The default is 4MB as of MySQL 5.6.6, 1MB before that.

The packet message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value by default is small, to catch large (possibly incorrect) packets.

You must increase this value if you are using large BLOB columns or long strings. It should be as big as the largest BLOB you want to use. The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.

When you change the message buffer size by changing the value of the max_allowed_packet variable, you should also change the buffer size on the client side if your client program permits it. On the client side, max_allowed_packet has a default of 1GB. Some programs such as mysql and mysqldump enable you to change the client-side value by setting max_allowed_packet on the command line or in an option file.

Given this information, you should be glad MySQL will expand and contract the MySQL Packet as needed. Therefore, go ahead and

set max_allowed_packet to 1G on both Master and Slave
set net_buffer_length to its max value of 1M on both Master and Slave

Master and Slave should match in terms of who they transmit data, especially BLOB data.

UPDATE 2013-07-04 07:03 EDT

From your messages concerning the relay log, it looks like you have the following

a corrupt relay log
a good master log

SUGGESTION

SHOW SLAVE STATUS\G
STOP SLAVE;
CHANGE MASTER TO
MASTER_LOG_FILE='(Relay_Master_Log_File from SHOW SLAVE STATUS\G)',
MASTER_LOG_POS=(Exec_Master_Log_Pos from SHOW SLAVE STATUS\G);
START SLAVE;

Running CHANGE MASTER TO clears all relay logs and starts with a new one. You will be replicating from the Last Master BinLog Event (BinLog,Position) that executed on the Slave.

Give it a Try !!!

MySQL Replication – Data Consistency with Binlog Format Changes

Relay log rotation have nothing to do with it.

What's critical is that the slaves, if set up with log_slave_updates (i.e. in themselves logging to binary logs), are pre-configured with binlog_format = MIXED or binlog_format = ROW.

To elaborate: there is no problem if the master uses binlog_format = STATEMENT and the slaves use ROW. There is a problem if the master uses ROW and the slaves use STATEMENT. You just can't restore the exact original statement from ROW format.

Likewise, if you master is MIXED, then slaves must be wither MIXED or ROW.

Back to your question, assuming the slaves are already configured with MIXED, there is no problem in just SET GLOBAL binlog_format = MIXED on the master. There is no problem in the binary log containing some entries of this format and some entries of the other. There is no problem with the relay logs containing entries of both forms.

I disagree with the accepted answer you linked to.