On 6/21/20 7:23 PM, Daniel Black wrote:
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 :-/
The skip-grant-tables preventing standard modification is a bit horrible still. One day I need to work out why that restriction is there.
it'll certainly be convenient if it _does_ get straightened out.
can you provide an explicit example of how to -- at this puposefully fubar'd stage -- create / init a root user?
$ cat > /tmp/reset.sql (snip)
well that's a certainly useful idea/reminder! would be nice on the wiki/docs (of course, might be there, and I missed it) (clean install & normal start) mysql > SELECT User, Host FROM mysql.global_priv; +-------------+-----------+ | User | Host | +-------------+-----------+ | mariadb.sys | localhost | | mysql | localhost | | root | localhost | +-------------+-----------+ DROP USER `root`@`localhost`; SELECT User, Host FROM mysql.global_priv; +-------------+-----------+ | User | Host | +-------------+-----------+ | mariadb.sys | localhost | | mysql | localhost | +-------------+-----------+ exit verify no access mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) kill systemctl stop mariadb.service killall mysqld killall mysqld_safe gen script cat << 'EOSSL' > /tmp/root_reset.sql CREATE USER `root`@`localhost` IDENTIFIED VIA unix_socket OR ed25519 USING PASSWORD('s...4'); GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION; EOSSL start/init (needed the '--defaults-file', here; otherwise, lots of errors!) sudo -u mysql \ /sbin/mysqld \ --defaults-file=/usr/local/etc/mariadb/my.cnf \ --init-file=/tmp/root_reset.sql & test, the 'new' root user's there mysql -e "SELECT User, Host FROM mysql.global_priv;" +-------------+-----------+ | User | Host | +-------------+-----------+ | mariadb.sys | localhost | | mysql | localhost | | root | localhost | +-------------+-----------+ kill again systemctl stop mariadb.service killall mysqld killall mysqld_safe start normal systemctl start mariadb check, looks good mysql -e " SELECT CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv)) FROM mysql.global_priv WHERE user = 'root'; " +-------------------------------------------------------------------------+ | CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv)) +-------------------------------------------------------------------------+ | root@localhost => { "access": 1073741823, "plugin": "ed25519", "authentication_string": "T...g", "auth_or": [ { "plugin": "unix_socket" }, { } ], "password_last_changed": 1592795308 } | access with mysql -e status | grep -i "Current user:" Current user: root@localhost and mysql -u root -pnotsosecure -e status | grep -i "Current user:" Current user: root@localhost both work. why this ALSO works, mysql -u root -pblah -e status | grep -i "Current user:" Current user: root@localhost i'm unclear. but a different issue ... so, back-in-biz with at least restoring a horked/missing root user. thxalot!