XML Feeds
Good time to not use mysql replication
If you insert data using server side metadata like now(), think about the implications in a multi-machine database spread specially when you have load.
These sort of problems are somewhat reduced when using rowlevel / binary replication rather than just sql replay. But think about the implications this is going to have across the entire system, the application and specially think about how you expect data in those fields to be used in the future.
- KB
08/Dec/2010 11:50:31 am, 3 comments
Comment from: Jeffrey Hulten [Visitor]
This is an issue when using statement replication. Personally I prefer row replication, which sends the changed data instead of the SQL.To change this, set "binlog_format" to ROW. This will increase the size of your log files and the amount of data being replicated, but I have never run into a problem with this on a local network.
Alternately you can set "binlog_format" to MIXED, which uses statement replication for safe events, and row replication for unsafe events.
binlog_format information: http://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html#sysvar_binlog_format
Replication format information: http://dev.mysql.com/doc/refman/5.1/en/replication-formats.html
08/Dec/2010 @ 12:54
As far as I know, now() is replication safe. When you run now(), the timestamp also goes to the binary log, so that you'll have the exact value on the slaves.
08/Dec/2010 @ 13:08
Feedback seems to indicate that now() should be replication safe, so I'm not entirely sure why the problem came up. The sort term ( and since it was a production db ) fix was to stop using now() and send real timestams' from the code in the sql, that resolves the problem quite nicely.- KB
08/Dec/2010 @ 13:11
This post has 11 feedbacks awaiting moderation...