6.2.17 Troubleshooting Problems Connecting to MySQL

6.2.17 Troubleshooting Problems Connecting to MySQL Техника
Содержание
  1. Background
  2. Проблема причины
  3. Описание проблемы
  4. Интеллектуальная рекомендация
  5. jar справочный проект
  6. JS на передней дороге: эта точка к проблеме
  7. Вам также может понравиться
  8. VMware Установка виртуальной машины
  9. Хранимые процедуры и функции MySQL (очередь)
  10. Video Tutorial
  11. Cart
  12. Step #1. Open Your MySQL Console
  13. Step #2. Enter the Provided Commands
  14. Report
  15. Solution 1: Sudo then Change Password
  16. Conclusion
  17. Solution 1: Sudo Into MySQL
  18. Solution 3 – Start MySQL in Safe Mode
  19. Closing
  20. 2 ответа
  21. How to change the MySql user(root) password tutorial
  22. How to reset the forgotten MySql root password
  23. MySQL 1045 error Access Denied triggers in the following cases
  24. 1) Connecting to wrong host:
  25. 2) User does not exist:
  26. 3) User exists but client host does not have permission to connect:
  27. 4) Password is wrong, or the user forgot his password:
  28. 5) Special characters in the password being converted by Bash:
  29. 6) SSL is required but the client is not using it:
  30. 7) PAM backend not working:
  31. ‘SQL error 1045 sqlstate 28000’ – What this means?
  32. ‘SQL error 1045 sqlstate 28000’ – Causes & Fixes
  33. 1) Typo in username and password
  34. Solution
  35. 2) Accessing from wrong host
  36. Solution
  37. 3) User doesn’t exist
  38. Solution
  39. 4) Existence of Anonymous users
  40. Solution
  41. 5) Insufficient privileges
  42. Solution
  43. Answer
  44. Solve Access Denied for User Root Error
  45. Test Root User MySQL Access
  46. Solution
  47. Related Questions In Other DevOps Questions
  48. Join the world’s most active Tech Community!
  49. Welcome back to the World’s most active Tech Community!
  50. Subscribe to our Newsletter, and get personalized recommendations.
  51. How to change password of MySQL user (8. x)¶
  52. Форум
  53. Решение
  54. How do I fix error 1045 28000 Access denied?
  55. How do I fix MySQL error Access denied for user root localhost?
  56. How do I fix error 1045 28000 Access denied?
  57. How do I fix MySQL error Access denied for user root localhost?

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).

6.2.17 Troubleshooting Problems Connecting to MySQL

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

6.2.17 Troubleshooting Problems Connecting to MySQL

6.2.17 Troubleshooting Problems Connecting to MySQL

Stop the MYSQL server, then press initiaze database

6.2.17 Troubleshooting Problems Connecting to MySQL

enter a password and choose, use legacy passsword encryption

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.

6.2.17 Troubleshooting Problems Connecting to MySQL

http://cliparting.com/free-happy-birthday-gif-43712/

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

Then I did:

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

Got this error message:

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

How can I fix this?

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.

Introduction to the article on resolving the "Access denied for root user" MySQL error.

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’.

Access denied for user root localhost error message.

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( )。6.2.17 Troubleshooting Problems Connecting to MySQL

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

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

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

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

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

6.2.17 Troubleshooting Problems Connecting to MySQL

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

6.2.17 Troubleshooting Problems Connecting to MySQL

6.2.17 Troubleshooting Problems Connecting to 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

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

jar справочный проект

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

JS на передней дороге: эта точка к проблеме

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

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

6.2.17 Troubleshooting Problems Connecting to MySQL

VMware Установка виртуальной машины

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

6.2.17 Troubleshooting Problems Connecting to MySQL

Хранимые процедуры и функции MySQL (очередь)

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

In this post, learn how to fix “MySQL Error 1045” issue. You may get this error while starting the server on command prompt after installing MySQL. Below is the exact error.

Step 1: To fix the issue, at first on your MAC, go to the Apple sign top-right and click on it. Select “System Preferences” as shown below:

Open System Preferences on MAC

Step 2: Search “MySQL” in “System Preferences” and click on it:

Search MySQL under System Preferences

Step 3: Now, after reaching “MySQL” section, click “Initialize Database”:

Click Initialize Database

Step 4: The password box is empty. Enter a new password and click OK:

Enter new password to fix MySQL Error 1045

Step 5: That’s it! Go to the Terminal and reach the MySQL bin path using cd command as shown below:

Reach the MySQL bin path

Press enter and fill the password you just set above as shown in Step 4:

Enter MySQL root password

The server starts as shown below:

MySQL Error 1045 Fixed and Server started

Video Tutorial

If you’d like to see video tutorial and fix the issue, then refer the below video,


  • Fix NetBeans Installation Error: An unexpected exception in thread main
  • Resolving Drupal Installation PHP OPcode Caching Not Enabled Warning
  • Fix SDK Build Tools revision is too low for project app error in Android Studio
  • Resolving Joomla Local Installation freezes and hangs up error
  • How to fix Page Not Found Error on every Drupal page except homepage

Studyopedia Editorial Staff

We work to create programming tutorials for all.

Cart

No products in the cart.

6.2.17 Troubleshooting Problems Connecting to MySQL

How to Solve Error #1045 in phpMyAdmin

tutuploadsphpMyAdmin-Access-Denied.png

Let’s learn how to get rid of it.

Step #1. Open Your MySQL Console

tutuploadsmedia_1338949643300.png

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

Step #2. Enter the Provided Commands

tutuploadsmedia_1338949812597.png

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.

tutuploadsmedia_1338949961143.png

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.

tutuploadsphpmyadmin7.png

Дополнительно:  Где хранятся ошибки синего экрана? - блог про компьютеры и их настройку

Report

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.

This “access denied” error is one of the most common errors you’ll get when working with MySQL.

Learn how to fix it, and see a range of solutions if the suggested fix does not work, in this article.

When you try to connect to a MySQL database on your own computer (called “localhost”), you may get this error:

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

You might get an error code in front of it:

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

You might also get the error with “using password no”:

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

You’ll see this if you log into MySQL using the command line:

mysql -u root -p

What does this mean? How can you fix it?

There are a few solutions to this, which I’ve detailed below. Try one, and if it doesn’t work, try another one.

Also a tip for logging in: don’t enter your password in the command line itself, because this will be stored in your command history. Use the -p option, as mentioned above, and then you’ll be prompted to enter the password.

Solution 1: Sudo then Change Password

If you get the “access denied” error, one way to solve it is by using sudo to log in to mysql and change the root password.

Step 1: Open the command line on your system.

Step 2: Open mysql using the sudo command:

sudo mysql

Step 3: Enter the password for this account.

Step 4: Change the auth_plugin to mysql_native_password, and the password for the root account, in a single command:

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

Substitute the word your_new_password with a new secure password that you want to use for the root account.

The mysql_native_password method is a traditional method of authentication and will allow you to login.

Step 5: Flush the privileges, which tells the server to refresh the grant tables and apply your changes, with this command:

FLUSH PRIVILEGES;
SELECT user, plugin
FROM mysql.user

Step 7: Exit the console by pressing CTRL + D or typing exit.

exit;

Step 8: Log in to mysql using the root account and the new password you set, which should work:

mysql -u root -p

You should now be logged in to the root account in mysql.

If the above solution did not work, you may need to edit the mysql.cnf file to allow for changes to the root account.

Step 1: Open the my.cnf file. This may be stored in:

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

If you’re not sure where it is, search your MySQL installation folder (e.g. on Windows or Mac) for the file.

If you don’t have a my.cnf file (MacOS does not include one by default). You can create one in the /etc folder if you like.

[mysqld]
skip-grant-tables

Step 3: Restart the MySQL server.

Step 4: Login to the root account:

mysql -u root -p

Step 5: Flush the privileges, which tells the server to refresh the grant tables and apply your changes, with this command:

FLUSH PRIVILEGES;

Step 6: Set a new password for the account:

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

Substitute the word your_new_password with a new secure password that you want to use for the root account.

Step 7: Open the my.cnf file you opened in step 1, and remove the line about skip-grant-tables, and save the file.

Step 8: Restart the MySQL server again.

Step 9: Log in to the root account again:

mysql -u root -p

You should now be able to log in successfully with your new password and not get an error.

Conclusion

If you have any questions, feel free to use the comments section below.


Using this guide, you will discover three methods of resolving this error.”

The best way to understand this error is to break it down into single components. This error occurs when you attempt to login into your MySQL instance on your local machine, hence ‘localhost.’

An example is as shown:

6.2.17 Troubleshooting Problems Connecting to MySQL

  1. ERROR 1045 (28000) – refers to the ERROR CODE and SQLSTATE, respectively. For example, in MySQL and MariaDB, the error code 1045 means access denied for that username with the specified password.
  2. The second is the message string. The error message follows the format: Access denied for user ‘%s’@’%s’ (using password: %s).

Solution 1: Sudo Into MySQL

The first and most common method to resolve this error is to sudo into your MySQL instance and set the auth_plugin to mysql_native_password.

Open your terminal and run:

Enter the password for the account and proceed.

Next, change the auth_plugin and set a new password for the root account as shown:

Changing the authentication plugin will allow you to log in to your server.

Next, flush the privileges to update the grant tables:

Finally, exit the terminal and attempt your login:

If the error persists, you may need to edit the MySQL configuration file. In most cases, it is under the name my.cnf in the /etc or /etc/mysql directories.

If the file does not exist, create it.

Close and save the file.

Next, restart the MySQL server and log in.

$ sudo service mysql restart

$ mysql -u root -p

Once logged in, flush the privileges and set a new password:

Ensure to replace the command above with a secure password.

Once completed, edit my.cnf file and remove the entry we added earlier.

Restart the MySQL server and log in with your new password.

Solution 3 – Start MySQL in Safe Mode

MySQL provides you with the mysqld_safe utility for starting the MySQL server. This utility allows us to specify safe features when starting and restarting the server for diagnosing errors.

Start by stopping the MySQL Server.

$ sudo service mysql stop

Next, start MySQL with no authentication as;

$ mysqld_safe —skip-grant-tables &amp;

The command will start the server in the background as a job.

6.2.17 Troubleshooting Problems Connecting to MySQL

Once the daemon is running (with authentication disabled), open a new terminal and run:

The command above should allow you to log in without a password.

Once logged in, flush the privileges and set a new password as:

Close the session and restart MySQL.

Now log in to your server with your new password.

Closing

In this article, we covered three main methods of resolving the

We hope this tutorial helped you resolve this error.

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

Окно Connection Error

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

Ошибка Access denied

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

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

Таблица USE Mysql

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

  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?

user avatar

asked Mar 28, 2013 at 19:44

user avatar

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@'%'.

user avatar

answered Mar 28, 2013 at 20:40

user avatar

RolandoMySQLDBARolandoMySQLDBA

174k31 gold badges303 silver badges494 bronze badges

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

user avatar

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

user avatar

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

user avatar

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

user avatar

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

user avatar

Я на 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)

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -u root

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)

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -u root -p

Enter password: ***

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.

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -u root -p

Enter password: ****

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 45

Server version: 5.6.25-log 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.

mysql>

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 —

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -u root

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 48

Server version: 5.6.25-log 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.

mysql>

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.

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -u root

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

Query OK, 0 rows affected (0.00 sec)

mysql> SET PASSWORD FOR root@127.0.0.1 = PASSWORD(‘new-password’);

Query OK, 0 rows affected (0.00 sec)

mysql>

or, use

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

WHERE User = ‘root’;

to set new password.

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -u root

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

-> WHERE User = ‘root’;

Query OK, 3 rows affected (0.00 sec)

Rows matched: 3 Changed: 3 Warnings: 0

mysql>

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.

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -u root -p

Enter password: ****

Welcome to the MySQL monitor. Commands end with ; or \g.

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

Query OK, 0 rows affected (0.00 sec)

mysql> SET PASSWORD FOR root@127.0.0.1 = PASSWORD(‘new-password’);

Query OK, 0 rows affected (0.00 sec)

mysql>

or,

use

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

WHERE User = ‘root’;

to set new password.

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -u root -p

Enter password: ****

Welcome to the MySQL monitor. Commands end with ; or \g.

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

-> WHERE User = ‘root’;

Query OK, 3 rows affected (0.00 sec)

Rows matched: 3 Changed: 3 Warnings: 0

mysql>

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.

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqladmin -u root -p password

Enter password: ****

New password: *****

Confirm new password: *****

C:\Program Files\MySQL\MySQL Server 5.6\bin>

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

sudo dpkg-reconfigure mysql-server-5.6

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:

[engineer@percona]# mysql -u root -psekret

mysql: [Warning] Using a password on the command line interface can be insecure.

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

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:

[engineer@percona]# mysql -u root -psekret -h <IP> -P 3306

2) User does not exist:

[engineer@percona]# mysql -u nonexistant -psekret -h localhost

mysql: [Warning] Using a password on the command line interface can be insecure.

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

Fix: Double check if the user exists:

mysql> SELECT User FROM mysql.user WHERE User=‘nonexistant’;

Empty set (0.00 sec)

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

mysql> CREATE USER ‘nonexistant’@‘localhost’ IDENTIFIED BY ‘sekret’;

Query OK, rows affected (0.00 sec)

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

[engineer@percona]# mysql -u nonexistant -psekret

mysql: [Warning] Using a password on the command line interface can be insecure.

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

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

mysql> SELECT Host, User FROM mysql.user WHERE User=‘nonexistant’;

+————-+————-+

| Host | User |

+————-+————-+

| 192.168.0.1 | nonexistant |

+————-+————-+

1 row in set (0.00 sec)

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

[engineer@percona]# ip address | grep inet | grep -v inet6

inet 127.0.0.1/8 scope host lo

inet 192.168.0.20/24 brd 192.168.0.255 scope global dynamic wlp58s0

or for public IP:

[engineer@percona]# dig +short myip.opendns.com @resolver1.opendns.com

177.128.214.181

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

mysql> CREATE USER ‘nonexistant’@‘%’ IDENTIFIED BY ‘sekret’;

Query OK, rows affected (0.00 sec)

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

[engineer@percona]# mysql -u nonexistant -pforgotten

mysql: [Warning] Using a password on the command line interface can be insecure.

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

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:

mysql> SELECT Host, User, authentication_string, PASSWORD(‘forgotten’) FROM mysql.user WHERE User=‘nonexistant’;

+————-+————-+——————————————-+——————————————-+

| Host | User | authentication_string | PASSWORD(‘forgotten’) |

+————-+————-+——————————————-+——————————————-+

| 192.168.0.1 | nonexistant | *AF9E01EA8519CE58E3739F4034EFD3D6B4CA6324 | *70F9DD10B4688C7F12E8ED6C26C6ABBD9D9C7A41 |

| % | nonexistant | *AF9E01EA8519CE58E3739F4034EFD3D6B4CA6324 | *70F9DD10B4688C7F12E8ED6C26C6ABBD9D9C7A41 |

+————-+————-+——————————————-+——————————————-+

2 rows in set, 1 warning (0.00 sec)

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:

mysql> set password for ‘nonexistant’@‘%’ = ‘hello$!world’;

Empty set (0.00 sec)

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

[engineer@percona]# mysql -u nonexistant -phello$!world

mysql: [Warning] Using a password on the command line interface can be insecure.

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

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

[engineer@percona]# mysql -u nonexistant -p’hello$!world’

mysql: [Warning] Using a password on the command line interface can be insecure

...

mysql>

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

mysql> create user ‘ssluser’@‘%’ identified by ‘sekret’;

Query OK, rows affected (0.00 sec)

mysql> alter user ‘ssluser’@‘%’ require ssl;

Query OK, rows affected (0.00 sec)

...

[engineer@percona]# mysql -u ssluser -psekret

mysql: [Warning] Using a password on the command line interface can be insecure.

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

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

[engineer@percona]# mysql -u ssluser -psekret —ssl-mode=REQUIRED

...

mysql>

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:

mysql> CREATE USER ‘ap_user’@‘%’ IDENTIFIED WITH auth_pam;

Query OK, rows affected (0.00 sec)

...

[engineer@percona]# mysql -u ap_user -pap_user_pass

mysql: [Warning] Using a password on the command line interface can be insecure.

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

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:

[engineer@percona]# cat /etc/passwd | grep ap_user

ap_user:x:1000:1000::/home/ap_user:/bin/bash

To reset password:

Дополнительно:  Почему не работает картридер на ноутбуке — Ваша техника

[engineer@percona]# sudo passwd ap_user

Changing password for user ap_user.

New 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
  3. Stop the instance
  4. Edit my.cnf and remove skip-grant-tables and skip-networking
  5. 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.

sql error 1045 sqlstate 28000

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.

sql error 1045 sqlstate 28000

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 &amp;

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.

Logging in with MySQL sudo command and its output.

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';
Alter user MySQL command output.

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.

6.2.17 Troubleshooting Problems Connecting to MySQL

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

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

success

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!

Форум

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

Аватара пользователя

ERserver

Сообщения: 311
Зарегистрирован: 03 авг 2015, 02:19
Откуда: СССР
Контактная информация:

doc777

Сообщения: 1
Зарегистрирован: 22 окт 2021, 20:36

6.2.17 Troubleshooting Problems Connecting to MySQL


Создано на основе 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 на первом этапе и снова перезапустить службу.

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

6.2.17 Troubleshooting Problems Connecting to MySQL

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

6.2.17 Troubleshooting Problems Connecting to MySQL

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

Оцените статью
Master Hi-technology
Добавить комментарий