Enable Remote Connections Mysql Ubuntu

To expose MySQL to anything other than localhost you will have to have the following line

For mysql version 5.6 and below

uncommented in /etc/mysql/my.cnf and assigned to your computers IP address and not loopback

For mysql version 5.7 and above

uncommented in /etc/mysql/mysql.conf.d/mysqld.cnf and assigned to your computers IP address and not loopback

#Replace xxx with your IP Address
bind-address = xxx.xxx.xxx.xxx
Or add a bind-address = 0.0.0.0 if you don’t want to specify the IP

Then stop and restart MySQL with the new my.cnf entry. Once running go to the terminal and enter the following command.

lsof -i -P | grep :3306
That should come back something like this with your actual IP in the xxx’s

mysqld 1046 mysql 10u IPv4 5203 0t0 TCP xxx.xxx.xxx.xxx:3306 (LISTEN)
If the above statement returns correctly you will then be able to accept remote users. However for a remote user to connect with the correct priveleges you need to have that user created in both the localhost and ‘%’ as in.

CREATE USER ‘myuser’@’localhost’ IDENTIFIED BY ‘mypass’;
CREATE USER ‘myuser’@’%’ IDENTIFIED BY ‘mypass’;
then,

GRANT ALL ON *.* TO ‘myuser’@’localhost’;
GRANT ALL ON *.* TO ‘myuser’@’%’;
and finally,

FLUSH PRIVILEGES;
EXIT;

Restart Mysql service and test again with new user to mysql from remote access.

Advertisements

Create a New User and Grant Permissions in MySQL

How To Create a New User and Grant Permissions in MySQL

================================

Before we create and grant the permissions to the new user, we need to open the ( MySql port 3306 from firewall ).
To check firewall is active or inactive at unbuntu by the following command

ufw status

if reply ufw inactive, we need to open the ufw (firewall) by the following command

ufw enable

And then you can check which port are allow in our web server by the following command

ufw status
and it will reply all status.
If mysql is not allow, we need to allow it by the following command

ufw allow 3306/tcp

if port 3306 is not okay to access from the remote / other local pc, we need to change the mysql default por to ‘3360’ or as you wish.

to change the MySql port to ‘3360’,
go to the /etc/mysql
and edit the my.cnf

go to ‘[mysqld]’ part
chnage the default “bind_address” to “server_ip_address
change the default “port” to “3360

And then, restart the mysql service by the follwoing command
sudo service mysql restart

After it all, we need to check one thing
which ports are opening at our Web Server by the following

nmap localhost/ipaddress

========================================

login to mysql with root password
mysql -uroot -p
and then,

1) Let’s start by making a new user within the MySQL shell:

CREATE USER ‘newuser’@’localhost’ IDENTIFIED BY ‘password’;

2) provide the user with access to the information they will need

GRANT ALL PRIVILEGES ON * . * TO ‘newuser’@’localhost’;

3) Let’s start by making a new user within the MySQL shell:

CREATE USER ‘newuser’@’ip_address’ IDENTIFIED BY ‘password’;

4) provide the user with access to the information they will need

GRANT ALL PRIVILEGES ON * . * TO ‘newuser’@’ip_address’;

5) Reload all privileges

FLUSH PRIVILEGES;

====================================

Cheers