How to reset MySQL root password

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

 

One thought on “How to reset MySQL root password”

  1. 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 Reply

Your email address will not be published. Required fields are marked *