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 ...
扫码关注我们
微信号：SRE实战
拒绝背锅 运筹帷幄
- 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
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
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">
SRE实战 互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。 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
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
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 completeView 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.
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 ~]
更多精彩
