cronjob of scheduled backup

[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"?

[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.

[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?

[mansonli]

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?

[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

[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?

Pls reply above questions

[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:

Code: Select all

127.0.0.1 -uroot -pnagiosxi

Now run each command individually or do this:

Code: Select all

bash -x MyISAM_to_InnoDB.txt

[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

[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

[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