I made a mistake and posted a blog entry to the wrong WordPress website. A comment was made before I could manually delete and post it to the other website. While I could just lose that comment, I decided it would be interesting to see if I could manually migrate the post content (with comments) directly from one WordPress MySQL database to the other.
After looking at the WordPress database schema, I found that I only needed to pull data from two tables and modify a few column values.
These are the essentials to keep in mind when migrating a post:
- The two “wp_posts” and “wp_comments” tables contain the data we need to migrate.
- For “wp_posts”, we need to modify columns “ID”, “post_author” (foreign key to “wp_users” table), and “guid”.
- For “wp_comments”, we need to modify columns “comment_ID”, “comment_post_ID”, and “comment_parent” (references “comment_ID” for replies).
- We can ignore tables “wp_postmeta” (contains edit lock info) and “wp_commentmeta” (contains Akismet approval data).
- No need to deal with categories and tags (kept in “wp_term_taxonomy”, “wp_termmeta”, and “wp_terms” tables). We can manually set category (from the default “Uncategorized”) and insert tags after the migration.
- Image links in the post will point back at the old WordPress site, so we will need to add the images to the new WordPress site and update the image URLs accordingly.
Before we start, make sure that both the WordPress websites are updated to the same version. (My websites are using the latest WordPress version 4.9.7.)
Read Post From Old WordPress Database
Identify the post identifier that you wish to move. If you click on your post header, the URL will change to include the identifier. For example, my URL is “https://www.chanhvuong.com/3048/how-to-sell-a-used-computer-like-a-car-salesman/” and the post identifier is 3048.
mysql -u root -p
# Input your MySQL root password if it is not blank
# Select the <database_name> to read from.
mysql> use <database_name>;
# By default, MySQL is configured to only support reading/writing files from a "mysql-files" directory.
# You could remove this requirement, but I decided to just make use it.
mysql> show variables like 'secure_file_priv';
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
# Output the post content matching <post_id> to a file.
mysql> select * from wp_posts where ID=<post_id> into outfile '/var/lib/mysql-files/post.txt';
# Output the post comments matching <post_id> to a file.
mysql> select * from wp_comments where comment_post_ID=<post_id> into outfile '/var/lib/mysql-files/comments.txt';
Insert Post Into New WordPress Database
While we could modify the column data in the output files, I decided to do manual overwrites when importing. (You will need to be the root user in order to access “/var/lib/mysql-files/” directory.)
mysql> use <other_database_name>;
# WordPress orders posts by publish date so insert using next auto-increment post id.
# You'll need this to modify the "guid" string below.
mysql> select max(ID)+1 from wp_posts;
# Find a user ID to use as the "post_author".
mysql> select ID,user_login,display_name from wp_users;
# Insert the post into the other database.
# Set ID as NULL so the next auto-increment value will be used.
# Select existing <my_user_id> as post_author.
# Update guid with other WordPress domain and the next auto-increment post id.
mysql> load data infile '/var/lib/mysql-files/post.txt' into table wp_posts set ID=NULL, post_author=<my_user_id>, guid="http://www.<other_domain>.com/?p=<auto_incremended_post_id>";
Inserting comments is problematic because reply comments require the identifier for the parent comment, which doesn’t exist yet.
One solution is to lookup the next auto-incremented comment ID and then edit the “comments.txt” outfile accordingly before importing. However, because my website is active, some comments could be inserted before I do the import, making all the identifiers in the outfile invalid.
The second solution, which I chose, is to import the comments and then manually fix the “comment_parent” identifiers (and the “user_id” which is nonzero for users in “wp_users”).
# Set comment_ID as NULL so the next auto-increment value will be used.
# Set the comment_post_ID to be our <auto_incremented_post_id> from above.
mysql> load data infile '/var/lib/mysql-files/comments.txt' into table wp_comments set comment_ID=NULL, comment_post_ID=<auto_incremented_post_id>;
# Lookup the newly-inserted comments.
mysql> select comment_ID,comment_author,comment_author_email,comment_parent,user_id from wp_comments where comment_post_ID=<auto_incremented_post_id>;
# Update comment_parent to the correct comment_ID (replace 472365 and 472366 below accordingly).
mysql> update wp_comments set comment_parent=472365 where comment_ID=472366;
# Update user_id for reply comments made by you (replace <my_user_id> and 472366 below accordingly).
mysql> update wp_comments set user_id=<my_user_id> where comment_ID=472366;
Fix Category, Tags, and Images
The post and comments should now be visible on your other WordPress website. You can set the post’s category and tags using the normal editing process. And add any images and update the image URLs in the post body accordingly.
Once you’re confident that the post is displayed correctly, you can delete its duplicate (and related images) from the old WordPress website. Also, you can delete the no longer necessary outfiles from the “/var/lib/mysql-files” directory.
And finally, for completeness, set a redirection from the old post URL to the new post URL.
Some info above gotten from: