SQL Error [ndoutils] : Incorrect file format 'nagios_notific

[scottwilkerson]

Here's what I recommend 1st. Take a current local backup Admin -> Backups
This way we have something to go back to that is where we are right now.
Once you see that, lets do this

See how this file is a lot bigger than the others? This is before the database got all messed up
-rw-r--r-- 1 root root 29341356 Jun 9 07:00 nagios_week.23.2018-06-09_07h00m.sql.gz

So lets run this

Code: Select all

cd /store/backups/mysql/weekly/nagios
gunziup nagios_week.23.2018-06-09_07h00m.sql.gz
cat nagios_week.23.2018-06-09_07h00m.sql|mysql -pnagiosxi
service nagios restart

This will restore all the historical info back to where it was on 2018-06-09 before the problems

[Kujab]

Thank you. I restore those records. But newer records (after 9th of June) are away. It's not so critical. But, is there any way how to merge tjhat together? Backup and current records ?

[scottwilkerson]

Those records are gone, with the exception of like the 22 til this AM which you could find in
/store/backups/mysql/daily/nagios/nagios_2018-06-25_07h00m.Monday.sql.gz

HOWEVER, you would need to unzip and make a ton of modifications because that .sql file if run like what we just did is going to DELETE and recreate the table with it's information.

If they are extremely critical, we could probably figure out a consulting amount of time to restore that portion, but you still will not get anything from 6/9 to 6/22. The data doesn't exist.

[Kujab]

No, it's not critical. I can use performance charts as a partially workaround for this short period.
Thank you.

Is there any easy way how to check if database is running fine, without any similar issues like now? It may happen in the future, and then I need to check that sql database is consistent. Any command, or procedure ?

[scottwilkerson]

If you have Nagios Log Server you could send the /var/log/mysqld.log to it and then query for new entries that contain ERROR

Same could be done with a plugin like
https://exchange.nagios.org/directory/P ... es/details

[Kujab]

I will try this huge check to configure to be able check mysql logs.

But any easy procedure/command for mysql databes is not available ? In case Nagios is not working I could use this check from command line, that's true, but still dependent on 3rd party code (this check).
Thank you.

[scottwilkerson]

A procedure would be to run the following to see if the last couple lines of this command contain ERROR

Code: Select all

tail /var/log/mysqld.log