Page 1 of 3
Unable to get 'nagios_logentries' DB to recover
Posted: Mon Sep 30, 2019 3:08 pm
by 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.
Re: Unable to get 'nagios_logentries' DB to recover
Posted: Mon Sep 30, 2019 4:39 pm
by 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.
Re: Unable to get 'nagios_logentries' DB to recover
Posted: Mon Sep 30, 2019 4:51 pm
by 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
Re: Unable to get 'nagios_logentries' DB to recover
Posted: Mon Sep 30, 2019 4:57 pm
by 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.
Re: Unable to get 'nagios_logentries' DB to recover
Posted: Tue Oct 01, 2019 1:22 pm
by 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.
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,
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.
Re: Unable to get 'nagios_logentries' DB to recover
Posted: Tue Oct 01, 2019 4:03 pm
by 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?
Re: Unable to get 'nagios_logentries' DB to recover
Posted: Tue Oct 01, 2019 4:09 pm
by mbellerue
It's entirely possible. Do you have an /etc/mysql, or /etc/mariadb directory?
Re: Unable to get 'nagios_logentries' DB to recover
Posted: Tue Oct 01, 2019 4:13 pm
by rferebee
I don't, but I do have /var/lib/mysql and it has both nagios and nagiosxi directories in it.
Re: Unable to get 'nagios_logentries' DB to recover
Posted: Tue Oct 01, 2019 4:17 pm
by 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.
Re: Unable to get 'nagios_logentries' DB to recover
Posted: Tue Oct 01, 2019 4:33 pm
by 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