Recently I discovered that I had lost all root privileges on my MySQL (MariaDB) server. Now there are a number of reasons that this may have occurred. It’s possible that I pulled the privileges from the root user and set up a different one for security reasons and simply forgot. Or perhaps I was mucking around with PHPMyAdmin and broke somether. I usually make a note in my Joplin instance whenever I change anything because there can to be a lot of time between instances of my looking in on something. Enough so that I simply can’t remember where I am with a specific server or VM. In this case, though, I failed to do so. Or it’s just borked for no reason.

What happened was that while I was adding a new WordPress instance for my C4Pi project, I discovered that I couldn’t do anything with the SQL server. This wasn’t a simple lost password issue either. I could log in as root, but any commands to modify or create a database, or any server directives tossed errors.

Hmmm.

Now, normally I would stop the server, restart it with the skip-grant-tables parameter, and fix whatever is broken, usually fir me, a password issue. The procedure differs from the Linux norm for a Windows 10-based instance of the server though. Thus, two issues: 1) how to start MariaDB in such a manner that I can fix things, and 2) how to restore the missing rights to root.

Step 1. MariaDB daemon

Open an admin DOS prompt by typing ‘cmd’ in the search box in the taskbar and selecting ‘run as Administrator.’

net stop mysql
cd "c:\program files\MariaDB 10.3\bin" 
mysqld.exe -u root --skip-grant-tables

With the daemon now running, open another DOS prompt with admin rights

cd "c:\program files\MariaDB 10.3\bin" 
mysql
use mysql;

Adapted from: https://jovicailic.org/2012/04/reset-forgotten-mysql-root-password-under-windows/

Step 2: Restore root rights

UPDATE mysql.user
SET Select_priv = 'y',
Insert_priv = 'y',
Update_priv = 'y',
Delete_priv = 'y',
Create_priv = 'y',
Drop_priv = 'y',
Reload_priv = 'y',
Shutdown_priv = 'y',
Process_priv = 'y',
File_priv = 'y',
Grant_priv = 'y',
References_priv = 'y',
Index_priv = 'y',
Alter_priv = 'y',
Show_db_priv = 'y',
Super_priv = 'y',
Create_tmp_table_priv = 'y',
Lock_tables_priv = 'y',
Execute_priv = 'y',
Repl_slave_priv = 'y',
Repl_client_priv = 'y',
Create_view_priv = 'y',
Show_view_priv = 'y',
Create_routine_priv = 'y',
Alter_routine_priv = 'y',
Create_user_priv = 'y',
Event_priv = 'y',
Trigger_priv = 'y',
Create_tablespace_priv = 'y'
WHERE User = 'root';

Close the daemon window. (<ALT><F4>)

Terminate the mysql server, and restart as a service. Flush the privileges.

taskkill /F /IM "mysqld.exe"
net start mysql
cd "c:\program files\MariaDB 10.3\bin" 
mysql -uroot -p
<password>
FLUSH PRIVILEGES;
EXIT

And done.