Restore a Deleted Root on MySQL/MariaDB

The problem

root user was delete from Mysql/MariaDB, so traditional fixes to update root password doesn't work.

GRANT USER doesn't work without grant tables enabled, and doesn't work after that if the user doesn't have a minimum set of privileges.

The solution

Insert a new user manually on mysql.user table.

Tested enviroment

  • Ubuntu 18.04
  • MariaDB

The code

service mysql stop
mysqld_safe --skip-grant-tables &
mysql

On mysql terminal (change ROOT_PASSWORD):

INSERT INTO user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections, max_user_connections, authentication_string)
VALUES('localhost','root',PASSWORD('ROOT_PASSWORD'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','','0','0','0','0', '');
FLUSH PRIVILEGES;
GRANT ALL ON *.* TO 'root'@'localhost';
exit

Then restart the server:

kill `cat /var/run/mysqld/mysqld.pid`
service mysql restart

References