The MySQL Activity Report package is a tool to help MySQL database administrators to collect several database parameters and variables. These collected values can be used for server monitoring or performance tuning purposes.
The most important component of the MySQL Activity Report is
mysqlard. This daemon collects the data from a MySQL server and stores these values in a Round Robin Database.
The mysqlard package also contains example graphing, cron and php scripts. The cron scripts can be used to keep archives of the generated graphics and the php script shows the generated graphics and tries to give some tuning tips.
An example of the output from the php script can be found on the demo page. The example shown is a snapshot of a client's database server that's allready been tuned at the system level but would benefit from extra database level tuning.
The package has been released under the GPL.
Dependencies
For the data storage in a RRD Toby Oetiker's excellent rrdtool library is used and the graphing script uses rrdtool to generate the graphics.
rrdtool's home page is :
http://people.ee.ethz.ch/~oetiker/webtools/rrdtool/
Obviously there's a dependency on MySQL. Why would anyone install this package without MySQL anyway ? The reason I mention it anyway is that not every MySQL server installation also has the mysqlclient libraries installed which are used by mysqlard.
Download
The package can be downloaded from this site. There's a download page dedicated to the project where you'll find the latest version.
Choose the package format that matches your distribution. The are source tarball, source RPM and i386 RPM (build on Fedora) packages availlable.
Installation
If you're using an i386 RPM based system like RedHat, Fedora, Mandrake or SuSE, just use the graphical installation tool of your choice or run "
rpm -ihv mysqlard-1.0.0.1.i386.
rpm"
If you want the daemon to start at boot time run:
chkconfig --level 345 mysqlard on
Verify that the Activity Report daemon starts after the MySQL server. The mysqlard daemon can cope with a MySQL server that goes away but it expects the MySQL server to be running at startup time.
If you're running a non i386 RPM based system you can rebuild from the source RPM with using the --rebuild option to rpm or rpmbuild.
You can also build the RPM from the tarball with
rpmbuild -ta mysqlard-1.0.0.tar.gz
There's a source tarball available for non RPM based systems. Just use the common configure -
make -
make install build cycle.
For example the build process using the values from the rpm spec file :
tar xzvf myslqard-1.0.0.tar.gzcd mysqlard-1.0.0../configure --prefix=/usr --sysconfdir=/etc --datadir=/var/libmakemake install
After installation you'll find the mysqlard.server script in datadir (/var/lib/mysqlard if you used the example). Copy this script to the right location if you want the daemon to start at boot time.
The example cron and php scripts are installed in the same localtion.
Configuration
Both the sample graphing script and the daemon start script use the configuration script in the sysconfig directory (usually /etc/mysqlard.cnf)
Security
The safest way to configure the daemon is to create a mysql user who has only USAGE permissions for MySQL, no permissions on any databases and no password. Configured like this, you don't have to put a plain text password in any of the config files or php files.
Run the following command in the MySQL shell to add a monitoring user without a password who can only connect from localhost :
GRANT USAGE ON *.* TO mysqlar@localhost;
If the daemon doesn't run on the same system as the MySQL server, use the hostname or the IP address of the monitoring sytem in stead of localhost.
If you don't want to connect anonymously but with the mysqlar user as described above, you'll have to edit the daemon start script. Search for the line
${MYSQLARD} --step=${step} --datadir=${datadir}
and replace it with
${MYSQLARD} --step=${step} --datadir=${datadir} --user=mysqlar
using the mysqlar user from the example.
If you don't want the daemon to run as
root, you can also add a special system user with a locked account to run the daemon. You'll have to edit the daemon server script and add a "su - marduser" to the daemon start command, and make sure the datadir is writeable for this user. The daemon start command should become
su - marduser -c "${MYSQLARD} --step=${step} --datadir=${datadir} --user=mysqlar"
However, the danger of running this daemon as root is minimal because it doesn't create files, read files, listen to sockets, etc.
Bugs and other problems
Don't hesitate to contact me if you run into trouble but please check the FAQ first.
