Error 1045 28000 access denied for user admin localhost using password no - Исправление ошибок и поиск оптимальных решений проблем

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

Порядок действий для установки/смены пароля root в mysql следующий:

1. Остановить mysql:
sudo service mysql stop

2. Запустить сервис со следующими параметрами:
sudo mysqld --skip-grant-tables --user=root

Если выдал ошибку то в файле /etc/mysql/mysql.conf.d/mysqld.cnf в секцию [mysqld] добавить строчкуskip-grant-tables и выполнить sudo service mysql restart

3. После этого подключиться к mysql командой:
mysql -u root

4. Обновить пароль root’a:

UPDATE mysql.user SET authentication_string=PASSWORD('<новый пароль>'), plugin='mysql_native_password' WHERE User='root' AND Host='localhost';    
FLUSH PRIVILEGES;

5. И перезапустить сервис:
sudo service mysql restart
Если на шаге 2 вы добавляли skip-grant-tables в /etc/mysql/mysql.conf.d/mysqld.cnf — удалить эту строчку.

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

Пароль по умолчанию пустой.
Возможно, вы неправильно набрали команду. Скопируйте именно эту: mysql -u root -p. На запрос пароля надо просто нажать Enter.

Попробуйте запустить mysql_secure_installation.

Если все равно не пускает — поищите пароль в логе: sudo grep 'temporary password' /var/log/mysqld.log.

Если и этот вариант не подошел — возможно, устанавливаете из какого-то левого репозитория. Удалите sudo apt-get purge mysql*, выключите левые репозитории и установите заново sudo apt-get install mysql-server.

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

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

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

Источник

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

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

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

I tried:

mysql -u root password '123'

But it shows me:

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

After this I try:

mysql -u root -p

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

Update: as Bozho suggested, I did the following:

I stopped the MySQL Service from Windows services
Opened CMD
Changed the location to c:program filesmysqlbin
Executed the command below

mysqld --defaults-file="C:\program files\mysql\mysql server 5.1\my.ini" --init-files=C:\root.txt
The command ran with a warning about character set which I mentioned below
I start the MySQL service from Windows services
I write in the command line

mysql -u root -p
EnterPassword: 123 // 123 was the password
The command line shows the following error

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

How do I solve this?

user

4,8555 gold badges17 silver badges35 bronze badges

asked Jun 8, 2010 at 5:53

Nasser HadjlooNasser Hadjloo

12.1k15 gold badges69 silver badges99 bronze badges

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

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

Stop the service/daemon of mysql running

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

Start mysql without any privileges using the following option;
This option is used to boot up and do not use the privilege system of MySQL.
```
 [root ~]# mysqld_safe --skip-grant-tables &
```

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

enter the mysql command prompt
```
 [root ~]# mysql -u root
 mysql> 
```

Fix the permission setting of the root user ;

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

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

    mysql> grant all privileges on *.* to root@localhost identified by '' with grant option;
    Query OK, 0 rows affected (0.01 sec)*
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)

Confirm the results:

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

Exit the shell and restart mysql in normal mode.
```
 mysql> quit;
 [root ~]# kill -KILL [PID of mysqld_safe]
 [root ~]# kill -KILL [PID of mysqld]
 [root ~]# service mysql start
```
Now you can successfully login as root user with the password you set
```
  [root ~]# mysql -u root -pYourNewPassword 
  mysql> 
```

Ryan M♦

17.3k30 gold badges60 silver badges71 bronze badges

answered May 29, 2014 at 2:37

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

answered Jun 8, 2010 at 5:54

BozhoBozho

582k142 gold badges1053 silver badges1136 bronze badges

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

C:wampbinmysqlmysql5.1.53bin by default.

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

    > SET PASSWORD FOR root@localhost = PASSWORD('new-password');

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

C:wampappsphpmyadmin3.3.9config.inc.php

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

C:xamppphpMyadminconfig.inc.php

It looks like this:

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

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

If you priviously changed the password for ‘root@localhost’, then you have to do 2 things to solve the error «Access denided for user ‘root@localhost'»:

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

This will resolve the error.

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

Andreas

6,4152 gold badges35 silver badges46 bronze badges

answered Dec 25, 2012 at 8:35

yellobirdyellobird

2793 silver badges3 bronze badges

Use mysql -u root -p
It will ask for password, insert password and enter.

answered May 16, 2019 at 11:10

I was getting the same error on OS X El captain.
Mysql version 5.7 . I was able to connect to mysql with root after executing these steps.

Stop the mysql server

sudo mysql.server stop

Start mysql in safe mode

sudo mysqld_safe --skip-grant-tables

Using mysqld, Change the database to mysql and update the details for user ‘root’.

show databases;
use mysql;
UPDATE mysql.user 
    SET authentication_string = PASSWORD('MyNewPass'), password_expired = 'N'
    WHERE User = 'root' AND Host = 'localhost';
exit;

After that kill the ‘mysqld_safe’ process and start mysql normally. You should be able to login to mysql using root and new password. SQL docs for more details

answered Jul 5, 2016 at 10:54

Simply edit my.ini file in C:xamppmysqlbin path. Just add:

skip-grant-tables

line in between lines of # The MySQL server [mysqld] and port=3306. Then restart the MySQL server.

Looks like:

Stephen Rauch♦

46.7k31 gold badges109 silver badges131 bronze badges

answered Mar 16, 2018 at 0:19

Ryan OscarRyan Oscar

2791 gold badge4 silver badges19 bronze badges

For some information I’ve get error after changing password:

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

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

In both cases there was error.

But the thing is after that I’ve tried it with

mysql -uroot -ppassword instead of

mysql -u root -p password -> with spaces between -uroot and -ppassword so maybe if someone get trouble can try this way.

answered Mar 1, 2020 at 18:39

Make sure the MySQL service is running on your machine, then follow the instructions from MySQL for initially setting up root (search for ‘windows’ and it will take you to the steps for setting up root):

Securing the Initial MySQL Account

answered Jun 8, 2010 at 5:56

sholsappsholsapp

15.2k10 gold badges47 silver badges65 bronze badges

Another solution if someone gets the error The specified password for user account ‘root’ is not valid, or failed to connect to the database server also with the right password, is the follow

•In the Windows registry, delete the mysql_pwd reg key under HKCUSoftwareMicrosoftWebPlatformInstaller

•Unistall older version of MySQL .NET connector

•Download and install the latest MySql .NET Connector.

answered Apr 5, 2014 at 18:50

SilverstormSilverstorm

14.7k2 gold badges36 silver badges52 bronze badges

Change the password from config.inc.php present in C:xamppphpMyAdmin.
Type mysql -u root -p in the command prompt.
You will be asked to enter the password. Enter that password which you updated in the config.inc.php.

double-beep

4,85916 gold badges32 silver badges41 bronze badges

answered May 19, 2020 at 15:39

In your code replace the ‘root’ with your Server username and password with your server password.
For example if you have DB and your php files on the server http://www.example.com
then obviously you would have to enter into this server site using your username and password.

answered Jun 22, 2015 at 19:21

Pir Fahim ShahPir Fahim Shah

10.3k1 gold badge79 silver badges79 bronze badges

For MySQL 5.7. These are the below steps:

Stop your MySQL server completely. This can be done by accessing the Services window inside Windows XP and Windows Server 2003, where you can stop the MySQL service.

Open your MS-DOS command prompt using «cmd» inside the Run window. Inside it navigate to your MySQL bin folder, such as C:MySQLbin using the cd command.

Execute the following command in the command prompt: mysqld.exe -u root —skip-grant-tables

Leave the current MS-DOS command prompt as it is, and open a new MS-DOS command prompt window.

Navigate to your MySQL bin folder, such as C:MySQLbin using the cd command.

Enter mysql and press enter.

You should now have the MySQL command prompt working. Type use mysql; so that we switch to the «mysql» database.

Execute the following command to update the password:

update user set authentication_string=password(‘1111′) where user=’root’;

answered Dec 1, 2016 at 2:30

KatieKatie

4527 silver badges18 bronze badges

Some times it just happens due to installation of Wamp or changing of password options of root user.
One can use privilages—>root (user) and then set password option to NO to run the things without any password OR set the password and use it in the application.

answered Feb 5, 2015 at 8:07

If you are using XAMPP just go to C:xamppphpMyAdmin and then open config.inc.php find $cfg['Servers'][$i]['password'] = '' line and put your password there.

answered Aug 4, 2018 at 6:22

Ajay RawatAjay Rawat

1672 silver badges10 bronze badges

if you changed the port to non standard one, then you need to specify it:

$connection = mysqli_connect('localhost:3308', 'root', '', 'loginapp');

answered May 21, 2020 at 18:54

If the root account exists but has no password, connect to the server as root using no password, then assign a password. This was my situation when I encountered this issue.

Connect to the server as root using no password:

$> mysql -u root --skip-password

Assign a password:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root-password';

I was able to solve my problem this way. Hope this helps someone who encounters a similar issue in the future. Cheers!

Reference: https://dev.mysql.com/doc/refman/8.0/en/default-privileges.html

answered Jan 9, 2022 at 17:10

K M Rakibul IslamK M Rakibul Islam

33.3k12 gold badges88 silver badges107 bronze badges

mysqladmin -u root -p password

enter your current password

then

enter your new password

0bserver07

3,2901 gold badge28 silver badges56 bronze badges

answered Oct 7, 2013 at 16:26

djrconceptsdjrconcepts

6255 silver badges6 bronze badges

It happens because of the security reason.

try with the following

mysql -u root -p

click enter and enter the password and try again

answered Apr 27, 2021 at 8:30

Источник

During our work in support, we see this again and again: “I try to connect to MySQL and am getting a 1045 error”, and most times it comes accompanied with “…but I am sure my user and password are OK”. So we decided it was worth showing other reasons this error may occur.

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, 0 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, 0 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’;

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

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

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

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, 0 rows affected (0.00 sec)

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

Query OK, 0 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, 0 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:

Stop the instance
Edit my.cnf and add skip-grant-tables under [mysqld] (this will allow access to MySQL without prompting for a password). On MySQL 8.0, skip-networking is automatically enabled (only allows access to MySQL from localhost), but for previous MySQL versions it’s suggested to also add –skip-networking under [mysqld]
Start the instance
Access with root user (mysql -uroot -hlocalhost);
Issue the necessary GRANT/CREATE USER/SET PASSWORD to correct the issue (likely setting a known root password will be the right thing: SET PASSWORD FOR ‘root’@’localhost’ = ‘S0vrySekr3t’). Using grant-skip-tables won’t read grants into memory and GRANT/CREATE/SET PASSWORD statements won’t work straight away. First, you need to execute “FLUSH PRIVILEGES;” before executing any GRANT/CREATE/SET PASSWORD statement, or you can modify mysql.users table with a query which modifies the password for User and Host like “UPDATE mysql.user SET authentication_string=PASSWORD(‘newpwd’) WHERE User=’root’ and Host=’localhost’;”
Stop the instance
Edit my.cnf and remove skip-grant-tables and skip-networking
Start MySQL again
You should be able to login with root from the localhost and do any other necessary corrective operations with root user.

Learn more about Percona Server for MySQL

Источник

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

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

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

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

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

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

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

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

Why is access denied?

asked Mar 28, 2013 at 19:44

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

Look at how you tried to logged in

mysql -u repl -p123

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

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

mysql -u repl -p123 -h127.0.0.1

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

Try it like this:

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

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

answered Mar 28, 2013 at 20:40

RolandoMySQLDBARolandoMySQLDBA

177k32 gold badges307 silver badges505 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

MannojMannoj

1,4992 gold badges14 silver badges34 bronze badges

The problem is these two accounts, added by default.

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

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

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

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

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

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

answered Mar 30, 2013 at 2:02

Michael — sqlbotMichael — sqlbot

22.2k2 gold badges46 silver badges75 bronze badges

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

mysql <enter>

Server version: xxx

Copyright (c) xxx

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

Mysql [(none)]>

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

mysql_secure_installation

answered Apr 18, 2020 at 7:45

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

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

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

answered Dec 13, 2019 at 22:59

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

Please do a check on those

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

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

If they are corrupt then you can fix them using

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

Thanks,

Masood

answered Mar 31, 2013 at 17:25

Источник

Дата: 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 при попытке подключения с пустым паролем.

Причина возникновения ошибки 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’;

то у вас будет создан пользователь ‘ODBC’@’%’ и при попытке подключения с локальной машины вы получите ошибку:

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

так как пользователя ‘ODBC’@’localhost’ у вас не существует.

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, в которой хранится информация о пользователях, и при необходимости отредактировать её.

Пример.

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

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

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

SET PASSWORD FOR root@localhost=PASSWORD(‘новый пароль’);
ERROR 1045 (28000): Access denied for user ‘ODBC’@‘localhost’ (using password: YES)

в данном случае в таблице привилегий отсутствует пользователь ‘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. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.

Источник

I’m trying to install queXS cati app on my Ubuntu desktop and I installed MySQL server and PHP 5 and I cannot login into MySQL server as root without password:

mysql -u root

it says

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

But it’s okay when I enter mysql -u root -p

I can’t figure out what the problem is.

oerdnj

7,88038 silver badges49 bronze badges

asked Jan 7, 2014 at 10:42

Add switch -p for password based login:

mysql -u root -p

That is the normal behaviour. You set a root password for your database so from now on you can’t access it without password. That is why it reports:

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

Obviously when you give the password with the -p switch you succeed.

answered Jan 7, 2014 at 10:47

falconerfalconer

14.7k3 gold badges45 silver badges66 bronze badges

In simple words your «root» session does not know the password of the mysql root user.

If you want to make it easier to access your mysql, create a file .my.cnf in /root/ with these lines:

[mysqladmin]
 user = root
 password = mysqlrootpassword
[mysql]
 user = root
 password = mysqlrootpassword
[mysqldump]
 user = root
 password = mysqlrootpassword

where of course mysqlrootpassword is your password for mysql’s root password. When you execute mysql it uses this password.

Attend to the safety of this file — give it secure rights, so that nobody on your server can read it!

Zanna♦

68.2k55 gold badges210 silver badges320 bronze badges

answered Jan 16, 2014 at 22:25

Login to webmin and under servers, access the mySQLdatabase server.You will then be able to set the (user) password provided you have:

mysql -u (**user**) -p < /usr/share/doc/rsyslog-mysql-5.8.10/createDB.sql.

The web gui is easy but to be secure use the terminal.

cheers.

answered Sep 24, 2014 at 12:36

Источник

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 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!

Answer

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

Solution:- reset root password

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

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

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

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

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

↑ Starting and Stopping MariaDB ↑

Comments

Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.

Источник