Changing root password and restoring mysql root user

This article addresses the questions "I accidently deleted MySQL's root account. What should I do?" and "I lost my root password! Help!".

I suppose everyone has experienced loosing/forgetting root password. In that case you should backup data directory, reinstall MySQL and restore all database files, except for the ones from mysql database. This is quite radical technique and here we'll mention a few alternatives.

Method #1 - skipping privileges check

There's an easy way to allow every user to modify mysql databes (including user accouts). This can be done by skipping privileges check. This also allows every user to access any database, so be carefull when doing this. A good practice is to restrict access to mysql server for everyone excep you. This can be done by firewall rule, or by stopping all network connectivity with --skip-networking option, making the server accessible only through the localhost. Here's what you should do in a few steps:

1. Stopping the server (if it's running)
    Windows instalations can be stopped using Task manager, or Services section of Control panel (if mysql is unning as service).
    Linux instalations can be stopped with kill command, but without usint -9 option. Kill -9 can cause data loss. Use ps afx | grep mysqld to detect mysql's PID

2. Starting the server with option for skipping privileges check
    Windows users should use this command:
    c:\mysql_dir\bin\mysqld-nt.exe --skip-grant-tables
    where c:\mysql_dir\ is mysql'instalation directory.
    Linux users should use this command:
    /usr/local/mysql/bin/mysqld_safe --skip-grant-tables --user=root --skip-networking

3. Connecting to the server using exsisting account (root or some otherif root account is deleted).
    You can use command-line client:
     c:\mysql_dir\bin\mysql.exe -u user
     or
     /usr/local/mysql/bin/mysql -u user

4. Issuing a query for changing the password:
        UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';
    or recreating user:
        CREATE USER root IDENTIFIED BY PASSWORD 'newpwd';
        GRANT ALL PRIVILEGES ON * . * TO 'root'@ 'localhost' IDENTIFIED BY 'newpwd' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

5. Turning on privileges check:
        FLUSH PRIVILEGES;

6. You should restart the server using usual method (mysqladmin or other command) if you have added --skip-networking option

Method #2 - using init file with queries

If you cannot afford to skip privileges check, there's another way to execute queries without having permittion to do so.

1. Save the command needed from point 4 in the previous method to a file, for example file.txt

2. Stopping the server

3. Starting server using option --init-file
    Under Windows
    c:\mysql_dir\bin\mysqld-nt.exe --init-file=c:\file.txt
    Under Linux
    /usr/local/mysql/bin/mysqld_safe --init-file=/root/file.txt

This way mysql server will execute the commands in the file after it starts.

 

Comments:

seiber (15-10-2008 17:40) :
when i execute mysql it demand a password? can you help me

Greg (20-07-2009 15:33) :
Above didn't work because you can't do user actions with --skip-grant-tables

This is a life saver:
http://hack2live.blogspot.com/2009/04/restore-repair-reset-mysql-root.html

Koala (11-03-2011 22:19) :
This saved my life!!

@Greg,
It works perfectly. You have to flush privileges first. Then create root user with privileges and you are done!

Back to articles list

This page was last modified on 2018-12-09 23:10:44