Page 2 of 2
Re: cronjob of scheduled backup
Posted: Wed Dec 02, 2020 10:40 pm
by mansonli
Update Optimize Intervals of Nagios XI Database, NDOUtils Database and NagiosQL Database? What's the effect of each optimize run on DB in terms of its size and performance?
Is it possible to predict how large of notification table will crash?
If historical data is required to be available after truncate, should use backup to restore it if it exists?
Retention intervals refer to "Max *(such as Commands/state history/Notifications) Age"?
Re: cronjob of scheduled backup
Posted: Thu Dec 03, 2020 4:42 pm
by ssax
Increase ALL THREE Optimize Intervals on that page for all DBs, once you have larger tables, if one optimize isn't finished before the next starts can cause crashing of tables. I increase it to 300 on every system that has larger DB tables since it's a common issue.
Is it possible to predict how large of notification table will crash?
Not that I'm aware of, there are a lot of factors involved, load/IO wait especially, which can cause slow DB processing and queuing of DB queries because of table locks.
If historical data is required to be available after truncate, should use backup to restore it if it exists?
You can but you the recommendation would be that you only restore that single table instead of going back in time entirely for all DB tables which will happen if you do a standard XI restore. Which table are you trying to restore? You would really need to extract the XI backup and get the individual table from the dump files (can also find some other dumps in /store/backups/mysql/daily/nagios, /store/backups/mysql/weekly/nagios, /store/backups/mysql/monthly/nagios). If you need to do this, let me know which table you're trying to restore and I can send some instructions.
Retention intervals refer to "Max *(such as Commands/state history/Notifications) Age"?
Correct, it is the max.
EDIT: I should also mention you can convert the DB tables from MyISAM (which crashes) to InnoDB (which doesn't crash) as another option. I was always told MyISAM is faster but InnoDB doesn't crash and supports transactions. I was told recently that InnoDB is faster for certain data types.
Re: cronjob of scheduled backup
Posted: Thu Dec 03, 2020 9:56 pm
by mansonli
Pls share steps to restore the following tables from backup dump if we're going to truncate them
nagios_logentries
nagios_notifications
How to check current DB table is MyISAM? How to convert from MyISAM to InnoDB without data loss?
Re: cronjob of scheduled backup
Posted: Thu Dec 03, 2020 10:06 pm
by mansonli
ssax wrote:There isn't currently a built-in way to do this but that's a great idea, I've created a feature request on your behalf:
FR: XI - Add scheduled backups notifications and include the backup job status/logs/size in the notification
Please keep in mind that the decision to implement the enhancement is at the discretion of our development team.
Since there isn't a way to hook into the built-in scheduled backups functionality the only option you have would be to disable your scheduled backups and then write a custom cron job script that does the backup and then mails you the logs/status/etc.
Will above feature add into XI 5.8? When will it release?
May you share such backup script with notification if it's available?
Re: cronjob of scheduled backup
Posted: Fri Dec 04, 2020 3:15 pm
by benjaminsmith
Hi
@mansonli,
The custom script was a suggestion, but a feature request has been submitted, and it will be reviewed internally. At this point, I can't guarantee if or when it would be available. I don't think it would make it to 5.8 at this point but possibly 6.0.
Best Regards,
Benjamin
Re: cronjob of scheduled backup
Posted: Tue Dec 08, 2020 8:36 am
by mansonli
mansonli wrote:Pls share steps to restore the following tables from backup dump if we're going to truncate them
nagios_logentries
nagios_notifications
How to check current DB table is MyISAM? How to convert from MyISAM to InnoDB without data loss?
Pls reply above questions
Re: cronjob of scheduled backup
Posted: Tue Dec 08, 2020 7:23 pm
by ssax
Please send me a copy of your profile, you can download it from Admin > System Profile by clicking the Download Profile button.
Are you intending to restore from an XI backup or from the backups in one of these directories?
Code: Select all
/store/backups/mysql/daily/nagios
/store/backups/mysql/weekly/nagios
/store/backups/mysql/monthly/nagios
To view the current engine type for each table:
Code: Select all
mysql -h 127.0.0.1 -uroot -pnagiosxi nagios -e "SELECT table_name, engine FROM information_schema.tables WHERE table_schema IN ('nagios', 'nagiosql', 'nagiosxi');"
For converting all MyISAM tables to InnoDB without data loss:
- NOTE: Take a VM snapshot/XI backup first, just in case
Download the attached file, edit each command in the downloaded file and change these values so they point to your DB server/have the proper mysql root password:
Now run each command individually or do this:
Re: cronjob of scheduled backup
Posted: Wed Dec 09, 2020 12:00 pm
by ssax
Please answer this:
Are you intending to restore from an XI backup or from the backups in one of these directories?
Code: Select all
/store/backups/mysql/daily/nagios
/store/backups/mysql/weekly/nagios
/store/backups/mysql/monthly/nagios
Re: cronjob of scheduled backup
Posted: Wed Dec 09, 2020 11:23 pm
by mansonli
Yes, consider to restore from daily XI backup among the following paths. May I know how to create this backup archive?
/store/backups/mysql/daily/nagios
/store/backups/mysql/weekly/nagios
/store/backups/mysql/monthly/nagios
Re: cronjob of scheduled backup
Posted: Thu Dec 10, 2020 2:06 pm
by ssax
Take a VM snapshot (if a VM)/XI backup just in case.
Those backups are created by this:
Code: Select all
[root@xid libexec]# cat /etc/cron.d/nagiosxi
# /etc/cron.d/nagiosxi: crontab fragment for nagiosxi
# Backup MySQL & PostgreSQL Databases
0 7 * * * root /root/scripts/automysqlbackup
For the XI backups you can use Admin > Scheduled Backups to enable them.
Here's how I did it to restore from the mysql daily backup:
- NOTE that you will need to change these lines before running to point to the proper file you're working with:
gunzip
nagios_2020-12-10_07h00m.Thursday.sql.gz
sed -n -e '/DROP TABLE.*`nagios_logentries`/,/UNLOCK TABLES/p'
nagios_2020-12-10_07h00m.Thursday.sql > /tmp/nagios_logentries.sql
sed -n -e '/DROP TABLE.*`nagios_notifications`/,/UNLOCK TABLES/p'
nagios_2020-12-10_07h00m.Thursday.sql > /tmp/nagios_notifications.sql
Code: Select all
cd /store/backups/mysql/daily/nagios
# Extract the backup (your name will differ)
gunzip nagios_2020-12-10_07h00m.Thursday.sql.gz
# Extract ONLY the nagios_logentries table/data from the backup
sed -n -e '/DROP TABLE.*`nagios_logentries`/,/UNLOCK TABLES/p' nagios_2020-12-10_07h00m.Thursday.sql > /tmp/nagios_logentries.sql
# Extract ONLY the nagios_notifications table/data from the backup
sed -n -e '/DROP TABLE.*`nagios_notifications`/,/UNLOCK TABLES/p' nagios_2020-12-10_07h00m.Thursday.sql > /tmp/nagios_notifications.sql
Then open up the files and validate it has the data in them by scrolling through it:
Code: Select all
view /tmp/nagios_logentries.sql
view /tmp/nagios_notifications.sql
NOTE: These commands drop the table removing the old data and then recreates them exactly as they were from the backup files
Code: Select all
# To import the nagios_logentries
mysql -h 127.0.0.1 -uroot -pnagiosxi nagios < /tmp/nagios_logentries.sql
# To import the nagios_notifications
mysql -h 127.0.0.1 -uroot -pnagiosxi nagios < /tmp/nagios_notifications.sql