The following guide describes setting up a MySQL Server on ubuntu. We will set up a test database and enable access to connect to it externally using a client. It is not advised to open access to all IP addresses.

Install MySQL Server

If you haven't already installed mysql, installed it with the following command. This guide assumes you are using MySQL 5.7 and above.

sudo apt-get update
sudo apt-get install mysql-server

Accept Yes and complete the installation.

Allow External IP Access

Make sure to open the port 3306.

sudo ufw enable
sudo ufw allow mysql

To check the status of allowed ports run the following command. sudo ufw status

Next, we want to update the bind-address

ubuntu@server:~$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

On line number 43 with ctrl+ _ (underscore), change the bind-address to allow connection from anywhere

bind-address   = 127.0.0.1

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 0.0.0.0

Allow Inbound connections to MySQL Port in EC2

If you are using AWS Cloud, you need to further enable inbound access to port 3306, apart from ufw

Go to the security group EC2> Security Groups and add MYSQL/Aurora in the inbound rules. Make sure the security group is attached to the EC2 instance where MySQL is installed.

Access MySQL Database externally

For this purpose, we will create a new user and a database. You can change the name of the db and user as per your need. Avoid using the root user for this purpose

create database deepak;
CREATE USER 'xuser'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON deepak.* TO 'xuser'@'%';
FLUSH PRIVILEGES;

We are also giving access to xuser to access all the tables of deepak from any ip address %

Connecting using a database client

Here we are using Netbeans to connect. Any other database client, workbench or other clients can be used to access the database.

As you can see in the above picture, we have connected to the database. You can check your existing connections with the following command on your ubuntu machine

lsof -i -P | grep :3306