How to push wordpress from testing to production

I use a testing server for my site, and I use wordpress. I create and update content on testing, then deploy it to production. But the wordpress import procedure won’t update an existing post so…

1. dump content as SQL from the testing database
2. import content via SQL to the production database

Dump content as SQL from the testing database

I use the following bash script to generate four files. It just dumps the contents of the mysql database in the form of a runnable sql script.

• wp_config.sql - contains the users, links, and configuration of wordpress
• wp_taxonomy.sql – contains the tags and categories
• wp_posts.sql – contains the pages and posts
• wp_comments.sql – contains the comments

I’ve broken it down to these four to provide control over each of these parts as often I only want to deploy the posts on testing without interfering with the ongoing comments in production.

I’ve also learn not to update the configuration without need since it has a couple of records (siteurl and home) which wordpress uses for hyperlinks. Neglecting these records will give links to your readers which point to a testing server they cannot reach.

I use this script to export SQL scripts to copy the database onto production. Change the database, user, and password to your own and confirm the path to mysqldump for your system.

#!/bin/bash
DATABASE="johnmee_wordpress"
OPTIONS="
--user=johnmee
--password=password
--no-create-info
--skip-extended-insert
--skip-dump-date"
DUMPDIR="test"
MYSQLDUMP="/usr/bin/mysqldump"

# config tables
FILE=$DUMPDIR/wp_config.sql

$MYSQLDUMP $OPTIONS	\
    --result-file=$FILE $DATABASE \
    wp_users wp_usermeta wp_links wp_options

SQLTRUNCATE="
TRUNCATE wp_users;
TRUNCATE wp_usermeta;
TRUNCATE wp_links;
TRUNCATE wp_options;
"
exec 3<> $FILE
awk -v prepend="$SQLTRUNCATE" 'BEGIN {print prepend}{print}' $FILE >&3
exec 3>&-

# taxonomy tables
FILE=$DUMPDIR/wp_taxonomy.sql

$MYSQLDUMP $OPTIONS	\
    --result-file=$FILE $DATABASE \
    wp_terms wp_term_taxonomy wp_term_relationships

SQLTRUNCATE="
TRUNCATE wp_terms;
TRUNCATE wp_term_taxonomy;
TRUNCATE wp_term_relationships;
"
exec 3<> $FILE
awk -v prepend="$SQLTRUNCATE" 'BEGIN {print prepend}{print}' $FILE >&3
exec 3>&-

# posts tables
FILE=$DUMPDIR/wp_posts.sql

$MYSQLDUMP $OPTIONS	\
    --result-file=$FILE $DATABASE \
    wp_postmeta wp_posts

SQLTRUNCATE="
TRUNCATE wp_postmeta;
TRUNCATE wp_posts;
"
exec 3<> $FILE
awk -v prepend="$SQLTRUNCATE" 'BEGIN {print prepend}{print}' $FILE >&3
exec 3>&-

# comments table
FILE=$DUMPDIR/wp_comments.sql

$MYSQLDUMP $OPTIONS	\
    --result-file=$FILE $DATABASE \
    wp_comments

SQLTRUNCATE="
TRUNCATE wp_comments;
"
exec 3<> $FILE
awk -v prepend="$SQLTRUNCATE" 'BEGIN {print prepend}{print}' $FILE >&3
exec 3>&-

Import content via SQL to the production database

Importing my new and updated posts is simply a matter of running the wp_posts.sql file on the production server. If I’ve changed the categories or tags I’ll also run wp_taxonomy.sql.

The only access I have to my production database is through phpMyAdmin. So to do this I manually paste the generated code into the SQL query window.

When I get really keen perhaps I could fully automate synchronisation by having the script ftp the SQL to the server and establishing a cron job to run it in the wee hours. New content ready for breakfast every morning