Mysql Mariadb Set Update Password

Nov 30, 2019 / Updated on Nov 30, 2019

Using mysqladmin CLI

If you installed the db with empty password then set the password

sudo mysqladmin password "<<new-password>>"
-- OR
mysqladmin --user=root password "<<new-password>>"

Change the existing password

mysqladmin --user=root --password=<<old-password>> password "<<new-password>>"

Using SET PASSWORD

SET PASSWORD FOR '<<user>>'@'<<hostname|ip>>' = PASSWORD('<<new-password>>');

Using SQL Update

Change the password by the SQL update to the table mysql.users (Execute this query as root)

USE mysql;
UPDATE user SET password=PASSWORD('<<new-password>>') WHERE User='<<user>>' AND Host = '<<hostname|ip>>';
FLUSH PRIVILEGES;

Using ALTER

ALTER USER '<<user>>'@'<<hostname|ip>>' IDENTIFIED BY '<<new-password>>>';
FLUSH PRIVILEGES;

OR

ALTER USER '<<user>>'@'<<hostname|ip>>' IDENTIFIED BY PASSWORD PASSWORD('<<new-password>>>');
FLUSH PRIVILEGES;

The above option will be useful when creating script in which password can be generated using SELECT PASSWORD('<<new-password>>) and use it in query like ALTER USER '<<user>>'@'<<hostname|ip>>' IDENTIFIED BY PASSWORD '<<hash-password>>>')

otherwise both the options have same effect. Either give hashed password using IDENTIFIED BY PASSWORD clause or just give plain string using IDENTIFIED BY clause, mysql will automatically hash the password.

comments powered by Disqus
Categories:
Tags: