I'm going to be releasing a site soon that is on it's own dedicated server and can find plenty of tools for monitoring the server its self (disk usage, cpu usage, memory usage, etc) but can't really find anything for MySQL and was wondering what other people use or recommend. I'm not necessarily looking for something with pretty charts and graphs, if all it does is write to a log that's fine because I can just point Splunk at it. I know MySQL writes some of its own logs (slow query log and no indexes log) but I was looking for something that can actually keep an eye on the database and say, for example, let me know when several queries are backed up in the queue because one of the tables they use has been locked by another query for the last 20 seconds.
You should check Zabbix or Cacti for graphing all kinds of MySQL statistics and Nagios for setting your alert thresholds and notifications
Another great tool for monitoring your MySQL instances in real time is mtop, it colors all problematic and locked queries right away. Make sure you got it open when releasing new features or doing your stress tests
For checking the status of MySQL you can use innotop (it's much better than mtop), but it's mainly useful to debug problems rather than to check MySQL load.
If you want some kind of automatic check that you can schedule every X seconds / minutes, you can just add a monitoring user and send a SHOW PROCESSLIST to MySQL. This will tell you if you're getting overloaded with too many queries.
If you're using slaves, you can check the value of Seconds_Behind_Master doing SHOW SLAVE STATUS. If Seconds_Behind_Master is Null, it typically means there is a consistency problem between the master and the slave and that the replication has been stopped. If you need something more accurate than Seconds_Behind_Master, you can create a heartbeat table in the master, update it regularly and check the heartbeat value in the slave.
Be sure to check out Kontrollbase. It does way more graphing than any other mysql app out there and it also monitors the variables with algorithms to generate performance tuning reports. So if your server needs a larger read_buffer or innodb_buffer_pool_size or any of the various other settings it will tell you what the variable needs to be set to for best performance. here's their site: http://kontrollsoft.com/software-kontrollbase
I've been using it for a while and it's pretty cool. A lot like the enterprise tools you get from mysql.com