0

Running MySql server 8.0.31 on an Amazon EC2 instance (52.99.189.74) in Ubuntu 22.04. When I log in to the mysql prompt with sudo, this is how the user and host columns of the mysql.user table look like.

+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| della            | %         |
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)

My Ubuntu user name, i.e. $USER is della. However, when I try this as a non-root user, I get the following output.

$ mysql -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'della'@'localhost' (using password: YES)

Should not the first row of the mysql.user table be resolved to represent my correct user credential, as my understanding was the % sign is a wildcard to represent any hostname and ip address?

In the same way, if I am using a laptop (also running Ubuntu, and my username being della), and I want to login to the same mysql server remotely, can I use this, to resolve to the same user?

$ mysql --user "della"  --host 52.99.189.74 --port 3306 -p 

But this is also giving the exact same error. Things I have tried,

  • Allow inbound traffic to port 3306 in the EC2 security group with the following protocols from any ip address. This is how the rules look like.

    IPv4 MYSQL/Aurora TCP 3306 0.0.0.0/0

  • Edited /etc/mysql/mysql.conf.d/mysqld.cnf to have the following parameters.

    bind-address = 0.0.0.0

    mysqlx-bind-address = 127.0.0.1

Anything else I need to do to get to the mysql prompt from the ec2 localhost (as a non-root user) and also remotely from my laptop?

1 Answer 1

1

In MySQL, % is a wildcard to represent any hostname or wildcard, except localhost.

MySQL does something uncommon: localhost is treated as a special name, and it does not mean what localhost does to other applications. For MySQL, when you connect using localhost, it means to connect via the UNIX domain socket instead of TCP/IP. The UNIX domain socket is very fast, but it only works if the client is on the local machine with the MySQL Server.

When you create users in the privileges tables, della@localhost is effectively a different user than della@%. They can have different privileges and different passwords. To allow clients to use either TCP/IP or the UNIX domain socket, you would have to create two users.

Or you could specify a TCP/IP host when you connect using a local client. That will make it match della@%.

$ mysql -h 127.0.0.1 -p

TCP/IP (even on the loopback interface) is slightly slower than the UNIX domain socket, but when using the database interactively on the same instance, the difference is so small you won't notice.

Most of the world treats "127.0.0.1" as synonymous with "localhost" but MySQL treats them differently.

2
  • Sorry, mysql -h 127.0.0.1 -p is throwing the same error ERROR 1045 (28000): Access denied. Also, mysql --user della --host 52.99.189.74 -p should work from my laptop, right, if it resolves to the first row of the table? That is also throwing the same error.
    – Della
    Nov 23, 2022 at 1:33
  • 1
    The most likely explanation is that you're using the wrong password. Nov 23, 2022 at 1:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.