mysqlmigrate

Post by on March 24, 2014

I'm a huge proponent of working in a local environment, far far away from that sacred land named "Production". However, one hindrance is the database: keeping it up to date and representative of the production environment. Meet mysqlmigrate.

I wrote mysqlmigrate over time as a wrapper around the mysqldump tool. I have needed a way to quickly migrate databases and manipulate the data in process. So, I wrote this Bash utility that has the following options:

  • set the Innodb file format as Barracuda
  • set the innodb_file_per_table ON
  • ignore DEFINER statements
  • set the net_buffer_length
  • set the max_allowed_packet size
  • find and replace strings

It is primarily tested in Terminal on Mac OS X, but use it in Linux too. Word of caution: the utility is meant to be a "dump" migration tool, so it will completely drop the database for where you are migrating to and create it again. This assures you have a clean migration, but obviously, you risk losing data. This is why my flow is often the following:

$ mysqlmigrate -u root -p password -d acme
$ mvbup acme.sql
$ mysqlmigrate --from-user root --from-password password --from-host myapp.com --to-user root --to-password password --ignore-definer acme acme

I use this utility on a daily basis and have a few helper Bash aliases (such as mvbup) to speed up my flow. Here are a few examples:

  1. I need to dump a copy of the local "acme" database: 
     $ mysqlmigrate -u root -p password -d acme
  2. I need to migrate from a remote "acme" database, but ignore the DEFINER statement: 
     $ mysqlmigrate --from-user root --from-password password --from-host myapp.com --to-user root --to-password password --ignore-definer acme acme
  3. I need to migrate from a remote "acme" database and need to find and replace all occurrences of "myapp.com" with "myapp.dev": 
     $ mysqlmigrate --from-user root --from-password password --from-host myapp.com --to-user root --to-password password --ignore-definer acme acme

The files created by mysqlmigrate are straight outputs from mysqldump, so you can re-use those files with different options. Unfortunately, if you use find and replace on serialized PHP strings it will break those strings. But, I've not run into an issue with that yet. Necessity is a mother of invention.

Older Posts ยป