Enable remote MySQL connection ERROR 1045 28000 Access denied for user

Question

MySQL 5 1 31 running on Windows XP   From the local MySQL server  192 168 233 142  I can connect as root as follows    gt mysql --host 192 168 233 142 --user root --password redacted   From a remote machine  192 168 233 163   I can see that the mysql port is open     telnet 192 168 233 142 3306 Trying 192 168 233 142    Connected to 192 168 233 142  192 168 233 142     But when trying to connect to mysql from the remote machine  I receive     mysql --host 192 168 233 142 --user root --password redacted ERROR 1045  28000   Access denied for user  root   192 168 233 163   using password  YES    I have only 2 entries in mysql user   Host         User     Password -------------------------------------- localhost    root      blahblahblah              root      same as above    What more do I need to do to enable remote access   EDIT  As suggested by Paulo below  I tried replacing the mysql user entry for   with an IP specific entry  so my user table now looks like this   Host             User     Password ------------------------------------------ localhost        root      blahblahblah 192 168 233 163  root      same as above    I then restarted the machine  but the problem persists

User · Accepted Answer

Paulo s help lead me to the solution   It was a combination of the following    the password contained a dollar sign I was trying to connect from a Linux shell   The bash shell treats the dollar sign as a special character for expansion to an environment variable  so we need to escape it with a backslash  Incidentally  we don t have to do this in the case where the dollar sign is the final character of the password   As an example  if your password is  pas word   from Linux bash we must connect as follows     mysql --host 192 168 233 142 --user root --password pas  word

User · Answer

By default in MySQL server remote access is disabled  The process to provide a remote access to user is    Go to my sql bin folder or add it to PATH Login to root by mysql -uroot -proot  or whatever the root password is   On success you will get mysql gt  Provide grant access all for that user      GRANT ALL PRIVILEGES ON     TO  username   IP  IDENTIFIED BY  password     Here IP is IP address for which you want to allow remote access  if we put   any IP address can access remotely   Example   C  Users UserName gt  cd C  Program Files  x86  MySQL MySQL Server 5 0 bin  C  Program Files  x86  MySQL MySQL Server 5 0 bin gt mysql -uroot -proot  mysql gt  GRANT ALL PRIVILEGES ON     TO  root      IDENTIFIED BY  root   Query OK  0 rows affected  0 27 sec   mysql gt  FLUSH PRIVILEGES  Query OK  0 rows affected  0 25 sec    This for a other user   mysql gt  GRANT ALL PRIVILEGES ON     TO  testUser      IDENTIFIED BY  testUser   Query OK  0 rows affected  0 00 sec   mysql gt  FLUSH PRIVILEGES  Query OK  0 rows affected  0 00 sec    Hope this will help

User · Answer

The user host combination may have been created without password   I was assuming that when adding a new host for an existing user  using a GUI app   the existing password would also be used for the new user host combination   I could log in with  mysql -u username -p PASSWORD   locally  but not from IPADDRESS with  mysql -u --host HOST -p PASSWORD    I could actually log in from IPADDRESS without using a password   mysql -u --host HOST   Setting the password allowed access   set password for   lt USER gt     lt IPADDRESS gt       lt PASSWORD gt

User · Answer

Do you have a firewall   make sure that port 3306 is open   On windows   by default mysql root account is created that is permitted to have access from localhost only unless you have selected the option to enable access from remote machines during installation    creating or update the desired user with     as hostname    example    CREATE USER  krish      IDENTIFIED BY  password

User · Answer

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

User · Answer

MySQL ODBC 3 51 Driver is that special characters in the password aren t handled      Warning     You might have a serious headache with MySQL ODBC 3 51 if the password in your GRANT command contains special characters  such as                MySQL ODBC 3 51 ODBC Driver does not support these special characters in the password box  The only error message you would receive is    Access denied     using password  YES    -   from http   www plaintutorials com install-and-create-mysql-odbc-connector-on-windows-7

User · Answer

I was struggling with remote login to MYSQL for my Amazon EC2 Linux instance  Found the solution was to make sure my security group included an inbound rule for MySQL port 3306 to include my IP address  or 0 0 0 0 0 for anywhere   Immediately could connect remotely as soon as I added this rule

User · Answer

I was getting the same error after granting remote access until I made this   From  etc mysql my cnf  In newer versions of mysql the location of the file is  etc mysql mysql conf d mysqld cnf    Instead of skip-networking the default is now to listen only on   localhost which is more compatible and is not less secure   bind-address             127 0 0 1    comment this line  bind-address    127 0 0 1   Then run service mysql restart

User · Answer

My case is absolutely simple  You may have this problem in case if you type in WRONG password  No create user is needed  user already existed   no other permissions  Basically make sure that the password is correct  So make double-sure the password is correct

User · Answer

New location for mysql config file is   etc mysql mysql conf d mysqld cnf

User · Answer

if you are using dynamic ip just grant access to 192 168 2   so now you dont have to worry about granting access to your ip address every time

User · Answer

In my case I was trying to connect to a remote mysql server on cent OS  After going through a lot of solutions  granting all privileges  removing ip bindings enabling networking  problem was still not getting solved    As it turned out  while looking into various solutions I came across iptables  which made me realize mysql port 3306 was not accepting connections   Here is a small note on how I checked and resolved this issue    Checking if port is accepting connections         telnet  mysql server ip   portNo  Adding ip table rule to allow connections on the port     iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT Would not recommend this for production environment  but if your iptables are not configured properly  adding the rules might not still solve the issue  In that case following should be done   service iptables stop   Hope this helps

User · Answer

Try to flush privileges again  Try to restart server to reload grants  Try create a user with host  192 168 233 163       appears to not allow all  it s weird

[mysql] Enable remote MySQL connection: ERROR 1045 (28000): Access denied for user

Examples related to mysql