MySQL single-instance reset password two ways
In work learning, we sometimes forget the database password, the following is the MySQL single-instance password reset steps.
Description
(1) [[email protected] ~]# cat/etc/redhat-release
CentOS Release 6.7 (Final)
(2) [[email protected] ~]# MySQL--version
MySQL Ver 14.14 distrib 5.7.13, for Linux (i686) using Editline Wrapper
The main steps are as follows:
-
First stop MySQL
[[email protected] ~]# /etc/init.d/mysqld stop
/etc/init.d /mysqld:line:/usr/local/mysql:is a directory
/etc/init.d/mysqld:line:/usr/local/mysql/data:is a Director Y
Shutting down MySQL. success!
View the status of MySQL:
[[email protected] ~]#/etc/init.d/mysqld status
/etc/init.d/mysqld:line:/usr/local/mysql:is a directory
/etc/init.d/mysqld:line:/usr/local/mysql/data:is a directory
error! MySQL is not running
View MySQL process:
[[email protected] ~]# PS aux|grep mysql|grep-v grep
Using--skip-grant-tables to enable MySQL Ignore login authorization verification
[Email protected] ~]# mysqld_safe--skip-grant-tables--user=mysql &
[1] 6559
[Email protected] ~]# 2017-07-30t14:23:38.600285z mysqld_safe Logging to '/usr/local/mysql/data/mysqld.err '.
2017-07-30t14:23:38.640326z Mysqld_safe starting mysqld daemon with databases From/usr/local/mysql/data
Log in to MySQL without a password
[[email protected] ~]# MySQL
650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M00/9D/4F/wKioL1l97WCQU9L_AABB-D630V8553.png "title=" 11. PNG "alt=" Wkiol1l97wcqu9l_aabb-d630v8553.png "/>
Reset Root Password
Description: The new installation of MySQL5.7, prompt password error when logging on, the installation did not change the password, and later through password-free way to change the password, enter the update mysql.user set Password=password (' wtf123 ') where User= ' root ' and host= ' localhost ' when prompted for error 1054 (42S22): Unknown column ' Password ' in ' Field List ', The original is mysql5.7 database has no password This field, password field changed to authentication_string.
mysql> Update mysql.user set Authentication_string=password (' wtf123 ') where user= ' root ' and host= ' localhost ';
Query OK, 1 row affected, 1 Warning (0.00 sec)
Rows matched:1 changed:1 warnings:1
650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M01/9D/64/wKioL1l_SDfzPq3aAAAa1klLVCM547.png "title=" 11. PNG "alt=" Wkiol1l_sdfzpq3aaaaa1kllvcm547.png "/>
Flush:mysql> flush privileges;
Quit:mysql> quit
Description: Cannot use set Password=password (' wtf1234 ');
650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M01/9D/64/wKioL1l_RIGyAraqAAAVDs9w-js062.png "title=" 11. PNG "alt=" Wkiol1l_rigyaraqaaavds9w-js062.png "/>
Restart the service and log in again
[Email protected] ~]#/etc/init.d/mysqld restart
[Email protected] ~]# mysql-uroot-pwtf123
Description: View Database Password command:
Mysql> select user,host,authentication_string from Mysql.user;
650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M00/9D/64/wKioL1l_RdbTxN25AAA781Ww2Yo103.png "title=" 11. PNG "alt=" Wkiol1l_rdbtxn25aaa781ww2yo103.png "/>
Extension: Resetting the MySQL password by modifying the/ETC/MY.CNF configuration file
1. OpenMySQLthe configuration file, command:vim/etc/my.cnf. Add a new line to the configuration fileSkip-grant-tablesand the results are as follows:
650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M02/9D/64/wKiom1l_SeWgOqa6AAATTWx-SgU457.png "title=" 22. PNG "alt=" Wkiom1l_sewgoqa6aaattwx-sgu457.png "/>
2. Save and Exit!
3. Restart mysqld, command: Service mysqld restart
4. Log in to MySQL without a password
[[email protected] ~]# MySQL
mysql> Update mysql.user set Authentication_string=password (' wtf123 ') where user= ' root ' and host= ' localhost ';
Flush privileges; #刷新权限
650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/9D/64/wKioL1l_So2RF_QeAAAXnPgZ4A0969.png "title=" 11. PNG "alt=" Wkiol1l_so2rf_qeaaaxnpgz4a0969.png "/>
Quit: Quit
5. Restore after exiting my.cnf Restart the command as follows:
VIM/ETC/MY.CNF #OpenMySQLconfiguration file that willSkip-grant-tablesFront plus#;
/etc /init.d/mysqld restart # restart
login with new password mysql database, The command is as follows:
#mysql –uroot–p123456 can log into the database properly!
This article is from the "Hand of the Paladin Control" blog, please make sure to keep this source http://wutengfei.blog.51cto.com/10942117/1952520
MySQL single-instance reset password two ways