Install MySQL Activity Report from Source

MySQL Activity Report is a handy database reporting tool that uses RRD (Round Robin Database) to display hourly, daily, weekly, and monthly graphs and gives helpful performance tuning recommendations for your MySQL installation. Here are the steps to install it from source on Ubuntu Server 12.04.3 while logged in as root. This assumes that you have the build-essentials, header files, etc necessary to build software already installed.

1. Change into your local source directory

cd /usr/local/src

2. Download the source files for rrdtool and mysqlard

wget http://oss.oetiker.ch/rrdtool/pub/rrdtool-1.4.8.tar.gz
wget http://gert.sos.be/downloads/mysqlar/mysqlard-1.0.0.tar.gz

3. Unzip the files

tar -zxvf rrdtool-1.4.8.tar.gz
tar -zxvf mysqlard-1.0.0.tar.gz

4. Change into the rrd directory

cd rrdtool-1.4.8

5. Install dependencies

apt-get install libpango1.0-dev libxml2-dev

6. Build rrdtool (will install to the /opt/rrdtool-1.4.8 directory)

./configure && make && make install

7. Change into mysqlard directory

cd ../mysqlard-1.0.0

8. Build mysqlard (will install to /var/lib/mysqlard directory)

./configure --prefix=/usr --sysconfdir=/etc --datadir=/var/lib --with-rrd=/opt/rrdtool-1.4.8 && make && make install

9. Move files to proper places

mv /var/lib/mysqlard/mysqlard.server /etc/init.d/
mv /var/lib/mysqlard/mysqlard.cnf /etc/

10. Change permissions

chmod +x /etc/init.d/mysqlard.server

11. Create symlinks

ln -s /opt/rrdtool-1.4.8/lib/librrd.so.4 /usr/lib/librrd.so.4
ln -s /opt/rrdtool-1.4.8/bin/rrdcgi /usr/bin/rrdcgi
ln -s /opt/rrdtool-1.4.8/bin/rrdtool /usr/bin/rrdtool
ln -s /opt/rrdtool-1.4.8/bin/rrdupdate /usr/bin/rrdupdate

12. Create a MySQL user for application (replace your passwords where necessary)

mysql -u root -pYourDBPassword -e "CREATE USER 'mysqlar'@'localhost' IDENTIFIED BY 'NewUserPassword'; GRANT USAGE ON * . * TO 'mysqlar'@'localhost' IDENTIFIED BY 'NewUserPassword' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0; FLUSH PRIVILEGES;"

13. Edit /var/lib/mysqlard/mysqlar.php and set MySQL password

$sqlpassword = "YourNewPassword";

14. Edit /etc/init.d/mysqlard.server file. At the top, find this line: MYSQLUSER=${MYSQLUSER:=”mysqlar”} and add this line underneath it.

MYSQLPASS=${MYSQLPASS:="YourNewPassword"}

At the bottom, inside the case 1 statement, underneath initrrd, add the following switches:

Find this line: ${MYSQLARD} --step=${step} --datadir=${datadir} $MYSQLHOST --pidfile=${pidfile} ${slaveopt}
Edit line to this: ${MYSQLARD} --step=${step} --datadir=${datadir} --user=${MYSQLUSER} --password=${MYSQLPASS} $MYSQLHOST --pidfile=${pidfile} ${slaveopt}

15. Add a cron job to collect RRD stats

crontab -e
*/5 * * * * hourly=1 daily=1 weekly=1 monthly=1 /usr/bin/mysqlar_graph > /dev/null

16. Start the service

service mysqlard.server start

17. Add an alias to your Apache virtual hosts conf file. This will be different on every system so I can only point you in the right direction. Edit your virtual hosts file located in /etc/apache2/sites-available/xxxx.conf and add the following:

Alias /sqlreport "/var/lib/mysqlard"

18. In a web browser, you should now be able to navigate to this URL:

http://hostname/sqlreport/mysqlar.php

19. Start this service at boot time.

update-rc.d mysqlard.server defaults

20. Last but not least, we need to make sure MySQL starts before our service.

mv /etc/rc2.d/S20mysqlard.server /etc/rc2.d/S99mysqlard.server
mv /etc/rc3.d/S20mysqlard.server /etc/rc3.d/S99mysqlard.server
mv /etc/rc4.d/S20mysqlard.server /etc/rc4.d/S99mysqlard.server
mv /etc/rc5.d/S20mysqlard.server /etc/rc5.d/S99mysqlard.server