Step-by-Step MySQL Setup on Oracle's Free Ubuntu VM

** This post was updated on 29-07-2024

Complete guide to connect to the MySQL instances remotely

In my previous article, I wrote about hosting a site on Oracle always free offering. The Oracle always free is a generous offer. One of the eligible free services is:

Arm-based Ampere A1 cores and 24 GB of memory usable as 1 VM or up to 4 VMs with 3,000 OCPU hours and 18,000 GB hours per month

That means you can set up a free VM with 4 cores CPU and 24 GB of memory!

Recently, I utilize the Oracle Arm-based VM to build a MySQL instance and migrate an existing MySQL database over. The migrated server runs very well.

In this article, I will go through the process and share some tricks of configuring the database.

Setup and Access the VM

I won’t repeat the details of VM setup and access via SSH, as it can be found in my previous article . The only difference in setup is to choose the AMPERE as Shape.

Please note that I choose 3 core OCPU and 18 GB memory, although the max total limit is 4 cores and 24 GB memory. I leave some resources for my other free VM instance.

The new VM is running as below.

Install MySQL Server

After the new VM runs, we can log in to it via SSH.

It is time to install the MySQL server by running the following commands.

1
2
3
4

# update apt
sudo apt update
# install the mysql server using the APT package repository
sudo apt-get install mysql-server

After the installation is completed, we can verify the installation by checking the version.

1

mysql --version

Secure the MySQL instance

The next step is to secure the database by running the mysql_secure_installation script.

1

sudo mysql_secure_installation

The mysql_secure_installation script will go through a few steps, and modify MySQL’s default security options to make it more secure. The first step is to set the level of password policy.

I choose the strong policy option, then was asked to set up the password of the root user. At this point, the following error is shown and blocks me from continuing.

After some googling, I found a solution: update the authentication method of the root user. Starts a new SSH session, and log in to MySQL using the following command.

1
2
3
4
5
6

mysql -u root -p
#under mysql prompt
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password 
BY '[your passowrd]';

exit;

Now, we can restart the mysql_secure_installation and should be able to complete the full process. In each question other than the password policy, I choose Yes and Enter for the default choice.

The secure installation script performs these actions to tighten up the security.

• remove some anonymous users

• remove the test database

• disable root login remotely

We can verify the status of the newly installed database instance.

sudo systemctl status mysql.service

Add a new MySQL User

Now, our new server is running with theroot user created. Since the root user is designed to be used for admin purposes only, we need to create another user which can be used from an external service or App.

Firstly, connect to MySQL prompt via root user login. Then, execute the following SQL commands to create an appuser account.

1
2
3
4
5

mysql> CREATE USER 'appuser'@'%' IDENTIFIED BY '[your password]';
mysql> GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES,
 RELOAD on *.* TO 'appuser'@'%' WITH GRANT OPTION; 
mysql> FLUSH PRIVILEGES;
mysql> exit;

Then, we can try to log in as the newly created user.

1

mysql -u appuser -p

Connect to the MySQL instance remotely

We have set up the MySQL instance with the necessary security options and user accounts in the Oracle cloud. At this point, you should be able to connect to the instance from the MySQL workbench using the public IP address via TCP/IP+SSH.

But you will receive an error if you try to connect to it via TCP/IP only via the MySQL workbench or any clients. That is because by default MySQL server is only configured to listen to local connections.

To allow remote access to the MySQL instance, we need to open up port 3306 which should be listened to by the MySQL server for external connections. It is a tricky step, as there are a few moving parts. The following are the steps involved:

• Add an ingress rule in the security list to allow port 3306

• Update MySQL configuration to enable it to listen to external requests

• Open the OS (Ubuntu) level firewall setting for port 3306

Let’s add the ingress access rule first. Navigate to the VM Instance Details page, clicks on the Virtual Cloud Network link will bring you to the VCN page. From the VCN page, navigate to the security list via “Subnet link” > “Default Security List for vcn-xxx”, then clicks on the “Add ingress rule” button to add a new rule.

Ingress rule

Please note that the Source CIDR field should be the IP address you will use to connect to the MySQL instance from. It is possible to allow any IP addresses by 0.0.0.0/0, but it is not recommended for obvious security reasons.

Update MySQL configuration

By default, there is a configuration in MySQL setting to only allow the server to listen to local connections.

1

bind-address   = 127.0.0.1

We need to either comment it out or change it to an external IP address. In the below example, 0.0.0.0 will allow any IP to connect. Since I already set up the ingress rule earlier, so only the whitelisted IP can reach the server, I use any IP setting here.

1

bind-address = 0.0.0.0

The location of the MySQL config varies depending on the distribution versions. In my case, the file can be found at /etc/mysql/mysql.conf.d/mysqld.cnf. It can be in my.cnf or mysql.cnf file in other MySQL distributions.

After the file is updated, you will need to restart the server to make it effective.

1

sudo systemctl restart mysql

To verify the server is listening to port 3306 for any IP address after the change, run the following command

1

netstat -anutp | grep : 3306

You should see a line of output similar to below

The last step is to open up port 3306 at the OS level.

Configure iptables

iptables is a firewall program that is used by ubuntu. The default Ubuntu distribution actually doesn’t apply any restrictions in iptables. In other words, port 3306 should be allowed by default setting. But the Oracle Ubuntu image takes a different approach, iptables is configured to deny all accesses by default. You can run this command to view the current rules:

1

sudo iptables -L

To allow port 3306 to pass through, you can run the following command

1
2
3

sudo apt install iptables-persistent
sudo iptables -I INPUT 6 -m state --state NEW -p tcp --dport 3306 -j ACCEPT
sudo netfilter-persistent save

Or for testing purposes, you can allow all ports temporary

1

sudo iptables -I INPUT -j ACCEPT

Finally, you should be able to connect to your new instance

MySQL Workbench test connection

Summary

So far, what we achieved are:

• Create an always free VM with 3 core CPU and 18 GB memory

• Install and configure a MySQL server instance with proper security and user accounts

• Update the VCN rules and allow the new server remotely accessible

The new MySQL server runs very fast. I can’t be happier with it.

There are still some remaining works:

• migrate the existing database to the new DB instance

• Set up automatic backups

But this article is already too long, I will write another post for those tasks.

Thanks for reading!