WordPress provides a great feature for media managers. It allows managing (add, edit, delete) the media (images and documents). It not only stores the actual file in its directory but also stores the file name in the database. In this article, we will see where the media library lives in the database. The media Library lives in the database in wp_posts and wp_postmeta tables.
How does WordPress store media?
WordPress stores the media in 2 locations; the directory and the database.
It creates year-wise folders in the wp-content > uploads folder to store the files.
While the file information is stored in wp_posts and wp_postmeta tables.
- wp_postmeta contains the image/file URL; while
- wp_posts contains an entry for each image/file insertion into a post, along with the post ID.
Although, there are many plugins that offer to export and import media from one site to another. However, the scenario we have discussed in this article will help when something went wrong with media while migrating a WordPress website.
Export and Import Media
The simplest solution is to simply export the tables from the old site and import them to the new site. However, exporting and importing the 2 tables (wp_posts and wp_postmeta) as SQL did not work. While experimenting, we have received an error ‘duplicate entry for key 7’…
Exporting and importing these 2 tables as CSV did work, using the “CSV using load data” option.
Before importing, I truncated the 2 tables in the database of the target website (that is, remove all records).
Get the attachments
WordPress use post_type as attachment for storing file information in the wp_posts table. The following SQL will return all the entries to the media library:
SELECT * FROM wp_posts WHERE post_type = 'attachment';
After the execution, you can export the result table as SQL or CSV, or any other portable data format you like. Remember if you are not sure whether the entries exist in the database, use the Insert IGNORE statement instead of INSERT. (Possible through exporting tab in PhpMyAdmin or other MySQL clients). Also, there are entries that refer to the media library for each post, such as attachment images or thumbnail images, which are stored in the wp_postmeta table. WordPress store them so the media can get attached to the posts and pages. If you want them to be exported too, you will need to use the following SQL query:
SELECT * FROM `wp_postmeta` WHERE meta_key IN ('_wp_attached_file', '_wp_attachment_backup_sizes', '_wp_attachment_metadata', '_thumbnail_id');Now, export them and then you can import them wherever you want.
