Mysql – Migration MySQL to Cloud Environment replication

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

If you are comfortable with a maintenance window, here is what you can do:

Step 01) You need to add this line to my.cnf on Server1

[mysqld]
log-bin-mysql-bin
expire-logs-days=7

Step 02) Restart mysql so that no one else can login to Server1

service mysql restart --skip-networking

Step 03) Dump everything and gzip it

DUMPFILE=/root/MySQLData.sql.gz
mysqldump --master-data=2 --single-transaction --flush-privileges -uroot -p --routines --triggers --all-databases | gzip > ${DUMPFILE}

Step 04) Restart mysql on Server1 to allow normal connectivity

service mysql restart

Step 05) Move the ZIP file to the new server (Server2) in /root

Step 06) Unzip the file on Server2

gzip -d /root/MySQLData.sql

Step 07) Login into mysql on Server2

mysql -uroot -p

Step 08) Load the data from the mysql prompt on Server2

mysql> source /root/MySQLData.sql

That's it. That makes all the data as of that dump available on Server2

If you wanted to establish MySQL Replication so that everything written on the Master immediately gets done on the Slave, the steps are a little different. For this example, you will need the Private IP address of Server1. You can get that with ip addr show. Let's suppose the Private IP address of Server1 is 10.1.2.20.

Step 01) You need to add this line to my.cnf on Server1

[mysqld]
log-bin-mysql-bin
expire-logs-days=7

Step 02) Restart mysql so that no one else can login to Server1

service mysql restart --skip-networking

Step 03) Create a Replication Username in mysql client:

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO repluser@'10.1.2.20' IDENTIFIED BY 'replpass';

Step 04) Dump everything and gzip it

DUMPFILE=/root/MySQLData.sql.gz
mysqldump --master-data=1 --single-transaction --flush-privileges -uroot -p --routines --triggers --all-databases | gzip > ${DUMPFILE}

Step 05) Restart mysql on Server1 to allow normal connectivity

service mysql restart

Step 06) Move the ZIP file to the new server (Server2) in /root

Step 07) Unzip the file on Server2

gzip -d /root/MySQLData.sql

Step 08) Login into mysql on Server2

mysql -uroot -p

Step 09) Setup replication in mysql client:

CHANGE MASTER TO
MASTER_HOST='10.1.2.20',
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000000',
MASTER_LOG_POS=2;

Don't worry about log file and position. They are bogus. The mysqldump has the correct log file and position at line 22. In order to see run this:

head -22 source /root/MySQLData.sql | tail -1

Step 10) Load the data from the mysql prompt on Server2

mysql> source /root/MySQLData.sql

Step 11) Start replication

START SLAVE; SELECT SLEEP(3); SHOW SLAVE STATUS\G

If Slave_IO_Running=Yes and Slave_SQL_Running=Yes, you are done.