Sync MySQL Databases

I run three different environments for robwilkerson.org. I have local development environment where I update Chyrp, the blogging software that runs the site, tweak parts of my theme and introduce new modules to the configuration. I also have a staging environment where I ensure that changes made in dev look and work okay in an environment that closely resembles my final environment, production.

Something I’ve long wanted to do is to keep the staging and production MySQL databases sync’d up so that I can create an even closer resemblance between the environments and get a better feel for the impact of the changes I make as they move up the stack. This morning I finally set about implementing this process. The process itself is pretty straightforward and looks like this:

  1. Export the production database to a SQL script
  2. In the exported script, replace any references to the production database name with the staging database name
  3. Execute the script against the staging database

The script to execute that process looks like this:

mysqldump -umyusername \
           -pmypassword \
           --opt \
           --no-create-db \
           --complete-insert \
           --databases production-db-name | \
sed s/production-db-name/staging-db-name/ | \
mysql -umyusername \
      -pmypassword \
      -D staging-db-name

Finally, I created a cron job to run this command every night at midnight. In my setup, the production and databases exist on the same host. If they didn’t, I’d need to add the -h flag to at least one of the mysql commands. Similarly, this command is executed on the same machine as those databases which eliminates the need for the -h flag on either command.

I wrote this primarily to supplement my own memory, but I offer it to you at no cost. I’m not a MySQL DBA, nor do I care to become one in the near future; if there’s an easier or better way, I’d love to hear about it.

Subscribe0 Comments on Sync MySQL Databases