Web Hosting Forum - Net Hosting Talk

We are a community of individuals and businesses passionate about web hosting. Let's build, learn, and grow together.

Tutorial MySQL commands that you will love if you use WordPress

NhtAdmin 

NHT Manager
Administrator
Hello,

If you manage many WordPress sites or manage sites with a lot of activity, there are times when plugins are too slow and inefficient. On these occasions, it is faster to use MySQL commands.

SECURITY BEFORE EVERYTHING: As in this article, we will work directly on the database, ALWAYS backing it up. A silly click error can be the prelude to disaster.

WHERE IS THIS DONE?

9L8zuR9.png


You can execute SQL commands in two main ways:
  • From the command console, with mysql.
  • From the PHPMyAdmin tool, available in all hosting.
USEFUL MYSQL COMMANDS FOR WORDPRESS SITES

Let's do it. We will not see all possible but practical MySQL commands for WordPress site administrators.

Replace URLs within content.


This is a handy command after a migration, for example, if you have changed a reference to a website and want to change it quickly on your site.

Code:
UPDATE wp_posts SET post_content = REPLACE (post_content, 'https://oldurl.com', 'https://newurl.com');

Change user by default.

A primary security measure, more accessible from MySQL:

Code:
UPDATE wp_users SET user_login = 'newadmin' WHERE user_login = 'Admin';

Explicit spam without waiting times or runtime errors

How often have you seen runtime errors when deleting thousands of spam messages? The problem with this command is over.

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

Run it, and you will erase all spam at lightning speed.

Delete comments on old posts.

Do you not have time or desire to walk around reviewing thousands of comments pending approval? If they are unimportant to your site, you delete them all at a stroke.

Code:
DELETE FROM wp_comments WHERE comment_approved = 0

Delete comments on old posts.

If you have not set the expiration of the publication of new comments in WordPress settings but you want to close the possibility of commenting on old posts, this MySQL command is lovely:

Code:
UPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '2018-01-01' AND post_status = 'publish';

You have to change the example date for the one you want. You can no longer leave new comments in all entries with a publication date before the one you put, respecting and showing the previous ones.

Delete pingbacks

When you start a blog, you always like to see the pingbacks, those links in the form of comments that show how others have linked us.

But, over time, you often realize that they do not contribute anything there, in the middle of the conversation of your tickets. Incidentally, they generate much outbound traffic, so it is time to erase them all.

Code:
DELETE FROM wp_comments WHERE comment_type = 'pingback';

Reset a user's password.

If you forget, this must be done more than once, sometimes out of necessity.

Code:
UPDATE wp_users SET user_pass = MD5( 'newpassword' ) WHERE user_login = 'admin';

Change content author

Has one of the editors gone and want to reassign the content to another? Have you changed your user and wish to assign your content to your new user? Not easy.

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

In this case, you have to know the ID of the previous and outgoing authors. You can identify it in the database by email, for example, or by editing it in WordPress by observing the generated URL, which would be something like this:

Code:
https://example.com/wp-admin/user-edit.php?user_id=1234

Delete all revisions

The input revisions are useful if you want to return to previous versions of the same content. Still, when you have already published, except for hobbies, they are rarely useful and fill your database with residue. If you are clear, then you quickly delete them:

Code:
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'

Convert pages to entries or vice versa.

Perhaps you have not even thought about it, but those of us who have WordPress sites migrated from other platforms or had the habit of using a CMS in another way sometimes find entries that would make more sense as pages or the opposite.

You can easily change the type of content like this:

Convert entries to pages:

Code:
UPDATE wp_posts SET post_type = 'page' WHERE post_type = 'post'

Convert pages to entries:

Code:
UPDATE wp_posts SET post_type = 'post' WHERE post_type = 'page'

Of course, remember this is a total change; convert ALL pages into entries and vice versa. Use it only if you are very sure.

Remove shortcodes

Have you stopped using a plugin or layout that you used for years, and you have left lots of shortcodes in your content?

With MySQL commands, you can quickly delete them, for example:

Code:
UPDATE wp_post SET post_content = replace(post_content, '[the-shortcode-to-delete]', '' ) ;

Use this command as many times and with as many shortcodes as you need to remove.

Replace any text in the content.

We finish with a command for almost everything; it's great to replace any text, URL, shortcode, whatever, for whatever. You will know.

Code:
UPDATE wp_posts SET 'post_content'
= REPLACE ('post_content',
'Oldtext',
'Newtext');

THERE IS MORE?

Sure, hundreds, if not thousands, of useful MySQL commands for WordPress sites.

And if you know any of these you usually use, share it in the reply. :)
 
  • Advertisement
  • Thank you for sharing this! Learning these commands was very helpful, particularly the command to allow one to "Change content author." Thank you again, and have a wonderful day!
     

    Advertisement

    Back
    Top