Recently I’ve had to move a few opsview servers databases from the Opsview master onto an alternate server, mostly because of disk space issues, but I’ve found this to be a pretty good performance increase on busy servers too, so its well worth doing anyway.
Firstly you want to build yourself a new server, I’m using Ubuntu and so have rolled out the 12.04 LTS beta. Installing mysql is pretty simple – apt-get install mysql-server.
There’s a few things you’ll want to change in the my.cnf config file (/etc/mysql/my.cnf on Ubuntu).
Change the bind address so that we can connect to our server across the LAN:
bind-address = 0.0.0.0
And the following performance based settings as recommend by Opsview: (check out http://docs.opsview.com/doku.php?id=opsview-community:mysql)
key_buffer = 256MB query_cache_size = 16M table_cache = 768 innodb_buffer_pool_size = 1024M innodb_file_per_table=1 innodb_flush_log_at_trx_commit=2
Now give your mysql a restart, check the log for errors: /etc/init.d/mysql restart if you’re oldschool or service mysql restart.
Now we need to get the data off the master server and onto the new dedicated mysql server. We can do this over SSH to save storing multiple copies of the database (mine was 50GB or so).
From the master server first stop opsview:
/etc/init.d/opsview stop /etc/init.d/opsview-web stop
Now run the following command to export the data and directly transfer into mysql on the new server over SSH. You should be a tad cautious here as the passwords are entered on the CLI in clear text.
mysqldump -u root -p<rootpassword> --databases odw opsview reports runtime | ssh user@<new-server-hostname> 'mysql -u root -p<rootpassword>'
The data may take some time to copy, depending on its size. You should consider running this command inside screen or tmux in case your SSH session gets disconnected.
Once all your data has been transferred, you need to change the /usr/local/nagios/etc/opsview.conf file to point to the new database server. It’s simply a mater of assigning a few variables like so:
$dbpasswd = "yourpassword"; $dbhost = 'mysql-hostname.domain.local'; $runtime_dbpasswd = "yourpassword"; $runtime_dbhost = 'mysql-hostname.domain.local'; $odw_dbpasswd = "yourpassword"; $odw_dbhost = 'mysql-hostname.domain.local'; $reports_dbpasswd = "yourpassword"; $reports_dbhost = 'mysql-hostname.domain.local';
There’s a handy wee script provided for configuring credentials in MySQL – you just need to run it, again we’ll pipe it directly to the new server ready for execution:
/usr/local/nagios/bin/db_mysql -t | ssh user@mysql-hostname ~/opsview_access.sql
I prefer the database access to be slighly more secure that what this script generates so I’d recommend replacing the % (any host) entries in the file with the hostname of your opsview server. This can by done easily using sed:
sed -i 's/%/opsview-master.domain.local/g' opsview_access.sql
Now you can go ahead and import this into mysql:
mysql -u root -p < opsview_access.sql
All thats left to do is to regenerate the Opsview configuration and then start up the services. This can be done as follows:
/usr/local/nagios/bin/rc.opsview gen_config /etc/init.d/opsview-web start
Once you’re happy that everything is running as expected, you can get rid of the old databases on the master server. Drop them like this:
drop database opsview; drop database odw; drop database runtime; drop database reports;