0

I'm trying to connect to a remote MySQL database for replication.

The database is on a machine that's part of a secured network, so after opening the security enough to allow my replication destination access to the 3306 port I wanted to connect through the CLI tool to check the connection.

However, no matter how I try to connect, I get the following response:

ERROR 1045 (28000): Access denied for user '{user}'@'{IP}' (using password: NO)

Despite what this message says, the user that I've been trying to connect with does have a password set and I do provide it for the CLI.

I've tried passing the password interactively, inline, and through a .cnf file

I've made sure the password is correct, and made sure that the user is expected to come from the IP address of the machine that hosts the replication destination.

I've changed the permissions for the user to ALL in case there was a permission that was denying access.

I've made sure that the replication destination server does not have any users that can log in with a password in case that was affecting default behavior.

None of those have changed the behavior, and unfortunately the only resources I can find online are how to log in if one has forgotten to root password, or how to set the root password after one done a fresh install.

EDIT: These are the commands I've used to try to pass the password to the CLI:

Interactively:

mysql --host={destination IP} --protocol=tcp -u remote_user -p

Inline:

mysql --host={destination IP} --protocol=tcp -u remote_user -p{password}

mysql --host={destination IP} --protocol=tcp --user=remote_user --password={password}

Configuration file:

(Config file {user}@{host}.cnf)
[client]
host={host}
user={user}
password={password}
protocol=tcp

(Command)
mysql --defaults-extra-file=/{user}@{host}.cnf

EDIT 2: The user table of the database I'm looking to connect to contains this relevant row:

| Host                  | User             | Password          | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections |
| {-IP address-}        | azure_user       | {-password_hash-} | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | N          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            |          |            |             |              |             0 |           0 |               0 |                    0 |

Unfortunately, this database is running MySQL v.5.1 and so does not contain all of the fields requested. The identifying information has been removed, but the IP is correct for the machine that I am trying to access the database from and so is the username. The password hash was set using ALTER USER 'azure_user'@'{-IP address-}' IDENTIFIED BY '{password}'.

9
  • 1
    Please show how you supplied the password. Because you're obviously not doing it correctly.
    – Barmar
    Mar 8 at 20:17
  • You might want to look at other questions like unix.stackexchange.com/questions/205180/…
    – Progman
    Mar 9 at 9:05
  • Thanks for the link @Progman ! I've changed the commands I've tried to reflect the correct format shown in the answers. Unfortunately, no changes in my issue after trying again with the proper format for the CLI or .cnf file
    – Spencer Ke
    Mar 11 at 15:42
  • "the user is expected to come from the destination IP" -- this sounds confusing. Did you mean to say "source IP"? When you select that user's row(s) from mysql.user, what does it show?
    – mustaccio
    Mar 11 at 19:33
  • 1
    Not showing the actual values is practically useless, but one thing I'd like to mention is that the Host column should contain the host the user is connecting from.
    – mustaccio
    Mar 11 at 23:45

1 Answer 1

0

The source of this issue was vastly different versions of MySQL.

The remote server was using MySQL version 5.1.73 and the client I was trying to log in from was using version 8.0.35. Once I downgraded the client to 5.5 I was able to log in with no issues whatsoever.

Thanks to @Damien-Layershift for bringing up their experience with mismatched client version!

Your Answer

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

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