Welcome to Web Hosting Forum - Net Hosting Talk

Register now and be part of our community! When you register with the Net Hosting Talk you can join in with topics, start new topics, and generally be a part of the first level of our community. It's also quick and totally free, so what are you waiting for?

MySQL commands that you will love if you use WordPress

nhtadmin 

Administrator
Moderator
Premium Member
Messages
38
Reaction score
18
Hello,

If you manage many WordPress sites, or manage sites with a lot of activity there are times when the use of plugins is too slow, and inefficient. On these occasions it is faster to use MySQL commands.SECURITY BEFORE EVERYTHINGAs in this article we will work directly on the database ALWAYS before making a backup of it. 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 some really useful MySQL commands for WordPress site administrators.

Replace URLs within content


Very useful command after a migration, for example, or 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 basic security measure, easier from MySQL:
Code:
UPDATE wp_users SET user_login = 'newadmin' WHERE user_login = 'Admin';
Clear spam without waiting times or runtime errors

How many times 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 not have time or desire to walk reviewing thousands of comments pending approval? If they are not important for your site, then 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 wonderful:
Code:
UPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '2018-01-01' AND post_status = 'publish';
You just have to change the date of the example for the one you want. You can no longer leave new comments in all entries with a publication date prior to 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, many times you realize that they do not contribute anything there, in the middle of the conversation of your tickets, and incidentally they generate a lot of outbound traffic, so it is time to erase them all.
Code:
DELETE FROM wp_comments WHERE comment_type = 'pingback';
Reset a user's password

This will have to do more than once, sometimes even out of necessity, if you forget it.
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 want 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 just 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 very useful if you want to return to previous versions of the same content, but 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 we simply had the habit of using a CMS in another way, sometimes we 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, eye, that this is total changes, convert ALL pages into entries and vice versa. Use it only if you are very sure.


Remove shortodes

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 easily 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 almost for everything, 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 that you usually use, share it in the reply :)


 
 

Hostwinds

Newbie
Messages
16
Reaction score
2
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

Top