Connect MySQL database with DBeaver through SSH

[This article was first published on MYHAPPYDATA BLOG, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

Aim:

The aim of this post is to create a straightforward tutorial how to connect to a server MySQL database with DBeaver through SSH.

Prerequisites:

  • DBeaver (version 5.0.1) has already installed natively to your PC (I used Manjaro)
  • Ubuntu 16.04 VPS (e.g. DigitalOcean) which has a LAMP or LEMP installation
  • Root privilege on your VPS

Step 1: allow remote access to MySQL

Open a terminal application and login to your VPS at first via SSH:

ssh -i PATH_OF_THE_PRIVATE_KEY@SERVER_IP

Navigate to ‘/etc/mysql/mysql.conf.d/’ folder and open the MySQL config file ‘mysqld.cnf’ with a text editor (like nano):

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Comment out the line ‘bind-address = 127.0.0.1’. Make sure This like has to begin with ‘#’ like this:

# bind-address = 127.0.0.1

Save the file and exit (CTRL+X and press ‘Y’ for yes). Restart the MySQL server to make the changes come into effect:

sudo systemctl restart mysql

Step 2: Allow connection to the mysql port through UFW

For security reasons the best way is just to gain access only to your PC’s IP you use:

sudo ufw allow from IP_OF_YOUR_PC to any port 3306

Step 3: Connect to MySQL server in Dbeaver

There are two important windows has to fill it in the right way. In the ‘General’ window you have to take care of only the ‘User name’ and ‘Password’ fields.

  • User name: (existing) user name you want to use at MySQL connection
  • Password: Password of the user you selected before

When you have already filled the required fields click to Next button. After it the second important window appears:

First of all click to the checkbox icon ‘Use SSH Tunnel’ to gain access to the settings. Fields you have to fill in the right way:

  • HOST/IP: IP of the VPS
  • User Name: (existing) user name you want to use at MySQL connection (same as on the other page)
  • Authentication Method: ‘Public Key’
  • Private Key: Path of the private key on your PC

If everything has configured correctly when you click to ‘Test Connection’ this message box will appear:

When the test connection works click to the ‘Finsih’ button. After the whole procedure you can connect to any MySQL database which your user has permission to access.

To leave a comment for the author, please follow the link and comment on their blog: MYHAPPYDATA BLOG.

R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)