All excellent information. I have much to ponder. -----Original Message----- From: Eric Braswell [mailto:[EMAIL PROTECTED] Sent: Thursday, April 20, 2006 11:20 AM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: How to Verify Replication Status?
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]
