Last year I wrote about the idea of a personal dashboard and earlier this year I described my 2020 goals and how I’d go about measuring my progress. The past two days I was able to combine the two concepts and created a simple Grafana dashboard to measure my progress against the blogging goal. As with most tasks, the most difficult part was getting started and the actual exercise took a few hours. While it’s still fresh in my mind I want to document the step by step process in order to both provide a perspective into how I work while also giving others a guide to setting up their own.
- Decide on the Grafana backend. I already committed to Grafana as the visualization layer but needed to decide on the actual statistics backend. This isn’t that important of a decision and I can change it any time so I just chose MySQL. I have a personal instance of MySQL floating around (who doesn’t?), am not going to be dumping much data into it, and am comfortable writing SQL queries.
- Set up MySQL. I already had a MySQL instance up so this required setting up a new database and the appropriate users. Note that I needed to create a master user for adding the data as well as a read-only user that Grafana uses for querying.
- Determine the MySQL schema. Since I know both the unit I want to analyze (blog post) and the metric I care about the schema practically writes itself. At first I chose date, title, and a few of the fields but tweaked this after the subsequent step.
Get the data into MySQL. Lucky for me I already had an old blog analysis script that took my blog, cleaned up all the posts, and dumped the contents to a CSV. I had to make a few tweaks to the script to allow me to specify the exact fields to write out but after that was done I had a CSV file that could be imported to MySQL. I had a few options for how to get this data into MySQL including modifying the above script to connect to MySQL but instead decided that using a manual
LOAD DATAcommand would suffice for now. Out of all the steps this one took the longest time - especially figuring out the right syntax for the
Connect Grafana to MySQL. Grafana worked right out of the box after installation with
apt getbut I had to create a read-only user as Grafana refused to use a user that had write access.
- Create a few dashboards. Now came the fun part. I started by trying to use Grafana’s query builder took but quickly resorted to just writing the SQL manually. The nuance here was having to convert the MySQL date field into a UNIX timestamp that worked with Grafana.
Clean up the deployment. By default, Grafana runs on port 3000 and I wanted to serve it on a standard port behind HTTPS. This required installing nginx (via
apt get), using it to create a simple reverse proxy, and then using Let’s Encrypt to add SSL. At this point I also gave this a unique Grafana subdomain.
The solution is far from perfect - for example if the instance hosting Grafana goes down I’d have to repeat the bulk of the above steps. At the same time, that’s a risk I’m currently willing to take, especially since the process itself was so quick and I’m manually saving down the JSON behind the dashboard.