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-grant-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:
#mysql -S /tmp/mysql3306.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.7.-log MySQL Community Server (GPL)
Copyright (c) , , Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. (root@localhost mysql3306.sock)[(none)]::>alter user root@localhost identified by 'innodb';
ERROR (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement (root@localhost mysql3306.sock)[(none)]::>flush privileges;
Query OK, rows affected (0.00 sec) (root@localhost mysql3306.sock)[(none)]::>alter user root@localhost identified by 'innodb';
Query OK, rows affected (0.00 sec) (root@localhost mysql3306.sock)[(none)]::>quit;
Bye #mysql -p -S /tmp/mysql3306.sock
Enter password: <here the new Password is "innodb">
II. SET PASSWORD ...
- pkill mysqld
- vim my.cnf -> add skip-grant-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:
#mysql -S /tmp/mysql3306.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.7.-log MySQL Community Server (GPL)
Copyright (c) , , Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. (root@localhost mysql3306.sock)[(none)]::>set password for root@localhost='mysql'; -- or,set password for root@localhost=password('mysql')
ERROR (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement (root@localhost mysql3306.sock)[(none)]::>flush privileges;
Query OK, rows affected (0.00 sec) (root@localhost mysql3306.sock)[(none)]::>set password for root@localhost='mysql';
Query OK, rows affected (0.00 sec) (root@localhost mysql3306.sock)[(none)]::>exit
Bye #mysql -p -S /tmp/mysql3306.sock
Enter password: <here the new Password is "mysql">
III. UPDATE MYSQL.USER SET ...
- pkill mysqld
- vim my.cnf -> add skip-grant-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:
#mysql -S /tmp/mysql3306.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.7.-log MySQL Community Server (GPL)
Copyright (c) , , Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. (root@localhost mysql3306.sock)[(none)]::>update mysql.user set authentication_string=('oracle') where user='root' and host='localhost';
Query OK, row affected (0.00 sec)
Rows matched: Changed: Warnings: (root@localhost mysql3306.sock)[(none)]::>select user,host,authentication_string from mysql.user;
+---------------+---------------+-------------------------------------------+
| user | host | authentication_string |
+---------------+---------------+-------------------------------------------+
| root | localhost | oracle |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| repl | 192.168..% | *872ECE72A7EBAC6A183C90D7043D5F359BD85A9E |
| zlm | 192.168..% | *B746A45EBB84DD9DDEF015B332281AEFD164E2A9 |
| zlm | 192.168.1.102 | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| zlm | 192.168.1.1 | *2447D497B9A6A15F2776055CB2D1E9F86758182F |
| zlm | 192.168.1.103 | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
+---------------+---------------+-------------------------------------------+
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.
(root@localhost mysql3306.sock)[(none)]::>update mysql.user set authentication_string=password('oracle') where user='root' and host='localhost';
Query OK, row affected, warning (0.00 sec)
Rows matched: Changed: Warnings:
(root@localhost mysql3306.sock)[(none)]::>select user,host,authentication_string from mysql.user;
+---------------+---------------+-------------------------------------------+
| user | host | authentication_string |
+---------------+---------------+-------------------------------------------+
| root | localhost | *2447D497B9A6A15F2776055CB2D1E9F86758182F |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| repl | 192.168..% | *872ECE72A7EBAC6A183C90D7043D5F359BD85A9E |
| zlm | 192.168..% | *B746A45EBB84DD9DDEF015B332281AEFD164E2A9 |
| zlm | 192.168.1.102 | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| zlm | 192.168.1.1 | *2447D497B9A6A15F2776055CB2D1E9F86758182F |
| zlm | 192.168.1.103 | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
+---------------+---------------+-------------------------------------------+
rows in set (0.00 sec)
(root@localhost mysql3306.sock)[(none)]::>exit
Bye
[root@zlm3 :: ~]
#mysql -p -S /tmp/mysql3306.sock
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:
[root@zlm3 :: ~]
#pkill mysqld [root@zlm3 :: ~]
#ps -ef | grep mysqld
root : pts/ :: grep --color=auto mysqld [root@zlm3 :: ~]
#pwd
/root [root@zlm3 :: ~]
#echo "alter user root@localhost identified by 'password';" > change_password.sql [root@zlm3 :: ~]
#cat change_password.sql
alter user root@localhost identified by 'password'; [root@zlm3 :: ~]
#mysqld --defaults-file=/data/mysql/mysql3306/my.cnf --init-file=/root/change_password.sql &
[] [root@zlm3 :: ~]
#ps -efl|grep mysqld
R root - - : pts/ :: grep --color=auto mysqld
[]+ Exit mysqld --defaults-file=/data/mysql/mysql3306/my.cnf --init-file=/root/change_password.sql [root@zlm3 :: ~]
#mysql -p -S /tmp/mysql3306.sock
Enter password:
ERROR (HY000): Can't connect to local MySQL server through socket '/tmp/mysql3306.sock' (2)
[root@zlm3 :: ~]
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:
--31T05::.520876Z [Note] Server hostname (bind-address): '*'; port:
--31T05::.520915Z [Note] IPv6 is available.
--31T05::.520920Z [Note] - '::' resolves to '::';
--31T05::.520934Z [Note] Server socket created on IP: '::'.
--31T05::.544976Z [Note] Event Scheduler: Loaded events
--31T05::.545087Z [Note] Execution of init_file '/root/change_password.sql' started.
--31T05::.545108Z [ERROR] mysqld: File '/root/change_password.sql' not found (Errcode: - Permission denied)
--31T05::.545111Z [ERROR] Aborting
--31T05::.545226Z [Note] Giving client threads a chance to die gracefully
--31T05::.545233Z [Note] Shutting down slave threads
--31T05::.545237Z [Note] Forcefully disconnecting remaining clients
--31T05::.545239Z [Note] Event Scheduler: Purging the queue. events
--31T05::.545301Z [Note] Binlog end
--31T05::.547647Z [Note] Shutting down plugin 'ngram'
--31T05::.547666Z [Note] Shutting down plugin 'BLACKHOLE'
--31T05::.547669Z [Note] Shutting down plugin 'partition'
--31T05::.547671Z [Note] Shutting down plugin 'ARCHIVE'
--31T05::.547673Z [Note] Shutting down plugin 'MyISAM'
--31T05::.547678Z [Note] Shutting down plugin 'CSV'
--31T05::.547681Z [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
--31T05::.547683Z [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
--31T05::.547685Z [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
--31T05::.547686Z [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
--31T05::.547687Z [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
--31T05::.547689Z [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
--31T05::.547690Z [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
--31T05::.547692Z [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
--31T05::.547693Z [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
--31T05::.547694Z [Note] Shutting down plugin 'INNODB_SYS_TABLES'
--31T05::.547696Z [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
--31T05::.547703Z [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
--31T05::.547705Z [Note] Shutting down plugin 'INNODB_FT_CONFIG'
--31T05::.547706Z [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
--31T05::.547707Z [Note] Shutting down plugin 'INNODB_FT_DELETED'
--31T05::.547709Z [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
--31T05::.547710Z [Note] Shutting down plugin 'INNODB_METRICS'
--31T05::.547711Z [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
--31T05::.547713Z [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
--31T05::.547714Z [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
--31T05::.547716Z [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
--31T05::.547717Z [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
--31T05::.547718Z [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
--31T05::.547720Z [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
--31T05::.547721Z [Note] Shutting down plugin 'INNODB_CMPMEM'
--31T05::.547722Z [Note] Shutting down plugin 'INNODB_CMP_RESET'
--31T05::.547724Z [Note] Shutting down plugin 'INNODB_CMP'
--31T05::.547725Z [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
--31T05::.547727Z [Note] Shutting down plugin 'INNODB_LOCKS'
--31T05::.547728Z [Note] Shutting down plugin 'INNODB_TRX'
--31T05::.547729Z [Note] Shutting down plugin 'InnoDB'
--31T05::.547781Z [Note] InnoDB: FTS optimize thread exiting.
--31T05::.547899Z [Note] InnoDB: Starting shutdown...
--31T05::.553631Z [Note] InnoDB: Buffer pool(s) load completed at ::
--31T05::.553667Z [Note] InnoDB: Dumping buffer pool(s) to /data/mysql/mysql3306/data/ib_buffer_pool
--31T05::.553809Z [Note] InnoDB: Buffer pool(s) dump completed at ::
--31T05::.366016Z [Note] InnoDB: Shutdown completed; log sequence number
--31T05::.366078Z [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
--31T05::.366085Z [Note] Shutting down plugin 'MEMORY'
--31T05::.366090Z [Note] Shutting down plugin 'MRG_MYISAM'
--31T05::.366093Z [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
--31T05::.366111Z [Note] Shutting down plugin 'sha256_password'
--31T05::.366113Z [Note] Shutting down plugin 'mysql_native_password'
--31T05::.366263Z [Note] Shutting down plugin 'binlog'
--31T05::.370287Z [Note] mysqld: Shutdown complete
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:
[root@zlm3 :: ~]
#ls -l
total
-rw-------. root root Jul anaconda-ks.cfg
-rw-r--r-- root root May : change_password.sql
-rwxr-xr-x root root Apr : mysql-5.7.-linux-glibc2.-x86_64.tar.gz
-rwxr--r-- root root Apr : mysqld.sh
-rw-r--r-- root root May : mysqld.strace
drwxr-xr-x mysql mysql May : zabbix-3.0.
-rwxr-xr-x root root May : zabbix-3.0..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:
[root@zlm3 :: ~]
#mv change_password.sql /home/mysql [root@zlm3 :: ~]
#cd /home/mysql [root@zlm3 :: /home/mysql]
#ls -l
total
-rw-r--r-- mysql mysql May : change_password.sql
let's start the mysqld process again,well,it's running now:
[root@zlm3 :: ~]
#mysqld --defaults-file=/data/mysql/mysql3306/my.cnf --init-file=/home/mysql/change_password.sql &
[] [root@zlm3 :: ~]
#ps aux|grep mysqld
mysql 3.2 17.5 pts/ Sl : : mysqld --defaults-file=/data/mysql/mysql3306/my.cnf --init-file=/home/mysql/change_password.sql
root 0.0 0.0 pts/ R+ : : grep --color=auto mysqld [root@zlm3 :: ~]
#mysql -p -S /tmp/mysql3306.sock
Enter password: <here the new Password is "password">
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.7.-log MySQL Community Server (GPL)
Copyright (c) , , Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
(root@localhost mysql3306.sock)[(none)]::>
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:
#mysql -p -S /tmp/mysql3306.sock
Enter password: <here put the right password>
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.7.-log MySQL Community Server (GPL)
Copyright (c) , , Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. (root@localhost mysql3306.sock)[(none)]::>exit
Bye [root@zlm3 :: ~]
#mysql -p -S /tmp/mysql3306.sock
Enter password: <here put the wrong password>
ERROR (): Access denied for user 'root'@'localhost' (using password: YES) [root@zlm3 :: ~]
#mysql -S /tmp/mysql3306.sock --not using "-p" parameter
ERROR (): Access denied for user 'root'@'localhost' (using password: NO) #[root@zlm3 :: ~]