MySQL forget root password solution

Posted by rejoice on Sat, 04 Apr 2020 00:08:14 +0200

Now the mysql database we use is divided into two versions, one is mysql and the other is mariadb. There are some differences between the two versions of the solution, which I will explain below.

First step

First, we need to turn on my.cnf

vim /etc/my.cnf

Add skip grant tables under [mysqld], then save and exit

If there is no my.cnf file, we can add skip grant tables under [MySQL D] of / etc/mysql/mysql.conf.d/mysqld.cnf, and then save and exit

Restart mysql server

systemctl restart mysql(mariadb)
service mysql(mariadb) restart

The second step

At this time, we do not need to enter a password when logging in to mysql, but we can change the root password. After logging in, we can enter the mysql database

[root@ub1 ~]$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.21-0ubuntu0.16.04.1 (Ubuntu)
... ...
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Change Password:

UPDATE user SET password=PASSWORD('your_new_passwd')WHERE User='root' ;

But there is no password field in mysql, but there is an authentication string:

mysql> SELECT User,Host,Password FROM user;
ERROR 1054 (42S22): Unknown column 'Password' in 'field list'
mysql> SELECT User,Host,authentication_string FROM mysql.user;
| User             | Host      | authentication_string                     |
| root             | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| debian-sys-maint | localhost | *91A4592A144CD258480130DC9002C5D61E82238D |
4 rows in set (0.01 sec)

So when we change the password, we change the password field to authentication [string:

mysql> UPDATE user SET authentication_string=PASSWORD('root')WHERE User='root';
Query OK, 0 rows affected, 1 warning (0.31 sec)
Rows matched: 1  Changed: 0  Warnings: 1


mysql> flush privileges;

The third step

Comment out skip grant tables in / etc/my.cnf (/ etc/mysql/mysql.conf.d/mysqld.cnf)


systemctl restart mysql(mariadb)
service mysql(mariadb) restart

Topics: MySQL MariaDB Database vim