Remote access to the cPanel hosting database Print

  • cpanel, adatbázis, ssh
  • 0

Connections to the MySQL or MariaDB service on port 3306 are not encrypted by default, so the password and the data could be intercepted. That is why the databases on our cPanel servers cannot be reached directly from outside, only through an encrypted SSH tunnel: port 3306 is closed, and the Databases group of cPanel does not even have a Remote Database Access item where you could allow an IP address. The SSH tunnel builds an encrypted connection from your computer to the server, and to the database the connection comes locally, from the server itself, so there is nothing to allow separately.

This is an advanced topic and assumes some experience with SSH and SQL client programs. If your plan does not include SSH, you can manage the database in your browser in cPanel’s phpMyAdmin, and you can also export and re-import the whole database from there; the article on importing and exporting databases covers this.

Prerequisites

Linux, macOS, WSL and Windows PowerShell

Open a terminal, then use the following command to set up the tunnel to the cPanel hosting account:

ssh -p 4231 -N -L 3333:localhost:3306 user@server

Where:

  • -p 4231: the SSH server port to which you need to connect.
  • -N: the client must not run any commands or shells; it must only maintain the tunnel.
  • -L 3333:localhost:3306: traffic arriving at port 3333 on the local machine is forwarded to the address localhost:3306 as seen from the server (where ‘localhost’ refers to the remote server).
  • user@servere: the cPanel username and the server name.

The built-in OpenSSH client in Windows 10 and 11 accepts the same command in PowerShell. If the connection is terminated due to inactivity, add the -o ServerAliveInterval=60 switch.

Windows, PuTTY

  1. In PuTTY, enter the server name and the SSH port, and in the Connection > SSH > Auth > Credentials section, enter the private key in PPK format.
  2. On the Connection > SSH > Tunnels page, enter 3333 in the Source port field and localhost:3306 in the Destination field, then click the Add button.
  3. Connect, and the tunnel will remain active while the window is open.

Configuring the SQL client

While the tunnel is active, enter the following in your SQL client (e.g. HeidiSQL, DBeaver, MySQL Workbench):

  • Server: 127.0.0.1 (do not enter localhost, as some clients interpret this as a local socket and will not use the tunnel),
  • Port: 3333,
  • Username and password: the database user’s full name and password.

The programs listed also have their own SSH tunnel functions (HeidiSQL: ‘MySQL (SSH tunnel)’, MySQL Workbench: ‘Standard TCP/IP over SSH’, DBeaver: SSH tab). In this case, no separate command is required: in the SSH section, enter the server name, port, cPanel username and key; in the database section, enter the address 127.0.0.1 and port 3306.

Common errors

  • Permission denied (public key): the key is not authorised in the cPanel SSH Access interface, or you are not using the correct private key.
  • Address already in use: port 3333 is already in use on your machine; please choose another one (for example, 3334:localhost:3306).
  • Access denied for user: the ‘cPanel’ prefix is missing from the start of the username, or the user is not assigned to the database.
  • SSL is required, but the server does not support it: from version 11.4, the MariaDB command-line client requests an encrypted connection by default. Inside the tunnel, the traffic is already encrypted, so add the --skip-ssl option: mysql -h 127.0.0.1 -P 3333 -u felhasznalo_webshop -p --skip-ssl.
  • Can’t connect to local server through socket: you entered localhost as the host, so the client looks for a local socket instead of the tunnel; enter 127.0.0.1.

Was this answer helpful?

« Back