MySQL single-instance reset password two ways

Source: Internet
Author: User

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:

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

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

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

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

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.