Using mysql replication: major problem

[emx]

We have a Zabbix server located in Europe that has a VPN link to another server in Asia. We need to monitor from Asia.

The connection in Asia is unreliable and slow. Using the web interface on the European server from Asia is just too slow to be useful, so we needed a better solution.

I have setup the Europe mysql database as a master, and setup replication to the mysql database in Asia. I've done that properly and verified that it worked. Works fine at first.

So now in Asia we are able to use a local web server with this replicated database. It is really fast. We're very happy with this solution.

And then it stopped working.

Upon investigation, the slave (the Asian server) complains that:

Error 'Duplicate entry '21' for key 'PRIMARY'' on query. Default database: 'zabbix'. Query: 'INSERT INTO auditlog (auditid,userid,clock,action,resourcetype,details,ip)  VALUES (21,3,1284105286,6,11,'User status for group name [Disabled]','10.130.0.3')'

What happens is (I think) that the Asian mysql gets data (e.g. from php frontend) and updates its local database. Then the master (Europe) sends a replication update and there is a 'unique' constraint that is violated.

I wanted to know if anyone has worked with a similar setup (using a replicated DB) and if there is a solution.

Alternatively, if you have any suggestions on how to do this differently, I'm interested.

The key points:
- We can't run the Zabbix server in Asia because the connection there is highly unreliable and we might loose events simply because our connection is unstable.
- We therefore run it in Europe. But then when monitored from Asia, the VPN connection is so slow that even the most patient monitoring staff would give up after 10 minutes.

Thank you for your insights.

[James Wells]

Greetings,

Upon investigation, the slave (the Asian server) complains that:

Error 'Duplicate entry '21' for key 'PRIMARY'' on query. Default database: 'zabbix'. Query: 'INSERT INTO auditlog (auditid,userid,clock,action,resourcetype,details,ip)  VALUES (21,3,1284105286,6,11,'User status for group name [Disabled]','10.130.0.3')'

I have asked a couple DBA's about this previously... Sadly, I have never gotten an answer. Basically what you want to do is replicate only parts of the DB, not the whole thing. Some of the stuff you want to replicate are;

• History* -- These are where Zabbix stores historically accurate data about each item.
• Trend* -- These are where Zabbix stores 30 minute statistical data about each item.
• Items -- This is where Zabbix stores the item configuration information for each item to be monitored.
• Hosts -- This is where Zabbix stores the host configuration information for each host in the system.
• Triggers -- This is where Zabbix stores the trigger configuration information for each trigger, this includes current status of the trigger.
• Functions -- This is a fun table. This is where Zabbix stores the actual mechanisms that Zabbix uses to evaluate the triggers.

Please note that that was not meant to be a comprehensive list of tables to replicate, just an absolute minimum.

Alternatively, if you have any suggestions on how to do this differently, I'm interested.

I haven't played with it yet, but supposedly this is what Zabbix's distributed monitoring architecture was meant for. For the last 5 years, I have been using the clustering system that I wrote for Zabbix back in the pre-1.1 days.

The idea would be that you create a slave server in Asia and have it aggregate / process data and send it in bulk to the master server in Europe. Have the slave do notifications for local events, but have the Europe server manage escalations. This way you will get notified one way or the other of an issue... May be delayed, but you will still get notified.

[tvirtualw]

You would need to setup a read-only DB user for the slave DB. But I doubt Zabbix supports that - I could imagine errors would be thrown that access is denied when the Asian frontend tries to write records to the slave.

Filtering replications is difficult and can lead to serious problems depending on how the master issues queries and if statement- or row-based replication is used.

If the connection between Europe and Asia is unreliable - isn't the replication unreliable as well? Is your replicated data current? Have you set the retry / connection timeouts accordingly or do you use MySQL defaults?

I would explore alternative possibilities of accessing the European frontend from Asia. Like avoiding VPN and using just an HTTPS link. Or find spots with a better connectivity. Why is it important to monitor in Asia? If connectivity is unreliable, is staff in Asia able to act upon alerts, e.g. if a European server goes down?

[bartelomeus]

The other way arround is the same

Hi,

It seems that when you do a check on the Asian side you will find that the connections are fine.
As long as you stay in that region.
ToConnections with Europe are dramatic.

Bartelomeus.