What to do if the password of MySQL super user is forgotten

1.1 MySQL 5.7x solution

1.1.1 first method

Use other users with super authority to make changes (without stopping the service), for example: chenliang @% user management

All libraries (*. *), permissions are all

#Log in the corresponding instance at the command line

mysql -uchenliang -p -h127.0.0.1 -P3306


#Use the update command to change the password of the root@localhost user

update mysql.user set authentication_string=PASSWORD('root') where user='root' and host='localhost';


#Check whether the password of the modified root@localhost user is encrypted and whether there is a problem of expiration

select user,host,authentication_string,password_expired from mysql.user where user='root' and host='localhost';


#Refresh permissions and log out of the current user

flush privileges;

exit;


#Check whether the modified password is effective

mysql -uroot -proot -S /data/mysql/3306/mysql.sock -e "show databases;"


#Check whether the startup script of mysql needs to be modified

If the way to stop the corresponding instance of MySQL in the MySQL startup script is "mysqladmin -uroot -p password - S path/mysql.sock"

In the "shutdown" mode, the corresponding password must be modified, otherwise the mysql service cannot be stopped with the script

1.1.2 second method

Without other users with super permission(Need to stop service),On the command line--skip-grant-tables

#See mysqlThe method to stop the startup script of the corresponding instance

A: If the method to stop the script is: mysqladmin -uroot -p Password -S path/mysql.sock shutdown,that

   ps -ef|grep mysql

   kill  PID Number Note: not available kill -9(Data will be lost)

B: How to stop a scriptNot for: mysqladmin -uroot -p Password -S path/mysql.sock shutdown,that(for example)

   /data/mysql/3306/mysqld stop


#Command line start mysqlCorresponding examples: for example

mysqld_safe --defaults-file=/data/mysql/3306/my.cnf --skip-grant-tables &

ps -ef|grep mysql

netstat -lntup|grep 3306


#Sign in mysqlCorresponding examples: for example:

mysql -uroot -p -S /data/mysql/3306/mysql.sock  <==When prompted for a password, enter directly


#modify root@localhostPassword

update mysql.user set authentication_string=PASSWORD('root') where user='root' and host='localhost';


#Seeroot@localhostWhether the modified password is encrypted or expired

select user,host,authentication_string,password_expired from mysql.user where user='root' and host='localhost';


#Refresh permissions and exit current login

flush privileges;

exit;


#Test whether the modified password is effective

mysql -uroot -p -S /data/mysql/3306/mysql.sock -e "show databases;" 

^==The password entered here is root


#inspect mysqlStop the startup script of the corresponding instance and restart it mysqlService of corresponding instance

A: If the method to stop the script is: mysqladmin -uroot -p Password -S path/mysql.sock shutdown,that

   a:Change the corresponding password in the script

   b:/data/mysql/3306/mysqld stop

   c:/data/mysql/3306/mysqld start

   d:netstat -lntup|grep 3306

B: How to stop a scriptNot for: mysqladmin -uroot -p Password -S path/mysql.sock shutdown,that(for example)

   a:/data/mysql/3306/mysqld stop

   b:/data/mysql/3306/mysqld start

   c: netstat -lntup|grep 3306

1.1.3 third method

Without other users with super permission(Need to stop service),stay my.cnf China Canada skip-grant-tables

#See mysqlThe method to stop the startup script of the corresponding instance

A: If the method to stop the script is: mysqladmin -uroot -p Password -S path/mysql.sock shutdown,that

   ps -ef|grep mysql

   kill  PID Number Note: not available kill -9(Data will be lost)

B: How to stop a scriptNot for: mysqladmin -uroot -p Password -S path/mysql.sock shutdown,that(for example)

   /data/mysql/3306/mysqld stop


#stay my.cnfIn the configuration file[mysqld]Add the following content under the module and save to exit

skip-grant-tables


#start-up mysqlCorresponding examples, such as:

/data/mysql/3306/mysqld start


#Sign in mysqlCorresponding examples: for example:

mysql -uroot -p -S /data/mysql/3306/mysql.sock çWhen prompted for a password, enter directly


#modify root@localhostPassword

update mysql.user set authentication_string=PASSWORD('root') where user='root' and host='localhost';


#Seeroot@localhostWhether the modified password is encrypted or expired

select user,host,authentication_string,password_expired from mysql.user where user='root' and host='localhost';


#Refresh permissions and exit current login

flush privileges;

exit;


#Test whether the modified password is effective

mysql -uroot -p -S /data/mysql/3306/mysql.sock -e "show databases;" 

^==The password entered here is root


#Before deleting my.cnfIn profile[mysqld]Added under module skip-grant-tablesparameter


#inspect mysqlThe method to stop the startup script of the corresponding instance,And restart mysqlService of corresponding instance

A: If the method to stop the script is: mysqladmin -uroot -p Password -S path/mysql.sock shutdown,that

   a:Change the corresponding password in the script

   b:/data/mysql/3306/mysqld stop

   c:/data/mysql/3306/mysqld start

   d:netstat -lntup|grep 3306

B: How to stop a scriptNot for: mysqladmin -uroot -p Password -S path/mysql.sock shutdown,that(for example)

   a:/data/mysql/3306/mysqld stop

   b:/data/mysql/3306/mysqld start

   c: netstat -lntup|grep 3306

Tags: MySQL mysqladmin

Posted on Mon, 02 Dec 2019 19:59:15 -0800 by CoderDan