Keeping Local and Production Database in Sync

As a developer, one of the challenges we often face is keeping our local development environment in sync with the production server. Particularly when it comes to databases, working with dummy or seeded data just doesn't cut it. Nothing beats working with real data, and that’s why I prefer to keep my local and production databases in sync. By ensuring that my local and remote environments share the same data, I can catch bugs, refine features, and develop more effectively—just as the users will experience it.

In this post, I'll share my approach to synchronizing local and production databases, using a custom Bash script to automate the process. The script I've written focuses on updating specific tables in my database, rather than the entire database, allowing me to choose what I need to sync. This is particularly useful if, like me, you use a framework like Laravel that has core tables like sessions or users, which don’t need to be included in every sync.

Why Keep Databases in Sync?

Keeping a local development database in sync with production can seem like a lot of effort, but there are some definite pros and cons:

Pros

• Backups for Peace of Mind: This approach serves as a form of backup, as data is always duplicated in both your local and remote environments. Even if a mistake is made during development, you’ve got another copy safely stored.
• Real Data During Development: Using real, production-like data during local development allows for more realistic testing and feature development. You’ll encounter the same edge cases and data structure peculiarities your users might face.
• Improved Debugging: A local environment that mirrors production helps you identify and resolve issues before they escalate. Bugs tend to surface in a predictable manner when environments are aligned.
• Faster Testing of Small Changes: Making small changes or fixes locally, and being able to quickly sync those changes, ensures that development and testing are more efficient.

Cons

• Manual Effort: The synchronization process is not 100% automatic or real-time. It still requires a command to be run to initiate the sync. This means that you have to remember to keep things updated regularly.
• Risk of Human Error: Since this sync is a manual process, there is always the risk of mistakes—such as syncing outdated tables or overwriting recent data changes unintentionally.
• Potential Data Sensitivity: Transferring real production data to your local environment might expose sensitive information. It’s important to handle this with care to ensure security and privacy compliance.
• Network Dependency: Syncing the database often depends on the speed and reliability of your network connection, which might not always be optimal. A bad connection could slow down the process or even result in failed synchronizations.

My Synchronization Approach

Typically, to keep my development environment and production environment distinct, I don't simply point my local development instance to the production database. Instead, I prefer to separate concerns. Code is shared using Git, media files are shared through the same cloud provider, so my main need is keeping databases synchronized.

I’ve written a Bash script that makes this easier. This script allows me to export tables from my local database, transfer them to my remote server, and restore the tables there. Similarly, I have another script to reverse the direction—so that the remote changes can be brought to my local environment.

The script is customizable, which means that I can choose to sync only the tables that I want. For example, in Laravel projects, I generally exclude core tables such as caching, sessions, and users. Here’s a streamlined version of my script:

#!/bin/bash

# Variables
LOCAL_DB_USER="localuser"              # Local MySQL username
LOCAL_DB_PASSWORD="password"           # Local MySQL password
LOCAL_DB_NAME="cesarcouto"             # Local database name
LOCAL_DB_DUMP_PATH="/users/myuser/desktop/database.sql"  # Path to store the SQL dump on your Mac

REMOTE_USER="remoteuser"               # Remote SSH user
REMOTE_HOST="remoteIP"                 # Remote server address
REMOTE_DB_NAME="remotedb"              # Remote MySQL database
REMOTE_DB_USER="remoteuser"            # Remote MySQL username
REMOTE_DB_PASSWORD="password"          # Remote MySQL password

# List of tables to export
TABLES="table01 table02 table03"

# Dump the selected tables from the local database
echo "Dumping selected tables from the local database..."
mysqldump -u"$LOCAL_DB_USER" -p"$LOCAL_DB_PASSWORD" "$LOCAL_DB_NAME" $TABLES > "$LOCAL_DB_DUMP_PATH"

# Check if the dump was successful
if [ $? -eq 0 ]; then
    echo "Database dump completed successfully!"
else
    echo "Error during the database dump."
    exit 1
fi

# Upload the dump file to the remote server
echo "Uploading database dump file to the server..."
rsync -avz -e ssh "$LOCAL_DB_DUMP_PATH" "$REMOTE_USER@$REMOTE_HOST:/tmp/$REMOTE_DB_NAME.sql"

# Restore the database on the remote server
echo "Restoring database on the remote server..."
ssh "$REMOTE_USER@$REMOTE_HOST" "mysql -u$REMOTE_DB_USER -p$REMOTE_DB_PASSWORD $REMOTE_DB_NAME < /tmp/$REMOTE_DB_NAME.sql"

# Remove the dump file from the remote server
echo "Removing dump file from the remote server..."
ssh "$REMOTE_USER@$REMOTE_HOST" "rm /tmp/$REMOTE_DB_NAME.sql"

echo "Database import completed!"

How the Script Works

• Step 1: The script uses mysqldump to create a dump of selected tables from the local database.
• Step 2: If the dump is successful, the file is uploaded to the remote server using rsync.
• Step 3: After uploading, the script runs a mysql command on the remote server to import the dump file into the remote database.
• Step 4: Finally, the script cleans up by removing the dump file from the remote server.

This script has made keeping my local and remote databases in sync a seamless process. Whether I’m making changes locally or on the production server, it’s easy to synchronize with a single command, ensuring that both environments stay up-to-date. I've also set up a reverse process so if I'm outside and updating my website, I can do the same when I come home and keep my local database updated as well.

Conclusion

Keeping local and production environments synchronized with real data is one of the best ways to ensure your application behaves as expected in the real world. It allows you to debug effectively and develop with confidence, knowing that what you're working on is an accurate reflection of your live environment.

While this process does require a bit of manual effort, it’s more than worth it for the value it provides during development. And with a Bash script like this, much of the manual effort is reduced to a single command, allowing you to focus on what really matters—building great software.

Feel free to adapt and use my approach in your projects, and let me know if you have other ideas for keeping local and production environments in sync!