RTFM && WTFN - backup

User Tools

Site Tools

Trace: • Быстрые заметки по MySQL • Ремонт mysql базы nagios • Работа zabbix-прокси за NAT • Быстрые заметки по git • Работа с mdadm
topic:linux_repare_nagios_db

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision		Previous revision
topic:linux_repare_nagios_db [2017/06/12 00:13] – created ktopic:linux_repare_nagios_db [2021/08/12 08:35] (current) – external edit 127.0.0.1
Line 1: Line 1:
-==== Ремонит ====+==== Ремонт mysql базы nagios ===== 
 + 
 +=== Описание проблемы ===
  
 Сломались таблицы в mysql базе в которую писались данные системы мониторинга Nagios.\\ Сломались таблицы в mysql базе в которую писались данные системы мониторинга Nagios.\\
-Сам nagios работал, но в лог (/var/log/messages) огромными количествами сыпались подобные сообщения:+Сам nagios работал, но в лог огромными количествами сыпались сообщения об ощибках ndo2db. 
 +<hidden Ошибки ndo2db> 
 +<WRAP prewrap 500>
 <code> <code>
 2017-04-11T13:49:25.025157+03:00 msk-02-noc ndo2db: mysql_error: 'Table './nagios_db/nagios_hoststatus' is marked as crashed and should be repaired' 2017-04-11T13:49:25.025157+03:00 msk-02-noc ndo2db: mysql_error: 'Table './nagios_db/nagios_hoststatus' is marked as crashed and should be repaired'
Line 8: Line 12:
 2017-04-11T13:49:25.025167+03:00 msk-02-noc ndo2db: Error: mysql_query() failed for 'INSERT INTO nagios_hoststatus SET instance_id='1', host_object_id='207', status_update_time=FROM_UNIXTIME(1491907765), output='PING OK - Packet loss = 0%, RTA = 61\.61 ms', long_output='', perfdata='rta=61\.612000ms;3000\.000000;5000\.000000;0\.000000 pl=0%;80;100;0', current_state='0', has_been_checked='1', should_be_scheduled='1', current_check_attempt='1', max_check_attempts='3', last_check=FROM_UNIXTIME(1491907746), next_check=FROM_UNIXTIME(1491907807), check_type='0', last_state_change=FROM_UNIXTIME(1491247748), last_hard_state_change=FROM_UNIXTIME(1489995197), last_hard_state='0', last_time_up=FROM_UNIXTIME(1491907764), last_time_down=FROM_UNIXTIME(1491247676), last_time_unreachable=FROM_UNIXTIME(1491237594), state_type='1', last_notification=FROM_UNIXTIME(0), next_notification=FROM_UNIXTIME(0), no_more_notifications='0', notifications_enabled='1', problem_has_been_acknowledged='0', acknowledgement_type='0', current_notification_number='0', passive_checks_enabled='1', active_checks_enabled='1', event_handler_enabled='1', flap_detection_enabled='0', is_flapping='0', percent_state_change='0.000000', latency='6.762000', execution_time='2.420050', scheduled_downtime_depth='0', failure_prediction_enabled='1', process_performance_data='1', obsess_over_host='1', modified_host_attributes='0', event_handler='', check_command='check-host-alive', normal_check_interval='1.000000', retry_check_interval='0.100000', check_timeperiod_object_id='2' ON DUPLICATE KEY UPDATE instance_id='1', host_object_id='207', status_update_time=FROM_UNIXTIME(1491907765), output='PING OK - Packet loss = 0%, RTA = 61\.61 ms', long_output='', perfdata='rta=61\.612000ms;3000\.000000;5000\.000000;0\.000000 pl=0%;80;100;0', current_state='0', has_been_checked='1', should_be_scheduled='1', current_check_attempt='1', max_check_attempts='3', last_check=FROM_UNIXTIME(1491907746), next_check=FROM_UNIXTIME(1491907807), check_type='0', last_state_change=FROM_UNIXTIME(1491247748), last_hard_state_change=FROM_UNIXTIME(1489995197), last_hard_state='0', last_time_up=FROM_UNIXTIME(1491907764), last_time_down=FROM_UNIXTIME(1491247676), last_time_unreachable=FROM_UNIXTIME(1491237594), state_type='1', last_notification=FROM_UNIXTIME(0), next_notification=FROM_UNIXTIME(0), no_more_notifications='0', notifications_enabled='1', problem_has_been_acknowledged='0', acknowledgement_type='0', current_notification_number='0', passive_checks_enabled='1', active_checks_enabled='1', event_handler_enabled='1', flap_detection_enabled='0', is_flapping='0', percent_state_change='0.000000', latency='6.762000', execution_time='2.420050', scheduled_downtime_depth='0', failure_prediction_enabled='1', process_performance_data='1', obsess_over_host='1', modified_host_attributes='0', event_handler='', check_command='check-host-alive', normal_check_interval='1.000000', retry_check_interval='0.100000', check_timeperiod_object_id='2'' 2017-04-11T13:49:25.025167+03:00 msk-02-noc ndo2db: Error: mysql_query() failed for 'INSERT INTO nagios_hoststatus SET instance_id='1', host_object_id='207', status_update_time=FROM_UNIXTIME(1491907765), output='PING OK - Packet loss = 0%, RTA = 61\.61 ms', long_output='', perfdata='rta=61\.612000ms;3000\.000000;5000\.000000;0\.000000 pl=0%;80;100;0', current_state='0', has_been_checked='1', should_be_scheduled='1', current_check_attempt='1', max_check_attempts='3', last_check=FROM_UNIXTIME(1491907746), next_check=FROM_UNIXTIME(1491907807), check_type='0', last_state_change=FROM_UNIXTIME(1491247748), last_hard_state_change=FROM_UNIXTIME(1489995197), last_hard_state='0', last_time_up=FROM_UNIXTIME(1491907764), last_time_down=FROM_UNIXTIME(1491247676), last_time_unreachable=FROM_UNIXTIME(1491237594), state_type='1', last_notification=FROM_UNIXTIME(0), next_notification=FROM_UNIXTIME(0), no_more_notifications='0', notifications_enabled='1', problem_has_been_acknowledged='0', acknowledgement_type='0', current_notification_number='0', passive_checks_enabled='1', active_checks_enabled='1', event_handler_enabled='1', flap_detection_enabled='0', is_flapping='0', percent_state_change='0.000000', latency='6.762000', execution_time='2.420050', scheduled_downtime_depth='0', failure_prediction_enabled='1', process_performance_data='1', obsess_over_host='1', modified_host_attributes='0', event_handler='', check_command='check-host-alive', normal_check_interval='1.000000', retry_check_interval='0.100000', check_timeperiod_object_id='2' ON DUPLICATE KEY UPDATE instance_id='1', host_object_id='207', status_update_time=FROM_UNIXTIME(1491907765), output='PING OK - Packet loss = 0%, RTA = 61\.61 ms', long_output='', perfdata='rta=61\.612000ms;3000\.000000;5000\.000000;0\.000000 pl=0%;80;100;0', current_state='0', has_been_checked='1', should_be_scheduled='1', current_check_attempt='1', max_check_attempts='3', last_check=FROM_UNIXTIME(1491907746), next_check=FROM_UNIXTIME(1491907807), check_type='0', last_state_change=FROM_UNIXTIME(1491247748), last_hard_state_change=FROM_UNIXTIME(1489995197), last_hard_state='0', last_time_up=FROM_UNIXTIME(1491907764), last_time_down=FROM_UNIXTIME(1491247676), last_time_unreachable=FROM_UNIXTIME(1491237594), state_type='1', last_notification=FROM_UNIXTIME(0), next_notification=FROM_UNIXTIME(0), no_more_notifications='0', notifications_enabled='1', problem_has_been_acknowledged='0', acknowledgement_type='0', current_notification_number='0', passive_checks_enabled='1', active_checks_enabled='1', event_handler_enabled='1', flap_detection_enabled='0', is_flapping='0', percent_state_change='0.000000', latency='6.762000', execution_time='2.420050', scheduled_downtime_depth='0', failure_prediction_enabled='1', process_performance_data='1', obsess_over_host='1', modified_host_attributes='0', event_handler='', check_command='check-host-alive', normal_check_interval='1.000000', retry_check_interval='0.100000', check_timeperiod_object_id='2''
 </code> </code>
 +</WRAP>
 +</hidden>\\
  
 +С помощью awk в логах mysql ищем имена сломашихся таблиц.
 +<code>
 +[root@msk-02-noc log]# grep  'crashed and should be repaired' mysqld.log | awk '{print $6}' | sort | uniq
 +'./nagios_db/nagios_hoststatus'
 +'./nagios_db/nagios_servicechecks'
 +'./nagios_db/nagios_systemcommands'
 +'./nagios_db/nagios_timedeventqueue'
 +</code>
 +
 +=== Решение ===
 +Останавливаем nagios и с помощью mysqlcheck чиним таблицы.
 +<hidden Процесс работы mysqlcheck>
 +<code>
 +[root@msk-02-noc nagios]# mysqlcheck -unagios_user -p --repair --extended nagios_db
 +Enter password:
 +nagios_db.nagios_acknowledgements                  OK
 +nagios_db.nagios_commands                          OK
 +nagios_db.nagios_commenthistory                    OK
 +nagios_db.nagios_comments                          OK
 +nagios_db.nagios_configfiles                       OK
 +nagios_db.nagios_configfilevariables               OK
 +nagios_db.nagios_conninfo                          OK
 +nagios_db.nagios_contact_addresses                 OK
 +nagios_db.nagios_contact_notificationcommands      OK
 +nagios_db.nagios_contactgroup_members              OK
 +nagios_db.nagios_contactgroups                     OK
 +nagios_db.nagios_contactnotificationmethods        OK
 +nagios_db.nagios_contactnotifications              OK
 +nagios_db.nagios_contacts                          OK
 +nagios_db.nagios_contactstatus                     OK
 +nagios_db.nagios_customvariables                   OK
 +nagios_db.nagios_customvariablestatus              OK
 +nagios_db.nagios_dbversion                         OK
 +nagios_db.nagios_downtimehistory                   OK
 +nagios_db.nagios_eventhandlers                     OK
 +nagios_db.nagios_externalcommands                  OK
 +nagios_db.nagios_flappinghistory                   OK
 +nagios_db.nagios_host_contactgroups                OK
 +nagios_db.nagios_host_contacts                     OK
 +nagios_db.nagios_host_parenthosts                  OK
 +nagios_db.nagios_hostchecks                        OK
 +nagios_db.nagios_hostdependencies                  OK
 +nagios_db.nagios_hostescalation_contactgroups      OK
 +nagios_db.nagios_hostescalation_contacts           OK
 +nagios_db.nagios_hostescalations                   OK
 +nagios_db.nagios_hostgroup_members                 OK
 +nagios_db.nagios_hostgroups                        OK
 +nagios_db.nagios_hosts                             OK
 +nagios_db.nagios_hoststatus
 +info     : Duplicate key  2 for record at 13480 against new record at 0
 +warning  : Number of rows changed from 268 to 267
 +status   : OK
 +nagios_db.nagios_instances                         OK
 +nagios_db.nagios_logentries                        OK
 +nagios_db.nagios_notifications                     OK
 +nagios_db.nagios_objects                           OK
 +nagios_db.nagios_processevents                     OK
 +nagios_db.nagios_programstatus                     OK
 +nagios_db.nagios_runtimevariables                  OK
 +nagios_db.nagios_scheduleddowntime                 OK
 +nagios_db.nagios_service_contactgroups             OK
 +nagios_db.nagios_service_contacts                  OK
 +nagios_db.nagios_servicechecks
 +warning  : Number of rows changed from 1095125 to 1094998
 +status   : OK
 +nagios_db.nagios_servicedependencies               OK
 +nagios_db.nagios_serviceescalation_contactgroups   OK
 +nagios_db.nagios_serviceescalation_contacts        OK
 +nagios_db.nagios_serviceescalations                OK
 +nagios_db.nagios_servicegroup_members              OK
 +nagios_db.nagios_servicegroups                     OK
 +nagios_db.nagios_services                          OK
 +nagios_db.nagios_servicestatus                     OK
 +nagios_db.nagios_statehistory                      OK
 +nagios_db.nagios_systemcommands
 +info     : Wrong bytesec:  23- 92-183 at 92223440; Skipped
 +info     : Wrong bytesec: 108-105- 98 at 395057336; Skipped
 +info     : Wrong block with wrong total length starting at 91145760
 +info     : Found block with too small length at 91146520; Skipped
 +info     : Found block with too small length at 91430372; Skipped
 +info     : Found block with too small length at 91430380; Skipped
 +info     : Wrong bytesec: 117-115-114 at 91648060; Skipped
 +info     : Wrong bytesec: 112-108- 15 at 485197884; Skipped
 +info     : Found block with too small length at 399791272; Skipped
 +info     : Wrong bytesec: 111-115- 47 at 91647448; Skipped
 +warning  : Number of rows changed from 3876808 to 3871726
 +status   : OK
 +nagios_db.nagios_timedeventqueue
 +warning  : Number of rows changed from 1009 to 948
 +status   : OK
 +nagios_db.nagios_timedevents                       OK
 +nagios_db.nagios_timeperiod_timeranges             OK
 +nagios_db.nagios_timeperiods                       OK
 +</code>
 +</hidden>
  
 +{{tag>mysql nagios}}
topic/linux_repare_nagios_db.1497215632.txt.gz · Last modified: (external edit)
Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki