I did a wonderful thing of deleting all root users from mysql by accident. That kind of a day.
However before I panic I figured I would use this method here from searching online that didn't seem so bad.
"Add 'skip-grant-tables' to my.cnf under the [mysqld] section
restart mysql"
then insert a new root user using "INSERT INTO mysql.user…"
When I do this I get an error I cannot find much information about
The target table user of the INSERT is not insertable-into
Does anyone have any ideas about this and how can I re-create a root user?
at first I thought this might have something to do with virtualmin because they talk about it here but I did this from CLI into mysql and it still says the same error
my environment
Centos7
Webmin version 1.940
Usermin version|1.780
Virtualmin version 6.08
mysql Ver 15.1 Distrib 10.4.11-MariaDB, for Linux (x86_64) using readline 5.1
Best Answer
I finally found my solution (big thanks to @RolandoMySQLDBA on this thread here).
The only problem I had with his answer was that
WITH GRANT OPTION;
was missing from/var/lib/mysql/InitFile.sql
:Do not forget to remove the file when you are done and update to remove the init-file string you added to
/etc/my.cnf
.I am still unsure why MariaDB was outputting error
INSERT is not insertable-into
and could not find any documentation explaining this was a MariaDB problem.