Comparing and Synchronizing of MySQL Databases from Master to Slave in a Replicated Environment Simplified

Introduction

Legacy systems that were built in a standalone infrastructure mostly suffer architecture design loose-ends in foreseeing effects on a distributed and replicated environment regardless of what technology is used. Systems using the community edition of MySQL as database are good examples of having to encounter mind-boggling challenges in moving to distributed.
In this particular topic, I will be discussing MySQL replication challenges and efficient ways in making sure that a Master (Publisher) is always synchronized with its Slaves (Subscribers) satisfying most of the following,

1. Can be verified fast, lock-free and consistent
2. Has acceptable overhead (CPU resources, memory, time to process)
3. Can provide a tangible report for the synchronicity verification
4. Doesn’t break the replication
5. Doesn’t involve moving raw files or SQL script dump to perform synchronization.

The Strategy

Comparing the Master and Slave requires that we capture the state of each of the databases and then comparing them. The state of a database pertains to,

1. Current Schema
2. Current Data

Getting the Current MySQL Schema(s)

To get the current schema, we will use the “mysqldump” utility that comes with the MySQL client package. We are only interested with the schema that's why the use of the option "-d" is important. For more information on the available options type "mysqldump --help". In this exercise, we will perform the schema dump for both the master and slave. You may do it in a single terminal of your choice given that you will be specifying server hostnames anyway.

Do a schema dump on the master server,

# mysqldump -d -B [database name] -h [slave hostname] --password=[password] > master.sql

Do the same thing on the slave(s),

# mysqldump -d -B [database name] -h [slave hostname] --password=[password] > 
slave.sql

Repeat the exercise on all databases that you want to compare.


Comparing the Schemas

To compare the schemas, you can use a file differencing tool like the shell tool "diff", eclipse IDE, vimdiff etc. "diff" tool Example:

# diff slave.sql master.sql

(Sorry that it took so long update this. I have been so busy lately)

Comparing Data Between Master and Slave on Mysql

Related Links:

• Related Links Widget for Blogspot