Category: MySQL Toggle Comment Threads | Keyboard Shortcuts

MySQL is a complex topic – I know enough to get by in the world of WordPress. It’s one of those topics that you never knew you needed until you started diving deeper into storing data.

I like to pronounce it “My Ess Queue Elle”, not “My Sequel”…
and here are some other pointers I’ve picked up along my journey.

  • Jay Versluis 11:26 am on November 2, 2015 Permalink | Reply
    Tags: , ,   

    Categories: MySQL ( 19 )

    How to install MySQL on Mac OS X El Capitan 

    MySQL 2015

    There are several ways to install MySQL on your Mac, for example:

    • compile from source
    • use the Homebrew package manager (
    • use a nifty script courtesy of Mac Mini Vault (
    • or use the dedicated MySQL installer package (recommended)

    I recommend the dedicated installer because it’s the only package that will also add a convenient Preference Pane for starting and stopping the service.

    In this article I’ll focus on the latter, and I’ll also talk you through how to add MySQL to the PATH variable and how to secure MySQL to keep the evildoers away from your server.

    These instructions will work on Yosemite and El Capitan (I’ve tested it on both systems – in fact that’s part of why I’m writing this, so that I can remember for next time).


    Screen Shot 2015-11-02 at 10.40.12

    (More …)

    • Wojtek Jakóbczyk 12:39 pm on November 11, 2015 Permalink | Reply

      Just a quick question – why would you recommend the mysql 10.9 package instead of 10.10?

      • Jay Versluis 5:28 pm on November 11, 2015 Permalink | Reply

        Hi Wojtek, sorry about the confusion – that’s not what I meant: if you’re running 10.10 or higher, definitely use the 10.10 package. The MySQL version and implementation is exactly the same on the 10.9 package, just the installer is different.

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

    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, '',

    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 :-)

  • Jay Versluis 11:54 am on December 4, 2014 Permalink | Reply  
    Categories: MySQL, Plesk ( 19 )

    How to move databases between subscriptions in Plesk 

    You can move databases and database users between subscriptions in Plesk. There’s no web interface for this, but with a bit of manual database tweaking you’ll soon get the hang of it.

    I recently split a subscription into two for a client and this trick came in handy.

    Before we begin, make sure you backup the psa database – that’s what Plesk uses to keep track of internal values, anything from user names, passwords, and which service is associated with what. If you ruin psa you’ll ruin your Plesk installation. Use caution!

    Editing psa

    You can use phpMyAdmin from Plesk to edit the psa database. Head over to Tools and Settings (or the Server Tab), Database Servers and click the little wrench icon. This will open phpMyAdmin in a new window.

    Screen Shot 2014-12-04 at 11.34.02

    Find the psa database and click on the little disclosure plus icon. This will show you all its tables, similar to this:

    Screen Shot 2014-12-04 at 11.37.24

    Scroll down to find data_bases and db_users. Open either of them (with the little disclose icon again) and you’ll find a list of databases and users respectively. Note the column dom_id. This is how Plesk knows which subscription (or domain) this database belongs to. MySQL takes care of the actual database, the value here is for visual representations in Plesk only.

    The difficult bit is to find out which numeric dom_id translates into which domain. There’s not an easy way to extract that info from Plesk, so we’ll use a quick workaround: create a new identifiable database (and user) in the subscription we’d like to move to and simply look at which dom_id it gets.

    Creating a Dummy Database

    Back in Plesk, head over to the subscription you’d like to move your database to and create a memorable user/database combo. Anything will do, we’ll delete this later. Call it “aaaaaaaaa” or “comehere” – up to you.

    Once done, head back over to psa database in phpMyAdmin, refresh and look at the data_bases (and db_users) again. You’ll see something like this:

    Screen Shot 2014-12-04 at 11.50.55

    Now we know that our important_database (and important_user) need a dom_id value of 2 instead of 1. Change it in both tables – and you’re done!

    Head back into Plesk and check your subscriptions: the database and user will have disappeared from subscription 1 and will now appear in subscription 2.

    Thanks to Matt Nelson for this tip!

    • Paul Martin 5:04 pm on July 9, 2015 Permalink | Reply

      Thanks for help been looking for this since upgrading 11 to 12. BUT when I did above I could not access the copied database via phpMyadmin on the domain I copied database. I got a loop error. When I tried to simply copy the new database I got error that I couldn’t. I wondered if it was due to permissions in the database still linking to the domain it was copied from?

      • Jay Versluis 10:03 am on July 10, 2015 Permalink | Reply

        Hi Paul, I’m afraid I’ve never encountered this problem before – I’m not sure what to make of it. If Plesk gives you any trouble, you can simply read out the database and recreate it manually under a different subscription. It’s more work, but at least that way Plesk does not complain.

  • Jay Versluis 11:42 am on September 22, 2014 Permalink | Reply  
    Categories: MySQL, Plesk, Screencast ( 19 )

    MySQL Databases and phpMyAdmin in Plesk 12 

    In this screencast I’ll show you how to access your MySQL databases from Plesk and where to find the phpMyAdmin tool.

    I’ll also show you how to create MySQL users and copy databases in Plesk – this can come in handy when you’re about to make a change and you want to create a backup in case something goes wrong.

  • Jay Versluis 1:15 pm on July 3, 2014 Permalink | Reply  
    Categories: MySQL ( 19 )

    How to find and replace in MySQL 

    Sometimes you need to change some text in a MySQL table. For example, imagine you want to correct a spelling mistake or a reference in all your WordPress posts without having to change them one by one.

    Here’s how to do it with a raw SQL command. This works only on a single table – repeat this for each table:

    update `wp_posts`
    set `post_content` = replace(post_content, 'Old Text', 'New Text')

    Note the use of standard ticks and back ticks here. First we’ll select the table in question (in this case wp_posts), then we’ll issue the find and replace query by selecting the column in the table (post_content), followed by the replace command.

    As soon as MySQL comes back all references will have changed from “Old Text” to “New Text”.

    Careful here: there is no undo function! Once executed, all changes are live instantly. Make a backup copy of your database before you do anything!

  • Jay Versluis 7:12 pm on March 26, 2014 Permalink | Reply  
    Categories: Linux, MySQL, Plesk ( 75 )

    How to log into MySQL as root user in Plesk 

    Plesk-LogoYou may have noticed that there is no MySQL root user on servers running Plesk. That’s because Plesk renames this user into “admin” by default – for security reasons.

    The password for the admin MySQL account is the same as for the Plesk Panel admin account.

    Even so, when you try to login to MySQL – remotely or locally – you may be puzzled to find that your admin password doesn’t seem to work. Let me assure you of your sanity and your keyboard skills: it’s because Plesk encrypts the password in the database.

    It is the encrypted version that you must present to MySQL, not the clear version. For example, if your password was indeed “password”, then the following command will not grant you access to MySQL:

    mysql -u admin -ppassword

    You can check your unencrypted password by issuing the following command (on Linux servers):

    /usr/local/psa/bin/admin --show-password

    In our example, it will indeed show “password” – so why doesn’t it work? It’s because that command will unencrypted the password for us. MySQL however needs the encrypted version. Here’s how we can extract this from Plesk:

    cat /etc/psa/.psa.shadow
    // will show you something like

    This will give you a weird looking output as shown above. Believe it or not, that’s your MySQL admin password!

    If you’re already logged into your server as root and want to issue a MySQL shell command, you can login to MySQL like so:

    mysql -uadmin -p`cat /etc/psa/.psa.shadow`
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 4231837
    Server version: 5.5.36-cll-lve MySQL Community Server (GPL) by Atomicorp
    Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    If you’re attempting a remote connection to MySQL then simply paste that cryptic looking password you got in the earlier step.

    • diego 12:54 am on November 2, 2014 Permalink | Reply

      If your memory is a bitch, like mine use this to access directly:

      root@server:[~]: cat /etc/psa/.psa.shadow

      root@server:[~]: cat /root/.my.cnf

      root@server:[~]: mysql

  • Jay Versluis 6:38 pm on May 13, 2013 Permalink | Reply
    Tags: , replication   

    Categories: MySQL ( 19 )

    MySQL Replication Troubleshooting 

    crab-iconSometimes things don’t work out with replication. When I first started experimenting with it I thought this was a “setup and forget about it” kind of job.

    Experience has shown though that you have to regularly triple check and see if things may have broken (despite a good and once working setup).

    Let’s take a look at what you can do when your Slave isn’t replicating anymore. If you want to know more about how to setup replication, have a look at my previous article in which I explain how this works.

    (More …)

    • Kanika 12:17 pm on December 8, 2013 Permalink | Reply

      My testdb Lab setup crashed due to low disk space on root dirve on my ec2 Amazon linux systems.

      I have a DB on Mysql servers.

      Does removing the Mysql database on Master & slave & also removing Bin log files from both the servers have any negative impact on replication If I create the databases with the same name in future.

      Any help appreciiated

      • Jay Versluis 1:30 pm on December 8, 2013 Permalink | Reply

        Hi Kanika,

        Removing a database will not delete those log files, it will merely add a log entry that you did it. Re-creating it with the same name in the future will also create a log entry, so as long as the slave does all the same steps it should not have a negative impact in the future. Good luck with the rebuild 😉

  • Jay Versluis 11:41 am on May 11, 2013 Permalink | Reply  
    Categories: Linux, MySQL ( 75 )

    How to setup MySQL Master/Slave Replication with existing data 

    mysqlThis is a step-by-step guide on how to replicate an existing MySQL server. The server is live and contains data and needs a constant backup companion.

    Many tutorials focus on how to setup replication when no data is present on the system. That’s an ideal solution if you’re building a new setup, but in case you’ve got a server that already has data present then here’s how to accomplish the this:

    1. setup your existing MySQL server (with data) as a Master
    2. export all your databases and user accounts
    3. create a slave and import all your data
    4. start replication

    I’ve done this several times and always forgot to take some notes – until today. Without further ado, let’s replicate MySQL.

    (More …)

    • Fabrizio 3:30 pm on May 16, 2013 Permalink | Reply

      A couples of errors:

      mysql# release lock; -> this should be mysql# unlock tables;

      root# mysql -p > everything.sql -> this should be root# mysql -p < everything.sql

      And some code error:

      mysql# CREATE USER 'replicator'@'%' IDENTIFIED BY '***';

      mysql# GRANT REPLICATION SLAVE ON * . * TO 'replicator'@'%'

      Remove the "" parts

      • Jay Versluis 10:54 pm on May 16, 2013 Permalink | Reply

        Thanks Fabrizio,
        you’re a life saver – not sure how I got those rather crucial commands wrong there. I’ve updated the article. Much appreciated!

    • Nathan 9:53 pm on September 10, 2015 Permalink | Reply

      How to get or open that command line?

      • Jay Versluis 10:33 am on September 11, 2015 Permalink | Reply

        Hi Nathan, that’s a complex subject 😉 These command line tools are commonly referred to as SSH Clients, SSH being the type of connection you typically make between your local system and the remote server. On Mac and Linux systems there’s a built-in tool you can use. It’s called Terminal. On Windows you have to install one, for example PuTTY. You can even get utilities for iOS and Android devices.

        Note that this article discusses two types of connections: the first is to the remote server’s operating system, and the second is to the MySQL server. Although they may be installed on the same system, the command line syntax is very different (shell commands vs. MySQL commands).

  • Jay Versluis 5:13 pm on February 19, 2013 Permalink | Reply  
    Categories: MySQL, WordPress ( 19 )

    How to bulk delete posts in WordPress with MySQL 

    mysqlUsually deleting several posts at once is not a problem thanks to the bulk delete options in WordPress. Those queries however rely on a single delete each, initiated by PHP loop. That’s fine if you’re deleting up to about 100 posts at a time.

    But it’s not when you have thousands of posts to delete. I’ve come across installations with hundreds of thousands of posts which have often been created automatically – and there comes the time when you need to clean things up and prune that massive database.

    Deleting 100.000 posts is impossible from within WordPress: it puts a huge load on your server, it takes forever, and besides WordPress will time out after about 200 posts.

    The solution: a dedicated SQL query.

    Sounds scary I know – let’s go through it step by step in this article.

    (More …)

    • Brad 11:52 am on February 7, 2015 Permalink | Reply

      Isn’t this for posts of a certain category? What if I have thousands of posts from thousands of categories? How do I delete all of the posts, while keeping the categories intact?

      • Jay Versluis 2:03 pm on February 7, 2015 Permalink | Reply

        Hi Brad,

        That’s right, the code bulk deletes all posts from a single category or a tag. To delete several categories/tags in one statement, add lines with an OR statement to the bottom like this:

        delete a,b,c,d
        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 )
        LEFT JOIN wp_term_taxonomy d ON ( d.term_taxonomy_id = b.term_taxonomy_id )
        LEFT JOIN wp_terms e ON ( e.term_id = d.term_id )
        WHERE e.term_id = 2
        OR e.term_id = 3
        OR e.term_id = 4

        For thousands of categories that’s not the approach you’re looking for either. The best I can suggest is to select everything that’s a post like so

        SELECT *
        FROM wp_posts a
        WHERE a.post_type = 'post'

        Trying to use this as a DELETE statement doesn’t work on my system though. You can still delete those posts with phpMyAdmin by selecting up to 500 at a time (slightly lame I know, but it works).

        • Brad 4:48 pm on February 7, 2015 Permalink | Reply

          Thank you for that, used with certain categories. For some reason, I was under the impression that removing a post (that was the ONLY post under a category) would remove the category, too. After learning that isn’t the case, I found a simple query that worked for me:

          delete from wp_posts where post_date < DATE_SUB(NOW(), INTERVAL 1 MINUTE);

          After that, I had to remove the posts from the trash with a DB optimizer. Then, I was left with nothing except the thousands of categories in which to add products to (i.e., it's a new ecommerce site).

          • Robust Group 4:10 am on September 2, 2015 Permalink

            this is not exact.
            you will have a lot of data left inside wp_term_relationships, wp_postmeta, wp_term_taxonomy – these are the tables that link the posts to other categories and other elements.

    • Dukewa 3:52 pm on April 9, 2015 Permalink | Reply

      I’m just trying to delete 2,00 posts created on a specific date. The function above would be great, but I can’t find the right field/column name to use in place of the “term_id”. Anyone know what this would look like if the WHERE clause used a specific date? Thanks much.

  • Jay Versluis 8:34 pm on February 18, 2013 Permalink | Reply

    Categories: MySQL ( 19 )

    What is the default MySQL password for root in MAMP? 


    It’s root.

    This is not the same on a vanilla MySQL install where the root user is not secured by a password by default – however under MAMP it is.

    So on a fresh MySQL install the credentials are

    User: root
    Password: (nothing)

    Under MAMP (and presumably MAMP Pro) they are

    User: root
    Password: root

    Now we know – thank you Aral 😉

compose new post
next post/next comment
previous post/previous comment
show/hide comments
go to top
go to login
show/hide help
shift + esc