XI local backups not running

[rferebee]

I get the following output when I try to run the second command:

ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)

[benjaminsmith]

Hi @rferebee,

Can you send me your system profile? It should have made a connection to the database. Do you have an offloaded database or perhaps is not running.

To send us your system profile.
Login to the Nagios XI GUI using a web browser.
Click the "Admin" > "System Profile" Menu
Click the "Download Profile" button
Save the profile.zip file and share in a private message or upload it to the post/ticket, and then reply to this post to bring it up in the queue.

[rferebee]

As far as I know the DB isn't offloaded and it is running.

I sent you a PM with my system profile.

[benjaminsmith]

Hi @referebee,

I noticed in the database log that the nagios_logentries table was crashed, but has since been repaired. Are your backups currently working as expected?

The database is local. Can you log into mysql directly?

Code: Select all

mysql -u root -pnagiosxi

If so, run the following command to get the tablesizes and then post the full output.

Code: Select all

use nagios
SELECT table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES;

[rferebee]

Yes, backups are working now. I'm just trying to figure out if there's some sort of regular maintenance we can perform to ensure they table doesn't crash or perhaps a Nagios XI service check I can create to tell me when the table is crashed?

Here's the output you requested:

mysql> SELECT table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES;
+--------------------------------------------+------------+
| Table | Size in MB |
+--------------------------------------------+------------+
| CHARACTER_SETS | 0.00 |
| COLLATIONS | 0.00 |
| COLLATION_CHARACTER_SET_APPLICABILITY | 0.00 |
| COLUMNS | 0.00 |
| COLUMN_PRIVILEGES | 0.00 |
| ENGINES | 0.00 |
| EVENTS | 0.00 |
| FILES | 0.00 |
| GLOBAL_STATUS | 0.00 |
| GLOBAL_VARIABLES | 0.00 |
| KEY_COLUMN_USAGE | 0.00 |
| PARTITIONS | 0.00 |
| PLUGINS | 0.00 |
| PROCESSLIST | 0.00 |
| PROFILING | 0.00 |
| REFERENTIAL_CONSTRAINTS | 0.00 |
| ROUTINES | 0.00 |
| SCHEMATA | 0.00 |
| SCHEMA_PRIVILEGES | 0.00 |
| SESSION_STATUS | 0.00 |
| SESSION_VARIABLES | 0.00 |
| STATISTICS | 0.00 |
| TABLES | 0.00 |
| TABLE_CONSTRAINTS | 0.00 |
| TABLE_PRIVILEGES | 0.00 |
| TRIGGERS | 0.00 |
| USER_PRIVILEGES | 0.00 |
| VIEWS | 0.00 |
| columns_priv | 0.00 |
| db | 0.01 |
| event | 0.00 |
| func | 0.00 |
| general_log | 0.00 |
| help_category | 0.00 |
| help_keyword | 0.10 |
| help_relation | 0.02 |
| help_topic | 0.46 |
| host | 0.00 |
| ndb_binlog_index | 0.00 |
| plugin | 0.00 |
| proc | 0.00 |
| procs_priv | 0.00 |
| servers | 0.00 |
| slow_log | 0.00 |
| tables_priv | 0.00 |
| time_zone | 0.00 |
| time_zone_leap_second | 0.00 |
| time_zone_name | 0.00 |
| time_zone_transition | 0.00 |
| time_zone_transition_type | 0.00 |
| user | 0.00 |
| nagios_acknowledgements | 0.16 |
| nagios_commands | 0.03 |
| nagios_commenthistory | 29.42 |
| nagios_comments | 0.10 |
| nagios_configfiles | 0.00 |
| nagios_configfilevariables | 0.01 |
| nagios_conninfo | 1.39 |
| nagios_contact_addresses | 0.00 |
| nagios_contact_notificationcommands | 0.10 |
| nagios_contactgroup_members | 0.02 |
| nagios_contactgroups | 0.00 |
| nagios_contactnotificationmethods | 6.09 |
| nagios_contactnotifications | 6.47 |
| nagios_contacts | 0.03 |
| nagios_contactstatus | 0.02 |
| nagios_customvariables | 0.10 |
| nagios_customvariablestatus | 0.10 |
| nagios_dbversion | 0.00 |
| nagios_downtimehistory | 3.80 |
| nagios_eventhandlers | 0.01 |
| nagios_externalcommands | 0.02 |
| nagios_flappinghistory | 2.38 |
| nagios_host_contactgroups | 0.04 |
| nagios_host_contacts | 0.02 |
| nagios_host_parenthosts | 0.00 |
| nagios_hostchecks | 0.00 |
| nagios_hostdependencies | 0.00 |
| nagios_hostescalation_contactgroups | 0.05 |
| nagios_hostescalation_contacts | 0.00 |
| nagios_hostescalations | 0.08 |
| nagios_hostgroup_members | 0.06 |
| nagios_hostgroups | 0.01 |
| nagios_hosts | 0.12 |
| nagios_hoststatus | 0.30 |
| nagios_instances | 0.00 |
| nagios_logentries | 138.59 |
| nagios_notifications | 14.46 |
| nagios_objects | 1.02 |
| nagios_processevents | 0.69 |
| nagios_programstatus | 0.00 |
| nagios_runtimevariables | 0.00 |
| nagios_scheduleddowntime | 0.01 |
| nagios_service_contactgroups | 0.23 |
| nagios_service_contacts | 0.09 |
| nagios_service_parentservices | 0.00 |
| nagios_servicechecks | 0.00 |
| nagios_servicedependencies | 0.00 |
| nagios_serviceescalation_contactgroups | 0.28 |
| nagios_serviceescalation_contacts | 0.00 |
| nagios_serviceescalations | 0.53 |
| nagios_servicegroup_members | 0.05 |
| nagios_servicegroups | 0.00 |
| nagios_services | 0.70 |
| nagios_servicestatus | 1.75 |
| nagios_statehistory | 204.12 |
| nagios_systemcommands | 0.04 |
| nagios_timedeventqueue | 0.00 |
| nagios_timedevents | 0.00 |
| nagios_timeperiod_timeranges | 0.08 |
| nagios_timeperiods | 0.02 |
| tbl_command | 0.05 |
| tbl_contact | 0.03 |
| tbl_contactgroup | 0.01 |
| tbl_contacttemplate | 0.01 |
| tbl_domain | 0.01 |
| tbl_host | 0.09 |
| tbl_hostdependency | 0.01 |
| tbl_hostescalation | 0.01 |
| tbl_hostextinfo | 0.00 |
| tbl_hostgroup | 0.02 |
| tbl_hosttemplate | 0.02 |
| tbl_info | 0.13 |
| tbl_lnkContactToCommandHost | 0.00 |
| tbl_lnkContactToCommandService | 0.00 |
| tbl_lnkContactToContactgroup | 0.00 |
| tbl_lnkContactToContacttemplate | 0.01 |
| tbl_lnkContactToVariabledefinition | 0.00 |
| tbl_lnkContactgroupToContact | 0.01 |
| tbl_lnkContactgroupToContactgroup | 0.00 |
| tbl_lnkContacttemplateToCommandHost | 0.00 |
| tbl_lnkContacttemplateToCommandService | 0.00 |
| tbl_lnkContacttemplateToContactgroup | 0.00 |
| tbl_lnkContacttemplateToContacttemplate | 0.00 |
| tbl_lnkContacttemplateToVariabledefinition | 0.00 |
| tbl_lnkHostToContact | 0.01 |
| tbl_lnkHostToContactgroup | 0.02 |
| tbl_lnkHostToHost | 0.00 |
| tbl_lnkHostToHostgroup | 0.01 |
| tbl_lnkHostToHosttemplate | 0.02 |
| tbl_lnkHostToVariabledefinition | 0.01 |
| tbl_lnkHostdependencyToHost_DH | 0.00 |
| tbl_lnkHostdependencyToHost_H | 0.00 |
| tbl_lnkHostdependencyToHostgroup_DH | 0.00 |
| tbl_lnkHostdependencyToHostgroup_H | 0.00 |
| tbl_lnkHostescalationToContact | 0.00 |
| tbl_lnkHostescalationToContactgroup | 0.00 |
| tbl_lnkHostescalationToHost | 0.00 |
| tbl_lnkHostescalationToHostgroup | 0.00 |
| tbl_lnkHostgroupToHost | 0.03 |
| tbl_lnkHostgroupToHostgroup | 0.00 |
| tbl_lnkHosttemplateToContact | 0.00 |
| tbl_lnkHosttemplateToContactgroup | 0.00 |
| tbl_lnkHosttemplateToHost | 0.00 |
| tbl_lnkHosttemplateToHostgroup | 0.00 |
| tbl_lnkHosttemplateToHosttemplate | 0.01 |
| tbl_lnkHosttemplateToVariabledefinition | 0.00 |
| tbl_lnkServiceToContact | 0.05 |
| tbl_lnkServiceToContactgroup | 0.14 |
| tbl_lnkServiceToHost | 0.09 |
| tbl_lnkServiceToHostgroup | 0.00 |
| tbl_lnkServiceToServicegroup | 0.00 |
| tbl_lnkServiceToServicetemplate | 0.04 |
| tbl_lnkServiceToVariabledefinition | 0.02 |
| tbl_lnkServicedependencyToHost_DH | 0.00 |
| tbl_lnkServicedependencyToHost_H | 0.00 |
| tbl_lnkServicedependencyToHostgroup_DH | 0.00 |
| tbl_lnkServicedependencyToHostgroup_H | 0.00 |
| tbl_lnkServicedependencyToService_DS | 0.00 |
| tbl_lnkServicedependencyToService_S | 0.00 |
| tbl_lnkServicedependencyToServicegroup_DS | 0.02 |
| tbl_lnkServicedependencyToServicegroup_S | 0.02 |
| tbl_lnkServiceescalationToContact | 0.00 |
| tbl_lnkServiceescalationToContactgroup | 0.00 |
| tbl_lnkServiceescalationToHost | 0.00 |
| tbl_lnkServiceescalationToHostgroup | 0.00 |
| tbl_lnkServiceescalationToService | 0.00 |
| tbl_lnkServiceescalationToServicegroup | 0.02 |
| tbl_lnkServicegroupToService | 0.04 |
| tbl_lnkServicegroupToServicegroup | 0.00 |
| tbl_lnkServicetemplateToContact | 0.00 |
| tbl_lnkServicetemplateToContactgroup | 0.00 |
| tbl_lnkServicetemplateToHost | 0.00 |
| tbl_lnkServicetemplateToHostgroup | 0.00 |
| tbl_lnkServicetemplateToServicegroup | 0.00 |
| tbl_lnkServicetemplateToServicetemplate | 0.01 |
| tbl_lnkServicetemplateToVariabledefinition | 0.00 |
| tbl_lnkTimeperiodToTimeperiod | 0.00 |
| tbl_logbook | 0.00 |
| tbl_mainmenu | 0.00 |
| tbl_permission | 0.02 |
| tbl_permission_inactive | 0.02 |
| tbl_service | 0.60 |
| tbl_servicedependency | 0.01 |
| tbl_serviceescalation | 0.01 |
| tbl_serviceextinfo | 0.00 |
| tbl_servicegroup | 0.01 |
| tbl_servicetemplate | 0.03 |
| tbl_session | 0.00 |
| tbl_session_locks | 0.00 |
| tbl_settings | 0.00 |
| tbl_submenu | 0.00 |
| tbl_timedefinition | 0.08 |
| tbl_timeperiod | 0.04 |
| tbl_user | 0.01 |
| tbl_variabledefinition | 0.07 |
+--------------------------------------------+------------+
206 rows in set (0.03 sec)

[benjaminsmith]

Hi @rferebee,

Thanks for posting the query - it looks normal. Going forward, I would reccomend monitoring the Nagios XI databases with the following plugin.

box293_check_mysql_table_status

This plugin will return critical if any of the tables in the database have issues.

Let me know if you have any questions.

[rferebee]

Thank you.

Can someone help me with the syntax for the command? There's no help file associated with this plugin, so I don't know how to write the command to go with it.

I can't find anything online either.

[benjaminsmith]

Hi @rferebee,

Certainly, the plugin is a short bash script that takes 3 arguments - the database to check, user and password. Go to Configure > CCM > Quick Tools > Monitoring Plugins and upload the plugin.

So from the command line to check the nagios database, run the following:

Code: Select all

cd /usr/local/nagios/libexec
su nagios
./box293_check_mysql_table_status nagios root nagiosxi

The process for setting up checks is explained in the following guide:
Managing Plugins in Nagios XI

After uploading the plugin, you'll define a new command in the CCM.

db-command.png

Then create a new service check in the CCM.

db-service.png

Have a great weekend, and let me know if this helps.

[rferebee]

I created the checks and they seem to be working.

Are there any other databases you would recommend we monitor on our XI servers?

Thank you.

[benjaminsmith]

Hi,

Are there any other databases you would recommend we monitor on our XI servers?

The other two databases used by Nagios XI are nagiosxi and nagiosql. You can simple copy your existing check command and modify it to select the other databases.