Tagged: phpMyAdmin Toggle Comment Threads | Keyboard Shortcuts

  • Jay Versluis 3:58 pm on December 5, 2014 Permalink | Reply
    Tags: phpMyAdmin   

    Categories: MySQL, WordPress ( 19 )

    How to find and replace in MySQL with phpMyAdmin 

    mysqlSometimes you need to replace a string in your database with another string, and it can be rather tedious to plough through a large table manually. Thankfully MySQL can execute raw queries such as find and replace.

    This comes in handy if you’ve moved a WordPress installation to another URL: you only need to tweak two values in the options table, but there may be countless image references and links in the posts and options table too. That’s where find and replace can come in handy.

    You can execute the following statement either on the MySQL command line, or use phpMyAdmin’s Raw SQL option:

    Screen Shot 2014-12-05 at 15.42.10

    That big text field is where we’ll use the following code. Before we do however, make a backup of your database because there is NO UNDO FUNCTION in MySQL. A cute typo can break things beyond repair!

    Here’s what the find and replace statement looks like in principle:

    update table_name set field_name = replace(
    field_name, 'original text',
    'replacement text');
    

    For WordPress specifically, if you’d like to replace text strings inside posts and pages, then wp_posts would be your table, and field_name is the column of that table. So for wp_posts this will be post_content. You can see the field labels at the top of each column when you select a table.

    To replace a URL in all posts and pages the statement would look like this:

    update wp_posts set post_content = replace(
    post_content, 'http://oldurl.com/',
    'http://newdomain.com/subfolder/');
    

    As soon as you hit GO, MySQL will go to work and show you a success or failure message. The above would replace all image references and links from your old domain to the new one, where WordPress is installed in a subfolder.

    Make a note of your table prefix and replace it accordingly. wp_ is the default, but this can easily be changed into something else for security reasons. Be cautious of trailing slashes when you’re replacing URLs.

    Also note that a small letter “l” and a capital “I” look surprisingly similar in the phpMyAdmin! If you keep getting errors like “this table does not exist”, it’s something to watch out for before questioning your sanity again 😉

     

    Replacing URL strings in WordPress

    I use this technique when I need to replace URLs across an entire WordPress installation. Those can hide not only in posts, but also in widgets and menus. Here’s a list of places to hunt for them:

    • wp_posts table, in the posts_content field (links inside posts and pages)
    • wp_links table, in the link_url field (the old Link Manager)
    • wp_postmeta table, in the meta_value field (URLs of Custom Menu items)
    • wp_options table, in the option_value field (anything saved by themes and plugins)
    • wp_comments table, in the comment_content field (URLs inside comments)

    And while we’re talking about replacing URLs: if you need to change the root URL of a WordPress installation, this is done in wp_options too. Look for two values called siteurl and home.

     

    Further Reading





     
    • Vincent 11:46 am on August 30, 2015 Permalink | Reply

      This works perfectly. You just saved me a very long Sunday having to manually adjust every image URL after a mass migration of a blog with over 12 posts within it. As you say – one should be cautious in regards to the trailing slashes. Thanks

      • Vincent 5:29 am on August 31, 2015 Permalink | Reply

        Correction – should read: ‘over 1200 posts’

    • Ramón Carmona 2:23 pm on October 26, 2015 Permalink | Reply

      Really this is the most complete and concise guide that will help anyone trying to change their wordpress site from one url to another. I think not even wordpress explains this topic in a detail. Thanks for the post Jays Versluis

      • Jay Versluis 11:38 am on October 27, 2015 Permalink | Reply

        Thank you 🙂

    • Jerry 3:53 pm on January 1, 2016 Permalink | Reply

      Thanks! Exactly what I needed. What I thought was going to take me hours took less than a minute (needed to update image URLs in all posts.)

    • Md. Ariful Islam (@cpncb) 12:38 pm on October 9, 2016 Permalink | Reply

      Thanks .

    • Estudio 27 Asturias 4:12 am on August 3, 2017 Permalink | Reply

      Me ha parecido de gran ayuda.
      gracias

  • Jay Versluis 11:27 am on February 25, 2009 Permalink | Reply
    Tags: , phpMyAdmin   

    Categories: WordPress ( 135 )

    How to backup your WordPress database? 

    The Database is the heart of your WordPress installation. Simply backing up and restoring all your files via FTP is not enough – it only contains the actual WordPress “programme” if you will, your plugins and themes, and all your uploaded media files (like pictures). It does NOT however contain your posts, links and some of the configuration data. That’s where the MySQL Database comes into play.

    Backing up your database is as important as backing up all other data on your server, and I’m afraid to say that if your server develops a fault, you’ve got nobody else but yourself to blame.

    Having said that, here’s a plugin that will do all the hard work for your, if you (want to) know about databases or not:

    Un-zip the contents, copy it into your /wp-plugins/directory, activate the plugin and off you go. You can even schedule your backups on a daily basis and have them emailed to you for safe keeping.

    Once you’ve done this, do somethign nice for yourself. Once you’ve done that, please familiarise yourself with how to RESTORE your database:

    Let me leave you with this thought:

    Backup often – you won’t regret it!





     
  • Jay Versluis 11:08 am on February 25, 2009 Permalink | Reply
    Tags: , , phpMyAdmin   

    Categories: MySQL ( 19 )

    How to restore your database? 

    The Database is the heart of your WordPress installation. Simply backing up and restoring all your files via FTP is not enough – it only contains the actual WordPress “programme” if you will, your plugins and themes, and all your uploaded media files (like pictures). It does NOT however contain your posts, links and some of the configuration data. That’s where the MySQL Database comes into play.

    For any of these articles to make sense, you need to be familiar and have access to phpMyAdmin. Your hosting provider will gladly help you our there.

    If you have a fairly small database (up to 5MB), have a look at this:

    If you database is significantly larger, you need other means of uploading and importing it. I reccomend the sue of a PHP script, as detailed here:

    One sure PHP Script is BigDump:

    Obviously, before a RESTORE actually works, you would have had to BACKUP your database (naturally). Perhaps you would like to check out this article to find out how to do this:

    http://wordpress-guru.co.uk/archives/how-to-restore-your-databasehow-to-backup-your-database/





     
c
compose new post
j
next post/next comment
k
previous post/previous comment
r
reply
e
edit
o
show/hide comments
t
go to top
l
go to login
h
show/hide help
shift + esc
cancel