MikeL's FreeBSD howto - mysql

mysql-based apps suddenly would fail to start:
PHP Warning: mysqli_connect(): (HY000/2002): No such file or directory in /dsk2/web/perennialvintners.com/html/weatherstation/pmi.php on line 264
Line 264 is simply:
    $gDBLink = mysqli_connect("localhost", "[user]", "[pswd]", "[dbname]");
During the time frame when this began happening, I have been messing with the system config, but nothing related to sql, only imap/pop/smtp stuff. I did not have any disk filled problems or any such thing. I have an app that runs hourly, and suddenly the cron job started having error output. There may have been a clean shutdown/reboot in there, so it's possible it was a config change I made a long time ago, and it only just showed up now due to the reboot, but I'm pretty sure I've rebooted several times since the last time I messed with sql which was probably a year ago.

Anyways, turned out mysql server was not running. When I tried to start it, in the log at /var/db/mysql/[hostname].err, I would get:
2023-01-16T19:10:10.789733Z 0 [ERROR] I/O error reading the header from the binary log
2023-01-16T19:10:10.789748Z 0 [ERROR] Can't init tc log
2023-01-16T19:10:10.789759Z 0 [ERROR] Aborting
With some googling, I found that I could simply clobber the mysql-bin.* files from the /var/db/mysql directory. This scared the crap out of me, as the data in mysql is precious. I renamed each of those files to ".bak", then did a service mysql-server start. The daemon did start correctly, and my data appears to be intact, thank god!

Some of the pages that I found, discussed changing the configuration to not make these files at all, and discussed how to use the "purge" command to clean them up - of course this latter can't be done until the daemon is running again so I was unable to use this to fix the problem. I also didn't want to make any config changes as I used to have a running system. Now that I'm back up and running, I will try to get back to these issues in the future, but of course at this writing I don't have the time, I used it all up firefighting getting things running at all again!

Start rant: What a fucking ridiculous error message! "No such file". I know, it is an accurate message, it is saying that the socket file is indeed not present, but how are we supposed to know that without context? This is my biggest beef by far with most all computer software, and that is poor or misleading error messages. If the message had been "mysql daemon is not running", I would have known just where to start. If it could have at least had the decency to say "/var/db/mysql/mysql.sock not found", it's not ideal, but at least you have an idea of where to start. End rant.

Did a pkg_upgrade of system. Now mysql server won't start.
cd /var/db/mysql
Check error log at full-system-name dot err (brix.vintners.net.err). Search for "error". Found:
[ERROR] Unix socket lock file is empty /var/db/mysql/mysql.sock.lock..
[ERROR] Unable to setup unix socket lock file.
I check, there is a file there already - maybe that's in the way?
mv mysql.sock.lock mysql.sock.lock.bak
Now it starts. Sigh.

As of mysql8, the PASSWORD() function has been removed. If you are using MYSQL PASSWORD, you will be completely fucked. You must fix your code NOW, before mysql is updated beneath you, to use an alternative mechanism for saving passwords. My old pswd tables happened to be a varchar(64) which is large enough to fit newer pswds. I then added code to simply check the field length; if short, then must be old, so must update and resave. If you are having to update the table width, you might as well go higher then the 256 bit, I only used this as I was able to get away with not ALTERing the table.
    $OldPswdCmd = "PASSWORD('".$_REQUEST[$gActionLoginPswd]."')";
    $NewPswdCmd = "SHA2('".$_REQUEST[$gActionLoginPswd]."', 256)";
    $PswdCmd = $OldPswdCmd;
    $MustUpdateOldPswd = (strlen($mysql->Pswd) <= 41);
    if (!$MustUpdateOldPswd) {
        $PswdCmd = $NewPswdCmd;
Note that you will have to have every user login before the upgrade to mysql8 in order for the auto update to happen.

[20200428] - FreeBSD12.1-RELEASE

I did pkg_upgrade. Was at mysql57-server-5.7.something-or-other, and it did an upgrade to Afterwards, server would not start.
Message from mysql57-server-5.7.29_1:

Remember to run mysql_upgrade the first time you start the MySQL server
after an upgrade from an earlier version.
I couldn't do the mysql_upgrade as the server would not start. There was no info from the start command. Looking in the log got me closer:
  2020-04-28T17:45:38.377045Z 0 [ERROR] I/O error reading the header from the binary log, errno=175, io cache code=0
Googling for this got me several different pages, most of which said to simply clobber the binary index file. So I renamed it out in case I needed it back.
cd /var/db/mysql/
mv mysql-bin.index mysql-bin.index.bak
service mysql-server start
This time it works, yay!
Now do the update:
mysql_upgrade --verbose -u [user] -p
It says it completed successfuly, again, yay!

BTW this is FreeBSD 11.

pkg install mysql80-server

  Message from mysql80-server-8.0.19:

  There is no initial password for first time use of MySQL.
  Keep in mind to reset it to a secure password.

  MySQL80 has a default %%ETCDIR%%/my.cnf,
  remember to replace it with your own
  or set `mysql_optfile="$YOUR_CNF_FILE` in rc.conf.

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:

Now go to /usr/local/etc/mysql
If there isn't already one:
cp my.cnf.sample my.cnf
Now edit my.cnf; there are two places in the file you'll need to modify, just search for "socket"; one in the client section, the other in the server section. Change these both to point to /var/db/mysql/mysql.sock.
Once you've installed, start it via:
service mysql-server start
This is how you will start and stop it henceforth.

[20200329 - old news, skip to mysql_secure_installation]
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.

Old news - see above fix of [20200326]
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:
This will need the root pswd you just set above.

Now look at /var/db/mysql/[full hostname].err and check for hidden problems.
[20200329 - this is ONLY for upgrade to mysql8]
I'm seeing deprecated warnings. Edit /usr/local/etc/my.cnf:
change expire_logs_days = 30
to binlog_expire_logs_seconds = 2592000
and comment out skip_symbolic_links

Note that the program startup log is at:
/var/db/mysql/[full hostname].err
Mine shows warnings about:
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
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 one 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;
mysql -u [username] -p

[20200326] "Factory reset"
Fucking stupidshit backups don't include user info, is that it? I'm still trying to figure this restore shit out. Can't get in, time to just start the fuck over again. Again. Again.
pkg uninstall/reinstall doesn't suffice.
service mysql-server stop
pkg remove mysql80-server
cd /var/db
rm -R mysql
pkg install mysql80-server
This seems to have worked.
Now in mysql;
use mysql;
create blows chunks with "ERROR 1785 (HY000): Statement violates GTID consistency: Updates to non-transactional tables can only be done in ei\ ther autocommitted statements or single-statement transactions, and never in the same statement as updates to transaction\ al tables."
Found somewhere to do this:
set sql_log_bin=0; Now new error "ERROR 1726 (HY000): Storage engine 'MyISAM' does not support system tables. [mysql.user]"
Better? I don't know yet.
Nope. FUCK MYSQL. FUCKING PIECE OF FUCKING HORSESHIT. I'VE SPENT STUPID HOURS OF MY LIFE TRYING TO JUST GET A FUCKING DB BACKED UP AND COPIED ONTO ANOTHER SERVER. WHY IS THIS SO FUCKING HARD? Should be able to say 'backup' one one system, copy the file over, then say 'restore'. FUCK MYSQL. FUCKING SHIT. I guess what this means is that the restore to factory did not work.
FUCK. You want me to fucking low-level format the fucking HD, then reinstall the whole fucking OS? Fuck.

Just did it all again, this time the "factory restore" worked. Still getting stupidfuck gtid error on goddamn grant command though. Fucking bullshit.

Ok, ignore all that previous ranting, I'll clean it up and keep the useful parts later.
RTFM, and of course improvise.
The real problem here is not that I'm doing a backup from one machine, and a restore onto another - it's that I'm also doing a simultaneous in-place upgrade. The old system was mysql5-something-or-other, this new system is mysql8.
After the factory reset, suck in the backup file with:
mysql -u root -p < backup.sql
service mysql-server stop
mysqld_safe --user=mysql --upgrade=FORCE &
Let it run for a few mins, there was no feedback that I observed.
mysqladmin -u root -p shutdown
service mysql-server start
I was now able to do "grant" command.

Here's how it all ends... The PASSWORD() function has been REMOVED in mysql8! This completely destroys all my customer's php apps as they all use this older call for authentication and login. Put simply, I cannot upgrade to mysql8 at this time. I cannot believe that they could do this! I will have to change the code in older scripts to use a newer mechanism, force all my customers to log in and change their password, and only then, upgrade to mysql8. This is BAD. I mean this is really, really, really bad.

After all the previous fucking around...

Gosub "factory reset" above, to remove mysql8 (stop after rm -R of var/db/mysql)
pkg install mysql57-server
Gosub above 20200326 for updating my.cnf. I had to back out the mysql8 changes I had made. You could probably just copy in the sample, then change the two socket lines to /var/db/mysql/mysql.sock from /tmp.
New note - you'll want to make sure that default_password_lifetime = 0

Now be sure to run:

mysql -u root -p --connect-expired-password
alter user 'root'@'localhost' identified by '[your pswd]' password expire never;
Close mysql. Suck in the backup file with:
mysql -u root -p < backup.sql
service mysql-server restart

Copyright © 1995-2023 Mike Lempriere (running on host pedicel)