Mysql Ubuntu Remote Connections

Question

For some reason, I was not able to remotely connect to my MySQL server. I tried everything and I still get errors.

root@server1:/home/administrator# mysql -u monty -p -h www.ganganadores.cl Enter password: ERROR 1045 (28000): Access denied for user 'monty'@'server1.ganganadores.cl' (using password: YES) 

Now I tried to run

  `GRANT ALL ON *.* to monty@localhost IDENTIFIED BY 'XXXXX'; GRANT ALL ON *.* to monty@'%' IDENTIFIED BY 'XXXXXX';` 

and nothing else! What am I doing wrong?

EDIT: my.cnf commented on the ip binding.

Answer 1

To open MySQL for anything other than local, you need the following line

For mysql version 5.6 and below

without commenting in /etc/mysql/my.cnf and assigned to your computers an IP address, not a loopback

For mysql version 5.7 and higher

without commenting in /etc/mysql/mysql.conf.d/mysqld.cnf and assigned to your computers an IP address, not a loopback

 #Replace xxx with your IP Address bind-address = xxx.xxx.xxx.xxx 

Or add bind-address = 0.0.0.0 if you do not want to specify IP

Then stop and restart MySQL with the new entry my.cnf. After starting, go to the terminal and enter the following command.

 lsof -i -P | grep :3306 

Something like this with your actual xxx's IP address

 mysqld 1046 mysql 10u IPv4 5203 0t0 TCP xxx.xxx.xxx.xxx:3306 (LISTEN) 

If the above operator returns correctly, you can accept remote users. However, for a remote user to connect with the correct privileges, you need this user to create both a local host 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; 

If you do not have the same user that was created as described above, when you log in locally, you can inherit the basic privileges of localhost and have access problems. If you want to restrict myuser access, then you will need to read the syntax of the GRANT statement HERE. If you go through all this and still have problems, add an additional output error and the corresponding my.cnf lines.

NOTE. If lsof is not returned or not found, you can install it HERE based on your Linux distribution. You do not need lsof to make everything work, but it is very convenient when everything does not work as expected.

Answer 2

Add a few points on top of the apesa superb post:

1) You can use the command below to check the ip address that mysql server is listening on

 netstat -nlt | grep 3306 sample result: tcp 0 0 xxx.xxx.xxx.xxx:3306 0.0.0.0:* LISTEN 

2) Use FLUSH PRIVILEGES to force the loading of grant tables if for some reason the changes are not effective immediately

 GRANT ALL ON *.* TO 'user'@'localhost' IDENTIFIED BY 'passwd' WITH GRANT OPTION; GRANT ALL ON *.* TO 'user'@'%' IDENTIFIED BY 'passwd' WITH GRANT OPTION; FLUSH PRIVILEGES; EXIT; 

3) If the netfilter firewall is enabled ( sudo ufw enable ) on the mysql server, follow these steps to open port 3306 for remote access:

 sudo ufw allow 3306 

check status with

 sudo ufw status 

4) Once the remote connection is established, it can be checked on the client or server computer using the commands

 netstat -an | grep 3306 netstat -an | grep -i established 

Answer 3

MySQL only listens on localhost, if we want to enable remote access to it, then we need to make some changes to the my.cnf file:

 sudo nano /etc/mysql/my.cnf 

We need to comment out the anchor lines and skip-external locks:

 #bind-address = 127.0.0.1 # skip-external-locking 

After making these changes, we need to restart the mysql service:

 sudo service mysql restart 

Answer 4

When testing on Windows, be sure to open port 3306.