Scheduled Local Backup Failed

[providentpolska]

Hi Community,

Once every 3 months I have a problem with schedule backup. I got a below message:

The scheduled local backup at 2024-09-30 02:09:29 failed.

Last output from the system: Error backing up MySQL database 'nagios' - check the password in this script!


View your full scheduled backup logs at /usr/local/nagiosxi/var/components/scheduledbackups.log to find out more details about the error. Your backup may have timed out, you can set a backup_timeout variable in config.inc.php to increase it.

After repair database, the problem becomes resolved, but it come back to every 3 months.

Maybe someone had a similar case and solved it permanently?

[jmichaelson]

To make sure I'm understanding this correct, the scheduled backups work normally for a period of time and then start failing at some point? Is it sporadic or once it starts does it continue to happen?

Also it will be helpful to know what version of Nagios XI you are using and the distribution and version of Linux.

[providentpolska]

Yes, the scheduled backups work normally for a period of time and then start failing. After that, we repaired database and it works correctly for some time.

Version Nagios XI 5.11.2

[DoubleDoubleA]

We're probably not going to be able to do a deep troubleshooting dive on this issue via the forum in the way we would in a full support ticket.

It sounds like the root cause is a database issue. It's great that you can repair it. I wonder what the actual database issue is, and why it might come up every 3 months or so. Do you know what the underlying database issue is?

[providentpolska]

Hi,
Unfortunately no. After perform truncate table nagios and run script repiar_databases.sh, problem is solve for a next 3 months.. but finally it is not except solving this case.

[tomhiddleston2]

I had a similar issue and strangely it works fine now.

[jmichaelson]

Hi @providentpolska

Now I'm wondering if you couldn't periodically snapshot the mysql database with mysqldump. Keep the last known good backup sitting around, and when it goes wonky take another snapshot of it and compare the two. It might help phone support figure out what's happening to your database.

[kg2857]

Maybe keep any eye on the server uptime before and after the issue. An uncontrolled restart can break databases. Also a separate nagios instance should be monitoring the nagios server.

[providentpolska]

Hi @providentpolska

Now I'm wondering if you couldn't periodically snapshot the mysql database with mysqldump. Keep the last known good backup sitting around, and when it goes wonky take another snapshot of it and compare the two. It might help phone support figure out what's happening to your database.

It is a good solution to proceed this issue.
Can you explain in more details about this snpashot mysql db with mysqldump? How should I do it?

Thanks.

[jmichaelson]

I should have been more clear. First you'll need the password for the mysql root user. you can retrieve it using the command

Code: Select all

/usr/local/nagiosxt/scripts/get_mysql_passwords.sh

The run the command:

Code: Select all

mysqldump -u root -p -A -R -E --triggers --single-transaction > last_known_good.sql

and enter the root password shwon from the above command. Then if the issue occurs again, you can rerun the mysqldump with a different output file than last_known_good.sql, and diff the two files to find out what changed. That aspect of things may require a call to support to diagnose depending on the complexity of the changes between the two samples.

Keep us posted (either here or with paid support) as to what happens.