Copy Data Between MySQL Databases with Sequel Pro
January 10, 2014
I often use Sequel Pro when I’m getting up to speed on the data model for a project or when I just want to debug in a more visual way than with the mysql command-line client. It’s a free OS X application that lets you inspect and manage MySQL databases. I also find it very useful for making small changes to the data while I develop and test web apps.
Quickly Copy Data Between Databases
I recently needed a way to copy a few dozen records from one camp to another. I tried using the "SELECT...INTO OUTFILE" method but ran into a permissions issue with that approach. Using mysqldump was another option but that seemed like overkill in this case—I only needed to copy a few records from a single table. At this point I found a really neat and helpful feature in Sequel Pro: Copy as SQL INSERT
I simply selected the records I wanted to copy and used the “Copy as SQL INSERT” feature. The SQL insert statement I needed was now copied to the system clipboard and easily copied over to the other camp and imported via the mysql command-line client.
The Sequel Pro website describes Bundles which extend the functionality in various ways—including copying data as JSON. Very handy stuff. Many thanks to the developers of this fine software. If you’re on OS X, be sure to give it a try.