If you are reading this, you probably know that WordPress doesn’t handle very well exporting media attachments unless you choose to export “All the content” with the WordPress Export tool.
Lot of users don’t need to import all the content and just choose to import Posts using the xml importer / exporter tool in WordPress and there’s where the problems starts.
A year ago I built a plugin that let users export featured images and then import them using WordPress importer tool because I needed for a client, and today I faced a very similar problem with another client but this time with all images attached to posts.
Basically my client developed a new theme on a new server, and imported just blog posts. He worked on the posts , make the theme live, moved old site to a folder on the server and used it normally.
After some time he realized that over 10k+ images that were in the media library were not there any more. So how can we import just media attachments into media library and WordPress?
Please be aware that this migth not work in your particular scenario, so be sure to backup everything before your start, Im not taking any responsability!
So here is what I did:
I used phpmyadmin, but you could use any tool that you feel confortable with.
First of all I did the following SQL query and then exported the results into a SQL file:
SELECT * FROM wp_posts WHERE post_type = 'attachment' AND post_parent != '0';
Be sure to change the table prefix to match yours. As you can see Im only retrieving attachments that are actually attached to something.
Then I did :
SELECT * FROM wp_postmeta WHERE post_id IN ( SELECT ID FROM wp_posts WHERE post_type = 'attachment' AND post_parent != '0' );
Once all the postmeta is exported is time to modify the sql files. I use Sublime text editor for coding and it handles very well large text files ( mine was around 40mb )
In the wp_posts.sql file I did the following:
- First I change all the INSERT INTO sentences to INSERT IGNORE INTO in case duplicate keys exists It don’t break them
- I modified all image urls to match new domain (not always needed, depending where you are importing from)
- I removed all unnecessary SQL code such as ALTER TABLE and INSERT TABLE and just left the INSERT INTO SENTENCES
- Zipped the file and imported into new site db with phpmyadmin
In the wp_postmeta.sql file I did the following:
- I removed all the unnecessary SQL code such as ALTER TABLE and INSERT TABLE and just left the INSERT INTO SENTENCES
- I changed all the INSERT INTO `wp_postmeta` ( `meta_id`, `post_id`, `meta_key`, `meta_value`) VALUES to INSERT INTO `wp_postmeta` ( `post_id`, `meta_key`, `meta_value`) VALUES . Basically Im removing meta_id because I want to insert all the postmeta at the end of the table and use the auto increment
- I did a regex search and replace to remove all ID from values. I used \( ([0-9]+), and replaced with just (
- Zipped file and imported to new site
The last and most import step is to copy the image files into the wp-content/uploads/. I did this directly on the linux console with a simple cp -n. Other might use rsync which is quite good also. I did something like:
cp -n OLD/wp-content/uploads/2013/08/* wp-content/uploads/2013/08/
This command just copy over files that don’t exist and ignore (don’t update) the ones with same name.
And voila! Everything was working again. Old the images where attached to the original posts, etc.
Again be careful and backup everything before trying this!
If you need help with your site , check out wordpress support plans!
Argie says
Hi thanks for the tutorial, I wonder how do you export selected rows in PHPMyAdmin? the Export and select all rows didn’t work as expected because it still redirected to EXPORT tab above without filtering the query results, too bad, can you tell me how did you export selected or queried?
Thanks,
Argie
wpadmin says
I was using phpMyAdmin 4.2.10 . Under the query results I have a box that let you print, create a graphic or export the query result. I used that export link.
Katherine says
Thanks so much for posting this. I was struggling and this worked perfectly.
John says
Wonderful!
Worked for me!
Cheers
John says
I owe you a beer or two! This just saved me a tonne of time – thanks so much for sharing.
For anyone using sequel pro instead of phpmyadmin, you’ll probably find that you can’t export the query results as sql. If that’s the case then just highlight all rows of the query result, right click and ‘copy as SQL INSERT’. You can then paste into your text editor and follow the instructions as normal.
I did find that the code I pasted into sublime text didn’t contain references to INSERT INTO SENTENCES for the post meta – just regular inserts but it worked just fine. Also, my regex was slightly different to account for the space and comma after the post meta id so basically mine looked like this: (\([0-9]+,\s)
Hope that helps someone using sequel pro. Thanks again for posting!
Mike says
Thanks so much for an informative and detailed post.
For those who prefer VIM for editing text files, here is the regex I used to remove all of the meta_id values from the wp_postmeta insert queries.
:%s/^([0-9]*, /(/g