Set MySQL password from the command line

If your MySQL root user’s password is currently empty (not set) and you want to add one to disable access without password, use the following commands.

Open MySQL my entering the below in your command line:

# Open MySQL mysql
Code language: PHP (php)
# Set the new password UPDATE mysql.user SET authentication_string=PASSWORD('EnterYourNewPasswordHere') WHERE user='root'; # Refresh FLUSH PRIVILEGES; # Exit quit
Code language: PHP (php)

Here, mysql.user is the database which has the list of all the MySQL users. So the command breaks down as updating the user list database (UPDATE mysql.user), setting the new password (SET authentication_string=PASSWORD('EnterYourNewPasswordHere') for the user called root (WHERE user='root').

FLUSH PRIVILEGES command refreshes the table which keeps a record of users and their passwords. This step is important for the change to take effect.

Now your password should be set. To login to MySQL now, run:

mysql -u root -p

You’ll be prompted to enter your password, and you can now login with the new password you have setup.

Running just mysql command without -u root -p now will return an Access Denied error.

Older MySQL Versions

These above commands are for MySQL version 5.7.x and above. In older versions of MySQL, the field that keeps a record the passwords is called ‘password’ and not ‘authentication_string’. So, the command will have to be changed to:

UPDATE mysql.user SET password=PASSWORD('EnterYourNewPasswordHere') WHERE user='root';
Code language: JavaScript (javascript)

To check your MySQL version, run the following in the command line:

mysql -V

You’ll get a response similar to mysql Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using EditLine wrapper. In this sample, 5.7.25 is your MySQL version.


Posted

in

by

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *