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.
Insert a new user manually on
- Ubuntu 18.04
service mysql stop mysqld_safe --skip-grant-tables & mysql
mysql terminal (change
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
- Stack Overflow: How can I restore the MySQL root user’s full privileges?
- Some fields are not used anymore but kept for backwards compability.
- In some MySQL/MariaDB versions extra fields should be needed.
- mysql.user Table
- StackExchange: MySQL user password vs authentication_string