WordPress Data Migration with MySQL

WP-CLI is a great tool for migrating data within WordPress from the command line, however it’s not always the appropriate or most efficient way to move data around. My rule of thumb is if I am working with unserialized data, I try to use MySQL queries first.

A simple migration script with WP-CLI could take up a lot of I/O and could take minutes or hours to complete depending on the data set. The same query in MySQL can take mere seconds. For example, if you wanted to take meta values from a specific post type, and move them to a new key, the WP-CLI script might look something like this:

$args = array(
	'posts_per_page' => -1,
	'nopaging' => true,
	'post_type' => 'post',
);
$posts = get_posts( $args );
foreach ( $posts as $post ) {
	$thumb_id = get_post_meta($post->ID,'_custom_thumb', true);
	if ( isset( $thumb_id ) && !empty( $thumb_id ) ) {
		update_post_meta( $post->ID, 'foo_meta_key', $thumb_id );
	}
}
WP_CLI::success( 'Foo Thumbs Set!' );

In order to accomplish your goal, you must set up loops and conditionals, commit your code, deploy your code, and then finally remove your code from the code base once the script has been run. You alternatively could roll back your database, freeze content entry, run the script locally, and then import your database back into production. Both scenarios are neither fun nor elegant.

The MySQL query is a better method in this case. Simply run one query on the database and you’re done. Using INSERT and SELECT together, you can easily add meta rows in one fell swoop. (Notice the ‘foo_meta_key’ string is set as a result column for all rows returned.)

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT p.ID, 'foo_meta_key', pm.meta_value from wp_posts as p
JOIN wp_postmeta as pm ON pm.post_id = p.ID
WHERE pm.meta_key = '_custom_thumb'
AND p.post_type IN ('post')
AND p.post_status IN ('publish', 'draft', 'private')
AND pm.meta_value > 0

On a database of ~15K posts, the WP-CLI script took several minutes to complete. The DML script took less than one second. If you’re not well-versed in MySQL, it’s worth the time invested to learn how to nest queries, join data sets, etc… You can save yourself quite a bit of time by first knowing your tools and secondly, knowing when to use each one.

Leave a Reply

Your email address will not be published.