Acceso denegado para el usuario ‘root’@’localhost’ (con contraseña

Background

After the virtual machine has been provisioning with LAMP and you check the MariaDB status

systemctl status mariadb

The MariaDB status showed the ERROR 1045 (28000): Access Denied for user ‘root’@’localhost’ (using password: NO).

It happened before BUT no documentation to fix this.

Server errors are annoying, especially when they are cryptic like “sql error 1045 sqlstate 28000″.

The message contains some error codes.

But, what’s the real problem here?

At Bobcares, we help website owners resolve complex errors like “sql error 1045 sqlstate 28000“ as part of our Outsourced Hosting Support services.

Today, let’s discuss the top 5 reasons for this error and we fix them.

sudo apt-get update
sudo apt-get install mysql-server-5.5
sudo apt-get install mysql-client-5.5
sudo apt-get install mysql-common
sudo apt-get install glade
sudo apt-get install ntp

cd ~/Desktop/iPDC-v1.3.1/DBServer-1.1
mysql -uroot -proot <"Db.sql"

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Apr 29

in Other DevOps Questions

by
Kichu

• 19,040 points

•

2,012 views

I already have a mysql database in local (installed, configured and containing things for a service).

When I try to install phpmyadmin, I get the following error : ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO).

Here are the steps I do to install phpmyadmin :

• Install phpmyadmin database : Yes
• Socket Unix
• Name of the database : phpmyadmin
• MySQL ID : root@localhost
• MySQL password : mypassword
• MySQL password confirmation : mypassword
• ERROR

The login and password I give to phpmyadmin are the one of the root account for the MySQL service. They work good.

I dont get why phpmyadmin ask for the password if it try to connect to the user without using it (using password: NO).

Do you have a solution to force make it using the password ? I can’t disable the password of MySQL because the service is accessible by the internet. (And I want to learn a «clean» solution).

Introduction

Most MySQL users encountered the ERROR 1698 (28000): Access denied for user ‘root’@’localhost’. This error message usually appears for new installations of MySQL when you try to connect to MySQL with the root user.

This guide will show you how to quickly resolve the access denied for user root on localhost. The outlined instructions apply to both MySQL and MariaDB. There will be no need to modify any tables or to perform complex configuration.

Prerequisites

• Access to a command line or terminal window
• MySQL or MariaDB installed
• User with sudo or root privileges

When you install MySQL and try to access it on the local machine with the root user, the command you use is:

mysql -u root -p

In most cases, you will receive the error message Access denied for user ‘root’@’localhost’.

You can enable access for root using one MySQL command.

Question

Hi guys, I am very new to using a service like digitalocean (opposed to a one click setup of mediatemple or godaddy) and I am trying to setup and configure a new DB using MySQL but I keep getting the error

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I have googled this, and searched the community for answers but cannot seem to solve this issue. Am I missing something? I have tried running it as root, and running it with sudo through another user and cannot get around it. If I offer no password it gives the same error but instead of using password: YES, it’s NO.

Thanks.

Submit an answer

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Ну после пустой строки, переподключился по ссш, Доввел предлагаемые команды:

mysql -uroot -e «grant all privileges on *.* to ‘root’@’localhost’ identified by ‘tmppass’ with grant option; flush privileges;»

killall mysqld (тут ввел systemctl stop mariadb)

/etc/init.d/mysql start (тут соответственно systemctl start mariadb)

После чего

[root@VM-2310-13181-01 ~]# /usr/bin/mysql_secure_installation

/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we’ll need the current
password for the root user. If you’ve just installed MariaDB, and
you haven’t set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

Enter current password for root (enter for none):

• Показать ответы
• Ссылка

mysql-docker-container — ERROR 1045 (28000): Acceso denegado para el usuario ‘root’@’localhost’ (con contraseña: NO)

Estoy creando el contenedor docker mysql usando el archivo docker-compose a continuación. El servicio se creó con éxito, pero cuando intento ingresar dentro del contenedor, aparece el siguiente error ERROR 1045 (28000): acceso denegado para el usuario ‘root’ @ ‘localhost’ (con contraseña: NO)

 docker container exec -it 966 /bin/bash root@96607883960b:/# mysql -uroot -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) root@96607883960b:/# version: '3.3' services: db: image: mysql:5.7.29 container_name: mysql deploy: replicas: 1 restart_policy: condition: on-failure environment: - MYSQL_USER="testpass" - MYSQL_PASSWORD="testpass" - MYSQL_ROOT_PASSWORD="testpass" ports: - 33060:3306 volumes: - /Users/hello/Work/Volumes/wavolumes/mysql:/var/lib/mysql

encontré muchos artículos que no ayudaron a resolver este problema

¿alguien tiene la solución para este problema?

3 months ago
·

Santiago Trujillo

Информация об ошибке

ERROR 1045 (28000): ProxySQL Error: Access denied for user ‘admin’@» (using password: NO)

причина

Клиентская версия MySQL -8.0.16, подключение аутентификации пароля не совместима

Подход

1. Уменьшите версию клиента MySQL

2. Использование Default-Auth = mysql_native_password login

mysql -uadmin -padmin -h127.0.0.1 -P6032 --default-auth=mysql_native_password

Интеллектуальная рекомендация

Вам также может понравиться

Когда коллега сделал запрос Get, параметр был на китайском языке, а полученный искаженный код на сервере Tomcat, поэтому я намеренно изучил его, главным образом потому, что кодировка, используемая Tom…

Python не поддерживает перегрузку параметров Обратите внимание на нижний пример: Описание в приведенном выше примере, тестирование () не вызова теста () без параметров, только называемых последним. В …

задний план Недавно друг спросил меня, что мое пространство имен PHP было похоже, но из-за долгосрочного развития автор на самом деле забыл почти, поэтому я не могу ответить. Просто не забудьте быть о…

I am running MySQL 8 on Kubernetes by using deployment, service and secret files which are given below. but getting access denied error. I tried all the StackOverflow solutions but did not work for me.

Note:- I am using service type as load balancer which is having external IP.

Please help me to solve this issue, if anyone has a solution context

My Kubernetes files are:-

apiVersion: v1
kind: Service
metadata:
  name: mysql
  namespace: mysql
spec:
  ports:
  - port: 3306
  selector:
    app: mysql
  type: LoadBalancer
---
apiVersion: apps/v1 # for versions before 1.9.0 use apps/v1beta2
kind: Deployment
metadata:
  name: mysql
  namespace: mysql
spec:
  selector:
    matchLabels:
      app: mysql
  strategy:
    type: Recreate
  template:
    metadata:
      labels:
        app: mysql
    spec:
      containers:
      - image: mysql:8.0
        name: mysql
        env:
          # Use secret in real usage
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-pass
              key: password
        ports:
        - containerPort: 3306
          name: mysql
        volumeMounts:
        - name: mysql-persistent-storage
          mountPath: /var/lib/mysql
      volumes:
      - name: mysql-persistent-storage
        persistentVolumeClaim:
          claimName: mysql-pv-claim

apiVersion: v1
kind: Secret
metadata:
  name: mysql-pass
  namespace: mysql
type: kubernetes.io/basic-auth
stringData:
  password: cGFzc3dvcmQK

output:-

mysql -u root -ppassword -P 3306 -h 1.2.3.4
ERROR 1045 (28000): Access denied for user 'root'@'1.0.0.7' (using password: YES)

I am trying to set up MariaDB server under xubuntu 20.04 and import all the db’s I had previously on another machine under xubuntu 18 (following the notes I took when doing it there coming from opensuse).

The installed mariadb has datadir in /var/lib/mysql, and contains only a «null» msyql db with a root user with no password (as tested entering as sudo mysql -u root mysql which requires my personal passwd password).

I stopped the MariaDB server from the system installation, edited the mariadb.conf.d/50-server.cnf with my few changes (chief of them is that the datadir is /elsewhere/Mysql), then
I rsynced all my databases from the previous machine (I had prepared also mysql dumps,but since the MariaDB version is the same, I thought this was easier).

Then a systemctl start mariadb returns with no error but a systemctl status mariadb shows that the post-processing debian-start has given the error reported in the subject.

MariaDB looks running OK. I can enter with the mysql client in all my personal databases using my old mysql password, and I can also enter as mysql root using my old mysql root password. Those passwords are of course not the same as the passwd ones.

So I can work normally, only there is this slightly disturbing message from debian-start as if it was trying to login using no root password (which succeeds using the original mariadb.conf.d/50-server.cnf).

This did not occur on Ubuntu 18 (there I had to set ProtectHome=false, but apparently this is not required on 20)

Any way to get rid of the annoying message, or should I just live with it since all the rest works ?

Let’s learn how to get rid of it.

Step #1. Open Your MySQL Console

• Left click your WAMP icon located at the bottom right of your desktop.
• Click on MySQL Console.

Step #2. Enter the Provided Commands

If you have a password, you can ignore this part.

• Type in:
  use MySQL;
• Press Enter.
• Set your MySQL password with the following command, replacing “EnterYourPasswordHere” with your new chosen password:
  UPDATE mysql.user SET Password=PASSWORD("EnterYourPasswordHere") WHERE User="root";
• Press Enter.
• Flush the privileges by typing:
  FLUSH PRIVILEGES;
• Exit by typing:
  Exit
• Press Enter.

Using a text editor, such as Notepad++, open your config.inc.php file.

• Find the following line of code:
  $cfg['Servers'][$i]['password'] = ''; // MySQL password
• Change the 'password' to the newly chosen password you created in Step #2.
• Click Save.

Дополнительно:  Ноутбук перегрелся и не включается: Ноут перегрелся и не включается. Не включается ноутбук – устанавливаем причину, решаем проблему – Ноут перегрелся и не включается. Ноутбук выключился и не включается —  Эксперт — интернет-магазин электроники и бытовой техники

You have already reported this

Это может быть, если пароль не был задан при установке.

Порядок действий для установки/смены пароля 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 — удалить эту строчку.

Подробнее в Русскоязычной документации Ubuntu

Пароль по умолчанию пустой.
Возможно, вы неправильно набрали команду. Скопируйте именно эту: 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.

Он пишет что пароль не нужен.

Тут два варианта, ИМХО.
1) Вы что-то не так поняли из курса:
2) Составитель курса что-то упустил.
В любом случае или стоило бы сюда ссылку кинуть на этот курс или писать составителю.

По проблеме. Сервер mysql пишет вам, что пользователю ‘root’ доступ закрыт. Как мне кажется, нужно вначале создать бд, применить схему и там создастся пользователь, с данными которого вы подключитесь к бд. А слова «Using password: NO» означает лишь, что пароль и не использовался.

0
2

Система Debian 9 Stable

Устанавливаю Maria DB:

apt-get update
apt-get install mariadb-server

Далее нужно задать пароль Root. Раньше у меня всегда срабатывали следующие рекомендации:

Либо эта: Задание пароля SQL-пользователя root для MariaDB (MySQL) в Debian 9Либо эта: Первичная настройка MariaDB (MySQL) после установки

Но теперь они не работают. И не только у меня. Вот, например, вопрос товарища (достаточно старый) так и остался без решения: Проблемы с mariadb.

Проблема вот в чем. Под рутом пробую зайти в консоль MariaDB, ни одна из команд не срабатывает:

# mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

# mysql -u root -p
Enter password: [пробовал Enter и пароль рута]
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): [нажимаю Enter]
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Enter current password for root (enter for none): [ввожу пароль рута]
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
...

Буржуи говорят, что теперь поставка MariaDB делается с временным паролем, который можно подсмотреть в логе: https://stackoverflow.com/questions/21944936/error-1045-28000-access-denied-f…

Типа, делается это командой:

grep 'temporary password' /var/log/mysqld.log
или
grep 'temporary password' /var/log/mysql.log
или
grep 'temporary password' /var/log/mysql/error.log

Но ничего похожего в логах нет, никакого упоминания даже ‘pass’.

Да и на стековерфлове у товарища так не получилось.

Там же написана процедура полного сброса пароля для MariaDB. Но это как-то уже за гранью добра и зла. Неужели свежеустановленную MariaDB нужно настраивать именно ТАК?

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

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Access denied for user 'root'@'localhost' (using password: NO)

mysql -u root -p

sudo mysql

ALTER USER 'root'@'localhost'
IDENTIFIED WITH mysql_native_password BY 'your_new_password';

FLUSH PRIVILEGES;

SELECT user, plugin
FROM mysql.user

exit;

mysql -u root -p

/etc/my.cnf
/etc/mysql/my.cnf

[mysqld]
skip-grant-tables

mysql -u root -p

FLUSH PRIVILEGES;

ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_new_password';

mysql -u root -p

I was in a time crunch. As everyone is. And I realized that I have lost my MySQL password on the only machine that I can work on at that time. In my defense, I have not used MySQL on that machine for a very long time. So I googled, stackoverflowed, read the docs, and did all I could to fix it. I finally got it.

First I wanted to run some assessments. First, I tried stopping MySQL:

The result I got was an error.

It was not happy. Now what?

I found the process! Now I need to kill it so that I can reset things.

‘kill process’ was not working, so I have checked the status

Well that is still running. So what if I try to kill all?

killall mysqld mysqld_safe// no effect

That did not work either.

I got the info. Will killall -9 work?

killall -9 mysqld mysqld_safe// kill all of them command. does not work

Nope. Will sudo stop work?

sudo mysqld stop// seems to render something, but there wasFatal error: Please read “Security” section of the manual to find out how to run mysqld as root!

Nope. Will sudo force starting will work?

It said it already has a process running, but success. Did that fix the problem?

Nope. After assessments, I concluded that I should clean install MySQL. Thus I went as follows.

sudo mysql.server stop// Shutting down MySQL .. SUCCESS! // did not helpkill -TERM 11595// mySQL still alive

Now that all the services are killed, it was time to re-install things.First, I tried a softer way of installation.

npm uninstall mysqlbrew uninstall mysql(and restart)// killed the process & dumped all the DB I think// but mysql command is not working

MySQL is configured to only allow connections from localhost by default. Hence the above. Now, to connect run, mysql -uroot was executed. To have launched start MySQL now and restart at login, brew services start mysql command was used. Or, if you don’t want/need a background service you can just run the following.

mysql.server start==> Summary*/brew services start mysql==> Successfully started `mysql` (label: homebrew.mxcl.mysql)

As you can see, it was still giving me some grief. Thus now I decided to attack the root directly. Hence, ALL related needed be deleted.

brew services stop mysql// Error: Service `mysql` is not started.~/Library/LaunchAgents에 homebrew.mxcl.mysql.plistcheck if homebrew-mysql related file is here. If not, good./usr/local/var/mysqlcheck if homebrew-mysql related file is here. If not, good.// ugh there issudo rm -rf /usr/local/var/mysqlls -a// check three times to make sure the thing is gone

MySQL is configured to only allow connections from localhost by default

To connect run:

To have launched start MySQL now and restart at login: brew services start MySQLOr, if you don’t want/need a background service you can just run:

mysql.server start==> Summary/usr/local/Cellar/mysql/5.7.18_1: 321 files, 232.9MB*/Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.mysql>

Finally, I got a prompt! Moral of the story? Keep trying, and you shall fix it!

‘SQL error 1045 sqlstate 28000’ – What this means?

Before we move on to the reasons for this error, let’s first get an idea of this error.

Website owners face this error when querying data from the SQL server.

For instance, the complete error message looks like this:

SQLSTATE[28000] [1045] Access denied for user 'user'@'localhost' (using password: YES)

This error shows that the MySQL server disallows the user to connect to it from localhost or 127.0.0.1.

‘SQL error 1045 sqlstate 28000’ – Causes & Fixes

In our experience managing servers, we’ll see the major causes of this error and how our Dedicated Support Engineers fix it.

1) Typo in username and password

This is the most common reason for the error “sql error 1045 sqlstate 28000″.

Users may type wrong username and password while connecting to the database.

Therefore, SQL server can’t identify the authenticity of the account.

Solution

In such cases, we help website owners reset the database user password.

For example, in cPanel servers, we reset the database user password from Databases > Mysql databases > Current Users.

Mysql databases option in cPanel

Also, for database driven websites like WordPress, Magento, etc., we update the new database username and password in the website configuration files.

For example, in the Magento application, we update the database name, username and password in the “app/etc/local.xml” file.

In some cases, website owners get errors like this:

SQLSTATE[28000] [1045] Access denied for user 'root'@'localhost' (using password: YES)

This is because, the root session don’t know the password of mysql root user.

And, it can be probably a mis-typed password during the initial setup.

Here, our Hosting Engineers reset the admin/root password after starting the MySQL in safe mode.

For example, we use the below command to reset the root password in safe mode.

update user set password=PASSWORD("YOURPASSWORDHERE") where User='root';

2) Accessing from wrong host

MySQL uses host based restrictions for user access to enhance security.

In other words, MySQL allows user access only from hosts defined in the MySQL user table.

So, any access from remote machines whose hostnames are not defined in this user table will bounce with the error “sql error 1045 sqlstate 28000”

Solution

First, our Hosting Engineers check whether the remote host is allowed in the MySQL user table.

If not, we add the hostname of the remote machine in the MySQL user table.

For instance, we use the below command to add a host in the MySQL user table.

update user set host='hostname' where user='username';

Here, hostname is the hostname of the remote machine, and username is the MySQL user.

We’ve seen cases where server owners use wildcards(%) in host field which gives universal access to this user.

But, this is not a good practice as there is a security risk that user can access the database from any hosts.

In addition to that, due to security concerns, we always disable accessing root user from remote machines.

[You don’t have to be a MySQL expert to keep your websites online. We have experienced MySQL admins available 24/7.]

3) User doesn’t exist

Similarly, this error “sql error 1045 sqlstate 28000” occurs when the user trying to access the database doesn’t exist on the MySQL server.

For example, if you access MySQL using a testuser that doesn’t exist, you can see the following error.

# mysql -u testuser -p 
Enter password:  
ERROR 1045 (28000): Access denied for user 'testuser'@'localhost' (using password: YES)

Solution

In such cases, our Support Engineers first check whether the user exists in the MySQL user table.

If not, we check the user’s requirement to access the database and if it is valid, we create a user with that username.

4) Existence of Anonymous users

Website owners face this error when there are anonymous MySQL users like ”@localhost or ”@127.0.0.1.

That is, when a client tries to connect to the database, the MySQL server looks through the rows in the user table in a sorted order.

The server uses the first row that matches the most specific username and hostname.

So, here the anonymous user (‘ ‘@localhost) precedes any other users like ‘user’@localhost when connecting from localhost.

And, use the anonymous user password to connect to the server.

Finally, the result is “sql error 1045 sqlstate 28000“.

Solution

Our Hosting Engineers check the MySQL user table and remove the anonymous user account.

For example, we use the below command to remove the anonymous user from MySQL.

delete from user where User = ' ';

5) Insufficient privileges

Likewise, insufficient privileges for the user to access the database can also result in this error.

Solution

In such cases, we assign proper access rights for the user to access the database.

For example, in cPanel servers, we manage user privileges from:

cPanel > Mysql databases > Current databases > Privileged users > Click on the database user.

Granting user privileges in cPanel

[Struggling with database user permissions and privileges? Our MySQL Experts are here for your help.]

MySQL 1045 error Access Denied triggers in the following cases

1) Connecting to wrong host:

If not specifying the host to connect (with -h flag), MySQL client will try to connect to the localhost instance while you may be trying to connect to another host/port instance.

Fix: Double check if you are trying to connect to localhost, or be sure to specify host and port if it’s not localhost:

2) User does not exist:

Fix: Double check if the user exists:

If the user does not exist, create a new user:

3) User exists but client host does not have permission to connect:

Fix: You can check to see which host user/host MySQL allows connections with the following query:

If you need to check from which IP the client is connecting, you can use the following Linux commands for server IP:

or for public IP:

You can then create a user with correct Host (client IP), or with ‘%’ (wildcard) to match any possible IP:

4) Password is wrong, or the user forgot his password:

Fix: Check and/or reset password:

You cannot read user passwords in plain text from MySQL as the password hash is used for authentication, but you can compare hash strings with “PASSWORD” function:

We can see that PASSWORD(‘forgotten’) hash does not match the authentication_string column, which means password string=’forgotten’ is not the correct password to log in. Also, in case the user has multiple hosts (with different password), he may be trying to connect using the password for the wrong host.

In case you need to override the password you can execute the following query:

5) Special characters in the password being converted by Bash:

Fix: Prevent bash from interpreting special characters by wrapping password in single quotes:

6) SSL is required but the client is not using it:

Fix: Adding –ssl-mode flag (–ssl flag is deprecated but can be used too)

You can read more in-depth on how to configure SSL in MySQL in the blog post about “Setting up MySQL SSL and Secure Connections” and “SSL in 5.6 and 5.7“.

7) PAM backend not working:

Fix: Double check user/password is correct for the user to authenticate with the PAM currently being used.

In my example, I am using Linux shadow files for authentication. In order to check if the user exists:

To reset password:

Finally, if you are genuinely locked out and need to circumvent the authentication mechanisms in order to regain access to the database, here are a few simple steps to do so:

1. Stop the instance
2. Start the instance
4. Stop the instance
5. Edit my.cnf and remove skip-grant-tables and skip-networking
6. Start MySQL again

Learn more about Percona Server for MySQL

Solution

From the other cloud services that we have, we knew that this is something to do with the ‘UNIX_SOCKET’ authentication.

We also know that cause as this new machine has been rebuilt a few times, changing the root password OR changing the MariaDB root password has got nothing to do with this.

After logging in to MariaDB, we found nothing on this query:

SELECT plugin from mysql.user where User='root';

We tried the following query:

UPDATE mysql.user SET plugin = 'mysql_native_password',  Password = PASSWORD('new_password') WHERE User = 'root';

We found something interesting configuration on the /etc/mysql/debian.cnf . The password was blank on both client and mysql_upgrade. We put the password for testing purposes.

Note: Ensure to double quote within the password. For instance: password = “password”

Restart the MariaDB:

systemctl restart mariadb

Check the MariaDB status:

systemctl status mariadb

The issue has gone.

Note: This may fix the issue, however, we are investigating this approach and we believe it’s not safe to put the password into this file. On the flip side though, the MariaDB root password has to be different anyway from the operating system root.

Change the MariaDB root password:

mysqladmin --user=root --password=currentpassword password "newpassword"

Test the root account.

Open the file – /etc/mysql/debian.cnf

Change the password to a new password.

Note: Ensure to double quote within the password. For instance: password = “password”

Stop MariaDB service:

sudo systemctl stop mariadb

Start the MariaDB service:

sudo systemctl start mariadb

Check the status again:

sudo systemctl status mariadb

Solve Access Denied for User Root Error

To be able to log into MySQL as root, first use sudo to modify the root user:

sudo mysql

Enter your password at the prompt. A MySQL shell loads.

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. This is a traditional method for authentication, and it is not as secure as auth_plugin. In the example above, we set “root” as the password, but we encourage you to set a stronger password.

Test Root User MySQL Access

After you run the commands listed above, exit the MySQL shell by pressing CTRL + D on your keyboard or type exit; and hit enter. There is no need to restart the mysqld service to log in.

Now try again to access MySQL with root. In the terminal, type in:

mysql -u root -p

Enter the password you used with the ALTER USER command. Do not type in the system password to access MySQL as it will not work. If everything worked fine, you should see the MySQL welcome message.

NOTE: The ALTER USER command may not work for MySQL and MariaDB versions older than 5.7.6 and 10.1.20 respectively.

Conclusion

Now you know how to bypass the MySQL ERROR 1698 (28000): Access denied for user ‘root’@’localhost’.

There are different ways to approach this issue, but we selected the easiest and fastest method. Make sure to enter the commands as listed in the article to avoid errors in SQL syntax.

Related Questions In Other DevOps Questions

• All categories
• 
  Apache Kafka
• 
  Apache Spark
• 
  Azure
• 
  Big Data Hadoop
• 
  Blockchain
• 
  C#
• 
  C++
• 
  Career Counselling
• 
  Cloud Computing
• 
  Cyber Security & Ethical Hacking
• 
  Data Analytics
• 
  Database
• 
  Data Science
• 
  DevOps & Agile
• 
  Digital Marketing
• 
  Events & Trending Topics
• 
  IoT (Internet of Things)
• 
  Java
• 
  Kotlin
• 
  Linux Administration
• 
  Machine Learning
• 
  MicroStrategy
• 
  PMP
• 
  Power BI
• 
  Python
• 
  RPA
• 
  SalesForce
• 
  Selenium
• 
  Software Testing
• 
  Tableau
• 
  Talend
• 
  TypeSript
• 
  Web Development
• 
  Ask us Anything!
• 
  Others

Join the world’s most active Tech Community!

Welcome back to the World’s most active Tech Community!

Subscribe to our Newsletter, and get personalized recommendations.

Already have an account? Sign in.

Access denied for user ‘[email protected]’ (using password

I’m new to MySQL, I’m trying to run WordPress in my Windows desktop and it needs MySQL.

I install everything with Web Platform Installer which is provided by Microsoft. I never set a root password for MySQL and in the final step of installing WordPress, it asks for a MySQL server password.

What is the default password for root (if there is one) and how to change it?

I tried:

mysql -u root password '123'

But it shows me:

Access denied for user '[email protected]' (using password:NO)

After this I try:

mysql -u root -p

However, it asks for a password which I don’t have.

Update: as Bozho suggested, I did the following:

1. I stopped the MySQL Service from Windows services
2. Opened CMD
3. Changed the location to c:\program files\mysql\bin

4. Executed the command below

   mysqld —defaults-file=»C:\\program files\\mysql\\mysql server 5.1\\my.ini» —init-files=C:\\root.txt

5. The command ran with a warning about character set which I mentioned below

6. I start the MySQL service from Windows services

7. I write in the command line

   mysql -u root -p
   EnterPassword: 123 // 123 was the password

How do I solve this? I’m waiting to hear from you.

Solutions

You can reset your root password. Have in mind that it is not advisable to use root without password.

for this kind of error; you just have to set new password to the root user as an admin. follow the steps as follows:

[root ~]# mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password:NO)

1. Stop the service/daemon of mysql running

   [root ~]# service mysql stop   
   mysql stop/waiting
   

At this moment, the terminal will seem to halt. Let that be, and use new terminal for next steps.

3. mysql> use mysql;
   Database changed
   mysql> select * from  user;
   Empty set (0.00 sec)
   mysql> truncate table user;
   Query OK, 0 rows affected (0.00 sec)
   mysql> flush privileges;
   Query OK, 0 rows affected (0.01 sec)
   mysql> grant all privileges on *.* to [email protected] identified by 'YourNewPassword' with grant option;
   Query OK, 0 rows affected (0.01 sec)
   

*if you don`t want any password or rather an empty password

    mysql> grant all privileges on *.* to [email protected] identified by '' with grant option;
    Query OK, 0 rows affected (0.01 sec)*
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)

Confirm the results:

    mysql> select host, user from user;
+-----------+------+
| host      | user |
+-----------+------+
| localhost | root |
+-----------+------+
1 row in set (0.00 sec)

5.  [root ~]# mysql -u root -pYourNewPassword 
    mysql> 
   

1) You can set root password by invoking MySQL console. It is located in

C:\wamp\bin\mysql\mysql5.1.53\bin by default.

Get to the directory and type MySQL. then set the password as follows..

    > SET PASSWORD FOR [email protected] = PASSWORD('new-password');

2) You can configure wamp’s phpmyadmin application for root user by editing

C:\wamp\apps\phpmyadmin3.3.9\config.inc.php 

Note :- if you are using xampp then , file will be located at

C:\xampp\phpMyadmin\config.inc.php

It looks like this:

        $cfg['Servers'][$i]['verbose'] = 'localhost';
        $cfg['Servers'][$i]['host'] = 'localhost';
        $cfg['Servers'][$i]['port'] = '';
        $cfg['Servers'][$i]['socket'] = '';
        $cfg['Servers'][$i]['connect_type'] = 'tcp';
        $cfg['Servers'][$i]['extension'] = 'mysqli';
        $cfg['Servers'][$i]['auth_type'] = 'config';
        $cfg['Servers'][$i]['user'] = 'root';
        $cfg['Servers'][$i]['password'] = 'YOURPASSWORD';
        $cfg['Servers'][$i]['AllowNoPassword'] = false;

The error «Access denied for user ‘[email protected]‘ (using password:NO)»
will be resolved when you set $cfg['Servers'][$i]['AllowNoPassword'] to false

If you priviously changed the password for ‘[email protected]‘, then you have to do 2 things to solve the error «Access denided for user ‘[email protected]‘»:

1. if [‘password’] have a empty quotes like ‘ ‘ then put your password between quotes.
2. change the (using password:NO) to (using password:YES)

This will resolve the error.

Note: phpmyadmin is a separate tool which comes with wamp.
It just provide a interface to MySQL. if you change my sql root’s password, then you should change the phpmyadmin configurations. Usually phpmyadmin is configured to root user.

Similar questions

mysql_query(): Access denied for user »@’localhost’ (using password: NO)

I’m running a cron job in my WordPress site and getting these two errors Warning: mysql_query(): A link to the server could not be established in /home/geekda6/public_html/wp-content/plugins/maxblogpress-ninja-affiliate/ninja-affiliate-library/include/mbp-ninja-affiliate.cls.php on line 251 Warning: mysql_query(): Access denied for user »@’localho…

Access denied for user »@’localhost’ (using password: NO) When Upgrading WordPress

I just tried to upgrade the latest wordpress version, and it throws this error. Access denied for user »@’localhost’ (using password: NO) . I immediately communicated it to the service provider, they say it is a database error. But i checked the wp-config file with correct database name, username and password, It is all perfect. But why still im g…

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

最近新装好的mysql在进入mysql工具时，总是有错误提示:
# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)
或者
# mysql -u root -p password ‘newpassword’
Enter password:
mysqladmin: connect to server at ‘localhost’ failed

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

现在终于找到解决方法了。本来准备重装的，现在不必了。
方法操作很简单，如下：

# /etc/init.d/mysqld stop //停止mysql服务的运行
# mysqld_safe —user=mysql —skip-grant-tables —skip-networking & //跳过受权表访问
# mysql -u root mysql //登录mysql

在mysql5.7以下的版本如下：
mysql> UPDATE user SET Password=PASSWORD(‘newpassword’) where USER=’root’ and host=’127.0.0.1′ or host=’localhost’;//把空的用户密码都修改成非空的密码就行了。

在mysql5.7版本如下：

update mysql.user set authentication_string=password(‘newpassword’) where user=’root’ and host=’127.0.0.1′ or host=’localhost’;

mysql> FLUSH PRIVILEGES; mysql> quit # /etc/init.d/mysqld restart //离开并重启mysql # mysql -uroot -p Enter password: <输入新设的密码newpassword>

How do I fix error 1045 28000 Access denied?

Set root user password Login as user root with blank password &gt,mysql -u root mysql&gt, ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘abc’,

How do I fix MySQL error Access denied for user root localhost?

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

How do I fix error 1045 28000 Access denied?

Set root user password Login as user root with blank password &gt,mysql -u root mysql&gt, ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘abc’,

How do I fix MySQL error Access denied for user root localhost?

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