Access Denied for User ‘root’@’localhost’ (using password: YES) — No Privileges?
I am continuously receiving this error. I am using mySQL Workbench and from what I am finding is that root’s schema privileges are null. There are no privileges at all. I am having troubles across platforms that my server is used for and this has been all of a sudden issue. root@127.0.0.1 apparently has a lot of access but I am logged in as that, but it just assigns to localhost anyways — localhost has no privileges. I have done a few things like FLUSH HOSTS , FLUSH PRIVILEGES , etc but have found no success from that or the internet. How can I get root its access back? I find this frustrating because when I look around people expect you to «have access» but I don’t have access so I can’t go into command line or anything and GRANT myself anything. When running SHOW GRANTS FOR root this is what I get in return:
restart server with —skip-grant-table and login with any user and provide full privileges to root and restart server again without the above option
28 Answers 28
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; +------------------+-------------------------------------------+-----------------------+-----------+ | 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.
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('manager'); This was absolutely infuriating trying to figure out and a giant waste of time. I wish they asked this during mysql_secure_installation . Thank you so much for this answer.
I did this but and the table entry changed the same way but password still not working. Also, sudo mysql_secure_installation prompts for password too.
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; I’m sorry Andy, but when I get to step 6 I keep running into a problem. Since I did use an installation wizard, I would be using «C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe» —defaults-file=»C:\\Program Files\\MySQL\\MySQL Server 5.6\\my.ini» —init-file=C:\\mysql-init.txt Everything is where it is supposed to be and what you have above I did put in the file accordingly. I do have the service stopped as well. I just get this output: prntscr.com/1ilxau And everything (plugins) shutdown. Really confusing.
At my stupidity surprise, I wasn’t. After I was, I got this prntscr.com/1imcnj — The first time I did it on their own lines (from paste) then I put it all on one line. I’m sorry this is just so confusing for me, not the sql expert per se.
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> ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO): что делать?
Решил, что пора изучать БД, и споткнулся, еще даже не сумев запустить БД.
В видеокурсе показано было следующее:
sudo apt-get install mysql-server
mysql -u root -p
Автор ввел пароль и спокойно зашел. Я получил ошибку, указанную в заголовке.
Начал гуглить и наткнулся примерно на следующие советы:
Совет 1: заходить через mysql -u root, то есть не добавлять -p. Не помогло, ошибка прежняя.
Совет 2: mysqladmin -u root password [newpassword]. То же самое.
Сложный 9 комментариев
Konstantin Malyarov: он его даже не просит при таком запросе:
anton@anton-X451CA:~/studyDB$ mysql -h localhost -u root
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)
Тут два варианта, ИМХО.
1) Вы что-то не так поняли из курса:
2) Составитель курса что-то упустил.
В любом случае или стоило бы сюда ссылку кинуть на этот курс или писать составителю.
По проблеме. Сервер mysql пишет вам, что пользователю ‘root’ доступ закрыт. Как мне кажется, нужно вначале создать бд, применить схему и там создастся пользователь, с данными которого вы подключитесь к бд. А слова «Using password: NO» означает лишь, что пароль и не использовался.
Ну вот у меня та же проблема.
$mysql
ERROR 1045 (28000): Access denied for user ‘rni’@’localhost’ (using password: NO)
А так пожалуйста
$ sudo mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.32-0ubuntu0.22.04.2 (Ubuntu)
А хотелось бы просто заходить без sudo
Это может быть, если пароль не был задан при установке.
Порядок действий для установки/смены пароля root в mysql следующий:
1. Остановить mysql:
sudo service mysql stop
2. Запустить сервис со следующими параметрами:
sudo mysqld —skip-grant-tables —user=root
Если выдал ошибку то в файле /etc/mysql/mysql.conf.d/mysqld.cnf в секцию [mysqld] добавить строчку skip-grant-tables и выполнить sudo service mysql restart
3. После этого подключиться к mysql командой:
mysql -u root
4. Обновить пароль root’a:
UPDATE mysql.user SET authentication_string=PASSWORD(''), plugin='mysql_native_password' WHERE User='root' AND Host='localhost'; FLUSH PRIVILEGES;5. И перезапустить сервис:
sudo service mysql restart
Если на шаге 2 вы добавляли skip-grant-tables в /etc/mysql/mysql.conf.d/mysqld.cnf — удалить эту строчку.
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
Я не запускал команду
sudo mysqld —skip-grant-tables —user=root
вручную отредактировал файл /etc/mysql/mysql.conf.d/mysqld.cnf , добавив skip-grant-tables
и затем просто запустил
mysqld -u root
fapchat, У меня тоже такое было. Повторил сначала, предварительно добавив строчку skip-grant-tables в /etc/mysql/mysql.conf.d/mysqld.cnf
Пароль по умолчанию пустой.
Возможно, вы неправильно набрали команду. Скопируйте именно эту: mysql -u root -p . На запрос пароля надо просто нажать Enter.
Попробуйте запустить mysql_secure_installation .
Если все равно не пускает — поищите пароль в логе: sudo grep ‘temporary password’ /var/log/mysqld.log .
Если и этот вариант не подошел — возможно, устанавливаете из какого-то левого репозитория. Удалите sudo apt-get purge mysql* , выключите левые репозитории и установите заново sudo apt-get install mysql-server .
Почему-то не получается отредактировать предыдущий комментарий.
Ни одна из команд не помогла. первая дала такой же результат, mysql_secure_installation выдала следующее:
anton@anton-X451CA:~/studyDB$ mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root:
В итоге та же самая ошибка. Оставлял поле пароля пустым, без толку.
Идаже удалить вашей командой не получается:
anton@anton-X451CA:~/studyDB$ sudo apt-get purge mysql*
Чтение списков пакетов… Готово
Построение дерева зависимостей
Чтение информации о состоянии… Готово
E: Не удалось найти пакет mysqladmin
Удалил командой sudo apt-get remove mysql-server и поставил заново тот же пакет. Результат тот же.
MySQL Access denied for user ‘root’@’localhost’
Check this page for possible causes of this error and workarounds to fix this error. rathishkumar.in/2017/04/…. It may help you.
7 Answers 7
If you’re running n localhost, just type the command below in terminal:
If you’re using an external server, enter the host IP (xxx.xxx.xxx.xxx) as well:
mysql -hxxx.xxx.xxx.xxx -uroot -p You will be prompted for your password, enter it and you will be able to access your MySQL prompt.
You can also look at this answer on how to reset your MySQL password.
In the answer I linked to, you have to replace the 5.x at the end with the version of the MySQL server you’re using. I’m using 5.5 so my command will be sudo dpkg-reconfigure mysql-server-5.5
so, the problem is that OP sees Access denied for user ‘root’@’localhost’ . You suggest calling mysql -u root -p . The result of this command is: Access denied for user ‘root’@’localhost’ .
I get the same symptoms when I update, but for me the fix (after killing mysql and restarting with —skip-grant-tables to get in) is to execute
update mysql.user set plugin = ''; The update process likes to set this column to «unix_socket». I don’t know what that is supposed to accomplish, but for me it breaks everything.
yes, I had run hardening script and it changed the plugin to mysql_socket so whatever I was set to using PASSWORD(‘xxxxxx’) , was not working.
I solved my problem with this:
sudo apt-get remove --purge mysql-server mysql-client mysql-common sudo apt-get autoremove sudo apt-get autoclean sudo apt install mysql-server Be extremely responsible. With this solution, you are removing MySql server and all of its data.
The same as Ulrich Metzger, after upgrading my machine to Ubuntu 16.04 and Mysql-server-5.7, I couldn’t log in with root because the plugin column changed.
My problem was not solved with sudo dpkg-reconfigure mysql-server-5.7 + removing + purging + cleaning.
I had to stop the mysql service:
Then restart the mysql daemon with the —no-grant-tables option:
# sudo mysqld_safe —no-grant-tables &
Then in another terminal, enter the mysql console (which now doesn’t need authentication) with the command mysql , and update the password and plugin columns via a SQL UPDATE:
UPDATE mysql.user SET authentication_string=PASSWORD(''), plugin='mysql_native_password' WHERE User='root' AND Host='localhost'; Finally, kill the mysqld_safe command, restart mysql service and connect to mysql normally:
# sudo service mysql start # mysql