If you have been managing a WordPress website for a while then you probably know that this content management platform uses a MySQL database to store every bit of information it needs to display your pages and apply your chosen settings.

WordPress and MySQL make a great partnership; both are free, and using WordPress means a constant source of new plugins and features to make life easier. But you can manage your site just as well, and in some cases even better, from phpMyAdmin if only you know how to work from the MySQL side. That, of course, is just a matter of running queries against the database WordPress relies on.

It’s not essential to know MySQL to build a WordPress site, but knowing how to run some useful queries that will save you headaches is definitely an advantage.

How to Manage your WordPress Database

If you’re familiar with phpMyAdmin and SQL (structured query language), skip ahead, but if this is new:

You can find phpMyAdmin on your hosting cPanel if you don’t know where to access it. When the console opens, select your site database from the list on the left. It should reflect the domain or username given to you when your WordPress site was set up, followed by suffix “_wp”, if the name hasn’t been changed. It is advised you don’t mess with the information_schema database at this point.

If you see multiple databases and can’t identify the proper database, select them one at a time, and each time open a new query window by clicking the SQL tab. Type in:

SELECT * from wp_options

Check the entries under the column named option_value. You should recognize your siteurl, blogname, and other information right there on the top few rows. If you the correct info is there, you have the right database. Before you do anything else, feel free to explore the tables and column names to see what you’ve got here. You have to select a database (and thus its tables) to run queries against it. If you want to see what’s in each table, use the SELECT * query above, swapping out wp_options for whatever table name you want to explore. Just don’t change any values yet.

First Step: Backup your Database

WordPress needs the information in the database to re-create every last post, comment, and setting you’ve created. No matter how competent you are with SQL, remember that one typo or mistaken click could destroy information that corrupts your database to the point where your site won’t load at all. Any kind of UPDATE or DELETE query could mean loss of critical site or content information. Backing up the database before you start tinkering with your data means you can always put it back just the way it was.

You can download plugins like WP-DB-Backup or WP-DBManager to assist you with this from your admin dashboard, but you can also do it straight from phpMyAdmin before you get started. The quickest and easiest way:

1. Login to phpMyAdmin.
2. Select your WordPress database.
3. Click on Export at the top of the window
4. In the FORMAT drop-down list, select SQL to export as a .sql file. It should be the default choice.
5. Click Go, and a copy of your database will be downloaded.

If you wanted to, you could click the Custom button for other options.

Alternately, you could go to the Operations tab, enter a file path in the Copy Database To box, select options, and click Go.

Restore a backup of your MySQL database

1. To restore the database, click the Import tab.
2. Leave the FORMAT as SQL or change it to whatever format was used
3. Browse to where you saved your backup of the database.
4. Click Go.

Note that phpMyAdmin does have some file size limits, so if your database has grown very large you’ll have to either trim it down using some of the queries below, or try another method such as one of the plugins. Upload size is usually 2MB, and these are the settings to look for:

post_max_size = 8M
upload_max_filesize = 2M

Note that post_max_size must be the same or larger than upload_max_filesize.
These settings may be set globally across the server depending on your installation, and you can’t change them. If not, you may find them in your php .ini file.

On Apache installations you may be able to change these settings locally by putting a “.htaccess” file in phpMyAdmin’s directory. The contents of the file should be:

php_value post_max_size 20M
php_value upload_max_filesize 20M

Change ’20M’ to however large you want file limits to be. If you get Internal Server Errors (500) then this likely means your host does not allow you to change these settings and you need to remove this file.

If you’re restricted by file sizes, you’ll just have to make multiple file backups by selecting the Custom checkbox and choosing individual tables for each backup file rather than the entire database, and restoring them one at a time.

SQL Hacks for WordPress Users

Now that that’s out of the way, here are some sample queries you can run from that same query box that will help in managing WordPress and MySQL databases:

1. Change your URL

WordPress stores the absolute path for your site URL and home URL. If you transfer your site to another server or domain, it will not load if the URLs are not updated. You can do this by running:

UPDATE wp_options SET option_value = replace(option_value, 'http://www.myoldurl.com', 'http://www.mynewurl.com') WHERE option_name = 'home' OR option_name = 'siteurl';

2. Update GUID (Globally Unique Identifier)

If you have uploaded your blog site from your computer to a new server or new domain, you also should update the URLs for the GUID field, since this is used to translate the post slug to the absolute path.

UPDATE wp_posts SET guid = REPLACE (guid, 'http://www.myoldurl.com', 'http://www.mynewurl.com');

3. Update URL in Content

Within the content of each post’s data, MySQL stores the old URLs referencing the old source. You need to change these to the new domain.

UPDATE wp_posts SET post_content = REPLACE (post_content, 'http://www.myoldurl.com', 'http://www.mynewurl.com');

4. Update Image Path

You might have heard about using Amazon CloudFront as a Content Delivery Network (CDN) to offload image delivery. After you’ve created a CNAME record, use the query below to update image paths in WordPress to load from Amazon CloudFront.

UPDATE wp_posts SET post_content = REPLACE (post_content, 'src="http://www.myoldurl.com', 'src="http://yourcdn.mynewurl.com');

You also need to update the GUID for image attachment:

UPDATE wp_posts SET guid = REPLACE (guid, 'http://www.myoldurl.com', 'http://yourcdn.mynewurl.com') WHERE post_type = 'attachment';

5. Add a New Field to Posts

This query adds a custom field to every post on your site. You can name the new field anything you like to easily identify it later by changing ‘MyNewCustomFieldValue’ to what you want (it must not already exist).

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
 SELECT ID AS post_id, 'UniversalCustomField'
 AS meta_key 'MyNewCustomFieldValue AS meta_value
 FROM wp_posts WHERE ID NOT IN
 (SELECT post_id FROM wp_postmeta WHERE meta_key = 'UniversalCustomField')
 '' AND post_type = 'post';

The following will do the same for all the pages on your site:

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
 SELECT ID AS post_id, 'UniversalCustomField'
 AS meta_key 'MyCustomFieldValue AS meta_value
 FROM wp_posts WHERE ID NOT IN
 (SELECT post_id FROM wp_postmeta WHERE meta_key = 'UniversalCustomField')
 AND 'post_type' = 'page';

6. Find all Posts with Field Name

If you need to find all the posts with the new field you’ve created (or any of the existing fields) you can use the following query, being sure to replace ‘FIELD_NAME’ with the name of the field you are searching for. Of course, you can also do the same for pages by changing ‘post_type’ = ‘post’ to ‘post_type’=’page’.

SELECT wp_posts.ID, wp_postmeta.meta_key
 FROM wp_posts
 JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
 AND wp_postmeta.meta_key = 'FIELD_NAME'
 WHERE wp_posts.post_type = 'post'
 order by wp_posts.ID asc

7. Delete Post Meta

Every time you install a plugin WordPress makes changes to the wp_postmeta table. After you remove the plugin, the data will still be there as useless clutter. You can clear it up with this query; just remember to change ‘UselessMetaKey’ to the appropriate value.

DELETE FROM wp_postmeta WHERE meta_key = 'UselessMetaKey';

8. Change Default Username

WordPress creates the default user account under the name “Admin”. This is so obvious that it can be a problem if a hacker or someone else intercepts your account password. They just login and take or delete what they want. For your own peace of mind, you can change this default username.

UPDATE wp_users SET user_login = 'Awesome Boss' WHERE user_login = 'Admin';

9. Identify Unused Tags

If you delete your old posts, the old tags for each will remain in the database. This query allows you to identify all of the old tags.

SELECT * From wp_terms wt
 INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id WHERE wtt.taxonomy='post_tag' AND wtt.count=0;

10. Deleting Spam

If you run a blog, you’ve gotten sick of all those spam comments piling up. This one SQL command will clear out all the comments you’ve marked as spam:

DELETE FROM wp_comments WHERE wp_comments.comment_approved = 'spam';

11. Reset Password

Here’s a handy one if you’ve ever forgotten your WordPress password or just want to change it (or someone else’s):

UPDATE wp_users SET user_pass = MD5( 'new_password' ) WHERE user_login = 'myusername';

12. Re-assign Articles

If you want to take ownership of some old legacy articles that came with the site, or that you now own the rights to, you can easily do it, just so long as you know the ID of old and new authors (which you can get from the Author & User page in the admin panel). Just click the author’s name and look for the user_id field.

UPDATE wp_posts SET post_author = 'new-author-id' WHERE post_author = 'previous-author-id';

13. Delete Revisions

If you edit a post multiple times in WordPress, it will save revision copies, which can add up to quite a bit over time and is a huge waste of resources slowing down your database. Clear them out like so:

DELETE a,b,c FROM wp_posts a
 LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
 LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
 WHERE a.post_type = 'revision'

This will delete all revisions; if you only want to delete specific ones you’ll have to retrieve the ID number and add it to the WHERE clause.

14. Delete All Unapproved Comments

Swamped with new comments you don’t have time to look at? Try this:

DELETE FROM wp_comments WHERE comment_approved = 0

15. Disable Comments on Old Posts

For this query, you can specify the comment_status as ‘open’, ‘closed’, or ‘registered_only’, as well as including date criteria.

UPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '2016-03-11' AND post_status = 'publish';

16. Export Comment Emails as Unique Records

Accumulating hundreds of comments can also lead to dozens of comments from the same email address. If you want to export all those emails for mailing campaigns or other needs, you can create a list of distinct emails with this query:

SELECT DISTINCT comment_author_email FROM wp_comments;

When it has returned the resulting list, under Query results operations, select the Export option.

17. Delete Pingback

Your most popular posts could generate tons of pingbacks accumulating in the database. To get rid of them, use this query:

DELETE FROM wp_comments WHERE comment_type = 'pingback';

18. Delete Comments from a Specific URL

If you’ve been getting a lot of spam from the same URL, you can remove all the garbage in one pass with the following query. The ‘%’ means you can delete all entries containing that portion of the URL string, which is handy to avoid typing URLs, but you may want to be careful not to be too general and delete URLs you didn’t mean t