dbRecorder - Wave2 Wiki

Overview

Revision Control is often used to track changes to source code and other documents, so why not your database?.

Rather than extend similar tools such as Rancid I decided to prove the concept by writing a quick Java application that utilised Open Source libraries like SVNKit and jYAML. The features continued to creep and before I knew it I was recording more than just table definitions. Server configuration and database users all found their way into my Subversion repository with e-mail notification whenever anything changed - nice huh!.

Change Control

Using revision control you are able to keep a record of any changes made to the configuration / structure of your database in the same way that developers keep track of their source code.

Notification

Everytime you run dbRecorder the database configuration / structure of the live data is compared to the repository version. Along with storing these change in revision control you can also configure notification via e-mail (more methods to come). Great for knowing when something changes!.

This software is Alpha quality!!!, it works for me and I will update as I find bugs but please accept that it may not work for you without a few tweaks...you have the code so please do contribute.

Notifications are cool but where are all those changes kept?

dbRecorder currently stores all changes to your database structure using a Subversion repository.
There are many tools out there for working with Subversion but if you are new to SVN you should check out the following:

Requirements

*Java Runtime 1.5+

Supported Databases

Currently only MySQL is supported. Keep watching for other databases in future.

Configuration File Format

I decided to use YAML as the format for the dbRecorder configuration file as it is much easier on the eye than XML. Using YAML you can define multiple database hosts and schemas using simple indentation and the - character as you will see next.

The dbRecorder config file is divided into several sections allowing you to tailor individual aspects of the application as follows.

Subversion Section
Key	Value	Description
`Subversion:`	None	Start of Subversion config
`- Name:`	String	Subversion Repository Name (brief description)
`URL:`	String	Subversion Repository URL
`Username:`	String	Username with permission to update SVN Repository (NOT IN USE)
`Password:`	String	Password used to access SVN Repository (NOT IN USE)

Notification Section
Key	Value	Description
`Notification:`	None	Start of Subversion config
`- Method:`	String	Notification method
`Server:`	String	Notification server (smtp server)
`Port:`	String	Notification port (default 25)
`From:`	String	E-mail from address
`To:`	Array	Notification recipients e.g. [test1@dbrecorder.org,test2@dbrecorder.org]

MySQL Section
Key	Value	Description
`MySQL:`	None	Start of MySQL config
`- Hostname:`	String	MySQL Hostname/IP
`Repository:`	String	Subversion Repository Name (brief description)
`Username:`	String	Username used to access MySQL database
`Password:`	String	Password used to access MySQL database
`SecureShell:`	None	If your MySQL instance is running on an SSH secured host why not forward the ports.
`Hostname:`	String	SSH Host
`Username:`	String	SSH Username
`Password:`	String	SSH Password
`LocalPort:`	String	Port to allocate on the local host
`RemotePort:`	String	Port to map to on the remote host e.g. 3306
`Schemas:`	None	If fine grained control of schema access is required use this section
`- Name:`	String	Schema name
`Tables:`	Array	Array of tables to be included in the VCS e.g. [mysql, authstor]
Note: By default dbRecorder will strip any AUTO_INCREMENT=XXX from table definitions to prevent excessive notifications. If you require this information just add the tables to the following Auto_inc directive.
`Auto_inc:`	Array	Array of tables that require AUTO_INCREMENT details stored (removed by default) e.g. [mysql, authstor]
`Triggers:`	Array	Array of Triggers to be included in the VCS
`Events:`	Array	Array of Events to be included in the VCS
`Routines:`	Array	Array of Routines to be included in the VCS

Example Config File

Subversion:
 - Name: MySVNRepository
   URL: file:///home/mysql/svn
   Username: mysql
   Password: mysqlpass

Notification:
 - Method: smtp
   Hostname: localhost
   Port: 25
   From: dbRecorder@dbRecorder.org

MySQL
 - Hostname: localhost
   Repository: MySVNRepository
   Username: mysqluser
   Password: mysqlpass
   Schemas:
    - Name: mysql
      Tables: [user,test]
      Triggers:
      Events:
      Routines:

Download

Alpha Release

The following release is considered Alpha quality. Functionality is still growing and future release may work differently (e.g. VCS layout).

dbRecorder.zip (3.08 MB)

Limitations

Support for local file:/// based Subversion repository only in this release.
Support for e-mail change notification only.

Help Improve dbRecorder

The best way to improve dbRecorder is to use it and let me know what works, what doesn't and what's missing. You can do that using the Issue Tracker - it's easy.

Source Code

Browse Source

References

http://jyaml.sourceforge.net/

http://www.yaml.org/spec/

Thanks

Thanks to Alistair over at HoundDog Technology for helping test this 0.1 release.