JIYIK CN >

In this article we will learn how to connect to a remote MySQL server using Ubuntu to manipulate data and start and stop the MySQL server.

Different ways to connect to a remote MySQL server using Ubuntu

1. Data operations from Ubuntu 20.04 client machine to Ubuntu 20.04 server machine
2. From Ubuntu 20.04 client machine to Ubuntu 20.04 server machine to stop/start MySQL server

Connect to Ubuntu 20.04 server machine from Ubuntu 20.04 client machine for data operations

Connect with custom user

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

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 install mysql-client on our machine. In Microsoft Windows 10, mysql-client is automatically installed when you install the MySQL server.

You can follow this article to install MySQL on your client machine.

To establish a remote connection, we need the IP address of the host hosting the MySQL server, the username, and the password for that specific user account. To retrieve the IP address, we use the ip a or ifconfig command on Ubuntu where the MySQL server is located.

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

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

If you are trying to establish a remote connection for the first time, you will most likely encounter the following error.

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 vim text editor to edit the configuration file, but you can use any of your choice.

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

Once the file is opened, find the Bind Address under the [mysqld] section and comment it as per the following screenshot. Why should we comment the Bind Address option?

Due to its default behavior, MySQL server does not allow remote connections; it only allows localhost to connect to MySQL server. That is why it is necessary to comment the bind address and allow remote client machines to establish connections with MySQL server.

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

1. We can change the bind-address value from 127.0.0.1 to 0.0.0.0.
2. Without the bind-address option, we could have written skip-networking and skip-bind-address.

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

1. Press I to edit the file.
2. Press Esc to exit edit mode.
3. Press Esc, type :w, and then press Enter to save the file.
4. Press Esc, type :q, and then press Enter to exit the file.
5. Press Esc, type :wq, and then press Enter to save and exit the file in one step.
6. Press Esc, type :q!, and then press Enter to exit the file, discarding all changes.

We have to restart the service as follows on the machine hosting the MySQL server.

$ sudo systemctl restart mysql

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

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

Connecting as root

Before we go into the details, let's check if the root user is accepting remote connections. We can use the following command on Ubuntu where the MySQL server is hosted.

mysql> SELECT USER, HOST from mysql.user;

Output:

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

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

Additionally, restart the MySQL server using the following command.

$ sudo systemctl restart mysql

Now, check if the root user accepts remote connections. As you can see, the host of the root user on the server machine is %, which means we can connect to the root account remotely.

Now, we can connect as root user from local machine to read/write data using following queries.

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

What if we want to start, stop, and restart the MySQL server? To do this, we must be on the shell of the MySQL server, which means we must connect in a way that opens a server shell on our machine.

Connect to the Ubuntu 20.04 server machine from the Ubuntu 20.04 client machine to start, stop, and restart the server

We have to follow the given steps to connect the remote server using Secure Shell (also known as Secure Socket Shell).

1. Install ssh to gain shell access to the MySQL server hosted on the Ubuntu machine.
2. Now, configure ssh. Then, open the terminal on the 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.

   1. Use ssh remote_host command. It is the most direct and commonly used when we have the same username on remote and local machines.
   2. If your username on the remote machine is different from your username on the local machine, use ssh remote_username@remote_host. This will give you shell access to the server machine.
3. We can use the following command to run as required.

Start the MySQL server:

$ sudo systemctl start mysql

Stop the MySQL server:

$ sudo systemctl stop mysql

Restart the MySQL server:

$ sudo systemctl restart mysql

Check the MySQL server status:

$ sudo systemctl status mysql

Remember that the MySQL server is called mysqld on your end, so you may have to use commands like the following.

$ sudo /etc/init.d/mysql restart