By

Install MySQL Activity Report from Source

安装过程确实与下文叙述的差不多. 唯一有点遗憾的是,好像不能出图,这个我找了下原作者写的博客,上面提到了这个是个bug,说后面会解决,可惜没有下文了.

原作者与此工具相关的链接: http://fossies.org/linux/privat/mysqlard-1.0.0.tar.gz/mysqlard-1.0.0/man/mysqlar_graph.1
https://fossies.org/linux/privat/old/mysqlard-1.0.0.tar.gz/mysqlard-1.0.0/man/mysqlar_graph.1
在此链接可下载到源码包,但源码包也是没有包含图片的. 如果有人会修改这个,麻烦修改后分享下.
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

  1. 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

  1. Unzip the files

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

  1. Change into the rrd directory

cd rrdtool-1.4.8

  1. Install dependencies

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

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

./configure && make && make install

  1. Change into mysqlard directory

cd ../mysqlard-1.0.0

  1. 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

  1. Move files to proper places

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

  1. Change permissions

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

  1. 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

  1. 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;”

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

$sqlpassword = “YourNewPassword”;

  1. 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}

  1. 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

  1. Start the service

service mysqlard.server start

  1. 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”

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

http://hostname/sqlreport/mysqlar.php

  1. Start this service at boot time.

update-rc.d mysqlard.server defaults

  1. 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

原文地址: http://www.paperstreetonline.com/?p=308