MikeL's FreeBSD howto - mysql

(01-Feb-2018)

Seriously, I wasted DAYS trying to get mysql to work. Here's the snippets I've managed to put together after all the reinstalls and shit...

BTW this is FreeBSD 11.

pkg install mysql56-server mysql56-client
NOTE! The installer LIES to you!. Ignore the comment about "$HOME/.mysql_secret" - THIS FILE IS NOT CREATED!

Edit /etc/rc.conf and add:
mysql_enable="YES"
mysql_optfile="/var/db/mysql/my.cnf"

Once you've installed, start it via:
service mysql-server start
This is how you will start and stop it henceforth.

Now do:
mysqladmin -u root password 'yourpassword'
You can ignore the warning about insecure -- this is the only time you will EVER use a password on a command line -- the command line can be seen be anyone on the system using "ps". We can get away with it here as we're running a program that completes in less than a second. Always use -p WITHOUT the pswd when using mysql.

Everything SHOULD work now, but no matter how hard I try, EVERYTHING insists that there's no /tmp/mysql.sock. THERE SHOULDN'T BE, DAMNIT! All the web searching and trying stuff, I never found REAL a way that actually fixes this problem. So -- kludge it:
ln -s /var/db/mysql/mysql.sock mysql.sock

Now be sure to run:
mysql_secure_installation
This will need the root pswd you just set above.

Note that the program startup log is at:
/var/db/mysql/[full hostname].err
Mine shows warnings about:
TIMESTAMP with implicit DEFAULT
FEDERATED disabled
no auto.cnf
no UUID found
no private_key.pem
no public_key.pem.

Now loading my sqldump file from the older system:
mysql -u root -p
[password]
source [backup file from other system];
(buncha stuff goes by, no errors spotted)
flush privileges;

At this point, all should work, but instead, the damn thing starts blowing chunks and restarting over and over again. Evidently it doesn't like something in the old backup file. I'll have to go through and backup once db at a time, transfer each, and load each until I find the bad one, assuming of course it's only one that it doesn't like.
I don't have enough time in my life for this FUCKING BULLSHIT.

Start all over -- AGAIN.
pkg remove mysql56-server mysql56-client
cd /var/db
rm -R mysql
888 GOTO 10


Ok, I've isolated the crash - it happens upon bringing in the old dbdump. The moment the 'mysql' table comes in, your life is over. So now I'm sourcing in each table one at a time and creating the user manually.
Start by going to the source code (presumably the website php code), find the expected user/pswd/dbname.
mysql -u root -p -D [dbname] < [mysqldumpfile]
[root password]
Note that some instructions I found show "identified: on the "create" command - this did not seem to work for me.
create user '[username]'@'localhost';
grant all on [dbname].* to '[username]'@'localhost' identified by '[userpswd]';
flush privileges;
Ctrl-D
mysql -u [username] -p
[userpswd]

Copyright © 1995-2018 Mike Lempriere