Bootstrapping MySQL 5.7 on FreeBSD

I tried a fresh install of MySQL 5.7 using ports on FreeBSD stable/10 the other day. All went smoothly until I needed to access the DBMS. The DBMS wouldn’t let me in, demanding a password I hadn’t set myself.

It later turned out that the MySQL root password had been set automatically to a random value with a copy stored in a secured, plain text file:

-rw-------   1 root  wheel  uarch   78B Jan 21 14:34 /root/.mysql_secret

Remove this file if you don’t like your MySQL root password stored on disk, outside the user table. Obviously, you should read this file to learn your (temporary) MySQL root password.

Back to my story: After googling a bit, I decided to use an init file to set the MySQL root password to a known password.

Here’s the init file, remember to adjust the password:

UPDATE mysql.user
  SET authentication_string =
    PASSWORD('somepassword'),
  password_expired = 'N'
  WHERE User = 'root' AND
    Host = 'localhost';

Tell MySQL to use the init file by adding this line to /etc/rc.conf:

mysql_args="--init-file=/root/mysql-init"

Start MySQL the usual way, then stop it.

Remove the above line from /etc/rc.conf. Neutralize or remove the init file.

Continue securing MySQL with this as a minimum:

[mysqld]
bind-address = localhost
local-infile = 0
innodb_file_per_table = 1

Start MySQL as usual and verify all settings.

Remember, MySQL 5.7, upon bootstrapping, sets a random root password with a plain text copy stored in ~/.mysql_secret. An upgrade from earlier versions will leave your passwords alone.