Thursday, August 18, 2016

Change/Forgot 'root' user password

Forgot MySQL Root Password:

Steps:
1> Stop MW/application connecting to Database
2> Kill MySQL process
3> Start database with --skip-grant-tables option
4> Logon to MySQL DB using 'root' user without password.
5> Change 'root' user password.
6> Kill MySQL process
7> Restart DB with normal process/convention.

Detailed Steps:
[root@ ~]# mysql -uroot -ptest -Dpractice
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@ ~]#
[root@ ~]# mysqladmin -uroot shutdown
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
[root@ ~]# <Grep pid of mysql process running>
[root@ ~]# kill -9 22961 23680
[root@ ~]# bin/mysqld_safe --defaults-file=/u01/app/mysql/practice/etc/my.cnf --datadir=/u02/mysqldata/practice/data --pid-file=/u03/mysqldata/practice/logs/practice_3.pid --skip-grant-tables &
[1] 26803
[root@ ~]# 160818 01:16:57 mysqld_safe Logging to '/u03/mysqldata/practice/logs/practice_3.err'.
160818 01:16:57 mysqld_safe Starting mysqld daemon with databases from /u02/mysqldata/practice/data

[root@ ~]# mysql -uroot
mysql> UPDATE mysql.user SET Password = PASSWORD('test') WHERE User = 'root';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

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

mysql> exit
Bye

[root@ ~]# ps -eaf | grep -i mysqld
[root@ ~]# <Grep pid of mysql process running>
[root@ ~]# kill -9 26803 27539
[root@ ~]# bin/mysqld_safe --defaults-file=/u01/app/mysql/practice/etc/my.cnf --datadir=/u02/mysqldata/practice/data --pid-file=/u03/mysqldata/practice/logs/practice_3.pid &
[root@ ~]# mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@ ~]# mysql -uroot -ptest
mysql>

No comments:

Post a Comment