Master Slave Replication

[nets]

I was creating a master slave replication on nagios. The issue that I have is that, once all the configuration is completed, the slave does try and synch with the master, but when I issue the command show slave status\G, it returns the following error:

Slave_IO_Running: yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '1-81-2010-06-27 06:23:01-239871' for key 2' on query. Default database: 'nagios'. Query: 'INSERT INTO nagios_hostchecks SET instance_id='1', host_object_id='81', check_type='0', is_raw_check='0', current_check_attempt='1', max_check_attempts='10', state='0', state_type='1', start_time=FROM_UNIXTIME(1277637781), start_time_usec='239871', end_time=FROM_UNIXTIME(1277637791), end_time_usec='123718', timeout='30', early_timeout='0', execution_time='0.021770', latency='0.239000', return_code='0', output='', perfdata='(null)', command_object_id='1', command_args='', command_line='' ON DUPLICATE KEY UPDATE instance_id='1', host_object_id='81', check_type='0', is_raw_check='0', current_check_attempt='1', max_check_attempts='10', state='0', state_type='1', start_time=FROM_UNIXTIME(1277637781), start_time_usec='239871', end_time=FROM_UNIXTIME(1277637791), end_time_usec='123718', timeout='30', early_timeout='0', execution_time='0.021770', latency='0.239000', return_code='0', output='', perfdata='
Skip_Counter: 0

Before this error, the Slave_sql_running is YES. I went and deleted the table causing the error from both master and slave just for testing, and then it gave the same error for another table, I deleted that as well, then it started replicating with no issues. I was hoping someone would be able to help me out with this issue, I just need to know why these two tables, nagios_hostchecks and nagios_timedeventqueue, are generating errors on replication, and what can I do to resolve this. I would really appreciate any advice that anyne can give me.

Thank You
Hani Masood

[mmestnik]

Take a step back and explain why you are trying to do this, I have a feeling that you are just seeing the tip of the Iceberg you are mounting.

This is the ndodb database, Postgres correct?, it should not be replicated instead the Nagios data should be shared with NSCA and each instance maintain there own state independently of each other.

The MySQL database could be replicated, AFAIK, however there are several tasks that need to be performed outside the database after updates.

[nets]

Well I am trying to replicate the nagios database between two master servers, I have figured out a solution and I'll share it with you. I created two nagios servers as masters and started replication between them, what I figured out was that in a master master replication scenario, I needed to turn off the nagios service on one server because I am also replicating the .cfg files between the two. If I have the same devices added in both servers (for redundancy purposes) then both nagios servers will be pinging and probing those devices, and there the data in the DB can be replicated. So, I cleared out the database on one server and shut off the nagios service, did a stop slave; and the load data from master; and then start slave; and since then everything has been working smoothly.

I have described all my steps so that you can advise me on wht I did wrong or right, since I am not a database person and have no experience with mysql. Please let me know if I am missing anything or if I am doing anything wrong.

Thank You

[mguthrie]

Here is a resource you might find useful:
http://www.doubletake.com/

The scenario you're using does create a high potential for problems to arise. We're still trying to understand the overall purpose and ultimate goals of replicating the database between two master servers. It would give us a better ability to advise if we could understand the bigger picture.