MySQL Replication - Introduce new Slave to replication

Question

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??!?! Or have I got the process with replication wrong, i.e starting at an incorrect log position?

Answer 1

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().

Answer 2

UPDATE

Here is an alternative answer I wrote in another StackExchange site

Original Answer

When you copied the mysql directory, please keep in mind that you also copied the relay logs. All the relay logs are tagged with the server_id of the master. The master tends to make a slave process "hiccup" when two slaves (sibilings) share the same server_id.

MySQL will complain if a master and slave have the smae server_id. MySQL Replication won't even get started in that case. This problem is a little more subtle and not so obvious. Here is why:

If a master has multiple slaves and two or more of those slaves share the same server_id, the slave that is connected first (with the lowerst process ID in the master's processlist) will replicate from the master just fine. All other connections from other slaves with a server_id identical to the first slave can and will disconnect and reconnect intermittently under the hood. This can also somewhat confuse the master since it writes timestamp data in the relay logs. It is probbably safer to clear out the relay logs first.

Instead of just starting replication up after the copy, you could try this:

1. Add skip-slave-start to /etc/my.cnf of this new slave
2. Start mysql on the new slave
3. Run CHANGE MASTER TO on the new slave (This clears all relays generated before, thus different timestamp data in relay logs are available)
4. Remove skip-slave-start from /etc/my.cnf of this new slave
5. Restart mysql

I hope this helps.

Answer 3

FYI- The now() call will use the timestamp from the master in the replication binlog. http://dev.mysql.com/doc/refman/5.5/en/replication-features-functions.html

The answer above MySQL Replication - Introduce new Slave to replication is entirely correct. You are missing the CHANGE MASTER TO.

Why are you copying the entire mysql directory and not just the specific database files? When bringing on a new slave, I usually do a mysql install then bring over the data. You don't need all the master's relay logs and such.

Answer 4

Are you running your master in 'statement-level' binary log format? I'm not 100% sure it would work, but perhaps changing it to row-level format would be fix the error.

The problem with row-level logging is it logs so much more information, you'll have to watch the disk usage and regularly clear out your logs to avoid running out of harddisk space.

Mixed level logging might automatically switch log format to 'row' for the NOW() call, but I've never tried it and the docs don't specifically state that it will switch for NOW(). Might be something to test.