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.
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.
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
If you are using AWS Cloud, you need to further enable inbound access to port 3306, apart from
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.
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
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