Sync Local And Remote Databases For Web Development

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:

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:

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:

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

Database Synchronization Script by Lee Blue

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.

6 thoughts on “Sync Local And Remote Databases For Web Development

  1. Tri Nguyen says:

    Hi, this is really useful as it’s something I’m looking for in my work flow. Thanks so much.

    Just a quick question, how does your script handle any search and replace in the database to change, say for eg ‘http://localhost/’ to ‘http://livedomain.com/’?

  2. Lee says:

    It does not handle that at all, the assumption is that you have modified your /etc/hosts file so that you local development domain is the same as the live remote domain.

    Recently I have been using the FTP deployment feature in a BeanStalk repository plus a new database syncing script to overcome this problem of needing to update links in the database. If it turns out that the script works well I’ll post it to this site. I am still refining it right now. I’m trying to make the script easily reusable by abstracting out the database settings and so forth.

  3. Samantha says:

    I look forward to what you come up with, Lee. Thank you!

  4. cniry says:

    For small web is your solution acceptable. But transfering of full sql-dump is not effective for bigger projects 🙁

  5. Jelson says:

    just asking..how will i know which database is updated, what if the updated database will change to an old files when using the dbsync.

  6. Tamera says:

    Hello,
    I would like to know if I could use this solution for Joomla ? The zip is no longer available.
    Can you help me ?
    Thanks and congratulations for the job !
    Tamera from France

Comments are closed.