Recover MySQL Root Password

Steve Popoola —  June 1, 2012 — 2 Comments

I recently had to work on a machine with an existing MySQL database running. Unfortunately, the person who installed MySQL had forgotten the root password.

If this happens to you, the first thing I will say is, ‘Don’t Panic!’. If you have root access to the server, then you are on your way, if not, then you will have to do this with someone who has root access.

Step 1.  Shut down MySQL. This differs from one OS to another. The example here is on Ubuntu 12.04.

/etc/init.d/mysql stop

Step 2.  Start up MySQL in safe mode

mysqld_safe --skip-grant-tables &

Step 3.  Log in to mysql as root without the -p option

mysql -u root

Step 4.  Update the root password and flush privileges

mysql> update user set password=PASSWORD("new password") where User='root';
mysql> flush privileges;
mysql> quit;

Step 5. Stop and Start  MySQL

/etc/init.d/mysql stop

/etc/init.d/mysql start

Login with your new password.

Done!

Steve Popoola

Posts Google+

2 responses to Recover MySQL Root Password

  1. Nice ! I liked it In windows below CMD would help.
    **********************************REM Batch file start
    @ECHO OFF..\processkiller.exe
    kill force mylqsd*.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.sqlECHO 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:\mysql\bin\mylqsd.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:ERRORTYPE
    reset_root.errECHO.ECHO
    Passwords for user root was not cleared!
    GOTO END:SUCESSECHO.ECHO Passwords for user root was cleared.
    ECHO.ECHO Please recycle the MySQL server.
    GOTO END:
    ENDDEL reset_root.errDEL
    reset_root.sql
    ECHO.PAUSEREM
    Batch complete **********************************

  2. Nice batch process for Windows. Thanks Antonio!

Leave a Reply

*

Text formatting is available via select HTML.

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>