I've recently taken on a sys admin position managing a group of 20 (or so) servers. One thing I haven't dealt with before (apart from in a test situation), is introducing a new slave to the MySQL replication farm.
Basically the replication is set up like so:
MS -> SL1 -> SL2 (backup) | SL3 (reporting) SB2 SL4 (loadbalanced web slave) SL5 (loadbalanced web slave) SL6 (loadbalanced web slave) SL7 (loadbalanced web slave)
But basically its a Master with two read slaves, (one purely for backup), one for the main read slave (master in waiting, if you will), with 6 slaves behind it, used for an additional backup, 4 load balanced web read slaves and a server used for reporting.
I've done much reading online, and assumed adding a new slave to the environment (from SL1), would be to:
- Log onto SL2:
FLUSH TABLES WITH READ LOCK;
- (copy mysql directory to new server)
- upon completion,
UNLOCK TABLES;, and then
START SLAVE;(all fine up until this point, SL2 comes back online and catches up)
- made sure db structure is correct on new server and master.info points at the right place (pointing correctly at SL1)
- Start MySQL on the new server, checked that Slave SQL and I/O is running (yes, this is fine)
However, after a period, I get duplicate key insert errors from the binary log:
110122 17:01:25 [ERROR] Slave SQL: Error 'Duplicate entry '2011-01-22 17:00:01' for key 'PRIMARY'' on query. Default database: 'thelm_soft'. Query: 'INSERT INTO thm_member_views ( member_view_ts, logged_in_members, non_members ) VALUES ( now(), '27037', '132834' )', Error_code: 1062 110122 17:01:25 [Warning] Slave: Duplicate entry '2011-01-22 17:00:01' for key 'PRIMARY' Error_code: 1062
Is this an issue with the fact now() is being in a query, which common sense tells me would cause issues if the inserts on that table were frequent enough??!?! Or have I got the process with replication wrong, i.e starting at an incorrect log position?