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().
One good way to pull off something of that nature is to set up Master-Master Replication or Circular Replication. This is not to be confused with MultiMaster Replciation.
Setting up Circular Replication is actaully very easy if you have setup Master-Slave Replication. Here is what you need to do in order to configure it.
For this example, we will assume Master-Slave Replication is active but you will experience a bit of downtime (1-2 minutes):
Step 1) Add this line to /etc/my.cnf on the Master.
log-slave-updates
Step 2) Add these line to /etc/my.cnf on the Slave:
log-bin=mysql-bin (or have whatever the master has for this)
log-slave-updates
WARNING : Here is the brief moment of downtime !!!
Step 3) On the Slave, service mysql restart
This will activate binary logs on the Slave
Step 4) On the Master, service mysql stop
Step 5) Use rsync to copy the /var/lib/mysql folder of the Slave to the Master.
WARNING : Here is the longer moment of downtime !!!
Step 6) On the Slave, service mysql stop
Step 7) On the Slave, find out the last binary log
Step 8) On the Slave, find out the filesize of the last binary log
Step 9) Use rsync to copy the /var/lib/mysql folder of the Slave to the Master. This should be a faster copy.
Step 10) On the Master, edit
Line 2 of master.info with the last binary log of the Slave.
Line 3 of master.info with the filesize of the last binary log of the Slave.
Line 4 of master.info with the IP of the Slave.
Line 5 is the userid of replication user (DO NOT TOUCH)
Line 6 is the password of replication user (DO NOT TOUCH)
Step 11) Delete all binary logs and binary log index file of the Master.
Step 12) On the Slave, service mysql start, wait 15 seconds
Step 13) On the Master, service mysql start
Step 14) On the Master, run STOP SLAVE; SHOW MASTER STATUS;
Step 15) On the Slave, run CHANGE MASTER TO MASTER_HOST='IP of Slave',MASTER_USER='userid of replication user from Step10',MASTER_PASSWORD='password of replication user from Step10',MASTER_LOG_FILE='binary log from Step14',MASTER_LOG_POS=LogPos from Step14.
Step 16) On the Slave, run START SLAVE;
Step 17) On the Master, run START SLAVE;
I performed steps similar to this for another StackExchange question I answered.
Give it a Try !!!
Best Answer
The answer is yes. What is actually happening is that the slave receives binary patch files and applies those. It is slightly complicated but in general your slave can be offline temporarily and then later catch up. I would imagine that there are limits in the amount of time that can pass but in the event that it falls too far behind you can use WAL segments to catch up (as I understand it, unarchived wal segments are the primary key here, so if you pass the point where you need archived segments to catch up, you will need those segments accessible via log shipping or the like).