Sunday, August 7, 2011

How to Set and Reset Mysql Root Password

    MySQL root password is the essential task for securing DB. Here i have given some of the ways to set and reset the root password.

1.To set root password for first time:

root@debian:/home/mughil# mysqladmin -u root password NEWPASSWORD

2.To change or update the password:

root@debian:/home/mughil# mysqladmin -u root 'OLDPASSWORD' password 'NEWPASSWORD'

3.To change mysql password for other user:

root@debian:/home/mughil# mysqladmin -u user 'OLDPASSWORD' password 'NEWPASSWORD';

        However, If you forget the root password and login into mysql without username and password to reset the password as follow.

1.First you have to ensure whether mysql is stopped or not:

root@debian:/home/mughil# /etc/init.d/mysql stop
Stopping MySQL database server: mysqld.

2.After the mysql is stopped, you have to start in safe mode:

root@debian:/home/mughil# mysqld_safe --skip-grant-tables &

3.You can login into the mysql and set the password as follow:

root@debian:/home/mughil# mysql --user=root mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.49-3 (Debian)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> update user set Password=PASSWORD('23534')  WHERE User='root';
Query OK, 3 rows affected (0.08 sec)
Rows matched: 3  Changed: 3  Warnings: 0

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

mysql> exit

      Now, you just need to stop the server, so that you can go back to running a secure MySQL.

No comments:

Post a Comment