MySQL忘記root密碼後修改

MySQL忘記root密碼後可以使用下面的方法修改。

1、登入MySQL所在的伺服器，手工kill掉MySQL程序

    kill `cat $mysql_data_dir/hostname.pid`

    $mysql_data_dir/hostname.pid為MySQL資料目錄，它記錄了MySQL服務的程序号。

[root@rhel6 ~]# ps -ef |grep mysql root      6602     1  0 21:39 ?        00:00:00 /bin/sh ./mysqld_safe --skip-grant-tables --user=mysql mysql     6754  6602  0 21:39 ?        00:00:01 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/lib/mysql/rhel6.er r --pid-file=/var/lib/mysql/rhel6.pidroot      6851  6830  0 21:47 pts/3    00:00:00 grep mysql [root@rhel6 ~]# cat /var/lib/mysql/rhel6.pid  6754 [root@rhel6 ~]# kill `cat /var/lib/mysql/rhel6.pid ` root      6859  6830  0 21:48 pts/3    00:00:00 grep mysql

2、使用--skip-grant-tables選項重新開機MySQL服務

    --skip-grant-tables意思是啟動MySQL服務時跳過權限表認證。啟動後，連接配接到MySQL的root将不需要密碼。或者在/etc/my.cnf中添加skip-grant-tables參數，啟動mysql，service mysql start/systemctl start mysql

[root@rhel6 bin]# ./mysqld_safe --skip_grant-tables --user=mysql & [1] 6900 [root@rhel6 bin]# 161122 21:52:03 mysqld_safe Logging to '/var/lib/mysql/rhel6.err'. 161122 21:52:03 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql [root@rhel6 bin]# ps -ef |grep mysql root      6900  6830  0 21:52 pts/3    00:00:00 /bin/sh ./mysqld_safe --skip_grant-tables --user=mysql mysql     7052  6900  1 21:52 pts/3    00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/lib/mysql/rhel6.er r --pid-file=/var/lib/mysql/rhel6.pidroot      7075  6830  0 21:52 pts/3    00:00:00 grep mysql [root@rhel6 bin]# mysql -uroot Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.34-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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. mysql> 

3、修改root密碼

mysql> set password=password('123456'); ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> update user set password=password('123456') where user='root' and host='localhost'; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0

    由于使用了--skip-grant-tables選項啟動，使用"set password"指令更改密碼失敗，直接更新user表的password字段後更改密碼成功。

在5.7版本中password有規則政策，123456不符合。

4、重新整理權限表

mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)

5、重新用root登入時，必須輸入新密碼

[root@rhel6 bin]# mysql -uroot  ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) [root@rhel6 bin]# mysql -uroot -p Enter password:  Your MySQL connection id is 4

參考：《深入淺出MySQL》

     本文轉自hbxztc 51CTO部落格，原文連結：http://blog.51cto.com/hbxztc/1875966，如需轉載請自行聯系原作者