How to: Install the MySQL Agent for NewRelic in an Ubuntu server

As part of a series of posts (How to: Install the Memcached Agent for NewRelic in an Ubuntu server and How to: Install the NginX Agent for NewRelic in an Ubuntu server) I am including instructions on how to install the MySQL monitoring agent for NewRelic. I generally only add instructions to those plugins that are a bit tricky. For example, the MS SQL plugin is very straight forward and there isn’t much to add, but when it comes to the MySQL plugin I always struggle with deciding how to install Java and then how to create a script that will run on start-up to monitor MySQL (you guessed right, as the instructions stand you need to manually launch the MySQL agent monitor).

Let’s take a look at the New Relic instructions:

Requirements:

To use this plugin, you must have:

• a Java Runtime Environment (JRE) of 1.6 or higher
• at least one database to monitor (MySQL 5.0 or higher)
• a New Relic account

Installation information

Manual Installation

1. Download the MySQL plugin .tar.gz file from Plugin Central (download button below)
2. Unpack the .tar.gz file in a new directory (typically tar zxvf newrelic_mysql_plugin-X.X.X.tar.gz)
3. Copy config/template_newrelic.properties to config/newrelic.properties
4. Open the config/newrelic.properties file and enter your license key (found in “Account Settings” menu at https://rpm.newrelic.com)
5. Copy config/template_mysql.instance.json to config/mysql.instance.json
6. Configure config/mysql.instance.json for each MySQL instance you want to monitor
7. Run java -Xmx128m -jar <the plugin jar name> in your console

Note: The -Xmx128m argument sets the maximum reserved memory for the Java JVM process to 128mb. See the README for more information.

As I mentioned there are a few things that I usually struggle every blue moon I decide to install this plugin: 1) How to get Java installed (it is a pre-requisite) and 2) How to script the execution of the monitoring agent on boot. Below are my installation steps that I hope will help others struggling with the same questions find a quick answer:

O. Summary (Just the commands)

For those who’ve done this in the past and just want to get it over with quickly:

sudo bash

(Install Java)

apt-get install openjdk-7-jre

(Download the plugin to a folder like /etc/newrelic and extract the files to a subdirectory. See to get the latest version)

wget https://rpm.newrelic.com/plugins/52/29407720d7e1aef0f0617334e66c9b4e

tar -xvzf 29407720d7e1aef0f0617334e66c9b4e

cd newrelic_mysql_plugin-1.2.1/

cp config/template_newrelic.properties config/newrelic.properties

nano config/newrelic.properties # Modify to add your NewRelic's key

cp config/template_mysql.instance.json config/mysql.instance.json

nano config/mysql.instance.json # Modify the configuration to monitor the instance(s)

mysql -uroot -p < mysql_user.sql # Deploy the user needed to monitor your server

nano /etc/newrelic/newrelic_mysql_plugin-1.2.1/etc/init.d/newrelic-mysql-plugin.debian # Make the changes recommended below

chmod +x newrelic-mysql-plugin.debian

ln -s /etc/newrelic/newrelic_mysql_plugin-1.2.1/etc/init.d/newrelic-mysql-plugin.debian /etc/init.d/newrelic-mysql-plugin

update-rc.d newrelic-mysql-plugin defaults

service newrelic-mysql-plugin start

And for those who are going through this for the first time: Step by Step with explanations:

I. Install Java

As of late I’ve been recommending using the webupd8team/java ppa as you get Oracle’s Java installed and updates delivered. Otherwise OpenJDK has worked well in my case when using this plugin. I wrote a post: How to: Install Java JRE on an Ubuntu computer that walks you through using different ppas, the original download from Oracle’s site or deploying OpenJDK. All options as far as I have tried them worked well. The easiest by far would be the webup8team’s ppa as you get updates with that or OpenJDK.

II. Get the Server Agent

Visit NewRelic’s site to download the latest version (1.2.1 as of the time of writing). Find a suitable location and extract the tar.gz file. Once you are done you are going to have to copy the configuration templates so you can work with them:

cd /etc/newrelic

(Download the MySQL plugin .tar.gz file from Plugin Central)

tar -zxvf newrelic_mysql_plugin-1.2.1.tar.gz

III. Configure the Server Agent

Now we are going to configure the Monitoring server agent. These step simply consist on creating (copying from templates) the files that contain the instances to monitor and your newrelic key:

cd newrelic_mysql_plugin-1.2.1/

cp config/template_newrelic.properties config/newrelic.properties

nano config/newrelic.properties # Modify to add your NewRelic's key

cp config/template_mysql.instance.json config/mysql.instance.json

nano config/mysql.instance.json # Modify the configuration to monitor the instance(s)

mysql -uroot -p < mysql_user.sql # Deploy the user needed to monitor your server

and proceed to test that everything works fine:

java -Xmx128m -jar newrelic_mysql_plugin-1.2.1.jar

if all is good you should see a message like this:

INFO: Using configuration file /etc/newrelic/newrelic_mysql_plugin-1.2.1/config/newrelic.properties
WARNING: Logging is not currently configured. Please add a config/logging.properties file to enable additional logging.
INFO: New Relic monitor started

IV. Create a script to automatically launch the server monitoring agent at boot / Create a Daemon

There is an init.d script (and others for other linux distributions) that comes with the download. Simply navigate to: /etc/newrelic/newrelic_mysql_plugin-1.2.1/etc/init.d/ to find the script and note following instructions:

# CONFIGURATION

# – Change DAEMONDIR to your chosen location for the plugin jar file.

# – To run on boot, don’t forget to run update-rc.d newrelic-mysql-plugin defaults.

I also recommend making the following changes (in bold) to limit the amount of memory the plugin may use:

log_daemon_msg "Starting $DESC: "
# add this if you need to log the output: --no-close
start-stop-daemon --start --quiet --background --make-pidfile --pidfile $PIDFILE --chuid $DAEMON_USER --chdir $DAEMONDIR --exec $JAVA -- -Xmx64m -jar $DAEMONDIR/$DAEMON  > /var/log/newrelic-plugin.log 2>&1
# append this to the above line if you need to log the output: > /var/log/newrelic-plugin.log 2>&1
log_end_msg $?

The first -Xmx64m is to limit the ammount of memory Java may use to run the plugin. Configure this based on the number of instances/data you will be collection as well as your server’s available memory. I believe NewRelic recommends 128mb but 64mb has worked well for me. The second change is adding ” > /var/log/newrelic-plugin.log 2>&1″ which pretty much directs all output to the log file as explained on the comment below.

VI. Execute the Daemon

First lets do a test to make sure things work. Execute your daemon to see if it is working fine. At this point the output should be going to the log file and if all goes well you should start seeing data on your NewRelic dashboard. But before that you need to mark the file as an executable otherwise you’ll get the following error message:

$ /etc/newrelic/newrelic_mysql_plugin-1.2.1/etc/init.d# ./newrelic-mysql-plugin.debian

bash: ./newrelic-mysql-plugin.debian: Permission denied

chmod +x newrelic-mysql-plugin.debian

After that you can run the daemon to see if everything is working fine:

Usage: /etc/init.d/newrelic-mysql-plugin {start|stop|status|restart|force-reload}

$./newrelic-mysql-plugin.debian start

* Starting New Relic mysql plugin daemon:

Note that you have start, stop, force-reload, restart and status at your disposal

VII. Make it start every time you reboot your computer

You may want to create a symbolic link to /etc/init.d/ to the actual location of new relic mysql plugin daemon in order to start the agent automatically at boot time.

We shall create a symbolic link:

ln -s /etc/newrelic/newrelic_mysql_plugin-1.2.1/etc/init.d/newrelic-mysql-plugin.debian /etc/init.d/newrelic-mysql-plugin

And then we should let Ubuntu know about it:

update-rc.d newrelic-mysql-plugin defaults

You should then get a similar message:

Adding system startup for /etc/init.d/newrelic-mysql-plugin …
/etc/rc0.d/K20newrelic-mysql-plugin -> ../init.d/newrelic-mysql-plugin
/etc/rc1.d/K20newrelic-mysql-plugin -> ../init.d/newrelic-mysql-plugin
/etc/rc6.d/K20newrelic-mysql-plugin -> ../init.d/newrelic-mysql-plugin
/etc/rc2.d/S20newrelic-mysql-plugin -> ../init.d/newrelic-mysql-plugin
/etc/rc3.d/S20newrelic-mysql-plugin -> ../init.d/newrelic-mysql-plugin
/etc/rc4.d/S20newrelic-mysql-plugin -> ../init.d/newrelic-mysql-plugin
/etc/rc5.d/S20newrelic-mysql-plugin -> ../init.d/newrelic-mysql-plugin

and you’re done! Congratulations!

Try rebooting your server and seeing if the daemon work properly.

First check the status on your server:

service newrelic-mysql-plugin status

because we tested earlier then it should pick up it is executing and you will get a line similar to the one below:

 * newrelic-mysql-plugin is running

Then go to NewRelic.com and see if you are getting data in your dashboard.