6.2.22 Troubleshooting Problems Connecting to MySQL

Oops!! Frustrated with ‘Access denied for user ‘root’@’localhost’ (using password yes) in MySQL?

This error may occur due to many reasons which include insufficient privileges for the user to access the database, and when MySQL root password is wrong and so on.

At Bobcares, we often get requests to fix MySQL errors, as a part of our Server Management Services.

Today, let’s see how our Support Engineers fix this MySQL for our customers.

Если при попытке входа в phpMyAdmin вы видите ошибку:

#1698 - Access denied for user 'root'@'localhost'

то данная статья поможет вам разобраться в проблеме и устранить её.

MySQL 5.7 изменила модель безопасности: теперь вход в MySQL под рутом (root) требует sudo (при этом пароль всё равно может быть пустым). Т.е. phpMyAdmin невозможно использовать под пользователем рутом. Подробное объяснение смотрите в статье Аутентификация по паролю и unix_socket в MySQL и MariaDB.

Самым простым (и самым безопасным) решением будет создать нового пользователя и предоставить ему требуемые привилегии.

Но начнём мы с другого решения:

i suggest steps to reset root password by running the server with option —skip-grant-tables this option allow you to log in in mysql without password running this with sudo:

# sudo service mysql stop

# sudo mysqld_safe —skip-grant-tables &

# sudo mysql -u root

mysql> use mysql;

mysql> update user set authentication_string=PASSWORD("YOUR-NEW-ROOT-PASSWORD") where User='root';

mysql> flush privileges;

mysql> quit

# sudo service mysql stop

# sudo service mysql start

# sudo mysql -u root -p

Now you can login to sql with your new password

check may be it's possible to find a query file about root password in path /home/$USER/.mysql_history or /root/.mysql_history

Note: prior to MySQL 5.7 the column was called password instead of authentication_string. Replace the line above with

mysql> update user set password=PASSWORD("YOUR-NEW-ROOT-PASSWORD") where User='root';

Goodluck

Access denied for user ‘root’@’localhost’

I am getting this error with adminer.php and phpmyadmin won’t work either. I am able to run SQL commands in terminal after typing ‘sudo mysql’. I have a user table with root as a username but there is no password field.

If anyone can help me to get this to work it would be great.

asked Apr 30, 2018 at 6:01

write this this command in your terminal with root user

getsebool -a | grep http

and look to httpd_read_user_content if it’s off then you have to make it on by this command

setsebool -P httpd_read_user_content on 

then restart your apache server and try to access to adminer and phpmyadmin

answered Sep 9, 2018 at 12:11

2

In newer versions of MariaDB/MySQL the default root user authentication method on Linux is unix socket. In other words, only the root user itself can login to the MySQL root user account (and no password is needed).

As unix socket authentication is the most secure way to authenticate, IMO the best way to go is to create a new root-like user (that does require a password) for use with Adminer (and/or other DB interfaces that require a user is authenticated via password).

answered Feb 3, 2021 at 0:42

Дата: 25.11.2013

Автор: Василий Лукьянчиков , vl (at) sqlinfo (dot) ru

Статистика форума SQLinfo показывает, что одной из наиболее популярных проблем является ошибка mysql №1045 (ошибка доступа).
Текст ошибки содержит имя пользователя, которому отказано в доступе, компьютер, с которого производилось подключение, а также ключевое слово YES или NO, которые показывают использовался ли при этом пароль или была попытка выполнить подключение с пустым паролем.

Типичные примеры:

ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES) — сервер MySQL
— сообщает, что была неудачная попытка подключения с локальной машины пользователя с именем root и
— не пустым паролем.

ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: NO) — отказано в
— доступе с локальной машины пользователю с именем root при попытке подключения с пустым паролем.

ERROR 1045 (28000): Access denied for user ‘ODBC’@‘localhost’ (using password: NO) — отказано в
— доступе с локальной машины пользователю с именем ODBC при попытке подключения с пустым паролем.

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

SELECT user, password
FROM user
WHERE User = 'root';

+-------------+----------+
| User | Password |
+-------------+----------+
| root | invalid |
+-------------+----------+

SET PASSWORD FOR 'root'@localhost = PASSWORD("myReallyStrongPwd");

При попытке выполнить вход phpMyAdmin или авторизоваться в MySQL вы можете столкнуться с ошибкой HY000/1045:

Warning: mysqli_connect(): (HY000/1045): Access denied for user 'username'@'localhost' (using password: YES)
mysqli::real_connect(): (HY000/1045): Access denied for user 'superuser'@'localhost' (using password: NO)

Ошибка возникает, когда используемая комбинация имени пользователя, хоста и пароля не позволяет подключаться к серверу. Например, если пытаться выполнить вход под пользователем, который не существует вовсе, то возникнет именно эта ошибка.

Давайте посмотрим на следующий код, который вызывает эту ошибку:

<?php define("DB_HOST", "localhost"); define("DB_USER", "root"); define("DB_PASSWORD", ""); define("DB_DATABASE", "databasename"); $db = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_DATABASE);

Увидели подвох? Устанавливается значение константы DB_USER, а используется для подключения DB_USERNAME, аналогично с DB_HOST и DB_SERVER. То есть в СУБД передаются неверные или пустые данные с которыми невозможно выполнить подключение.

Посмотрите на следующий код, который также вызовет ошибку:

$servername = '127.0.0.1';
$username = 'user_bob';
$password = "sdfasdf$B";
$conn = mysqli_connect($servername, $username, $password, 'any_database');

В пароле содержится специальный символ $. В принципе, в пароле можно (и даже нужно) использовать специальные символы, но некоторые из них имеют специальное значение для языков программирования или для оболочки (если подключение выполняется в командной строке). Одинарная и двойная кавычки хотя и используются одинаково для обозначения строк чтобы символы в них перестали трактоваться как другие элементы языка, но они не являются идентичными. Одинарная кавычка делает так, что любые символы теряют своё специальное значение. А в строке, помещённой в двойные кавычки, некоторые специальные символы сохраняют своё значение. То есть «sdfasdf$B» интерпретатором PHP расценивается как буквальная строка «sdfasdf» за которой следует переменная $B.

Правильно так:

$servername = '127.0.0.1';
$username = 'user_bob';
//НЕ ИСПОЛЬЗУЙТЕ ДВОЙНЫЕ КАВЫЧКИ для строк со специальными символами
//$password = "sdfasdf$B";
$password = 'sdfasdf$B';
$conn = mysqli_connect($servername, $username, $password, 'any_database');

Чтобы убедиться, что проблема в пароле содержащим специальные символы, вы можете временно установить пароль на не содержащий специальных символов примерно следующей командой:

GRANT ALL PRIVILEGES ON БАЗА_ДАННЫХ.* TO 'ПОЛЬЗОВАТЕЛЬ'@'%' IDENTIFIED BY '12345';

В этом примере пароль установлен на «12345».

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.

I know that some users may say that this is a duplication but seriously I have combed the answers and those on the internet for over half a day and I am so frustrated i am going to explode.

Now that therapeutic explanation is done, I have installed MySql Server and MariaDb 10 from the Ubuntu repositories on Ubuntu 12.04 LTS and input the root password when it is installing[When the pink screen appears and prompts for the root password] and the installation goes through as smoothly as a knife through butter.

Now the real frustration begins in either of the below 2 scenarios:

1. When I enter the sudo /usr/bin/mysql_secure_installation command nd am prompted to enter the root password

or

2.When I enter the mysql -u root -p command and am prompted for the password.

Please I really need help on this — I have lost count of the number of times I have had to uninstall and reinstall either MySQL or MariaDb because this pain in neck erf an error.

asked Apr 16, 2014 at 6:39

6

Check the file: /etc/mysql/mariadb.conf.d/mysqld.cnf

verify if this line exists:

plugin-load-add = auth_socket.so

then go to MariaDB:

sudo mysql -u root

and make these modifications:

MariaDB [(none)]>use mysql;
MariaDB [(none)]>update user set plugin=' ' where User='root';
MariaDB [(none)]>flush privileges;
MariaDB [(none)]>exit

I didn’t have access to mysqlworkbench, but after I applied this solution, everything ruan well…

Fabby

33.8k38 gold badges93 silver badges191 bronze badges

answered Jun 15, 2015 at 5:34

5

I think I may have figured out the issue — It has to do with the long password.I think that this should reported as a bug to MySQl and MariaDB developers because on installation you can create a password with over 100 characters but when you try to login to MySQl or MariaDB when it is in production you get the above error.

So glad I figured this out after so much pain — surprising really that this has not been identified as a bug considering how many years MySQL has been in production.

So basically one resolution is to go through the following steps to resolve the above issue:

1. Start with a blank password on a non-production server and is if it works

2. If you are successful with step 1 then proceed with securing your mysql installation by typing the follwing command: sudo /usr/bin/mysql_secure_installation and try entering a more complex password — comination of lowercase letters, numbers, symbols and uppercase letters of at least 8 characters should be fine.

3. I recommend starting with 8 and move upwards till you reach a ceiling with the number of characters you can enter.

The above should be a step in the right direction to getting your MySQL server secured.

answered Apr 16, 2014 at 20:51

chetwynd2014chetwynd2014

1111 gold badge1 silver badge5 bronze badges

2

Try this command in terminal:

mysqladmin -u root -pcurrentpassword password 'newpassword'

Then, type:

mysql -u root -pnewpassword

jobin

26k16 gold badges98 silver badges116 bronze badges

answered Apr 16, 2014 at 7:04

The best answer is here

It explains what’s happening. It explains why you shouldn’t just disable the plugin. It explains how to get around it (login using sudo, create a new superuser account that’s not root).

Note that this solution by david-leonardo doesn’t survive a reboot.

answered Oct 15, 2017 at 7:18

datakiddatakid

3744 silver badges21 bronze badges

I’m working on Xubuntu 15.04. I already installed the MariaDB-Server on various systems and was always asked for a root password during installation. This time, however, I don’t remember being asked for the password. When I try to login without a password (or a blank password), I get the Access denied for user 'root'@'localhost' error.
I tried uninstalling the package completely by

sudo apt-get remove mariadb-server
sudo apt-get purge mariadb-server

When I reinstalled, I still didn’t get asked for the root password.

I tried the mysqld --skip-grant-tables approach from
mysql how to fix Access denied for user ‘root’@’localhost’ .
I can modify the password for the root user in the mysql database — at least the hash value changes — but I still cannot login with the new password after a restart of the mysql-server. I still get the same error.

The user debian-sys-maint does not exist. So, I cannot use it to fix anything.

Any ideas what else I could try?

asked Aug 15, 2015 at 16:12

1

You need to reset the password.
so for that

sudo mysql -u root
use mysql;
update user set plugin='' where User='root';
flush privileges;
exit;

answered May 15, 2016 at 16:16

3

The idea with the new set-up is that you shouldn’t be using passwords at all. See UNIX_SOCKET Authentication Plugin for details.

What’s especially relevant is the contents of /usr/share/doc/mariadb-server-10.0/README.Debian.gz on Ubuntu 16.04:

So if you disable that plug-in for root and set a password, the daily cron job will break as it’s assuming it will log in as root without a password, but with the plug-in.

Later it says:

So it looks like passwords should no longer be used by applications.

answered Jul 21, 2016 at 20:22

colancolan

7757 silver badges15 bronze badges

1

I solved the problem following the answer from this post:

Can’t reset MySQL (MariaDB) root password

One has to change the plugin field of mysql.user for all roots to a blank string.

answered Aug 28, 2015 at 6:42

d72b617cd72b617c

2891 gold badge3 silver badges6 bronze badges

2

I did it by running this command, right after installation:

$ sudo mysql_secure_installation

At first step, password is blank, so just press Enter.

answered May 30, 2017 at 1:57

2

Just use sudo mysql -u root — that’s it

Details: Newer versions authenticate to mysql using system authentication. So if you can sudo to the OS, it assumes you’re db root too. You can confirm this by issuing sudo mysql -u root -e "USE mysql; SELECT User, Host, plugin FROM mysql.user;". You should see something like this (maybe with auth_socket in other distros)

+------+-----------+-------------+
| User | Host | plugin |
+------+-----------+-------------+
| root | localhost | unix_socket |
+------+-----------+-------------+

answered Oct 12, 2018 at 23:09

DeepSpace101DeepSpace101

8,55712 gold badges43 silver badges47 bronze badges

1

I had the same issue on a raapberry pi with stretch. My solution was to create a new user with all privileges by doing the following:

sudo mysqld_safe --skip-grant-tables --skip-networking &
mysql -u root
MariaDB [(none)]> use mysql
MariaDB [mysql]> FLUSH PRIVILEGES;
GRANT ALL privileges ON mysql.* TO 'admin'@'localhost' with grant option;

Now I can login and us the user «admin» as superuser.

I hope this is going to help someone.

answered Nov 5, 2017 at 13:44

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

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

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

mysql -u root -p

sudo mysql

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

FLUSH PRIVILEGES;

SELECT user, plugin
FROM mysql.user

exit;

mysql -u root -p

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

[mysqld]
skip-grant-tables

mysql -u root -p

FLUSH PRIVILEGES;

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

mysql -u root -p

I tried many steps to get this issue corrected. There are so many sources for possible solutions to this issue that is is hard to filter out the sense from the nonsense. I finally found a good solution here:

Step 1: Identify the database version

mysql --version

You’ll see some output like this with MySQL:

mysql Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapper

Or output like this for MariaDB:

mysql Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1

Make note of which database and which version you’re running, as you’ll use them later. Next, you need to stop the database so you can access it manually.

Step 2: Stopping the database server

To change the root password, you have to shut down the database server beforehand.

You can do that for MySQL with:

sudo systemctl stop mysql

And for MariaDB with:

sudo systemctl stop mariadb

Step 3: Restarting the database server without permission checking

If you run MySQL and MariaDB without loading information about user privileges, it will allow you to access the database command line with root privileges without providing a password. This will allow you to gain access to the database without knowing it.

To do this, you need to stop the database from loading the grant tables, which store user privilege information. Because this is a bit of a security risk, you should also skip networking as well to prevent other clients from connecting.

Start the database without loading the grant tables or enabling networking:

sudo mysqld_safe --skip-grant-tables --skip-networking &

The ampersand at the end of this command will make this process run in the background so you can continue to use your terminal.

Now you can connect to the database as the root user, which should not ask for a password.

mysql -u root

You’ll immediately see a database shell prompt instead.

MySQL Prompt

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

MariaDB Prompt

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>

Now that you have root access, you can change the root password.

Step 4: Changing the root password

mysql> FLUSH PRIVILEGES;

Now we can actually change the root password.

For MySQL 5.7.6 and newer as well as MariaDB 10.1.20 and newer, use the following command:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

For MySQL 5.7.5 and older as well as MariaDB 10.1.20 and older, use:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');

Make sure to replace new_password with your new password of choice.

Note: If the ALTER USER command doesn’t work, it’s usually indicative of a bigger problem. However, you can try UPDATE ... SET to reset the root password instead.

[IMPORTANT] This is the specific line that fixed my particular issue:

mysql> UPDATE mysql.user SET authentication_string = PASSWORD('new_password') WHERE User = 'root' AND Host = 'localhost';

Remember to reload the grant tables after this.

In either case, you should see confirmation that the command has been successfully executed.

Query OK, 0 rows affected (0.00 sec)

The password has been changed, so you can now stop the manual instance of the database server and restart it as it was before.

Step 5: Restart the Database Server Normally

The tutorial goes into some further steps to restart the database, but the only piece I used was this:

For MySQL, use:

sudo systemctl start mysql

For MariaDB, use:

sudo systemctl start mariadb

Now you can confirm that the new password has been applied correctly by running:

mysql -u root -p

The command should now prompt for the newly assigned password. Enter it, and you should gain access to the database prompt as expected.

Conclusion

You now have administrative access to the MySQL or MariaDB server restored. Make sure the new root password you choose is strong and secure and keep it in safe place.

This might seem redundant but I was unable to find a correct solution.

I was unable to login to mysql using the mysql console.It is asking for a password and I have no clue what I actually entered.(Is there a way to get the password or change it?)
This is how my config.inc look.

When I try to open phpmyadmin I get this error(#1045 — Access denied for user ‘root’@’localhost’ (using password: YES))

<?php
/* Servers configuration */
$i = 0;
/* Server: localhost [1] */
$i++;
$cfg['Servers'][$i]['verbose'] = 'localhost';
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['port'] = '3306';
$cfg['Servers'][$i]['socket'] = '';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['extension'] = 'mysqli'; $cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = 'prakash123';
$cfg['Servers'][$i]['AllowNoPassword'] = true;
/* End of servers configuration */
$cfg['DefaultLang'] = 'en-utf-8';
$cfg['ServerDefault'] = 1;
$cfg['UploadDir'] = '';
$cfg['SaveDir'] = '';
/* rajk - for blobstreaming */
$cfg['Servers'][$i]['bs_garbage_threshold'] = 50;
$cfg['Servers'][$i]['bs_repository_threshold'] = '32M';
$cfg['Servers'][$i]['bs_temp_blob_timeout'] = 600;
$cfg['Servers'][$i]['bs_temp_log_threshold'] = '32M';
?>

I have tried to uninstall( Plus Deleted all the related files) WAMP and reinstall.It didn’t help either.
While reinstalling WAMP server it is not asking for any username password stuff I don’t know why.
Any help is highly appreciated.

asked May 30, 2013 at 20:54

4

I first changed the root password running mysql at a prompt with

mysql -u root -p

Update password:

UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';

Edited line in the file config.inc.php with the new root password:

$cfg['Servers'][$i]['password'] = 'MyNewPass'

Stop and re-start mysql service (in Windows: mysql_stop.bat/mysql_start.bat)

and got phpMyAdmin to work!

EDIT 2017: for MySQL≥5.7 use authentication_string in place of Password (see this answer):

`UPDATE mysql.user SET authentication_string=PASSWORD('MyNewPass') WHERE User='root';

`

Ritesh

4,4646 gold badges25 silver badges40 bronze badges

answered Oct 15, 2013 at 11:16

user2314737user2314737

24.9k18 gold badges94 silver badges105 bronze badges

2

The problem was I have 2 instances of Mysql installed and I didn’t know the password for both instances.Just check if port 80 is used by any of the programs.
This is what I did

1.Quit Skype because it was using port 80.(Please check if port 80 is used by any other program).

2.Search for Mysql services in task manager and stop it.

3.Now delete all the related mysql files.Make sure you delete all the files.

4.Reinstall

radbyx

9,08219 gold badges79 silver badges124 bronze badges

answered Aug 8, 2013 at 20:19

PrakashPrakash

7,4834 gold badges47 silver badges44 bronze badges

Well, there are many solutions already given above. If there are none of them works, maybe you should just try to reset your password again to ‘root’ as described here, and then reopen http://localhost/phpMyAdmin/ in other browser. At least this solution works for me.

answered Oct 27, 2013 at 20:18

yunhasnawayunhasnawa

79512 silver badges27 bronze badges

This worked for me.
In your config file

$cfg['Servers']['$i']['password'] = 'yourpassword';

In your mysql shell, login as root

mysql -u root

change your password or update if you’ve forgotten the old one

UPDATE mysql.user SET Password=PASSWORD('yourpassword') WHERE User='root';

stop and restart your mysql server from the xampp control panel. phpmyadmin can login to see your databases

answered Jul 1, 2018 at 23:43

mysql.exe->Run as administrator or go to following path C:\wamp\bin\mysql\mysql5.5.24\bin and than right click on mysql.exe go to properties and than select tab as Compatibility and see the bottom of dialog box in privilege level and just check the Run this program as an administrator and then click apply ok.finished now you open success phpMyadmin. bye

answered Aug 5, 2013 at 15:40

1. mysql -u root -p
2. UPDATE mysql.user SET Password=PASSWORD('mypass') WHERE User='root';
3. Flush the privileges: FLUSH PRIVILEGES;
4. Exit by typing: Exit

answered Dec 29, 2014 at 9:08

Go to ‘config.inc.php’. Write your password over here — $cfg['Servers'][$i]['password'] =''

answered Jan 21, 2015 at 17:42

halkujabrahalkujabra

2,7843 gold badges23 silver badges35 bronze badges

This process is quite simple in correcting the the error. What is happening is a failure to connect to phpMyAdmin. In order to fix the problem you simply need to provide the correct password to the system phpMyAdmin configuration file located in apps\phpMyadmin\config.ini.php
1. the root should already be set as user
2. Insert the password between ‘ ‘ and that it.

If you still have problems then this means that the user name and /or the password need to be updated or inserted into the DB. to do this use the command line tool and do an update.

UPDATE mysql.user SET Password=PASSWORD(‘Johnny59 or whatever you want to use’) WHERE User=’root’;

answered Jan 26, 2015 at 2:52

Go to config.inc.php, find $cfg['Servers'][$i]['password'] and remove any password provided, i.e change $cfg['Servers'][$i]['password'] = 'password'; with $cfg['Servers'][$i]['password'] = '';

Now you can launch phpMyAdmin

Selecting Users menu from phpMyAdmin, select the root user and click Edit previlidges.
Now scroll down to Change Password area, switch between No Password and Password to provide your new password.
that’s it.

answered Jun 20, 2015 at 8:52

I had the same error today. I had installed Djangostack and when I checked my task manager there were two instances of mysqld running. I checked one was for wamp server and the other was for django stack. I ended the one for django stack, restarted all services in wamp server and I was able to access phpmyadmin

answered Jun 22, 2015 at 9:15

NgenoNgeno

213 bronze badges

after installation i started wamp and i was asked for user and pass which were already set on default (user:admin pass: dots), and that was wrong with a message from your topic. Than, i just entered:

Username: root
Password: (leave it empty)

and it worked for me!!

answered Aug 2, 2016 at 11:39

0

1. Edit the config.inc.php file

For now on the PHPMyAdmin will ask you for your password, no more error.

ata

3,1705 gold badges19 silver badges31 bronze badges

answered Feb 20, 2018 at 20:35

Try the following code:

$cfg['Servers'][$i]['password'] = '';

if you see Password column field as ‘No’ for the ‘root’ user in Users Overview page of phpMyAdmin.

рüффп

4,91434 gold badges67 silver badges109 bronze badges

answered Oct 24, 2013 at 9:17

PerisPeris

293 bronze badges

with MariaDb, above solutions doesn’t works.

Use (exemple below with ubuntu 16.04 and mariadb-server Distrib 10.0.28):

 sudo mysql_secure_installation … Change the root password? [Y/n] New password: 

answered Jan 26, 2017 at 10:45

bcag2bcag2

1,7291 gold badge14 silver badges27 bronze badges

I have encountered similar mistakes, and later found that my password is wrong.

answered Aug 14, 2017 at 1:28

For UNIX, try this. It worked for me:

1. connect MySQL use Navicat Premium with inital root/»password»
2. UPDATE mysql.user
SET authentication_string = PASSWORD('MyNewPass'), password_expired = 'N'
WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;
3. restart MySQL

answered Feb 17, 2017 at 2:37

AaronAaron

191 bronze badge

1

First you have to go config.inc.php file then change the following instruction

$cfg['Servers'][$i]['user'] ='';
$cfg['Servers'][$i]['password'] =''; 

or

answered Aug 23, 2017 at 15:30

1

If you arrived here because you can’t log into your phpMyAdmin, then try the root password from your Mysql instead of the password you put during phpMyAdmin installation.

answered Oct 10, 2017 at 11:38

Kaizoku GambareKaizoku Gambare

2,8833 gold badges27 silver badges39 bronze badges

Just now I have this situation and I have tried this way which is very easy.

First stop your mysql service using this command:

service mysql stop

and then just again start your mysql service using this command

service mysql start

I hope it may help others… 🙂

STiLeTT

1,00311 silver badges23 bronze badges

answered Dec 1, 2017 at 10:51

Sachin ShahSachin Shah

4,3023 gold badges19 silver badges48 bronze badges

After I updated my MySql, I was getting the same error message.
It turned out that after installing a different version on MySql, inside the my.ini, the port was different. Previous MySql version had port 3306 but the new one have port 3308.
Check your MySql my.ini, if it is different use the port from .ini in your connection.

answered Jan 9, 2020 at 23:52

GuntarGuntar

4094 silver badges21 bronze badges

php artisan serve 

this command get the env contents for the first time and if you update .env file need to restart it.

in my case my username and dbname is valid and php artisan migrate worked

but need to cntrl+c , to cancel php artisan serve , and run it again

php artisan serve

answered Feb 19, 2020 at 17:51

Check the name of Environment Variable

Case with me:

• I was using sqlalchemy in a python-flask project and got this issue.
• IDE used: PyCharm
• In my config.py file I had setup SQLALCHEMY_DATABASE_URI as:

SQLALCHEMY_DATABASE_URI = os.getenv("DATABASE_URI")

Mistake I did (it was a silly mistake 😅)

• While setting evironment_variables in PyCharm, I did:

SQLALCHEMY_DATABASE_URI=<my_db_uri>

Solution

• I changed the above to:

DATABASE_URI=<my_db_uri>

Namaste 🙏

answered Apr 6, 2021 at 6:34

Deepam GuptaDeepam Gupta

1,8201 gold badge22 silver badges29 bronze badges

In the my.ini file in C:\xampp\mysql\bin, add the following line after the [mysqld] command under #Mysql Server:

skip-grant-tables

This should remove the error 1045.

answered Dec 20, 2017 at 7:23

1

if multiple myslq running on same port no
enter image description here

Right click on wamp and test port 3306
if its wampmysqld64 its correct else change port no and restart server

radbyx

9,08219 gold badges79 silver badges124 bronze badges

answered Jun 16, 2018 at 12:00

Why does access denied error occurs in MySQL?

This error may occur due to different reasons in different scenarios. Some of them are:

• When a user who doesn’t exist on the MySQL server tries to access the database.
• When there is no privilege for the user.
• If the username or password is wrong.

Today, we’ll discuss in detail how we fix this Access denied for user ‘root’@’localhost’ (using password yes) in MySQL.

How we fix ‘access denied for user ‘root’@’localhost’ (using password yes) in MySQL’?

Let’s see, how we fix this access denied error for our customers in detail.

a. Not able to login

Recently, one of our customers approached us saying that he is getting an error like the one shown below while he is trying to log in to MySQL.

So, our Support Engineers checked in detail and found that the root password is wrong. So, to reset the password we did the following:

1. Initially, we opened /etc/mysql/my.cnf

2. Then we added skip-grant-tables under [mysqld] section.

Here we skipped grant tables as there is no other way to log in.

By skipping the grant table option it allows anyone to log in from anywhere and can do anything on the database.

Note: skip-grant-tables is dangerous, so we will remove this at the end.

3 Then, we restarted the MySQL service using the command below:

service mysql restart

Now MySQL is configured to skip the grant-tables. So, we can log in now.

4. We logged into MySQL using:

mysql -u root 

5. Then, we flushed the privileges using:

6. After that, we set a new password using the command below: UPDATE user SET

Password=PASSWORD('my_password') where USER='root';
FLUSH PRIVILEGES;

7. Then, we removed skip-grant-tables from /etc/mysql/my.cnf

8. Finally, we restarted the MySQL service and logged in to MySQL using the new password.

This fixes the error.

b. No global privilege

If there is no global privilege then this error can happen. In such cases, our Engineers add the privilege by:

GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost';

This fixes the error.

[Need more assistance to fix MySQL Error?- We’re available 24/7.]

Причина возникновения ошибки 1045

Как ни банально, но единственная причина это неправильная комбинация пользователя и пароля. Обратите внимание, речь идет о комбинации пользователь и пароль, а не имя пользователя и пароль. Это очень важный момент, так как в MySQL пользователь характеризуется двумя параметрами: именем и хостом, с которого он может обращаться. Синтаксически записывается как ‘имя пользователя’@’имя хоста’.

Таким образом, причина возникновения MySQL error 1045 — неправильная комбинация трех параметров: имени пользователя, хоста и пароля.

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

Замечание: Важно понимать, что в базе не существует просто пользователя с заданным именем (например, root), а существует или пользователь с именем root, имеющий право подключаться с заданного хоста (например, root@localhost) или даже несколько разных пользователей с именем root (root@127.0.0.1, root@webew.ru, root@’мой домашний ip’ и т.д.) каждый со своим паролем и правами.

Примеры.
1) Если вы не указали в явном виде имя хоста

GRANT ALL ON publications.* TO ‘ODBC’ IDENTIFIED BY ‘newpass’;

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

2) Другой первопричиной ошибки mysql 1045 может быть неправильное использование кавычек.

CREATE USER ‘new_user@localhost’ IDENTIFIED BY ‘mypass’; — будет создан пользователь ‘new_user@localhost’@’%’

3) Неочевидный вариант. IP адрес 127.0.0.1 в имени хоста соответствует ключевому слову localhost. С одной стороны, root@localhost и ‘root’@’127.0.0.1’ это синонимы, с другой, можно создать двух пользователей с разными паролями. И при подключении будет выбран тот, который распологается в таблице привелегий (mysql.user) раньше.

4) Аккаунт с пустым именем пользователя трактуется сервером MySQL как анонимный, т.е. позволяет подключаться пользователю с произвольным именем или без указания имени.
Например, вы создали пользователя »@localhost с пустым паролем, чтобы каждый мог подключиться к базе. Однако, если при подключении вы укажите пароль отличный от пустого, то получите ошибку 1045. Как говорилось ранее, нужно совпадение трех параметров: имени пользователя, хоста и пароля, а пароль в данном случае не совпадает с тем, что в базе.

Что делать?

Во-первых, нужно убедиться, что вы используете правильные имя пользователя и пароль. Для этого нужно подключиться к MySQL с правами администратора (если ошибка 1045 не дает такой возможности, то нужно перезапустить сервер MySQL в режиме —skip-grant-tables), посмотреть содержимое таблицы user служебной базы mysql, в которой хранится информация о пользователях, и при необходимости отредактировать её.

SELECT user,host,password FROM mysql.user;
+—————+——————+——————————————-+
| user          | host            | password                                  |
+—————+——————+——————————————-+
| root          | house-f26710394 | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| aa            | localhost       | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |
| test          | localhost       |                                           |
| new_user      | %               |                                           |
|               | %               | *D7D6F58029EDE62070BA204436DE23AC54D8BD8A |
| new@localhost | %               | *ADD102DFD6933E93BCAD95E311360EC45494AA6E |
| root          | localhost       | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+—————+——————+——————————————-+

Если изначально была ошибка:

• значит вы указывали при подключении неверный пароль, так как пользователь root@localhost существует. Сам пароль храниться в зашифрованном виде и его нельзя узнать, можно лишь задать новый

• в данном случае в таблице привилегий отсутствует пользователь ‘ODBC’@’localhost’. Его нужно создать, используя команды GRANT, CREATE USER и SET PASSWORD.

Экзотический пример. Устанавливаете новый пароль для root@localhost в режиме —skip-grant-tables, однако после перезагрузки сервера по прежнему возникает ошибка при подключении через консольный клиент:
ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES)
Оказалось, что было установлено два сервера MySQL, настроенных на один порт.

Phpmyadmin

При открытии в браузере phpmyadmin получаете сообщение:

Error
MySQL said:

#1045 — Access denied for user ‘root’@’localhost’ (using password: NO)
Connection for controluser as defined in your configuration failed.
phpMyAdmin tried to connect to the MySQL server, and the server rejected the connection. You should check the host, username and password in your configuration and make sure that they correspond to the information given by the administrator of the MySQL server.

Ни логина, ни пароля вы не вводили, да и пхпадмин их нигде требовал, сразу выдавая сообщение об ошибке. Причина в том, что данные для авторизации берутся из конфигурационного файла config.inc.php Необходимо заменить в нем строчки

$cfg[‘Servers’][$i][‘user’] = ‘root’;      // MySQL user
$cfg[‘Servers’][$i][‘password’] = »;      // MySQL password (only needed

$cfg[‘Servers’][$i][‘user’] = ‘ЛОГИН’;      
$cfg[‘Servers’][$i][‘password’] = ‘ПАРОЛЬ’

Установка новой версии

Устанавливаете новую версию MySQL, но в конце при завершении конфигурации выпадает ошибка:

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

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

P.S. Статья написана по материалам форума SQLinfo, т.е. в ней описаны не все потенциально возможные случаи возникновения ошибки mysql №1045, а только те, что обсуждались на форуме. Если ваш случай не рассмотрен в статье, то задавайте вопрос на форуме SQLinfo
Вам ответят, а статья будет расширена.

Дата публикации: 25.11.2013

© Все права на данную статью принадлежат порталу SQLInfo.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.

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

Xpress

answered Dec 16, 2018 at 16:24

I was facing the same problem when I’m trying to connecting Mysql database using the Laravel application.
I would like to recommend please check the password for the user. MySQL password should not have special characters like #, &, etc…

answered Mar 13, 2020 at 16:23

1

In my case:

• I set plugin authentication to «» (empty) and I can’t run mysql server:

SOLUTION:

1. nano /etc/mysql/my.cnf
2. edit:

[mysqld]

skip-grant-tables

3. service mysql restart
4. mysql -u root
5. use mysql
6. UPDATE mysql.user SET plugin = ‘mysql_native_password’ WHERE User = ‘root’
7. flush privileges

answered Apr 27, 2021 at 12:08

CecherzCecherz

361 silver badge6 bronze badges

cause might be missing mysqld file in /var/run/mysqld

sudo service mysql stop
sudo mysqld_safe --skip-grant-tables
sudo service mysql start

if file does not exits then create file

mkdir -p /var/run/mysqld
chown mysql:mysql /var/run/mysqld

check now you are able to login mysql -uroot -p123
otherwise do

sudo mysql -u root
use mysql;
show tables;
describe user;
update user set authentication_string=password('1111') where user='root';
FLUSH PRIVILEGES;
exit;
mysql -uroot -p123

link — mysqld_safe Directory ‘/var/run/mysqld’ for UNIX socket file don’t exists

answered Jun 29, 2021 at 12:41

alter user 'root'@'localhost' identified with mysql_native_password by '$your_password$';

it worked for me.

note: use strong password
for example

alter user 'root'@'localhost' identified with mysql_native_password by 'root';

answered Mar 31 at 15:01

For windows:-

If the instance configuration fails with similar issue and if you cannot log in to the root account.

Steps I followed to fix the issue:—

• Stop MySql service if running.
• Uninstall MySql using ‘remove’ option from the installation wizard.
• If MySql service is not removed from services,

sc delete

• Delete all data containing in the MySql folder (‘Program files’, ‘Program data’ or the custom installation directory you have given).
• Remove MySql path from the environmental variable.
• Disable windows firewall.
• Reinstall and config root account with new password.

answered Jun 30 at 9:55

Same issue occurred with me also, turned out my db username was wrong

answered Jul 20 at 6:04

Создание нового пользователя при ошибке HY000/1045

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

phpMyAdmin ERROR: mysqli_real_connect(): (HY000/1045): Access denied for user 'pma'@'localhost' (using password: NO)

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

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

sudo mysql -u root -p

Теперь добавьте нового пользователя MySQL с именем пользователя по вашему выбору. В этом примере мы называем его pma. Обязательно замените ЗДЕСЬ ПАРОЛЬ своим собственным. Символ % здесь указывает MySQL разрешить этому пользователю входить в систему из любого места удалённо (а не только с локального компьютера). Если вам нужна повышенная безопасность, вы можете заменить это IP-адресом.

CREATE USER 'pma'@'%' IDENTIFIED BY 'ЗДЕСЬ ПАРОЛЬ';

Теперь мы предоставим нашему новому пользователю привилегию суперпользователя.

GRANT ALL PRIVILEGES ON *.* TO 'pma'@'%' WITH GRANT OPTION;

Теперь перейдите к файлу config.inc.php (может располагаться, например, в /etc/phpmyadmin/config.inc.php) и добавьте туда следующие строки:

/* User for advanced features */
$cfg['Servers'][$i]['controluser'] = 'pma';
$cfg['Servers'][$i]['controlpass'] = 'ЗДЕСЬ ПАРОЛЬ';

Связанные статьи

• Как установить веб-сервер Apache с PHP 7, MariaDB/MySQL и phpMyAdmin (LAMP) на Ubuntu (67.9%)
• Как включить доступ root к MySQL и MariaDB с любых хостов (67.9%)
• Ошибка «ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/run/mysqld/mysqld.sock’ (2 «No such file or directory»)» (РЕШЕНО) (65.1%)
• Ошибка «Failed — Network error» во время экспорта в phpMyAdmin (РЕШЕНО) (65.1%)
• Как установить веб-сервер (Apache, PHP, MySQL, phpMyAdmin) на Linux Mint, Ubuntu и Debian (62.8%)
• Гостевая машина VirtualBox не загружается если выбран тип подключения «Сетевой мост» (РЕШЕНО) (RANDOM — 1.2%)

Разрешить пользователю root подключаться к MySQL и MariaDB без sudo

Для получения доступа к базе данных MySQL/MariaDB обычному пользователю без использования sudo привилегий, зайдите в приглашение командной строки MySQL

sudo mysql

и запустите следующие команды:

Переключиться на аутентификацию по паролю можно следующим SQL запросом:

use mysql;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'ПАРОЛЬ';
exit

Обратите внимание, что вам нужно ввести ПАРОЛЬ.

Затем попробуйте войти в базу данных без sudo, как показано ниже.

mysql -u root -p

==================Устаревший метод==================

Это устаревший метод, он не работает в новых версиях MariaDB, но может по-прежнему работать в MySQL.

Для получения доступа к базе данных MySQL/MariaDB обычному пользователю без использования sudo привилегий, зайдите в приглашение командной строки MySQL

sudo mysql

и запустите следующие команды:

use mysql;
update user set plugin='' where User='root';
flush privileges;
exit

Затем перезапустите службу MySQL и попробуйте войти в базу данных без sudo, как показано ниже.

sudo systemctl restart mysql.service
mysql -u root -p

==================Конец устаревшего метода==================

Создание нового пользователя для работы с MySQL через phpMyAdmin

1. Подключитесь к mysql

sudo mysql --user=root mysql

2. Создайте пользователя для phpMyAdmin

Запустите следующие команды (замените some_pass на желаемый пароль):

CREATE USER 'phpmyadmin'@'localhost' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Если ваш phpMyAdmin подключается к localhost, то этого должно быть достаточно.

3. Опционально: разрешение удалённых соединений

Помните: разрешение удалённому пользователю иметь все привилегии – это вопрос безопасности, имейте это ввиду, выполняя последующие шаги. Если вы хотите, чтобы этот пользователь имел те же привилегии во время удалённых подключений, дополнительно выполните (замените some_pass на пароль, используемый на Шаге #2):

CREATE USER 'phpmyadmin'@'%' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

4. Обновите phpMyAdmin

Используя sudo, отредактируйте файл /etc/dbconfig-common/phpmyadmin.conf обновив значения пользователь/пароль в следующих секциях (замените some_pass на пароль, используемый на шаге #2):

# dbc_dbuser: database user
# the name of the user who we will use to connect to the database.
dbc_dbuser='phpmyadmin'
# dbc_dbpass: database user password
# the password to use with the above username when connecting
# to a database, if one is required
dbc_dbpass='some_pass'

Связанные статьи

• Аутентификация по паролю и unix_socket в MySQL и MariaDB. Исправление ошибки #1698 — Access denied for user ‘root’@’localhost’ (100%)
• Ошибка «ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/run/mysqld/mysqld.sock’ (2 «No such file or directory»)» (РЕШЕНО) (56%)
• Ошибка «mysqli_connect(): (HY000/1045): Access denied for user ‘username’@’localhost’» (РЕШЕНО) (56%)
• Ошибка «Failed — Network error» во время экспорта в phpMyAdmin (РЕШЕНО) (56%)
• Как установить веб-сервер Apache с PHP 7, MariaDB/MySQL и phpMyAdmin (LAMP) на Ubuntu (55.2%)
• Как изменить адрес страницы входа в phpMyAdmin (RANDOM — 53.1%)