Linux mysql root access denied

Ошибка access denied for user root localhost

Если вы захотите настроить резервное копирование базы данных на другой сервер, или протестировать соединение с базой данных из другого сервера. И тогда вы можете столкнуться с ошибкой access denied for user root localhost, даже если вы указали верное имя пользователя, базу данных и пароль.

В этой небольшой статье мы рассмотрим почему возникает эта ошибка, а также как ее исправить и подключиться к MySQL из другого сервера через интернет.

Что означает access denied for user root localhost?

Если переводить дословно, то эта ошибка означает что у вас нет доступа к данной базе данных от имени этого пользователя. В примере я использовал пользователя root, но вы можете использовать и другого пользователя. Это может быть вызвано несколькими причинами:

• Пароль введен неверно;
• По каким-либо причинам у пользователя нет прав на доступ к базе данных;
• В настройках этого пользователя запрещено авторизоваться с этого сервера;

Для безопасности базы данных в mysql была придумана настройка хоста, из которого пользователь может авторизоваться. По умолчанию для пользователей устанавливается разрешение на авторизацию только с localhost. Чтобы разрешить подключение с других хостов, нужно менять настройки. Рассмотрим как это делается с помощью Phpmyadmin и в терминале.

Исправляем ошибку access denied for user root localhost

1. Подключение с другого хоста

Сначала рассмотрим как работать с Phpmyadmin. Это намного проще для начинающих и тех, кто не любит работать в терминале. Откройте Phpmyadmin, авторизуйтесь в программе с правами root и перейдите на вкладку «Учетные записи пользователей»:

Здесь, вы увидите, кроме обычных полей, поле «имя хоста», которое указывает с какого хоста может подключаться пользователь. Если в этом поле написано localhost, значит этот пользователь может авторизоваться только с локальной машины. Также, в этом поле может находиться IP адрес, с которого есть разрешение или символ %, который означает, что пользователь может подключаться с любого IP.

Чтобы изменить права для пользователя, нужно нажать на ссылку «Редактировать привилегии» для него, на открывшейся странице перейдите на вкладку «Информация об учетной записи»:

Затем установите в поле «Имя хоста» значение «Любой хост» чтобы разрешить этому пользователю авторизоваться с любого IP. Если вы хотите разрешить только определенный IP, выберите «Использовать текстовое поле» и укажите нужный адрес или подсеть:

После этого останется нажать кнопку «Вперед» чтобы сохранить настройки. Если вам нужно чтобы был доступ и с локального IP, и с другого, то необходимо создать еще одного пользователя. После этого вы сможете авторизоваться от имени этого пользователя.

Теперь рассмотрим другой способ решить ошибку 1045 access denied for user root localhost, с помощью терминала. Это немного проще, поскольку вам нужно только выполнить несколько команд:

> UPDATE mysql.user SET Host=’%’ WHERE Host=’localhost’ AND User=’имя_пользователя’;
> UPDATE mysql.db SET Host=’%’ WHERE Host=’localhost’ AND User=’имя_пользователя’;
> FLUSH PRIVILEGES;

Уже после этого, вы можете подключаться к серверу баз данных с любого другого компьютера и не получите никаких ошибок. Вместо символа %, можно указать нужный ip или localhost, если ограничение нужно вернуть обратно.

2. Неверный пароль root

Иногда случается, что при установке базы данных пароль для root задается, но вы его не знаете. Поскольку это главный пользователь и если вы не можете войти от его имени, то вы не сможете ничего исправить. Сначала попробуйте авторизоваться от имени root в системе и подключиться к базе без пароля:

Иногда это работает. Если не сработало, остановите службу mysql и запустите ее без проверки безопасности, а затем попробуйте снова:

systemctl stop mysqld
mysqld —skip-grant-tables
mysql

> USE mysql;
> UPDATE user SET Password=PASSWORD(‘ваш_пароль’) where USER=’root’;
> FLUSH PRIVILEGES;

Еще можно попытаться выдать права над всеми таблицами нашему пользователю, если это необходимо:

> GRANT ALL ON *.* TO ‘root’@’localhost’ WITH GRANT OPTION;

Обратите внимание на хост localhost, если вы хотите чтобы удаленные узлы тоже могли подключаться к этому пользователю, то нужно использовать %. Дальше можно перезапустить базу данных и работать как обычно.

Выводы

Теперь вы знаете как решается mysql access denied for user root localhost и что делать в таких ситуациях, чтобы решить проблему. Надеюсь, эта информация была полезной для вас. Если у вас остались вопросы, спрашивайте в комментариях!

Источник

Access denied for root user in MySQL command-line

I’ve just installed xampp, and am using command line to write mySQL. I am using ‘root’ with no password and can connect to mysql but cannot CREATE DATABASE as I get the error 1044 access denied for user » @ ‘localhost’ . I am logged in as -uroot .

I have privileges in phpMyadmin to do what I want, but, in command line I seem to have no write privileges. I’ve looked at all the other related posts on this topic but to no avail. I cannot GRANT privileges as I have none anyway.

9 Answers 9

Are you logging into MySQL as root? You have to explicitly grant privileges to your «regular» MySQL user account while logged in as MySQL root.

First set up a root account for your MySQL database.

In the terminal type:

To log into MySQL, use this:

To set the privileges manually start the server with the skip-grant-tables option, open mysql client and manually update the mysql.user table and/or the mysql.db tables. This can be a tedious task though so if what you need is an account with all privs I would do the following.

Start the server with the skip-grant-tables option

Start mysql client (without a username/password)

Issue the command

which forces the grant tables to be loaded.

Create a new account with the GRANT command something like this (but replacing username and password with whatever you want to use.

Restart the server in normal mode (without skip-grant-tables) and log in with your newly created account.

Источник

Access denied for user ‘root’@’localhost’ (using password: YES) after new installation on Ubuntu

Today I did a login as root into Ubuntu 14.04.1 LTS ll

and then apt-get install mariadb-server (without sudo but as root).

With mySQL -h localhost -u root —password=

Access denied for user ‘root’@’localhost’ (using password: YES)

With mySQL -u root -p I logged into the DB and did

But this did not help. Have you got any idea? I did not find the answer for the similar questions.

8 Answers 8

TL;DR: To access newer versions of mysql/mariadb as the root user, after a new install, you need to be in a root shell (ie sudo mysql -u root , or mysql -u root inside a shell started by su — or sudo -i first)

Having just done the same upgrade, on Ubuntu, I had the same issue.

What was odd was that

Would accept my password, and allow me to set it, but I couldn’t log in as root via the mysql client

I had to start mariadb with

to get access as root, whilst all the other users could still access fine.

Looking at the mysql.user table I noticed for root the plugin column is set to unix_socket whereas all other users it is set to ‘mysql_native_password’. A quick look at this page: https://mariadb.com/kb/en/mariadb/unix_socket-authentication-plugin/ explains that the Unix Socket enables logging in by matching uid of the process running the client with that of the user in the mysql.user table. In other words to access mariadb as root you have to be logged in as root.

Sure enough restarting my mariadb daemon with authentication required I can login as root with

Having done this I thought about how to access without having to do the sudo, which is just a matter of running these mysql queries

with your desired mysql root password). This enabled password logins for the root user.

Alternatively running the mysql query:

Will change the root account to use password login without changing the password, but this may leave you with a mysql/mariadb install with no root password on it.

After either of these you need to restarting mysql/mariadb:

And voila I had access from my personal account via mysql -u root -p

PLEASE NOTE THAT DOING THIS IS REDUCING SECURITY Presumably the MariaDB developers have opted to have root access work like this for a good reason.

Thinking about it I’m quite happy to have to sudo mysql -u root -p so I’m switching back to that, but I thought I’d post my solution as I couldn’t find one elsewhere.

Источник

Access denied for user ‘root’@’localhost’ (using password: Yes) after password reset LINUX

I have a MySQL installed on my linux server, I forgot it’s password so I went and changed it using the methods I found on the web. What I did was as follows:

The update query did change the password as it showed me the number of rows affected and Query OK etc.

Then I restarted mysql

Now when I logged in with the new password

it still gives me errors «ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: Yes)»

Is there something that I am missing?

10 Answers 10

I was able to solve this problem by executing this statement

Which will change the root password.

You have to reset the password! steps for mac osx(tested and working) and ubuntu

Start it in safe mode:

(above line is the whole command)

This will be an ongoing command until the process is finished so open another shell/terminal window, log in without a password:

your new password is ‘password’.

I had the same problem. You have to write mysql -u root -p

NOT mysql or mysql -u root -p root_password

I meet the same problem, @progrAmmar enlightened me, «took a closer look at the user table in mysql database«.

My problem is not ssl_type, instead of the first field：Host. I changed the value by using

update user set Host=’localhost’ where User=’root’ and Host=’%’;

in mysqld_safe —skip-grant-tables model.

Then it works well.

You may need to clear the plugin column for your root account. On my fresh install, all of the root user accounts had unix_socket set in the plugin column. This was causing the root sql account to be locked only to the root unix account, since only system root could login via socket.

If you update user set plugin=» where User=’root’;flush privileges; , you should now be able to login to the root account from any localhost unix account (with a password).

Источник

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:

Error Code: 1141. There is no such grant defined for user ‘root’ on host ‘%’

23 Answers 23

If you have that same problem in MySql 5.7.+ :

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 :

then you will see it :

To allow connection with root and password, then update the values in the table with command :

Then run the select command again and you’ll see it has changed :

And that’s it. You can run this process after running and completing the sudo mysql_secure_installation command.

For mariadb, use

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

It didn’t like my user privilege so I SUDO it. (in bash

Try the following commands

for the people who are facing below error in mysql 5.7+ version —

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

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)

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.

Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

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)

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

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.

When I tried to log in with the normal ubuntu user it was throwing me this issue.

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.

Then you should be able to login to Mysql with the normal account.

If you are getting this error in Workbench but you are able to log in from terminal then follow this steps.

First simply log in with your current password:

Then change your password because having low strength password gives error sometimes.

Then simply exit and again login with your new password:

Once you successfully logged in type the command:

It should show a message like ‘Database changed’ then type:

After that type:

Then simply exit:

Now try to log in with your new password in your WORKBENCH. Hope it will work. Thank you.

A simple way to reset root password on Linux systems :

Checkout some other reasons for Access denied :

I faced this problem while installing Testlink on Ubuntu server, I followed these steps

Now stop the instance and start again i.e

Well the easiest way to reset root password is:

restart mysqld —skip-grant-tables option. This enables anyone to connect without a password and with all privileges. Because this is insecure, you might want to use —skip-grant-tables in conjunction with —skip-networking to prevent remote clients from connecting.

Connect to the mysqld server with this command:

shell> mysql Issue the following statements in the mysql client. Replace the password with the password that you want to use.

mysql> UPDATE mysql.user SET Password=PASSWORD(‘MyNewPass’) -> WHERE User=’root’; mysql> FLUSH PRIVILEGES;

Stop the server, then restart it normally (without the —skip-grant-tables and —skip-networking options).

Source Mysql documentation and personal experience:

in mysql 5.7 the password field has been replaced with authentication_string so you would do something like this instead

I resolved the same issue by running Workbench as administrator.

. I guess it’s because of restrictions on company computers, in my case.

MySQL default password for root is assigned depending on the way you have installed MySQL.

If you have installed it from MySQL Yum repository, MySQL SUSE repository, or RPM packages directly downloaded from Oracle, you can obtain the password using following command:

For my case, I found this error after fresh installation of mysql on Mac OS Big Sur.

What i did to fix it was: I click on the apple logo, go to system preferences and then click on mysql.

There’s an initialize database button on the opened settings window, I click on that, and then when I try to access again, it’s solved.

Try out the following steps to overcome this issue:

1. Open terminal / command prompt and navigate to the bin folder of the MySQL installation folder. Then run the command mysqld —console .
2. If you can see that line 171010 14:58:22 [Note] —secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled, after executing the above command from the command prompt.
3. Then you need to check that the mysqld is either blocked by the Windows Firewall or another program.
4. If it’s blocked by Windows Firewall then need to unblock from it and save settings.
5. To unblock the mysqld or mysql application, follow the below steps:
   1. Go to command prompt and type wf.msc to open the firewall settings.
   2. Click on Allow an app or feature through Windows Firewall.
   3. Check the mysqld or mysqld instances are available in the list and check the checkbox for the domain, public and private and save the settings.
6. Return to the bin folder and try the command from step 1 again.
7. It should work fine and not show any errors.

It should be possible to run the MySQL console without any problems now!

I resolved the same issue using next sql and restarting MySQL server:

I worked on Access Denied for User ‘root’@’localhost’ (using password: YES) for several hours, I have found following solution,

I don’t think you have to escape the —init-file parameter:

Источник