Menu
Use the ALTER USER command and change the authentication method to log into MySQL as root: ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'insert_password'; This command changes the password for the user root and sets the authentication method to mysql_native_password.
Solution 1: Sudo then Change Password If you get the “access denied” error, one way to solve it is by using sudo to log in to mysql and change the root password. Step 1: Open the command line on your system. Step 3: Enter the password for this account.
To resolve the error, you must create a user with the following command: mysql> GRANT ALL ON *. * to user_name@localhost IDENTIFIED BY 'password'; Replace user_name with the user's username and password with the user's password.
Configuring a default root password for MySQL/MariaDB Use the following procedure to set a root password. To change the root password, type the following at the MySQL/MariaDB command prompt: ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyN3wP4ssw0rd'; flush privileges; exit; Store the new password in a secure location.
If you have that same problem in MySql 5.7.+ :
Access denied for user 'root'@'localhost'
it's because MySql 5.7 by default allow to connect with socket, which means you just connect with sudo mysql. If you run sql :
SELECT user,authentication_string,plugin,host FROM mysql.user;
then you will see it :
+------------------+-------------------------------------------+-----------------------+-----------+
| user | authentication_string | plugin | host |
+------------------+-------------------------------------------+-----------------------+-----------+
| root | | auth_socket | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *497C3D7B50479A812B89CD12EC3EDA6C0CB686F0 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)
To allow connection with root and password, then update the values in the table with command :
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Current-Root-Password';
FLUSH PRIVILEGES;
Then run the select command again and you'll see it has changed :
+------------------+-------------------------------------------+-----------------------+-----------+
| user | authentication_string | plugin | host |
+------------------+-------------------------------------------+-----------------------+-----------+
| root | *2F2377C1BC54BE827DC8A4EE051CBD57490FB8C6 | mysql_native_password | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *497C3D7B50479A812B89CD12EC3EDA6C0CB686F0 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)
And that's it. You can run this process after running and completing the sudo mysql_secure_installation command.
For mariadb, use
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('manager');
to set password. More at https://mariadb.com/kb/en/set-password/
Use the instructions for resetting the root password - but instead of resetting the root password, we'll going to forcefully INSERT a record into the mysql.user table
In the init file, use this instead
INSERT INTO mysql.user (Host, User, Password) VALUES ('%', 'root', password('YOURPASSWORD'));
GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;
It didn't like my user privilege so I SUDO it. (in bash << sudo set user and password) (this gives username of root and sets the password to nothing) (On Mac)
sudo mysql -uroot -p
Try the following commands
~$ sudo /etc/init.d/mysql stop
~$ sudo mysqld_safe --skip-grant-tables &
~$ mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
mysql> use mysql;
mysql> update user set password=PASSWORD("root") where User='root';
mysql> flush privileges;
mysql> quit
~$ sudo /etc/init.d/mysql stop
Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended
[1]+ Done mysqld_safe --skip-grant-tables
~$ sudo /etc/init.d/mysql start
~$ mysql -u root -p
* MySQL Community Server 5.6.35 is started
~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
for the people who are facing below error in mysql 5.7+ version -
Access denied for user 'root'@'localhost' (using password: YES)
Open new terminal
sudo /etc/init.d/mysql stop
...
MySQL Community Server 5.7.8-rc is stopped
sudo mysqld_safe --skip-grant-tables &
this will skipp all grant level privileges and start the mysql in safe mode
Sometimes the process got stucked just because of
grep: write error: Broken pipe 180102 11:32:28 mysqld_safe Logging to '/var/log/mysql/error.log'.
Simply press Ctrl+Z or Ctrl+C to interrupt and exit process
mysql -u rootWelcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.8-rc MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
use mysql;
Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update user set authentication_string=password('password') where user='root';
Query OK, 4 rows affected, 1 warning (0.03 sec)
Rows matched: 4 Changed: 4 Warnings: 1
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
sudo /etc/init.d/mysql stop
..180102 11:37:12 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended . * MySQL Community Server 5.7.8-rc is stopped arif@ubuntu:~$ sudo /etc/init.d/mysql start .. * MySQL Community Server 5.7.8-rc is started
mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.8-rc MySQL Community Server (GPL)
after mysql 5.7+ version the column password replaced by name authentication_string from the mysql.user table.
hope these steps will help anyone, thanks.
I was using ubuntu 18 and simply installed MySQL (password:root) with the following commands.
sudo apt install mysql-server
sudo mysql_secure_installation
When I tried to log in with the normal ubuntu user it was throwing me this issue.
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
But I was able to login to MySQL via the super user. Using the following commands I was able to log in via a normal user.
sudo mysql
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
exit;
Then you should be able to login to Mysql with the normal account.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With