Synchronizing Local Databases and Web Content via Dropbox (MySQL, Apache, WAMP)

WAMP ServerWhen working on web-based projects locally, developers are often faced with the burden of transferring the data across multiple development machines for one reason or another. In my case, I develop primarily on a desktop machine in my office but occasionally require access to the project files from a laptop or mobile device; this is especially true when working remotely or if needing to demonstrate a WIP to a client.

A typical web project consists of many static assets, dynamic scripts powered by a server-side language like PHP, and database-driven content powered by an engine like MySQL. Due to these interconnected components, constantly exporting and importing the databases from one machine to the next while also carrying over all of the updated page scripts and static assets immediately becomes a tedious and time-intensive commitment. Database synchronization can be especially cumbersome when needing to mirror user accounts, table and database privileges and other lower level details.

By utilizing a cloud storage service like Dropbox, the entire data synchronization process can be automated across devices to ease developer woes. This approach carries several other added benefits as well, including:

  • Previous versions of files can be restored as needed in most cloud services. With Dropbox, for instance, the history of each file is preserved for 30 days on its standard plan, or a full year on its extended plan. This acts as a safeguard against accidental deletion or catastrophic programmer error.
  • The project folder(s) can instantly be shared with other internal and external team members to streamline the development workflow. Of course, when dealing with complex team projects you will want to use a more apt solution that manages advanced source control and revisioning.
  • By fully isolating the content from the applications, there no longer exists any concerns over OS formats and forgotten backups. By default, the SQL and website data gets nestled inside the Apache/MySQL installation folders commonly placed on the root OS drive, making it a ripe candidate for forgetfulness upon format (speaking from several bouts of personal experience).

NOTE: The tutorial that follows uses WAMP (Apache/MySQL) and Dropbox on a Windows machine, but the same general concepts will apply regardless of cloud service provider, web software or operating system.

Installing the Required Software

To get started, you'll want to install the server software and cloud storage software on each device you plan to use during development. Again, this tutorial uses WampServer and Dropbox.

  1. Download and install WampServer. At the time of this writing, the latest available version is WAMP 2.5, which includes Apache 2.4.9, MySQL 5.6.17 and PHP 5.5.12. You are free to install these packages manually or use an alternative package like XAAMP; I have grown to appreciate the quick-access options of WAMP for server configuration and service management.  The installation process is straight-forward, with the only customizable options being the installation directory, desktop icon and quick launch icon.  The default path of c:\wamp\ is a smart choice.  If you do change it, I encourage you to refrain from using any directories with spaces, which can sometimes muddy up path referencing and complicate navigation. You will also want to avoid installing it to any protected directories (i.e., 'Program Files') which can cause read/write issues.

    Note that there is a 32-bit and 64-bit version of WAMP.  You do not have to install the 64-bit version to match your operating system.  In almost every common scenario the 32-bit version is perfectly sufficient and in some instances provides better plugin and extension support as not every 32-bit component has been built for 64-bit architecture.  There's also another small blunder in the 64-bit version of MySQL distributed with WAMP as I will note later on.
WampServer Installation

WampServer Installation Screen

  1. Download and install Dropbox. At the time of this writing, the latest available version is Dropbox 3.4.4.  The install process is quick and effortless.  Once installed and running, you may wish to change your default Dropbox folder location.  To do this, click on the Drobox icon in taskbar, click the Gear icon in the top-right and select Preferences.  On the Account tab, click the "Move..." button to specify a new location and then select OK.

    My preference of choice is to keep the Dropbox location on a separate hard drive to avoid having to backup or re-download all of the Dropbox storage after a format. This way after installing a new operating system I simply update the storage location to my external path created previously and it is all set.
Dropbox Custom Storage Location

If desired, specify a custom Dropbox storage location within the Preferences.

Getting Synchronized

With the software up and running on all necessary devices, we now get to the meat of the procedure by altering various server paths to point to the cloud storage location.

Before starting, make sure all services are STOPPED in WAMP and exit WAMP Manager, if you had previously opened it.

  1. Create a new parent folder in your Dropbox storage location to house the web data (e.g., 'wamp'). Within this folder, create two subfolders to house the database store (e.g., 'db') and web assets (e.g., 'www').   To keep things tidy, I keep the 'wamp' folder and its subfolders in an 'appdata' folder on Dropbox, which is where I also house data for other developer tools and applications including Sublime Text Editor.
Dropbox Wamp Data Folders

An example folder structure for storing database and web asset files on Dropbox.

  1. Navigate to the Apache Configuration folder and open the file httpd.conf  in Notepad++ or your favorite plain text editor (try to avoid using the default Windows Notepad; it may not render the linebreaks properly and instead appear a garbled mess). The default location of this file when installed via WAMP 2.5 is: C:\wamp\bin\apache\apache2.4.9\conf\httpd.conf .
  2. Locate the DocumentRoot declaration around line 230.  Change the path to the 'www' folder you created within your Dropbox storage in step #1.  Always use forward slashes here and include the trailing slash at the end.  Save the file and close the document. This will now point all http web requests to the new Dropbox-based 'www' location. My line appears as:

  1. After this line and past the first <Directory /> clause, you will see another <Directory...> entry around line 252 with the original 'www' path within it.  Change this line to match the same as entered for the DocumentRoot above:

  1. Now, navigate to the MySQL root folder and open the file my.ini in a plain text editor.  The default location of this file when installed via WAMP 2.5 is: C:\wamp\bin\mysql\mysql5.6.17\my.ini.
  2. Locate the datadir declaration around line 38.  Change the path to the 'db' folder you created within your Dropbox storage in step #1.  Always use forward slashes here and omit the trailing slash. Save the file and close the document.  This will now point all SQL requests to the new Dropbox-based 'db' location.

WARNING: If you installed the 64-bit version of MySQL using WAMP, you MUST change line 26 of my.ini from [wampmysqld] to [wampmysqld64]. This is an apparent oversight in their 64-bit release and without changing that value to match the 64-bit daemon, the personalized settings will never be applied and it will continue reading database information from the MySQL install directory.

  1. If using WAMP, open from the Scripts directory of the WAMP install with a plain text editor and change the $wwwDir variable declaration around line 61 to the 'www' folder you created within your Dropbox storage in step #1 (you must also remove the '$c_installDir.' portion so it resembles below). Save the file and exit.  This will update the quick-access shortcut from the WAMP icon to open your web directory contents.

  1. Now, copy all of the contents of the wamp/bin/mysql/mysql5.6.17/data directory to the 'db' directory created in the Dropbox storage folder, and copy all of the contents of the wamp\www directory to the 'www' directory created in the Dropbox storage folder.
  2. Open the copied 'index.php' file from the 'www' Dropbox folder in a plain text editor.  Change the $server_dir variable around line 40 to reflect your root WAMP install. Save the file and exit.  This will enable you to access the default WAMP index page with quick access to useful tools like phpMyAdmin and general server information.

  1. Finally, launch WAMP and start (or restart) all services.  You should now be able to access your local server via http://localhost/ and perform all database operations through phpMyAdmin or custom scripts.  All of the data will now be saved to the Dropbox location and accessible from the other machines, automatically syncing between them.

You will have to repeat all of the steps above for each device you wish to synchronize with.  But once configured, you will be able to edit databases and site files on any given machine and all changes will be applied on the other machines with no other user intervention required.