How do I find out my root MySQL password

Question

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

User · Answer

I m going to make a bit of an assumption here because I m not sure  I don t think my MySQL  running on latest 20 04 upgraded  even has a root  I have tried setting one and I remember having problems  I suspect there is not a root user and it  will automatically log you in as the MySQL root user if you re logged in as root  Why do I think this  Because when I do MySQL -u root -p  it will accept any password and log me in as the MySQL root user when I am logged in as root  I have confirmed that trying that on a non root user doesn t work  I like this model  EDIT 2020 12 19  It is no longer a mystery to me why if you are logged in as the root user you get logged into MySQL as the root user  It has to do with the authentication type  Later versions of MySQL are configured with the MySQL plugin  auth socket   maybe you ve noticed the  run mysqld mysqld sock file on your system and wondered about it   The plugin uses the SO PEERCRED option provided by the library auth socket so  You can revert back to password authentication if desired simply by create update of the password  Showing both ways and options below to make clear  CREATE USER  valerie   localhost  IDENTIFIED WITH auth socket  CREATE USER  valerie   localhost  IDENTIFIED BY  password

User · Answer

Here is the best way to set your root password   Source Link Step 3 is working perfectly for me   Commands for You        sudo mysql   SELECT user authentication string plugin host FROM mysql user    ALTER USER  root   localhost  IDENTIFIED WITH mysql native password BY  password     FLUSH PRIVILEGES    SELECT user authentication string plugin host FROM mysql user    exit      Now you can use the Password for the root user is  password            mysql -u root -p   CREATE USER  sammy   localhost  IDENTIFIED BY  password     GRANT ALL PRIVILEGES ON   TO  sammy   localhost  WITH GRANT OPTION    FLUSH PRIVILEGES    exit      Test your MySQL Service and Version   systemctl status mysql service sudo mysqladmin -p -u root version

User · Answer

Under MYSQL 5 7  If you are using mysql for development purpose  just     1 kill mysql       sudo service mysql stop   2 start mysql under --skip-grant-tables mode      sudo mysqld safe --skip-grant-tables    and  further  you could try to change the user table under  skip-grant-table  mode  however I failed    so  this is just a workaround

User · Answer

I realize that this is an old thread  but I thought I d update it with my results   Alex  it sounds like you installed MySQL server via the meta-package  mysql-server   This installs the latest package by reference  in my case  mysql-server-5 5   I  like you  was not prompted for a MySQL password upon setup as I had expected  I suppose there are two answers   Solution  1  install MySQL by it s full name     sudo apt-get install mysql-server-5 5   Or  Solution  2  reconfigure the package       sudo dpkg-reconfigure mysql-server-5 5   You must specific the full package name  Using the meta-package  mysql-server  did not have the desired result for me   I hope this helps someone     Reference  https   help ubuntu com 12 04 serverguide mysql html

User · Answer

It is actually very simple  You don t have to go through a lot of stuff  Just run the following command in terminal and follow on-screen instructions   sudo mysql secure installation

User · Answer

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  amp    mysql -u root   mysql gt  use mysql  mysql gt  update user set authentication string PASSWORD  YOUR-NEW-ROOT-PASSWORD   where User  root   mysql gt  flush privileges  mysql gt  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 gt  update user set password PASSWORD  YOUR-NEW-ROOT-PASSWORD   where User  root

User · Answer

There is a simple solution   MySql 5 7 comes with anonymous user so you need to reconfigure MySQL server   You can do that with this command  try to find temp pass   grep  temporary password   var log mysqld log   then   sudo mysql secure installation   On this link is more info about mysql 5 7  https   dev mysql com doc refman 5 7 en linux-installation-yum-repo html

User · Answer

sudo mysql -u root ALTER USER  root   localhost  IDENTIFIED WITH mysql native password BY  YOUR PASSWORD HERE   FLUSH PRIVILEGES   mysql -u root -p   and it works

User · Answer

For RHEL-mysql 5 5    etc init d mysql stop   etc init d mysql start --skip-grant-tables   mysql gt  UPDATE mysql user SET Password PASSWORD  newpwd   WHERE User  root    mysql gt  FLUSH PRIVILEGES   mysql gt  exit   mysql -uroot -pnewpwd  mysql gt

User · Answer

MySQL 5 5 on Ubuntu 14 04 required slightly different commands as recommended here   In a nutshell   sudo  etc init d mysql stop sudo  usr sbin mysqld --skip-grant-tables --skip-networking  amp  mysql -u root   And then from the MySQL prompt  FLUSH PRIVILEGES  SET PASSWORD FOR root  localhost    PASSWORD  password    UPDATE mysql user SET Password PASSWORD  newpwd   WHERE User  root   FLUSH PRIVILEGES    And the cited source offers an alternate method as well

User · Answer

Hmm Mysql 5 7 13 to reset all I did was     sudo service mysql stop To stop mysql    mysqld safe --skip-grant-tables  amp  Start mysql    mysql -u root  Just like the correct answer  Then all I did was do what  eebbesen did   mysql gt  SET PASSWORD FOR root  localhost    PASSWORD  NEW-password-HERE     Hope it helps anyone out there

[mysql] How do I find out my root MySQL password?

Examples related to mysql

Examples related to ubuntu