MySQL Reset password

MySQL Reset password

May for various reasons, we accidentally lost the MySQL password, to use the time can not login, can only reset the password! This configuration modification document is in the Linux Ubunt environment, other operating systems can also be used as a reference, the same principle!

1) Modify the MySQL configuration file, skip permission verification at login

CentOS是在 /etc/my.cnfUbunt的mysql配置文件路径:sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf    #在[mysqld]下追加上 skip-grant-tables    [mysqld]        ...    skip-grant-tables    

2) Restart the MySQL service to make the configuration effective

sudo service mysql restart

3) After restarting the MySQL service, we execute the mysql login command again, and we will find that we can log in without entering the password.

mysql -u root -p#回车完会提示输入密码,不用输密码直接回车即可登录

4) reset root account password

#重置root密码为空update mysql.user set authentication_string="" where user="root";   #重置 root 用户的密码为空（5.7 之前为 password 字段）#修改root密码update mysql.user set authentication_string=password("pswd") where user="root";#在重置 root 密码的同时，也可以设置默认密码。不过密码不能为明文，必须使用 password() 函数加密

5) Refresh Permissions table

flush privileges;

Reset the root password, refresh the permissions table and exit the MySQL command line, and remove the Skip permission verified configuration command that you just added in the configuration file. Finally, restart the MySQL service to log in with the root user password!

6) Remove the Add Skip Permission Verification configuration command to restart the MySQL service

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf    #删除或注释刚才在[mysqld]下追加 skip-grant-tables    [mysqld]        ...    #skip-grant-tables

Restart MySQL Service

sudo service mysql restart

For security reasons, we will not allow MySQL users to log in without a password, to set a password for the root account after resetting the password

Setting the password to remove the Bypass permission Validation command in the configuration file, restart the MySQL service login setting, or it will prompt for no authentication, no permission operation.

Method 1:

以 root 身份登录 mysql后，再使用 set password 命令修改密码：set password for [email protected] = password("new_password");

Method 2:

mysqladmin -u root -p password "new_password"#执行该命名后会提示输入原密码，输入正确后即可修改。

Method 3:

update mysql.user set authentication_string=password("pswd") where user="root";#在重置 root 密码的同时，也可以设置默认密码。不过密码不能为明文，必须使用 password() 函数加密使用该方法之后需刷新权限列表:flush privileges;

MySQL Reset password