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 professional 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.
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( id, wp_posts.id, 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( meta_value, wp_postmeta.meta_value, 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( term_id, wp_term_taxonomy.term_id, wp_term_taxonomy.term_id + 10000 ) WHERE term_id > 0" mysql --user=root --password= db2 -e "UPDATE wp_term_taxonomy SET parent = REPLACE( parent, wp_term_taxonomy.parent, 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( object_id, wp_term_relationships.object_id, 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( term_taxonomy_id, wp_term_relationships.term_taxonomy_id, 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.
# Import into dummy DB echo "Import into dummy DB" echo "====================================================" mysql -u root -p db1 < wp_posts.sql mysql -u root -p db1 < wp_postmeta.sql mysql -u root -p db1 < wp_terms.sql mysql -u root -p db1 < wp_term_taxonomy.sql mysql -u root -p db1 < wp_term_relationships.sql
That’s it! Everything should have merged correctly.