CODEWITHSUNDEEP
mysql
Ryan
Ryan

Reputation: 2053

mysql new user access denied

Just started with mysql. I login with root and follow the online reference to create a new user:

mysql> CREATE USER 'abc'@'%' IDENTIFIED BY '111111';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'abc'@'%' WITH GRANT OPTION;

then I quit and tried to login with the new user:

mysql --user=abc --password=111111 mysql

But got an error message:

Access denied for user 'abc'@'localhost' (using password: YES)

Did I missed something?

Upvotes: 21

Views: 35974

Answers (6)

Eric
Eric

Reputation: 469

If you run MySQL version 8 or higher using Docker image, you should downgrade the version. I tried MYSQL 5.7 image and then I was able to connect with a user(not root) from remote network.

Upvotes: -1

BaiJiFeiLong
BaiJiFeiLong

Reputation: 4609

Change host from '%' to 'localhost' worked for me.

However I do not know why.

Remember to 'flush privileges;'

I am using mysql Ver 15.1 Distrib 10.1.32-MariaDB, for Linux (x86_64) using readline 5.1

Upvotes: 2

user109764
user109764

Reputation: 654

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

Upvotes: 5

ii iml0sto1
ii iml0sto1

Reputation: 1742

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.

Upvotes: 3

Daniel Wondyifraw
Daniel Wondyifraw

Reputation: 7713

This is it. 

grant all privileges on *.* to `root`@`%` identified by 'chnagethepasswordhere'; 
flush privileges;
--helps to excute store procedure as root user

GRANT EXECUTE ON PROCEDURE mbu4u.ratingProcedure TO 'root'@'ip.ip.ip.ip' identified by 'changepasswordheere';

GRANT ALL ON mbu4u.* TO root@'ip.ip.ip.ip' IDENTIFIED BY 'changepassword';

dantheman @have a nice day!

Upvotes: -2

Venu
Venu

Reputation: 7279

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

Upvotes: 25

Related Questions