Next on my list of “interesting things” is Maria DB – i recently read that it advertises “100% compatability with MySQL applications” and i wanted to actually see if this was the case. Given that in my company we use MySQL as the back-end for Opsview, i figured this was the prime candidate for this – can i get Opsview to use Maria DB, as if it were MySQL – without any dev work?
So first and foremost – the scenario. I have my local Opsview server, monitoring “X” number of devices and its getting a bit slow now due to all the workload, so i want to offload my database onto a new server – and have Opsview connect to it. We have a document for that, here:
http://docs.opsview.com/doku.php?id=opsview4.5:dbs-on-diff-server
Firstly, I span up a new server (Ubuntu 13.04 in this case) and open the firewall to allow inbound connections on TCP 3306. Next, I need to install MySQL MariaDB on my new server – so that Opsview can use it.
Installation of MariaDB
So installation of MariaDB:
Step 1. Navigate to https://downloads.mariadb.org/mariadb/repositories/ and choose your OS/Version/Location, and it will provide you the repositories required, similar to below (I’m using MariaDB “10”):
sudo apt-get install software-properties-common sudo apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db sudo add-apt-repository 'deb http://mirror.stshosting.co.uk/mariadb/repo/10.0/ubuntu raring main'
Step 2. After adding the key’s etc above, we need to install MariaDB:
sudo apt-get update sudo apt-get install mariadb-server
And thats the installation done (as with MySQL, it will ask you for a root password for your MariaDB installation). We can now test this out as we would with mysql:
root@mariadb:/home/ubuntu# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 159 Server version: 10.0.3-MariaDB-1~raring-log mariadb.org binary distribution Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
And it works, ta-da. Not very difficult so far.
Configuration of Opsview / MariaDB
First things first, the one thing that trips oodles of people up each time they use a “remote DB”, is the my.cnf settings. We need to edit this, and change the line “bind-address” to the external IP address of your server as below:
bind-address = ec2-46-137-68-85.eu-west-1.compute.amazonaws.com
And then restart MariaDB (/etc/init.d/mysql restart).
Next, we need to configure Opsview to point to the new server as below:
1. Stop Opsview so we have a consistent snapshot:
/etc/init.d/opsview stop /etc/init.d/opsview-web stop
2. Backup the Opsview databases:
mysqldump -u root -p --opt --databases opsview runtime odw dashboard| gzip -c > databases.sql.gz
3. On the MariaDB server, restore the backup:
root@mariadb:/home/ubuntu# gunzip -c databases.sql.gz | mysql -u root -p Enter password: root@mariadb:/home/ubuntu#
No compatability issues here – works like a charm. Next!
4. After the restore, we need to point Opsview at the MariaDB server. In /usr/local/nagios/etc/opsview.conf add the following lines:
$dbhost = "ec2-46-137-68-85.eu-west-1.compute.amazonaws.com"; $odw_dbhost = "ec2-46-137-68-85.eu-west-1.compute.amazonaws.com"; $runtime_dbhost = "ec2-46-137-68-85.eu-west-1.compute.amazonaws.com"; $reports_dbhost = "ec2-46-137-68-85.eu-west-1.compute.amazonaws.com"; $dashboard_dbhost = "ec2-46-137-68-85.eu-west-1.compute.amazonaws.com";
Where the host in the quotes is your MariaDB server (the same address as the “bind-address =” we set earlier).
5. Next, we need to copy the access controls from the Opsview server to MariaDB server.
On the Opsview server:
/usr/local/nagios/bin/db_mysql -t > opsview_access.sql
Then copy this to the MariaDB server.
On the MariaDB server:
mysql -u root -p < opsview_access.sql
6. Finally, lets regenerate the config for the new settings and reload opsview-web:
root@opsview-master:/home/ubuntu# /usr/local/nagios/bin/rc.opsview gen_config ec2-46-137-68-85.eu-west-1.compute.amazonaws.com Thu Jun 27 10:26:42 2013 Starting Thu Jun 27 10:26:42 2013 --> Writing config files for Master Monitoring Server Thu Jun 27 10:26:42 2013 Created distributed information Thu Jun 27 10:26:42 2013 Created keyword lookup list for 3 keywords Thu Jun 27 10:26:42 2013 84 commands written to checkcommands.cfg Thu Jun 27 10:26:42 2013 24 service definitions written to services.cfg Thu Jun 27 10:26:42 2013 1 hosts written to hosts.cfg Thu Jun 27 10:26:42 2013 2 hostgroups written to hostgroups.cfg Thu Jun 27 10:26:42 2013 3 contacts (2 profiles) written to contacts.cfg Thu Jun 27 10:26:42 2013 3 contacts written to htpasswd.users Thu Jun 27 10:26:42 2013 6 groups written to contactgroups.cfg Thu Jun 27 10:26:42 2013 Written nagios.cfg Thu Jun 27 10:26:43 2013 Written cgi.cfg Thu Jun 27 10:26:43 2013 Written misccommands.cfg Thu Jun 27 10:26:43 2013 Written timeperiods.cfg Thu Jun 27 10:26:43 2013 Written snmptraps.cfg Thu Jun 27 10:26:43 2013 Written notificationmethodvariables.cfg Thu Jun 27 10:26:43 2013 Written nsca.cfg Thu Jun 27 10:26:43 2013 Written nrd.conf Thu Jun 27 10:26:43 2013 Written send_nrd.cfg Thu Jun 27 10:26:43 2013 Written send_nsca.cfg Thu Jun 27 10:26:43 2013 Written ndo2db.cfg Thu Jun 27 10:26:43 2013 Written ndomod.cfg Thu Jun 27 10:26:43 2013 5 dependencies written to services.cfg
Nagios config re-generated in 1.724 seconds
mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': Unknown system variable 'OPTION' (1193) mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': Unknown system variable 'OPTION' (1193) Cannot backup database at /usr/local/nagios/bin/db_dashboard line 100. root@opsview-master:/home/ubuntu#
A few errors here regarding a “OPTION” variable – the first error ive seen so far – but seems like the operation completed. Next, lets restart the services we stopped earlier:
/etc/init.d/opsview-web start
7. Finally, to ensure we arent using the local MySQL server and are infact using the remote one, lets drop everything and stop the service:
mysql -u root -p drop database opsview; drop database odw; drop database runtime; drop database reports; drop database dashboard; exit /etc/init.d/mysql stop
… and thats the configuration complete!
Testing
Its fairly simple to test, we simply login to the Opsview GUI and hope everything works 🙂
And voila! C’est fini. It works – and all of the service checks that check the Opsview database pass successfully, all of the servers we were previously monitoring are still here – it just works.
Closing Thoughts
Overall, I am very impressed with the backwards compability of MariaDB – and that it is (on the face of the above) a very simple “drop in” replacment for MySQL where neccessary.
