Vladimir Vuksan's blog

I have a need in my development environment to easily create/drop mySQL databases and users. Initially I was gonna implement a simple hacky HTTP GET method but was dissuaded by Ben Black from doing so. He suggested I write a proper RESTful interface. Without further ado I present to you dbrestadmin

https://github.com/vvuksan/dbrestadmin

It is my first foray into writing RESTful services so things may be rough around the edges. However it allows you to do following

• manage multiple database servers
• create/drop databases
• list databases
• create/drop users
• list users
• give user grants
• view grants given to the user
• view database privileges on a particular database given to a user

For example need to create a database called testdb on dbserver ID=0 use this cURL command

curl -X POST http://myhost/dbrestadmin/v1/databases/0/dbs/testdb

Create a user test2 with password test

curl -X POST "http://localhost:8000/dbrestadmin/v1/databases/0/users/test2@localhost" -d "password=test"

Give test2 user all privileges on testdb

curl -X POST "http://localhost:8000/dbrestadmin/databases/0/users/test2@'localhost'/grants" -d "grants=all privileges&database=testdb"

There is more. You can see all of the methods here

https://github.com/vvuksan/dbrestadmin/blob/master/API.md

Improvements and constructive criticism welcome

Here are slides from the August 3rd, 2010 Boston DevOps meetup where Jeff Buchbinder and I spoke about deployment and other helpful hints

http://www.scribd.com/doc/35757228/Deploying-Yourself-Into-Happiness

Slides have been slightly modified based on the feedback we received at the meetup. If you have any questions please post them in comments and I'll attempt to answer them.

This is a question that often comes up in deployment discussion. How do you rollback in case of a "bad" deploy ? Bad deploy can be any of the following

• Site completely broken
• Significant performance degradation
• Key feature(s) broken

There are obviously a number of ways to deal with this issue. You could put up a notice on the site that x and y feature is broken while you work to fix it. Same with performance degradation. Let's however deal with rollback ie. you decided (determined by a number of different factors) that the stuff you just deployed is broken and you should roll back to a previous last know version. In such a case you would

• Undo any configuration changes you may have applied (often none)
• Deploy last known good version that worked. This is one of the reasons why I prefer using labelled binary packages. I simply instruct the deployment tool to install version 1.5.2 which was last good version and off we go.

The only caveat are database changes. In general you can't easily undo DB changes especially in the situations where you discover a deployment problem couple hours after deployment has taken place since by then users may have added new posts, changed their profiles etc. It would be a major effort to undo all DB changes, evaluate newly added data and whether it needs to be changed. That said DB changes are usually not a problem if you follow these easy steps

1. Don't do any column drops immediately after the release. You can do those in QA but in production those can wait. In most cases they only take up space. I have heard of places that would first zero out then drop "unused" columns once a quarter or so.
2. Related to 1. never ever use SELECT * since if you drop or add a column your code may break during roll back
3. If there are data changes you have to do ie. update carrier set name="AT&T" where name="Cingular", have the reverse SQL statement ready as the insurance policy. Those are quite easy to implement.
4. You don't have to worry about added tables since older version will not use them.
5. You don't have to worry about added columns provided you don't do 2. and have not placed constraints ie. NOT NULL. In that case you may need to adjust those or drop them during rollback.

The wildcard in all this is added or removed constraints ie. new foreign keys. There is no single solution for this one. Perhaps the right policy is to discuss constraints prior to deployment and have a plan ready on what to do. Good luck.

Next Boston DevOps meetup we'll try something new, Jeff Buchbinder of FreeMed Software fame and myself will talk about "Deploying your way into happiness". If you want flavor of the kinds of things we'll talk about you can check out my Devops homebrew post. We will go into much more detail with actual code snippets and some of the omitted nitty gritty details. We will also open the floor for questions.

Date for the meetup is August 3rd, 2010 from 6-8 pm and we'll be meeting at Microsoft's New England R&D center. I expect we'll start presenting around 6:45 or so.

Please register at

http://www.eventbrite.com/event/770217742

since we need to provide building security at NERD with the list of people attending.