How to build dashboards on MySQL data

To build dashboards on MySQL, connect a BI tool over the MySQL protocol (TCP port 3306) with a dedicated read-only user, require TLS, and point dashboard queries at a source that will not compete with your application’s writes: the primary if traffic is modest, or a read replica if reporting is heavy. The detail most teams miss is that MySQL is usually a transactional (OLTP) database, not a warehouse. With the default InnoDB storage engine, a plain SELECT does not block writers, so the risk is rarely locking. It is resource contention and long-running reporting transactions that hold an old read view open and make the database work harder. The fix is a read replica or curated summary tables, not a pile of query hints.

This guide is for engineers, analysts, and operators who run an app on MySQL, MariaDB, RDS, Aurora MySQL, Cloud SQL, or PlanetScale and want shareable dashboards on top of it without standing up a full analytics stack first. Below: why MySQL is different from a warehouse, where dashboard queries should land, how to connect a BI tool safely, how to create a least-privilege user, how to enforce per-user row access when MySQL has no built-in row-level security, and when MySQL is the wrong place to build a dashboard.

Why MySQL changes how you build dashboards

Most BI advice assumes an always-on analytics warehouse like Snowflake or BigQuery, or a lakehouse like Databricks. MySQL is neither. It is usually the transactional database behind a running application, tuned for many small reads and writes, not the large scans a dashboard generates. Three things follow from that.

InnoDB reads do not block writers. With InnoDB, the default storage engine since MySQL 5.5, a plain SELECT is a consistent nonlocking read. It reads a multi-version snapshot instead of taking shared locks, so readers and writers do not block each other the way they can on a lock-based system. That is good news: you are less likely to freeze your app with a slow dashboard query. The catch is subtler, and it is below.

Long reporting transactions cost you elsewhere. Because InnoDB serves consistent reads from old row versions, a long-running or forgotten reporting transaction pins a read view and forces the engine to keep undo log history around so it can still show that snapshot. That growing history list makes reads slower and consumes space until the transaction ends. A dashboard tool that opens a transaction and holds it, or a runaway analytical query, can degrade the whole database without ever taking a lock.

Deployment shape varies. MySQL runs self-hosted, on RDS for MySQL, on Amazon Aurora MySQL, on Google Cloud SQL, on Azure Database for MySQL, or on PlanetScale (built on Vitess), and MariaDB is a common drop-in fork. That changes networking, authentication, and how you add a read replica. A cloud BI tool reaching a self-hosted server needs a network path in; a managed service needs firewall or authorized-network rules.

The takeaway: decide where dashboard queries land, connect a least-privilege identity over TLS, and keep heavy reporting off the same resources your app depends on. The rest of this guide is how.

Where should dashboard queries point?

This is the decision that determines whether BI on MySQL stays safe as usage grows. You have three realistic targets, and the right one depends on query volume and how fresh the data must be.

For most teams the honest answer is: start on the primary, move reporting to a read replica when dashboard load grows. Managed MySQL makes replicas easy. Amazon RDS read replicas and Aurora replicas let you send read-only BI traffic to a separate endpoint so analytical scans never touch the writer, and PlanetScale exposes replica connections for the same reason. The tradeoff is replication lag: an async replica is usually seconds behind, which is fine for a revenue dashboard and wrong for a “did my write just land” check.

Whatever you choose, keep dashboard queries out of long-lived transactions. Run them with autocommit on, or make sure the BI connection closes its read transaction promptly, so you do not pin an old snapshot and grow the history list.

How to connect a BI tool to MySQL

Connecting comes down to three things: the driver and endpoint, authentication, and a network path.

Driver and endpoint

MySQL speaks its own client/server protocol, and BI tools reach it through the MySQL Connector/J JDBC driver, an ODBC driver, or a native connector. The default port is TCP 3306. A BI tool needs the host, port, database (schema) name, and credentials. Always require TLS so credentials and result sets are encrypted in transit; managed providers support it and self-hosted MySQL can be configured for it.

Authentication

MySQL 8.0 changed the default authentication plugin to caching_sha2_password, which is more secure than the older mysql_native_password but requires a driver that supports it and a TLS connection (or an RSA key exchange) to send the password safely. If an older BI connector fails to authenticate against MySQL 8, this mismatch is usually why. On managed services you can also use provider identity: RDS and Aurora support IAM database authentication, which issues short-lived tokens instead of a static password. For a production BI connection, use a dedicated identity rather than a person’s account, so access can be rotated and audited independently.

Network path

If MySQL is self-hosted and the BI tool is in the cloud, the tool cannot reach it by default. Use one of: the tool running inside your network, a VPN or SSH tunnel, or an allowlist of the tool’s IP ranges. On RDS, Aurora, and Cloud SQL, keep the instance private and use security groups, authorized networks, or a private endpoint that permits only your BI tool’s addresses. Never expose port 3306 to the public internet. Our guide on safely connecting a BI tool to your production database covers the general pattern: replicas, read-only roles, and network isolation.

Create a least-privilege read-only user

Do not connect BI as root or any account with write access. Create a dedicated user, require TLS, and grant read only on the schema dashboards actually need.

-- 1. Create a dedicated BI user that must connect over TLS
CREATE USER 'bi_reporting'@'%'
  IDENTIFIED BY 'use-a-strong-secret'
  REQUIRE SSL;

-- 2. Grant read only, scoped to the reporting schema (not *.*)
GRANT SELECT ON analytics.* TO 'bi_reporting'@'%';

-- Prefer a curated set of views over raw tables:
-- GRANT SELECT ON analytics.v_daily_revenue TO 'bi_reporting'@'%';

FLUSH PRIVILEGES;

Granting SELECT on a purpose-built reporting schema of views, rather than a global GRANT SELECT ON *.*, does two things. It limits what a leaked BI credential can read, and it gives you a stable contract: you can refactor base tables without breaking dashboards, because the views absorb the change. This pairs well with defining each metric once in a SQL view, so every dashboard reads the same definition of “revenue” or “active account.” Scope the host part of the account ('bi_reporting'@'10.0.%' rather than @'%') when you know where the BI tool connects from.

Live queries or extracts on a transactional source

On a warehouse, live queries are usually the right default. On MySQL the answer depends on how busy the primary is and how fresh the numbers must be.

Query live when dashboard traffic is modest and you are reading from a replica or a lightly loaded primary. Live queries mean no copy to keep in sync, no staleness window beyond replica lag, and permissions enforced by the database on every read.

Extract or pre-aggregate when dashboards are high-traffic, the queries scan large history, or the primary is already busy serving the app. A scheduled rollup into summary tables, or a pull into a small warehouse, takes the analytical load off the transactional system entirely. The cost is a freshness lag you accept and monitor.

A practical middle path: point live tiles at a read replica for operational dashboards, and pre-compute expensive historical rollups into summary tables refreshed on a schedule. Our dashboard refresh strategies guide walks through live queries, scheduled refreshes, and cached extracts, and the performance playbook for slow BI dashboards covers the query-side fixes when a single tile is slow.

How to enforce per-user row access in MySQL

MySQL has no built-in row-level security policy feature the way SQL Server and PostgreSQL do. There is no CREATE POLICY that the engine transparently applies to every query. That means per-viewer row filtering has to live somewhere else, and you have two practical options.

Filter with views. Create a view that restricts rows based on session context, for example a view that joins to a mapping table on CURRENT_USER() or a session variable your connection sets, and grant BI access only to the view. This keeps the filter in the database, but it depends on each dashboard connection carrying the right identity, which is awkward when a BI tool uses one shared service account for everyone.

Filter in the BI layer. Most teams enforce who-sees-what in the BI tool instead: the tool authenticates the human, then scopes their queries to the rows they are allowed to see. This is usually the more practical model on MySQL precisely because the database will not do it for you. If you are weighing where to enforce access, our comparison of BI tools and row-level security covers the tradeoffs between database-enforced and tool-enforced permissions. Either way, keep the underlying BI user read-only so a filtering mistake cannot become a data-loss incident.

Common mistakes

• Connecting as root or an admin user. A read-only account scoped to a reporting schema limits blast radius and prevents an accidental write.
• Holding a long reporting transaction open. InnoDB keeps undo history alive for old read views; a forgotten transaction bloats the history list and slows every read. Run dashboards with autocommit and short-lived reads.
• Running heavy BI directly on a busy primary. InnoDB will not block your writers, but big scans still consume CPU, memory, and IO the app needs. Offload to a read replica.
• Treating replica lag as zero. Async replicas are seconds behind. Fine for trends, wrong for “did this row just change.”
• Querying raw transactional tables. Point dashboards at curated views or summary tables so a schema change does not break every tile.
• Exposing 3306 to the internet or skipping TLS. Keep the port private, allowlist the BI tool, and require encrypted connections.

When not to build dashboards on MySQL directly

MySQL is an excellent transactional database and a poor fit for a few analytical jobs. Use this as a filter before you build.

• Very large historical analytics. Scanning years of transactional rows on every dashboard load belongs in a columnar warehouse, not a row-store OLTP engine. Pipe it out.
• Blending many sources. If a dashboard needs MySQL plus Stripe plus product analytics, a warehouse that already consolidates them is a better base than joining across systems at query time.
• A primary that is already at capacity. If the app database is CPU- or IO-bound, adding BI reads makes it worse. Use a replica or a copy.
• High-concurrency external embedding. Serving dashboards to thousands of customers is a different problem than internal BI, and a busy transactional primary is the wrong place to absorb that load.

If you are hitting these limits, our guide on when to add a data warehouse walks through the signals that your database has outgrown double duty.

Tool options for MySQL dashboards

Because MySQL speaks a standard protocol, most BI tools can connect. The right one depends on who will use the dashboards.

• Metabase is a common open-source choice for internal MySQL dashboards, with a friendly query builder and a low setup bar.
• Looker Studio connects to MySQL for free and suits simple, shareable reports, though it can get slow on large live queries.
• Tableau and other incumbents connect over the standard driver and suit teams with dedicated analysts building curated workbooks.
• Basedash fits teams that want non-technical people to explore MySQL data and ask follow-up questions in plain English, querying a read-only connection directly rather than maintaining a separate extract. See Basedash for how that works across MySQL and other databases.

For a wider rundown of options and their tradeoffs, see our list of BI dashboarding tools for MySQL.

FAQ

How do I connect a BI tool to MySQL?

Use the MySQL Connector/J JDBC driver, an ODBC driver, or your BI tool’s native MySQL connector to reach the server over TCP port 3306. Supply the host, port, database name, and credentials, and require TLS so the connection is encrypted. Create a dedicated read-only user scoped to a reporting schema rather than connecting as root, and make sure the network path is open only to your BI tool, through a private endpoint, allowlist, or SSH tunnel.

Will running dashboards slow down my MySQL database?

It can, but not usually by locking. With the default InnoDB engine, a plain SELECT is a consistent nonlocking read, so dashboard queries do not block your app’s writes. The real risks are resource contention (heavy scans competing for CPU, memory, and IO) and long-running reporting transactions that pin an old read view and grow InnoDB’s undo history. Reduce both by reading from a read replica, keeping reporting transactions short, and pre-aggregating heavy rollups into summary tables.

Should I use a read replica for MySQL reporting?

Yes, once dashboard traffic grows. A read replica gives BI its own read-only endpoint so analytical scans never touch the writer that serves your application. RDS, Aurora MySQL, Cloud SQL, and PlanetScale all support replicas. The one thing to plan for is replication lag: an async replica is typically a few seconds behind the primary, which is fine for trend dashboards but wrong for confirming a write that just happened.

Does MySQL support row-level security for dashboards?

Not natively. Unlike SQL Server and PostgreSQL, MySQL has no row-level security policy feature that the engine applies to every query automatically. You enforce per-user row access either with views that filter on session context (CURRENT_USER() or a session variable) or, more commonly, in the BI tool that authenticates each viewer and scopes their queries. Keep the underlying MySQL user read-only regardless of which approach you choose.

Can I build dashboards on Aurora MySQL, RDS, or PlanetScale the same way?

Mostly yes. They all speak the MySQL protocol on port 3306, so the connection, driver, and read-only user steps are the same. The differences are operational: managed services give you a separate reader endpoint for replicas, support IAM or provider authentication, and use security groups or authorized networks instead of firewall config. Point BI at the reader endpoint where one exists so reporting stays off the writer.