MySQL Activity Report

MySQL Activity Report is a powerful tool that aids in database tuning. It is designed to assist MySQL database administrators in gathering numerous database parameters and variables that can be useful for performance tuning or server monitoring purposes. The package's most crucial component is mysqlard, which is a daemon that collects data from a MySQL server and stores the information in a Round Robin Database.

Additionally, the mysqlard package includes sample graphing, php, and cron scripts. The cron scripts are ideal for maintaining archives of the produced graphics, while the php script displays the generated graphics and provides tuning tips. For proper usage, the tool requires RRDtool.

Installation of MySQL Activity Report varies depending on the system in use. For instance, if you are using an i386 RPM-based system like RedHat, Fedora, Mandrake, or SuSE, you can use your preferred graphical installation tool or run "rpm -ihv mysqlard-0.9.1-1.i386.rpm." Also, it is essential to verify that the Activity Report daemon begins after the MySQL server.

For running non i386 RPM-based systems, rebuilding from the source RPM with the –-rebuild option to rpm or rpmbuild is the way to go. Besides, the RPM can be built from the tarball with the "rpmbuild -ta mysqlard-0.9.1.tar.gz" command, while non-RPM-based systems can utilize the standard configure - make -make install build cycle.

To ensure proper configuration, the sample graphing script, and the daemon start script use the configuration script in the sysconfig directory ("/etc/mysqlard.cnf" in most cases). Security is a vital aspect of MySQL Activity Report. It is recommended that, to configure the daemon safely, create a mysql user with only USAGE permissions, no permissions on any database, and no password. By doing so, there is no need to enter a plain text password in any of the config or php files. To add a monitoring user which can only connect from localhost, run the following command in the MySQL shell: "GRANT USAGE ON *.* TO mysqlar@localhost;"

If the daemon does not run on the same system as the MySQL server, use the hostname or IP address of the monitoring system instead of localhost. Connection to the daemon anonymously is not advisable; hence editing the daemon start script is mandatory. In the line " ${MYSQLARD} --step=${step} --datadir=${datadir}", replace "mysqlar" with a preferred user for authentication.

For users who do not want to run the daemon as root, they can add a special system user with a locked account to run the daemon. Editing the daemon server script and adding "su - marduser" to the daemon start command can achieve this, provided the datadir is writeable for that user. The daemon start command should look like this: "su - marduser -c "${MYSQLARD} --step=${step} --datadir=${datadir} --user=mysqlar". Despite not creating and reading files or listening to sockets, running this daemon as root poses minimal danger.

In conclusion, the latest MySQL Activity Report release incorporates several new features, including support for MySQL 5, and configuration support for MySQL and rrdtool installs in non-standard locations. Employing MySQL Activity Report assures database administrators' performance tuning and efficient server monitoring.