MySQL in Grafana: time series and table queries

MySQL goes on app01, Grafana stays on graf01. Commands say which VM they run on. The database is a small online shop selling block-print kurtas and blue pottery mugs, with about a week of orders and one new order every minute.

1Install MySQL 8 on app01

Run this on app01:

sudo apt-get update
sudo apt-get install -y mysql-server
mysqld --version
systemctl is-active mysql

On Ubuntu 26.04 you should see:

/usr/sbin/mysqld  Ver 8.4.11-0ubuntu0.26.04.1 for Linux on x86_64 ((Ubuntu))
active

Ubuntu 26.04 ships MySQL 8.4 LTS, and 24.04 ships MySQL 8.0 (its line reads Ver 8.0.46-0ubuntu0.24.04.4 or a later 8.0 release). Every command in this lesson works on both. On Ubuntu the MySQL root account uses socket authentication, so sudo mysql logs you in as root without a password.

✅ Checkpoint: The version check prints an 8.x version and the mysql service is active on app01.

2Create shopdb with a week of orders

Paste this whole block on app01:

sudo mysql <<'SQL'
CREATE DATABASE IF NOT EXISTS shopdb;
USE shopdb;

CREATE TABLE products (
  id        INT AUTO_INCREMENT PRIMARY KEY,
  name      VARCHAR(60)  NOT NULL,
  category  VARCHAR(30)  NOT NULL,
  price     DECIMAL(10,2) NOT NULL
);

CREATE TABLE orders (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  created_at  DATETIME NOT NULL,
  product_id  INT NOT NULL,
  city        VARCHAR(30) NOT NULL,
  qty         INT NOT NULL,
  amount      DECIMAL(10,2) NOT NULL,
  status      ENUM('paid','shipped','cancelled') NOT NULL DEFAULT 'paid',
  INDEX idx_created_at (created_at),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

INSERT INTO products (name, category, price) VALUES
  ('Block print kurta', 'Clothing', 899.00),
  ('Blue pottery mug',  'Home',     349.00),
  ('Leather mojari',    'Footwear', 1299.00),
  ('Bandhani dupatta',  'Clothing', 649.00),
  ('Brass diya set',    'Home',     499.00);

-- About 7 days of history: one order every 10 minutes (1000 rows), times in UTC
INSERT INTO orders (created_at, product_id, city, qty, amount, status)
WITH RECURSIVE t(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM t WHERE n < 1000)
SELECT UTC_TIMESTAMP() - INTERVAL n*10 MINUTE,
       1 + FLOOR(RAND()*5),
       ELT(1 + FLOOR(RAND()*4), 'Jaipur', 'Delhi', 'Mumbai', 'Pune'),
       1 + FLOOR(RAND()*3),
       0,
       ELT(1 + FLOOR(RAND()*10), 'paid','paid','paid','paid','paid','shipped','shipped','shipped','shipped','cancelled')
FROM t;

UPDATE orders o JOIN products p ON p.id = o.product_id SET o.amount = p.price * o.qty;
SQL
sudo mysql shopdb -e "SELECT COUNT(*) FROM orders; SELECT status, COUNT(*) FROM orders GROUP BY status;"

You should see (your status counts will differ):

COUNT(*)
1000
status	COUNT(*)
cancelled	86
shipped	412
paid	502

Your status counts differ because RAND() picks them, but the total is always 1000. Every created_at is written with UTC_TIMESTAMP(), so the table holds UTC times; keep that in mind for the Break It section.

✅ Checkpoint: shopdb.orders has 1000 rows on app01, with paid, shipped and cancelled statuses.

3Add one new order every minute with the event scheduler

Run this on app01:

sudo mysql shopdb <<'SQL'
CREATE EVENT IF NOT EXISTS ev_new_order
  ON SCHEDULE EVERY 1 MINUTE
  DO
    INSERT INTO orders (created_at, product_id, city, qty, amount, status)
    SELECT UTC_TIMESTAMP(), p.id, ELT(1 + FLOOR(RAND()*4), 'Jaipur','Delhi','Mumbai','Pune'), 1, p.price, 'paid'
    FROM products p ORDER BY RAND() LIMIT 1;
SQL
sleep 65
sudo mysql shopdb -e "SELECT @@event_scheduler; SELECT id, created_at, city, amount FROM orders ORDER BY id DESC LIMIT 2;"

You should see (your IDs, times, cities and amounts will differ):

@@event_scheduler
ON
id	created_at	city	amount
1002	2026-11-08 06:42:44	Mumbai	649.00
1001	2026-11-08 06:41:44	Pune	349.00

The event scheduler is on by default in MySQL 8, so the event starts firing at once. Your shop now has live data, and a “Last 15 minutes” graph keeps moving.

✅ Checkpoint: Two new rows appear one minute apart and @@event_scheduler shows ON.

4Create the read-only user grafana_ro

Run this on app01 (choose your own password):

sudo mysql <<'SQL'
CREATE USER 'grafana_ro'@'192.168.56.70' IDENTIFIED BY 'Ro-Pass-2026';
GRANT SELECT ON shopdb.* TO 'grafana_ro'@'192.168.56.70';
SHOW GRANTS FOR 'grafana_ro'@'192.168.56.70';
SQL

You should see:

Grants for grafana_ro@192.168.56.70
GRANT USAGE ON *.* TO `grafana_ro`@`192.168.56.70`
GRANT SELECT ON `shopdb`.* TO `grafana_ro`@`192.168.56.70`

The host part @192.168.56.70 means this account can only log in from graf01. USAGE is MySQL’s word for “no global privileges”, and SELECT on shopdb.* is the only real right it has.

✅ Checkpoint: SHOW GRANTS lists USAGE on *.* and SELECT on shopdb.* for grafana_ro@192.168.56.70.

5Let MySQL listen on the host-only network, for graf01 only

Ubuntu’s MySQL listens on 127.0.0.1 only (bind-address = 127.0.0.1 in /etc/mysql/mysql.conf.d/mysqld.cnf). Change it to app01’s host-only address and open port 3306 to graf01 alone.

Run this on app01:

sudo sed -i 's/^bind-address.*/bind-address = 192.168.56.71/' /etc/mysql/mysql.conf.d/mysqld.cnf
sudo systemctl restart mysql
sudo ss -ltnp | grep 3306
sudo ufw allow from 192.168.56.70 to any port 3306 proto tcp
sudo ufw status | grep 3306

You should see (your PID will differ):

LISTEN 0      151    192.168.56.71:3306       0.0.0.0:*    users:(("mysqld",pid=5120,fd=23))
3306/tcp                   ALLOW       192.168.56.70

Then test from graf01:

sudo apt-get install -y mysql-client
mysql -h 192.168.56.71 -u grafana_ro -p shopdb -e "SELECT COUNT(*) FROM orders;"

Enter password:
COUNT(*)
1003

The login works from graf01 with SELECT rights. sudo mysql on app01 still works too, because it uses the local socket, not the network address.

✅ Checkpoint: mysql from graf01 as grafana_ro returns the order count, and ss on app01 shows mysqld on 192.168.56.71:3306.

6Add the MySQL data source in Grafana

In Grafana open Connections > Data sources > Add new data source > MySQL and fill in:

Write the host as host:port without mysql:// or http://. Click Save & test; you should see Database Connection OK. Setting the session timezone to UTC matches how the table stores times, and the Break It section shows what happens without it.

✅ Checkpoint: Save & test on “MySQL app01” shows Database Connection OK.

7Orders per hour as a time series

Create a new dashboard Shop sales in folder SaatVedha Lab. Add a panel, choose MySQL app01, switch the editor from Builder to Code, set Format to Time series and paste:

SELECT
  $__timeGroupAlias(created_at, 1h),
  COUNT(*)     AS orders,
  SUM(amount)  AS revenue
FROM orders
WHERE $__timeFilter(created_at)
  AND status <> 'cancelled'
GROUP BY 1
ORDER BY 1

Set the time range to Last 6 hours and press Ctrl+Enter (Cmd+Return on a Mac). You get two lines, orders and revenue. Move revenue to the right axis with an override (Fields with name: revenue, Axis > Placement: Right, Unit: Currency > Indian Rupee (₹)) so the small order counts aren’t flattened.

Title it Orders and revenue per hour.

The same query as Grafana sends it, run by hand on graf01 (macros expanded for the last 6 hours):

F=$(date -u -d '-6 hours' +%s); T=$(date -u +%s)
mysql -h 192.168.56.71 -u grafana_ro -p shopdb -e "
SELECT UNIX_TIMESTAMP(created_at) DIV 3600 * 3600 AS \"time\", COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders WHERE created_at BETWEEN FROM_UNIXTIME($F) AND FROM_UNIXTIME($T) AND status <> 'cancelled'
GROUP BY 1 ORDER BY 1;"

The output looks like this, with your own times and numbers, because RAND() made the orders:

time	orders	revenue
1794099600	4	5794.00
1794103200	5	9188.00
1794106800	5	6842.00
1794110400	6	14134.00
1794114000	6	9588.00
1794117600	2	1396.00

The time column holds epoch seconds rounded to the hour, which is what the macro produces. Open the panel’s Query inspector > Query tab and you’ll see Grafana’s own expanded SQL looks the same.

✅ Checkpoint: The panel shows orders and revenue per hour for the last 6 hours, with revenue on a right-hand rupee axis.

8One line per city, using $__interval

Add another time series panel. A text column in a time series query becomes a label, so this draws one line per city:

SELECT
  $__timeGroupAlias(created_at, $__interval),
  city,
  COUNT(*) AS orders
FROM orders
WHERE $__timeFilter(created_at)
GROUP BY 1, 2
ORDER BY 1

In the panel’s Query options set Min interval to 10m, because new rows arrive every minute and history every 10 minutes; smaller buckets would be full of zeros and ones. Zoom out to 7 days and the buckets grow by themselves through $__interval.

✅ Checkpoint: The panel shows four lines named Jaipur, Delhi, Mumbai and Pune, and the bucket size changes when you change the time range.

9Panels without a time axis: table, bar chart, pie chart

Add three panels with Format: Table. They still filter with $__timeFilter so they follow the time picker, but they don’t return a time column.

For the pie chart set Value options > Show to All values, so each row becomes a slice. For the table, add a Cell type: Colored text override on status with value mappings paid = green, shipped = blue, cancelled = red.

Check the bar chart numbers on graf01 (last 6 hours):

mysql -h 192.168.56.71 -u grafana_ro -p shopdb -e "
SELECT p.category, SUM(o.amount) AS revenue FROM orders o JOIN products p ON p.id=o.product_id
WHERE o.created_at BETWEEN FROM_UNIXTIME($F) AND FROM_UNIXTIME($T) AND o.status<>'cancelled'
GROUP BY p.category ORDER BY revenue DESC;"

The output looks like this (your amounts will differ):

category	revenue
Footwear	22083.00
Clothing	14432.00
Home	10427.00

✅ Checkpoint: Shop sales has five panels: two time series, a bar chart, a pie chart and a coloured latest-orders table.

10Try the SQL builder and a city variable

Add a Query variable city on MySQL app01 with the query SELECT DISTINCT city FROM orders ORDER BY city, Multi-value and Include All on. In the per-city panel add AND city IN ($city) to the WHERE clause; for SQL data sources Grafana formats a multi-value selection as a quoted, comma-separated list, so IN works directly.

Then open a new panel and stay in Builder mode: pick table orders, column amount with aggregation SUM, filter status != cancelled and group by city. Click Code to see the SQL the builder wrote. The builder is fine for quick questions, while Code is better for joins and macros.

✅ Checkpoint: Picking Jaipur and Pune in the City drop-down leaves only those two lines in the per-city panel.