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.
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.
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.
Hungry for more Pi?
For more project ideas be sure to check out our other Raspberry Pi Guides.