mysql忘記root密碼

標籤：NPU   admin   table   database   error   root   user   copy   roo   

mysql忘記了超級使用者管理密碼，可以利用重新啟動mysql進程的時候帶 --skip-grant-tables參數來啟動資料庫，並空密碼登入。這個就是相當於是資料庫第一次啟動的時候。隨後可以查看mysql.user表裡面的超級使用者，以及修改超級使用者的密碼

1# 關閉MYSQL instance，或者殺死進程

2# 重新啟動mysql，帶--skip-grant-tables語句

[[email protected] my.cnf.d]$ mysqld_safe --defaults-file=‘/data/mysqldata/3306/my.cnf‘  --skip-grant-tables &[1] 32530[[email protected] my.cnf.d]$ 180828 19:02:13 mysqld_safe Logging to ‘/data/mysqldata/3306/data/../mysql-error.log‘.180828 19:02:13 mysqld_safe Starting mysqld daemon with databases from /data/mysqldata/3306/data[[email protected] my.cnf.d]$ 

3# 空密碼登入MySQL

[[email protected] my.cnf.d]$ msyql -S /data/mysqldata/3306/mysql.sock bash: msyql: command not found...[[email protected] my.cnf.d]$ mysql -S /data/mysqldata/3306/mysql.sock Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.6.31-log Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.([email protected])[(none)]> 

4# 修改mysql.user表

([email protected])[(none)]> use mysql;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changed([email protected])[mysql]> ([email protected])[mysql]> select user,host,password from user;+------+-----------+-------------------------------------------+| user | host      | password                                  |+------+-----------+-------------------------------------------+| root | localhost | *A0F874BC7F54EE086FCE60A37CE7887D8B31086B |+------+-----------+-------------------------------------------+1 row in set (0.00 sec)([email protected])[mysql]> update user set password=password(‘password123‘) where user=‘root‘;Query OK, 0 rows affected (0.00 sec)Rows matched: 1  Changed: 0  Warnings: 0([email protected])[mysql]> 

5# 正常關閉資料庫

[[email protected] my.cnf.d]$ mysqladmin -S /data/mysqldata/3306/mysql.sock -uroot -p‘password123‘ shutdownWarning: Using a password on the command line interface can be insecure.180828 19:04:54 mysqld_safe mysqld from pid file /data/mysqldata/3306/mysql.pid ended[1]+  Done                    mysqld_safe --defaults-file=‘/data/mysqldata/3306/my.cnf‘ --skip-grant-tables[[email protected] my.cnf.d]$ 

6# 正常開啟資料庫

[[email protected] my.cnf.d]$ mysqld_safe --defaults-file=‘/data/mysqldata/3306/my.cnf‘  &[1] 33094[[email protected] my.cnf.d]$ 180828 19:05:04 mysqld_safe Logging to ‘/data/mysqldata/3306/data/../mysql-error.log‘.180828 19:05:04 mysqld_safe Starting mysqld daemon with databases from /data/mysqldata/3306/data[[email protected] my.cnf.d]$

7# 正常登入資料庫

[[email protected] my.cnf.d]$ mysql -uroot -S ‘/data/mysqldata/3306/mysql.sock‘ -p‘password123‘Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.6.31-log Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.([email protected])[(none)]> 

至此mysql的root密碼修改完成

mysql忘記root密碼