I’m attempting this is be as unbiased as possible, since I write the Monolith application. This will hopefully help one decide between a free MySQL server monitoring system and paying for a per-server based licensed product. Both have strengths and weaknesses that should be pointed out before making a decision. You can infer the weaknesses based on the strengths below. That said, let’s just get into it.

Strengths of each product over the other

Monolith - MySQL DBA Console

• presents overall database size, index size, data size, number of schema per server, and aggregate statistics for all monitored servers
• runs mysql server backups remotely and reports on backup state execution
• collects cnf files from each server during the talkback script execution for historical viewing
• built on the LAMP stack, no need for tomcat/jboss knowlege
• provides …

The Boston MySQL User Group was lucky enough to get Keith Murphy to speak at the June User Group meeting, about backups.

Direct play the video at:
http://technocation.org/node/559/play

Direct download the video (351 MB) at:
http://technocation.org/node/559/download

Links referred to in the presentation:

MyLVMBackup by Lenz Grimmer
http://lenz.homelinux.org/mylvmbackup/

InnoDB Hot Backup:
Prices are at:
http://www.innodb.com/hot-backup/order/
and at the time of this writing are:
1-Year License ? 390 USD$ 605 per server
…

Did you notice that I moved this blog from pair Networks to pairLite hosting?

Probably not, unless you check the DNS of xaprb.com regularly!

Don’t you hate it when people say “I’m moving my blog, I hope there won’t be more than a few days of downtime, blah blah…” Why is this ever necessary, I wonder? I wonder the same thing about a lot of hosting providers — recently I had a client in my consulting practice whose (very large, well-known) hosting provider tried to help them with some very simple MySQL work and ended up causing them an obscene amount of downtime, like many many days, and there was no end in sight. As I spoke on the phone with him and asked him about his business, he said “we have X thousand users in our beta.” long pause. “Well, we did …

If you have Monolith installed in a RedHat cluster environment and are wondering how to make the cron job scripts only execute on the active cluster node, this script is a working example. It needs to be installed and running on each cluster node. The only variable to change is “MYHOST” depending on which node the script is running on. This analyzes the output of /usr/sbin/clustat for active node status and service type.

Feel free to use this as a starting point for other RH cluster aware wrappers.

Here are the associated crontab entries for the monolith cluster aware wrappers.

# Monolith Monitor Wrapper Scripts
02 * * * * root /usr/local/bin/monolith_cluster_report-generator.sh > /dev/null 2>&1
*/15 * * * * root /usr/local/bin/monolith_cluster_agent.sh > /dev/null 2>&1
02 * * * * root /usr/local/bin/monolith_cluster_cronexec.sh > /dev/null 2>&1
…

If you’re verifying successful backups by using the last line of a mysqldump sql file that says “dump completed” - be aware that prior to version 5.0.27 mysqldump does not output that line. Confusion could ensue!

If you read my blog, chances are you are aware of this new fad called the internet, and this new language called PHP. You’re also probably aware of things like MySQL and SQL, and the importance of data to visitors of your website. So, keeping that in mind, I’d also like you to know about backing up this data.

As I grow my business, the number of clients I run into that do not do backups, astounds me. I’m not talking about end user laptops/desktops, but rather backups of their SugarCRM database, or their file server, or even their company website, which is powered by Drupal/Wordpress/CMS Du Jour. When, not If, the database is corrupted, or the disk fails, they have no idea what they would do then. They just hope that it doesn’t happen to them.

So I did some thinking, and decided I should write a post, and make available to the world, a simple script, which I believe will at least give you onsite backups. This is what the …

This integrates with Monolith, but the database update function can be stripped out for use without Monolith. The idea is that this script is a wrapper for mysqldump that does backup file consistency checking, email reporting, file based logging and directory pruning.

I used to have one script for daily, weekly, and monthly all running out of /etc/cron.daily /etc/cron.weekly /etc/cron.monthly - respectively. But maintaining 3 scripts is foolish if one can do everything. So I added some variables to check day of week and day of month to achieve this.

Enjoy the code. Script Link here.

I’ve been giving presentations at work each week about MySQL DBA topics. An hour of speaking about the things one does on a daily basis is rather fun, and hopefully spreads the work about how efficient MySQL is to administer and setup, as well as optimize and troubleshoot vs MSSQL and Oracle - for which we also have weekly classes.

Recently I’ve been covering backup and recovery methods, which brings me to my next point - the talkback scripts I have been integrating into my Monolith application. These scripts are wrappers for mysqldump that do extensive error checking for file completion, directory pruning to rotate out old backups per the backup retention period policy, and then report back via email and to a separate database for tracking purposes.

Here is the create table for the reporting aspect. Note: this is part of the Monolith 1.2 release which is still in beta. Stay tuned for the release that includes this, as well as …

Sometimes you need to have the general query log on and even though it causes more disk I/O than you may want, it’s good for troubleshooting. This log can and probably will fill up your disks rather quickly. Then there’s the slow query log - setting log_slow_queries and log_queries_not_using_indexes will write out the queries that take longer than long_query_time to execute, as well as any query not using an index.

So, since MySQL does not apply the expire_logs_days value to these logs - only to the binary log (log_bin), we need another solution. There are probably a bunch of custom scripts out there that do this, but big surprise - we have one as well. This was originally written by Jim Wood until I got my hands on it and made some changes. The changes are listed in the head of the script. This little guy will rotate the logs out to another directory and gzip them. …

I try to do a decent job of advocating for caring about good backups and business continuity strategies in my 7 Deadly Habits article. But this one beats them all:

Just too funny and great not to share. Found via this reddit article, where there is a lively discussion underway.