MySQL Activity Report 1.0.0 review

MySQL Activity Report is a valuable database tuning aid. The MySQL Activity Report package is a tool to help MySQL database admini

License: GPL (GNU General Public License) File size: 41K Developer: Gert Dewit

0

MySQL Activity Report is a valuable database tuning aid.

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.

Requirements:
RRDtool

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-0.9.1-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-0.9.1.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-0.9.1.tar.gz
cd mysqlard-0.9.1
./configure --prefix=/usr --sysconfdir=/etc --datadir=/var/lib
make
make 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 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.

What's New in This Release:
This release adds support for MySQL 5 and configure support for MySQL and rrdtool installs in non-standard locations.