1

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

Improve this question

2 Answers 2

Reset to default
1

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.

Improve this answer
2
  • 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 Commented Mar 20, 2012 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 Commented Mar 23, 2012 at 9:00
0

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?

Improve this answer
2
  • 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" Commented Mar 20, 2012 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. Commented Mar 20, 2012 at 16:24

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.