I have set up mysql remote login through a tutorial here. It was working for a while. And it suddenly stops working.

bound to 0.0.0.0

root@ubuntu20:~# netstat -plant | grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1026/mysqld
tcp6 0 0 :::33060 :::* LISTEN 1026/mysqld

root@ubuntu20:~# ufw status
To Action From


3306/tcp ALLOW Anywhere

I can log in locally with the remote user, dev.

mysql> SELECT User, Host FROM mysql.user;
+——————+———–+
| User | Host |
+——————+———–+
| dev | % |

When I try to remote login with the same user, I get error 1045.

What is the problem? I didn’t do anything to the server. It just stops working.

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
3 answers

FYI - I have a ubuntu 20 and ubuntu 18 droplets with the exact remote login setup. The ubuntu 18 remote login still works. Just doesn’t work on ubuntu 20 anymore.

You have to put this as root:

GRANT ALL PRIVILEGES ON . TO ‘USERNAME’@'IP’ IDENTIFIED BY 'PASSWORD’ with grant option;
;

where IP is the IP you want to allow access, USERNAME is the user you use to connect, and PASSWORD is the relevant password.

If you want to allow access from any IP just put % instead of your IP

and then you only have to put

FLUSH PRIVILEGES;
Or restart mysql server and that’s it.

  • Yes, I have done all that. That is why the remote login was working before.

    SELECT User, Host FROM mysql.user;
    +——————+———–+
    | User | Host |
    +——————+———–+
    | dev  | %    |
    
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `dev`@`%` WITH GRANT OPTION
    
    GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `dev`@`%` WITH GRANT OPTION
    

I end up using ssh tunnel + localhost login in the client app to get around the problem. Still don’t know why it stops working and how to get it working again.

Submit an Answer