Unable to setup remote connections MYSQL Ubuntu

Question

I'm having trouble opening a up my MYSQL server to ALL remote connections. I have followed many online guides and appear to have something wrong. Perhaps SO could provide guidance? My server details are as follows:

• Ubuntu 12.04 Server,
• MYSQL Ver 14.14 Distrib 5.5.34, for debian-linux-gnu (x86_64) using readline 6.2

/etc/mysql/my.cnf: Other stuff too, but importantly the bind-address...

bind-address = 0.0.0.0

my.conf has the following permissions: -rw-r--r-- 1 root root 3516 Jan 31 17:12 my.cnf

The server isn't blocked because: telnet myDomain.com 3306 prompts for my native mysql password.

MYSQL Queries

CREATE USER 'myUser'@'%' IDENTIFIED BY 'myPASSWORD';

GRANT INSERT ON db.table_v TO 'myUser'@'%' IDENTIFIED BY 'myPASSWORD';

FLUSH PRIVILEGES;

Permissions from show grants for 'myUser'@'%';

GRANT USAGE ON . TO 'myUser'@'%' IDENTIFIED BY PASSWORD '****************'

GRANT INSERT ON db.table_v TO 'myUser'@'%'

I also restarted my server

PROBLEM:

mysql -h myDomain.com -u myUser -p

Enter password:

ERROR 1045 (28000): Access denied for user 'myDomain'@'***MYIPADDRESS***' (using password: YES)

I am also not able to login locally with any user where the host is not specifically local, such as '%' or my home IP.

Answer 1

this is interesting if not typo:

Access denied for user 'myDomain' 

are you trying to use your domain name as username? there should be myUser instead of myDomain cause you give remote permissions to myUser only.

also in:

mysql -h myDomain.com -u myUser -p

-h means the host where mysql server resides, not your user's current host. so trying anything other then "localhost" when you're locally trying to connect the database surely gives error cause you are trying to connect a database you are not intend to. suppose you are connected to mysql server locally and typed the above line; this command will try to connect the server at myDomain.com with the user myUser and password you provide. if the permissions you defined are where you run this command, you'll get that access denied error cause you are connecting to a different server.

Answer 2

I think the problem is the location of the .cnf file where you specify the bind-address attribute. Most guides and tutorials say the file is /etc/mysql/my.cnf but after many tries I realized that the correct place was in mysqld.cnf file under mysqld profile.

Try to include the bind-address attribute there. To check if this worked for you type "netstat -lt" and find if the local address of mysql process is 0.0.0.0:3306.

Then check again yor connection

Answer 3

You need also create local user account

CREATE USER 'myUser'@'localhost' IDENTIFIED BY 'myPASSWORD';

MySQL Manual | Adding users

I don't advise usage such unsecure statements as

GRANT ALL ON *.* TO 'myuser'@'%';

It creates superuser e.g. grants privileges to all databases for all operations.

Answer 4

May I ask why you need direct access to MySQL? MySQL should really only listen to localhost, but you can access easily using SSH.

If using Windows, Putty allows you to establish an SSH connection to the host, and then forward 3306 localhost traffic to your own computer.

Better still, programs like SQLyog allow SSH tunnel connections to MySQL which is how I usually connect (unless the servers are on a VPN).

Are you using this server for development or does it have a public IP address?

Answer 5

please post the result of

nmap ***MYIPADDRESS***

and

nmap localhost

and

nmap MyDomain

if you do not have nmap install it from apt, then I can help you more.