FlashPanel

Access MySQL remotely ​

You may need to connect to MySQL/MariaDB on your server from your own computer (with HeidiSQL, TablePlus…), or use that server as a remote database shared by several applications.

Opening database port 3306 is the fastest way to connect to a remote database, but it is also the least secure: port 3306 becomes public, so anyone can reach it and try to guess passwords. If you use a weak password, your database can easily be attacked.

If you still choose this method:

  1. Enable Remote Access for the database user (see Database)
  2. Open port 3306 in the firewall. Allow only the IP of the machine that needs to connect instead of every IP

With an SSH tunnel, your database tool connects to the server over SSH (the SSH port is already open), then connects to MySQL at 127.0.0.1 from inside the server. Port 3306 does not need to be open to the Internet, and the database user does not need Remote Access.

Preparation:

  1. Generate an SSH Key on your computer, or click Generate SSH Key when adding the SSH Key to the server in step 2 and download the private key file (OpenSSH Key for ssh.exe/TablePlus, PuTTY Key for plink.exe)
  2. Add the SSH Key to the server. Remember the Linux user you choose when adding the key (for example root); it is the SSH Username in the steps below
  3. Have the name and password of the database user ready

Instructions for using HeidiSQL (Windows) ​

This guide uses ssh.exe (OpenSSH, built into Windows 10/11) with a private key in OpenSSH format, for example an id_ed25519 or id_rsa file created with ssh-keygen (see Generate an SSH Key on Windows).

  1. Open HeidiSQL, click New to create a new session and give it a name
  2. On the Settings tab:
    • Network type: select MariaDB or MySQL (SSH tunnel)
    • Hostname / IP: enter 127.0.0.1
    • User: the database username
    • Password: the database user's password
    • Port: 3306
  3. Switch to the SSH tunnel tab:
    • Check Use SSH tunnel
    • SSH executable: select ssh.exe
    • SSH host + port: enter the server IP and SSH port (22 by default)
    • Username: the Linux user you chose when adding the SSH Key to the server
    • Private key file: select the OpenSSH private key that matches the SSH Key you added, for example C:\Users\<name>\.ssh\id_ed25519
    • Local port: keep the default value (for example 3307), as long as no other program uses that port on your computer
  4. Click Save, then click Open to connect

TIP

If you have a PuTTY key (.ppk), select plink.exe in SSH executable instead of ssh.exe. Do not use a .ppk file with ssh.exe or the other way around. You can convert between the two formats with PuTTYgen (Load the key file, then Save private key or Conversions > Export OpenSSH key).

Instructions for using TablePlus ​

  1. Click Create a new connection, select MySQL or MariaDB, then click Create
  2. Fill in the database details:
    • Name: a memorable name for the connection
    • Tag: select production (optional, helps you recognize connections to a live server)
    • Host: 127.0.0.1
    • Port: 3306
    • User and Password: the database user's name and password
  3. Click Over SSH to open the SSH settings, then fill in:
    • Server: the server IP
    • Port: the SSH port (22 by default)
    • User: the Linux user you chose when adding the SSH Key to the server
    • Check Use SSH key and select the matching private key file
  4. Click Test to check the connection, then click Connect