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
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:
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
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?
It’s been 4 years since Google first announced it will be removing third-party cookies from Chrome. Since then, we’ve seen multiple delays on the plan which has left marketers doubting...
The deadline to move to Google Analytics 4 is just around the corner. So, how can you prepare to make the change, with minimal impact on your marketing measurement? In...
Google Optimize is a core part of the CRO toolkit. But with the news that Google will soon be sunsetting it, what Google Optimize alternatives are available? We’ve put together...