Database change user password and forget Administrator password workaround

1 Normal change user password

Remember the password case, there are the following three ways to modify the user 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 ' #只能更改管理员密码

Example

Use the Inner key function password () to change the root password

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

MariaDB [sunny]> flush Privileges;

Change Password using update

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

Change the administrator password using mysqladmin

[[Email protected] ~] #mysqladmin-uroot-ppass1234567 password ' Pass123456 '

2 Forget admin password

Approximate steps of the solution:

(1) When starting the mysqld process, use the--skip-grant-tables and--skip-networking options; Before operation, it is best to disable 3306 ports, prevent other users from logging on, or disable network logon--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 a normal manner;

CentOS7 Forget the administrator password the following steps

Premise, with system-level permissions, that is, start-stop service, crack the administrator password steps are as follows

First, stop the MySQL server

[[Email protected] ~] #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

Overloaded files

[[Email protected] ~] #systemctl daemon-reload

Restart Mqsql

[[Email protected] ~] #systemctl Restart MARIADB

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

[[Email protected] ~] #mysql

Welcome to the MariaDB Monitor. Commands End With; or \g.

To change the root password after logging in to MySQL, use the password function to modify the table Mysql.user directly, 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

[[Email protected] ~] #systemctl stop mariadb; [[Email protected] ~] #vim/usr/lib/systemd/system/mariadb.service execstart=/usr/bin/mysqld_safe--basedir=/usr

Overloaded files

[[Email protected] ~] #systemctl daemon-reload[[email protected] ~] #systemctl Restart MARIADB

After the MySQL service starts successfully, reconnect, need to use the new password to connect, at this time the password modified successfully

Change the database user password and forget the Administrator password resolution