On 6/21/20 5:47 PM, Daniel Black wrote:
what different/additional steps are required to recreate a deleted root user?
There are perfectly usable SHOW CREATE USER (https://mariadb.com/kb/en/show-create-user/) to get the SQL to create a user, and CREATE USER (https://mariadb.com/kb/en/create-user/) like what SHOW CREATE USER outputs, it is a portable, future safe way to recreate users that isn't dependent on however structure MariaDB uses internally.
DROP USER (https://mariadb.com/kb/en/drop-user/) is for removing users.
FLUSH PRIVILEGES (https://mariadb.com/kb/en/flush/) isn't need when you use any proper SQL to create/modify/drop users.
(clean install) systemctl start mariadb mysql > SELECT User, Host FROM mysql.global_priv; +-------------+-----------+ | User | Host | +-------------+-----------+ | mariadb.sys | localhost | | mysql | localhost | | root | localhost | +-------------+-----------+ SHOW CREATE USER; +----------------------------------------------------------------------------------------------------+ | CREATE USER for root@localhost | +----------------------------------------------------------------------------------------------------+ | CREATE USER `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket | +----------------------------------------------------------------------------------------------------+ DROP USER `root`@`localhost`; SELECT User, Host FROM mysql.global_priv; +-------------+-----------+ | User | Host | +-------------+-----------+ | mariadb.sys | localhost | | mysql | localhost | +-------------+-----------+ exit this^ is the stage at which i'd get a oops-i-deleted-my-root-user instance for 'fixing' ... systemctl restart mariadb mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) systemctl stop mariadb.service killall mysqld killall mysqld_safe sleep 5 mysqld_safe \ --defaults-file=/usr/local/etc/mariadb/my.cnf \ --skip-grant-tables \ --skip-networking & mysql -u root > CREATE USER `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket; ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement so, atm, i can't access the 'normal' running server without root user, and can't create the root user when server's running '--skip-grant-tables'. i guess i'm missing the 'perfectly usable' part :-/ can you provide an explicit example of how to -- at this puposefully fubar'd stage -- create / init a root user?