Three ways to recover a root user password:

The order of solutions here under gets more creative on the way down :)

1. obviously, before starting messing around check my.cnf or scripts for passwords entries, then try home directories for password files
2. secondly – can you restart mysql? if yes, restart with –skip-grant-tables, log into mysql, change your password and restart without –skip-grant-tables
3. third option – (on linux / unix ONLY)
If you haven’t found the password anywhere and can’t afford to restart your mysql.

cd data/mysql
cp -rp user.MYD bck_user.MYD_`date +%Y%m%d`
cp -rp user.MYD /tmp/user.MYD
vi /tmp/user.MYD #(edit the hashed passwords next to root*)
cp -rp /tmp/user.MYD user.MYD
sudo kill -HUP `pidof mysqld`

Note that the latter method of recovering a root password CAN be easily used maliciously leaving no trace! The only way to avoid such an attack is to make the data directory ONLY readable and writable by the user used to start/stop mysql (don’t user *nix root user to own mysql since that opens another can of worms … it’s a whole other blog post).

VN:F [1.9.22_1171]
Rating: 7.8/10 (4 votes cast)
VN:F [1.9.22_1171]
Rating: +2 (from 2 votes)

Recovering a MySQL `root` password – Three solutions, 7.8 out of 10 based on 4 ratings

  1. [...] Note that the latter method of recovering a root password CAN be easily used maliciously leaving no trace! The only way to avoid such an attack is to make the data directory ONLY readable and writable by the user used to start/stop mysql (don’t user *nix root user to own mysql since that opens another can of worms … it’s a whole other blog post). Read more: http://mysqlpreacher.com/wordpress/2011/03/recovering-a-mysql-root-password-three-solutions/#ixzz1Jl… [...]

  2. Tom says:

    Interestingly, (ive not checked the code to see why) but mysqld didn’t read from disk on the first -HUP, it required a second one like so…;

    sudo kill -HUP `pidof mysqld`

    The first HUP, only caused this to happen;

    -bash-4.2# strace -f -p `pidof mysqld` -e trace=file
    Process 31966 attached with 16 threads
    [pid 31991] open(“/var/log/mysqld.log”, O_WRONLY|O_CREAT|O_APPEND, 0666) = 31
    [pid 31991] open(“/var/log/mysqld.log”, O_WRONLY|O_CREAT|O_APPEND, 0666) = 31

    just to make sure I wasn’t being a ‘tard I double checked the HUP, and low and behold…;

    sudo kill -HUP `pidof mysqld`

    [pid 31991] open(“/var/log/mysqld.log”, O_WRONLY|O_CREAT|O_APPEND, 0666) = 31
    [pid 31991] open(“/var/log/mysqld.log”, O_WRONLY|O_CREAT|O_APPEND, 0666) = 31
    ….
    [pid 31991] readlink(“./mysql/user.MYI”, 0x7f1b367ddf10, 511) = -1 EINVAL (Invalid argument)
    [pid 31991] readlink(“./mysql/user.MYD”, 0x7f1b367de110, 511) = -1 EINVAL (Invalid argument)
    [pid 31991] lstat(“./mysql/user.MYD”, {st_mode=S_IFREG|0660, st_size=344, …}) = 0
    [pid 31991] open(“./mysql/user.MYD”, O_RDWR) = 16
    [pid 31991] access(“./mysql/user.TRG”, F_OK) = -1 ENOENT (No such file or directory)

    [pid 31991] open(“/var/log/mysqld.log”, O_WRONLY|O_CREAT|O_APPEND, 0666) = 29
    [pid 31991] open(“/var/log/mysqld.log”, O_WRONLY|O_CREAT|O_APPEND, 0666) = 29

    second time lucky… it did it.

  3. Rômulo Collopy says:

    If you’re trying in wamp/windows read this: http://www.staenzwebsolutions.com/reset-phpmyadmin-mysql-wampserver/569/

  4. U can use the debian-sys-maint user to recover it. no need to start mysql in safe mode. more info at http://newexception.com/recover-mysql-root-password

*