September 7, 2025 by Latest In Tutorials
exporting and importing wordpress posts directly from the database advanced
6



When migrating a WordPress site or splitting it from a multisite network, sometimes you need more control than the built-in Tools → Export/Import option. In those cases, you can export and import posts directly at the database level using phpMyAdmin or MySQL.

This guide walks you step-by-step through the advanced method of handling WordPress posts, metadata, categories, and thumbnails.

Understand Where WordPress Stores Posts

WordPress doesn’t keep posts in just one table. When you export/import posts manually, you need to take care of multiple tables:

Table Purpose
wp_posts Stores all posts, pages, custom post types, and media attachments.
wp_postmeta Stores metadata for posts (custom fields, featured image _thumbnail_id, etc.).
wp_terms Stores categories and tags.
wp_term_taxonomy Defines taxonomy types (category, tag, custom taxonomy).
wp_term_relationships Connects posts to terms (e.g., which category a post belongs to).

⚠️ Note: Your table prefix may not be wp_. If your database uses wp_2_ or blog1_, adjust queries accordingly.

Export Posts from the Source Database

You can export directly from phpMyAdmin or the MySQL command line.

Export Posts

SELECT *
FROM wp_posts
WHERE post_type='post'
INTO OUTFILE '/tmp/posts.sql';

Export Post Metadata

SELECT *
FROM wp_postmeta
WHERE post_id IN (SELECT ID FROM wp_posts WHERE post_type='post')
INTO OUTFILE '/tmp/postmeta.sql';

Export Categories/Tags

SELECT *
FROM wp_term_relationships
WHERE object_id IN (SELECT ID FROM wp_posts WHERE post_type='post')
INTO OUTFILE '/tmp/term_relationships.sql';

SELECT * FROM wp_terms INTO OUTFILE '/tmp/terms.sql';
SELECT * FROM wp_term_taxonomy INTO OUTFILE '/tmp/term_taxonomy.sql';

💡 Pro Tip: If you also want images, don’t forget to include posts of type attachment:

SELECT *
FROM wp_posts
WHERE post_type='attachment'
INTO OUTFILE '/tmp/attachments.sql';

Import Posts into the Target Database

  1. Open phpMyAdmin on the new site.

  2. Go to the database → click Import.

  3. Upload each exported .sql file (posts.sql, postmeta.sql, terms.sql, etc.).

  4. If you’re using MySQL CLI, run:

mysql -u username -p databasename < posts.sql
mysql -u username -p databasename < postmeta.sql
mysql -u username -p databasename < terms.sql
mysql -u username -p databasename < term_taxonomy.sql
mysql -u username -p databasename < term_relationships.sql
mysql -u username -p databasename < attachments.sql

4. Fix IDs and Relationships

  • Posts reference thumbnails using _thumbnail_id in wp_postmeta.

  • Categories and tags reference posts using object_id in wp_term_relationships.

  • If the target site already has content, IDs may conflict. In that case, you may need to remap IDs using a script or plugin like WP Migrate DB.

5. Move Media Files (Images)

The database only stores references to images — the actual files live in /wp-content/uploads/.

  • Copy the entire uploads/ folder from the old site to the new one:

scp -r oldserver:/var/www/html/wp-content/uploads/ newserver:/var/www/html/wp-content/uploads/

  • Ensure correct file permissions (755 for folders, 644 for files).

6. Update URLs if the Domain Changed

If the new site has a different domain, update old URLs inside the database.

UPDATE wp_posts
SET guid = REPLACE(guid, 'https://oldsite.com', 'https://newsite.com')
WHERE post_type IN ('post','attachment');

UPDATE wp_posts
SET post_content = REPLACE(post_content, 'https://oldsite.com', 'https://newsite.com')
WHERE post_type='post';

This ensures images and internal links point to the correct domain.

7. Regenerate Thumbnails

Since WordPress stores thumbnails as physical image files, after moving media you should regenerate thumbnails.

  1. Install the plugin Regenerate Thumbnails.

  2. Run it to rebuild all image sizes.

8. Test the Import

  • Visit your new site → Posts → check if all content is present.

  • Open a few posts to confirm:

    • Categories and tags are showing correctly.

    • Featured images are attached.

    • Media loads from /uploads/.

✅ Final Thoughts

Exporting and importing WordPress posts directly from the database is powerful but requires care:

  • Always back up both source and target databases first.

  • Make sure you migrate posts + metadata + terms + attachments together.

  • Copy the uploads/ folder, otherwise images won’t load.

  • Run SQL replacements if your domain changes.

  • Regenerate thumbnails for best results.

This method is best for developers and advanced site migrations, especially when splitting a site out of a multisite or merging data between two WordPress installs.

Leave a Reply

Your email address will not be published. Required fields are marked *