Prem Tiwari - - Mysql, WordPress, WordPress Snippets
This is a very quick tutorial about some useful WordPress MySQL queries. In this tutorial, I will share some useful MySQL queries which will help you when you are going to move your WordPress site from your dev, staging server to production server.
When you upload any WordPress site from staging server to production server, then you need to replace the staging URLs from your application with production URLs.
Below are the lists of some MySQL queries which replace your old URLs to new URLs in your website’s database tables. Copy the below MySQL queries and execute it in SQL tab of your PHPMyAdmin or you can use any MySQL client software like MySQL workbench.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | # Change Siteurl & Homeurl UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldurl.com', 'http://www.newurl.com') WHERE option_name = 'home' OR option_name = 'siteurl'; # Change GUID UPDATE wp_posts SET guid = REPLACE (guid, 'http://www.oldurl.com', 'http://www.newurl.com'); # Change URL in Content UPDATE wp_posts SET post_content = REPLACE (post_content, 'http://www.oldurl.com', 'http://www.newurl.com'); # Change Image Path Only UPDATE wp_posts SET post_content = REPLACE (post_content, 'src="https://www.oldurl.com', 'src="https://yourcdn.newurl.com'); UPDATE wp_posts SET guid = REPLACE (guid, 'http://www.oldurl.com', 'http://yourcdn.newurl.com') WHERE post_type = 'attachment'; # Update Post Meta UPDATE wp_postmeta SET meta_value = REPLACE (meta_value, 'http://www.oldurl.com','http://www.newurl.com'); # Change Default "Admin" Username UPDATE wp_users SET user_login = 'Your New Username' WHERE user_login = 'Admin'; # Reset Password UPDATE wp_users SET user_pass = MD5( 'new_password' ) WHERE user_login = 'your-username'; # Assign all articles by Author B to Author A UPDATE wp_posts SET post_author = 'new-author-id' WHERE post_author = 'old-author-id'; # Delete Revision 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' # Delete Post Meta DELETE FROM wp_postmeta WHERE meta_key = 'your-meta-key'; # Export all Comment Emails with no Duplicate SELECT DISTINCT comment_author_email FROM wp_comments; # Delete all Pingbacks DELETE FROM wp_comments WHERE comment_type = 'pingback'; # Delete all Spam Comments DELETE FROM wp_comments WHERE comment_approved = 'spam'; # * 0 = Comment Awaiting Moderation # * 1 = Approved Comment # * spam = Comment marked as Spam # Identify Unused 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; |
Prem Tiwari is the founder of FreeWebMentor.com and also a professional developer who has vast experience in PHP and open source technologies. Apart from this, he is a blogger by hobby and also he has been a regular speaker of WordPress sessions in various IT Companies. View all posts by Prem Tiwari
Tags: mysql query example, update mysql query, wordpress mysql tutorial, wordpress tutorial, wordpress tutorial for beginner, wordpress tutorial for beginners, wordpress tutorials, wordpress tutorials for beginners
