如何实现Power BI连接仅允许本地访问的远程MySQL数据库？

Hey there! Let's tackle your two Power BI + MySQL connection scenarios step by step—super doable once you know the tricks.

This one's straightforward, but you need to make sure you have the right driver first:

• First, install the MySQL ODBC Driver (match the 32/64-bit version to your Power BI installation). Power BI relies on this driver to talk to MySQL. After installing, restart Power BI to pick up the driver.
• Open Power BI Desktop, click Get Data > More, search for "MySQL", select MySQL Database, and hit Connect.
• In the connection window, enter localhost or 127.0.0.1 as the server, 3306 as the port (use your custom port if you changed MySQL's default), then type in your database name. Click OK.
• Choose Database authentication, enter your local MySQL username and password, then click Connect.
• Once the data preview loads, select the tables/views you need, then click Load (or Transform Data if you want to clean up the data first). You're all set!

Since Power BI doesn't have built-in SSH tunneling, we'll create a local "bridge" using an SSH tunnel tool—this makes Power BI think it's connecting to a local MySQL instance, while the traffic actually routes to the remote server. Here are two reliable methods:

Method 1: Use OpenSSH (Built into Windows 10/11) #

No extra software needed—perfect for modern Windows systems:

• Open Windows Terminal (CMD or PowerShell) and run this command (replace placeholders with your details):
  ssh -L 3307:127.0.0.1:3306 your_remote_ssh_username@remote_server_public_ip
  • Breakdown: 3307 is a free local port (pick any unused one), 127.0.0.1:3306 is the remote MySQL's local address/port, your_remote_ssh_username is your SSH login for the remote server, and remote_server_public_ip is the server's public IP.
• Enter your remote SSH password when prompted. Keep this terminal window open—closing it will drop the tunnel.
• Now go back to Power BI and follow the local connection steps, but use localhost as the server and 3307 as the port. Enter the remote MySQL's username and password (not the SSH ones) and connect. Power BI will route traffic through the tunnel to the remote database.

Method 2: Use PuTTY (For Older Windows Versions) #

If you don't have OpenSSH, PuTTY is a classic free tool:

• Open PuTTY, go to the Session tab, enter your remote server's public IP and SSH port (default is 22).
• Navigate to Connection > SSH > Tunnels in the left menu. In Source port, enter your local port (e.g., 3307). In Destination, enter 127.0.0.1:3306, then click Add.
• Go back to Session, save this configuration (so you don't have to re-enter it later), then click Open.
• Enter your remote SSH username and password to establish the tunnel. Keep the PuTTY window open.
• Connect Power BI using localhost:3307, with your remote MySQL credentials—same as the OpenSSH method.

Quick Notes to Avoid Headaches #

• Make sure your remote server's SSH port (usually 22) is open in its firewall, and your local IP is allowed to connect to it.
• Check if your chosen local port is unused with netstat -ano | findstr :3307 (replace 3307 with your port).
• If the remote MySQL is bound to an internal server IP (not 127.0.0.1), update the Destination in the tunnel command to that internal IP:port.
• For long-term tunnels, you can set up a Windows service to run the SSH command in the background (tools like NSSM work great for this).

内容的提问来源于stack exchange，提问作者Stefano Maglione