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?
SEO can make or break your eCommerce site. It’s one of many reasons why SEO is important for eCommerce. So, in this blog, we’ll cover the top 4 reasons why...
When it comes to running an eCommerce site, having a solid SEO strategy is crucial for success. However, it can be difficult to know where to begin or which factors...
Good SEO is a non-negotiable for eCommerce brands. Ensuring your site has been optimised, and has the right to rank well, will lead to better search engine rankings, increased traffic...