Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Though out of my "core" knowledge I maintain a two-way replicated MySQL database (primary and backup). It's been working fine mostly. All changes are almost instantly replicated between the two servers.

But now I've noticed something strange: I have a couple of cases where there are no replication on feb 29th. Admittedly I have not yet confirmed that all replication is lost. But all cases I've found so far have had this issue.

Not too long ago I changed timezone from UTC to CET on the backup, it has been CET on the primary all along.

Am I fixating on this because it happened on the leap day, or could there be something to it?

The servers are both CentOS 5.4 with MySQL 5.0

share|improve this question

2 Answers 2

up vote 1 down vote accepted

I can sense what the concern can be. If everything was UTC, this would not be an issue.

Here is a question to think about:

Do you have the same problems on days that involving daylight saving time?

CET does observe daylight saving time.

Replication issues you described should also happen when the clock goes forward. In theory, this should last about an hour.

You could do one of two things to ensure the same data:

OPTION 1 : mysqldump all data from the primary to the backup

MYSQL_HOST_ROOT=localhost
MYSQL_USER_ROOT=roothome
MYSQL_PASS_ROOT=rootpassword
MYSQL_CONN_ROOT="-h${MYSQL_HOST_ROOT} -u${MYSQL_USER_ROOT} -p${MYSQL_PASS_ROOT}"
MYSQL_HOST_BCKP=localhost
MYSQL_USER_BCKP=rootremote
MYSQL_PASS_BCKP=rootremotepassword
MYSQL_CONN_BCKP="-h${MYSQL_HOST_BCKP} -u${MYSQL_USER_BCKP} -p${MYSQL_PASS_BCKP}"
echo "SET SQL_LOG_BIN=0;" > /root/MySQLData.sql
echo "STOP SLAVE;" >> /root/MySQLData.sql
MYSQLDUMP_OPTIONS="--master-data=1 --all-databases --routines --triggers"
mysqldump ${MYSQL_CONN_ROOT} ${MYSQLDUMP_OPTIONS} >> /root/MySQLData.sql
echo "START SLAVE;" >> /root/MySQLData.sql
mysql ${MYSQL_CONN_BCKP} -A < /root/MySQLData.sql

OPTION 2 : Use pt-table-checksum and pt-table-sync

I wrote up a sample script that uses mk-table-checksum and mk-table-sync but can be applied to pt-table-checksum and pt-table-sync as well.

share|improve this answer
    
The system in question hasn't been online across daylight saving time yet, though we are approaching now so I will keep watch. If this was a one time thing relating to the fact that I changed the clock, dumping the data is probably the best option, if not I would like to understand why it happens so I don't have to copy the database every once in a while –  danneth Mar 20 '12 at 8:00
    
Since we are looking in to upgrading servers anyway, I will simply bite the bullet and do a new dump and restart everything –  danneth Mar 23 '12 at 9:00

Replication typically just walks the binary log step by step. (paying no attention to the actual date)

Are you certain that your masters and slaves hold the actual data?

share|improve this answer
    
Not going in to the specifics of the system, but looking at the change history of an "item". On the master there are actions on feb 28, feb 29, mar 1. But on the backup there are actions only on feb 28 and mar 1. What confuses me is the fact that replication didn't "catch up" –  danneth Mar 20 '12 at 7:57
    
you can use the 'mysqlbinlog' tool to see what was actually put in your binary logs. As I said, the binary logs are what the slave uses to replicate changes made on the master. If you see the statements in the binlog, but not on the slave then that would point to something broken with slaving. If you don't seem the changes in the binlog then there might be a problem with the application writing to the db. –  Joel K Mar 20 '12 at 16:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.