Some MySQL Tricks That Could Help You With Your Blog (2026 Update)

A backup plugin telling me “backup complete” used to be enough. It isn’t anymore.

The moment that changed was watching a shared MySQL instance get killed by the kernel during a memory spike, the incident I wrote about in the VPS resource isolation piece. The database came back fine that time, restarted in under ten minutes. But it made me actually think through what happens on the day it doesn’t come back clean, and what else I’d been doing directly against a live database without the same caution.

These are the direct-database tricks I actually use now: backing up properly, changing a site’s URL without silently corrupting it, cleaning up the bloat that piles up over years, and editing content in bulk without breaking things that matter.

The Backup Command Everything Else Depends On

Every trick below assumes you’ve done this first. For a single WordPress database:

mysqldump --single-transaction --quick -u [user] -p[password] --routines --triggers --events [database] > backup.sql

Each flag earns its place:

One thing worth knowing before you rely on --single-transaction alone: it only guarantees consistency for InnoDB tables. If a database mixes InnoDB and older MyISAM tables, and older WordPress installs sometimes do, that flag isn’t enough on its own. That mix needs --lock-all-tables or a proper maintenance window instead, since MyISAM has no equivalent snapshot mechanism and falls back to a full table lock regardless.

For anything leaving the server, client data, a database with user emails or order history, compress and encrypt it before it goes anywhere:

mysqldump --single-transaction --quick -u [user] -p[password] [database] | gzip -9 > backup.sql.gz
gpg --symmetric --cipher-algo AES256 backup.sql.gz

Automating It With Cron

Manual backups are the ones that don’t happen on the day they’re actually needed:

0 2 * * * /usr/bin/mysqldump --single-transaction --quick -u [user] -p[password] [database] | gzip -9 > /backups/db_$(date +\%F).sql.gz

Two practical notes that aren’t obvious the first time you set this up. Don’t hardcode the password directly in a cron file other processes or users on the box might be able to read, a .my.cnf with restricted permissions or an environment variable is the safer place for it. And add a rotation step, whether that’s a simple find /backups -mtime +30 -delete or something more deliberate, since an unmonitored backup directory grows forever and eventually fills the disk it’s sitting on.

Restoring is the easy part, technically:

gunzip < backup.sql.gz | mysql -u [user] -p[password] [database_to_restore]

The part that actually matters is doing this on a schedule before you need it for real. A backup that’s never been restored isn’t verified, it’s an assumption. Restore the latest dump to a staging database monthly, confirm the site loads correctly against it, and only then trust the automated nightly backups are doing what they’re supposed to. This is the same cadence I laid out in the resource isolation breakdown for hosting multiple client sites on a VPS, and it applies whether you’re running one site or fifteen.

Changing Your Site URL Without Breaking Everything

This is the trap almost everyone falls into at least once. Migrating a domain, moving from staging to production, or switching to HTTPS all mean the same URL needs to change everywhere in the database. The instinct is a simple SQL replace:

-- This looks safe. It is not.
UPDATE wp_options SET option_value = REPLACE(option_value, 'http://old-domain.com', 'https://new-domain.com');

Run that and widgets disappear, theme options break, and plugin settings quietly corrupt. The reason is serialized data. WordPress stores arrays and objects in wp_options, wp_postmeta, and wp_usermeta as PHP serialized strings, and those strings encode their own length as part of the format, something like s:21:"http://old-domain.com", where 21 is the exact character count. Swap in a URL of a different length and the string is still 21 characters as far as the stored data claims, but the actual content no longer is. WordPress can’t unserialize it, and that setting, widget, or field just breaks, often silently.

The fix is wp-cli‘s search-replace command, which unserializes each value before replacing, does the string swap, and reserializes it with the correct new length:

wp search-replace 'http://old-domain.com' 'https://new-domain.com' --dry-run
wp search-replace 'http://old-domain.com' 'https://new-domain.com'

Always run the --dry-run pass first. It shows you exactly what would change without touching anything, which catches surprises (a URL appearing somewhere you didn’t expect) before they’re live. For very large databases, add --memory-limit=512M or scope the operation to specific tables to avoid running out of memory mid-operation:

wp search-replace 'http://old-domain.com' 'https://new-domain.com' wp_posts wp_postmeta

One clarification worth making explicit: post_content itself, the actual body text of your posts, is stored as plain text, not serialized, so a direct SQL REPLACE against wp_posts.post_content alone doesn’t carry this specific risk. The danger is concentrated in wp_options, wp_postmeta, and wp_usermeta, wherever WordPress or a plugin is storing structured data as a serialized string. That distinction is exactly why the nofollow trick further down is safe to run as raw SQL, while a URL change across the whole database is not.

Finding What’s Bloating Your Database

Databases that have been live for years accumulate weight nobody notices until something feels slow. Two queries worth running periodically.

Find which tables are actually taking up space:

SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" FROM information_schema.TABLES WHERE table_schema = "your_database_name" ORDER BY (data_length + index_length) DESC LIMIT 10;

More often than not, wp_postmeta or wp_options tops that list, and post revisions are usually a big part of why. Back up first, then check how many you’re actually carrying:

SELECT COUNT(*) FROM wp_posts WHERE post_type = 'revision';

If that number is high, clean them out:

DELETE FROM wp_posts WHERE post_type = 'revision';

Worth doing after that cleanup: OPTIMIZE TABLE wp_posts; to actually reclaim the freed disk space, since MySQL doesn’t always release it automatically after a large delete. And going forward, adding define('WP_POST_REVISIONS', 10); to wp-config.php caps how many revisions WordPress keeps per post, so the bloat doesn’t just come back.

Bulk-Nofollowing Links Without Breaking Anything

There are legitimate, non-manipulative reasons to bulk-nofollow links: marking user-generated content you haven’t vetted, staying compliant with affiliate disclosure expectations, or cleaning up old links to sites that have since gone spammy or changed hands.

Back up first, using the command from above. Then check scope before changing anything, a SELECT, not an UPDATE, so you know exactly how many posts you’re about to touch:

SELECT ID, post_title FROM wp_posts WHERE post_content LIKE '%

Then run the update:

UPDATE wp_posts SET post_content = REPLACE(post_content, '

As noted above, this one’s safe from the serialization trap since post_content is plain text. What it isn’t safe from is being a blunt instrument: it nofollows every link in every published post, including your own internal links to other pages on your site, which is almost never what you want. Internal links are exactly the link equity you want flowing freely, and a raw find-replace like this can’t tell an internal link from a link to a spammy affiliate page from ten years ago.

For most sites, a plugin-level tool that applies rules by domain or link type, “nofollow all links to domain X,” “nofollow all comment links”, is the safer everyday approach. The raw SQL version earns its place for a one-time cleanup where the SELECT step above has already told you exactly what’s being touched, not as a standing habit.

Questions People Ask About MySQL Tricks for WordPress

Does --single-transaction make my backup completely safe on its own?
Only for InnoDB tables. If your database mixes InnoDB and MyISAM tables, --single-transaction alone isn’t sufficient, MyISAM has no equivalent mechanism and mysqldump falls back to a table lock for those tables regardless. Check your table engines before assuming this flag covers everything.

Why did my widgets and theme settings disappear after I changed my site’s URL?
Almost certainly a raw SQL REPLACE against wp_options. WordPress stores structured settings as PHP serialized strings that encode their own character length, and a plain string swap breaks that length once the replacement text is a different size. wp-cli search-replace handles this correctly by unserializing, replacing, and reserializing with the right length.

Is it safe to run a raw SQL REPLACE on post content?
Yes, post_content is stored as plain text, not serialized data, so a direct REPLACE there doesn’t carry the same risk as touching wp_options or wp_postmeta. The nofollow trick above relies on exactly that distinction.

How often should I actually test a backup restore, not just take one?
Monthly, at minimum, for anything you’d genuinely be upset to lose. An untested backup is an assumption, not a safety net, and the only way to know a restore actually works is to have done it before you need it.

Is it safe to bulk-edit links directly in the WordPress database?
Only with a backup taken first and a SELECT query run before the UPDATE so you know the exact scope of what’s changing. Even then, it’s a blunt instrument that can’t distinguish your own internal links from external ones, so it’s best used for a one-time cleanup rather than routine maintenance.

What’s actually bloating most WordPress databases?
Post revisions are one of the most common culprits, especially on sites that have been live for years without a revision limit set. Checking table sizes via information_schema.TABLES and capping revisions with WP_POST_REVISIONS in wp-config.php both help keep it from building back up.

Conclusion

A backup plugin’s success message isn’t proof of anything by itself, and neither is a database trick that’s never been tested against what it could break. Every command here works the same way: know what you’re changing before you change it, back up first, and understand which parts of WordPress’s storage are safe to touch directly and which aren’t. That discipline is what separates a genuinely useful MySQL trick from the kind that quietly corrupts a site three steps down the line.