Marko Zivkovic
Linux Ubuntu 18.4 terminal - status of MySQL

How to Connect to a Remote MySQL Server Using SSH

October 18, 2019 by

This article will explain how to set up a connection from a Windows host OS using a PuTTY client to a remote MySQL server on Linux Ubuntu 18.04.

Before we start, you must have already:

MySQL installation on Linux Ubuntu 18.4

First, you must install MySQL on the remote Linux machine.

On Linux Ubuntu 18.4, open the Terminal, type the sudo apt-get update command and press the Enter key:

Linux Ubuntu 18.4 terminal

Next, in the Terminal, type the sudo apt-get install mysql-server command to start installing MySQL:

Linux Ubuntu 18.4  terminal - installing MySQL

Type Y to proceed with the installation.

To see which version is installed on the machine, type and run the mysql –version command:

Linux Ubuntu 18.4  terminal - MySQL version

For detailed information on how to install and configure MySQL, please visit the How To Install MySQL on Ubuntu 18.04 page.

After setting up MySQL, make sure it is active and running. To see the status of MySQL, type and run the service mysql status command:

Linux Ubuntu 18.4  terminal - status of MySQL

If MySQL is not running (inactive),

Linux Ubuntu 18.4  terminal - MySQL not running

Execute the sudo service mysql start command:

Linux Ubuntu 18.4  terminal - start MySQL

Install SSH server on Linux Ubuntu 18.4

To install Secure Shell (SSH) on Linux Ubuntu 18.4, from the Terminal type the sudo apt install openssh-server command. Press the Enter key and type user password when prompted. Then press Y and Enter to continue with the installation:

Linux Ubuntu 18.4  terminal - install ssh server

To verify that the installation was successful and the SSH service is running, type and run the sudo systemctl status ssh command:

Linux Ubuntu 18.4  terminal - check ssh status

If the SSH server is not running, type and execute the sudo systemctl start ssh command.

Installing SSH client on Windows OS

There are many SSH clients for Windows OS. For the purpose of this article, we’ll use the 64-bit PuTTY client to connect to the remote MySQL server.

Double click on the putty-64bit-<version>-installer.msi installer. The Welcome window will appear. Click the Next button to continue:

Install ssh client - PuTTY

During the Destination Folder step, choose where the PuTTY client will be installed:

PuTTY - Destination Folder dialog

In the Product Features step, select the way you want the features installed. If you plan to use PuTTY frequently, add a PuTTY shortcut to the Desktop. Click the Install button:

PuTTY - Product Features dialog

After the successful installation, click the Finish button to close the PuTTY installer:

PuTTY - Completed dialog

Connect to a remote MySQL server

To connect to a remote MySQL server, you’ll need its IP address. Go to Linux Ubuntu 18.04 and in the Terminal, type ifconfig or the IP address command and see information under inet:

Linux Ubuntu 18.4  terminal - check IP address

Now, go to the local Windows machine and launch PuTTY. In the Host Name (or IP address) box, under the Session tab, enter the IP address of the remote machine and click the Open button:

PuTTY - Configuration dialog

The PuTTY Security Alert warning dialog appears the first time you connect. Click the Yes button to continue:

PuTTY - Security Alert  dialog

In the login as, type the username that will be used to connect to the remote machine. Press Enter and type a password for that user:

PuTTY - login dialog

When logged in, the terminal should look like this:

PuTTY - connection to remote Linux Ubuntu 18.04 system

To check the connection, execute some commands, for example, ls -l:

List remote files

Now, type and run the mysql -u root -p command for a root user or for a user who has permission to connect to a remote MySQL server:

Connect to remote MySQL Server

If the ERROR 1698 (28000): Access denied for user ‘root’@’localhost’ appears when trying to connect to the remote MySQL server when using the root user, the authentication method from auth_socket to mysql_native_password needs to be changed. To change this, open the MySQL prompt from a terminal:

sudo mysql

Now, check the authentication methods of MySQL user accounts and type the following code:

MySQL authentication methods

As shown in the example, the root user uses the auth_socket authentication method (plugin).

To configure the root user, use the mysql_native_password authentication method. Execute the following code:

Then, execute the FLUSH PRIVILEGES code which tells the server to reload the grant tables and put your new changes into effect:

Now, when checking the authentication methods for the MySQL users, it can be seen that the authentication method for the root user is changed from auth_socket to mysql_native_password:

MySQL - mysql_native_password authentication method

Another common problem that can occur when trying to connect to a remote MySQL server is when Uncomplicated Firewall (UFW) is active, and traffic on port 22 is not allowed. In a situation like this, the error Network error: Connection timed out may appear:

PuTTY - Connection problem when ufw is active

To resolve this, make sure that the firewall is either down, or the rules are correct to allow you to connect to the remote MySQL server.

Check the status of the firewall by using the following command:

sudo ufw status

Check status of ufw

As shown above, the firewall is active. To disable it, type and execute the following command:

sudo ufw disable

Disable ufw

To allow traffic over port 22 without disabling the firewall, type and run the following command:

sudo ufw allow 22

Allow port 22 in ufw

Now, when checking ufw status, it can be seen that port 22 is set/enabled in the firewall:

Check ufw status

Also, in ufw, you can set a rule to allow ssh service traffic instead of a port, by typing the following command:

sudo ufw allow ssh

The same Network error: Connection timed out error message may appear if, in the Host Name box of the PuTTY Configuration dialog, you type a wrong IP address:

PuTTY - connection problem due wrong IP address

If you are using SQL Server Management Studio and want to connect to a MySQL server and query data, you can look at articles that show how to configure and use MySQL linked servers:

Marko Zivkovic

Marko Zivkovic

Marko is a Mechanical engineer, who likes to play basketball, foosball (table-soccer) and listen to rock music. He is interested in SQL code, PHP development, HTML and CSS techniques.

Currently working for ApexSQL LLC as a Software Sales Engineer, he is helping customers with any technical issues and does quality assurance for ApexSQL Complete, ApexSQL Refactor and ApexSQL Search free add-ins.

View all posts by Marko Zivkovic
Marko Zivkovic
229 Views