After several upgrades of MySQL on my FreeBSD 12.1 Server and, in particular, upgrading from MySQL 5.6 to MySQL 5.7 caused the MySQL 5.7 Server not to start. Reviewing the MySQL .err log file, I decided to remove all the data under /var/db/mysql! Of course, backups and snapshots were made. After removing the data structure under /var/db/mysql, one must re-establish MySQL’s root password thus prompting this post.

Although not a great source of inspiration or knowledge presented in this post, but it is more for my recollection in the future!

Steps for resetting your MySQL instance in FreeBSD 12.1:

root@xxx:/ # service mysql-server stop
root@xxx:/ # cd /var/db/mysql/
root@xxx:/var/db/mysql # rm -Rf *
root@xxx:/var/db/mysql # sysrc mysql_args="--skip-grant-tables"
root@xxx:/var/db/mysql # service mysql-server start
root@xxx:/var/db/mysql # mysql -u root
root@localhost [(none)]> FLUSH PRIVILEGES;
root@localhost [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new password';
root@localhost [(none)]> exit
root@xxx:/var/db/mysql # service mysql-server stop
root@xxx:/var/db/mysql # sysrc -x mysql_args="--skip-grant-tables"
root@xxx:/var/db/mysql # service mysql-server start

Checking your password:

root@xxx:/var/db/mysql # mysql -u root -p
Enter password: 'new password'
root@localhost [(none)]> exit

Summary:

It’s not very often one should feel the need to wax your MySQL Instance, but if you do, ensure backup and snapshots of the data are taken first.