I develop WordPress sites, themes, and plugins all the time and have been working on improving my workflow. I have configured our server using git (gitosis) for easy deployment of WordPress (and related files) which I’ll write about in a later post. This post is to share a little script I use for copying my local database to/from the remote (live) database.

My local development server is an exact replica of my live site. I am using MAMP which makes it really easy to set up new local sites with local name resolution. You can edit you /etc/hosts file and point your live domain to your local machine while working on your site. The point is that when I am ready to deploy my site I can simply drop all my files on the live server and everything works with no modifications because all the paths and so forth are the same starting from the document root of the site. I toggle between viewing the live site or my local site using my /etc/hosts file (or MAMP’s local name resolution checkbox).

General Workflow

Working with the database involves the following process:

1. Pull down a current copy of the remote database
2. Work locally on things which may involve database modifications
3. Replace my live (remote) database with a copy of my local database

So I have created a PHP CLI script to make this really easy. The syntax for using it is:

  dbsync.php [put|get] [config.ini]

If you want to copy your local database to your remote server, use the put parameter. If you want to copy your remote database to your localhost database then use the get parameter.

The config.ini file is an INI file that contains the credentials for connecting to your remote and local databases. The remote database is connected to via SSH. The format of the INI file looks like this:

[remote]
ssh      = user@server.com
username = remoteDbUsername
password = remoteDbPassword
database = remoteDbName

[local]
username = localDbUsername
password = localDbPassword
database = localDbName

I usually name my INI file dbs.ini and store it in the root of my local git repository as an ignored file so that it doesn’t get pushed to my remote repository. Then after completing checking in and pushing my work on my site I can also quickly push up the local copy of my database like this:

dbsync.php put dbs.ini

Download & Installation

1. Download the dbsync.php script
2. Put dbsync.php somewhere in your system path. I’ve got mine in my ~/bin directory which holds a variety of little scripts that I use for everyday development tasks.
3. Set dbsync.php to be executable chmod a+x dbsync.php
4. Create a config.ini file that contains the remote and local credentials for connecting to your databases/ssh
5. Run the script like this: dbsync.php put config.ini

Download the dbsync.php script

Notes

To make life easier, you can set up SSH keys to you don’t have to enter passwords all the time.

If you use your .ssh/config file you can make an entry for you SSH connection and give it a name, something like mysite, then you can just use the name from your .ssh/config file rather than the user@host.com syntax for the value of remote ssh.

This script does not merge data. It simply dumps a database and replaces another database with the dump. So, if you are accepting comments on your WordPress site or allow your visitors to do anything else that may modify the database, you should put your site in maintenance mode while you are working locally. This way you don’t overwrite anything that was changed in your database between when you get the remote database and when you put your local database.

To minimize “maintenance mode” time. Do a get then immediately do the work that’s going to involve database changes (like installing a new plugin or whatever) the put your local database.