There are so many sources for possible solutions to this issue that is is hard to filter out the sense from the nonsense. I finally found a good solution here:
Step 1: Identify the Database Version
$ mysql --version
You’ll see some output like this with MySQL:
$ mysql Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapper
Or output like this for MariaDB:
mysql Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1
Make note of which database and which version you’re running, as you’ll use them later. Next, you need to stop the database so you can access it manually.
Step 2: Stopping the Database Server
To change the root password, you have to shut down the database server beforehand.
You can do that for MySQL with:
$ sudo systemctl stop mysql
And for MariaDB with:
$ sudo systemctl stop mariadb
Step 3: Restarting the Database Server Without Permission Checking
If you run MySQL and MariaDB without loading information about user privileges, it will allow you to access the database command line with root privileges without providing a password. This will allow you to gain access to the database without knowing it.
To do this, you need to stop the database from loading the grant tables, which store user privilege information. Because this is a bit of a security risk, you should also skip networking as well to prevent other clients from connecting.
Start the database without loading the grant tables or enabling networking:
$ sudo mysqld_safe --skip-grant-tables --skip-networking &
The ampersand at the end of this command will make this process run in the background so you can continue to use your terminal.
Now you can connect to the database as the root user, which should not ask for a password.
$ mysql -u root
You’ll immediately see a database shell prompt instead.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
Now that you have root access, you can change the root password.
Step 4: Changing the Root Password
mysql> FLUSH PRIVILEGES;
Now we can actually change the root password.
For MySQL 5.7.6 and newer as well as MariaDB 10.1.20 and newer, use the following command:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
For MySQL 5.7.5 and older as well as MariaDB 10.1.20 and older, use:
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
Make sure to replace
new_password with your new password of choice.
Note: If the
ALTER USER command doesn’t work, it’s usually indicative of a bigger problem. However, you can try
UPDATE ... SET to reset the root password instead.
[IMPORTANT] This is the specific line that fixed my particular issue:
mysql> UPDATE mysql.user SET authentication_string = PASSWORD('new_password') WHERE User = 'root' AND Host = 'localhost';
Remember to reload the grant tables after this.
In either case, you should see confirmation that the command has been successfully executed.
Query OK, 0 rows affected (0.00 sec)
The password has been changed, so you can now stop the manual instance of the database server and restart it as it was before.
Step 5 — Restart the Database Server Normally
First, stop the instance of the database server that you started manually in Step 3. This command searches for the PID, or process ID, of MySQL or MariaDB process and sends
SIGTERM to tell it to exit smoothly after performing clean-up operations. You can learn more in this Linux process management tutorial.
For MySQL, use:
sudo kill `cat /var/run/mysqld/mysqld.pid`
For MariaDB, use:
sudo kill `/var/run/mariadb/mariadb.pid`
Then, restart the service using
For MySQL, use:
sudo systemctl start mysql
For MariaDB, use:
sudo systemctl start mariadb
Now you can confirm that the new password has been applied correctly by running:
mysql -u root -p
The command should now prompt for the newly assigned password. Enter it, and you should gain access to the database prompt as expected.