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!
