maria-discuss team mailing list archive

[PGNet Dev <pgnet.dev@xxxxxxxxx>]

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?