mysql忘记root密码解决方案

介绍一个非常有用的mysql启动参数—— --skip-grant-tables。顾名思义，就是在启动mysql时不启动grant-tables，授权表。他有什么用呢？当然是忘记管理员密码后有用。

操作方法：

1、杀掉原来进行着的mysql：

kill -TERM mysqld

2、以命令行参数启动mysql：

bin/mysqld_safe --skip-grant-tables &

3、修改管理员密码：

use mysql;

update user set password=password('yournewpasswordhere') where user='root';

flush privileges;

exit;

4、杀死mysql，重启mysql.

下面看个例子

[root@VM mysql]# bin/mysqld_safe --skip-grant-tables

&

[1] 10898

[root@VM mysql]# 160119 13:37:52 mysqld_safe Logging to

'/data/mysql3306/BJ-CSK-0-135.err'.

160119 13:37:52 mysqld_safe Starting mysqld daemon with databases from

/data/mysql3306

[root@VM mysql]#

[root@VM mysql]# ps aux|grep mysql

root 10898 0.6 0.0 108456 1432 pts/0 S 13:37 0:00 /bin/sh

bin/mysqld_safe --skip-grant-tables

nobody 11694 63.6 9.8 11073336 1608180 pts/0 Sl 13:37 0:09

/apps/mysql/bin/mysqld --basedir=/apps/mysql --datadir=/data/mysql3306 --

plugin-dir=/apps/mysql/lib/mysql/plugin --user=nobody --skip-grant-tables --log-

error=/data/mysql3306/VM.err --open-files-limit=8192 --pid-file=/data/mysql3306/VM.pid --socket=/tmp/mysql_3306.sock --

port=3306

root 11786 0.0 0.0 105484 916 pts/0 S+ 13:38 0:00 grep mysql

[root@VM mysql]# bin/mysql -u root -S /tmp/mysql_3306.sock

bin/mysql: Unknown OS character set 'GB18030'.

bin/mysql: Switching to the default character set 'latin1'.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.12-rc60.4-log Percona Server with XtraDB (GPL), Release rc60.4,

Revision 393

Copyright (c) 2009-2013 Percona Ireland Ltd.

Copyright (c) 2000, 2013, 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> 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('sunyankui') where user='root';

Query OK, 4 rows affected (0.01 sec)

Rows matched: 4 Changed: 4 Warnings: 0

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

[root@VM mysql]# ps aux|grep mysqlroot 10898 0.1 0.0 108456 1432 pts/0 S 13:37 0:00 /bin/sh

bin/mysqld_safe --skip-grant-tables

nobody 11694 12.0 9.8 11139068 1610144 pts/0 Sl 13:37 0:09

/apps/mysql/bin/mysqld --basedir=/apps/mysql --datadir=/data/mysql3306 --

plugin-dir=/apps/mysql/lib/mysql/plugin --user=nobody --skip-grant-tables --log-

error=/data/mysql3306/VM.err --open-files-limit=8192 --pid-

file=/data/mysql3306/VM.pid --socket=/tmp/mysql_3306.sock --

port=3306

root 11959 0.0 0.0 105484 912 pts/0 S+ 13:39 0:00 grep mysql

[root@VM mysql]# kill -9 10898

[1]+ 已杀死 bin/mysqld_safe --skip-grant-tables

[root@VM mysql]# kill -9 11694

[root@VM mysql]# ps aux|grep mysql

root 12119 0.0 0.0 105480 908 pts/0 S+ 13:39 0:00 grep mysql

[root@VM mysql]# bin/mysqld_safe --defaults-file=/apps/mysql/my.cnf

&

[1] 12437

[root@VM mysql]# 160119 13:41:16 mysqld_safe Logging to

'/data/mysql3306/BJ-CSK-0-135.err'.

160119 13:41:16 mysqld_safe Starting mysqld daemon with databases from

/data/mysql3306

[root@VM mysql]# ps aux|grep mysql

root 12437 0.7 0.0 108540 1552 pts/0 S 13:41 0:00 /bin/sh

bin/mysqld_safe --defaults-file=/apps/mysql/my.cnf

nobody 13223 30.4 9.8 11073372 1608732 pts/0 Sl 13:41 0:03

/apps/mysql/bin/mysqld --defaults-file=/apps/mysql/my.cnf --

basedir=/apps/mysql --datadir=/data/mysql3306 --plugin-

dir=/apps/mysql/lib/mysql/plugin --user=nobody --log-

error=/data/mysql3306/VM.err --open-files-limit=8192 --pid-

file=/data/mysql3306/VM.pid --socket=/tmp/mysql_3306.sock --

port=3306

root 13282 0.0 0.0 105480 912 pts/0 S+ 13:41 0:00 grep mysql

#end