jon zobrist (dot) com

This will be a quick install to setup your server to e-mail you daily mysql reports using the cool mysqlreport application at hackmysql.com

As the user you want to set this up on, run the following commands.

(Note that I touch and then chmod og-rwx the .my.cnf file, since you’re putting your users mysql password in it, creating it and making it unreadable before editing is always a good idea.)

wget http://hackmysql.com/scripts/mysqlreport
chmod uog+x mysqlreport
mkdir bin logs
mv mysqlreport bin/
touch ~/.my.cnf
chmod og-rwx ~/.my.cnf
vi ~/.my.cnf

Now, put in a client section like this in, you can find your sock file with grep sock /etc/my.cnf, or grep sock /etc/mysql/my.cnf

[client]

user="root"

password="password"

socket=/var/run/mysqld/mysqld.sock

Now, install perl’s DBD::MySQL

Debian/Ubuntu:

sudo su

apt-get install libdbd-mysql-perl

via CPAN/Other Linux/Generic BSD:

perl -MCPAN -e shell

Optional, update CPAN:

install Bundle::CPAN

reload cpan

(If there are errors on following some dependencies exit, and restart your CPAN shell, then start with install Bundle::CPAN)
Install perl DBI

install DBD::mysql
exit

Now, run it to test, you should see a bunch of text output

~/bin/mysqlreport

Now, set it up to run from cron

crontab -e

Add a line like this (this will email to test@example.com a full report nightly at midnight, and put the last nights report in /home/USERNAME/logs/mysqlreport)

0 0 * * * /home/USERNAME/bin/mysqlreport --email test@example.com > /home/USERNAME/logs/mysqlreport.log 2>&1