asked on

Myaql: Master Master replication

I have Master-master replication setup.
I am getting the errors 1032/1062 . knowing that I don't have an auto_increment column key on any table => I didn't set the variables for auto_increment_offset and auto_increment_increment.

I have "log_slave_updates" set in my.cnf file on both masters

could this ("log_slave_updates" ) be what causes replication to break, since both masters writes to their bin log files and they get updated from each other.

I appreciate your help.

Thanks

Phil Davidson

You may be on to something. To reduce errors, some people recommend getting the MySQL master database fully backed up. In a MASTER-MASTER configuration, there can be more problems. It isn't as cut-and-dried with two master nodes. I would see if you could reproduce the configuration in a Dev or QA environment.

CCVOIP

ASKER

It is the same problem on Dev and QA environment ( I have the same configuration there too) !

Phil Davidson

I would modify my.cnf (my.conf?) file in a Dev environment.

introduce this line:
slave-skip-errors=1062,1032

Bounce the mysql server services

# service mysql restart

Verify if replication starts and works

If you can do testing, you may want to remove the slave-skip-errors=1062,1032 stanza from the configuration file. Then bounce the services again. The errors may go away completely yet errors 1062 and 1032 will still alert you in the future.

CCVOIP

ASKER

Exactly, I have done all of that.

I just want to know if "log_slave_updates" is required in master-master replication?

Thanks

CCVOIP

ASKER

Hi all,

After more digging on the duplicate issue, I found that at some point there was deletes that did not make it to the slave.

I checked if they are logged in "mysql-bin log files" and they were there, but the slave did not pickup the deletes, and when there is another insert for the same unique key, it gets inserted on the master and throw the error of duplicate unique key on the slave.

Any idea why relay-logs (or slave) did not execute the deletes?

Any help would be appreciated.

Thanks

Phil Davidson

Possible causes: There is a "BEFORE DELETE" trigger on the slave database. Maybe there is some no delete constraint on the slave database tables.

Would a redesign of the tables be possible? A "disabled" column could be used to mark the databases for deletion. Then you could periodically delete any column that is disabled on both instances.

Can you run this?

SHOW SLAVE STATUS\G

Open in new window

The results may or may not show something relevant (e.g., the relay file isn't working correctly).

I think the log that is getting replicated over isn't from the binary log. That is normally how replication works. But the deletes aren't working.

I'm curious what would happen if you purged the binary logs in dev and started over. I'm also curious if you could truncate the table in the slave in dev and started over.

CCVOIP

ASKER

there is no triggers on the slave side. both hosts are designed in the same way and contains the exact same schema.

SHOW SLAVE STATUS \G

Open in new window

==> usually gives errors for a duplicate unique key. and when I tracked it, I found out that those records got deleted from the master but the slave didn't pickup the changes.

I usually bring back the replication by tacking a dump from the active host to slave and then reset the replication parameters.

This issue happened many times with the same behavior, for this I want to track the root cause!!

Note: we have Keepalived installed to manage a switch over to the slave if something should happen on the active host. we noticed that keepalived switches even though mysql on the active host up and running fine, and when the switch happens there is some active session that are left behind(keep in mind that we have active/active replication) . should this be the problem.

Thanks

ASKER CERTIFIED SOLUTION

Phil Davidson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial