One of the significant challenges of developing a site locally is the need to sync local database changes to a remote server, often repeatedly. For years, my solution was to manually copy changes from one database to the other, which absorbed a significant amount of time.

Rebranding this site provided the opportunity to review my workflow processes, and database migration was one of the first problems I addressed. After looking at several different apps and approaches, I found an excellent solution that can be run in a single line in the Terminal of Mac OS X.

This solution does make several assumptions:

1. You are using OS X and MAMP as a local server (although it should be relatively simple to move this lesson to another platform and server setup).
2. You have some familiarity with the Terminal.
3. You are accessing the server using ssh with public and private keys.
4. You have access to root or admin accounts on both the local and remote MySQL servers.
5. You keep backups of your work.

In the Terminal, the command is:

/Applications/MAMP/Library/bin/mysqldump -u username -ppassword databasename | 
ssh user@ipaddress "mysql -u root -ppassword databasename"

A quick explanation:

• the first part is the path to the local mysqldump utility, which allows you to export a database en masse
• the second part is the username and password for the local MySQL server. Often this will be root and root. Note that the password must be prefixed with -p, with no space after it.
• the databasename is exactly that.
• The ssh part uses the same syntax as the commands for the local database but with the username and ip address of the server.
• All of this is written in a single line in the Terminal.

Note

This command exports and syncs the entire database from the local to the remote server, dropping the tables on the remote server and rebuilding them with the records from the local copy: obviously, this needs to be approached carefully, and always with backups at hand.

For relatively small databases (up to 10MB - the size of most blogs) and a decent internet connection, the sync process shouldn’t take more than a few seconds to complete; a large database that needed only incremental updates (or a more cautious developer) would require a different approach.

The best part is that once run successfully, this command remains in the Terminal history; you only need open the Terminal window and press the up cursor key to run it again.

Photograph by Hernán Piñera, used under a Creative Commons Attribution-ShareAlike 2.0 Generic license.

Enjoy this piece? I invite you to follow me at twitter.com/dudleystorey to learn more.