Database.Guide

No matter what you enter, it prompts for insufficient permissions

mysql>  database sufadi;
ERROR  (): Access denied  user @  database 

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

mysql -u root -p

You can enable access for root using one MySQL command.

The Easiest Way to Fix Error 1698 (28000)

$ sudo mysql_secure_installation 

You need to enter a new password and, for confirmation, re-type that password.

If the password is less than 25 characters, you will be prompted to confirm whether you want to continue or not. If you don’t want to set password characters more than 25, simply type “n”.

In my case, it didn’t work and threw me an error on-screen:

This error is very common when you try to log in to your MySQL for the first time.

There are two ways to resolve this error.

First, you will see a simple and less time-consuming method. If the first method didn’t work in your case, then go to the second one.

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");

mysql> show GRANTS FOR test;
+-------------------------------------------------------------+
| Grants for test@%                                           |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `tt`.* TO 'test'@'%'                |
+-------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+

mysql> use mysql;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'

asked Jul 4, 2012 at 6:32

3 gold badges5 silver badges13 bronze badges

After you change privileges don’t forget to run «FLUSH PRIVILEGES» otherwise you changes wont affect.

answered Jul 5, 2012 at 20:23

1 gold badge4 silver badges5 bronze badges

You can also run mysql_secure_installation which will take care of a lot of this for you.

answered Jul 8, 2012 at 2:44

Gavin Towey

2 silver badges3 bronze badges

Try running this:

SELECT user,host,password FROM mysql.user WHERE user='';

mysql> select user,host,password from mysql.user;
+-----------+-------------+-------------------------------------------+
| user      | host        | password                                  |
+-----------+-------------+-------------------------------------------+
| lwdba     | 127.0.0.1   | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| lwdba     | localhost   | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| lwdba     | %           | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| root      | localhost   | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| root      | 127.0.0.1   | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| vanilla   | localhost   |                                           |
| mywife    | %           |                                           |
|           | %           |                                           | <<<--- LOOK !!!
| replicant | 10.64.113.% | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| kumar     | %           |                                           |
+-----------+-------------+-------------------------------------------+

So, how did you login? Run this query:

SELECT USER(),CURRENT_USER();

There was a problem when logging in to mysql data and want to switch to mysql database. The switch command is

When I went to see if there was a database named mysql, I found that there was no, the command was

Then create a mysql database, the command is

create databases mysql；

1. Close the mysql database

service mysql stop

Online talk will show

Can only stop first, and then enter the same command but it shows that the thread already exists

There is one more mysql thread, no matter what, let’s see what happens next step

3. Open a new terminal input

mysql -u root mysql

Some netizens said that the method would not work,

But method 2 doesn’t work here for me, I don’t know why.

I am a beginner, and if there is anything wrong, I hope you can advise me.

$ mysql -u root -p

Or we can get this error without providing the -p option like below.

$ mysql -u root

$ mysqladmin -u root -p

“Access denied for user ‘root’@’localhost’” Error Solution

$ sudo mysql

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypass123*.!';

If The Root User Password Is Unknown

$ sudo systemctl mysql restart

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypass123*.!';

The last steps are removing the skip-grant-table from the my.conf configuration file and restarting the MySQL service like below.

$ sudo systemctl mysql restart

Login MySQL Shell

$ mysql -u root -p

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 

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.

39 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:

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

answered Jan 7, 2014 at 10:47

3 gold badges46 silver badges67 bronze badges

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!

56 gold badges215 silver badges327 bronze badges

answered Jan 16, 2014 at 22:25

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.

answered Sep 24, 2014 at 12:36

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.

Any ideas what else I could try?

asked Aug 15, 2015 at 16:12

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

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

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

9 silver badges15 bronze badges

Can’t reset MySQL (MariaDB) root password

answered Aug 28, 2015 at 6:42

1 gold badge3 silver badges6 bronze badges

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

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

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

answered Oct 12, 2018 at 23:09

12 gold badges44 silver badges47 bronze badges

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;

I hope this is going to help someone.

answered Nov 5, 2017 at 13:44

Access Denied for User using Alter

Check the Authorization Mechanism

Run the below command in your terminal:

$ sudo mysql -u root

Once you are logged in, you will find the MySQL console looking like the below-shown image.

Now you can access your MySQL account with the above command, but there is a small problem with this: you cannot log in to MySQL without sudo privileges.

There are different types of password mechanisms supported by MySQL by default you will find “auth_socket” for your root account to make it robust you can choose conventional “mysql_native_password” or the latest “caching_sha2_password“ on later version 8.0.0

Before moving to the next step, you should check what password mechanism or plugin is attached to your root account.

mysql> SELECT User, plugin from mysql.user ;

The behavior of the above command is shown below:

Set a password on the root account

mysql> use mysql;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH 'caching_sha2_password' BY '[ENTER-NEW-PASSWORD]';

I’m using “caching_sha2_password”.

If you want to use “mysql_native_password” on > MySQL 5.7 version, type the below queries on your MySQL console.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY '[ENTER-NEW-PASSWORD]';

Once the changes have been implemented, restart the MySQL service.

$ sudo systemctl restart mysql.service

And once the service is restarted, type the below code in your terminal and enter the password which you have set above.

$ mysql -u root -p

mysqldump -u user -p --single-transaction database_name > database_backup.sql

mysql -u root -p

GRANT LOCK TABLES ON database_name.* TO 'user'@'localhost';

exit;

Ошибка: Access denied for user ‘root’@’localhost’ (Using password: YES и NO)

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

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

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

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

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

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

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

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

There are many similar questions, but none of them helped me.

I had been using MySQL on Windows with root/root credentials for a while, but since lately, I’m not able to login

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

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

> mysql -uroot -p
Enter password: ****
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

> mysql -uroot -proot
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)

> cat C:\mysql-init 
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';

> mysqld --init-file=C:\mysql-init --console

but there is an error line:

2015-12-17T10:34:14.616701Z 1 [ERROR] 1131  You are using MySQL as an anonymous user and anonymous users are not allowed to change passwords

asked Dec 17, 2015 at 10:40

1 gold badge1 silver badge8 bronze badges

Ok, I started mysqld with --skip-grant-tables option. After that, I was able to connect using mysql client and reset my password with

mysql> FLUSH PRIVILEGES;

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

answered Dec 18, 2015 at 7:53

1 gold badge1 silver badge8 bronze badges

Enable skip-grant-tables in my.ini and restart MySQL. Now root can login without password so that you can reset your password:

update mysql.user set password=password('newpassword') where user='root';

13 gold badges98 silver badges143 bronze badges

answered Dec 17, 2015 at 15:40

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

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

Based on the information

I had been using MySQL on Windows with root/root credentials

$ mysql -uroot -p
Enter password: ****
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

$ mysql -uroot -proot
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)

You will get this error when you enter wrong password.

To reset your root password:

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

answered Dec 17, 2015 at 11:05

Ahmad Abuhasna

4 gold badges22 silver badges36 bronze badges

I’ve found that sometimes, especially when I have more than one MySQL instance installed on a machine, I get similar errors, unless I explicitly specify the port (with -P) and/or host (with -h). Try this:

mysql -uroot -p -P3306 -h127.0.0.1

Substituting the actual port (check my.ini if you don’t know) of course.

In this case, it is possible that your password has not changed, does not need to be reset, etc. Try using your old, formerly functional root password with this login string.

answered Dec 18, 2015 at 21:56

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.

Wrapping Up

If you are interested in learning about how to change the MySQL port number, then check out this article.

See you later in the next article.

A man with a tech effusive who has explored some of the amazing technology stuff and is exploring more. While moving towards, I had a chance to work on Android development, Linux, AWS, and DevOps with several open-source tools.

Set password

Run as administrator

set password

mysql UPDATE user  PasswordPASSWORD()  user;
Query OK,   affected ( sec)
 matched:   Changed:   Warnings: 

refresh

mysql> flush privileges
    -Query OK, 0 rows affected (0.01 sec)

Re-login the cmd window

mysql -u root -p

:\Users\admin>mysql -u root -p
 password:

C:\Users\admin>mysql -u root -p
Enter password: ******
Welcome   MySQL monitor.  Commands   ;  \g.
Your MySQL connection   
Server :  MySQL Community Server (GPL)

Copyright (c) , , Oracle /  affiliates. All rights reserved.

Oracle  a registered trademark  Oracle Corporation / 
affiliates. Other names may be trademarks  their respective
owners.

Type 'help;'  '\h'  help. Type '\c'  clear  current input statement.

mysql>

Build a database

mysql> create database pythondb
Query OK,  row affected ( )

show databases

mysql> show databases;
+--------------------+

| informationschema |
| pythondb           |
| sufadi             |

7 rows in set (0.00 sec)

What privileges did you have when you logged in?

SHOW GRANTS;

• Feb 17, 2012 : MySQL : Why are there «test» entries in mysql.db?
• Feb 17, 2012 : What is the mysql.db table used for?
• Jan 18, 2012 : MySQL error: Access denied for user ‘a’@’localhost’ (using password: YES)

echo "SET PASSWORD FOR root@localhost=PASSWORD('password');" > /var/lib/mysql/rootpwd.sql
service mysql restart
rm -f /var/lib/mysql/rootpwd.sql

Give it a Try !!!

The reason

D:\python3-webapp-Su\www>mysql -u root -p
Enter password: ******
Welcome   MySQL monitor.  Commands   ;  \g.
Your MySQL connection   
Server :  MySQL Community Server (GPL)

Copyright (c) , , Oracle /  affiliates. All rights reserved.

Oracle  a registered trademark  Oracle Corporation / 
affiliates. Other names may be trademarks  their respective
owners.

Type 'help;'  '\h'  help. Type '\c'  clear  current input statement.

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

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.

Solve Access Denied for User Root Error

sudo mysql

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

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