CODEWITHSUNDEEP
mysqlsizereplication
merlin.metso
merlin.metso

Reputation: 107

mysql replication databases different size

Replication between MySQL_5.6 servers. Master and Slave. 30 minutes after replication begins there were 1062 errors. Now everything is OK. No errors in 'SHOW SLAVE STATUS;' If I create Tables in Master I can see them on the Slave. Looks like everything is alright, BUT.

Size of Databases on Master 17 GB. File system is UFS. (FreeBSD_9)

Size of Databases on Slave 12 GB only. File system EXT4 with NOATIME (Ubuntu_12.04)

Is it OK?

Upvotes: 0

Views: 2678

Answers (3)

Marcin Rygiel
Marcin Rygiel

Reputation: 1

There are tools available, that can check master-slave consistency on-line, without having to disable writes or whatever else - one of which is: https://github.com/KredytyChwilowki/MySQLReplicaIntegrityCheck

Upvotes: 0

Michael - sqlbot
Michael - sqlbot

Reputation: 179004

The fact that you got the 1062 errors almost certainly means that you did not get a clean start with replication, and you should start over.

If you didn't use the --master-data and --single-transaction option when you extracted the data with mysqldump ... or you didn't use precisely the correct coordinates with CHANGE MASTER TO then these would be the likely causes.

The other thing to consider, however, is that tables do fragment over time, so the tablespaces on the master are not likely to be as compact as the newly-created ones on the slave. This can be remedied with the OPTIMIZE \[LOCAL\] TABLE statement run on the master, which you can automate with the mysqlcheck utility.

$ mysqlcheck --all-databases --optimize --skip-write-binlog

However... this is more for your assurance that the sizes are really comparable, than anything actually necessary, and be aware that each table will be locked while being optimized, resulting in potential problems with your application while the locks are in place. Also, it will not actually change the size of the data on disk if you are using InnoDB tables but you aren't using the innodb_file_per_table option... which you should be using, but if you aren't, then changing it now won't affect any existing tables on the master -- only new ones.

It is vitally critical that master and slave have absolutely identical data, or you will have a false sense of security and your servers will not be identical and replication will eventually break again -- and meanwhile, queries against the slave can return different results than the master.

CHECKSUM TABLE run against each table will return a number. If that number is not the same on both servers, then the tables do not contain identical data. There are also 3rd party tools that can automate this checking... or even fixing the problem:

The pt-table-sync utility can be used to get the two machines back in sync if the inconsistency is not severe. I don't use it because we have our own in-house systems that audit servers for consistency and repair mismatched tables... which, by the way, is an extremely rare condition when replication is set up correctly from the beginning.

Upvotes: 2

Mahesh Patil
Mahesh Patil

Reputation: 1551

I think there might be inconsistency in data between Master and Slave server.

You can check the replication integrity by using this open-source tool called pt-table-checksum which safely and efficiently verifies that data on MySQL replicas is identical to the master. Having inconsistent data on MySQL replicas is a well-known and rather common problem, yet many people who depend on MySQL replication never check for it.

Upvotes: 1

Related Questions