mysqlubuntu

How do I find out my root MySQL password?


I just installed MySQL on Ubuntu and the root user can't log in :)

How can I recover or find out my password? Using blank for password does not work.


Solution

  • You can reset the root password by running the server with --skip-grant-tables and logging in without a password by running the following as root (or with sudo):

    # service mysql stop
    # mysqld_safe --skip-grant-tables &
    $ mysql -u root
    
    mysql> use mysql;
    mysql> update user set authentication_string=PASSWORD("YOUR-NEW-ROOT-PASSWORD") where User='root';
    mysql> flush privileges;
    mysql> quit
    
    # service mysql stop
    # service mysql start
    $ mysql -u root -p
    

    Now you should be able to login as root with your new password.

    It is also possible to find the query that reset the password in /home/$USER/.mysql_history or /root/.mysql_history of the user who reset the password, but the above will always work.

    Note: prior to MySQL 5.7 the column was called password instead of authentication_string. Replace the line above with

    mysql> update user set password=PASSWORD("YOUR-NEW-ROOT-PASSWORD") where User='root';