CODEWITHSUNDEEP
mysqlmariadb
Javide
Javide

Reputation: 2647

How to set root password in MariaDB 10.4 on macOS?

I have just installed MariaDB 10.4 on macOS Mojave and I now need to set a password for the root user. I read on other SO questions that I can achieve this via

sudo mysql -u root
MariaDB [mysql]> UPDATE mysql.user SET authentication_string = PASSOWRD('mypassword') WHERE user = 'root';

but this raises error

ERROR 1348 (HY000): Column 'authentication_string' is not updatable

and if I try

UPDATE mysql.user SET Password=PASSWORD('mypassword') WHERE User='root';

raises error

ERROR 1348 (HY000): Column 'Password' is not updatable

This

set password for 'root'@'localhost' = 'mypassword';

throws

ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number

and this

alter user 'root'@'localhost' identified with mysql_native_password by 'mypassword';

throws

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version...

How can I solve this?

Upvotes: 4

Views: 20216

Answers (5)

BliteKnight
BliteKnight

Reputation: 196

For 10.4, I had to run this command to force the password prompt for the root user:

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('YourSecretPassword');
FLUSH PRIVILEGES;

Looks like the install process has changed to using unix_socket authentication plugin by default https://mariadb.com/kb/en/authentication-from-mariadb-104/

Upvotes: 13

Deepak3301086
Deepak3301086

Reputation: 487

If update command give error like column 'password' is not updatable then use alter command instead.

ALTER USER 'root'@'localhost' IDENTIFIED BY '1234';

then

flush privileges;

then update this password in config.inc.php file so that you can access http://localhost/phpmyadmin

Upvotes: 5

Alon G
Alon G

Reputation: 3373

Just to add something here - sometimes you must run flush privileges; before you try to set the password. so the command will be:

flush privileges;
SET PASSWORD FOR root@localhost = PASSWORD("some_password");
flush privileges;

Upvotes: 3

kmarsh
kmarsh

Reputation: 1398

For the mysql user:

SET password for 'mysql'@'localhost' = password('mypassword');

Upvotes: 0

Georg Richter
Georg Richter

Reputation: 7476

Since MariaDB 10.4 mysql.global_priv has replaced mysql.user. Latter one is now a view, which can't be updated anymore.

Since you already logged in as root, keep it simple and change your password with:

SET PASSWORD=PASSWORD('mypassword')

Since the user can't be authenticated over socket anymore, the authentication method will be automatically set to mysql_native_password.

Upvotes: 9

Related Questions