Unable to get 'nagios_logentries' DB to recover

[rferebee]

Hello,

The 'nagios_logentries' DB crashed on all three of my XI servers earlier today and despite my best efforts I cannot get them to recover.

I followed all the steps in this document: https://assets.nagios.com/downloads/nag ... tabase.pdf

Also, the commands I normally use to correct these issues aren't working either:

Code: Select all

/usr/local/nagiosxi/scripts/repairmysql.sh nagios

Code: Select all

mysqlcheck -r -f -uroot -pnagiosxi --all-databases --use_frm

Is there anything else I can try?

Thank you.

[mbellerue]

Let's verify a few things first. Is there anything special being done for the storage of the MySQL databases? Some people like to put them on remote storage like NFS or iSCSI. Anything like that where the storage may have fallen out from underneath the databases?

Let's hop into the MySQL command line and try running the repair.

Code: Select all

mysql
connect nagios;
repair table nagios_logentries;
exit

Let us know what the output of that repair table command is.

[rferebee]

Thanks for your reply.

I'm not aware of any storage changes for these servers.

When I attempt to connect to the nagios DB, I'm getting an access denied error.

Code: Select all

admwoody@nagiostest:/home/rferebee>su nagios
Password:
bash-4.2$ mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5717
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> connect nagios;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'nagios'
unknown [nagios]> repair table nagios_logentries;
No connection. Trying to reconnect...
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'nagios'
ERROR: Can't connect to the server

[rferebee]

I should say, I believe we're using PostgreSQL not MySQL. Not sure if that matters.

I'm not very competent with DB issues.

[mbellerue]

Okay so it looks like the PostgreSQL process is similar. However, they highly recommend taking a file level backup of your data first.

First, find the data directory

Code: Select all

postgres psql
postgres=# SHOW data_directory;
\q

That should display the data directory for you. Then shutdown PostgreSQL.

Code: Select all

systemctl stop postgresql

From there you can copy the data to another location. That way if something goes sideways with the repair, you can always try again.

Start PostgreSQL,

Code: Select all

systemctl start postgresql

And then we run the repair.

Code: Select all

psql nagios
postgres=# REINDEX TABLE nagios_logentries

Let that run, and tell us if that gets your table up and running again.

[rferebee]

I'm getting a FATAL error. It's telling me that 'database "nagios" does not exist'.

There is POSTGRESQL data on the server, but I guess this particular database isn't Postgres? Could we have both MySQL and Postgres running in tandem?

[mbellerue]

It's entirely possible. Do you have an /etc/mysql, or /etc/mariadb directory?

[rferebee]

I don't, but I do have /var/lib/mysql and it has both nagios and nagiosxi directories in it.

[rferebee]

I think mysql is where we need to be, but it's telling me that the accounts don't have access to the nagios database. I've tried root, mysql, nagios... nada.

[scottwilkerson]

The nagios database is never in postgresql

Run the following:

Code: Select all

echo 'repair table nagios_logentries use_frm;' | mysql -t -u root -pnagiosxi nagios

Then if you still show an error, show the output of the following:

Code: Select all

tail -f /var/log/mysqld.log /var/log/mariadb/mariadb.log