Related

Product Now Available: Managed MySQL Databases Product
Install MySQL on New Droplet Question Question
How can I solve error "SQLSTATE[HY000] [2002] Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)" Question Question

Question

Remote login to mysql with error 1045

Posted October 26, 2020 309 views
MySQL

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.

Add a comment
Timothy2
By:
Timothy2

Related

Product Now Available: Managed MySQL Databases Product
Install MySQL on New Droplet Question Question
How can I solve error "SQLSTATE[HY000] [2002] Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)" Question Question

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.

×
Submit an Answer
3 answers
Timothy2 October 26, 2020

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.

Reply Report
pistle2020 October 26, 2020

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.

Reply Report
  • Timothy2 October 26, 2020

    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
    Reply Report
Timothy2 October 27, 2020

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.

Reply Report

Related

Looking for something else?

Ask a new question Search for more help