Enable MySQL Server Remote Connection in Ubuntu

Enable MySQL Server Remote Connection in Ubuntu

By default MySQL Server on Ubuntu run on the local interface, This means remote access to the MySQL Server is not Allowed. To enable remote connections to the MySQL Server we need to change value of the bind-address in the MySQL Configuration File.

First, Open the /etc/mysql/mysql.conf.d/mysqld.cnf file (/etc/mysql/my.cnf in Ubuntu 14.04 and earlier versions).

vim /etc/mysql/mysql.conf.d/mysqld.cnf

Under the [mysqld] Locate the Line,

bind-address = 127.0.0.1

And change it to,

bind-address = 0.0.0.0

systemctl restart mysql.service

Now Ubuntu Server will allow remote access to the MySQL Server, But still you need to configure MySQL users to allow access from any host.

For example, when you create a MySQL user, you should allow access from any host.

CREATE USER ‘username’@’%’ IDENTIFIED BY ‘password’;

Or Allow from Specific IP Address,

CREATE USER ‘username’@’192.168.1.100’ IDENTIFIED BY ‘password’;

The output should show that MySQL Server running on the socket 0 0.0.0.0:3306 instead of 127.0.0.1:3306.

MySQL Server running on the socket 0 0.0.0.0:3306
You can also try to telnet to the MySQL port 3306 from a remote host. For example, if the IP Address of your Ubuntu Server is 192.168.1.10, Then from the remote host execute,

telnet 192.168.1.10 3306

You can also run the nmap command from a remote computer to check whether MySQL port 3306 is open to the remote host.

nmap 192.168.1.10

The output should list MySQL port 3306 and the STATe should be open. If the MySQL port 3306 not open, Then there is a firewall which blocks the port 3306.

Troubleshoot Ubuntu MySQL Remote Access
To make sure that, MySQL server listens on all interfaces, run the netstat command as follows.

netstat -tulnp | grep mysql

How to Create a New User

Log in to the MySQL server.
Log in to MySQL with the command mysql -u root -p
Type the MySQL root user password.

CREATE USER ‘newuser’@’localhost’ IDENTIFIED BY ‘password’;
GRANT ALL PRIVILEGES ON * . * TO ‘newuser’@’localhost’;
FLUSH PRIVILEGES;

How To Grant Different User Permissions

Issue the MySQL command:
To allow specific IP for specifi user
GRANT ALL ON wordpressdb.* TO ‘wpadmin’@’192.168.1.100’ IDENTIFIED BY ‘%u#098Tl3’ WITH GRANT OPTION;

To allow any IP for specifi user
GRANT ALL ON wordpressdb.* TO ‘wpadmin’@’%’ IDENTIFIED BY ‘%u#098Tl3’ WITH GRANT OPTION;

Flush the MySQL privileges with the command
FLUSH PRIVILEGES;

Exit out of the MySQL prompt with the command exit;

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.

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

Exporting / Importing data in MySql

Importing data

mysql -h[host] -u[user] -p database < file.sql

Exporting data

mysqldump -h[host] -u[user] -p database > database.sql
mysqldump -h[host] -u[user] -p database table1 table2 > database_tables.sql

mysqldump –no-create-info -h[host] -u[user] -p database table1 table2 > database_tables.sql
mysqldump –no-data -h[host] -u[user] -p database table1 table2 > database_tables.sql