MySQL settings for user passwords (modify, reset, retrieve)

1. log in to MySQL

1.1 Single-Instance login

1) MySQL has just installed MySQL without password in the case of login

2) mysql–u root just installed MySQL login without password

3) mysql–u root–p Standard DBA Login

3) mysql–u root–p ' password ' no interactive login. Generally not, easy to leak password

Prompt:mysql> after successful login

1.2 Multi-Instance login

mysql–u root–s Specify the location of the Mysql.sock file

Tip: The only difference from a single instance is that multiple instances need to specify the location of the Mysql.sock

2. Modify user password

1.1 interactively modify the root password

• 
  

  • Single instance: mysqladmin–u root–p password ' new password '

[[email protected] ~]# mysqladmin-u root-p password ' jeck123 ' Enter password:-----> enter Old password

• 
  

  • Multi-instance:mysqladmin–u root–p password ' new password '-s mysql.sock location

[Email protected] ~]# mysqladmin-u root-p password ' jeck123 '-s/usr/local/mysql/tmp/mysql.sock Enter password: -----> enter Old password

Note: If you have just finished loading MySQL , you Root The password is empty, the direct return can

1.2 non-interactive modification of root password

• 
  

  • Single instance: Mysqladmin–u root–p ' old password ' password ' new password ' (note that there are no spaces after-p)

[Email protected] ~]# mysqladmin-uroot-p ' jeck123 ' password ' 123jeck '

• 
  

  • Multi-instance: Mysqladmin–u root–p ' old password ' password ' new password '-s mysql.sock location

[Email protected] ~]# mysqladmin-uroot-p ' jeck123 ' password ' 123jeck '-s/usr/local/mysql/tmp/mysql.sock

This is used for non-interactive password modification, for scripting

1.3 to change the password in the MySQL database

Method One: (Only the password of the currently logged in MySQL user can be modified)

[[email protected] ~]# mysql -uroot -p123jeckwelcometo the mysql  Monitor.  commands end with; or \g.yourmysql connection id is  4serverversion: 5.1.56-log source distributioncopyright (c)  2000, 2010,  Oracle and/or its affiliates. all rights reserved. thissoftware comes with absolutely no warranty. this is free  software,andyou are welcome to modify and redistribute it under  The gpl v2 licensetype ' help; '  or  ' \h '  for help. Type  ' \c '  to clear the current input  statement.mysql> setpassword=password (' 456jeck '); query ok, 0 rows  affected  (0.00 sec) mysql> flushprivileges; query ok, 0 rows affected  (0.00 SEC) mYsql> exit 

method Two: (can modify any user's password, as long as there is modify permission)

[[email protected] ~]# mysql -uroot -p456jeckwelcometo the mysql  Monitor.  commands end with; or \g.yourmysql connection id is  6serverversion: 5.1.56-log source distributioncopyright (c)  2000, 2010,  Oracle and/or its affiliates. all rights reserved. thissoftware comes with absolutely no warranty. this is free  software,andyou are welcome to modify and redistribute it under  The gpl v2 licensetype ' help; '  or  ' \h '  for help. Type  ' \c '  to clear the current input  statement.mysql> updatemysql.user set password=password (' 789jeck ')  where  user= ' root '; query ok, 3 rows affected  (0.00 sec) rows matched: 3  changed : 3&Nbsp; warnings: 0mysql> flushprivileges; query ok, 0 rows affected  (0.00 sec) mysql>

3. Retrieve user password

3.1 turn off MySQL

[Email protected]~]# service mysqld Stopshuttingdown MySQL. success! [Email protected] ~]# NETSTAT-LNT | grep mysqld

3.2 start MySQL with Mysqld_safe, need to add-skip-grant-tables (Ignore authorization authentication)

Single instance:

[[email protected]~]# /usr/local/mysql/bin/mysqld_safe--skip-grant-tables &    ---> Add &, so that the process runs in the background [1]2864315010315:56:25 mysqld_safe logging to  '/usr/local/ Mysql-5.1.56/data/mysql.err '. 15010315:56:25 mysqld_safe starting mysqld daemon with  databases from/usr/local/mysql-5.1.56/data[[email protected] ~]# mysql -uroot  -pEnter password:               ----> Direct enter because the validation welcometo the mysql monitor.  commands end with; has been ignored  or \g.yourmysql connection id is 1serverversion: 5.1.56-log source  distributioncopyright (c)  2000, 2010, Oracle and/or its affiliates.  All rights reserved. thissoftware comes with absolutely no warranty. this is free  SoftwAre,andyou are welcome to modify and redistribute it under the  gpl v2 licensetype ' help; '  or  ' \h '  for help. Type  ' \c '  to clear the current input  statement.mysql> setpassword=password (' jeck123 ');             ---> This method does not work error 1290  (HY000): the mysql server  Is running with the--skip-grant-tables option so it cannot execute  this statementmysql> updatemysql.user set password=password (' jeck123 ')  where  user= ' root ';   query ok, 3 rows affected  (0.00 sec) rows  matched: 3  Changed:3  Warnings: 0mysql> flushprivileges; query ok, 0 rows affected  (0.00 sec) Mysql> exitbye

[[email protected]~]# /usr/local/mysql/bin/mysqld_safe--defaults-file=/etc/my.cnf -- skip-grant-tables &[1]2864315010315:56:25 mysqld_safe logging to  '/usr/local/ Mysql-5.1.56/data/mysql.err '. 15010315:56:25 mysqld_safe starting mysqld daemon with  databases from/usr/local/mysql-5.1.56/data[[email protected] ~]# mysql -uroot  -p   -S/usr/local/mysql/tmp/mysql.sock  Enter password:               ----> Direct enter because the validation welcometo the has been ignored  mysql monitor.  commands end with; or \g.yourmysql connection  id is 1serverversion: 5.1.56-log source distributioncopyright (c)  2000,  2010, oracle and/or its affiliates. all rights reserved. thissoftware comes with absolutely No warranty. this is free software,andyou are welcome to modify  and redistribute it under the gpl v2 licensetype ' help; '  or  ' \h '  for help. Type  ' \c '  to clear the current input  statement.mysql> updatemysql.user set password=password (' jeck123 ')  where  user= ' root ';               ----> Change root password    Query OK, 3 rows affected  (0.00 sec) rows matched:  3  Changed:3  Warnings: 0mysql> flushprivileges; query ok, 0 rows affected  (0.00 sec) Mysql> exitbye

3.3 Restart MySQL ( must be restarted)

[[Email protected]~]# service mysqld restartshuttingdown mysql.150103 16:07:17 mysqld_safe mysqld from PID file/usr/local /mysql-5.1.56/data/mysql.pid ended success! Startingmysql. success! [1]+ Done/usr/local/mysql/bin/mysqld_safe--skip-grant-tables

3.4 you can log in with the newly modified password.

[Email protected]~]# mysql-uroot-pjeck123welcometo the MySQL monitor. Commands End With; or \g.yourmysql connection ID is 1serverversion:5.1.56-log Source distributioncopyright (c) #, Oracle and/or its Affiliates. All rights reserved. Thissoftware comes with absolutely NO WARRANTY. This was free software,andyou was welcome to modify and redistribute it under the GPL v2 licensetype ' help; ' or ' \h ' for Hel P. Type ' \c ' to clear the current input statement.mysql>

This article is from the "Study-everyday" blog, make sure to keep this source http://studys.blog.51cto.com/9736817/1599104

MySQL settings for user passwords (modify, reset, retrieve)