How to restore MySQL database password

• Stop Mysql server

• Skip authorization table to start MySQL service program

• Reset root password (update user table record)

• Restart MySQL service program in normal way

Password recovery instance

Example 1: reset MySQL management password

• Stop the running MySQL service program first

[root@host50 ~]# systemctl stop mysqld
[root@host50 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Tue 2019-07-02 03:54:56 CST; 6s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 1426 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 1083 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 1430 (code=exited, status=0/SUCCESS)

Jul 02 03:31:22 host50 systemd[1]: Starting MySQL Server...
Jul 02 03:31:36 host50 systemd[1]: Started MySQL Server.
Jul 02 03:54:55 host50 systemd[1]: Stopping MySQL Server...
Jul 02 03:54:56 host50 systemd[1]: Stopped MySQL Server.

• Skip the authorization table to start the MySQL service program (see the last line of the document for the configuration -- skip grant tables option)

[root@host50 ~]# vim /etc/my.cnf
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
secure_file_priv="/myload"
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
default-storage-engine=innodb
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
skip_grant_tables=1

• After reconnecting mysql, reset the local login password of root user by modifying the record in user table of MySQL database.

[root@host50 ~]# systemctl restart mysqld
[root@host50 ~]# mysql -uroot
mysql> UPDATE mysql.user SET authentication_string=PASSWORD('123456')
-> WHERE user='root' AND host='localhost';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1
mysql> flush privileges; 
Query OK, 0 rows affected (0.01 sec)
mysql> exit

Note: execute "FLUSH PRIVILEGES;" to make the authorization table take effect immediately. For normal MySQL service, you can also use the above method to change the password without restarting the service. In this case, because the password is recovered, it is better to restart the MySQL service program, so the above "FLUSH PRIVILEGES;" operation can be skipped.

• Restart the Mysql service program in the normal way, and verify the new password (note skip > grant > tables option)
  

[root@host50 ~]# vim /etc/my.cnf
[mysqld]
#skip_grant_tables=1
.
.
.
[root@host50 ~]# systemctl restart mysqld
[root@host50 ~]# mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[root@host50 ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql>

Example 2: reset Mysql administrative user password (known password)

• Method 1: using mysqladmin management tool, you need to verify the old password

[root@host50 ~]# mysqladmin -u root -p password 'qaz123edc'                    
Enter password:                                   
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

• Method 2: after logging in mysql as root, use the set password command to set (you must first configure validate_password_policy=0)

mysql> set password for root@localhost=password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

• Method 3: log in mysql as root and use grant authorization tool to set

mysql> grant all on *.* to root@localhost identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

• Method 4: after logging in MySQL as root, update the corresponding table records with update

mysql> update mysql.user set authentication_string=password('123456')
    -> where user='root' and host='localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1