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

Question

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.

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.

By default in MySQL server remote access is disabled. The process to provide a remote access to user is.

1. Go to my sql bin folder or add it to PATH
2. Login to root by mysql -uroot -proot (or whatever the root password is.)
3. On success you will get mysql>
4. 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> cd C:\Program Files (x86)\MySQL\MySQL Server 5.0\bin

C:\Program Files (x86)\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root';
Query OK, 0 rows affected (0.27 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.25 sec)

This for a other user.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'testUser'@'%' IDENTIFIED BY 'testUser';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Hope this will help

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