Access denied for user root localhost using password no windows

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.

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

Пример.

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 |
+—————+——————+——————————————-+

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

• 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 получаете сообщение:

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

In mysql, Access denied for user ‘root’@’localhost’ error is due to the root user not having sufficient privilege to access the database. Either the root user password is incorrect, or the root user password is not set, or the root user has insufficient privileges.

In mysql, “access denied for user ‘root’@’localhost’ (using password: yes)” error is shown while the access denied for user root localhost using password yes. In mysql, “access denied for user ‘root’@’localhost’ (using password: no)” error is shown while the access denied for user root localhost using password no.

In mysql, if you attempt to access the root password, it throws error saying “Access denied for user ‘root’@’localhost’”. This exception is due to various reasons. Here we can see how this error is handled and how to correct it.

All the privileges should be granted to root user. By default, mysql does not provide the root user with all of the privileges. These privileges should be added to the root user.

If the root user has trouble logging in, the error below will appear in mysql 

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

Root cause

You try to connect mysql using root user. Mysql denied access to login with root user. The denial could be due to various reason. Mysql requires valid user name and password to login. The user must have enough privileges to login and should be provided with enough permission.

The above error will be thrown due to any of the above reason is not satisfied. The root password is mismatch or nor provided. The user does not have enough permission to login to the system.

How to reproduce this issue

This error occurs under various conditions.This error will be seen if you login with root user without password. It is a easy way of creating this error. There are various ways to replicate this error.

mysql -u root 

Solution 1

The first option is to use the command below to attempt login with username and correct password. This command will prompt you to enter your password. Enter the right password. Mysql will validate the username and password. If both are matching with the existing system, then mysql will allow to login. The error “Access denied for user ‘root’@’localhost’” is thrown due to the invalid password.

$>mysql -u root -p 
Enter password:

Solution 2

If you have forgotten the root user, the following steps will help you to update your root password. The root user will be modified in PASSWORD() function with the provided password. The password is set as “password” in the example below. This step will allow to reset the root user password.

$> mysqld --skip-grant-tables

$> mysql -u root mysql

mysql> update mysql.user set password=PASSWORD("password") where User='root';

mysql> flush privileges;

Solution 3

By default, the root user will not have a plugin attached. Update the root user with the plugin ‘ mysql native password ‘ This plugin ‘ mysql native password ‘ allows native authentication. For any command, the error “Access denied for user ‘root’@’localhost’” is shown if the mysql native password plugin is not set to the root user.

$> mysqld --skip-grant-tables

$> mysql -u root mysql

mysql> update mysql.user set plugin = 'mysql_native_password' where User='root';

mysql> flush privileges;

Solution 4

The password of the root user and the plugin must be changed. The alter command will change the password of the root user and update the plugin as “mysql native password.” The plugin stores a password in a native format and allows native authentication.

$> mysqld --skip-grant-tables

$> mysql -u root mysql

mysql> ALTER mysql.user 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

mysql> FLUSH PRIVILEGES;

mysql> exit

start mysql again

Solution 5

The root user has the admin privileges. Provide all permissions for the root user to connect from the local system. The grant command will add the privileges to the root command. If you want to revert the privilege, use the ‘revoke’ command. The ‘revoke’ command will remove the privileges from the user.

mysql> CREATE USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
        or
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

mysql> grant all privileges on *.* to 'root'@'localhost' identified by 'password' with grant option;

mysql> FLUSH PRIVILEGES;

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:

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. 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]
3. Start the instance
4. Access with root user (mysql -uroot -hlocalhost); 

5. 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’;”

6. Stop the instance
7. Edit my.cnf and remove skip-grant-tables and skip-networking
8. Start MySQL again
9. 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

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

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

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

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

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

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

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

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

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

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

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

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

you can use

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

to set new password.

or, use

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

   WHERE User = ‘root’;

to set new password.

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

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

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

to set new password.

or,

use

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

   WHERE User = ‘root’;

to set new password.

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

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

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

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

Related >>

How to change the MySql user(root) password tutorial

How to reset the forgotten MySql root password

Yoodley is reader-supported. When you buy through links on our site, we may earn an affiliate commission.

Have you recently been facing the error that says ‘Access denied for user ‘root’@’localhost’, whenever you try to operate MySQL?  In MySQL, the relational database management system, the error might show up when you attempt to access the root password. 

This post elucidates all possible solutions to help fix the error. However, it is equally important to understand what is causing the error ‘Access denied for user ‘root’@’localhost’ in the first place. 

Therefore, let us briefly understand what is causing MySQL to throw up the error ‘Access denied for user ‘root’@’localhost’, and then move on to solutions to fix the problem, followed by answering a few of the most asked questions relevant to the same.

Why does MYSQL say ‘Access denied for user ‘root’@’localhost’?

In all simplicity, you are facing the error ‘Access denied for user ‘root’@’localhost’ because as the root user, you do not have the adequate privilege (permission, in other terms) to access the MySQL database.

Now, to further the discussion, you could be facing the issue due to multiple reasons, some of which include:

• When a user is non-existent to the MySQL server tries accessing the MySQL database.
• When no privilege exists for the user (as mentioned).
• If the user inputs the wrong username or password.

Now the error ‘Access denied for user ‘root’@’localhost’ is extensively of two types:

• access denied for user ‘root’@’localhost’ (using password: yes)
• access denied for user ‘root’@’localhost’ (using password: no)

The former error message is thrown up when the root user tries to access the MySQL database with the password ‘yes’, which is wrong or different from the original password. Similarly, the latter error message occurs when the root user enters the password as ‘no’, which is incorrect.

Now that you know what could possibly be restraining you from accessing the database and throwing the message ‘Access denied for user ‘root’@’localhost’, below we look at X solutions to fix the same.

Solution:

In theory, it is the lack of permissions that throw the error ‘Access denied for user ‘root’@’localhost’. To provide all the permissions to the root user, you can use the ‘grant’ command to add privileges. Use the following chain of commands to grant privileges to the root user:

mysql> CREATE USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;

        or

mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;

mysql> grant all privileges on *.* to ‘root’@’localhost’ identified by ‘password’ with grant option;

mysql> FLUSH PRIVILEGES;

To take away the privileges and revert the changes, you can use the ‘revoke’ command. The ‘revoke’ command will do exactly what it sounds like: revoke all the privileges from the root user once you choose to remove the permissions/privileges.

Solution:

If your MySQL 5.7 (or higher version) is running in Ubuntu systems, the root user is to be authenticated using not the password but the auth_sccket plugin by default. 

Although this can come in handy and provide great usability and security, it can cause difficulty when you are trying to allow programs from the outside, such as phpMyAdmin, to access the user.

The main solution to this is to connect MySQL as root by switching authentication from auth_socket to mysql_native_password in the terminal:

sudo mysql

• Run the ALTER USER command to configure the root account and change the authentication type to password.

ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;

• Now, run FLUSH PRIVILEGES; in the command prompt.
• To confirm if the authentication configuration has been changed to a password, type the following command:

SELECT user,authentication_string,plugin,host FROM mysql.user;

• Once confirmed, exit MySQL shell using 

exit

Solution:

This method works specifically for all macOS users. 

• Firstly, install a version of MySQL that is compatible with your MacOS’s version.
• Next, set the password for root during installation and note it down somewhere. Use the following command to set the root password:  

root <root-password>

• Select Use Legacy Password Encryption from the two options available, the other one being Use Strong Password Encryption.
• Using the search tool, open MySQL.prefPane and select the configuration tab.
• Click ‘Select’ option of the ‘Configuration File’ and select /private/etc/my.cnf
• Now, from the MySQL terminal, open a new file or an existing file, and type the following command:

 [mysqld]

 skip-grant-tables

With skipping the grant table, you can log in from anywhere and do almost anything on the database server.

• Next, restart MySQL as follows:

ps aux | grep mysql

kill -9 <pid1> <pid2>

• Upon restarting, run the following command to fix the error:

/usr/local/mysql-<version>-macos<version>-x86_64/bin/mysql -uroot -p<root-password>

Solution:

As we mentioned previously, you could be facing the error because you have been feeding the server the wrong password. Whether you don’t know the password or forgot the correct one, here is how you can reset it, so you can access the MySQL database as a root user again:

• Open /etc/mysql/my.cnf from Configuration File. Now add ‘skip-grant-tables’ under [mysqld] section. Note that ‘skip-grant-tables’ is dangerous; therefore make sure to remove it towards the end of the solution.
• Restart MySQL with the command given below:

service mysql restart

• Since you have added the line of code ‘skip-grant-tables‘ already, you will be able to log in since MySQL will be skipping the grant-tables. Use the command below to log in:

mysql -u root

• Once logged in, flush privileges that are restraining you from accessing the database originally using the command below:

mysql> flush privileges;

• Now, set a new password using the command below:

Password=PASSWORD(‘my_password’) where USER=’root’;

FLUSH PRIVILEGES;

• Since you have already reset the password, it is time to remove ‘skip-grant-tables’ from etc/mysql/my.cnf.
• Restart MySQL again and log in using the new password. The service will no longer show the error.

FAQs

1. Where is the MYSQL error log?

Error logs are one of the most important logs in terms of IT operations because it helps in detecting and diagnosing functional problems that simply improves performance. 

The MYSQL error log basically contains error messages, warnings and different notes which are created during the startup and shutdown phases.

MYSQL error logging is always enabled and allows the users to set destination, verbosity levels and time zone. 

A file or the console is a general location or, say, destination of error logs. When no location is specified, then in windows, the error logs are written to host_name.err ( host_name is the host system name) in the data directory, whereas in UNIX/Linux, the console is the default destination of errors. 

A user can change the destination of error logs by specifying the location in the –log-error option (i.e., –log-error=”G:/TMP/mysql_logs/mysql_error.err” or –log-error=/var/log/mysql/error.log).

2. How to change the root password for MySQL?

To change the root password in MySQL:

• In order to change the password for MYSQL, a new file must be created with the following contents – ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘PASSWORD’ ; (here password is the new password to be used) and this file should be saved as ~/mysql-pwd. 
• Stop MYSQL with sudo systemctl stop mysql command and then issue the command: sudo mysqld -init-file=~/mysql-pwd. As the command prompt returns, restart the MYSQL using the sudo systemctl start mysql command. 
• Now the MYSQL root user password is changed successfully, and you can login with the new admin password. 

3. How to recover the root password for MySQL?

To recover the root password in MySQL:

• Use the sudo service mysql stop command to stop the MYSQL server.
• Now start the MYSQL server using the sudo mysqld_safe –skip-grant-tables –skip-networking &
• Use the mysql -u root command to connect to the MYSQL server as the root user.
• Issue the following commands to reset the root password:

mysql> use mysql;

​mysql> update user set authentication_string=password(‘NEWPASSWORD’) where user=’root’;

​mysql> flush privileges;

​mysql> quit

Note: In the above commands, NEWPASSWORD is the new password to be used.

• Restart the MYSQL daemon using sudo service mysql restart and log in with the new password.