Changing a MySQL password sounds like it should be one command. It is, mostly. The confusion comes from there being several routes to the same result, and from the fact that which one works depends on whether a password is already set and which version you are running.
Following are the methods to use on both Windows and Unix Environment including Ubuntu, Debian, Redhat, CentOS, Fedora, Arch Linux, SUSE etc.
One thing to get straight before anything else, because it saves a lot of confusion later. The Linux root account and the MySQL root account are completely separate things that happen to share a name. Being root on the server does not make you root in MySQL, and changing one has no effect at all on the other.
Set MySQL Root Password Using mysqladmin
This command ‘mysqladmin’ works whether password is not currently assigned for the root user account. So we will set the password without giving current password.
[root@linuxpathfinder /]# mysqladmin -u root password 'newpassword' [Note: Here, first attemp to set password for root.]
This is the fresh-install case. A brand new MySQL server often has no root password at all, so there is nothing to authenticate against and nothing to supply. Do this early. A MySQL server sitting with a blank root password is the kind of thing that gets found.
Change MySQL User (non-root) Password Using mysqladmin
mysqladmin command is also used to change user’s (non-root) password.
[root@linuxpathfinder /]# mysqladmin -u asrk -pcurrentpassword password 'newpassword'
Same tool, ordinary account. Note that putting a password on the command line means it lands in your shell history and is visible to anyone running ps at the wrong moment. On a shared machine that matters, and omitting the value so you get prompted for it instead avoids the whole problem.
Change MySQL User’s Password Using UPDATE SQL Command
You can also use the SQL Update command to change the user’s password as shown below.
mysql> UPDATE user SET password=PASSWORD('newpassword') WHERE user='asrk';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
Look closely at that output. Two rows matched, not one. That is because MySQL identifies an account by user and host together, so ‘asrk’@’localhost’ and ‘asrk’@’%’ are two separate accounts that both answer to the same name. A WHERE clause on user alone catches every one of them.
Sometimes that is what you want. Often it is not, and it is a good habit to check what exists before running an UPDATE that broad.
Change MySQL Root Password Using mysqladmin
MySQL root password can be changed using mysqladmin command. It should not space between -p and currentpassword as shown below.
[root@linuxpathfinder /]# mysqladmin -u root -pcurrentpassword password 'newpassword'
That detail about the space is not a style preference, it is the single most common reason this command fails. Everywhere else in Unix a flag and its value can be separated. Here they cannot. Put a space after -p and MySQL reads the next word as a database name instead of a password, then asks you to authenticate, and the error it gives you does not point at the real cause.
When you have made a change with new password then make sure can you login with new password, as shown below.
[root@linuxpathfinder /]# mysql -u root -pnewpassword Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.1.25-rc-community MySQL Community Server (GPL) mysql>
Logged in successfully.
Always check. It takes five seconds, and the alternative is finding out the change did not take at the point where you actually need to get in.
Change MySQL Root Password From MySQL UPDATE Command
You will use the SQL Update command to change the password as shown below. First of all, you need to login to MySQL root account using old password.
[root@linuxpathfinder /]# mysql -u root -poldpassword Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.1.25-rc-community MySQL Community Server (GPL) mysql>
Change root Password with UPDATE Command
mysql> UPDATE user SET password=PASSWORD('newpassword') WHERE user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Verify the new MySQL root password
When you will make a change then make sure can you login with new password, as shown below.
[root@linuxpathfinder /]# mysql -u root -pnewpassword Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.1.25-rc-community MySQL Community Server (GPL) mysql>
An UPDATE against the user table edits the grant tables directly, which is why it needs a FLUSH PRIVILEGES afterwards to take effect on connections MySQL has already loaded into memory. mysqladmin does that part for you, which is one reason it is the easier route for a straightforward password change.
Worth being aware of the version this was written against, which the banner above tells you outright: server version 5.1. On that generation the password lives in a column called password and the PASSWORD() function is how you hash it. Later releases changed both, so on MySQL 5.7 and 8.0 the UPDATE shown here will fail with an unknown column error rather than doing anything harmful. The mysqladmin method carried across unchanged, so if you are on a current server and want the shortest path, that is the one to reach for.
If you are starting from nothing rather than changing an existing password, installing MySQL on Linux covers the server setup and the security steps that come with it.
