Change and Reset MySQL root Password

Other then the ways specified here to reset and change the root password for mySQL database in the case that the password is forgotten or lost, the following instructions explain in details the alternative way at the last part of the guide, where no additional file needs to be created:

  1. Login as root to the Windows or Unix-like (Unix, Linux or BSD) machine with the MySQL server.
  2. Stop the MySQL server by using either of the following command:

    Linux: /etc/rc.d/init.d/mysql stop
    FreeBSD: /usr/local/etc/rc.d/mysql-server.sh stop

  3. Open the mysql server startup script (i.e. mysql-server.sh – the file executed to start or stop MySQL server.
  4. Add –skip-grant-tables to the end of the line that contains the mysqld_safe command as its parameter.
  5. Start MySQL server with the following command:

    Linux: /etc/rc.d/init.d/mysql start
    FreeBSD: /usr/local/etc/rc.d/mysql-server.sh start

  6. Alternatively, start the MySQL server directly and skip the editing with the following command:

    mysqld_safe –skip-grant-tables &

    Depending on your path environment, you may need to point to the correct directory where mysqld_safe is instead.

  7. Run the following commands to login as the mysql user and connect to mysql user/permission database:

    # mysql -u root mysql

  8. Run the update queries to change the MySQL password:

    mysql> UPDATE user SET Password=PASSWORD(’newrootpassword’) WHERE User=’root’;
    mysql> FLUSH PRIVILEGES;

    Note: Replace newrootpassword with the new root password for MySQL server. Flush Privileges is needed to making the password change effect immediately.

  9. Exit mysql database client by typing exit.
  10. Stop MySQL server with commands listed at step 2.
  11. Open the mysql server startup script edit in step 3 again and remove the –skip-grant-tables parameter that has been added.
  12. Start MySQL server by using command from step 5 or 6.

For Redhat Linux users, use the following instructions as the root user of Redhat Linux machine:

  1. Stop MySQL process by using command:

    # killall mysqld

  2. Start the MySQL server with following options:

    # /usr/libexec/mysqld -Sg –user=root &

  3. Start the MySQL client:

    # mysql

    You should see the following message:

    Welcome to the MySQL monitor. Commands end with ; or g.
    Your MySQL connection id is 1 to server version: 3.xx.xx

    Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.

    mysql>

  4. Use mysql database:

    mysql> USE mysql

    You should see the following message:

    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A

    Database changed

  5. Then, update the password for the root user with the following command:

    UPDATE user SET password=password(”newpassword”) WHERE user=”root”;

    Replace newpassword with your desired password. You should see the following message:

    Query OK, 2 rows affected (0.03 sec)
    Rows matched: 2 Changed: 2 Warnings: 0

    Rows affected may be different, but the Query OK should be there.

  6. Flush the database privileges to reload it in order to make the changes effective:

    mysql> flush privileges;

    You should get the following result:

    Query OK, 0 rows affected (0.02 sec)

  7. Exit the MySQL client by typing exit.
  8. Kill the MySQL server process by typing killall mysqld.
  9. Then start MySQL again:

    /etc/init.d/mysqld start


10 Responses to “Change and Reset MySQL root Password”

  1. Matt J.
    September 20th, 2009 00:19
    10

    I see it worked under Ubuntu for ‘trail’, but it did not work for me.

    On the contrary: even after following Rajesh’s suggestion to use “–skip-grant-tables” instead of “-skip-grant-tables”, even after correcting the erroneous reference to “/etc/rc.d/init.d” for Linux (my Ubuntu has no such directory, it has only “/etc/init.d/”) I get strange error messages by the time I get to step 7.

    It seems either one of two things goes wrong. Either 1) the server process starts, then soon dies or 2) if it finds the process running at all, it still gives me “ERROR 1045 (28000): Access denies for ‘root’@'localhost’ (using password: NO)”

    What has my even more baffled is that I get this last message when trying to start and stop mysql!

    Finally, the instructions to edit mysql are highly misleading: one cannot put “–skip-grant-tables” at the END of the line, since the line ends with redirection and ‘&’. The line now reads:

    “/usr/bin/mysqld_safe –skip-grant-tables > /dev/null 2>&1 &”

    I am running the very latest released Ubuntu, with all updates as of yesterday.

  2. Reset MySQL Root Password (for Windows) « Noel’s Techie Blog
    August 8th, 2009 10:57
    9

    [...] This article was written based on information from http://www.mydigitallife.info/2006/06/06/change-and-reset-mysql-root-password/ to illustrate changing the mysql password on [...]

  3. ap
    March 12th, 2009 17:35
    8

    @trail: Worked like a charm, thanks!

  4. trail
    September 23rd, 2008 17:22
    7

    worked for me on ubuntu, in just one line:

    sudo dpkg-reconfigure mysql-server-5.0

  5. Rajesh Cheethirala
    July 3rd, 2008 05:46
    6

    On ubuntu, command is mysqld_safe –-skip-grant-tables &

    Extra dash is required so mysql can parse it correctly.

  6. Mengubah password root pada MySQL 5.0 di Windows. « Iwan Binanto: “hari-hari ku yang berlalu ..”
    May 5th, 2008 16:52
    5

    [...] judulnya), setelah googling cuma dapat cara untuk mereset dan mengganti password MySQL di UNIX-like di sini. Wah, perlu sedikit modifikasi ni … Berikut cara2 yang saya lakukan: 1. Kill proses MySQL [...]

  7. links for 2007-04-23 | Patrick Kempf
    April 23rd, 2007 08:23
    4

    [...] Change and Reset MySQL root Password » My Digital Life (tags: mysql administration) No Tags Gelesen: 2 / heute: 2 [...]

  8. Help me retrieve my password - Geeks Talk
    December 19th, 2006 14:54
    3

    [...] Re: Help me retrieve my password Try this link… Change and Reset MySQL root Password

  9. Vincitore
    July 9th, 2006 12:05
    2

    I wanna reset my ZXDSL 831 ADSL Modemn
    Plz tell me the way how to reset it
    I need to do that right now

    Best Rgds
    dugger(vincitore)

  10. Reset the Root Password of MySQL Server » My Digital Life
    June 6th, 2006 18:18
    1

    [...] Alternatively, on any platform, mysql client can be used to set the new password, althought it’s less secure way of resetting the password (detailed instruction here): [...]

Leave a Reply

You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Subscribe to comments feature has been disabled. To receive notification of latest comments posted, subscribe to My Digital Life Comments RSS feed or register to receive new comments in daily email digest.
Custom Search

New Articles

Incoming Search Terms for the Article

reset mysql password - mysql change root password - change mysql root password - mysql password reset - mysql password recovery - mysql reset password - ubuntu mysql password - reset mysql root password - how to reset mysql password - ubuntu mysql default password - retrieve mysql root password - retrieve mysql password - reset mysql password linux - change root password mysql - mysql root password reset - mysql reset root password - resetting mysql password - ubuntu mysql root - ubuntu mysql root password - reset password mysql - mysql root password change - Mysql Change password - crack mysql password - linux mysql reset password - ubuntu reset mysql root password - set password mysql - how to change mysql root password - mysql default password - mysql retrieve root password - mysql retrieve password - how to reset password in mysql - forgot mysql password - how to retrieve mysql password - linux mysql password reset - lost mysql password - recover mysql password - mysql root password recovery - reset lost mysql root password - change mysql password - how to change mysql root password in windows - mysql password reset linux - how to change mysql password - mysql set root password - mysql ubuntu password - reset mysql database password - set mysql root password - mysql default password ubuntu - mysql change password flush - mysql changing root password - change password mysql -