MySQL DevOps First Step: Revision Control

MySQL environments are notorious for being understaffed – MySQL is everywhere, and an organization is lucky if they have one full-time DBA, as opposed to a developer or sysadmin/SRE responsible for it.

That being said, MySQL is a complex program and it’s useful to have a record of configuration changes made. Not just for compliance and auditing, but sometimes – even if you’re the only person who works on the system – you want to know “when was that variable changed?” In the past, I’ve relied on the timestamp on the file when I was the lone DBA, but that is a terrible idea.

I am going to talk about configuration changes in this post, mostly because change control for configuration (usually /etc/my.cnf) is sorely lacking in many organizations. Having a record of data changes falls under backups and binary logging, and having a record of schema changes is something many organizations integrate with their ORM, so they are out of scope for this blog post.

Back to configuration – it is also helpful for disaster recovery purposes to have a record of what the configuration was. You can restore your backup, but unless you set your configuration properly, there will be problems (for example, an incompatible innodb_log_file_size will cause MySQL not to start).

So, how do you do this? Especially if you have no time?

While configuration management systems like chef, puppet and cfengine are awesome, they take setup time. If you have them, they are gold – use them! If you do not have them, you can still do a little bit at a time and improve incrementally.

If you really are at the basics, get your configurations into a repository system. Whether you use rcs, cvs, subversion or git (or anything else), make a repository and check in your configuration. The configuration management systems give you bells and whistles like being able to make templates and deploying to machines.

It is up to you what your deployment process is – to start, something like “check in the change, then copy the file to production” might be good enough, for a start – remember,  we’re taking small steps here. It’s not a great system, but it’s certainly better than not having any revision control at all!

A great system will use some kind of automated deployment, as well as monitoring to make sure that your running configuration is the same as your configuration file (using <A HREF=””>pt-config-diff). That way, there are no surprises if MySQL restarts.

But having a great system is a blog post for another time.