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!
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
