How to Set Up a Hosted MySQL Server on Clouding.io

Clouding.io is a cloud hosting company that allows you to rent low-cost Virtual Private Servers (VPS). Clouding.io bills you hourly, depending on the resource usage of your VPS. The web management interface of Clouding.io is really simple and easy to use, for example you can easily configure automated backups. For these reasons, you should give Clouding.io a try! As an example, in this article, I will show you how to deploy a VPS on Clouding.io and set up a MySQL server. So, let us begin.

Creating A Clouding.io Account

To use Clouding.io, you will need to create an account. To create a Clouding.io account, visit the official website of Clouding.io.

Type in your email address and password in the sign-up form.

Then, check the checkboxes labeled “Accept the terms of use and privacy” and “I accept that Clouding.io may send notices and commercial communications.”

Then, click the button that says, “Create a free account.” Your Clouding.io account should now be set up and active.

Once your account is created, click “Log in” from the Clouding.io homepage.

Now, type in your email address and password and click “Log in.”

Once you have logged in, you should see your Clouding.io dashboard.

Creating a VPS

To create a new VPS, click the button that says, “CLICK HERE TO CREATE YOUR FIRST SERVER.”

The VPS creation wizard should be displayed on your screen.

Type in a name for your VPS in the “Pick a name” section. I chose the name mysql-server in my example.

Select an operating system and an operating system version for your VPS from the “Select disk source” section. In the example, I used Ubuntu 18.04 LTS OS.

From the “Select the server configuration” section, configure the amount of RAM, SSD disk space, and CPU cores for the VPS using the slider.

The cost of your VPS per hour should be displayed on the right-hand side of the page, as you can see in the screenshot below.

If you want to see the cost on a monthly basis, instead of the default hourly basis, click the toggle button, as indicated below.

As you can see, the monthly cost of the VPS is displayed.

If you wish to enable backup for your VPS, toggle on the “Enable backups” button, as marked in the screenshot below.

Then, you can select the Backup frequency and number of backups (Number of slots) you would like for Clouding.io to keep in the dropdown menus. The cost of the backup will be displayed on the righthand side of the screen.

By default, the Backup frequency is set to One week and Number of slots is set to 4.

Once you are finished, click “SUBMIT.”

As you can see, the VPS mysql-server is being created. This process may take a while to complete.

Once the VPS is ready, Clouding.io will assign a public IP to the VPS and the Status should be Active, as you can see in the screenshot below.

Connecting to the VPS via SSH

To connect to your VPS via SSH, you will need to know the IP address or DNS name of your VPS and the root password. You can find this information from the Settings page of the VPS.

First, click the “…” button of the VPS you wish to connect to from the Clouding.io dashboard.

Click “More…” as marked in the screenshot below.

You should be taken to the Settings page of your VPS.

Scroll down a bit and click the eye icon to see the root password of the VPS, as marked in the screenshot below.

As you can see, the password is displayed.

To connect to the VPS via SSH, you will need the Hostname and the Password of the VPS. You can copy this information from the Settings page, as shown in the screenshot below. The Username in this example is root.

From your computer, connect to the VPS as follows:

$ ssh root@<vps-hostname>

In my case, the <vps-hostname> is 7bf2e8f2-e851-4785-8fa2-1dfb07f9107b.clouding.host

The hostname will be different in your case. So, make sure to replace this name with yours from now on.

Type “yes” and press <Enter> to accept the SSH key.

Type the password for your VPS and press <Enter>.

You should now be logged in to your VPS.

Upgrading the VPS Operating System

Once you have logged in to your VPS via SSH, the first thing you should do is to upgrade all the installed software packages of your Ubuntu OS.

To do this, update the APT package repository cache via the following command:

$ apt update

The APT package repository cache should now be updated.

Upgrade the remainder of the installed packages via the following command:

$ apt upgrade

To confirm the upgrade, press Y and then press <Enter>.

APT should now begin downloading the upgrades. Once all upgrades are downloaded, the upgrades will begin installing.

If you see this prompt message during the Ubuntu upgrade process, select <Yes> and press <Enter>.

The upgrade should continue.

Every time you see this type of prompt message, select “keep the local version currently installed” and press <Enter>.

The upgrade should continue.

If you see this type of prompt, simply press <Enter>.

Select “keep the local version currently installed” and press <Enter>.

The upgrade should continue.

At this point, Ubuntu should be fully upgraded.

Reboot the VPS as follows:

$ reboot

Once your VPS starts, you may use SSH to log in to your VPS, as follows:

$ ssh root@<vps-hostname>

Once you have logged in, run the following command to check the Ubuntu version.

$ lsb_release -a

At the time of this writing, the latest version of Ubuntu 18.04 LTS is Ubuntu 18.04.4.

Installing MySQL Server

You can install the MySQL server and client programs on your VPS via the following command:

$ apt install mysql-server mysql-client

To confirm the installation, press Y and then press <Enter>.

The APT package manager should begin downloading and installing all required packages.

At this point, the MySQL server and client programs should be installed.

Check the status of the mysql service as follows:

$ systemctl status mysql

The mysql service should be active (running) and enabled (will automatically start on boot), as shown in the screenshot below:

If the mysql service is still not active (running) for any reason, you may also start the mysql service manually, as follows:

$ systemctl start mysql

If the mysql service is not enabled (won’t automatically start on boot) for any reason, you may add the mysql service to the system startup, as follows:

$ systemctl enable mysql

Allowing Remote Access to MySQL Server

By default, the MySQL server binds to TCP port 3306 of the localhost IP address 127.0.0.1, as you can see in the screenshot below.

$ netstat -tlpen

To allow remote access to your MySQL server, open the configuration file /etc/mysql/mysql.conf.d/mysqld.cnf as follows:

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

Replace the line bind-address = 127.0.0.1, as shown in the screenshot below.

Change it to bind-address = 0.0.0.0, as shown in the screenshot below.

Once you are finished with this step, press <Ctrl> + X followed by Y and <Enter> to save the configuration file.

Restart the mysql service, as follows:

$ systemctl restart mysql

Check the status of the mysql service, as follows:

$ systemctl status mysql

The mysql service should be active (running).

The MySQL server should run on TCP port 3306 of the public IP address of your VPS, as you can see in the screenshot below.

$ netstat -tlpen

Creating MySQL Databases and Users for Remote Access

The most secure way of accessing MySQL databases remotely is to create a new MySQL user and allow user access to only the required databases. I will show you how to do this in the following example.

First, log in to your MySQL Server Console as the root user, as follows:

$ mysql -u root -p

By default, the MySQL root user does not have any password set. So, just press <Enter>.

You should be logged in to the MySQL Server console.

Create a new database, db01, via the following SQL statement:

mysql> CREATE DATABASE db01;

Create a new remote user, say, shovon, and set the user password to, say, secret, with the following SQL statement.

mysql> CREATE USER 'shovon'@'%' IDENTIFIED BY 'secret';

The remote user shovon should now be created.

Allow the remote user shovon full access to the db01 database as follows:

mysql> GRANT ALL PRIVILEGES ON db01.* TO 'shovon'@'%'

For the changes to take effect, run the following SQL statement.

mysql> FLUSH PRIVILEGES;

Exit out of the MySQL Server console, as follows:

mysql> exit

To test whether the newly created user shovon can log in to the MySQL Server, run the following command:

$ mysql -u shovon -p

Type in the password of the user shovon and press <Enter>.

You should be logged in to the MySQL Server console. So, everything is working so far.

Exit out of the MySQL Server console as follows:

mysql> exit

Configuring the Firewall

You will still be unable to access your MySQL server remotely, as remote access to TCP port 3306 of your VPS is blocked by default.

To allow remote access to TCP port 3306, navigate to My Firewalls from the Clouding.io dashboard and click the + button, as indicated in the screenshot below.

Type a name and a short description of your firewall profile. Then, click SUBMIT.

A new firewall profile should be created.

Click the edit button  of your newly created firewall profile.

Click the + button to add a new firewall rule to the profile.

Click the Template rules dropdown menu, as marked in the screenshot below.

Select Allow MySQL from the list.

Click the + button, as marked in the screenshot below.

Click SUBMIT.

A new firewall rule for allowing TCP port 3306 should now be added, as seen in the screenshot below.

Click the “…” button alongside your mysql-server VPS on the Clouding.io dashboard.

Click “More…” as marked in the screenshot below.

Go to the Network tab of your VPS and click the + button, as marked in the screenshot below.

Select the newly created firewall profile and click SUBMIT.

The firewall profile should now be added to your VPS.

Connecting to the Remote MySQL Server

To connect to the remote MySQL Server, you must have the MySQL client program installed on your computer.

If you are using Ubuntu/Debian operating system on your computer, you will need to install the mysql-client package.

First, update the APT package repository cache, as follows:

$ sudo apt update

Next, install the mysql-client package, as follows:

$ sudo apt install mysql-client

To confirm the installation, press Y and then press <Enter>.

The MySQL client program should now be installed.

If you are using a CentOS 8/RHEL 8 operating system, you may install the MySQL client programs via the following commands:

$ sudo dnf makecache
$ sudo dnf install mysql

Once you have installed the MySQL client programs, you will be able to connect to your MySQL server remotely, as follows:

$ mysql -u <remote-username> -h <vps-hostname> -p

Here, <remote-username> is the MySQL user that you have created on your VPS, and the <vps-hostname> is the DNS name or IP address of your VPS. Be sure to replace these with your <remote-username> and <vps-hostname>.

Enter the password of your remote MySQL user and press <Enter>.

You should now be logged in to your remote MySQL server.

As you can see, the database db01 is remotely accessible for the remote user.

mysql> SHOW DATABASES;

You may use the database db01 as follows.

mysql> USE db01

Create a new table users with the following SQL statement.

mysql> CREATE TABLE users (id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30) NOT NULL);

As you can see, the users table has been created.

mysql> SHOW TABLES;

Insert some dummy values into the users table, as follows:

mysql> INSERT INTO users(name) VALUES ('Alex'),('Bob'),('Lily');

You may fetch all rows from the users table, as follows:

mysql> SELECT * FROM users;

So, according to the above image, everything is working.

You may exit out of the MySQL Server console, as follows:

mysql> exit

CONCLUSION

This article showed you how you set up a hosted MySQL server on Clouding.io. In the world of the virtual data center and cloud computing, it is easier then ever to provision server resources dynamically as your needs arise, even without your own physical hardware and data center space. The entire process is automated, and as a developer, you can safely assume that the hardware will be there when you need it.

The Clouding.io platform provides a clear interface to provision the resources you need, select the hardware resources required for your task, and see the granular pricing that will be incurred by your hardware selection.

Once you have allocated the resources, you will gain access to the virtual environment via familiar tools, such as the SSH protocol. From there, the Linux system administration and DevOps experience will be the same as physical platforms, as if you bought your own machine.

I encourage you to give Clouding.io a try for yourself and take advantage of this great resource!

from Linux Hint https://ift.tt/2XR9bGQ