The solution of changing user password and forgetting administrator password in database

1 change the user password normally

If you remember the password, there are three ways to change the user's password:

(1) SET PASSWORD [FOR 'user'@'host'] = PASSWORD (' cleartext password')

(2) UPDATE mysql.user SET Password=PASSWORD ('cleartext password') WHERE User='USERNAME' AND Host='HOST'

(3) mysqladmin-uUSERNAME-hHOST-p password 'NEW_PASS' # can only change the administrator password

Examples

Use the internal key function password () to modify the root password

MariaDB [sunny] > set password for 'root'@'localhost' = password (' Pass123456')

MariaDB [sunny] > flush privileges

Use update to change your password

MariaDB [(none)] > update mysql.user set password=password ('Pass1234') where user='test'

Use mysqladmin to change the administrator password

[root@CentOS7A ~] # mysqladmin-uroot-pPass1234567 password 'Pass123456'

2 forget the administrator password

General steps for the solution:

(1) when starting the mysqld process, use the-- skip-grant-tables and-- skip-networking options; before operation, it is best to disable port 3306 to prevent other users from logging in, or disable network login-- skip-networking

CentOS 7:mariadb.service

CentOS 6:/etc/init.d/mysqld

(2) modify the administrator password through the UPDATE command

(3) start the mysqld process in normal way

The specific steps for CentOS7 to forget the administrator password are as follows

The prerequisite is to have system-level permissions, that is, to start and stop the service, and the steps to crack the administrator password are as follows

First, stop the mysql server

[root@CentOS7A ~] # systemctl stop mariadb.service

Then modify the configuration file

Vim / usr/lib/systemd/system/mariadb.serviceExecStart=/usr/bin/mysqld_safe-basedir=/usr-skip-grant-tables-skip-networking

Reload a file

[root@CentOS7A ~] # systemctl daemon-reload

Restart mqsql

[root@CentOS7A ~] # systemctl restart mariadb

At this point, you can log in to mysql without an account and password.

[root@CentOS7A ~] # mysql

Welcome to the MariaDB monitor. Commands end with; or\ g.

Change the root password after logging in to mysql, and modify the table mysql.user directly using the password function, as follows

MariaDB [(none)] > update mysql.user set password=PASSWORD ('Pass12345678') where user= "root"

After the password update is complete, stop the mysql service, then change the configuration file again, remove the skip option and restart

[root@CentOS7A ~] # systemctl stop mariadb; [root@CentOS7A ~] # vim / usr/lib/systemd/system/mariadb.service ExecStart=/usr/bin/mysqld_safe-basedir=/usr

Reload a file

[root@CentOS7A ~] # systemctl daemon-reload [root@CentOS7A ~] # systemctl restart mariadb

After the mysql service starts successfully, reconnect, and a new password is required to connect. In this case, the password is modified successfully.