Exporting single WordPress database tables

Sometimes you do not want the database. You want one table out of it. Copying orders to staging to reproduce a checkout bug with real data. Pulling wp_postmeta out to see why one product has four thousand meta rows. Handing a client a spreadsheet of their subscribers. Moving one plugin’s data to a new site and leaving the rest alone.

A full export is the wrong tool for all of those. A large dump you have to load somewhere just to read one table is slow, and importing it into a site you care about overwrites everything else when you only wanted one thing.

This covers which table holds what, the trap that makes a naive table copy break on a different domain, how to export one table in phpMyAdmin, mysqldump and WP-CLI, and when .csv is right.

Before you start

You need phpMyAdmin from your hosting panel, or SSH access. The credentials are in wp-config.php, along with the one detail people forget:

define( 'DB_NAME', 'example_wp' );
$table_prefix = 'wp_';   // could be wpab_, mysite_, anything

If the prefix is not wp_, every table name below changes to match. Check it before hunting for tables that do not exist.

Exporting is read-only and safe. Importing is not: everything here that puts a table back into a database can destroy the data already in that table, and I have flagged where. Take a database backup before any import, and if you are not sure what a real one is, I have written that up in backing up a WordPress database properly.

Step 1: Find the table that actually holds your data

A standard install has twelve tables, and almost everything you will be asked for is in one of these:

• wp_posts holds posts, pages, every custom post type, attachments, revisions and menu items, told apart by the post_type column. Not just blog posts, which is why it is nearly always the second biggest table.
• wp_postmeta holds custom fields for everything in wp_posts, joined by post_id: ACF fields, SEO titles, page builder layouts. Usually the largest table.
• wp_options holds site settings, theme options, plugin configuration and transients. Its autoload column decides whether a row loads on every page request, which is why a bloated wp_options is a speed problem too.
• wp_users and wp_usermeta hold accounts and hashed passwords, and everything else about a user. wp_comments and wp_commentmeta do the same for comments.
• wp_terms, wp_termmeta, wp_term_taxonomy and wp_term_relationships hold categories, tags and custom taxonomies. You need all four: the term, its meta, the taxonomy it belongs to and which posts it is attached to.

Plugins add their own tables with the same prefix. WooCommerce keeps line items in wp_woocommerce_order_items and wp_woocommerce_order_itemmeta. On installs using High-Performance Order Storage the orders themselves are in wp_wc_orders, wp_wc_orders_meta, wp_wc_order_addresses and wp_wc_order_operational_data. With HPOS off they are rows in wp_posts with a post_type of shop_order, detailed in wp_postmeta. Check which before you export orders, because it changes the table list entirely.

Action Scheduler adds wp_actionscheduler_actions and wp_actionscheduler_logs. Form, membership and booking plugins keep entries in their own tables. To see what you have:

wp db tables --all-tables-with-prefix
wp db size --tables --format=table   # names and sizes together

Or read phpMyAdmin’s Structure tab, which lists every table with its row count and size.

Step 2: Understand what breaks when you move a table

This is the part that turns a five minute job into a broken site.

Tables are related, so exporting one gets you half a thing. wp_postmeta rows point at wp_posts rows by ID. Take wp_posts alone to another site and every custom field is gone. Take wp_postmeta alone and it references IDs that mean something entirely different on the other side. Categories need all four term tables or the assignments vanish. Decide what the complete set is before you export.

IDs collide. Importing another site’s wp_posts over the top does not merge anything: the DROP TABLE statement deletes what is there and replaces it wholesale. Fine when refreshing staging from live, a disaster when moving ten products into a site that already has two hundred. For content moving between two live sites, use Tools, Export (the WXR file) or the importer belonging to the plugin that owns the data, because those create new rows and remap IDs instead of flattening the table.

Serialised data breaks on a different domain. WordPress stores arrays and objects (widget settings, theme options, page builder layouts, plugin configuration) as serialised PHP strings, and those strings record the byte length of every value inside them:

a:1:{s:3:"url";s:21:"https://oldsite.co.uk";}

The 21 is the length of the URL that follows it. Move that row to a different domain, fix the URL with a raw SQL REPLACE(), and the recorded length no longer matches. PHP then refuses to unserialise the whole option, silently. Widgets disappear, page builder layouts come back blank, theme options reset to defaults, and nothing in any log tells you why. So never fix URLs in an imported table with UPDATE ... REPLACE(). Use a tool that unserialises properly:

# Always dry run first, and target only the table you imported.
wp search-replace 'https://oldsite.co.uk' 'https://newsite.co.uk' wp_options --dry-run
wp search-replace 'https://oldsite.co.uk' 'https://newsite.co.uk' wp_options --precise

wp_options deserves its own warning. It holds siteurl and home, so importing another site’s copy points yours at the wrong domain and redirects you away from your own admin. It also holds active_plugins, so it can try to activate plugins that are not installed and hand you a fatal error. If you must move it, be ready to set WP_HOME and WP_SITEURL in wp-config.php to get back in.

There are four ways to get the file out, and which one suits you depends mostly on what access your host gave you and how big the table is. Pick a route below to see what it covers before you commit to one.

Route A: phpMyAdmin

Either click the table name and use its Export tab, or tick the table’s box in the Structure list and choose Export from the “With selected” dropdown, which is better for three related tables in one file.

Choose Custom, set Dump table to “structure and data”, and decide on the DROP TABLE statement deliberately: tick it when you are replacing the same table on the target (refreshing staging from live), leave it off when the target does not have the table yet or you plan to import under a different name. Set Compression to gzipped if the table is large, and leave the character set as utf-8.

To export some rows rather than all of them, do not export the table. Run a query in the SQL tab:

SELECT * FROM wp_posts
WHERE post_type = 'product' AND post_status = 'publish';

Then scroll to the bottom of the results and click Export. phpMyAdmin exports the result set, which is how you get “just this year’s orders” rather than everything since 2019.

For CSV, pick it as the format and tick “Put columns names in the first row”. Keep the defaults of comma for terminated and " for enclosed and escaped: that is what spreadsheets expect.

One thing phpMyAdmin will not do is rename the table. If the two sites use different prefixes, the dump still says wp_posts and the import replaces exactly that. Fix it in the file first, a one-line job in Route B.

Route B: mysqldump, when you have SSH

Name the database, then the tables. That is the whole trick:

# One table.
mysqldump --user=example_wpuser --password \
  --single-transaction --quick --default-character-set=utf8mb4 --no-tablespaces \
  example_wp wp_postmeta > wp_postmeta.sql

# A related set, in one file, in the order they should import.
mysqldump --user=example_wpuser --password \
  --single-transaction --quick --default-character-set=utf8mb4 --no-tablespaces \
  example_wp wp_posts wp_postmeta > content.sql

--single-transaction reads inside one transaction, so the export is consistent and nothing on the live site is locked. --quick streams rows rather than loading a whole table into memory. --default-character-set=utf8mb4 stops accented names and emoji arriving as question marks.

Three flags do most of the useful work beyond that:

# Rows matching a condition. NOTE: --where applies to every table you list,
# so run it against one table at a time unless the column exists in all of them.
mysqldump ... --where="post_type='product'" example_wp wp_posts > products.sql

# Data only, keeping the structure that already exists on the target.
mysqldump ... --no-create-info example_wp wp_options > options-data.sql

# Structure only, to recreate an empty table with the right columns and indexes.
mysqldump ... --no-data example_wp wp_posts > posts-schema.sql

--no-create-info is the safe choice when you are adding rows to a table that must keep its current definition, and it also means no DROP TABLE.

Renaming a table on the way in is a text edit, and the safest way to import something you want to inspect before it touches the real table:

# Import wp_posts from another site as wp_posts_import instead.
sed 's/`wp_posts`/`wp_posts_import`/g' posts.sql > posts-import.sql
mysql -u example_wpuser -p example_wp < posts-import.sql

Now you can query it, compare it and copy across only what you need, with the live table untouched. On any site with real content, reach for that version.

Route C: WP-CLI

WP-CLI reads the credentials from wp-config.php, so there is nothing to mistype:

wp db export subscribers.sql --tables=wp_users,wp_usermeta

# Any mysqldump flag passes straight through.
wp db export products.sql --tables=wp_posts --where="post_type='product'"

# Straight to a compressed file.
wp db export - --tables=wp_postmeta | gzip > postmeta.sql.gz

For data you want as a spreadsheet, query it instead. SELECT ... INTO OUTFILE is usually blocked by secure_file_priv and writes on the server rather than to you, so the practical route is a batch query:

# Tab-separated, UTF-8, straight to your machine's stdout.
mysql --batch --raw --default-character-set=utf8mb4 -u example_wpuser -p example_wp \
  -e "SELECT ID, user_email, display_name, user_registered FROM wp_users" > users.tsv

Open that with tab as the delimiter and UTF-8 as the encoding and nothing gets mangled on the way.

.sql or .csv: which one you want

.sql is for another database. It carries column types, indexes, the character set, NULL as distinct from an empty string, and the exact CREATE TABLE definition, so it reimports into MySQL or MariaDB and comes out identical. Nothing else can read it usefully.

.csv is for a human or another system. A spreadsheet, an import into a CRM or mailing tool, a client who asked for “the list”. It is plain text, and it loses everything the database knew: types, NULLs, indexes and any relationship to other tables.

It has traps worth knowing before you send one to a client. Excel drops leading zeros from postcodes and turns long order numbers into scientific notation. A UTF-8 file without a byte order mark shows £ as £, though it opens correctly through Data, From Text/CSV with UTF-8 picked. Values containing commas or line breaks need proper quoting, which phpMyAdmin’s defaults handle and a hand-rolled export often does not. And serialised meta is unreadable in a spreadsheet, so wp_postmeta as CSV is rarely worth the trouble.

The rule I use: if it is going back into a database, .sql; if a person is going to read it, .csv; if both, export both. And remember a subscriber list is personal data. Share it as a link with an expiry rather than an email attachment, delete it once collected, and never leave a copy in wp-content/uploads where anyone with the URL can download it.

The in-admin route: ABCode Database Exporter

Everything so far needs SSH or a phpMyAdmin you can stand using. When you have neither, and you only want one table out of a site, that is the gap ABCode Database Exporter fills, free on wordpress.org.

What it does:

• Lists every table with a search box, so you tick the one you want.
• Exports it as .sql or .csv, with optional gzip and a progress bar.
• Copies any table’s CREATE TABLE statement in one click, which beats writing a query to see what a plugin created.
• Writes phpMyAdmin-compatible .sql, so it imports back through phpMyAdmin, mysql or WP-CLI with no editing.

What it does not do: no row filter, so “only this year’s orders” is still a query in phpMyAdmin or mysqldump --where. It exports and does not import, and it will not rename tables for you. It does not solve Step 2 either, and nothing that exports a table can, because the related tables, the ID collisions and the serialised URLs are yours to think about whichever tool writes the file.

On the jobs where you already know which table you want, ticking it and downloading the file is the whole task, and it does not care whether your host gave you SSH.

How to check it worked

Check the file before you trust it. tail -3 wp_postmeta.sql should end cleanly rather than mid-statement, but the reliable check is counting rows on both sides: run SELECT COUNT(*) FROM wp_postmeta; before exporting, then the same query on the target after importing. The two numbers match or something went wrong.

For a .csv, wc -l users.csv should give your row count plus one for the header. Then open it and find a row with an accented character, a comma inside a value and a leading zero. If those three survive, the rest will.

After importing into a WordPress site, check at the WordPress level: load the admin screen that owns the data. Products should list with prices, orders with totals, users with roles. An empty screen with the right row count means a missing related table.

When it does not work

sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' table.sql

Common questions

Picking the right tool for the job

Single table exports are a job where the mechanics are easy and the judgement is the work. mysqldump db table, a tick box in phpMyAdmin, or wp db export --tables=, and the file exists in a minute.

What takes the thought is the part before: which tables belong together, whether IDs will collide on the other side, and whether anything in the table holds a serialised URL. Get that right and the copy is boring. Get it wrong and you lose an afternoon to a page builder layout that came back empty after an import that reported success.

If you are exporting tables by hand because a plugin stores its data somewhere awkward, or a client asks for the same export every month, that should be a button rather than a routine. Building those is most of what plugin development work is, and usually a smaller job than people expect. If that sounds like you, get in touch.