Editing Remotely Connecting to a Namecheap Database

== Command line ==

Change `tunneluser`, `HOSTING_ACCOUNT_ADDRESS` and port numbers to the appropriate values. 

`5522:127.0.0.1:3306` equates to the translation between the local and remote database server addresses. The default local address is being used because it is not specified on the left side of the semicolon. Do not change anything on the right side of the semicolon.

<syntaxhighlight lang="shellscript">
$ ssh -f tunneluser@HOSTING_ACCOUNT_ADDRESS -p XXXX -L 5522:127.0.0.1:3306 -N 
</syntaxhighlight>

A prompt for a password will appear after entering the command. Enter the password for `tunneluser`. 

The database is now available at `127.0.0.1:5522`. 

== PHPStorm/DataGrip connections ==

The `ssh` command documented above must be run before using the remote connection in PHPStorm, DataGrip, or any other IDE.

Create a new database connection in PHPStorm/DataGrip as if creating a new local connection.

Enter properties used when creating the tunnel.

* '''Host:''' 127.0.0.1
* '''Port:''' 5522 (or whatever port was specified on the command line)
* '''Database''', '''User''', '''Password:''' Use connection properties for the database on the remote server.

== Connecting to the database from a Docker container hosted web app ==

Use the internal docker address for the database, e.g. `host.docker.internal`. This is paired with `localhost` on the local machine through the docker container configuration. 

All the other properties of the are the same as if connecting the the database on the command line. 

Make sure to use the database port number specified when establishing the tunnel. 

And make sure to use the schema on the remote database server, if it is different from the local development environment.

== DataGrip (legacy) ==

=== Make a new connection ===

<p class="alert alert-warning">You can do it this way, but if a tunnel has already been established via the command line, the port will already be in use and this method will fail. It is probably better to establish the tunnel on the command line, ignore the '''SSH''' option in the DataGrip connection dialog, and use the local port from the tunnel to connect to the remote database.</p>

* <kbd>&#8984;</kbd> + <kbd>;</kbd> to open the '''Data Sources and Drivers''' dialog, then <kbd>&#8984;</kbd> + <kbd>N</kbd> for a new connection.
** Or Select the '''Plus Sign''' icon at the top left of the app > '''Data Source''' > '''MariaDB'''.
** '''SSH/SSL''' tab
*** Check '''Use SSH tunnel'''
*** Click the '''three dots''' button to the far right of the '''Use SSH tunnel''' line to create a new tunnel (or select from the drop down if one has already been created for this Namecheap hosting account.)
**** Click the '''plus sign''' at the top left of the '''SSH Configurations''' dialog to add a new configuration.
**** '''Host:''' IP or default URL for the Namecheap hosting account.
**** '''Port:''' SSH port assigned to the Namecheap hosting account.
**** '''User name:''' Admin user name for the Namecheap account, i.e. the account that has ssh access.
**** '''Authentication type:''' Key pair
**** '''Private key file:''' ''(path to a key file downloaded from cPanel)''
***** '''cPanel''' > '''Security''' > '''SSH Access''' > '''Manage SSH keys''' > '''Public keys''' > ''(item named like `id_rsa`)'' > '''View/Download'''
***** Download the file and save it to `~/.ssh/`. Makes sense to rename it to `namecheap_rsa.pub` so as to overwrite any existing `id_rsa` files.
**** Click the '''Test Connection''' button.
***** Ignore the passphrase prompt.
***** A 2nd prompt will appear asking for a password. Enter the ssh account password.
**** The SSH configuration can be saved if the test connection was successful.
** '''General''' tab 
*** '''Host:''' 127.0.0.1 (Don't use "localhost" here.)
*** '''Port:''' 3306
*** '''User:''' MariaDB user name with access to the web app's database
*** '''Password:''' MariaDB password
*** '''Database:''' The web app's MariaDB database name
*** Click the '''Test Connection''' button
**** Ignore the '''passphrase''' prompt.
**** The next dialog will prompt for a '''password'''. Enter the ssh account's password.
*** The data source is ready to be saved if the test connection succeeds.

=== Connecting to the existing data source ===

When first establishing a connection to the data source, DataGrip will prompt for a password on the status bar at the bottom right of the app. Enter '''passphrase''' and '''password''' as described above.

The connection will time out after some minutes go by. It will be necessary to enter the ssh password again after this happens. The prompt will show up at the bottom right of the DataGrip app.

== Reference ==

* [https://www.namecheap.com/support/knowledgebase/article.aspx/1249/89/how-to-remotely-connect-to-a-mysql-database-located-on-our-shared-server/ How to remotely connect to a MySQL database located on our shared server] - Namecheap Knowledgebase
[[Category:MySQL]][[Category:MariaDB]][[Category:Databases]][[Category:Web Development]]