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!
Own your marketing data & simplify your tech stack.
Have you read?
Generative AI is transforming the way that marketers plan and assemble content for their Paid Ads. As big platforms like Google, Meta and TikTok increasingly build the tools needed to...
In a surprising move that has sparked heated debate, Mark Zuckerberg announced on his Instagram that Meta will be reducing its levels of censorship and in particular fact-checking on its...
It is no understatement to say that the impact of AI in marketing is huge right now. Here we take a look at some of the top uses cases that...