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 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 MySQL 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 thing we're going to do to optimize MySQL is replace 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 MySQL's 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 MySQL 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 MySQL 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.


comments powered by Disqus