Can't access remote MySQL

user_16518141 0
Viewed 82

I got a VPS with CentOS, the only thing I have done is install MySQL for testing purposes.

When trying to connect from my pc I got this:

mysql -u root  -p -h [REMOTE-IP]
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'fixed[MyIP].totalplay.net' (using password: NO)

Weirdly it says using password=no, even when I put the -p and I got prompt it for it.

I did mysql_secure_installation:

  1. Changed root password
  2. Allowed root remote connection(testing, so no biggies there)

Still no luck.

I know I'm hitting the remote MySQL because I got "ERROR 1045 (28000)", nonetheless I checked:

1 Netstat on remote server, showing that is listening on 3306(OOTB port) and bind-address is on 0.0.0.0 ( allow anywhere)

netstat -tuplen | grep 3306
tcp        0      **0 0.0.0.0:3306            0.0.0.0:***               LISTEN      27         14923343   12002/mysqld

2 Telnet from local to remote to check that port is open and reachable

  telnet [REMOTE-IP] 3306         
    Trying [REMOTE-IP]...
    Connected to [REMOTE-IP]

I checked the user table

mysql> select user,host from mysql.user where user='root';
+------+-------------------------------------+
| user | host                                |
+------+-------------------------------------+
| root | %                                   |
| root | fixed[My-IP].totalplay.net |
| root | localhost                           |
+------+-------------------------------------+
3 rows in set (0.00 sec)

The users were create this way:

CREATE USER 'root'@'%' IDENTIFIED BY 'PASS'; CREATE USER 'root'@'fixed[My-IP].totalplay.net' IDENTIFIED BY 'PASS';

The one @localhost was of course already created

So the user does exists, checked the grants:

+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+-------------------------------------------------------------------
mysql> show grants for root;
+-------------------------------------------------------------+
| Grants for root@%                                           |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
1 row in set (0.00 sec)


 mysql> show grants for 'root'@'fixed[My-IP].totalplay.net';
+--------------------------------------------------------------------+
| Grants for root@fixed[My-IP].totalplay.net                |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'fixed[My-IP].totalplay.net' |
+--------------------------------------------------------------------+
1 row in set (0.01 sec)

I also:

1 Created another user @ my ip, fixed[My-IP].totalplay.net and '%', granted privileges; same issue

2 Turned off firewall

3 Tried to connect using python, got similar results

mysql.connector.errors.ProgrammingError: 1045 (28000): Access denied for user 'root'@'fixed[MyIP].totalplay.net' (using password: YES)

The only difference is that now it says I'm using password.

My /etc/my.cnf

cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
performance-schema=0
bind-address=0.0.0.0
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
innodb_buffer_pool_size=25165824
max_allowed_packet=268435456
open_files_limit=40000
innodb_file_per_table=1

Thanks for the help!

mysql
0 Answers
Related