MySQL修改root密码的多种方法

方法1: 用SET PASSWORD命令

  mysql -u root

  mysql> SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘newpass’);

方法2:用mysqladmin

  mysqladmin -u root password “newpass”

  如果root已经设置过密码,采用如下方法

  mysqladmin -u root password oldpass “newpass”

方法3: 用UPDATE直接编辑user表

  mysql -u root

  mysql> use mysql;

  mysql> UPDATE user SET Password = PASSWORD(‘newpass’) WHERE user
= ‘root’;

  mysql> FLUSH PRIVILEGES;

在丢失root密码的时候,可以这样

  mysqld_safe –skip-grant-tables&

  mysql -u root mysql

  mysql> UPDATE user SET password=PASSWORD(“new password”) WHERE
user=’root’;

  mysql> FLUSH PRIVILEGES;

方法1: 用SET
PASSWORD命令 mysql -u root mysql SET PASSWORD FOR ‘root’@’localhost’ =
PASSWORD(‘newpass’); 方法2:用mysqladmin mysqlad…

   

    sometimes we will forget our password
of root in MySQL DB server.so,there’re several methods below to solve
these kind of issues.

 

美高梅电子游戏,I. ALTER USER …

  1. pkill
    mysqld
  2. vim
    my.cnf -> add skip-grants-tables
  3. sh
    mysqld.sh
  4. mysql -S
    /tmp/mysql3306.sock
  5. flush
    privileges;
  6. alter user root@localhost identified by
    ”;
  7. login
    again using new password
  8. exit &
    modify my.cnf to the original state

 eg 1:

 

 1 #mysql -S /tmp/mysql3306.sock
 2 Welcome to the MySQL monitor. Commands end with ; or \g.
 3 Your MySQL connection id is 1
 4 Server version: 5.7.21-log MySQL Community Server (GPL)
 5 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 6 Oracle is a registered trademark of Oracle Corporation and/or its
 7 affiliates. Other names may be trademarks of their respective
 8 owners.
 9 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
10  
11 (root@localhost mysql3306.sock)[(none)]03:23:51>alter user root@localhost identified by 'innodb';
12 ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
13  
14 (root@localhost mysql3306.sock)[(none)]03:24:18>flush privileges; 
15 Query OK, 0 rows affected (0.00 sec)
16  
17 (root@localhost mysql3306.sock)[(none)]03:24:41>alter user root@localhost identified by 'innodb';
18 Query OK, 0 rows affected (0.00 sec)
19  
20 (root@localhost mysql3306.sock)[(none)]03:24:53>quit;
21 Bye
22  
23 #mysql -p -S /tmp/mysql3306.sock 
24 Enter password: <here the new Password is "innodb">

 

II. SET PASSWORD …

  1. pkill
    mysqld
  2. vim
    my.cnf -> add skip-grants-tables
  3. sh
    mysqld.sh
  4. mysql -S
    /tmp/mysql3306.sock
  5. flush
    privileges;
  6. set password for
    root@localhost=”; –also can use password() function
    here
  7. login
    again using new password
  8. exit &
    modify my.cnf to the original state

 eg 2:

 

 1 #mysql -S /tmp/mysql3306.sock
 2 Welcome to the MySQL monitor. Commands end with ; or \g.
 3 Your MySQL connection id is 2
 4 Server version: 5.7.21-log MySQL Community Server (GPL)
 5 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 6 Oracle is a registered trademark of Oracle Corporation and/or its
 7 affiliates. Other names may be trademarks of their respective
 8 owners.
 9 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
10  
11 (root@localhost mysql3306.sock)[(none)]03:32:24>set password for root@localhost='mysql'; -- or,set password for root@localhost=password('mysql')
12 ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
13  
14 (root@localhost mysql3306.sock)[(none)]03:33:13>flush privileges;
15 Query OK, 0 rows affected (0.00 sec)
16  
17 (root@localhost mysql3306.sock)[(none)]03:33:25>set password for root@localhost='mysql';
18 Query OK, 0 rows affected (0.00 sec)
19  
20 (root@localhost mysql3306.sock)[(none)]03:33:32>exit
21 Bye
22  
23 #mysql -p -S /tmp/mysql3306.sock 
24 Enter password: <here the new Password is "mysql">

 

III. UPDATE MYSQL.USER SET

  1. pkill
    mysqld
  2. vim
    my.cnf -> add skip-grants-tables
  3. sh
    mysqld.sh
  4. mysql -S
    /tmp/mysql3306.sock
  5. flush
    privileges; –this step is not
    indispensable
  6. update
    mysql.user set authentication_string=password(”) where …
    ; –must use password()
    function,don’t forget where clause to specify condition
  7. login
    again using new password
  8. exit &
    modify my.cnf to the original state

 eg 3:

 1 #mysql -S /tmp/mysql3306.sock
 2 Welcome to the MySQL monitor. Commands end with ; or \g.
 3 Your MySQL connection id is 3
 4 Server version: 5.7.21-log MySQL Community Server (GPL)
 5 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 6 Oracle is a registered trademark of Oracle Corporation and/or its
 7 affiliates. Other names may be trademarks of their respective
 8 owners.
 9 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
10  
11 (root@localhost mysql3306.sock)[(none)]03:42:32>update mysql.user set authentication_string=('oracle') where user='root' and host='localhost';
12 Query OK, 1 row affected (0.00 sec)
13 Rows matched: 1 Changed: 1 Warnings: 0
14  
15 (root@localhost mysql3306.sock)[(none)]03:43:50>select user,host,authentication_string from mysql.user; 
16 +---------------+---------------+-------------------------------------------+
17 | user | host | authentication_string |
18 +---------------+---------------+-------------------------------------------+
19 | root | localhost | oracle |
20 | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
21 | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
22 | repl | 192.168.1.% | *872ECE72A7EBAC6A183C90D7043D5F359BD85A9E |
23 | zlm | 192.168.1.% | *B746A45EBB84DD9DDEF015B332281AEFD164E2A9 |
24 | zlm | 192.168.1.102 | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
25 | zlm | 192.168.1.1 | *2447D497B9A6A15F2776055CB2D1E9F86758182F |
26 | zlm | 192.168.1.103 | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
27 +---------------+---------------+-------------------------------------------+
28 8 rows in set (0.00 sec)

 

*    be careful,if you don’t using the password() function to get your
password,then you’ll get a wrong result,and you cannot use the password
“oracle” to login the mysql server.*

 

 1 (root@localhost mysql3306.sock)[(none)]03:44:00>update mysql.user set authentication_string=password('oracle') where user='root' and host='localhost';
 2 Query OK, 1 row affected, 1 warning (0.00 sec)
 3 Rows matched: 1 Changed: 1 Warnings: 1
 4 (root@localhost mysql3306.sock)[(none)]03:44:18>select user,host,authentication_string from mysql.user; 
 5 +---------------+---------------+-------------------------------------------+
 6 | user | host | authentication_string |
 7 +---------------+---------------+-------------------------------------------+
 8 | root | localhost | *2447D497B9A6A15F2776055CB2D1E9F86758182F |
 9 | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
10 | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
11 | repl | 192.168.1.% | *872ECE72A7EBAC6A183C90D7043D5F359BD85A9E |
12 | zlm | 192.168.1.% | *B746A45EBB84DD9DDEF015B332281AEFD164E2A9 |
13 | zlm | 192.168.1.102 | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
14 | zlm | 192.168.1.1 | *2447D497B9A6A15F2776055CB2D1E9F86758182F |
15 | zlm | 192.168.1.103 | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
16 +---------------+---------------+-------------------------------------------+
17 8 rows in set (0.00 sec)
18 (root@localhost mysql3306.sock)[(none)]03:44:25>exit
19 Bye
20 [root@zlm3 03:45:03 ~]
21 #mysql -p -S /tmp/mysql3306.sock 
22 Enter password: <here the new Password is "oracle">

 

*IV. USING –INIT-FILE WITHOUT
–SKIP-GRANT-TABLES
(Recommended)*

  1. pkill
    mysqld
  2. add
    “alter user …” into file change_pass.sql
  3. start
    mysqld with –init-file=<yourpath>/change_pass.sql

eg 4:

 

Author

发表评论

电子邮件地址不会被公开。 必填项已用*标注