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:42 am on September 22, 2014 Permalink | Reply  
    Categories: MySQL, Plesk, Screencasts ( 16 )

    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 ( 16 )

    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 ( 46 )

    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
    $AES-128-CBC$w78TYgIfzDsKjOvEqkg/nQ==$O4xPUtsQe1TI3P601wQgYw==

    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
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql>

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





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

    Categories: MySQL ( 16 )

    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 ( 46 )

    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'@'%'
      IDENTIFIED BY '***' WITH MAX_QUERIES_PER_HOUR 0
      MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0
      MAX_USER_CONNECTIONS 0 ;

      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!

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

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





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

    Categories: MySQL ( 16 )

    What is the default MySQL password for root in MAMP? 

    mamp_home

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





     
  • Jay Versluis 6:24 pm on September 24, 2012 Permalink | Reply  
    Categories: MySQL, WordPress ( 16 )

    How to move very large MySQL Databases 

    I’ve been shifting some things around and came across several very large WordPress databases for a few projects I was hosting. The largest was 1.3GB or 317MB compressed (!) which obviously poses a problem. Most sites – even those of frequent bloggers – amount to well under 2MB when compressed, but Multi Site or BuddyPress databases can get large quick.

    To extract a database from a WordPress site, I’ve always relied on the superb WP DB Backup by Austin Matzko but even that timed out after 170MB.

    In this article I’ll talk you through how we can handle this. I’m assuming here that a simple phpMyAdmin export/import isn’t working for you anymore. This is by all means not the only way to go about this task.

    (More …)





     
    • ox 6:42 pm on September 24, 2012 Permalink | Reply

      “Hope this article helps…” — nope, useless, I don’t have a Mac.

      • Jay Versluis 7:02 pm on September 24, 2012 Permalink | Reply

        You sad sad man (or woman). I suggest you stop whining and get a Mac.

    • Elliott Richmond 7:29 am on September 26, 2012 Permalink | Reply

      SequelPro rocks!! But you can still use it to upload to the new server as well, no need for BigDump unless I’m missing something here?

      • Jay Versluis 9:35 am on September 26, 2012 Permalink | Reply

        You can??? That would be absolutely awesome! I didn’t find an import feature – could you describe where it is?

        • Alessio 5:08 am on January 3, 2014 Permalink | Reply

          I’ve done a copy/paste of SQL queries with a 70mb (uncompressed) db, so very small compared with your, and SequelPRO performs around 1500 queries in less than 10 minutes… this was my n00b way :)

    • Stephen 10:48 am on February 26, 2013 Permalink | Reply

      Great stuff! Very informative, thanks for having this blog out there, very helpful for WordPress.org developers!

    • Daitya 10:30 pm on September 9, 2013 Permalink | Reply

      Jay, I’ve got a wp multisite installation with 75 tables and 72 MB on a shared hosting account running Cloud Linux, and cPanel’s phpMyAdmin is just not handling backup any longer. Host doesn’t allow shell access on shared hosting accounts, and switching over to VPS or dedicated hosting is out of budget. Would you recommend using phpMyAdmin to download the db in parts (say, x number of tables in one download, and y number of tables in another)? Can these be stitched together when populating a new database as in restore or dev work? How would this work?

      • Jay Versluis 10:51 pm on September 9, 2013 Permalink | Reply

        Hi Daitya, yes that does sound a bit large for phpMyAdmin to handle. My two cents: Splitting the database into chunks could work, I’d try exporting it table by table – you can probably group them together, and only export the largest one separately (however you may find that one table takes up 90% of your total size, probably the posts table). It’s trial and error with phpMyAdmin I’m afraid. I would not recommend breaking up tables though – it can get extremely messy.

        A much better and more convenient way I’d say is Sequel Pro. It’s a free Mac app that lets you administer your database remotely, so you can read it out right on your desktop and export it that way: http://www.sequelpro.com. This will also allow you to conveniently import large databases. I swear by it!

        Another idea to get round the shell access would be to access your MySQL server from another Linux machine, and issue a mysqldump command from there. Your “local” Linux system would then access the remote one. Let me know if this makes sense.

    • http://jordimundo.com 2:07 pm on June 8, 2014 Permalink | Reply

      sequelYOG on PC, there is a free version, and it is awesome, actually I’d say it is better than sequel pro, you can copy DB across hosts straight for example…

      • Jay Versluis 2:24 pm on June 8, 2014 Permalink | Reply

        Thanks Jordi, awesome tip – I was always wondering if there’s a similar tool on Windows. I’ll check it out – thanks for sharing!

  • Jay Versluis 11:09 pm on June 16, 2012 Permalink | Reply  
    Categories: MySQL ( 16 )

    How to change your MySQL User Password 

    Today I had to admit that I did not remember the password for a MySQL user account. Since it’s saved in encrypted form simply reading it out won’t mean I can recover it. A Google search did eventually bring up the correct way of doing it, however it took me quite some time – so here’s how you can do this:

    (More …)





     
  • Jay Versluis 4:21 pm on April 22, 2012 Permalink | Reply
    Tags: , ,   

    Categories: Linux, MySQL ( 46 )

    How to install MySQL on CentOS 

    This is a step-by-step guide of what you need to do in order to install and setup MySQL on a new server.

    We’ll prepare a fresh CentOS 6 system (64 bit) for use as a database server. All you need is access to an SSH client and your server root credentials.

    (More …)





     
    • Jeff Huckaby 2:27 pm on June 20, 2012 Permalink | Reply

      Another item I always do is update the default my.cnf. By default, a number of features, such as query caching, is not enabled.

      If you look in:
      /usr/share/doc/mysql-server-5.0.95/

      You will find some example my.cnf files. I typically use my-large.cnf as a starting point for systems with 4GB of RAM or less. Use my-huge.cnf for systems with more than this.

      Once deployed, check out tools like http://mysqltuner.pl and http://hackmysql.com/mysqlreport to better optimize your installation.

      • Jay Versluis 10:41 pm on June 21, 2012 Permalink | Reply

        Thank you so much for sharing Jeff, much appreciated! I’ll go check it out ;-)

    • wayne 12:01 pm on September 2, 2012 Permalink | Reply

      hi, i am trying to set up mysql, and i still don’t see how to get to the actual webpage to set up everything. i set everything up right as far as i know. but i still need help getting to the actual program

    • gunawan 7:20 am on October 1, 2012 Permalink | Reply

      I tried but install but after click the yum
      service ….
      is failed
      then I try
      mysql
      “error 2002 …”

      what I miss here?

    • Tom 4:30 am on December 24, 2012 Permalink | Reply

      It doesn’t work with me.
      With no configuration, i think it’s impossible

      • Jay Versluis 4:12 pm on December 24, 2012 Permalink | Reply

        What’s the error message you’re getting? Is yum installed on your system?

    • booksd 9:46 am on January 11, 2013 Permalink | Reply

      Any suggestions for the following command and response?

      [root@LiunxCentos arasu]# service mysql start
      mysql: unrecognized service
      [root@LiunxCentos arasu]#

      • Jay Versluis 4:06 pm on January 11, 2013 Permalink | Reply

        Sounds like MySQL is not installed, or your system does not recognize the service command. You can also try this:

        /etc/init.d/mysqld restart

        If you get a similar error message, then MySQL is not installed.

      • Jay Versluis 4:08 pm on January 11, 2013 Permalink | Reply

        I just re-read your command – the service is called mysqld, not mysql. Try again using

        service mysqld restart

        • Yeah 9:44 pm on January 11, 2013 Permalink | Reply

          Yeah, you actually wrote ‘service mysql restart’ in the tutorial, I imagine its tripped up a few others, myself included. Only took me 30 secs on google to figure out its ‘service mysqld restart’..Good article anwyays, thanks

          • Jay Versluis 12:41 am on January 12, 2013 Permalink | Reply

            How embarrassing!! I’ll change that right away – thanks for letting me know ;-)

            • Carl Partridge 10:40 am on May 3, 2013 Permalink | Reply

              Actually, don’t change it – on some installations the service is called mysqld, on some the service is called mysql

              If you install from the current packages as per your tutorial, the service is called mysql

    • Mihai Alexandru 6:51 pm on February 23, 2013 Permalink | Reply

      Go on http://softiny.com and choose the LINUX tab and there are software apps to help you do this…

    • Grover 11:38 pm on March 25, 2013 Permalink | Reply

    • Independent Software Developer 4:14 pm on March 26, 2013 Permalink | Reply

      My first time really getting into CentOS and I’m trying to setup basic hosting for my site… I now have a new found respect for system admins because I have gotten so many headaches its not even funny.

      Thanks for this tutorial!

    • madhu 11:31 pm on January 25, 2014 Permalink | Reply

      hai frnds. . . ..
      iam facing problem with secure installation of mysql in centos 6.5
      while secure installation of mysql : /usr/bin/mysql_secure_installation
      am getting error as:Enter current password for root (enter for none):
      ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)
      am entered as enter button only,i got the above error

      • Jay Versluis 11:48 pm on January 25, 2014 Permalink | Reply

        Try setting a password via the mysqladmin command as described by the friendly install message. Also, check if MySQL is actually running – you can’t connect if the service is down.

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