i've installed distro-packaged MDB mariadb -V mariadb Ver 15.1 Distrib 10.4.13-MariaDB, for Linux (x86_64) using readline 5.1 on lsb_release -rd Description: Arch Linux Release: rolling uname -rm 5.7.3-arch1-1 x86_64 i'd like to modify & delete/restore my 'root' user; an exercise in recovery ... starting with a clean install, mysql -e " SELECT User, Host, HEX(authentication_string) FROM mysql.user;" " +-------------+-----------+----------------------------+ | User | Host | HEX(authentication_string) | +-------------+-----------+----------------------------+ | mariadb.sys | localhost | | | root | localhost | 696E76616C6964 | | mysql | localhost | 696E76616C6964 | +-------------+-----------+----------------------------+ mysql -e " SELECT CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') FROM mysql.user WHERE user = 'root'; " +----------------------------------------------------------+ | CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') | +----------------------------------------------------------+ | SHOW GRANTS FOR 'root'@'localhost'; | +----------------------------------------------------------+ adding grants & mod'ing, mysql > REVOKE ALL ON *.* FROM 'root'@'localhost'; REVOKE GRANT OPTION ON *.* FROM 'root'@'localhost'; REVOKE ALL ON *.* FROM 'root'@'%'; REVOKE GRANT OPTION ON *.* FROM 'root'@'%'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION; ALTER USER 'root'@'localhost' IDENTIFIED VIA unix_socket; ALTER USER 'root'@'%' IDENTIFIED VIA unix_socket; FLUSH PRIVILEGES; exit; works as expected mysql -e " SELECT CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') FROM mysql.user WHERE user = 'root'; " +----------------------------------------------------------+ | CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') | +----------------------------------------------------------+ | SHOW GRANTS FOR 'root'@'%'; | | SHOW GRANTS FOR 'root'@'localhost'; | +----------------------------------------------------------+ mysql -e " SELECT CONCAT('plugin: ', plugin, ' \'', user, '\'@\'', host, '\';') FROM mysql.user WHERE user = 'root'; " +-----------------------------------------------------------------+ | CONCAT('plugin: ', plugin, ' \'', user, '\'@\'', host, '\';') | +-----------------------------------------------------------------+ | plugin: unix_socket 'root'@'localhost'; | | plugin: unix_socket 'root'@'%'; | +-----------------------------------------------------------------+ mysql -e " SELECT User, Host, HEX(authentication_string) FROM mysql.user; " +-------------+-----------+----------------------------+ | User | Host | HEX(authentication_string) | +-------------+-----------+----------------------------+ | mariadb.sys | localhost | | | root | localhost | | | mysql | localhost | 696E76616C6964 | | root | % | | +-------------+-----------+----------------------------+ mysql -e status -------------- mysql Ver 15.1 Distrib 10.4.13-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 24 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 10.4.13-MariaDB-log Arch Linux Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /run/mysqld/mysqld.sock Uptime: 15 min 9 sec Threads: 8 Questions: 93 Slow queries: 0 Opens: 19 Flush tables: 1 Open tables: 13 Queries per second avg: 0.102 -------------- if i stop, then safe-restart mdb systemctl stop mariadb.service killall mysqld killall mysqld sleep 10 mysqld_safe \ --defaults-file=/usr/local/etc/mariadb/my.cnf \ --skip-grant-tables \ --skip-networking & mysql -u root mysql MariaDB [(mysql)]> server still looks good, status; -------------- mysql Ver 15.1 Distrib 10.4.13-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 9 Current database: mysql Current user: root@ SSL: Not in use Current pager: /usr/bin/less Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 10.4.13-MariaDB-log Arch Linux Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /run/mysqld/mysqld.sock Uptime: 14 sec Threads: 8 Questions: 37 Slow queries: 0 Opens: 34 Flush tables: 1 Open tables: 30 Queries per second avg: 2.642 -------------- if I *destroy* the 'root' user user mysql; DELETE FROM mysql.user WHERE user = 'root' AND host = 'localhost' OR host = '%'; flush privileges; SELECT User, Host, HEX(authentication_string) FROM mysql.user; +-------------+-----------+----------------------------+ | User | Host | HEX(authentication_string) | +-------------+-----------+----------------------------+ | mariadb.sys | localhost | | | mysql | localhost | 696E76616C6964 | +-------------+-----------+----------------------------+ then, get a pass hash, select password('testpass'); +-------------------------------------------+ | password('testpass') | +-------------------------------------------+ | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 | +-------------------------------------------+ & attempt to re-init & insert the 'root' user insert into `user` VALUES('localhost','root','*00E247AC5F9AF26AE0194B41E1E769DEE1429A29','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','Y','Y','Y','','','','',0,0,0,0,'',''); it fails ERROR 1471 (HY000): The target table user of the INSERT is not insertable-into what different/additional steps are required to recreate a deleted root user?