ptmysqllog /

Filename Size Date modified Message
app
conf
public
test
106 B
35.1 KB
4.7 KB

ptmysqllog

ptmysqllog is a web app which can log records from your MySQL general_log table into your Papertrail as they happen. I developed it specifically to solve the problem of monitoring MySQL instances hosted in Amazon RDS using Papertrail. Check out my video for a demo: http://youtu.be/iB2LkqD3Wxs

Configuration

ptmysqllog is a Play 1.25 app which requires minimal configuration as follows:

  1. Go to the ptmysqllog/conf folder and edit the application.conf file. Go to the bottom of the file and change all xxx placeholders to match your RDS MySQL instance:

    db.url=jdbc:mysql://mysql-rds.xxx.us-east-1.rds.amazonaws.com:3306/mysql
    db.driver=com.mysql.jdbc.Driver
    db.user=xxx
    db.pass=xxx

  2. In the same conf folder, edit the log4j.xml file. Near the top of the file look for the first appender element and change xxx to your Papertrail port number:

    <appender name="papertrail" class="org.apache.log4j.net.SyslogAppender">
    <!-- TODO specify your Papertrail port below from: https://papertrailapp.com/systems/setup -->
    <param name="SyslogHost" value="logs.papertrailapp.com:xxx"/>

  3. (Optional) ptmysqllog polls the RDS instance you configured in (1) every second, sending all new general_log records to the Papertrail instance you configured in (2). To change the polling frequency, go to the ptmysqllog/app/jobs folder and edit the MySqlMonitorJob.java file. Near the top find the @every("1s") line and change "1s" to the frequency you want:

    @OnApplicationStart
    @every("1s")
    public class MySqlMonitorJob extends Job {

  4. Save your changes and commit to your local git repo:

    • git add .
    • git commit

Now get ready to deploy this baby...

Deployment

The simplest way to deploy the app is to Heroku. However, because you can't make any assumptions about which IP address Heroku will run your app from, before deployment you need to open up your RDS instance to connections from an arbitrary IP address (make sure your DB is protected by strong passwords). This means creating a DB Security Group in your RDS console to authorize 0.0.0.0/0 and assigning that security group to the DB Instance you want the app to connect to. You will need to apply this change and restart your DB Instance for it to take effect. Then you can deploy the app using simply:

  • heroku create <your-app-name>
  • git push heroku master

The other consideration with Heroku is you should configure at least 2 dynos for your app. If you stick with only the free single web dyno, your app will be idled after one hour of the app not being accessed, making it useless as a continuously polling background app.

Alternatively, you can also deploy the app to AWS Elastic Beanstalk or your own server to get around the non-deterministic IP address and dyno issues. However, then you will need to install the Play framework locally and use it to build the app as a war to deploy. If you want to do this, see the instructions at: http://www.playframework.org/documentation/1.2.5/deployment

Notes

  • See the video for what the app actually logs from your RDS instance, the format used, and what is filtered out (RDS heartbeat queries, etc.).
  • The code assumes the app will be running in the same time zone as your RDS instance.
  • The app auto-recovers from connection errors post successful startup, for example if you have to reboot your RDS instance. In fact, it's kind of cool to watch the logs of your instance shutting down, then restarting in Papertrail.
  • Although designed for the RDS hosted use case, the app can actually be used with any MySQL instance it can connect to with read access.
  • Also, although currently supporting only the general_log table, the app was designed to be extensible to support other system tables in your MySQL such as slow_log.

So now go use Papertrail to monitor your MySQL instances on Amazon RDS and everywhere!

ptmysqllog is licensed under GPLv3 by William Cheung