Accessing Your Server Database Locally via SSH Tunnel

Contents

• What is an SSH tunnel?
• Requirements
• Step 1: Create a script to set up a tunnel
• Step 2: Open the tunnel via your browser
• Step 3: Reset the MySQL root password (optional)
• Frequently Asked Questions
• Contact

What is an SSH tunnel?

An SSH tunnel creates a secure connection between your computer and your server. You connect a local port on your computer to a port on the server, giving you local access to, for example, a database or web application running on the server.

Requirements

• A server with SSH access
• The SSH private key (or password)
• Access to a MySQL database on the server
• A local SSH client (such as OpenSSH or Git Bash on Windows)

Step 1: Create a script to set up a tunnel

Create a new text file with the extension .bat (for example, start-tunnel.bat) and add the following lines. Adjust the variables to your situation:

@echo off
REM === Configuration ===
set PRIVATE_KEY="C:\path\to\your\private_key.pem"
set REMOTE_USER=root
set REMOTE_HOST=example.com
set LOCAL_PORT=8080
set REMOTE_PORT=80

echo Starting SSH tunnel...
ssh -i %PRIVATE_KEY% -L %LOCAL_PORT%:127.0.0.1:%REMOTE_PORT% %REMOTE_USER%@%REMOTE_HOST%

pause

Save the file and double-click it to start the tunnel. If everything is correct, you can connect to the service on your server via your browser or client.

Step 2: Open the tunnel in your browser

Once the tunnel is active, you can go to this URL in your browser:

http://127.0.0.1:8080/

Replace the port number 8080 if you've set a different port.

Step 3: Reset the MySQL root password (optional)

If you can't log in to the MySQL database, you can reset the password. Follow these steps on the server:

1. Stop MySQL with the following command:

sudo systemctl stop mysql

2. Start MySQL in safe mode (without password checking):

sudo mysqld_safe --skip-grant-tables &

3. Log in to MySQL without a password:

mysql -u root

4. If you receive an error about a socket, first run the following:

sudo mkdir -p /var/run/mysqld
sudo chown mysql:mysql /var/run/mysqld
sudo mysqld_safe --skip-grant-tables &
mysql -u root

5. In MySQL, run the following to change the password:

USE mysql;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';

FLUSH PRIVILEGES;

6. Stop and restart MySQL:

sudo killall mysqld
sudo systemctl start mysql

7. Test if the password works:

mysql -u root -p

Frequently Asked Questions

What if I don't have a private key?

You can also connect with your user password, but SSH keys are more secure and recommended.

Can I use a port other than 8080? Yes, as long as the port is free on your computer.

Does this also work with other databases like PostgreSQL? Yes, as long as you know which port the database is listening on.

Contact

Have questions or feedback? Open an issue or send a message through this repository.