I found this post on a project called Taps. It’s a database proxy to slurp data out of one database and dump it into another, which can be from a different vendor (e.g. it can handle MySQL -> PostgreSQL migrations).
Whenever possible, I’d rather leave serialization to that which knows it best, in my case
The blog post states:
Migrating databases from one server to another is a pain: mysqldump on old server -> gzip -> scp big dump file -> gunzip -> mysql. It takes a long time, and is very manual and (and thus error-prone), and generally has the stink of “lame” hanging about it.
My solution: UNIX. The example below uses PostgreSQL, but the general structure holds true for MySQL. It comes in two flavors; the pull method:
ssh -C current_db_server 'pg_dump current_database' | psql new_database
or the push method:
pg_dump current_database | ssh -C new_db_server 'psql new_database'
The mechanics are strikingly similar to the, ‘stink of “lame”,’ but no babysitting required. I get compression (
-C), encryption (
SSH), and data integrity (
pg_dump) from battle-hardened tools; all nicely wrapped into a single command.
It generally “just works”, but if your connection is interrupted, you’ll have to drop the new db and start again. Remember to use screen!
Taps doesn’t require SSH, but I suppose it is a given in any environment. Worst case scenario is establishing a tunnel using SSH’s
Taps can also migrate between database vendors, which is interesting. We went from MySQL to PostgreSQL and had to tweak an existing tool to do so.
Watch out for issues like, “Foreign Keys get lost in the schema transfer,” ouch. Eh, whatever, RI is for stuffy folks.