Different Ways to Connect a Remote MySQL Server Using Ubuntu

1. Different Ways to Connect a Remote MySQL Server Using Ubuntu
2. Connect From Ubuntu 20.04 Client Machine to Ubuntu 20.04 Server Machine for Data Manipulation
3. Connect From Ubuntu 20.04 Client Machine to Ubuntu 20.04 Server Machine to Start, Stop and Restart the Server

Today, we will learn how to connect a remote MySQL server using Ubuntu to manipulate data and start and stop the MySQL server.

Different Ways to Connect a Remote MySQL Server Using Ubuntu

1. From Ubuntu 20.04 Client Machine to Ubuntu 20.04 Server Machine for Data Manipulation
2. From Ubuntu 20.04 Client Machine to Ubuntu 20.04 Server Machine to Stop/Start MySQL Server

Connect From Ubuntu 20.04 Client Machine to Ubuntu 20.04 Server Machine for Data Manipulation

Connect With a Custom User

Here, we will learn how to connect a custom user that resides in a remote MySQL server. We have created a user named mehvish in the remote MySQL server and granted all the privileges.

mysql> CREATE USER "mehvish"@"%" IDENTIFIED BY "PASSWORD";
#here % sign shows that this user can be connected remotely

To connect to a remote MySQL server, we must have a mysql-client installed on our machine. In Microsoft Windows 10, the mysql-client gets automatically installed when installing the MySQL server.

You can follow this article for MySQL installation on the client machine.

To make a remote connection, we need the IP address of a host machine where the MySQL server is being hosted, the username, and the password of that particular user account. To retrieve that IP address, we use the ip a or ifconfig command on Ubuntu, where the MySQL server resides.

Once we know the IP address, username, and password, we can use the following command to connect to a remote MySQL server.

# Syntax
# mysql -h hostIP -u username -p password
$ mysql -h 192.168.56.102 -u mehvish -p ******

You most probably will get the following error if you try to make a remote connection for the first time.

ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.2.15:3306' (10060)

To get rid of this error, we need to modify the configuration file on the MySQL server. We use the vim text editor to edit the configuration file, but you can use any of your choices.

$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

Once the file is opened, locate the bind-address under the [mysqld] section and comment on this as given in the following screenshot. Why are we commenting on the bind-address option?

MySQL server does not allow remote connections due to its default behavior; it only lets the localhost connect with the MySQL server. That is why comment the bind-address and allow a remote client machine to establish a connection with the MySQL server.

Alternatively, we can do the following instead of commenting on the bind_address.

1. We can change the value of bind-address from 127.0.0.1 to 0.0.0.0.
2. If there is no bind-address option, we can write skip-networking and skip-bind-address.

We can use the following options to edit and save the configuration file using the vim text editor.

1. Press I to edit the file.
2. Press Esc to get out of the editing mode.
3. Press Esc, type :w, and hit Enter to save the file.
4. Press Esc, type :q, and hit Enter to quit the file.
5. Press Esc, type :wq, and hit Enter to save and quit the file in one step.
6. Press Esc, type :q!, and hit Enter to exit from the file by discarding all the changes.

We have to restart the service as follows on the machine where the MySQL server is being hosted.

$ sudo systemctl restart mysql

Now, use the following command again to successfully connect with the mehvish user account that lives in the remote MySQL server. Remember, we are using the following query on our local machine.

$ mysql -h 192.168.56.102 -u mehvish -p ******

Connect With a root User

Before diving into the details, let’s check if the root user accepts the remote connections. We can use the following command on Ubuntu, where our MySQL server is hosted.

mysql> SELECT USER, HOST from mysql.user;

Output:

Did you see that the root user can only be connected from localhost? We have to grant remote access by using the following commands to connect it remotely.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';
mysql> UPDATE mysql.user SET host='%' WHERE user='root';

Further, restart the MySQL server by using the following command.

$ sudo systemctl restart mysql

Now, check whether the root user accepts the remote connections or not. See, the host of the root user on the server machine is %, which shows that we can remotely connect to the root account.

Now, we can connect the root user from our local machine by using the following query to read/write data.

$ mysql -h 192.168.56.102 -u root -p ******

What if we want to start, stop and restart the MySQL server? For that, we must be on the MySQL server’s shell, which means we have to connect in a way that opens the server’s shell on our machine.

How can we do that? Let’s see the following chapter.

Connect From Ubuntu 20.04 Client Machine to Ubuntu 20.04 Server Machine to Start, Stop and Restart the Server

We have to follow the given steps to connect a remote server using a secure shell (also called a secure socket shell).

1. Install ssh to get the shell access to the MySQL server, hosted on Ubuntu Machine.

2. Now, configure the ssh. Then, open the terminal on your host machine (local machine), and write the correct credentials to connect to the remote server.

   You can use any of the following depending on your situation.

   2.1. Use the ssh remote_host command. It is the most straightforward and used when we have the same username on the remote and local machines.
   2.2. Use ssh remote_username@remote_host if the username differs on a remote machine compared to the local machine. This will provide you access to the shell of the server machine.

3. We can use the following commands to function as per the requirements.

Start MySQL Server:

$ sudo systemctl start mysql

Stop MySQL Server:

$ sudo systemctl stop mysql

Restart MySQL Server:

$ sudo systemctl restart mysql

Check Status of MySQL Server:

$ sudo systemctl status mysql

Remember, the MySQL Server is named mysqld on your end, then you may have to use the command as follows.

$ sudo /etc/init.d/mysql restart