解決MySQL忘記root密碼

標籤：

網上有很多關於忘記MySQL root密碼的一些文章，裡面都有寫怎麼去解決，但有時覺得寫得太噁心，要麼一字不漏的抄別人的，要麼就說得不清不楚，好了，不吐槽了，以下是解決的整個過程。

首先我們要知道忘記MySQL root密碼後，能否重啟mysql，能重啟的操作是怎麼樣的？不能重啟的操作又會是怎麼樣的？

 

情況一：（能重啟情況下）

修改my.cnf設定檔，在mysqld欄下添加skip-grant-tables選項，意思是mysqld server啟動之後並不使用許可權系統（privilege system），也可以理解為跳過授權表。為了安全起見，通常加上skip-networking，mysqld不偵聽任何TCP/IP串連請求。

重啟mysqld，然後空密碼串連：

[root ~]$mysql -uroot -S /data/mysql-5.5/mysql.sockWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.5.40-log MySQL Community Server (GPL)Copyright (c) 2000, 2014, 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.mysql> 

可以看到已經成功登入了，然後修改root密碼：

mysql> update mysql.user set password=password(‘123456‘) where user=‘root‘;    Query OK, 4 rows affected (0.00 sec)Rows matched: 4  Changed: 4  Warnings: 0mysql> flush privileges; 
Query OK, 0 rows affected (0.01 sec)

已經成功修改密碼了，但還有事情要做，就是把剛剛添加到my.cnf裡的skip-grant-tables和skip-networking刪除掉或者注釋掉。

 

情況二：(不能重啟mysql的情況)

如果不能重啟，mysql.user 剛好有許可權比較低的使用者，如果沒有，你請神仙來幫你吧，哈哈

1、為了測試，我自己建立一個使用者，可以沒什麼許可權

mysql> create user [email protected]‘localhost‘ identified by ‘123456‘;Query OK, 0 rows affected (0.00 sec)

2、進到資料目錄下：

[root mysql-5.5]$ pwd/data/mysql-5.5[root mysql-5.5]$ cp mysql/user.* test/[root mysql-5.5]$ chown mysql.mysql test/user.*

3、用許可權比較小的使用者登入：

[root mysql-5.5]$mysql -uxuanzhi -p123456 -S /data/mysql-5.5/mysql.sock Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.5.40-log MySQL Community Server (GPL)Copyright (c) 2000, 2014, 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.mysql> use testDatabase changedmysql> update user set password=password(‘123123‘) where user=‘root‘;Query OK, 4 rows affected (0.00 sec)Rows matched: 4  Changed: 4  Warnings: 0

4、把修改後的user.MYD和user.MYI複製到mysql目錄下，記得備份之前的檔案。

[root mysql-5.5]$ pwd/data/mysql-5.5[root mysql-5.5]$ mv mysql/user.MYD mysql/user.MYD.bak[root mysql-5.5]$ mv mysql/user.MYI mysql/user.MYI.bak[root mysql-5.5]$ cp test/user.MY* mysql/[root mysql-5.5]$ chown mysql:mysql  mysql/user.*

5.尋找mysql進程號，並且發送SIGHUP訊號，重新載入許可權表。（有時載入一次不行的時候，再載入多一次@。@）

[root mysql]$ pgrep -n mysql23166[root mysql]$ kill -SIGHUP 23166[root mysql]$ /usr/local/mysql-5.5.40/bin/mysql -uroot -p123123 -S /data/mysql-5.5/mysql.sock ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using password: YES)[root mysql]$ kill -SIGHUP 23166[root mysql]$ /usr/local/mysql-5.5.40/bin/mysql -uroot -p123123 -S /data/mysql-5.5/mysql.sock Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 5Server version: 5.5.40-log MySQL Community Server (GPL)Copyright (c) 2000, 2014, 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.mysql> 

 

不重啟的第二種方法：

1、建立新的資料目錄，並給原來user相應的許可權,忘記密碼對應的執行個體它的user是mysql，所以把許可權給mysql使用者

[root data]$ mkdir -pv /dbdata/datadir/mkdir: 已建立目錄 "/dbdata"mkdir: 已建立目錄 "/dbdata/datadir/"[root data]$ chown  -R mysql:mysql /dbdata/datadir/

2、執行初始化操作：（報錯了）

[root scripts]$ pwd/usr/local/mysql-5.5.40/scripts[root scripts]$ ./mysql_install_db --datadir=/dbdata/datadir/ --user=mysql2FATAL ERROR: Could not find ./bin/my_print_defaultsIf you compiled from source, you need to run ‘make install‘ tocopy the software into the correct location ready for operation.If you are using a binary release, you must either be at the toplevel of the extracted archive, or pass the --basedir optionpointing to that location.

解決方案：

[root scripts]$ /usr/local/mysql-5.6.10/scripts/mysql_install_db --datadir=/dbdata/datadir/ --user=mysql --datadir=/dbdata/datadir/ --basedir=/usr/local/mysql-5.6.10/Installing MySQL system tables...141210 16:09:24 [Warning] ‘THREAD_CONCURRENCY‘ is deprecated and will be removed in a future release.OKFilling help tables...141210 16:09:24 [Warning] ‘THREAD_CONCURRENCY‘ is deprecated and will be removed in a future release.OK

3、啟動一個新的進程，這裡要注意一下port，sock檔案，還有pid檔案，這都是新的，user還是忘記密碼執行個體的user，而不是忘記密碼對應的那個資料庫執行個體的，這裡我們不需要用到InnoDB引擎,設定預設引擎為MyISAM：

[root ~]$  /usr/local/mysql-5.6.10/bin/mysqld_safe --datadir=/dbdata/datadir --plugin-dir=/usr/local/mysql-5.6.10/lib/plugin/  --skip-innodb \
> --default-storage-engine=myisam --socket=/dbdata/datadir/mysql2.sock --user=mysql --port=3305 --log-error=/dbdata/datadir/error2.log --pid-file=/data/mysql-5.6/mysql.pid &
[1] 21204
[root ~]$ 141210 16:56:11 mysqld_safe Logging to ‘/dbdata/datadir/error2.log‘.141210 16:56:11 mysqld_safe Starting mysqld daemon with databases from /dbdata/datadir

4、登入新啟動的mysql執行個體，此時密碼為空白密碼：

root datadir]$ /usr/local/mysql-5.6.10/bin/mysql -S /dbdata/datadir/mysql2.sock Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.5.30-log Source distributionCopyright (c) 2000, 2013, 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.mysql> flush tables;Query OK, 0 rows affected (0.00 sec)

修改root密碼：

mysql> select user, host, password from user where user like ‘root‘;+------+-----------------------+----------+| user | host                  | password |+------+-----------------------+----------+| root | localhost             |          || root | localhost.localdomain |          || root | 127.0.0.1             |          || root | ::1                   |          |+------+-----------------------+----------+4 rows in set (0.02 sec)mysql> update mysql.user set password=password(‘654321‘) where user=‘root‘; Query OK, 4 rows affected (0.03 sec)Rows matched: 4  Changed: 4  Warnings: 0mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)

5、拷備新的user表到忘記密碼的執行個體資料庫的mysql目錄下

[root mysql]$ pwd/dbdata/datadir/mysql[root mysql]$ cp user.* /data/mysql-5.6/mysql/cp：是否覆蓋"/data/mysql-5.6/mysql/user.frm"？ ycp：是否覆蓋"/data/mysql-5.6/mysql/user.MYD"？ ycp：是否覆蓋"/data/mysql-5.6/mysql/user.MYI"？ y[root mysql]$ chown -R mysql5.6:mysql5.6 /data/mysql-5.6/[root mysql]$ chmod 660 /data/mysql-5.6/mysql/user.*

6、我們需要到mysqld發送一個sighup訊號，MySQL響應這個訊號載入授權表，重新整理表，日誌，線程緩衝。
如果是單個MySQL執行個體，可以用這樣的方法去重新載入

[root ~]$ kill -1 $(/sbin/pidof mysqld)

如果是多個MySQL執行個體在一台伺服器上的話，就要注意點了，可以通過這樣的方法找到pid，我舊執行個體的連接埠是3306：

[root mysql-5.6.10]$ netstat -nltpActive Internet connections (only servers)Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name   tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      8414/mysqld         tcp        0      0 0.0.0.0:3308                0.0.0.0:*                   LISTEN      6430/mysqld         tcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTEN      1144/sshd                 tcp        0      0 :::3310                     :::*                        LISTEN      17151/mysqld        tcp        0      0 :::22                       :::*                        LISTEN      1144/sshd           tcp        0      0 ::1:631                     :::*                        LISTEN      1109/cupsd          tcp        0      0 :::3306                     :::*                        LISTEN      2091/mysqld[root mysql-5.6.10]$ kill -1 2091

有時kill -1一次不行，再執行一次就可以了：

[root mysql-5.6.10]$ kill -1 2091[root mysql-5.6.10]$ /usr/local/mysql-5.6.10/bin/mysql -uroot -p654321 -S /data/mysql-5.6/mysql.sock Warning: Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using password: YES)[root mysql-5.6.10]$ kill -1 2091[root mysql-5.6.10]$ /usr/local/mysql-5.6.10/bin/mysql -uroot -p654321 -S /data/mysql-5.6/mysql.sock 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 13Server version: 5.6.10-log MySQL Community Server (GPL)Copyright (c) 2000, 2013, 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.mysql> 

OK，已經成功登入了，如果有更多好的方法，我們可以再一起討論下

 

 

總結：

1）第一種方法簡單，但需要重啟MySQL,重啟會影響線上業務，一般不建議重啟

2）第二種方法比較好，不用重啟MySQL執行個體，修改密碼，只修改root使用者的，而且其它保持不變

3）第三種方法也不需要重啟，但是新的user表裡，只有root一個使用者，如果之前伺服器還存在別的使用者及許可權，這就比較麻煩了

 

 

 參考資料：http://www.percona.com/blog/2014/12/10/recover-mysql-root-password-without-restarting-mysql-no-downtime/

PS:本人也是參考別人的部落格做的，但我沒有照搬別人的東西，太噁心了，希望大家有自己的風格。^.^

 

解決MySQL忘記root密碼