Tutorial – Install MySQL server on Raspberry Pi

In this tutorial, I’m going to go through the steps to install MySQL on Raspberry Pi. This is part 2 in a 3 part tutorial which will ultimately see WordPress running from Nginx on your Raspberry Pi.

Prerequisites

I’m assuming that you know your way round command line interface and are comfortable using it. I’m also assuming that you’ve followed the previous tutorial on how to install Nginx and PHP on Raspberry Pi.

Step 1 – Important, check your firmware

Before we begin, there is a bug either in Raspbian or in the Firmware which prevents a successful install of MySQL Server on Raspbian. Type the following command to find which version of the firmware you have installed:

sudo uname -a

If your firmare reads this:

Linux raspberrypi 3.12.20+ #687 PREEMPT Fri May 30 16:39:11 BST 2014 armv6l GNU/Linux

Then we will need to roll it back to the April firmware in order to prevent the install from failing. Type this command:

sudo rpi-update f6eef32dd6388c3b04dbf462bd324d93281bf397

If your firmware does not appear to be Friday May 30, skip to step 2. The Firmware update utility will begin running. Important, make sure you have backed up everything just in case the firmware fails to update. The process takes approximately 5 minutes, after which you will see the following message:

Firmware Update Raspberry Pi

When the reboot message is displayed, go ahead and reboot your Raspberry Pi by typing:

sudo reboot

It may take a bit longer than usual to start back up, but yet again just let it do it’s thing and you should be back up and running.

Step 2 – Install MySQL on Raspberry Pi

Now we’re ready to start the installation. First we need to ensure everything’s up-to-date:

sudo apt-get update && sudo apt-get upgrade

Install any out of date packages when prompted. Next we’re going to install the actual MySQL server by typing:

sudo apt-get install mysql-server --fix-missing

I tend to use the –fix-missing flag when installing mysql-server as in the past I’ve found some packages were missing. You should be prompted for a root password:

Firmware Update Raspberry Pi

Choose a secure password, and keep it safe. You will need this later to create additional users for WordPress. You will be prompted to confirm your password:

Firmware Update Raspberry Pi

 

Once you’ve confirmed your password, MySQL server should finish installing and the service should start.

Now we need to install a couple of extra packages, php5-mysql and mysql-client. The php5-mysql package allows connections to be made to MySQL Server through PHP, and mysql-client allows us to connect to our local MySQL server through the CLI:

sudo apt-get install mysql-client php5-mysql

That’s it. Now all we need to do is create an additional user and create our database, ready for when we install WordPress.

Step 3 – Create a Database and a MySQL user for WordPress

We need to connect to our local MySQL instance and create a user and a database. Type the following command:

mysql -uroot -hlocalhost -p

You will be prompted for the root password. Go ahead and type the password that you gave for the root user in step 2. You should now see something that looks like this:

Install MySQL on Raspberry Pi

 

I’m going to call my database wpdb, but you can call yours whatever you want. Just substitute wpdb with the name of your choice:

CREATE DATABASE wpdb;

This will create the database. With MySQL, ensure you end each command with a semicolon. I’m going to call my database user wpuser but yet again, call yours anything you like. Also choose a strong password and substitute password_here with the password of your choice for the new user:

CREATE USER 'wpuser'@'localhost' IDENTIFIED BY 'password_here';

This creates the user that our WordPress installation will use to connect to its database. We’ve created the user with the host localhost as we will only be connecting to MySQL server on the local machine (the Raspberry Pi). Now we need to give the user wpuser access rights to the database wpdb. Type the following:

GRANT ALL PRIVILEGES ON wpdb.* TO 'wpuser'@'localhost';

Finally, flush the privileges for the changes to take effect:

FLUSH PRIVILEGES;

We’ll quickly test this to ensure we can login using our newly created user on our new database. Press CTRL + C to exit MySQL Client. Login using your new credentials:

mysql -uwpuser -hlocalhost wpdb -p

When prompted, type the password that you assigned to your new user. You should be logged into the server using these new credentials.

Conclusion

So, let’s recap what we’ve done so far. We’ve rolled back our firmware if required, we’ve installed MySQL Server, MySQL client and the package to allow WordPress to connect to our local database, and finally created a new user and database ready for WordPress.

In our final tutorial in this series, we concentrate on installing and configuring WordPress on our Nginx web server. Keep your eyes peeled for the tutorial. Any comments or questions, sound them below.

Ste

Web Developer living in Manchester, working for Studio Skylab (http://www.studioskylab.com). Views and thoughts are my own.

You may also like...

24 Responses

  1. alongtheway says:

    bash: $’sudo302240rpi-update’: command not found

  2. alongtheway says:

    A reboot fixed it, not sure what happened. I updated to the latest firmware (released today) and everything is working well. The mysql install freezes, but after rebooting, it all seems to be there.
    Linux RaspberryPi 3.12.22+ #690 PREEMPT Mon Jun 16 13:25:46 BST 2014 armv6l GNU/Linux

    I also changed

    GRANT ALL PRIVILEGES ON wpdb* TO ‘wpuser’@’localhost’;

    to

    GRANT ALL PRIVILEGES ON wpdb.* TO ‘wpuser’@’localhost’;

    Don’t forget to run as root or with sudo! (sudo mysql -uroot -hlocalhost -p)

    Now anxiously awaiting part 3! Thanks!

    • Ste W says:

      Hi

      Thanks for the message. I’ve corrected the typo on the GRANT ALL PRIVILEGES now, thanks for the heads up there! The issue of it getting stuck on the installation was the exact same issue I was having, hence the rollback to a firmware I knew worked. It hung at “Processing triggers for menu”, hopefully the guys over at raspberrypi.org will fix that soon.

      With the MySQL client, you don’t necessarily need to run as sudo as it uses MySQL authentication rather than unix authentication. If you’re doing SQL dumps though, you would need to run as sudo, as you’d need permission to save to the filesystem.

      I’m working on the final part today, I’ve made my notes, just need to tidy them up into a tutorial. Hopefully end of play today I’ll have the next bit up 🙂

      • alongtheway says:

        Without the sudo, my “GRANT ALL PRIVILEGES ON wpdb.* TO ‘wpuser’@’localhost’;” statement kept saying no database…though this was also during the time that I figured out the typo. I think I tried the correct line without sudo and it didn’t work, but I guess I can’t be certain. Good to know that it shouldn’t be needed, though. Thanks! I am learning a lot!

  3. I enjoy your tut very much – I’ve been looking for a howto on setting up a DB for a while – until I stumbled over a link to your site 🙂
    I might use your tut for a WP blog later – but for now I’m only interested in setting up a DB.
    I need the DB to run on an external usb drive (WD – 2TB) to hold the db of a new project to go public soon, and that db could grow over time, hence it’s not viable to have it reside on a micro sd card.
    You might have already done a tut on this, but how do I setup mysql to hold the storage on an external drive?

  4. jaggaTW says:

    Hi, rpi2 does not respond after
    sudo rpi-update f6eef32dd6388c3b04dbf462bd324d93281bf397
    & reboot..
    What happend?

  5. Tom says:

    Hi. I still get loads of 404 errors when I try to install MySQL.

    Do you know how to fix this?

    Thanks,
    Tom

  6. matt says:

    going back to old firmware killed my whole card. had to wipe it and start again. not happy

  7. Lawrence says:

    Love your site and tutorials man! I’ve spent a lot of time here as I’ve been tinkering with the raspberry pis I have.

    One question on the MySQL server install. I’ve seen other tutorials out there that suggest you run the following command to secure your MySQL server a bit:

    sudo mysql_secure_installation

    Do you do that or use other (more hands on) methods to harden it?

  8. Do not rollback, I now no longer have a Raspberry PI 2… will need to start from scratch now.

  9. James says:

    great tutorial! One question though, I am trying to connect to the server from an external computer, using mysql workbench, but it is not working. What do I need to do to set this up?

  10. snigda says:

    after reboot it is not again restarting where the problem occurs….????

  11. Syed Haris says:

    hey @Ste
    if i just adding database into raspberry pi not for the wordpress, is it possible..?
    And after reboot its not starting i have spend 20 mint but it not start.
    Thanks

  12. Chmod says:

    Thanks for this, It’ll be really helpfull.
    Is the step 1 still required ? Or with the newer version, it’s corrected ?

  13. Sneha says:

    Thank you so much! this helped a lot! 🙂

  14. reddioment says:

    Is the next (3rd) tutorial out?

  1. 16th June 2014

    […] Ste Wright In this tutorial, I’m going to go through the steps to install MySQL server on your Raspberry […]

  2. 24th June 2014
  3. 14th November 2015

    […] Part 2: Install MySQL server on Raspbian […]

  4. 3rd July 2016

    […] Tutorial – Install MySQL server on Raspberry Pi […]

  5. 27th July 2017

    […] some information from this guide, you will need to install MySQL and drivers. Php is not needed for […]

Leave a Reply