MySQL How to reset or change the MySQL root password

Question

How do I change the MySQL root password and username in ubuntu server   Do I need to stop the mysql service before setting any changes    I have a phpmyadmin setup as well  will phpmyadmin get updated automatically

User · Answer

Setting MySQL Password

stop MySQL database service

sudo /etc/init.d/mysql stop

create a new mysqld directory

sudo mkdir /var/run/mysqld/

give mysql user access

sudo chown mysql /var/run/mysqld/

start MySQL in safe mode...

sudo mysqld_safe --skip-grant-tables &

logon to the database server without a password

sudo mysql -u root

use the default mysql database

use mysql;

change the root password

update user set authentication_string=PASSWORD("New_Passwore_Here") where User='root';

Save the changes

flush privileges; exit;
stop MySQL safe_mode and start MySQL default service

sudo /etc/init.d/mysql stop

sudo /etc/init.d/mysql start

log back onto MySQL database using the root new password

sudo mysql -u root -p

*** Source : https://websiteforstudents.com/resetting-mysql-root-password-on-ubuntu-16-04-17-10-and-18-04-lts/

User · Answer

This solution belongs to the previous version of MySQL  By logging in to MySQL using socket authentication  you can do it   sudo mysql -u root   Then the following command could be run    ALTER USER  root   localhost  IDENTIFIED WITH mysql native password BY  password     Details are available here

User · Answer

What worked for me  Ubuntu 16 04  mysql 5 7    Stop MySQL  sudo service mysql stop   Make MySQL service directory   sudo mkdir  var run mysqld   Give MySQL user permission to write to the service directory   sudo chown mysql   var run mysqld   Start MySQL manually  without permission checks or networking   sudo mysqld safe --skip-grant-tables --skip-networking  amp    On another console  log in without a password   mysql -uroot mysql   Then   UPDATE mysql user SET authentication string PASSWORD  YOURNEWPASSWORD    plugin  mysql native password  WHERE User  root  AND Host  localhost   EXIT    Turn off MySQL   sudo mysqladmin -S  var run mysqld mysqld sock shutdown   Start the MySQL service normally   sudo service mysql start

User · Answer

If you know the  root  users password  log in to mysql with that credentials  Then execute the following query to update the password   ALTER USER  root   localhost  IDENTIFIED BY  new passowrd

User · Answer

At first run this command   sudo mysql   and then you should check which authentication method of your MySQL user accounts use So run this command   SELECT user authentication string plugin host FROM mysql user    now you can see something like this already     ------------------ ------------------------------------------- ----------------------- -----------    user               authentication string                       plugin                  host         ------------------ ------------------------------------------- ----------------------- -----------    root                                                           auth socket             localhost     mysql session       THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE   mysql native password   localhost     mysql sys           THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE   mysql native password   localhost     debian-sys-maint    CC744277A401A7D25BE1CA89AFF17BF607F876FF   mysql native password   localhost    ------------------ ------------------------------------------- ----------------------- -----------    in the table that is in the above   you can see that all of your mysql users accounts status  amp  if you have set a password for root account before you see mysql native password in plugin column instead auth socket  All in all for change your root password you should run    ALTER USER  root   localhost  IDENTIFIED WITH mysql native password BY  password      Be sure to change password to a strong password of your choosing  Then for reload your server to put your new changes into effect run this   FLUSH PRIVILEGES    So again check the authentication methods which has employed by your mysql   by this command   SELECT user authentication string plugin host FROM mysql user    and now the output is     ------------------ ------------------------------------------- ----------------------- -----------    user               authentication string                       plugin                  host         ------------------ ------------------------------------------- ----------------------- -----------    root                3636DACC8616D997782ADD0839F92C1571D6D78F   mysql native password   localhost     mysql session       THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE   mysql native password   localhost     mysql sys           THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE   mysql native password   localhost     debian-sys-maint    CC744277A401A7D25BE1CA89AFF17BF607F876FF   mysql native password   localhost    ------------------ ------------------------------------------- ----------------------- -----------    as you can see in the grant table your root account has mysql native password   now you can exit MYSQL shell   exit    That s it just you should restart mysql by sudo service mysql restart  Now you can login to mysql as a root account with your password easily

User · Answer

I haven t seen the official steps recommended by the MySQL 8 0 guide  which were the only ones that worked for me  Here s a summary of those steps    Stop the MySQL server if it is running  Look in  var lib mysql    var run mysqld   or  usr local mysql data  to find the pid file with the server s process ID  Generally the file begins with either mysqld or your system s host name and ends with  pid  Replace mysql-data-directory and host name that you just found  in the following command      sudo kill  sudo cat  mysql-data-directory host name pid   This command will create a text file in  tmp mysql-init with the SQL statement and makes the mysql user the owner  Replace in the command MyNewPass with your own password      echo  ALTER USER  root   localhost  IDENTIFIED BY  MyNewPass     gt   tmp mysql-init  amp  amp  sudo chown mysql  tmp mysql-init  Start the MySQL server by running the following command on the command line  After this the password is updated and you can close the server again with CTRL C     sudo mysqld --user mysql --init-file  tmp mysql-init  amp   Remove the temporary file with your password     sudo rm  tmp mysql-init

User · Answer

This  works like charm I did it for Ubuntu 16 04  Full credit to below link as I got it from there    https   coderwall com p j9btlg reset-the-mysql-5-7-root-password-in-ubuntu-16-04-lts  1   Stop MySQL  sudo service mysql stop   Make MySQL service directory      sudo mkdir  var run mysqld  Give MySQL user permission to write to the service directory   sudo chown mysql   var run mysqld   Start MySQL manually  without permission checks or networking   sudo mysqld safe --skip-grant-tables --skip-networking  amp    Log in without a password   mysql -uroot mysql   Update the password for the root user  make sure at atleast root account gets updated by the below query  make some selection and check the existing values if you like  UPDATE mysql user SET  authentication string PASSWORD  YOURNEWPASSWORD     plugin  mysql native password  WHERE User  root   EXIT    Turn off MySQL   sudo mysqladmin -S  var run mysqld mysqld sock shutdown   Start the MySQL service normally   sudo service mysql start

User · Answer

In my case this option helped   https   stackoverflow com a 49610152 13760371 Thank you  Rahul  except for the following moment  when I try entered command  UPDATE mysql user SET authentication string PASSWORD  YOURNEWPASSWORD    plugin  mysql native password  WHERE User  root  AND Host       the console issued a warning  1681  password  is deprecated and will be removed in a future release  cured with this command  UPDATE mysql user SET authentication string CONCAT      UPPER SHA1 UNHEX SHA1  NEWPASSWORD        plugin  mysql native password  WHERE User  root  AND Host  localhost    MySQL version 5 7 X My variant  1   gt  sudo service mysql stop 2   gt  sudo mkdir  var run mysqld 3   gt  sudo chown mysql   var run mysqld 4   gt  sudo mysqld safe --skip-grant-tables --skip-networking  amp  5   gt  mysql -uroot mysql 6   gt  UPDATE mysql user SET authentication string CONCAT      UPPER SHA1 UNHEX SHA1  NEWPASSWORD        plugin  mysql native password  WHERE User  root  AND Host  localhost   7   gt   q  8   gt  sudo mysqladmin -S  var run mysqld mysqld sock shutdown 9   gt  sudo service mysql start

User · Answer

Stop MySQL sudo service mysql stop Make MySQL service directory  sudo mkdir  var run mysqld Give MySQL user permission to write to the service directory  sudo chown mysql   var run mysqld Start MySQL manually  without permission checks or networking  sudo mysqld safe --skip-grant-tables --skip-networking  amp    5 Log in without a password  mysql -uroot mysql  6 Update the password for the root user   UPDATE mysql user SET authentication string PASSWORD  YOURNEWPASSWORD    plugin  mysql native password  WHERE User  root  AND Host      EXIT    Turn off MySQL  sudo mysqladmin -S  var run mysqld mysqld sock shutdown Start the MySQL service normally  sudo service mysql start

User · Answer

To update the  root  Mysql user password you must have in mind that you will need of super user permissions for that  If you have super user privilegies  try the following commands   MySQL 5 7 6 and later  sudo su service mysql stop mysql -u root ALTER USER  root   localhost  IDENTIFIED BY  MyNewPass    q  exit mysql -u root -p MyNewPass   MySQL 5 7 5 and earlier  sudo su service mysql stop mysql -u root SET PASSWORD FOR  root   localhost    PASSWORD  MyNewPass     q  exit mysql -u root -p MyNewPass

User · Answer

When you use MySQL s PASSWORD   on the system where you want to change the password  it  can cause the password turn up in a MySQL log in cleartext  source   Keeping them  their backups etc  as secure as the password sounds like nightmare to me  so I rather like to do it as follows    On your local machine  run this with your password    mysql -u someuser -p  lt   lt  echo  SELECT PASSWORD  mypass        Note the space in front to prevent it from turning up in the bash history  for other distros than Ubuntu  this might work differently     source   On your server machine  execute the following command to change its MySQL root password  replace myhash with your password s hash as printed by the first command    mysql -u root -p  lt   lt  echo  SET PASSWORD FOR root localhost    myhash      Optionally  let s be a bit paranoid  On your local machine  clear your terminal screen with clear and purge your virtual terminal scrollback  to hide the cleartext password appearing in the command above

User · Answer

If you would like to change the MySQL root password  in a terminal enter   sudo dpkg-reconfigure mysql-server-5 5  The MySQL daemon will be stopped  and you will be prompted to enter a new password

User · Answer

The only method that worked for me is the one described here  I am running ubuntu 14 04   For the sake of clarity  these are the steps I followed    sudo vim  etc mysql my cnf Add the following lines at the end    mysqld   skip-grant-tables sudo service mysql restart mysql -u root use mysql select   from mysql user where user    root   - Look at the top to determine whether the password column is called password or authentication string UPDATE mysql user set  password field from above    PASSWORD  your new password   where user    root  and host    localhost   - Use the proper password column from above FLUSH PRIVILEGES  exit sudo vim  etc mysql my cnf Remove the lines added in step 2 if you want to keep your security standards  sudo service mysql restart   For reference   https   dev mysql com doc refman 5 7 en resetting-permissions html

User · Answer

For Ubuntu 18 04 and mysql version 14 14 Distrib 5 7 22 follow the below step to reset the mysql password   Step 1  sudo systemctl stop mysql   Step 2  sudo systemctl edit mysql   This command will open a new file in the nano editor  which you ll use to edit MySQL s service overrides  These change the default service parameters for MySQL  This file will be empty  so add the following content    Service  ExecStart  ExecStart  usr sbin mysqld --daemonize --pid-file  run mysqld mysqld pid --skip-grant-tables --skip-networking   Step 3  sudo systemctl daemon-reload sudo systemctl start mysql   Step 4  sudo mysql -u root   Step 5  FLUSH PRIVILEGES    Step 6  UPDATE mysql user SET authentication string   PASSWORD  new password   WHEREuser    root     Step 7  UPDATE mysql user SET plugin   mysql native password  WHERE user    root     Step 8  sudo systemctl revert mysql   and finally   sudo systemctl restart mysql   Now enjoy

User · Answer

To reset or change the password enter sudo dpkg-reconfigure mysql-server-X X  X X is mysql version you have installed i e  5 6  5 7  and then you will prompt a screen where you have to set the new password and then in next step confirm the password and just wait for a moment  That s it

User · Answer

Change the MySQL root password   This method exposes the password to the command-line history  these commands should be run as root      Login through mysql command line tool   mysql -uroot -poldpassword  Run this command   SET PASSWORD FOR root  localhost    PASSWORD  newpassword       or    Run this command  which sets a password for the current user   root  for this case        SET PASSWORD   PASSWORD  newpassword

User · Answer

Set   change   reset the MySQL root password on Ubuntu Linux  Enter the following lines in your terminal    Stop the MySQL Server  sudo  etc init d mysql stop Start the mysqld configuration  sudo mysqld --skip-grant-tables  amp   In some cases  you ve to create the  var run mysqld first   sudo mkdir -v  var run mysqld  amp  amp  sudo chown mysql  var run mysqld  Run  sudo service mysql start Login to MySQL as root  mysql -u root mysql Replace YOURNEWPASSWORD with your new password   UPDATE   mysql user SET   Password   PASSWORD  YOURNEWPASSWORD   WHERE   User    root   FLUSH PRIVILEGES  exit        Note  on some versions  if password column doesn t exist  you may want to try     UPDATE user SET authentication string password  YOURNEWPASSWORD   WHERE user  root     Note  This method is not regarded as the most secure way of resetting the password  however  it works   References    Set   Change   Reset the MySQL root password on Ubuntu Linux How to Reset the Root Password

User · Answer

The official and easy way to reset the root password on an ubuntu server     If you are on 16 04  14 04  12 04   sudo dpkg-reconfigure mysql-server-5 5   If you are on 10 04   sudo dpkg-reconfigure mysql-server-5 1   If you are not sure which mysql-server version is installed you can try    dpkg --get-selections   grep mysql-server   Updated notes for mysql-server-5 7  Note that if you are using mysql-server-5 7 you can not use the easier dpkg-reconfigure method shown above    If you know the password  login and run this    UPDATE mysql user SET authentication string PASSWORD  my-new-password   WHERE USER  root   FLUSH PRIVILEGES    Alternatively  you can use the following    sudo mysql secure installation   This will ask you a series of questions about securing your installation  highly recommended   including if you want to provide a new root password    If you do NOT know the root password  refer to this Ubuntu-centric write up on the process     See for more info   https   help ubuntu com 16 04 serverguide mysql html https   help ubuntu com 14 04 serverguide mysql html

User · Answer

This is the solution for me  I work at Ubuntu 18 04  https   stackoverflow com a 46076838 2400373  But is important this change in the last step   UPDATE mysql user SET authentication string PASSWORD  YOURNEWPASSWORD    plugin  mysql native password  WHERE User  root  AND Host  localhost

User · Answer

I am sharing the step by step final solution to reset a MySQL password in Linux  Ubuntu    Reference taken from blog  dbrnd com   Step 1  Stop MySQL Service   sudo stop mysql   Step 2  Kill all running mysqld   sudo killall -9 mysqld   Step 3  Starting mysqld in Safe mode   sudo mysqld safe --skip-grant-tables --skip-networking  amp    Step 4  Start mysql client  mysql -u root   Step 5  After successful login  please execute this command to change any password   FLUSH PRIVILEGES    Step 6  You can update mysql root password    UPDATE mysql user SET Password PASSWORD  newpwd   WHERE User  root     Note  On MySQL 5 7  column Password is called authentication string   Step 7  Please execute this command   FLUSH PRIVILEGES

User · Answer

for mysql 5 6 this command works and you can set password through the wizard   sudo dpkg-reconfigure mysql-server-5 6

User · Answer

If you know your current password  you don t have to stop mysql server  Open the ubuntu terminal  Login to mysql using   mysql - username -p   Then type your password  This will take you into the mysql console  Inside the console  type    gt  ALTER USER  root   localhost  IDENTIFIED BY  new password     Then flush privileges using    gt  flush privileges    Then you are all done

User · Answer

Instead of resetting the password there is a work around on the local machine if you have setup phpmyadmin to connect without giving the password or username  Check this out by starting mysql  apache etc  I have xampp installed in my local machine  So starting the xampp will start all the necessary services  Now going to http   localhost phpmyadmin shows me all the databases  This confirms that you have saved the username and passsword in the config file of phpmyadmin which can be found in the phpmyadmin install location  If you have xampp installed the phpmyadmin folder can be found in the root folder of xampp installation  Search for the word password in the config inc php file  There you will find the password  and username

User · Answer

Most of the answers on this topic are outdated  two major changes have occurred in MySQL up until the writing of this answer   1- the  Password  field in the user table has been replaced by  authentication string  column   2- the  Password  encryption function   PASSWORD  of some text   is deprecated    Please refer to this link for further information dev mysql com doc refman 8 0 en resetting-permissions html

User · Answer

You can use this command   UPDATE mysql user SET Password PASSWORD  newpwd   WHERE User  root     after that please use flush   FLUSH PRIVILEGES

User · Answer

As mysql documentation on the password   function says       This function was removed in MySQL 8 0 11    This invalidates pretty much all existing answers for mysql v8 0 11 and newer   Per mysql documentation the new generic way to reset the root password is as follows      The preceding sections provide password-resetting instructions   specifically for Windows and Unix and Unix-like systems    Alternatively  on any platform  you can reset the password using the   mysql client  but this approach is less secure        Stop the MySQL server if necessary  then restart it with the   --skip-grant-tables option  This enables anyone to connect without a password and with all privileges  and disables account-management   statements such as ALTER USER and SET PASSWORD  Because this is   insecure  if the server is started with the --skip-grant-tables   option  it enables --skip-networking automatically to prevent remote   connections       Connect to the MySQL server using the mysql client  no password is   necessary because the server was started with --skip-grant-tables   shell gt  mysql       In the mysql client  tell the server to reload the grant tables so that account-management statements work   mysql gt  FLUSH PRIVILEGES        Then change the  root   localhost  account password  Replace the password with the password that you want to use    To change the password for a root account with a different host name   part  modify the instructions to use that host name   mysql gt  ALTER USER  root   localhost  IDENTIFIED BY  MyNewPass         You should now be able to connect to the MySQL server as root using the   new password  Stop the server and restart it normally  without the   --skip-grant-tables and --skip-networking options

User · Answer

Echoing rogerdpack s comment  if you don t know the MySQL root password and you don t care about MySQL data settings  you can reinstall it and reset the root s password as follows   sudo apt-get remove --purge mysql-server mysql-client mysql-common sudo rm -rf  var lib mysql sudo apt-get install -y mysql-server mysql-client    During the installation  you can choose the root s password

User · Answer

You don t need all this  Simply log in   mysql -u root -p  Then change the current user s password as the mysql gt  prompt   mysql gt  set password password  the new password    mysql gt  flush privileges

User · Answer

Change the MySQL root password  In Simpler way  All these commands should be run as root    Login through MySQL command line tool using your old password   Step-1  mysql -uroot -p your old password    Then run below command   Step-2      SET PASSWORD FOR root  localhost    PASSWORD  your new password      Method-2  First login using your old password using above command   Run this command  which sets a password for the current user   SET PASSWORD   PASSWORD  your new password      Above command is for the current user  If you want to change the password for other user  you can put the user name instead of   root

User · Answer

I had to go this route on Ubuntu 16 04 1 LTS   It is somewhat of a mix of some of the other answers above - but none of them helped  I spent an hour or more trying all other suggestions from MySql website to everything on SO  I finally got it working with   Note  while it showed Enter password for user root  I didnt have the original password so I just entered the same password to be used as the new password   Note  there was no  var log mysqld log only  var log mysql error log  Also note this did not work for me   sudo dpkg-reconfigure mysql-server-5 7  Nor did   sudo dpkg-reconfigure --force mysql-server-5 5  Make MySQL service directory   sudo mkdir  var run mysqld  Give MySQL user permission to write to the service directory   sudo chown mysql   var run mysqld  Then    kill the current mysqld pid run mysqld with sudo  usr sbin mysqld  amp  run  usr bin mysql secure installation  Output from mysql secure installation     root myServer     usr bin mysql secure installation      Securing the MySQL server deployment       Enter password for user root       VALIDATE PASSWORD PLUGIN can be used to test passwords   and improve security  It checks the strength of password   and allows the users to set only those passwords which are   secure enough  Would you like to setup VALIDATE PASSWORD plugin       Press y Y for Yes  any other key for No  no   Using existing password for root    Change the password for root     Press y Y for Yes  any other key for No    y      New password       Re-enter new password    By default  a MySQL installation has an anonymous user    allowing anyone to log into MySQL without having to have   a user account created for them  This is intended only for   testing  and to make the installation go a bit smoother    You should remove them before moving into a production   environment       Remove anonymous users   Press y Y for Yes  any other key for No    y   Success       Normally  root should only be allowed to connect from    localhost   This ensures that someone cannot guess at   the root password from the network       Disallow root login remotely   Press y Y for Yes  any other key for No    y   Success       By default  MySQL comes with a database named  test  that   anyone can access  This is also intended only for testing    and should be removed before moving into a production   environment       Remove test database and access to it   Press y Y for Yes  any other key for No    y         Dropping test database      Success    Removing privileges on test database      Success          Reloading the privilege tables will ensure that all changes   made so far will take effect immediately       Reload privilege tables now   Press y Y for Yes  any other key for No    y   Success       All done

User · Answer

You can try these some steps to reset mysql 5 7 root password    Stop Mysql Service 1st   sudo  etc init d mysql stop    Login as root without password  sudo mysqld safe --skip-grant-tables  amp    After login mysql terminal you should need execute commands more   use mysql      UPDATE mysql user SET authentication string PASSWORD  solutionclub3   G    plugin  mysql native password  WHERE User  root     flush privileges    sudo mysqladmin -S  var run mysqld mysqld sock shutdown   After you restart your mysql server If you still facing error you must visit    Reset MySQL 5 7 root password Ubuntu 16 04

User · Answer

I faced problems with ubuntu 18 04 and mysql 5 7  this is the solution  Try restart mysql-server before execution the comands   sudo service mysql restart   MYSQL-SERVER    5 7  sudo mysql -uroot -p USE mysql  UPDATE user SET authentication string PASSWORD  YOUR PASSWORD   WHERE User  root   UPDATE user SET plugin  mysql native password   FLUSH PRIVILEGES  quit    MYSQL-SERVER  lt  5 7  sudo mysql -uroot -p USE mysql  UPDATE user SET password PASSWORD  YOUR PASSWORD   WHERE User  root   UPDATE user SET plugin  mysql native password   FLUSH PRIVILEGES  quit

User · Answer

when changing resetting the MySQL password the following commands listed above did not help  I found that going into the terminal and using these commands is pointless  instead use the command sudo stop everything  DELETE SYSTEM 32 for windows if that helps

User · Answer

You can easily change the mysql password if deployed on xampp through provided phpadmin gui   phpMyAdmin - gt  User Accounts - gt  Edit Privileges  Select the intended user  - gt  Change Password  Tab

[mysql] MySQL: How to reset or change the MySQL root password?

Examples related to mysql

Examples related to ubuntu

Examples related to phpmyadmin