MySQL 5.7修改root密码的4种方法

   

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:

 

 1 [root@zlm3 06:50:25 ~]
 2 #pkill mysqld
 3  
 4 [root@zlm3 06:50:29 ~]
 5 #ps -ef | grep mysqld
 6 root 4719 3724 0 06:52 pts/0 00:00:00 grep --color=auto mysqld
 7  
 8 [root@zlm3 06:52:51 ~]
 9 #pwd
10 /root
11  
12 [root@zlm3 06:56:50 ~]
13 #echo "alter user root@localhost identified by 'password';" > change_password.sql
14  
15 [root@zlm3 06:57:54 ~]
16 #cat change_password.sql
17 alter user root@localhost identified by 'password';
18  
19 [root@zlm3 06:58:04 ~]
20 #mysqld --defaults-file=/data/mysql/mysql3306/my.cnf --init-file=/root/change_password.sql &
21 [1] 4738
22  
23 [root@zlm3 06:59:30 ~]
24 #ps -efl|grep mysqld
25 0 R root 4770 3724 0 80 0 - 28160 - 06:59 pts/0 00:00:00 grep --color=auto mysqld
26 [1]+ Exit 1 mysqld --defaults-file=/data/mysql/mysql3306/my.cnf --init-file=/root/change_password.sql
27  
28 [root@zlm3 06:59:51 ~]
29 #mysql -p -S /tmp/mysql3306.sock
30 Enter password:
31 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql3306.sock' (2)
32 [root@zlm3 07:00:28 ~]

 

it's obviously that the mysqld process has not been startd normally,let's check the "error.log" file to find what have happened.error.log shows below:

 

 1 2018-05-31T05:15:13.520876Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
 2 2018-05-31T05:15:13.520915Z 0 [Note] IPv6 is available.
 3 2018-05-31T05:15:13.520920Z 0 [Note] - '::' resolves to '::';
 4 2018-05-31T05:15:13.520934Z 0 [Note] Server socket created on IP: '::'.
 5 2018-05-31T05:15:13.544976Z 0 [Note] Event Scheduler: Loaded 0 events
 6 2018-05-31T05:15:13.545087Z 0 [Note] Execution of init_file '/root/change_password.sql' started.
 7 2018-05-31T05:15:13.545108Z 0 [ERROR] mysqld: File '/root/change_password.sql' not found (Errcode: 13 - Permission denied)
 8 2018-05-31T05:15:13.545111Z 0 [ERROR] Aborting
 9 2018-05-31T05:15:13.545226Z 0 [Note] Giving 0 client threads a chance to die gracefully
10 2018-05-31T05:15:13.545233Z 0 [Note] Shutting down slave threads
11 2018-05-31T05:15:13.545237Z 0 [Note] Forcefully disconnecting 0 remaining clients
12 2018-05-31T05:15:13.545239Z 0 [Note] Event Scheduler: Purging the queue. 0 events
13 2018-05-31T05:15:13.545301Z 0 [Note] Binlog end
14 2018-05-31T05:15:13.547647Z 0 [Note] Shutting down plugin 'ngram'
15 2018-05-31T05:15:13.547666Z 0 [Note] Shutting down plugin 'BLACKHOLE'
16 2018-05-31T05:15:13.547669Z 0 [Note] Shutting down plugin 'partition'
17 2018-05-31T05:15:13.547671Z 0 [Note] Shutting down plugin 'ARCHIVE'
18 2018-05-31T05:15:13.547673Z 0 [Note] Shutting down plugin 'MyISAM'
19 2018-05-31T05:15:13.547678Z 0 [Note] Shutting down plugin 'CSV'
20 2018-05-31T05:15:13.547681Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
21 2018-05-31T05:15:13.547683Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
22 2018-05-31T05:15:13.547685Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
23 2018-05-31T05:15:13.547686Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
24 2018-05-31T05:15:13.547687Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
25 2018-05-31T05:15:13.547689Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
26 2018-05-31T05:15:13.547690Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
27 2018-05-31T05:15:13.547692Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
28 2018-05-31T05:15:13.547693Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
29 2018-05-31T05:15:13.547694Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
30 2018-05-31T05:15:13.547696Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
31 2018-05-31T05:15:13.547703Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
32 2018-05-31T05:15:13.547705Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
33 2018-05-31T05:15:13.547706Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
34 2018-05-31T05:15:13.547707Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
35 2018-05-31T05:15:13.547709Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
36 2018-05-31T05:15:13.547710Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
37 2018-05-31T05:15:13.547711Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
38 2018-05-31T05:15:13.547713Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
39 2018-05-31T05:15:13.547714Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
40 2018-05-31T05:15:13.547716Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
41 2018-05-31T05:15:13.547717Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
42 2018-05-31T05:15:13.547718Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
43 2018-05-31T05:15:13.547720Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
44 2018-05-31T05:15:13.547721Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
45 2018-05-31T05:15:13.547722Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
46 2018-05-31T05:15:13.547724Z 0 [Note] Shutting down plugin 'INNODB_CMP'
47 2018-05-31T05:15:13.547725Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
48 2018-05-31T05:15:13.547727Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
49 2018-05-31T05:15:13.547728Z 0 [Note] Shutting down plugin 'INNODB_TRX'
50 2018-05-31T05:15:13.547729Z 0 [Note] Shutting down plugin 'InnoDB'
51 2018-05-31T05:15:13.547781Z 0 [Note] InnoDB: FTS optimize thread exiting.
52 2018-05-31T05:15:13.547899Z 0 [Note] InnoDB: Starting shutdown...
53 2018-05-31T05:15:13.553631Z 0 [Note] InnoDB: Buffer pool(s) load completed at 180531 7:15:13
54 2018-05-31T05:15:13.553667Z 0 [Note] InnoDB: Dumping buffer pool(s) to /data/mysql/mysql3306/data/ib_buffer_pool
55 2018-05-31T05:15:13.553809Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 180531 7:15:13
56 2018-05-31T05:15:15.366016Z 0 [Note] InnoDB: Shutdown completed; log sequence number 1036828567
57 2018-05-31T05:15:15.366078Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
58 2018-05-31T05:15:15.366085Z 0 [Note] Shutting down plugin 'MEMORY'
59 2018-05-31T05:15:15.366090Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
60 2018-05-31T05:15:15.366093Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
61 2018-05-31T05:15:15.366111Z 0 [Note] Shutting down plugin 'sha256_password'
62 2018-05-31T05:15:15.366113Z 0 [Note] Shutting down plugin 'mysql_native_password'
63 2018-05-31T05:15:15.366263Z 0 [Note] Shutting down plugin 'binlog'
64 2018-05-31T05:15:15.370287Z 0 [Note] mysqld: Shutdown complete

View Code

 

okay,now we know about the reason why the mysqld process down,it was the privilege issue of OS code 13.let's check the privilege of "change_password.sql" then:

 

 1 [root@zlm3 07:41:36 ~] 
 2 #ls -l
 3 total 685212
 4 -rw-------. 1 root root 1431 Jul 16 2015 anaconda-ks.cfg 
 5 -rw-r--r-- 1 root root 52 May 31 06:57 change_password.sql
 6 -rwxr-xr-x 1 root root 641798603 Apr 28 14:02 mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz
 7 -rwxr--r-- 1 root root 54 Apr 28 14:14 mysqld.sh
 8 -rw-r--r-- 1 root root 451 May 30 05:18 mysqld.strace
 9 drwxr-xr-x 14 mysql mysql 4096 May 2 07:57 zabbix-3.0.16
10 -rwxr-xr-x 1 root root 59801600 May 2 07:55 zabbix-3.0.16.tar

 

first of all,i use command "chown mysql.mysql change_password.sql" to give the right ownership to the sql file,but it still don't work. why?'cause the father directory "/root" is not belong to the mysql user.then,i moved the file to the "/home/mysql" directory which owned by mysql user:

 

 1 [root@zlm3 07:41:37 ~]
 2 #mv change_password.sql /home/mysql
 3  
 4 [root@zlm3 07:42:11 ~]
 5 #cd /home/mysql
 6  
 7 [root@zlm3 07:42:14 /home/mysql]
 8 #ls -l
 9 total 4
10 -rw-r--r-- 1 mysql mysql 52 May 31 06:57 change_password.sql

 

let's start the mysqld process again,well,it's running now:

 

 1 [root@zlm3 07:42:33 ~]
 2 #mysqld --defaults-file=/data/mysql/mysql3306/my.cnf --init-file=/home/mysql/change_password.sql &
 3 [1] 5181
 4  
 5 [root@zlm3 07:42:45 ~]
 6 #ps aux|grep mysqld
 7 mysql 5181 3.2 17.5 1069676 179052 pts/0 Sl 07:42 0:00 mysqld --defaults-file=/data/mysql/mysql3306/my.cnf --init-file=/home/mysql/change_password.sql
 8 root 5215 0.0 0.0 112640 960 pts/0 R+ 07:42 0:00 grep --color=auto mysqld
 9  
10 [root@zlm3 07:42:52 ~]
11 #mysql -p -S /tmp/mysql3306.sock
12 Enter password: <here the new Password is "password">
13 Welcome to the MySQL monitor. Commands end with ; or \g.
14 Your MySQL connection id is 4
15 Server version: 5.7.21-log MySQL Community Server (GPL)
16 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
17 Oracle is a registered trademark of Oracle Corporation and/or its
18 affiliates. Other names may be trademarks of their respective
19 owners.
20 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
21 (root@localhost mysql3306.sock)[(none)]07:43:38>

 

Summary:

 

• when changing the password of root,shutdown the mysqld process once is necessary.
• method 1~3 based on the parameter "--skip-grant-tables",the only difference is using different gramma.
• method 1~2 need to use "flush privileges;" before excecution the spercific changing command.
• method 4 is more convenient,so i rather recommend to use this way to achive your purpose.
• putting the parameter "init-file=<your sql file path>" under the "[mysqld],[mysqld_safe],[server]" group is also a workaround,but i don't recommend that.
• once you've executed "flush privileges;" ,it means the privilege table has been updated,then you must use the specific password you've changed just now with "-p" parameter to login the MySQL server,even if your parameter "skip-grant-tables" is still in my.cnf,only if you restart the mysqld process.

 

for example:

 

 1 #mysql -p -S /tmp/mysql3306.sock
 2 Enter password: <here put the right password>
 3 Welcome to the MySQL monitor. Commands end with ; or \g.
 4 Your MySQL connection id is 5
 5 Server version: 5.7.21-log MySQL Community Server (GPL)
 6 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 7 Oracle is a registered trademark of Oracle Corporation and/or its
 8 affiliates. Other names may be trademarks of their respective
 9 owners.
10 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
11  
12 (root@localhost mysql3306.sock)[(none)]08:52:25>exit
13 Bye
14  
15 [root@zlm3 08:53:26 ~]
16 #mysql -p -S /tmp/mysql3306.sock
17 Enter password: <here put the wrong password>
18 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
19  
20 [root@zlm3 08:53:32 ~]
21 #mysql -S /tmp/mysql3306.sock --not using "-p" parameter
22 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
23 
24 #[root@zlm3 09:04:55 ~]