6.2.17 Troubleshooting Problems Connecting to MySQL

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.

Проблема причины

ОригиналMySQLбаза данныхВ таблице пользователей, имеется анонимный счет с анонимным счетом в присутствии пользователя. Это на самом деле используется корнем, но это на самом деле анонимный Логин.Это можно увидеть «» @ «локальный» в сообщении об ошибке строки.

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.

Sometimes you get this error when you do

mysql -u root -p

To fix this error, open your settings, click on the mysql icon

then start the server again.

Now open your terminal and type

mysql -u root -p

and enter your password.

Hopefully your mysql is logged in now.

After some searching, I realized that I should log in with a username and password:

mysql -u root -p

If you forget your password, there are steps to solve it in the later section.

Server version: 8.0.19 MySQL and 8.0.13 MySQL have been verified.

msyql> use msyql;
msyql> alter user 'root'@'localhost' identified by '123';
msyql> flush privileges;

verification：try to login with mysql -u root -p to see if it is successful.

If there is no accident, you can see that the password change is successful.

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,009 views

I am new to Oracle Cloud and not an expert on MySql, I have built my first Arm-based Ampere A1. I was successfully able to install Apache web server. I tried to do a yum install for MySQL.

I can start and stop mysqld

/usr/sbin/mysqld (mysqld 8.0.27) starting as process 2408

I can confirm that port 3306 starts

However when I try to login into MySQL itself

$mysql -u root

I get the following error:

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

I have tried the following

1. I have updated /etc/my.cnf with a password for root 
2. Turned off SELinux
3.  sudo mysqld —skip-grant-tables —skip-networking

Any help is greatly appreciated!

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.

https://blog.csdn.net/u012730299/article/details/51840416

Воспроизведение проблем (в дальнейшем обсуждалась только среда Windows):

   C:\AppServ\MySQL> mysql -u root -p 
Enter password:  
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

：

mysql my.ini（ mysql ， D:\Program Files\MySQL\MySQL Server 5.0\my.ini）， [mysqld] skip-grant-tables mysql

1. “ ”->“ ”( Win+R)。

2. ： net stop mysql

3. ： net start mysql  

cmd mysql -u root -p ， password： ， ERROR 1045 (28000)， ， grant（ ）。

Продолжайте принимать процесс ниже:

1. Введите базу данных MySQL: mysql> use mysql;  Database changed

2. Установите новый пароль в пользователь root: MYSQL> Обновить набор пользователей Пароль = пароль («новый пароль»), где пользователь = «root»; Query OK, 1 rows affected (0.01 sec)  Rows matched: 1 Changed: 1 Warnings: 0

3. Обновить базу данных mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)

4. Выход MySQL: mysql> quit;  Bye

После изменения, измените этот файл с помощью этого файла, удалите «Skip-Grant-Table», мы просто присоединяемся, сохраните выход и перезапустите MySQL.

I have installed MariaDB on Ubuntu 16.04. But when trying for the first time is says:

~$ mysql
ERROR 1045 (28000): Acces denied for user 'username'@ 'localhost' (using password: NO)

And when I try mysql_secure_installation to set a root password is says:

~$ 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): 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Enter current password for root (enter for none): 

Giving Enter doesn’t work.
I read something about «UNIX_SOCKET plugin is installed by default in new installs of Ubuntu 15.10» but I do not understand how I can log in with this. I hope getting a answer soon!

Описание проблемы

При входе в MySQL в Linux, сталкиваются с магической проблемой, выполнить MySQL -u -p корень, и были побуждая после ввода пароля.ERROR 1045 (28000): Access denied for user’root’@’localhost’ (using password: YES)После того, как Интернет проверяется, это в основном запрос на изменение пароля. Прежде всего, я помню, что я поставил пароль на корню перед установкой, и я также проверить подключенную корень с помощью Navicat, во-вторых, я вновь установить пароль виртуальной машины MySQL в соответствии с онлайн учебник В пароле. 123456 Испытано с Navicat, это действительно успешным, но при входе в виртуальную машину, сообщается в этой неисправности, наконец, я войду в MYSQL , когда я войти в пароль.

После того, как я не могу решить вышеуказанные проблемы, я войду мой MySQL напрямую, попытайтесь создать базу данных, а также сообщить об ошибке в результате.ERROR 1044 (42000): Access denied for user»@’localhost’ to database ‘new_180423’И я нашел проход в Интернете и, наконец, нашел эти два вопроса.

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)

пришлось поменять сервер, и тут возникла проблема с подключением к базе данных, а если быть точнее, то с подключением из Laravel.

На старом сервере была MySQL 8.0, тут же MariaDB 10.5.15, вроде как они работают на одном драйвере, но может быть ошибаюсь.

Команды в терминале выглядят вот так:

mysql -u root
use table;
select * from users;

Всё работает правильно, как и нужно.

PhpMyAdmin тоже работает правильно, а вот сайт:

SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: YES) (SQL: select * from `users` where `email` = 123@gmail.com limit 1)

Получается как-то так, пробовал выдавать права (хотя не понимаю зачем, это же root), пробовал:

ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('root');

Никак не помогает, пробовал создавать новых пользователей — тоже не работает, как я понимаю вся проблема именно в подключении из Laravel,

'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

Этот код config/database.php я не менял, и по идее он должен работать, а по факту — нет.

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

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

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

Давайте возьмем электронную почту в качестве примера, чтобы увидеть, как процитировать банки первой и второй сторон   В 4.0 в папке электронной почты есть папка Email / emailcommon, которая отлич…

Просто поговорите о проблеме в функции, 1. Что такое Атис? Любая функция по существу называется объектом. Если она не указана напрямую, это окно Таким образом, существует переменная внутри функции, и …

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

1, Baidu Search VMware и скачать 2. Выберите установленный путь к файлу, а затем вы можете пройти весь путь. Отказ 3. При открытии введите ключ продукта и найдите два онлайн. VMware 2017 V14.x Постоян…

Обзор Упоминание о хранимых процедурах может поднять другую тему — преимущества и недостатки хранимых процедур. Я не буду их здесь обсуждать. Как правило, когда меня спрашивают, я отвечу, что если вы …

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.

Дополнительно:  Скачать bluestacks уже с root правами

You have already reported this

grep 'temporary password' /var/log/mysqld.log
Sort date (newest date)

You may see something like this;

[root@SERVER ~]# grep 'temporary password' /var/log/mysqld.log
2016-01-16T18:07:29.688164Z 1 [Note] A temporary password is generated for root@localhost: O,k5.marHfFu
2016-01-22T13:14:17.974391Z 1 [Note] A temporary password is generated for root@localhost: b5nvIu!jh6ql
2016-01-22T15:35:48.496812Z 1 [Note] A temporary password is generated for root@localhost: (B*=T!uWJ7ws
2016-01-22T15:52:21.088610Z 1 [Note] A temporary password is generated for root@localhost: %tJXK7sytMJV
2016-01-22T16:24:41.384205Z 1 [Note] A temporary password is generated for root@localhost: lslQDvgwr3/S
2016-01-22T22:11:24.772275Z 1 [Note] A temporary password is generated for root@localhost: S4u+J,Rce_0t
[root@SERVER ~]# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root: 

The existing password for the user account root has expired. Please set a new password.

New password: 

Re-enter new password:

If you see it says

... Failed! Error: Your password does not satisfy the current policy requirements
That means your password needs to have a character such as ! . # - etc...
mix characters well, upper case, lower case, ! . , # etc...

New password: 

Re-enter new password: 
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 100 
Change the password for root ? ((Press y|Y for Yes, any other key for No) : Y

New password: 

Re-enter new password: 

Estimated strength of the password: 100 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y
Success.

All done! 
[root@SERVER ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.10 MySQL Community Server (GPL)

Watch the last 10 minutes of this video, it teaches you how you do it.

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

Понять суть проблемы можно, переведя сообщение об ошибке на русский язык. Означает оно, что пользователю с именем root на машине localhost запрещен доступ к БД при использовании пароля или без него.

Чтобы свободно получить доступ в MySQL, должно совпасть три параметра, описывающих пользователя базы данных — имя, название машины и пароль. Если есть какие-то несовпадения, доступ будет запрещен. Самая простая причина проблемы — неправильный ввод пароля. Кроме этого, вызывать ошибку может неправильный синтаксис.

Наиболее распространенные ошибки при обращении к БД:

1. При присвоении прав новому пользователю не был указан адрес машины, с которой он может подключаться. В таком случае ему автоматически будет разрешено пользоваться БД с любого хоста, кроме локального, и при попытке подключения с localhost возникнет ошибка доступа.
2. Использование пароля при его отсутствии в базе данных.

Порядок действий таков:

1. Откройте таблицу пользователей.
2. Проверьте, существует ли пользователь root с хостом localhost. Если он есть, смотрите на поле «password». Если там пусто, зайти в базу можно без ввода пароля. Если там что-то есть, значит, вы вводите неправильный пароль.
3. Смените пароль командой SET PASSWORD.
4. Если пользователя root нет, создайте его, установите пароль и предоставьте ему права.

После этого в базу данных можно зайти. Если изменить данные не получается, следует использовать параметр —skip-grant-tables, который отменяет все настройки разрешений.

Если ошибка появляется с ключом (Using password: NO), нужно сделать следующее изменить файл config.inc.php, указав в нем правильные данные. Если проблема возникает при установке MySQL, нужно удалить базы данных старой версии программы или сменить пароль для доступа к ним, используя режим —skip-grant-tables.

I just installed a fresh copy of Ubuntu 10.04.2 LTS on a new machine. I logged into MySQL as root:

david@server1:~$ mysql -u root -p123

I created a new user called repl. I left host blank, so the new user can may have access from any location.

mysql> CREATE USER 'repl' IDENTIFIED BY '123';
Query OK, 0 rows affected (0.00 sec)

I checked the user table to verify the new user repl was properly created.

mysql> select host, user, password from mysql.user;
+-----------+------------------+-------------------------------------------+
| host      | user             | password                                  |
+-----------+------------------+-------------------------------------------+
| localhost | root             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| server1   | root             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| 127.0.0.1 | root             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| ::1       | root             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| localhost |                  |                                           |
| server1   |                  |                                           |
| localhost | debian-sys-maint | *27F00A6BAAE5070BCEF92DF91805028725C30188 |
| %         | repl             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-----------+------------------+-------------------------------------------+
8 rows in set (0.00 sec)

I then exit, try to login as user repl, but access is denied.

david@server1:~$ mysql -u repl -p123
ERROR 1045 (28000): Access denied for user 'repl'@'localhost' (using password: YES)
david@server1:~$ mysql -urepl -p123
ERROR 1045 (28000): Access denied for user 'repl'@'localhost' (using password: YES)
david@server1:~$ 

Why is access denied?

asked Mar 28, 2013 at 19:44

1

The reason you could not login as repl@'%' has to do with MySQL’s user authentication protocol. It does not cover patterns of users as one would believe.

Look at how you tried to logged in

mysql -u repl -p123

Since you did not specify an IP address, mysql assumes host is localhost and tries to connect via the socket file. This is why the error message says Access denied for user 'repl'@'localhost' (using password: YES).

One would think repl@'%' would allow repl@localhost. According to how MySQL perform user authentication, that will simply never happen. Would doing this help ?

mysql -u repl -p123 -h127.0.0.1

Believe it or not, mysql would attempt repl@localhost again. Why? The mysql client sees 127.0.0.1 and tries the socket file again.

Try it like this:

mysql -u repl -p123 -h127.0.0.1 --protocol=tcp

This would force the mysql client to user the TCP/IP protocol explicitly. It would then have no choice but to user repl@'%'.

answered Mar 28, 2013 at 20:40

RolandoMySQLDBARolandoMySQLDBA

174k31 gold badges303 silver badges494 bronze badges

0

You should issue for localhost specific to it.

  GRANT USAGE ON *.* TO 'repl'@'localhost' IDENTIFIED BY '123';

And try connecting.

answered Mar 29, 2013 at 5:40

MannojMannoj

1,4792 gold badges14 silver badges34 bronze badges

The problem is these two accounts, added by default.

http://dev.mysql.com/doc/refman/5.5/en/default-privileges.html

+-----------+------------------+-------------------------------------------+
| host      | user             | password                                  |
+-----------+------------------+-------------------------------------------+
| localhost |                  |                                           |
| server1   |                  |                                           |
+-----------+------------------+-------------------------------------------+

A blank user name is a wildcard, so no matter what account you use, it matches this user if MySQL thinks you’re connecting from localhost or your local server name (server1 in this case)… since they have no password, any password you try is wrong. User authentication only tries the first match, so the user you created never gets noticed when your host is localhost (or your server name).

Delete these two from the mysql.user table and then FLUSH PRIVILEGES;.

Or, the mysql_secure_installation script can do this for you, although I tend to prefer doing things manually.

http://dev.mysql.com/doc/refman/5.5/en/mysql-secure-installation.html

answered Mar 30, 2013 at 2:02

Michael — sqlbotMichael — sqlbot

22.1k2 gold badges45 silver badges75 bronze badges

Database may not be configured yet just issue a no-arg call:

mysql <enter>

Server version: xxx

Copyright (c) xxx

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Mysql [(none)]> 

If Mysql must be set a root password, you can use

mysql_secure_installation

answered Apr 18, 2020 at 7:45

Make sure that all fields in the connector are set up with the correct details

host = «localhost»,
user = «CorrectUser»,
passwd = «coRrectPasswd»,
database = «CorreCTDB»

Check for upper and lowercase errors as well — 1045 is not a Syntax error, but has to do with incorrect details in the connector

answered Dec 13, 2019 at 22:59

This could be an issue with corruption of your mysql database. Tables inside mysql database like user table can get corrupt and may cause issued.

Please do a check on those

myisamchk /var/lib/mysql/mysql/ *.MYI

Usually while checking or fixing myisam tables we would like to take mysql down first. If this problem is still not solve please try this out aswell.

If they are corrupt then you can fix them using

myisamchk —silent —force —fast /path/table-name.MYI

Thanks,

Masood

answered Mar 31, 2013 at 17:25

Я на Ubuntu. Я в процессе установки MySQL не задал ни одного пароля к своей учётной записи root. Пытаюсь запустить MySQL с помощью

mysql -u root 

Однако у меня не получается, в итоге выдается следующая ошибка access denied.

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

Затем, я подумал, что это может быть из-за выдачи пароля. Я попробовал вот это и ввожу пустой пароль и нажал клавишу Enter.

abc@ubuntu:~$ mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Затем, я даже попробовал ввести пароль (мой пароль пользователя на компьютере). У меня в итоге получилось вот это

abc@ubuntu:~$ mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

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

mysql

ubuntu

Источник

24 февраля 2014 в 19:45

2 ответа

• Ошибка Mysql: ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

  Это сводит меня с ума… когда я пытаюсь авторизоваться в Mysql. user-MacBook:~ user$ cd /usr/local/mysql/bin user-MacBook:bin user$ ./mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES) user-MacBook:bin user$ ./mysql ERROR 1045…

• Mysql на Ubuntu: ERROR 1045 (28000)

  Я установил mysql server на свою Ubuntu 16.04 с помощью этой команды: user@samariter-ubuntu:~/dev$ sudo apt-get install mysql-server Я оставил корневой проход пустым. Он прошел нормально, возражений нет. Однако, когда я пытаюсь его запустить, он отказывается: user@samariter-ubuntu:~/dev$ mysql…

0

Вы должны логиниться от root поэтому для этого вы должны написать sudo вроде:

sudo mysql -p

Сначала дай свой системный пароль

Потом он просит пароль mysql сервера, в моем случае ‘123’ Потом ты аккументируешь свою базу данных.

24 февраля 2014 в 19:52

24 февраля 2014 в 20:00

Mac mysql ERROR 1045 (28000): Access denied for user ‘root’@’localhost’

Mac 10.10.1, Mysql 5.6.22 Когда я устанавливаю mysql в своем mac, и пишу mysql -u root -p на терминале, то возникает ошибка: ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using…

Ошибка 1045 (28000) Cannot stop mysql

Я пытаюсь остановить MySQL с помощью sudo mysql stop команды но у меня получается Error 1045 (28000): Access denied for user ‘root’@’localhost’ (using password NO) Я видел в инете некоторые ответы…

ERROR 1045 (28000): Unknown error 1045

После успешной установки mysql-community-server-5.6 на Oracle Linux запускается служба mysqld. Но когда я подключаюсь к MySQL возникла следующая ошибка. init.d]# service mysqld status mysqld (pid…

Ошибка Mysql: ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

Это сводит меня с ума… когда я пытаюсь авторизоваться в Mysql. user-MacBook:~ user$ cd /usr/local/mysql/bin user-MacBook:bin user$ ./mysql -u root -p Enter password: ERROR 1045 (28000): Access…

Mysql на Ubuntu: ERROR 1045 (28000)

Я установил mysql server на свою Ubuntu 16.04 с помощью этой команды: user@samariter-ubuntu:~/dev$ sudo apt-get install mysql-server Я оставил корневой проход пустым. Он прошел нормально, возражений…

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

Знаю этот вопрос тут задавался вроде миллион раз но за жизнь я вроде бы не могу разобраться что не так. Был бы признателен если кто-то сможет мне помочь в устранении неисправности. Я установил mysql…

Не могу получить доступ к mysql ERROR: 1045(28000)

У меня в Ubuntu 12.04 box был установлен lamp server, когда я попытался получить доступ к MySQL я получил вот такой баг ERROR 1045(28000) Я пробовал многие решения доступные в инете ничего не…

Xampp/ Lampp Mysql Terminal acces ERROR 1045 (28000)

Устанавливаю на Ubuntu 16.04 LTE Xampp (lampp) все хорошо Работает: # ~ $ Sudo / opt / lampp / lampp status Version: XAMPP for Linux 5.6.20-0 Apache is running. MySQL is running. ProFTPD is running….

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

Когда я хочу запустить mysql в windwos cmd я получил эту ошибку ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO) что я могу сделать? mysql -u root -p ERROR 1045…

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO), can’t reset password тоже

Никак не могу получить доступ к моим базам данных MySQL. Проблема: root@server:~# mysql -uroot ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO) Пытаюсь сбросить…

In this MySql tutorial we will solve very important MySql error —  How to resolve ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO) /  (using password: YES) in MySql.

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

when you try to login to root user using mysql -u root. You may face ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

The default password  for user root is blank (i.e. an empty string). If in case it has never been changed then you may simply login to root user using mysql -u root command.

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES) in MySql >

when you try to login to root user using  mysql -u root -p, and if you enter wrong password, you may face ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

SOLUTION to ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES/NO) in MySql>

For solving this problem, when you try to login to root user, type

mysql -u root -p. You will be asked for password and enter correct password to login.

The default password  for user root is blank (i.e. an empty string). If in case it has never been changed then you may simply login to root user using mysql -u root command —

How to change the MySql user(root) password in MySql

Change MySql user password 1: Changing the root password — When root password is blank (i.e. an empty string).

you can use

SET PASSWORD FOR root@localhost = PASSWORD(‘new-password’);

to set new password.

or, use

UPDATE mysql.user SET Password = PASSWORD(‘new-password’)

   WHERE User = ‘root’;

to set new password.

Change MySql user password 2: Changing the root password — When root password is NOT blank.

First you’ll have to enter existing password for root user, than you can use

SET PASSWORD FOR root@localhost = PASSWORD(‘new-password’);

to set new password.

or,

use

UPDATE mysql.user SET Password = PASSWORD(‘new-password’)

   WHERE User = ‘root’;

to set new password.

Change MySql user password 3: Changing the root password using mysqladmin.

First you’ll have to enter existing password for root user, than you can enter set new password.

Change MySql user password 4: UBUNTU user can try this for setting up root password >

In this MySql tutorial we solved very important MySql error —  How to resolve ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO) /  (using password: YES) in MySql.

Related >>

How to change the MySql user(root) password tutorial

How to reset the forgotten MySql root password

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

‘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.]

Answer

It seems that you have forgotten root password which was set during installation of MariaDB

Solution:- reset root password

Follow below steps to reset root password:-
1) Stop mariadb services
service mysql stop

2) Run mysqld safe version by below command
sudo mysqld_safe —skip-grant-tables &

3) Run mysql client with below commnad on new terminal
mysql -u root

4) Set password in user table of mysql database
use mysql;
update user SET PASSWORD=PASSWORD(«<new_password>») WHERE USER=’root’;
flush privileges;
exit

5) Login with new password, set in above command
mysql -u root -p
Enter password:

• 
  ↑ Starting and Stopping 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.

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

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.

How to change password of MySQL user (8. x)¶

Step 1: Shut down the mysql service

the mysql service can be closed by net stop mysql as administrator or in the task manager.

Step 2: Skip Mysql password verification

Enter the command prompt (as administrator) operation, enter the \bin folder in the \mysql directory, the difference between mysql8.0 and other versions is that mysqld --skip-grant-tables cannot be used directly to skip password login. Here we use mysqld -console --skip-grant-tables --shared-memory to skip permission verification.

C:\Windows\system32> d:
D:\> cd D:\mysql-8.0.19-winx64\bin
D:\mysql-8.0.19-winx64\bin> mysqld -console --skip-grant-tables --shared-memory
D:\mysql-8.0.19-winx64\bin>

There is no feedback after the input is executed.

next, open a new administrator window to execute again.After entering the directory, make sure that you have closed the Mysql service:

net stop mysql

After closing the Mysql service, continue to operate in the D:\mysql-8.0.19-winx64\bin directory:

execute:

mysql --console --skip-grant-tables --shared-memory

After entering this line of code, as shown below, we have successfully skipped Mysql password login:

Step 3: Enter Mysql without password

After the above steps, open another cmd.exe running in administrator mode (this is the third window)

After entering the bin directory under mysql, log in directly to mysql

No need to open mysql service via net start mysql

Enter the following code in the command line

> d:
> cd D:\mysql-8.0.19-winx64\bin #This should be changed to your own path
> mysql -u root -p

At this point, it will show you to enter the password, press Enter directly, and you can successfully connect to Mysql.

Step 4: Set the login password to empty

Enter the code and set the password to be empty (the password cannot be directly modified at this time, it must be set to be empty first, otherwise an error will be reported)

input:

> use mysql; 
# leave the password blank
> update user set authentication_string='' where user='root'; 
# then quit Mysql
> quit; 

Step 5: Change your login password

It is divided into two parts

1. Close the first two cmd windows (must be closed!);
2. Enter the code in the third window;

# close the mysql service, although it will show that the service is not started, but just in case
net stop mysql 
# open the mysql service again
net start mysql 

we must do this, if the last mysql service is not closed, we still log in without a password.

Then enter:

# Enter the installation directory on your computer here
cd D:\mysql-8.0.19-winx64\bin
# The input password will be displayed here, just press Enter, we have left it empty in the fourth step
mysql -u root -p
# (change the password)
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';

last step: verify that the password has been changed successfully

enter:

# exit mysql
quit
# Enter new password and log in again
mysql -u root -p

done!

Форум

• Первое новое сообщение
• Поиск

• Ошибки Open Server

• • Текущее время: 18 авг 2022, 18:05
  • Часовой пояс:

ERserver

Сообщения: 311

Зарегистрирован: 03 авг 2015, 02:19

Откуда: СССР

Контактная информация:

doc777

Сообщения: 1

Зарегистрирован: 22 окт 2021, 20:36

Создано на основе phpBB® Forum Software © phpBB Limited
Разработчик визуального стиля Arty — MrGaby

Контакты:
, предоставляем бесплатную удалённую техническую поддержку через TeamViewer

© 2010-2021 «OSPanel.io»

Решение

Таким образом, вам нужно только удалить анонимных пользователей в таблице пользователей, чтобы решить эти две проблемы.

1、# vi /etc/my.cnf

В последнем лице [туздО] блок, добавить пропуск ГРАНТ-таблицу, чтобы служба перезапуска войдите в Mysql без пароля, сохранение выхода Vim

2、# service mysqld restart

Перезапустить сервис

3、# mysql -u root -p

Введите непосредственно в MySQL

4、mysql&gt; use mysql ;

5、mysql>delete from user where user=»; 

Удаление имени пользователя пустым пользователей

6、mysql>flush privileges;

Обновить правильную таблицу

7. Удалите строку плюс один в файле my.cnf на первом этапе и снова перезапустить службу.

Удалить таблицу пользователя перед:

Удален Пользователь таблица:

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