Installing mysql-server in remote ubuntu server and access from a remote client

Today I had to spend hours trying to find a way how to do this easily but unfortunately many options were lengthy and most of them didn’t work at all. Finally I figured out to do it in my own way( Maybe there are some security issues, so, use with caution).

The goal of this is to install and configure mysql server in remote ubuntu server and to access to it with a local client (my case it is dbeaver )

First logged in the remote server through ssh

user@local:~$ ssh ‘user’@‘remoteserver’

Install mysql server by

user@remoteserver:~$ sudo apt install mysql-server

In newer mysql versions of mysql the user is authenticated via the auth socket plugin, so you can log in to the root user with your system user credential by,

user@remoteserver:~$ sudo mysql -u root -p

Here you have to give the sudo password. If you need to change this to the old method check here.

Now create a database, user and assign privileges to that user who can connect through a remote connection.

mysql> create user ‘user’@‘localhost’ identified by ‘password’;
mysql> create database 'database';
mysql> grant all privileges on 'database'.* to 'user'@'localhost';

Now go to the local client you are using. In my case it is dbeaver.

Go to the add new connection wizard.

1. Fill the server Host as localhost and for the database and user name as above. Now go to next page.
2. From the tabs menu put a tick in the Use SSH Tunnel and then fill the detail
3. Test Connection

Nothing new right ? Yes, but when you google there are many things to do but you don’t have to do those unnecessary things where the solution is much much easier.