MySQL 5.7修改root密码的4种方法
版权声明:本文为博主原创文章,如需转载请保留此声明及博客链接,谢谢!
博客地址: http://www.cnblogs.com/aaron8219 & http://blog.csdn.net/aaron821
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:
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 ~]
版权声明:本文为博主原创文章,如需转载请保留此声明及博客链接,谢谢!
博客地址: http://www.cnblogs.com/aaron8219 & http://blog.csdn.net/aaron821