Migrate NagiosQL DB Back to Local Issue

[azreenariff]

Hi Nagios Support,

We are using NagiosXI 5.4.13, running on RHEL 7.4 with 16 CPU, 32GB memory, MariaDB 5.5.56. We have been running with the DB offloaded to a remote MySQL 5.7.21 DB. Now we want to inload the DB back to the local MariaDB on Nagios XI server. However for 'nagiosql' DB, after migrating back to MariaDB all the assignments of the service checks like 'Manage Hosts', 'Manage Templates', 'Manage Host Groups', etc. are not there and their values are '0'. We compared the remote 'nagiosql' with the migrated local 'nagiosql' and it is the same.

Need your help as to what could cause the assignments to be gone in Nagios XI.

Thanks.

[benjaminsmith]

Hello @azreenariff,

Have you logged into the nagiosql database to make sure it's accessible locally and contains your data? You can test the connection to the nagiosql database using the following command:

Code: Select all

mysql -u nagiosql -p<password> -h localhost -e STATUS;

Please post the output of the following query as it will list the size of the tables in all 3 databases ( adjust password if necessary ):

Code: Select all

echo "SELECT table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema IN ('nagios', 'nagiosql', 'nagiosxi');" | mysql -uroot -pnagiosxi --table

Please send us your system profile so we can review the settings. Thanks.

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 and then reply to this post to bring it up in the queue.

[azreenariff]

Hi Benjamin,

Below are the outputs as requested:

[root]# mysql -u root -p -h localhost -e STATUS;

Enter password:

--------------

mysql Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1



Connection id: 1321

Current database:

Current user: root@localhost

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server: MariaDB

Server version: 5.5.56-MariaDB MariaDB Server

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: latin1

Db characterset: latin1

Client characterset: utf8

Conn. characterset: utf8

UNIX socket: /var/lib/mysql/mysql.sock

Uptime: 1 hour 54 min 25 sec



Threads: 159 Questions: 4214885 Slow queries: 0 Opens: 145 Flush tables: 2 Open tables: 84 Queries per second avg: 613.967

--------------



[root]# echo "SELECT table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema IN ('nagios', 'nagiosql', 'nagiosxi');" | mysql -uroot -pnagiosxi --table

+--------------------------------------------+------------+

| Table | Size in MB |

+--------------------------------------------+------------+

| alc | 0.00 |

| bdc | 0.00 |

| hla | 0.00 |

| hlib | 0.00 |

| hlisb | 0.00 |

| limit_t1 | 0.00 |

| limit_t2 | 0.00 |

| limit_t3 | 0.00 |

| limit_total | 0.00 |

| nagios_acknowledgements | 0.51 |

| nagios_commands | 0.03 |

| nagios_commenthistory | 1383.98 |

| nagios_comments | 0.20 |

| nagios_configfiles | 0.00 |

| nagios_configfilevariables | 0.01 |

| nagios_conninfo | 0.81 |

| nagios_contact_addresses | 0.00 |

| nagios_contact_notificationcommands | 0.06 |

| nagios_contactgroup_members | 0.01 |

| nagios_contactgroups | 0.00 |

| nagios_contactnotificationmethods | 33.12 |

| nagios_contactnotifications | 32.22 |

| nagios_contacts | 0.02 |

| nagios_contactstatus | 0.01 |

| nagios_customvariables | 1.50 |

| nagios_customvariablestatus | 1.62 |

| nagios_dbversion | 0.00 |

| nagios_downtimehistory | 8.20 |

| nagios_eventhandlers | 0.02 |

| nagios_externalcommands | 56.80 |

| nagios_flappinghistory | 3.71 |

| nagios_host_contactgroups | 0.08 |

| nagios_host_contacts | 0.11 |

| nagios_host_parenthosts | 0.00 |

| nagios_hostchecks | 0.00 |

| nagios_hostdependencies | 0.00 |

| nagios_hostescalation_contactgroups | 0.00 |

| nagios_hostescalation_contacts | 0.00 |

| nagios_hostescalations | 0.00 |

| nagios_hostgroup_members | 0.28 |

| nagios_hostgroups | 0.01 |

| nagios_hosts | 0.37 |

| nagios_hoststatus | 0.86 |

| nagios_instances | 0.00 |

| nagios_logentries | 1419.41 |

| nagios_notifications | 921.35 |

| nagios_objects | 10.58 |

| nagios_processevents | 1.41 |

| nagios_programstatus | 0.00 |

| nagios_runtimevariables | 0.00 |

| nagios_scheduleddowntime | 0.11 |

| nagios_service_contactgroups | 1.73 |

| nagios_service_contacts | 1.38 |

| nagios_service_parentservices | 0.00 |

| nagios_servicechecks | 0.00 |

| nagios_servicedependencies | 0.00 |

| nagios_serviceescalation_contactgroups | 0.00 |

| nagios_serviceescalation_contacts | 0.00 |

| nagios_serviceescalations | 0.00 |

| nagios_servicegroup_members | 0.00 |

| nagios_servicegroups | 0.00 |

| nagios_services | 6.65 |

| nagios_servicestatus | 16.61 |

| nagios_statehistory | 1739.60 |

| nagios_systemcommands | 0.04 |

| nagios_timedeventqueue | 0.00 |

| nagios_timedevents | 0.00 |

| nagios_timeperiod_timeranges | 0.01 |

| nagios_timeperiods | 0.00 |

| profile | 0.00 |

| region | 0.02 |

| tc | 0.00 |

| tbl_command | 0.05 |

| tbl_contact | 0.02 |

| tbl_contactgroup | 0.01 |

| tbl_contacttemplate | 0.01 |

| tbl_domain | 0.01 |

| tbl_host | 0.33 |

| tbl_hostdependency | 0.00 |

| tbl_hostescalation | 0.00 |

| tbl_hostextinfo | 0.00 |

| tbl_hostgroup | 0.03 |

| tbl_hosttemplate | 0.01 |

| tbl_info | 0.13 |

| tbl_lnkcontactgrouptocontact | 0.00 |

| 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_lnkcontacttocommandhost | 0.00 |

| tbl_lnkcontacttocommandservice | 0.00 |

| tbl_lnkcontacttocontactgroup | 0.00 |

| tbl_lnkcontacttocontacttemplate | 0.01 |

| tbl_lnkcontacttovariabledefinition | 0.00 |

| 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.07 |

| tbl_lnkhostgrouptohostgroup | 0.01 |

| tbl_lnkhosttemplatetocontact | 0.00 |

| tbl_lnkhosttemplatetocontactgroup | 0.00 |

| tbl_lnkhosttemplatetohost | 0.00 |

| tbl_lnkhosttemplatetohostgroup | 0.00 |

| tbl_lnkhosttemplatetohosttemplate | 0.00 |

| tbl_lnkhosttemplatetovariabledefinition | 0.00 |

| tbl_lnkhosttocontact | 0.06 |

| tbl_lnkhosttocontactgroup | 0.05 |

| tbl_lnkhosttohost | 0.00 |

| tbl_lnkhosttohostgroup | 0.03 |

| tbl_lnkhosttohosttemplate | 0.05 |

| tbl_lnkhosttovariabledefinition | 0.01 |

| 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_lnkserviceescalationtocontact | 0.00 |

| tbl_lnkserviceescalationtocontactgroup | 0.00 |

| tbl_lnkserviceescalationtohost | 0.00 |

| tbl_lnkserviceescalationtohostgroup | 0.00 |

| tbl_lnkserviceescalationtoservice | 0.00 |

| tbl_lnkservicegrouptoservice | 0.01 |

| 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_lnkservicetocontact | 1.83 |

| tbl_lnkservicetocontactgroup | 0.10 |

| tbl_lnkservicetohost | 1.64 |

| tbl_lnkservicetohostgroup | 0.00 |

| tbl_lnkservicetoservicegroup | 0.00 |

| tbl_lnkservicetoservicetemplate | 1.46 |

| tbl_lnkservicetovariabledefinition | 1.13 |

| tbl_lnktimeperiodtotimeperiod | 0.00 |

| tbl_logbook | 0.00 |

| tbl_mainmenu | 0.00 |

| tbl_service | 9.26 |

| tbl_servicedependency | 0.00 |

| tbl_serviceescalation | 0.00 |

| tbl_serviceextinfo | 0.00 |

| tbl_servicegroup | 0.01 |

| tbl_servicetemplate | 0.02 |

| tbl_session | 0.00 |

| tbl_session_locks | 0.00 |

| tbl_settings | 0.00 |

| tbl_submenu | 0.00 |

| tbl_timedefinition | 0.01 |

| tbl_timeperiod | 0.01 |

| tbl_user | 0.01 |

| tbl_variabledefinition | 2.25 |

| xi_auditlog | 8.38 |

| xi_commands | 0.00 |

| xi_eventqueue | 0.01 |

| xi_events | 0.05 |

| xi_incidents | 0.00 |

| xi_meta | 7.02 |

| xi_options | 0.02 |

| xi_sysstat | 0.01 |

| xi_usermeta | 0.64 |

| xi_users | 0.01 |

+--------------------------------------------+------------+


Nagios local DB is MariaDB 5.5.56. We have been running with offloaded DB to MySQL 5.7.21 DB. As we have been experiencing UI slow performance, we wanted to inload the DB back to the local MariaDB. We have managed to migrate back the 'nagios' DB and the performance improved tenfold. However for 'nagiosql' DB, all the assignments/links of the service checks like 'Manage Hosts', 'Manage Templates', 'Manage Host Groups', etc. are lost after importing back. So, we left the settings to use 'nagios' DB on local, and the other 'nagiosql' and 'nagiosxi' still offloaded. We have compared the remote 'nagiosql' with the migrated local 'nagiosql' and it is the same. Could this be related to the compatibility issue of MySQL 5.7.21 DB migrating back to MariaDB 5.5.56?

I will PM you the system profile.

Thanks.

[benjaminsmith]

Hi,

Thank you for sending over the system profile. I noticed this entry in the command subsystem log:

PROCESSED 0 COMMANDS
Connection timed out after 15001 milliseconds

Can you post the output of the following commands to check the nagios user account. Are you able to successfully apply configuration changes?

Code: Select all

chage -l nagios
grep nag /etc/group

Regarding the database issues, you'll want to migrate between the same or higher versions of mariadb ( see: this). That said, the majority of database activity is generated on the nagios database by the ndoutils Nagios broker module. So there's option of maintaining the current systems setup if it's working properly as moving the nagios database has improved performance.

Be sure to make a backup or take a vm snapshot before making any changes.

[azreenariff]

Hi Benjamin,

Thank you. Regarding DB upgrade, it would take quite some time to be able to proceed as there are protocols that we need to go through.

In the meantime, kindly find below as requested:

Apply Configuration is successful most of the time, but intermittently it fails not completing with the '.' filling up the screen.

[root]# chage -l nagios

Last password change : Jul 16, 2018
Password expires : never
Password inactive : never
Account expires : never
Minimum number of days between password change : -1
Maximum number of days between password change : -1
Number of days of warning before password expires : -1

[root]# grep nag /etc/group

apache48:nagios
nagcmd1001:apache,nagios
nagios1002:apache,nagios


Thanks.

[benjaminsmith]

Hello,

Typically if apply configuration is failing, it's the result of incorrect configuration files or a sudoers/permissions issue.

Reset the configuration permissions, by running the following script as root from the terminal:

Code: Select all

/usr/local/nagiosxi/scripts/reset_config_perms.sh

Upload or PM your sudoers file for us to review.

Code: Select all

cat /etc/sudoers >/tmp/info.txt

As this is happening intermittently, or if you're able to repeat the error, please run the following tail command, apply configuration and post the output to the thread to review the errors.

Code: Select all

tail -f /usr/local/nagiosxi/var/cmdsubsys.log

[azreenariff]

Hi Benjamin,

I've PM the sudoers as requested. Unfortunately, the Apply Configuration was working fine today so could not submit to you the cmdsubsys.log when it fails

Thanks.

[benjaminsmith]

Hello,

Ok, next time you have issue with Apply Configuration, please download a fresh system profile and send it over. Did you run the reset_config_perms script? I just want to confirm.

Code: Select all

/usr/local/nagiosxi/scripts/reset_config_perms.sh

Thanks.

[azreenariff]

Hi Benjamin,

Okay, sure. Also, we already performed the reset_config_perms.

Thanks.

[benjaminsmith]

Hi.

Great. We'll keep this open and wait for your reply.