Nil Infobin Nil Infobin
Menu
Search
How to reset MySQL root password
Jun 10th, 2011 @ 10:15 am › Nilnandan Joshi
↓ Skip to comments

1. Stop mysql service

shell> /etc/init.d/mysql stop

2. Start to MySQL server without password.

shell> mysqld_safe –skip-grant-tables &

3. Connect to mysql server using mysql client.

shell> mysql -u root

Welcome to the MySQL monitor.  Commands end with ; or g.Your MySQL connection id is 107Server version: 5.1.49-1ubuntu8.1 (Ubuntu)Type ‘help;’ or ‘h’ for help.

Type ‘c’ to clear the current input statement.

mysql>

4. Setup new MySQL root user password

mysql> use mysql;

mysql> update user set password=PASSWORD(“new password”) where User=’root’;

mysql> flush privileges;

mysql> quit

5. Stop MySQL Server

shell> /etc/init.d/mysqld stop

6. Start MySQL server and test it

shell> /etc/init.d/mysqld startshell> mysql -u root -p

 

Categories: MySQL
Tags: , , , , , , ,

arrow1 Response

  1. Bhavin Mandiwala
    13 mos, 3 wks ago

    Nice ! I liked it :)
    In windows… below CMD would help.

    **********************************
    REM Batch file start…
    @ECHO OFF
    ..processkiller.exe –kill –force mysqld*.exe >nul 2>&1
    REM Above can be done with any other tools or may be manually.

    ECHO USE `mysql`; >reset_root.sql
    ECHO. >>reset_root.sql
    ECHO INSERT IGNORE INTO `user` VALUES (‘localhost’, ‘root’, ”, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ”, ”, ”, ”, 0, 0, 0, 0); >>reset_root.sql
    ECHO REPLACE INTO `user` VALUES (‘localhost’, ‘root’, ”, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ”, ”, ”, ”, 0, 0, 0, 0); >>reset_root.sql

    REM Correct path below for mysql.

    c:mysqlbinmysqld.exe –no-defaults –bind-address=127.0.0.1 –bootstrap –console –skip-grant-tables –skip-innodb –standalone reset_root.err 2>&1

    IF ERRORLEVEL 1 GOTO ERROR
    GOTO SUCCESS

    :ERROR
    TYPE reset_root.err
    ECHO.
    ECHO Passwords for user “root” was not cleared!
    GOTO END

    :SUCESS
    ECHO.
    ECHO Passwords for user “root” was cleared.
    ECHO.
    ECHO Please recycle the MySQL server.
    GOTO END

    :END
    DEL reset_root.err
    DEL reset_root.sql
    ECHO.
    PAUSE
    REM Batch complete…
    **********************************

Leave A Comment


one + 2 =