mysql access denied for user ‘root’@’localhost’ (using password yes)
First, you need to execute “FLUSH PRIVILEGES;” before executing any GRANT/CREATE/SET PASSWORD statement,
Possible cases:
- Wrong password
- connecting wrong Database or database not exist
- insufficient privileges for the user to access the database,
- user who doesn’t exist on the MySQL server
- User exists but client host does not have permission to connect:
- No global privilege
skip-grant-tables (last step)
allows anyone to log in from anywhere and can do anything on the database.
nano /etc/mysql/my.cnf
edit:
[mysqld]
skip-grant-tables
service mysql restart
or
sudo /etc/init.d/mysql stop
sudo mysqld_safe –skip-grant-tables &
~$ mysql -u root
SHOW GRANTS FOR ‘zzz’@’127.0.0.1’;
password Authentication plugin maybe different
note: mysql 8 removed password column in mysql.users table
SELECT user,authentication_string,plugin,host FROM mysql.user;
ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘Current-Root-Password’;
FLUSH PRIVILEGES;
Updating from auth_socket to mysql_native_password fixed my problem
/etc/mysql/my.cnf
bind-address = 0.0.0.0
cause 1 wrong password
SELECT Host, User, authentication_string, PASSWORD(‘forgotten’) FROM mysql.user WHERE User=’newrelic’;
SELECT Host, User, authentication_string, PASSWORD(Raazk23@#123) FROM mysql.user WHERE User=’newrelic’;
SELECT Host, User, authentication_string, PASSWORD FROM mysql.user WHERE User=’newrelic’;
PASSWORD(‘forgotten’) hash does not match the authentication_string column, which means password string=’forgotten’ is not the correct password to log in.
solution: Change or update MySQL user password using UPDATE statement
USE mysql;
UPDATE user SET password = PASSWORD(‘dolphin’) WHERE user = ‘dbadmin’ AND host = ‘localhost’;
FLUSH PRIVILEGES;
from 5.7mysql only use authentication string removed password column
USE mysql;
UPDATE user SET authentication_string = PASSWORD(‘Raazk23@#123’) WHERE user = ‘newrelic’ AND host = ‘localhost’;
FLUSH PRIVILEGES;
OR
ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘insert_password’;
Solution 2: Grant all required privileges or permissions to user
error executing `SHOW GLOBAL VARIABLES`: Error 1045: Access denied for user
GRANT ALL PRIVILEGES ON . to ’-your_name-’@’your-hostname’ WITH GRANT OPTION;
FLUSH PRIVILEGES;
Case 3: client host does not have permission to connect:
hosts:
localhost, same machine
127.0.01 = same machine
‘%’ = from anywhere
possible solution 1 : Allow remote ip connections
mysql> SHOW VARIABLES LIKE ‘skip_networking’;
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| skip_networking | OFF |
+—————–+——-+
bind address = 127.0.0.1, ip1, ip2
mysql access denied for user ‘root’@’localhost’ (using password no) linux
- there maybe a password for mysql user and you not entered.
- Possible all other errors with using password no
error 1045 mysql 28000 linux in phpmyadmin
useraccounts>>select user & edit previligios>> change password.
or Databases>>mysql>>users table>. select users>> and change the password column with native password plugin. authentication type md5.
check wordpress password reset using phpmyadmin.
mysql password working shell client but not from the script client
- authentication method that the plugin implements may or may not use a password stored in the user table.
- Passwords stored in the user table are encrypted using plugin-specific algorithms
- ASCII characters (A-Z, 0-9, and dashes (-))vs non-ASCII characters>> client applications should call the mysql_options()
Set a password in ASCII and try
UPDATE user SET authentication_string = PASSWORD(‘Raazk23@#123’) WHERE user = ‘newrelic’ AND host = ‘localhost’;
mysql -u root -p
UPDATE user SET authentication_string = PASSWORD(‘Raazk23-234’) WHERE user = ‘newrelic’ AND host = ‘127.0.0.1’;
or
mysql> ALTER USER ‘newrelic’@’127.0.0.1’ IDENTIFIED WITH mysql_native_password BY ‘Raazk23-234’;
Query OK, 0 rows affected (0.01 sec)