Reset/recover MySQL root password in freebsd

This is a little memory note for my self and hopefully it can help someone else how to reset MySQL root password.

It happens that we are asked to do some work on our customers dedicated servers and when that happens it’s not that uncommon that there is not a single person alive that has the root password for the MySQL server. When that happen it’s good to know how to reset it 😉

This post is about how simple it’s to reset MySQL’s root password under FreeBSD.

The first step is to start MySQL without it’s grant tables.

Under FreeBSD you pass your mysql startup parameters in /etc/rc.conf

Add the following to /etc/rc.conf. We add –skip-networking to because we do not want someone from the outside to connect without passwords to our server.

1
2
mysql_enable="YES"
mysql_args="--skip-grant-tables --skip-networking"

And then restart MySQL with the following command.

1
> /usr/local/etc/rc.d/mysql-server restart

When MySQL comes back online it’s running without grant tables which means that you do not have to use a password to connect to it (no one has to).

Simply connect as root (without password) and issue the following SQL query.

1
2
3
> mysql -u root mysql

mysql> UPDATE mysql.USER SET Password=PASSWORD('<Your new password>') WHERE USER='root';

Now we are almost done. Just comment the skip-grant-tables line from rc.conf and restart MySQL and you should have a brand new root password.

For for other *nix users the process is the same except from how you pass your MySQL params to MySQL at startup. You can also start MySQL manually with something like this.

1
<path to safe_mysqld>/safe_mysqld --user=mysql --skip-grant-tables --skip-networking &

Cheers!

Posted in Databases, Hosting, MySQL at November 24th, 2008. Trackback URI: trackback Tags: , , Written by: 
  • Lukman

    thanks a lot man

  • imc

      Table ‘mysql.USER’ doesn’t exist
    ;(

  • https://twitter.com/#!/stjernstrom stjernstrom

    Try:

      use mysql

    and then

      show tables

  • imc

     I ran:

    cd /usr/local/ && bin/mysql_install_db && bin/mysql_secure_installation and all the correct databases were put in place :)

    Cheers!

  • https://twitter.com/#!/stjernstrom stjernstrom

    Nice! Glad it worked out :)

  • digioz

    Excellent information that saved me a lot of time. Thanks for sharing!

    Pete

It's past my bedtime is using WP-Gravatar