MySQL forgot root password solution

When using MySQL, if you forget the other user's password, you can use the root user to reset, but if you forget the root password, you need to use a special method to operate.
Directly modify the authorization table can modify the root password, the following detailed steps, take mysql5.7 as an example.

1. Stop the MYSQLD service process

   [[email protected] ~]# systemctl stop mysqld.service [[email protected] ~]# netstat -ntpln | grep 3306  //检测mysql是否已经启动

2. Start the database with mysqld combined with skip-grant-tables, which is useful for users who log on without using the authorization table, so users can log in without using a password directly.

   [[email protected] ~]# mysqld --skip-grant-tables&[[email protected] ~]# netstat -ntpul | grep 3306  //检测mysql是否已经启动tcp6       0      0 :::3306                 :::*                    LISTEN      

3. You can log in to MySQL without using a password, and use update to change the root password.

[[email protected] ~]# mysqlWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.7.17 Source distributionmysql> update mysql.user set authentication_string=password(‘123abc‘) where user=‘root‘; //修改root密码为123abcQuery OK, 1 row affected, 1 warning (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 1

4. Refresh the database, using update to modify the password, only in the database has been modified, in-memory data is not modified. The purpose of flush privileges is to extract the user information and permission settings from the MySQL library into memory in the current users and privilege tables. After the MySQL user data and permissions have been modified, you will need to execute the following command if the MySQL service is not restarted directly.

mysql> flush privileges;      //刷新数据库Query OK, 0 rows affected (0.01 sec)

5. Login test with new password, login success indicates successful modification.

[[email protected] ~]# mysql -uroot -p123abc   //登录mysqlmysql: [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 4Server version: 5.7.17 Source distribution

Note Here the login password after-p directly followed, there can be no space, otherwise there will be errors.

MySQL forgot root password solution