How to Set or Change the MySQL Root Password with mysqladmin

Quick commands for setting or changing the MySQL root password with mysqladmin, and for creating a user with privileges.

Before you start

(Added later.)

You need shell access to the server running MySQL. Run the commands as root or with sudo if your user cannot reach the MySQL server.
mysqladmin is a command-line tool that comes with the MySQL client. You run it from the Linux shell, not from inside the mysql> prompt. The CREATE USER and GRANT statements further down are run inside the mysql> prompt.

Set the root password for the first time

mysqladmin -u root password newpassword

This works when root has no password yet, for example straight after installing MySQL on older systems. Replace newpassword with your own password.

Change or update the root password

The original command was:

mysqladmin -u root -p 'oldpassword' password 'newpassword'

Corrected: with a space after -p, mysqladmin does not read 'oldpassword' as the password. It prompts for a password and then treats oldpassword as a command, which fails. Either put the old password straight after -p with no space:

mysqladmin -u root -p'oldpassword' password 'newpassword'

or, better, leave it out so you are prompted for the current password and it is not typed on the command line:

mysqladmin -u root -p password 'newpassword'

Check the new password works

(Added later.)

mysql -u root -p

Enter the new password when prompted. If you get the mysql> prompt, the change worked. Type quit to leave.

MySQL: create a user and grant privileges

The original was:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'newpassword'; GRANT ALL PRIVILEGES ON database.* 'username'@'localhost' WITH GRANT OPTION;

Corrected: the GRANT statement was missing the word TO. The working version, one statement per line:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'newpassword';
GRANT ALL PRIVILEGES ON database.* TO 'username'@'localhost' WITH GRANT OPTION;

What each part means:

'username'@'localhost': the user can only connect from the same machine. Use a specific IP address or host name instead of localhost to allow connections from another machine.
database.*: all tables in the database called database. Replace it with your real database name.
WITH GRANT OPTION: the user can also give their own privileges to other users. Leave it out unless the user really needs it.

To check what a user has been given:

SHOW GRANTS FOR 'username'@'localhost';

Notes for today

MySQL 5.7.6 and later, including 8.0, use ALTER USER to change a password. Log in with mysql -u root -p, then run:
```
ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
```
The same statement works for any user. The old SET PASSWORD = PASSWORD('...') form no longer works in MySQL 8.0.
GRANT no longer creates users or sets passwords in MySQL 8.0. Always run CREATE USER first, then GRANT, as in the corrected example above.
No root password after install. On many current Linux distributions, MySQL or MariaDB is set up so the root account logs in through the operating system (for example sudo mysql) rather than with a password, or a temporary password is written to the error log at install time. Running mysql_secure_installation after installing is the usual way to set things up. Check the documentation for your version: https://dev.mysql.com/doc/
Security. Any password typed on the command line, as in mysqladmin ... password 'newpassword', is saved in your shell history (for example ~/.bash_history) and can be seen by other users in the process list while the command runs. Use -p without a value so you are prompted, and where you can, change the password with ALTER USER from inside the mysql> prompt instead.