MySQL Replication - Introduce new Slave to replication

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:

• STOP SLAVE;

• 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??

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??!?!

Yes, I believe this is the cause of your errors. Your method for introducing the new slave seems to be correct. It is quite strange in my opinion to define a table with a DATETIME field as the Primary Key. As you've quite rightly pointed out, the slave gets the replicated queries from the master and they will use the now() keyword in the queries, which will grab the timestamp from the local server.

Really, the table should be defined with some other data type for the PK (such as INT or BIGINT) which can be guaranteed to be unique, unlike a timestamp inserted with now().

StackExchange Database Administrators Q#798, answer score: 5