mysql new user access denied

Usualy this does the trick for me:

Login to MySQL

mysql -u root -p

Create a MySQL user

CREATE USER 'username'@'ip' IDENTIFIED BY '*password*';

Allow full control to that user

GRANT ALL PRIVILEGES ON * . * TO 'username'@'ip';

Reloading the privileges tables

FLUSH PRIVILEGES;

Leaving the MySQL interface

exit;

Allow external acces from certain ip to port 3306

sudo ufw allow from ip to any port 3306

Go to the MySQL config file

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Allow MySQL to look for connections externally

Change:

bind-address            = 127.0.0.1

To:

bind-address            = 0.0.0.0

In MySQL 8+ this line is not there to begin with, if so add it

Restart MySQL

sudo systemctl restart mysql

I am however for some reason still experiencing the problem 'Acces denied for user', so if anyone could extend on my answer with a solution to my problem that would be amazing.


Run this to work

FLUSH PRIVILEGES

Once you have given the desired privileges for your user, you will need to FLUSH privileges in order to complete the setup and to make the new settings work. To do so, run this command within the SQL command prompt:

[EDIT]
If you want to connect from localhost also, you should create another account.

It is necessary to have both accounts for 'user' to be able to connect from anywhere as 'user'. Without the localhost account, the anonymous-user account for localhost that is created by mysql_install_db would take precedence when 'user' connects from the local host. As a result, 'user'would be treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the 'user'@'%' account and thus comes earlier in the user table sort order. )

FYI: http://dev.mysql.com/doc/refman/5.5/en/adding-users.html


In my case the problem was in password. After making it containing lesser amount of special characters, it started working.

Tags:

Mysql