New MySQL database on my remote computer, behind a router

New MySQL database on my remote computer, behind a router

1. Enable your router’s port forwarding feature on port 22 (ssh service). (No need to open port 3306, good for security reasons)
2. Make sure the MySQL service is running on the remote computer. In console, type:
   • mysql -h localhost -u root -p
   • You might need to start the service: sudo /etc/init.d/mysql start
3. Make a ssh tunnel from your Internet connected computer to your remote computer which is running MySQL:
   • If you’re running some service locally, on port 3306, stop it
   • Then, connect the remote computer’s port 3306 to your local port 3306, type:
     • ssh -L 3306:localhost:3306 user@remote_routers_public_ip
     • provide the corresponding ssh password
   • When the tunnel has been established, leave the console active, and the you can connect to the remote port 3306, as if it was local in other console, example:
     • mysql -h 127.0.0.1 -u root -p some_database
4. Connect as root to your MySQL remote instance through local port 3306 and create a new database.
5. Create a new user and grant the corresponding permissions:
   • mysql> CREATE USER ‘username’@’%’ IDENTIFIED BY ‘password’;
   • mysql> GRANT ALL PRIVILEGES ON database.* TO ‘username’@’%’;
6. You can now connect to your database with the new user.