How to Extract Specific WordPress Post Meta from a Raw SQL Dump

TL;DR

If you need to extract specific wp_postmeta rows from a large mysqldump file on a managed host where you cannot create a temporary database:

1. Do not use grep — mysqldump's extended INSERT format packs thousands of rows per line, so grep gives you entire lines full of unrelated data
2. Use a Python script to split the VALUES section on ),( separators, filter by your target meta_key, and write clean individual INSERT statements
3. Import the clean file directly with mysql using credentials from wp-config.php
4. Verify the row count matches the source before deleting temp files

Full script and step-by-step walkthrough below.

---

The Problem

Last week I was migrating a WordPress site to a new managed host. The file transfer went fine. The database import went fine. But then the client noticed all their post galleries were gone.

Turns out, during the initial cleanup of leftover theme meta, a key called vw_post_format_gallery_images got wiped along with the junk. This meta key stored the media attachment IDs for every gallery on the site. Without it, hundreds of posts lost their image galleries.

The fix sounded simple: pull that one meta key from the original database backup and re-import it. The backup was a 659MB .sql file sitting on the server. Easy enough, right?

Not quite.

Step 1: Why grep Fails on mysqldump Files

My first instinct was to grep the backup for the meta key name:

grep -i "vw_post_format_gallery_images" /home/myuser/dbbackup.sql > gallery_extract.sql

This ran without errors and produced a 23MB file. Looked promising until I opened it.

The problem is that mysqldump uses extended INSERT format by default. Instead of one INSERT per row, it packs thousands of rows into a single line:

INSERT INTO `wp_postmeta` VALUES (31875,14420,'_vw_sidebar_position','field_53cd...'),(31876,14420,'vw_left_sidebar',''),(31877,14420,'vw_post_format_gallery_images','20651'),(31878,14420,'vw_right_sidebar','')...

One giant line. Thousands of value tuples separated by ),(. When you grep for your target, you get the entire line back, which includes every other meta key for that batch of posts. Importing that file would re-insert all the junk meta you already cleaned up.

Step 2: Why the Obvious Fix Was Not Available

The textbook approach would be:

1. Create a temporary database
2. Import the full backup into it
3. Use mysqldump --where to extract only the rows you need
4. Drop the temp database

mysql -u myuser -p -e "CREATE DATABASE temp_import;"
mysql -u myuser -p temp_import < dbbackup.sql
mysqldump --no-create-info --complete-insert -u myuser -p temp_import wp_postmeta \
  --where="meta_key='vw_post_format_gallery_images'" > clean_extract.sql
mysql -u myuser -p -e "DROP DATABASE temp_import;"

But on managed WordPress hosting, you rarely get CREATE DATABASE privileges. The hosting panel manages databases for you. So this approach was off the table.

Step 3: Parse the Dump File with Python

Since I could not load the data into MySQL to query it, I wrote a short Python script to parse the raw dump file directly. The logic is straightforward:

1. Read each line of the dump
2. Find lines that are INSERT INTO wp_postmeta statements
3. Split the VALUES section on ),( to isolate individual tuples
4. Keep only tuples that contain the target meta key
5. Write each match as a standalone INSERT statement

target = 'vw_post_format_gallery_images'
out = open('/home/myuser/gallery_meta_clean.sql', 'w')
count = 0

with open('/home/myuser/dbbackup.sql', 'r', encoding='latin1') as f:
    for line in f:
        if 'INSERT INTO' in line and 'wp_postmeta' in line and target in line:
            idx = line.index('VALUES') + 7
            data = line[idx:].rstrip(';\n').strip()
            if data.startswith('('):
                data = data[1:]
            if data.endswith(')'):
                data = data[:-1]
            for t in data.split('),('):
                if target in t:
                    parts = t.split(',', 1)
                    rest = parts[1]
                    out.write(
                        "INSERT INTO `wp_postmeta` "
                        "(post_id, meta_key, meta_value) "
                        "VALUES ({});\n".format(rest)
                    )
                    count += 1

out.close()
print("Extracted {} rows".format(count))

Save it on the server and run it:

python3 /home/myuser/extract_gallery.py

Output:

Extracted 4391 rows

That matched the row count from the source database exactly.

A Note on Encoding

Notice encoding='latin1' in the open() call. Many older database backups contain characters that are not valid UTF-8. If you get a UnicodeDecodeError, switching to latin1 (or using errors='ignore') will get you through it. The meta values in this case were just numeric attachment IDs, so encoding did not affect the actual data.

Step 4: Verify the Output

Before importing anything, always check that the output looks correct:

head -3 /home/myuser/gallery_meta_clean.sql

INSERT INTO `wp_postmeta` (post_id, meta_key, meta_value) VALUES (1242,'vw_post_format_gallery_images','20651');
INSERT INTO `wp_postmeta` (post_id, meta_key, meta_value) VALUES (1242,'vw_post_format_gallery_images','20649');
INSERT INTO `wp_postmeta` (post_id, meta_key, meta_value) VALUES (1242,'vw_post_format_gallery_images','20650');

Clean, individual INSERT statements. Each row maps a post_id to a single media attachment ID. Exactly what we need.

Step 5: Import into the Live Database

With the clean SQL file ready, import it using the credentials from wp-config.php:

cd ~/public_html

mysql -u $(grep DB_USER wp-config.php | cut -d"'" -f4) \
  -p$(grep DB_PASSWORD wp-config.php | cut -d"'" -f4) \
  $(grep DB_NAME wp-config.php | cut -d"'" -f4) \
  < /home/myuser/gallery_meta_clean.sql

No errors. Then verify the row count on the destination:

wp db query "SELECT COUNT(*) FROM wp_postmeta WHERE meta_key = 'vw_post_format_gallery_images';"

COUNT(*)
4391

All 4,391 gallery meta rows restored. The post galleries were back.

If you are not comfortable pulling credentials inline like that, you can always grab them first and use them manually:

grep -E "DB_NAME|DB_USER|DB_PASSWORD" wp-config.php

For more on diagnosing WordPress database issues in general, check out my guide on fixing the "Error Establishing a Database Connection" error.

Step 6: Cleanup

Remove the temporary files when you are done:

rm /home/myuser/extract_gallery.py
rm /home/myuser/gallery_meta_clean.sql
rm /home/myuser/gallery_meta_extract.sql

When to Use This Approach

This technique works well when:

• You need a small subset of rows from a large dump file
• You cannot create a temporary database on the server
• The dump uses extended INSERT format (which is the default for mysqldump)
• You need to preserve specific post/media ID relationships during a migration

It does not replace a proper migration tool for full-site transfers. But for surgical, targeted data recovery from a backup file, it gets the job done in minutes.

---

Frequently Asked Questions

Why does grep not work for extracting rows from a mysqldump file?

Because mysqldump uses extended INSERT format by default. It packs hundreds or thousands of rows into a single SQL line, separated by ),(. When you grep for a keyword, you get the entire line back, which includes every other row in that batch. You end up with far more data than you need, and importing it would insert rows you did not intend to restore.

Can I use this Python script for any WordPress table, not just wp_postmeta?

Yes. The script logic works on any table that uses extended INSERT format. You just need to change the table name in the if condition and adjust the column mapping in the output INSERT statement. The ),( splitting approach works the same way regardless of which table the dump contains.

What if my SQL dump file is larger than available RAM?

The script reads the dump file line by line, not all at once. It processes one line at a time and writes matches immediately to the output file. This means it can handle dump files of any size without running out of memory. I have used it on dumps over 2GB without issues.

What encoding should I use when opening the dump file?

Start with the default UTF-8. If you get a UnicodeDecodeError, switch to latin1. Most older WordPress databases were created with latin1 character sets, and the dump files inherit that encoding. Using latin1 will read every byte without errors. If your data contains non-Latin characters (Arabic, Chinese, etc.), try utf-8 with errors='replace' so corrupted bytes get replaced instead of crashing the script.

Is there a risk of duplicate rows when importing the extracted SQL?

Yes, if the meta key already exists in the destination database. Before importing, check if any rows already exist:

wp db query "SELECT COUNT(*) FROM wp_postmeta WHERE meta_key = 'your_meta_key';"

If the count is not zero, either delete the existing rows first with wp db query "DELETE FROM wp_postmeta WHERE meta_key = 'your_meta_key';" or skip the import. Importing on top of existing data will create duplicates, which can break plugins that expect a single value per post.