How to install and optimize MySQL on Raspberry Pi

It wasn’t until recently that I actually had a need to use MySQL on my Raspberry Pi so I figured I’d share my learnings on the general installation and a couple of things you can do to help optimize the performance. For this tutorial you will want to be running Raspbian.

Installing MySQL

Thankfully due to Debian’s wonderful packaging system, installing MySQL is quite easy to do. Just run the following commands.

sudo apt-get update
sudo apt-get install mysql-server

After the package is installed it will ask to set a root password, this account has full administrative privileges so you generally want to give it a fairly strong password. Once that’s done the service will be up and running and at this point you could use it but there’s a couple things we can do to make it run a little better on the Pi.

Optimizing MySQL

The first step we’re going to take towards optimization is replacing the default my.cnf configuration file with one that is tuned for systems with less resources.

sudo mv /etc/mysql/my.cnf /etc/mysql/my.cnf.bak
sudo cp /usr/share/doc/mysql-server-5.5/examples/my-small.cnf /etc/mysql/my.cnf

And then the other thing we’re going to do is set a size for the query cache. Basically, MySQL has the ability to cache query results in memory until the data behind them is changed, thus delivering the results faster. It’s up to you how big you want to make it, but as a default I’m just going to use 8 megabytes. So to do this open up /etc/mysql/my.cnf in a text editor and navigate to the[mysqld] section of the file and place the following line in it.

query_cache_size = 8M

Now make sure you save your changes and let’s restart the service for all the changes to effect.

sudo service mysql restart

And there you go, MySQL is now optimized for your Raspberry Pi.

Using MySQL with PHP (Optional)

Since MySQL is often used in a LAMP or LEMP stack I figured it would also be helpful to provide instructions on what you need in order to use it with PHP. Once again it’s pretty simple, we just need to install the PHP extension as follows.

sudo apt-get install php5-mysql

By default when you install the extension it will automatically be enabled so now if you do one of these in a PHP page, you should see that the extension is loaded.

<?php phpinfo(); ?>

If you don’t see it enabled then you may need to restart you system for the extension to get loaded. But once you’ve confirmed that it’s there then you’re all set to start using MySQL in your PHP.

  • Scott S.

    Hi, I’m trying to get this working and I got an error when running the following command:

    pi@scraperbot ~ $ sudo cp /usr/share/doc/mysql-server-5.5/examples/my-small.cnf
    cp: missing destination file operand after `/usr/share/doc/mysql-server-5.5/examples/my-small.cnf’
    Try `cp –help’ for more information.

    Any insight as to what might be gong on would be greatly appreciated.

    • John D.

      The cp command works with a “target” and a “destination”. The first file you write in the cp command is the target, the second will be the destination.

      So for instance if I were to copy the file package.deb from my downloads directory to my home directory it would look something like this:
      cp /home/me/downloads/package.deb /home/me/package.deb

      This will keep the original file package.deb in the downloads directory and create a copy of it in the home directory.

      In this case what you want to do is use the small mysql config file from the examples directory as your standard mysql config file. To do that you would need to use the following command:
      sudo cp /usr/share/doc/mysql-server-5.5/examples/my-small.cnf /etc/mysql/my.cnf

  • Scott S.

    So I gave it the destination path of the first file we moved. Hopefully, that was the right way to go.

    • Ben Hanna

      Yep, that’s correct. I didn’t notice until now that I forgot to add the destination in that command.