XI local backups not running

[rferebee]

Hello, are there any logs for local backups? I noticed that the backup that is supposed to occur nightly on my XI server hasn't been happening for the last 5 days, but the GUI says it ran last night.

[benjaminsmith]

Hi @rferebee

The log file for scheduled backups is located in /usr/local/nagiosxi/var/components/scheduledbackups.log.

Also take a look a the subsystem log for any errors.

Code: Select all

/usr/local/nagiosxi/var/cmdsubsys.log

Let us know if you get it worked out.

Reference:
Nagios XI Log File and Descriptions

[rferebee]

It looks like it's a SQL issue. I ran a repair. I'll let you know tomorrow morning if the backup runs tonight.

Thank you.

[benjaminsmith]

It looks like it's a SQL issue. I ran a repair. I'll let you know tomorrow morning if the backup runs tonight.

Thanks for the update and let me know if you get it running again.

[rferebee]

Looks like everything is working properly now.

I notice we have a lot of DB issues on a monthly basis. Is there anything we can do to prevent these issues? We have a DB repair script that runs every Thursday, but it doesn't seem to help. All I did to fix this latest problem was to run the DB repair manually and also I ran another script that was provided by you folks. See below for examples:

Built in SQL repair script: /usr/local/nagiosxi/scripts/repairmysql.sh nagios

Command provided by Nagios: mysqlcheck -r -f -uroot -pnagiosxi --all-databases --use_frm

Should we only be using one of these versus the other?

[benjaminsmith]

Hello @rferebee,

Should we only be using one of these versus the other?

I would recommend using the built in database repair script. The only difference between the two is the --use-frm option. In some cases, it's necessary to re-create the .MYI index file to repair the database.

Let's check the size of your database tables. Open up a terminal run the following command, and post the full output.

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 -h 127.0.0.1 -uroot -pnagiosxi --tab

[rferebee]

Here's what I got:

[root@nagiosxi ~]# 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 -h 127.0.0.1 -uroot -pnagiosxi --tab
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)

I could be wrong, but I think we're using PostgreSQL not MySQL.

[benjaminsmith]

Hi @rferebee,

Ok, let's check that by running the following command:

Code: Select all

grep mysql /usr/local/nagiosxi/html/config.inc.php | wc -l

If it outputs the number 2, you have postgres and run the following command and post the full output:

Code: Select all

echo "SELECT relname as Table, pg_size_pretty(pg_total_relation_size(relid)) As Size, pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as ExternalSize FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;" | psql nagiosxi nagiosxi

[rferebee]

Yup, looks like it's Postgre

Here's the output:

[root@nagiosxi ~]# echo "SELECT relname as Table, pg_size_pretty(pg_total_relation_size(relid)) As Size, pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as ExternalSize FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;" | psql nagiosxi nagiosxi
table | size | externalsize
---------------------+------------+--------------
xi_meta | 8208 kB | 1256 kB
xi_auditlog | 3048 kB | 1896 kB
xi_usermeta | 2408 kB | 1488 kB
xi_events | 744 kB | 576 kB
xi_options | 160 kB | 120 kB
xi_users | 128 kB | 72 kB
xi_commands | 112 kB | 96 kB
xi_sysstat | 96 kB | 72 kB
xi_mibs | 72 kB | 64 kB
xi_sessions | 56 kB | 48 kB
xi_auth_tokens | 40 kB | 40 kB
xi_eventqueue | 32 kB | 24 kB
xi_cmp_trapdata | 24 kB | 24 kB
xi_cmp_trapdata_log | 16 kB | 16 kB
xi_incidents | 8192 bytes | 8192 bytes
(15 rows)

[benjaminsmith]

Ok. Thanks. The last output looks normal.

There are two other databases on the system, are you able to get the table sizes with this command?

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');" | mysql -h 127.0.0.1 -uroot -pnagiosxi --tab

Also, take a look at the following guide, and if possible, you can improve performance reducing data retention values.

Nagios XI Database Optimization