You'll need to checksum them at the same point in time, naturally --
which you can do by stopping replication on the slave, then performing
the command on the master, then slave, and compare.
In other words, if you are doing this on a live system, you have to make
sure there are no writes in between the two checksums... but I'm sure
that's clear.
The one problem with using CHECKSUM TABLE is that a row-by-row
comparison takes some time and necessarily forces a table lock, so
performing this on a live system can be problematic if you are expecting
frequent writes. You might consider adding the live checksum to your
tables for that.
See: http://dev.mysql.com/doc/refman/5.0/en/create-table.html
A data dump, on the other hand, can be fairly quick and would allow you
to perform the checksum without interfering with any other operations. I
don't know the details of your situation (or the size of your data), but
I'm fairly sure that that's how I would do it.
Another possibility is to do a checksum directly on the database files
if you can do so while the server is not updating anything, which would
avoid the dump, and is extremely fast. You could have a shell script
lock the tables, do an md5sum on the .MYD and maybe .frm files (for
MyISAM), then unlock. I can't off-hand think of any reason this wouldn't
work well as long as the slave and master are configured identically and
are the same version.
Eric
--
Eric Braswell
Web Manager MySQL AB
Cupertino, USA
Robinson, Eric wrote:
Eric, that is very helpful. Thanks.
Assuming the master and slave are in sync, is there a reason the
checksums would not match? I would rather not dump the database and run
an external checksum unless I have to.
--
Eric Robinson
Director of Information Technology
Physician Select Management, LLC
775.720.2082
-----Original Message-----
From: Eric Braswell [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 20, 2006 1:49 AM
To: Robinson, Eric
Cc: mysql@lists.mysql.com
Subject: Re: How to Verify Replication Status?
There are only a very limited set of circumstances where slaves could
get out of sync, and if everything is set up right, it basically should
not happen.
See: http://dev.mysql.com/doc/refman/5.0/en/replication-rules.html
And: http://dev.mysql.com/doc/refman/5.0/en/replication-features.html
CHECKSUM TABLE is a good option if it's a read-only table or you can
stop writes (or just replication) long enough to do that. It only works
on MyISAM.
You could also do basically the same thing by dumping the data in the
same way on each server, and running a checksum (e.g. md5sum) or diff
tool. One thing I have done is to use:
mysqldump --skip-opt {database} > {database}.sql
..on each machine, then diffed the files using "diff" (note *nix bias
here). Using skip-opt to output inserts on individual lines allows you
to compare the data to see exactly where any differences are. But this
won't help you if you can't transfer all the data to one place -- you
could just do a checksum then on both sides and compare that. Would be
pretty easy to script that and perform periodic checks.
Eric
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
