Use MySQL client to execute SQL statements

• Edit etc/opsview.conf to set correct backup destination

 su - nagios
 /usr/local/nagios/bin/rc.opsview backup

• Ensure opsview.conf is correct

su - nagios
/usr/local/nagios/bin/db_opsview db_backup | gzip -c > {backup file}

The runtime, odw and reports databases may be backed up in the same way.

Identify the required image to restore from (location is held in $backup_dir variable within the opsview.conf file if using a full backup rather than database only).

su - nagios
gunzip -c {/path/to/nagios-db-{date}.sql.gz} | /usr/local/nagios/bin/db_opsview db_restore

If you need to upgrade the database schema, you can run:

/usr/local/nagios/installer/upgradedb_opsview.pl

We recommend you set a password for 'root' user

 mysqladmin -u root password {password}

For making remote database connections to Opsview Data Warehouse

 grant all privileges on *.* to '<username>'@'<hostname>' identified by '<password>' with grant option;
 flush privileges;

If a database table is damaged, you may get error messages like:

Table 'service_saved_state' is marked as crashed and should be repaired

A common cause is running out of space on /var partition where mysql writes its table files.

 mysqlcheck -p -u <user> <database>

To repair table (from MySQL client - note that you'll need enough disk space free for MySQL to make a new copy of the damaged table as a .TMD file):

 use <database name>;
 REPAIR TABLE <tablename>;

To check all databases you can use the following as the mysql root user

mysqlcheck -A -r -u root -p

We have seen issues where a database has a bad schema and indexes are given the wrong name. This causes problems for the upgrade scripts as they expect specific names to exist when upgrading.

Follow this process to reset the schema while retaining the existing data. You should not normally have to do this.

• Stop opsview and opsview-web
• Take a backup of the opsview database: /usr/local/nagios/bin/db_opsview db_backup > /tmp/opsview.db
• Take another backup, for comparing differences: mysqldump -u {user} -p{password} --skip-extended-insert opsview > /tmp/opsview.diff
• Export just data from the database: mysqldump --skip-extended-insert -t -c -u {user} -p{password} opsview > /tmp/opsview.data
• Create the database from scratch: /usr/local/nagios/bin/db_opsview db_install
• Export the schema information from a fresh install: mysqldump -d -u {user} -p{password} opsview > /tmp/opsview.schema
• Delete and recreate just the database: echo “drop database opsview; create database opsview” | mysql -u {user} -p{password}
• Import the fresh schema information: mysql -u {user} -p{password} opsview < /tmp/opsview.schema
• Import the data: mysql -u {user} -p{password} opsview < /tmp/opsview.data
• Take a new backup: /usr/local/nagios/bin/db_opsview db_backup > /tmp/opsview_post.db
• Take another backup, for comparing: mysqldump -u {user} -p{password} --skip-extended-insert opsview > /tmp/opsview2.diff
• Compare to check differences: diff -u /tmp/opsview.diff /tmp/opsview2.diff
• Restart Opsview

#!/bin/bash
BACKUPTARGET="/var/backups"
BACKUP_RETENTION_DAYS=14
DATE=`date +"%Y-%m-%d-%H%M"`

echo Creating ODW backup in $BACKUPTARGET
nice -n 19 /usr/local/nagios/bin/db_odw db_backup > $BACKUPTARGET/opsview-datawarehouse-$DATE.sql
echo Compressing backup with gzip
nice -n 19 gzip -9 $BACKUPTARGET/opsview-datawarehouse-$DATE.sql
echo Removing old ODW backups in $BACKUPTARGET
find $BACKUPTARGET -type f -name "opsview-datawarehouse-*.gz" -mtime +${BACKUP_RETENTION_DAYS-30} -exec rm {} \;

Logs are generated with the name mysql-bin.0000xxx and mysql-bin.index or similar. These are binary logs used for crash recovery and replication.

To remove these logs, as root mysql user run “PURGE MASTER LOGS TO 'mysql-bin.0000xx';”

For more info look at http://www.mysql.com/doc/en/Binary_log.html

To disable bin logging, in /etc/mysql/my.cnf:

binlog_ignore_db = odw
binlog_ignore_db = runtime

MySQLReport is a useful tool to evaluate the performance of MySQL. To tune MySQL, edit these values in the mysqld section of /etc/mysql/my.cnf (OS dependent) and restart mysql. Good starting values for a reasonably sized database server of 2-4GB memory are:

• table_cache = 768 (check tables opened/sec in mysqlreport)
• query_cache_size = 16M (this should not be any higher due to limitations in mysql - see this post)
• key_buffer = 256M
• innodb_buffer_pool_size = 1024M
• innodb_file_per_table = 1
• innodb_flush_log_at_trx_commit = 2
• innodb_autoinc_lock_mode=0 # Required for replication with MySQL 5.1 or later
• max_allowed_packet = 16M

You can see the current values with mysqladmin variables.

You may want to consider starting mysqld without name resolution. See http://dev.mysql.com/doc/refman/5.0/en/dns.html for more information.

You can use the opsview_preupgrade_check script to see if there are any variables that need changing. Obviously, values will depend on the resources available on your server, so this acts as guidelines.

Download the script from our svn repository for the latest version. This needs to be copied into /usr/local/nagios/installer to run. You can specify which components to check from your Opsview system.

Note: the recommendations for mysql server variables depend on your system and what other services run on it, so you have to exercise judgement when changing your system. Make sure that you do not over-commit resources to mysql because if it causes the server to go into swapping, this will reduce the performance of mysql.

Note: the crashed tables check may take a while to run.

More information about the innodb parameters are on the mysql documentation site.

Check iostat -x 5. This gives I/O statistics per disk. You could have a low overall I/O wait time, but it could be due to a single disk being used 100% of the time.

For maximum I/O, you should stripe the disks so that all disks are being utilised.

You should use separate disks for data files and index files - this improves read and write times.

You should use a fast disk for redo logs.

If you get this error when trying to create a table:

ERROR 1005 (HY000): Can't create table '<db>.<table>' (errno: 121

This can occur if the table is an InnoDB table and there is a foreign key constraint where the name of the key is already used.

This can sometimes be seen in /var/log/opsview/opsviewd.log:

[ndoutils_configdumpend] [FATAL] DBD::mysql::db do failed: Can't create file '/tmp/#sql76a1_6ff4_1.frm' (errno: 9) [for Statement "CREATE TEMPORARY TABLE opsview_viewports_temporary LIKE opsview_viewports"]

This has been seen on a MySQL 5.1.61 instance where the permissions on the data files were changed. See this forum post for more information.