Tuesday, July 12, 2011

SchemaMgr - MySQL schema management tool

SchemaMgr is a simple perl tool to manage schema change in MySQL DBs. I wrote this in 2007/2008 and it has been used in production for many years.


Each change is assigned a version number and placed in a file. When the SQL in the file is executed successfully, a special table is set with that version number. Subsequent runs install only the higher versioned files.

It can also be used to reinstall views and stored procedures.

The best practice is to copy the file and change X in the filename and in the $DB_NAME variable.

$ ./bin/schemamgr_X.pl
Usage: either create or upgrade X database
schemamgr_X.pl -i -uUSERNAME -pPASSWORD [-vVERSION] [-b]
updates DB of to current (default) or requested version
schemamgr_X.pl -s -uUSERNAME -pPASSWORD
reinstalls all stored procedures
schemamgr_X.pl -w -uUSERNAME -pPASSWORD
reinstalls all views
schemamgr_X.pl -q -uUSERNAME -pPASSWORD
Requests and prints current version
Optional Params
-vXX -- upgrades upto a specific version number XX
-b -- backs up the database (with data) before upgrades
-nYY -- runs the upgrades against database YY - default is X
By convention you create ONE create_X_objects_v1 file with a date.
All other files are update files with greater than v1 numbers.
|-- create_X_objects_v1_20110615.sql
|-- update_X_objects_v2_20110701.sql
`-- update_X_objects_v3_20110702.sql

Posted via email from aicoder - nealrichter's blog