MySQL single-instance reset password two ways

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:

2. 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

4. 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

6. 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 "/>

7. 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 "/>

9. 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