Automated reporting with a Raspberry Pi: Part 2 – MySQL

A MySQL database serves as the backend for the automated reporting. This is not overkill. Using Excel data tables enforces sensible data layout (typically, the biggest problem with any Excel workbook), and introduces SQL expressions that allows us to avoid horrendous nested/array-formula complications.

Setting up MySQL on the Raspberry Pi is relatively straightforward.

Install MySQL Server On The Raspberry Pi

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

This will take several minutes to complete. It should ask you to set a root password – do so and take note, you’ll need it later.

Optimize MySQL For Raspberry Pi

The Pi only has 512mb of RAM, so we want to configure MySQL to reflect that. Luckily, there is a default configuration file for systems with limited resources. Backup the existing conf file in case you need it later, and then copy over my-small.cnf:

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

Then edit the my.cnf file to adjust the query cache size. Add the following lines:

query_cache_limit       = 1M
query_cache_size        = 16M

Restart MySQL:

sudo service mysql restart

Set MySQL Character Encoding

We need to set our MySQL character encoding. Importing SEO, PPC and web analytics data means that we’re likely to encounter strings with foreign characters in them. To ensure MySQL can handle them properly, edit the configuration file (/etc/mysql/my.cnf) again and add the following line:
default-character-set=utf8mb4

Restart MySQL:
sudo service mysql restart

Create Our MySQL Database And User

Login as root:
mysql -u root -p

Create the database. I’m calling it ‘reporting’:
CREATE DATABASE reporting

Create a user. In this example I’m using ‘reporting’. Modify as you like.
CREATE USER 'reporting'@'%' IDENTIFIED BY '1Nice2securE3pAssword';

Note the @’%’ – this is granting the reporting user remote access to MySQL. This is important when we come to plug Excel into the database.

Grant privileges read/write/destroy privileges to all tables in the reporting database to our user:
GRANT ALL PRIVILEGES ON reporting.* TO 'reporting'@'%' IDENTIFIED BY '1Nice2securE3pAssword';

Test Your Connection

From the commandline on the Pi:
mysql -u reporting reporting -p

Remotely:
mysql -u reporting -h IPForPi -p

We haven’t set up any tables yet because we’re going to do that in a future post using Python and SQLAlchemy. So for now, we’re done!