Ravindra BagaleCourses & study guides

4. Connecting to Databases: SQL Server, MySQL, DirectQuery and Live Connection

4.5 Connecting to MySQL

By Last updated:

Many start-ups and web apps store orders in MySQL. Power BI Desktop has a built-in MySQL connector, pan it needs a driver.

Steps in Power BI

  1. Install the driver first. Download and install MySQL Connector/NET (Oracle's ADO.NET driver) from the official MySQL website. Use a version that the current Microsoft documentation lists as supported for the MySQL connector. Restart Power BI Desktop after installing.
  2. Home › Get data › More… › Database › MySQL database › Connect.
  3. Server: qc-mysql.example.com:3306 (or localhost:3306); Database: quickcommerce.
  4. Optional Advanced options: Command timeout, SQL statement, Include relationship columns, Navigate using full hierarchy.
  5. Credentials: choose Database, enter the MySQL user (e.g. pbi_reader) and password › Connect.
  6. Select tables in the Navigator › Transform Data.
  7. For scheduled refresh through a gateway, install the same driver on the gateway machine too.

MySQL-style SQL for the same Maharashtra filter (note the backticks and LIMIT):

SELECT o.order_id, o.order_datetime, s.city, s.area, o.amount, o.delivery_time_mins
FROM   `orders` o
JOIN   `dark_store` s ON s.store_id = o.store_id
WHERE  s.city IN ('Pune','Solapur','Nashik','Sambhaji Nagar','Kolhapur','Nagpur')
ORDER BY o.order_datetime DESC
LIMIT 1000;
Error message (typical) Likely cause Fix
This connector requires one or more additional components to be installed MySQL Connector/NET is missing Install the driver and restart Desktop
Unable to connect to any of the specified MySQL hosts Wrong server/port, server down, firewall blocks port 3306 Check server:port, ask the admin to open the port or allow your IP
Access denied for user 'pbi_reader'@'…' Wrong password, or the user is not allowed from your machine/host Reset the password, ask the admin to grant access from your host
SSL/TLS or certificate errors Server requires/does not support encryption Ask the admin about SSL settings
Works in Desktop, fails in the Service Driver not installed on the gateway, or credentials not set on the gateway connection Install the driver on the gateway and configure the connection

Import vs DirectQuery for MySQL

Simple bhashet sangaycha tar, check the current Microsoft documentation for which connectivity modes the MySQL connector supports. Most MySQL reports use Import mode with scheduled refresh.

Practice task

Install MySQL Community Server locally, create tables orders and dark_store with ten rows each (Pune and Solapur stores), connect from Power BI, and deliberately type the wrong port to see the error message.

Ravindra Bagale's Tip

The error that comes up most often with MySQL is a missing or wrong-version driver. Install the MySQL Connector/NET version Microsoft's documentation asks for, restart Power BI Desktop, and use server:port only if the port is not the default 3306. A driver missing on the gateway machine causes the same error in scheduled refresh. It's very simple – just make it a habit.