Solved Set/Reset MySQL Password after the installation.

[Zvoni]

i don't bother with that script.
What i do after a MySQL-Server-Install
1) check my.cnf, that it has everything neccessary (e.g. tablenames in lowercase, auth-plugin is "mysql_native_password" etc.) BEFORE starting the Server the first time!
2) sysrc mysql_args="--initialize-insecure --user=mysql"
Start the MySQL-Server. And wait for it to finish
3) sysrc mysql_args="--skip-grant-tables"
restart the MySQL-Server
4) mysql -uroot (Note: No "-p")
5) "Use mysql;" (Switch to system-DB)
6) "update user set authentication_string='' where user='root';" (Deleting whatever random password was generated)
7) quit
8) sysrc -x mysql_args
restart the MySQL-Server
9) mysql -uroot (again no "-p")
10) "alter user 'root'@'localhost' identified by 'mypassword';"
11) quit
restart MySQL-Server

 

[Mike G]

1. Stop the MySQL server if necessary, then restart it with the --skip-grant-tables option. This enables anyone to connect without a password and with all privileges, and disables account-management statements such as ALTER USER and SET PASSWORD. Because this is insecure, if the server is started with the --skip-grant-tables option, it enables --skip-networking automatically to prevent remote connections.
2. Connect to the MySQL server using the mysql client; no password is necessary because the server was started with --skip-grant-tables:
   shell> mysql
3. In the mysql client, tell the server to reload the grant tables so that account-management statements work:
   mysql> FLUSH PRIVILEGES;
   Then change the 'root'@'localhost' account password. Replace the password with the password that you want to use. To change the password for a rootaccount with a different host name part, modify the instructions to use that host name.
   mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

After hours of wrestling with this, and repeatedly failing because every set of instructions I read either did not spell out exactly how to do it, or else got you to log in to mysql-server instead of the client, your great instructions seem to have worked - thank you so much, one of the troubles with getting to grips with running a server on Freebsd is that there are so many tutorials on line that either only worked on the specific setup of the author, have a quirk that is completely un-necessary and counter-productive (like manually creating a v-host to listen on 445 when certbot will sort all that out properly - and then being instructed to add obsolete files for the certificate that will drop your security rating to a B) or that simply will never work because they are really a cut and paste job by someone who has no idea what they are doing. So good clear and correct advice like this is so much appreciated.