Home

Mysql Migrations with PHP

Attention: migrated to github https://github.com/idler/MMP/

MMP is a tool to enable automatic creation of MySQL migration scripts and to handle their loading/removal to a given database schema.

It can:

  • create initial schema from an existing database
  • load the schema into a database (for example on new test or production servers)
  • create migration scripts (as PHP classes)
  • Load migrations into a database
  • Update to a specified revision (time)

Configuration

The configuration is done by creating a config.ini file in MMP's main folder. A sample config file called config.ini.sample is provided to help a little bit with that.

The location of config file can be overridden via command line option --config="<path_to_file>"

Available configuration options:

OptionDescription
hostHostname of the database server
userUser on the database server
passwordPassword for the database user
dbDatabase to track for any schema changes
exclude_tablesTables matching this regex pattern will not be tracked for changes. Value should be quoted (ex. "/regex[^0-9]\s+test/i")
savedirFolder where migration files shall be stored
verboseOutputs additional info when executing commands (a bunch of queries at the moment)
versiontableDatabase table where revision numbers of the applied migrations will be stored

Note: all the configuration options can also be supplied via command line. Their format in that case would be --<config option>=<value>.
For example: --host=localhost, --db="testing", etc.

Commands

All MMP commands are supplied in the form like this: ./migration.php [configuration options] command [command options]

CommandDescription
helpList available commands. Use help <command> for a more detailed description of each.
schemaTake initial snapshot of the database schema.
initLoad initial database schema (of what's been created with schema command above). No migrations are loaded after this.
createGenerate a new migration.
listList available and applied migrations.
migrateLoad or remove migrations. Target migration point is provided as an absolute or relative date from the current moment, in the format supported by strtotime() php function, such as "-2 hours", "2011.03.01 16:00", etc. If nothing is provided, all migrations up to current time will be loaded.

Usage

To start using this tool you must first create the initial schema

./migration.php schema

Then add db/ dir to version control (or whatever else savedir was set to).

Now you can change database structure and then call:

./migration.php create

Add the newly-created migration file to version control, go to the another working copy, update filesystem from VC, and load initial schema

./migration.php init

Now your initial database created and you can migrate to any time

./migration.php migrate -2 hour

Or migrate database to latest version

./migration.php migrate

./migration.php help

                    MySQL Migration with PHP
---------------------------------------------------------------------
  help:       display this help and exit
  schema:     create schema for initial migration/installation
  init:       load initial schema (install)
  create:     create new migration
  migrate:    migrate to specified time

In migrate comand you can use strtotime format
Examples:
*********************************************************************
./migrate.php migrate yestarday
./migrate.php migrate -2 hour
./migrate.php migrate +2 month
./migrate.php migrate 20 September 2001
./migrate.php migrate
********************************************************************
Last example will update your database to last version


---------------------------------------------------------------------
Licenced under: GPL v3
Author: Maxim Antonov <max.antonoff@gmail.com>

Russian version of this tutorial

Updated

Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.