Keep your database in sync with MySQL and PostgreSQL

If you write web applications you may already be using a version control system to keep code in sync between your servers, but what about your MySQL or PostgreSQL database?

Sure, there is replication, one master database server with one or more slaves is a possible case, but that may be overkill for most simple projects. You just want to make sure the database is the same in your development, staging and production servers at a certain time, like after some major changes in code or before a new release.

Worry not my friend as you just need a few quick commands to keep your valuable data in sync. Yes, it's command line time again.

The dump, drop and create cycle

As most developers I create my web applications on a development server and enter content and settings to be stored in the database. Then, when I commit my code to a version control system on a staging server, Bazaar is my tool of choice these days, I have three options for moving my data:

1. Enter all the data again, which is too much work and prone to error.
2. Duplicate the entire database, the option we'll use, and
3. Duplicate just the tables you know have changed, a slight variation of the second approach.

I'll focus on duplication of the entire database for now as adapting for specific tables is just as easy.

For this method to work you need to use the same database name and user credentials in all your servers. Let's imagine we want to update the database on the staging server with the latest version from the development server. Once you have all your data ready in the development server these are the steps to follow:

• Create a dump file of the database in the development server.
• Move your dump file from the development to the staging server, I usually use scp but version control should be fine too.
• Drop the database in the staging server, optionally creating a backup dump.
• Recreate the database in the staging server and feed it with the dump from the development server.

You can write a script to automate the process if you want. Now, I'll take a look at what we need to run on the command line, let's start with PostgreSQL.

Sync your database with PostgreSQL

The administrative user for PostgreSQL is usually named postgres. If you haven't already done so let's use this user via sudo to create the owner of your database in all your servers, in my case the owner will be alexis:

sudo -u postgres createuser -P alexis

And now create a database owned by that user, I'll call mine cataybea:

sudo -u postgres createdb cataybea -O alexis

Once you have some data in the database of the development server get a dump file by running:

pg_dump -f cataybea.sql cataybea

The cataybea.sql file contains all the SQL needed to recreate your database. Now transfer it to the staging server, backup the database you already have there and drop it:

dropdb cataybea

Now recreate the database with the same name, the permissions are already set, and feed it with the dump you got from the development server:

sudo -u postgres createdb cataybea -O alexis
psql -U alexis -d cataybea -f cataybea.sql

Finally, confirm you've got the data correctly by running a few queries:

psql -d cataybea

Sync your database with MySQL

The administrative user for MySQL is usually named root. If you haven't already done so let's create the database in all your servers, in my case its named cataybea and the owner will be alexis:

mysqladmin -u root -p create cataybea
mysql -u root -p
mysql> GRANT ALL PRIVILEGES ON cataybea.* TO 'alexis'@'%' IDENTIFIED BY 'secret';

Once you have some data in the database of the development server can get a dump file by running:

mysqldump -u alexis -p cataybea > cataybea.sql

The cataybea.sql file contains all the SQL needed to recreate your database. Now transfer it to the staging server, backup the database you already have there and drop it:

mysqladmin -u root -p drop cataybea

Now recreate the database with the same name, the permissions are already set, and feed it with the dump you got the development server:

mysqladmin -u root -p create cataybea
mysql -u alexis -p cataybea < cataybea.sql

Finally, confirm you've got the data correctly by running a few queries:

mysql -u alexis -p cataybea

Help on its way

You can sync all your databases in PostgreSQL and MySQL just by following the steps above and replacing your database name and user credentials; however, if you need some context, you can get all the details for any of the commands mentioned using the --help option, for example:

psql --help
mysql --help
createdb --help
mysqladmin --help

And that's it. Happy database syncing!