Play with web technologies

How to merge two MySQL WordPress databases

March 30, 2015
Attention: This article is old and the content is outdated. I've left the article here for historical purposes.
How to merge two MySQL WordPress databases

Recently I've had to do this and I was unable to find something that could solve my problem online. I wanted to merge a WooCommerce Wordpress site into a very large Wordpress blog. It ended up being relatively simple to do this, however I'd just like to note that I'm not a MySQL so there may be much more efficient ways of doing this, however this was good enough for my needs. Make sure to backup your Wordpress database before messing around with it.

Problems were faced

The problem when merging Wordpress databases is that you end up having conflicts with the primary keys. Initially I thought this wasn't a problem since I could just merge the one into the other without specifying the primary key. Mysql would auto-increment this and all my problems would have been solved. I tried this and quickly realised that this would only work for me if I was merging a single table. By auto-incrementing the primary keys in the various tables I was merging, it broke the primary/foreign key relationship between them. What was the solution? Simple, increment all primary and foreign keys in one of the databases by X (read: insert number here). X can change depending on how large your database is. I was working on a particularly large one and did an increment of 10 000.

You have db1 and db2. You want db1 to have the extra posts/content types/categories/images included in db2. Do the primary/foreign key increments on db2. Mysqldump the relevant tables from db2 with --no-something-or-other flag and then import that script into db1.

Bash script

The bash script below will export posts, pages, products, images, categories and tags from db2 into db2.sql. Edit it to your needs. I've left comments in the bash script.

# Wordpress export
# Assuming the db details are:
# db_name = db2
# db_user = root
# db_pass = pass
# =============================================================================

echo "Change all IDs to avoid conflicts"
echo "===================================================="

# wp_posts
mysql --user=root --password= db2 -e "UPDATE wp_posts 
    SET id = REPLACE(
        wp_posts.id + 10000
    WHERE id > 0"

# wp_postmeta
# Increment post_id 
# Increment meta_value fields for post thumbnails to retain reference
mysql --user=root --password= db2 -e "UPDATE wp_postmeta 
    SET post_id = REPLACE(
        post_id, wp_postmeta.post_id, 
        wp_postmeta.post_id + 10000
    WHERE post_id > 0"
mysql --user=root --password= db2 -e "UPDATE wp_postmeta 
    SET meta_value = REPLACE(
        wp_postmeta.meta_value + 10000
    WHERE meta_key = '_thumbnail_id'"

# wp_terms
mysql --user=root --password= db2 -e "UPDATE wp_terms 
    SET term_id = REPLACE(
        term_id, wp_terms.term_id, 
        wp_terms.term_id + 10000
    WHERE term_id > 0"

# wp_term_taxonomy
mysql --user=root --password= db2 -e "UPDATE wp_term_taxonomy 
    SET term_taxonomy_id = REPLACE(
        term_taxonomy_id, wp_term_taxonomy.term_taxonomy_id, 
        wp_term_taxonomy.term_taxonomy_id + 10000
    WHERE term_taxonomy_id > 0"
mysql --user=root --password= db2 -e "UPDATE wp_term_taxonomy 
    SET term_id = REPLACE(
        wp_term_taxonomy.term_id + 10000
    WHERE term_id > 0"
mysql --user=root --password= db2 -e "UPDATE wp_term_taxonomy 
    SET parent = REPLACE(
        wp_term_taxonomy.parent + 10000
    WHERE parent > 0"

# wp_term_relationships
mysql --user=root --password= db2 -e "UPDATE wp_term_relationships 
    SET object_id = REPLACE(
        wp_term_relationships.object_id + 10000
    WHERE object_id > 0"
mysql --user=root --password= db2 -e "UPDATE wp_term_relationships 
    SET term_taxonomy_id = REPLACE(
        wp_term_relationships.term_taxonomy_id + 10000
    WHERE term_taxonomy_id > 0"

echo "Now for the dumping"
echo "===================================================="

mysqldump --no-create-info --user=root --password= db2 wp_posts > wp_posts.sql
mysqldump --no-create-info --user=root --password= db2 wp_postmeta > wp_postmeta.sql
mysqldump --no-create-info --user=root --password= db2 wp_terms > wp_terms.sql
mysqldump --no-create-info --user=root --password= db2 wp_term_taxonomy > wp_term_taxonomy.sql
mysqldump --no-create-info --user=root --password= db2 wp_term_relationships > wp_term_relationships.sql

echo "Edit exported files to allow for seamless inserting"
echo "===================================================="

# wp_postmeta id's don't need to be force incremented. Remove all ID inserts
sed -ir "s/[)],[(][0-9]*,/),(/g" wp_postmeta.sql
sed -i "s/INSERT INTO \`wp_postmeta\` VALUES ([0-9]*,/INSERT INTO \`wp_postmeta\` (\`post_id\`, \`meta_key\`, \`meta_value\`) VALUES (/g" wp_postmeta.sql

# Prevents duplicates on wp_terms and wp_users script import
sed -i "s/);/) ON DUPLICATE KEY UPDATE slug = slug;/g" wp_terms.sql

Note: Users have been removed from the above export, therefore db1 users will be untouched.

Once the script has done all of the hard work, it's time to import db2.sql into db1.
<pre><code class="language-bash">
# Import into dummy DB
echo "Import into dummy DB"
echo "===================================================="
mysql -u root -p db1 &lt; wp_posts.sql
mysql -u root -p db1 &lt; wp_postmeta.sql
mysql -u root -p db1 &lt; wp_terms.sql
mysql -u root -p db1 &lt; wp_term_taxonomy.sql
mysql -u root -p db1 &lt; wp_term_relationships.sql

That's it! Everything should have merged correctly.