当前位置：移动技术网 > IT编程>数据库>Mysql > MySQL 5.7修改root密码的4种方法

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

2018年06月03日 | 移动技术网IT编程 | 我要评论

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

pkill mysqld
vim my.cnf -> add skip-grants-tables
sh mysqld.sh
mysql -S /tmp/mysql3306.sock
flush privileges;
alter user root@localhost identified by '';
login again using new password
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 ...

pkill mysqld
vim my.cnf -> add skip-grants-tables
sh mysqld.sh
mysql -S /tmp/mysql3306.sock
flush privileges;
set password for root@localhost=''; --also can use password() function here
login again using new password
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 ...

pkill mysqld
vim my.cnf -> add skip-grants-tables
sh mysqld.sh
mysql -S /tmp/mysql3306.sock
flush privileges; --this step is not indispensable
update mysql.user set authentication_string=password('') where ... ; --must use password() function,don't forget where clause to specify condition
login again using new password
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）

pkill mysqld
add "alter user ..." into file change_pass.sql
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 ~]

您可能感兴趣的文章:

如对本文有疑问，点击进行留言回复！！

Mysql高级部分＜一＞

事务，一个事务其实相当于一个线程开启一个事务，就相当去开启了一个线程解决事务的问题，可以用serializabl... [阅读全文]
Mysql学习（四） -- 相关知识

Mysql，Oracle和SQLserve 三大数据库的区别Oracle：最贵，功能最多，安全最不方便，Orac... [阅读全文]
消费资产化通证打造国内首家区块链消费生态服务平台

继去年10月24日国家强调加快推动区块链技术和产业创新发展以来，行业内外纷纷对区块链+产业落地寄予厚望；并随着今... [阅读全文]
macos 10.15 安装 MySQL

MySQL在mac系统安装如下一.下载https://downloads.mysql.com/archives/... [阅读全文]
项目实战: CMDB自动化资产扫描（搞懂这个项目算你没白学运维）

1.项目介绍本项目通过KVM虚拟化搭建Linux系统集群，使用 Ansible实现Linux集群下的批量部署与自... [阅读全文]
MySQL中建表时可空（NULL）和非空（NOT NULL）的用法详解

对于mysql的一些个规范，某些公司建表规范中有一项要求是所有字段非空，意味着没有值的时候存储一个默认值。其实所有字段非空这么说应该是绝对了，应该说是尽可能非空... [阅读全文]
MySQL多表查询详解上

时光在不经意间，总是过得出奇的快。小暑已过，进入中暑，太阳更加热烈的绽放着ta的光芒，...在外面被太阳照顾的人们啊，你们都是勤劳与可爱的人啊。在房子里已各种姿... [阅读全文]
MySQL多表查询详解下

好好吃饭，好好休息，听着很简单，实际落实缺失不那么容易。继续回顾mysql的多表查询之1999语法#二，sql1999语法语法: select 查询列表 fro... [阅读全文]
Lua脚本:Linux下如何安装Lua环境和解决依赖问题

Lua脚本介绍Lua是一款基于C编写的脚本语言设计目的:通过灵活嵌入应用程中从而为应用程序提供灵活的扩展和定制功... [阅读全文]
SSD原理及Pytorch代码解读——网络架构（二）：特征提取网络及总体计算过程

特征提取网络前面我们已经知道了SSD采用PriorBox机制，也知道了SSD多层特征图来做物体检测，浅层的特征图... [阅读全文]

网友评论


验证码：

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

2018年06月03日 | 移动技术网IT编程 | 我要评论

您可能感兴趣的文章:

相关文章:

网友评论