MySQL Replication – Troubleshooting Insertion Issues with 1 Master and 2 Slaves

auto-incrementMySQLreplicationwamp

I am a Python developer, working with 3 databases, configured in Master -> slave,slave style.

Each slave has their own application associated with it.

I am not too familiar with database administration as it relates to replication, but I do have a working scenario between the Master, and one of the slaves

The first slave stays in synch at all times properly at this time.

The second slave however, fails on the first insert

Table creation queries, user creaiton all replicates fine, but when I input any values, the first slave gets the record, while the second slave does not.

I tried importing the master DB to the second slave, updates to records will replicate, but new records will not.

My research leads me to believe this is an issue with autoincrement but I have not been able to use this to fix the issue. As opposed to a hack and slash method which is not working I figured id ask here.

If there is any more info I can provide, please let me know.

I am on Windows. Using WAMP Server 2.5 (MySQL 5.6.17)

Thanks for reading

EDIT: I am using binlog_format=mixed

Best Answer

Have you set your second slave to server_id = 3 (or depends on you, should be different from 1st slave)

and in mysql of second slave:

CHANGE MASTER TO MASTER_HOST='IP Address of Master Server',
MASTER_USER='slave_user2', 
MASTER_PASSWORD='slave_user_password', 
Below lines will need the values from ‘SHOW MASTER STATUS’ in Master Database
MASTER_LOG_FILE='', 
MASTER_LOG_POS=;
Related Question