There are a lot of posts around telling you how to update mysql root password. "Simple!," they cry, "just start mysqld_safe, and use mysqladmin …" But wait, what's that? You don't HAVE mysqld_safe? Well the chances are, you've got one of these new fangled systemd systems, where everything is ass backwards.
Then you went and tried to run mysqld as root didn't you? That didn't work either. Read on …
So the trick is, you don't need mysqld_safe any more, as you can control the –skip-grant-tables directly from systemctl. First of all you can take a look at what's in the systemctl environment with
systemctl show-environment
I just had LANG and PATH settings in mine, and nothing to do with MYSQL, so it was no problem to use set-environment to add the skip-grant-tables option. I was doing this on a live server, and didn't want the downtime, so I put it all in a script, below, but if you can afford some downtime, then you can type the commands one by one. When you get to the mysql bit, you can put in your chosen password: my script just replaces the root password with the word 'blank', and then I went and changed it manually when the database was back up and running again.
#!/bin/bash
# MySQL Root Password Reset Script. For Systemd systems.
SYSTEMCTL=$(which systemctl)
# If you're typing manually start here, replacing $SYSTEMCTL with /usr/bin/systemctl or whatever it is on your system
$SYSTEMCTL stop mysqld.service
$SYSTEMCTL set-environment MYSQLD_OPTS="--skip-grant-tables "
$SYSTEMCTL start mysqld.service
mysql -u root -e "UPDATE mysql.user SET authentication_string = PASSWORD('blank') WHERE User = 'root' AND Host = 'localhost';"
$SYSTEMCTL stop mysqld.service
$SYSTEMCTL unset-environment MYSQLD_OPTS
$SYSTEMCTL start mysqld.service
# This just to check its running OK
$SYSTEMCTL status mysqld.service
echo "Try logging in with 'mysql -uroot -p' and the password 'blank'"
A few footnotes.
1) On my system the validate password options module was running, and at a quite high level, so I had to include Upper case, lower case, and special characters in the password before it was accepted.
2) If you're copying the script wholesale make sure you clean up all the inverted commas and quote-marks that WordPress may have destroyed.
3) This was mysql 5.7 on Centos. So the SQL is "SET authentication_string" not "SET password" any more. Because they like changing things.