Allow remote access to MySQL, Ubuntu

Question

Allow remote access to MySQL, Ubuntu

I am trying to enable remote accecss for MySQL on my Ubuntu Server (VPS).

I am trying to access a database via Im building Java application in Netbeans. When trying to connect Netbeans get the following error message:

Unable to connect to JDBC: MySQL: //xx.xxx.xxx.xxx: 3306 / xxxxxx zeroDateTimeBehavior = convertToNull using com.mysql.jdbc.Driver (null, message from server: "Host 'xx.xxx.xxx.xxx 'not allowed to connect to this MySQL server ")

I followed this tutorial while trying to connect to my MySQL server via Netbeans: https://www.youtube.com/watch?v=Fk2EkBs-Oq4

MySQL

MySQL runs on the standard port 3306. In /etc/mysql/my.cnf, I comment the following line:

bind-address = 127.0.0.1

So there is no link-address in my my.cnf file.

UFW

I have UFW installed on my server. The uufw status looks like this:

     To                         Action      From
     --                         ------      ----
[ 1] 22                         ALLOW IN    Anywhere
[ 2] 80/tcp                     ALLOW IN    Anywhere
[ 3] Anywhere                   ALLOW IN    MY-IP-ADDRESS*
[ 4] 3306/tcp                   ALLOW IN    Anywhere
[ 5] 3306/tcp                   ALLOW IN    MY-IP-ADDRESS*
[ 6] 22 (v6)                    ALLOW IN    Anywhere (v6)
[ 7] 80/tcp (v6)                ALLOW IN    Anywhere (v6)
[ 8] 3306/tcp (v6)              ALLOW IN    Anywhere (v6)

* MY-IP-ADDRESS = The IP address I have "access" to the Internet, which I get from: http://whatismyipaddress.com/

What am I doing wrong and why can't I connect?

EDIT : The thing is, if I link in my.cnf, I can only bind one IP address. I need local access for my live websites running on vps as well as access from my dev machine (which this thread is targeting). My thoughts were to remove the bind in my.cnf to allow everything and then grant access through my UFW firewall to localhost and my dev machine.

+3

mysql ubuntu netbeans

JohnF Dec 18 14 at 15:29

source to share

3 answers

Change

bind-address = 127.0.0.1

For

bind-address = YOUR-IP-ADDRESS*

0

Citizen SP Dec 18 14 at 15:31

source to share

You need to grant access to this database:

GRANT ALL ON foo.* TO bar@xxx.xxx.xxx.xxx IDENTIFIED BY 'PASSWORD';

And also updated the firewall rules like this:

/sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT

0

panagdu Dec 18 '14 at 15:35

source to share

moskito-x · Accepted Answer · 2014-12-18T18:28:04+0000

if you want to use Connect via PuTTY

and tunnel
look at the bottom of this answer

I am assuming you have a mysql admin tool.
The following will look like depending on the linux tool used or whatever, but work the same.

Login and login to user administration.

enter image description here

If you have normal Mysql installed then there should only be root without host.

Since the creation of localhost, Mysql assumes that you want to manage multiple hosts.

enter image description here

Create an existing node. A computer name exists on your network. Here root@dxxxxx-p

. This must be created under username root

.

That's not all, now you still have all the permissions to grant tables.
There pricelist

is no assigned permissions

enter image description here

But the sample has all the permissions

enter image description here

For example, when administering multiple hosts.

Computer with two available names

myComp1: IP 192.168.0.101
localhost: IP 127.0.0.1

If you now connect on the same machine with "mysql -h localhost -u root ....", you get the permissions assigned to localhost.

You might be thinking because localhost

and myComp1

is the same computer.
Now automatically myComp1

has the same resolution as localhost

.
But this is not the case. So be careful.

Connect via "PuTTY" and tunnel

when you connect you to PuTTY, none of the above is required.

With a tunnel, you connect as root @ localhost on the ubuntu server.

Localhost is somewhat misleading here because it is not connected to your windows machine, but to localhost on the ubuntu server.

Accessing a remote MySQL server over SSH

So, you have MySQL on your web server, but by default it is open to local ports for security reasons.

If you want to access your database from a client tool like NetbeansMySQL Query Browser

or Netbeans , you usually need to open access from your local IP ... but this is not so secure.

So instead, just use port forwarding over an SSH tunnel, so your MySQL client thinks it's connecting to your localhost machine, but it does connect to another server through the tunnel.

enter image description here

Go to SSH-> Tunnels

enter image description here

After clicking Save

Make sure the MySQL server is disabled on the Windows machine.
I am using MySQL System Tray Monitor

.

When I right click, I see all the options.

enter image description here

click Open

enter image description here

If you have done all the settings for SSH

on ubuntu right, it should appear here. (If you DO NOT search for Ubuntu SSH and Putty on the Internet)

enter image description here

Don't forget: now that you are logged into the ubuntu server, you have all root-localhost rights on the server itself, since root @ localhost has all rights, you don't need to create a host and create schema permissions

enter image description here

Now on your Windows computer open MySQL Query Browser

enter image description here

Now connect to localhost (remember that localhost means localhost on ubuntu)
You can use the same settings in Netbeans to connect to Mysql on Ubuntu

<T411>

Open Mysql Query Browser, you can work with your databases in UBUNTU

enter image description here

Netbeans

enter image description here

Once closed, a New Connection Wizard

New connection is created with all databases on Mysql Ubuntu.

enter image description here

Allow remote access to MySQL, Ubuntu

Connect via "PuTTY" and tunnel

Netbeans

More articles: