Changing a password in a MySQL database can be done in several ways, including with the mysqladmin tool, via a query, or using various tools such as phpMyAdmin or MySQL Workbench. I'll focus on the first two solutions.

Changing the password with mysqladmin

Only users with the SUPER privilege granted can use the mysqladmin tool ( It allows the user to use administrative actions such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and the mysqladmin commands ). On a freshly installed MySQL server, root is such a user; if we're aware of what we're doing, we can always grant such permissions with a query:

Otherwise we'll be treated to the following refusal: mysqladmin: Can't turn off logging; error: 'Access denied; you need the SUPER privilege for this operation' From the system shell we issue the command: mysqladmin -u root -p'old_password' password new_password and additionally reload the privileges with the command: mysqladmin -u root -p flush-privileges.

Changing the password with an SQL query

In this case we'll consider two variants of changing the password as a regular user without special privileges, including without access to the mysql database and the root user.

• Changing your own password as a regular user
• We log into the mysql console: mysql -u username -p
• Then in the mysql console we issue the command: SET PASSWORD = PASSWORD('new_password');
• After logging out, we can log back in using the new password.
• Of course the root user can also change their password this way.
• Changing the root user's password
• We log into the mysql console: mysql -uroot -p
• We can change the password in several different ways:

## method I:
mysql> SET PASSWORD = PASSWORD('new_password');
## method II - in this case we additionally specify the host:
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
## method III - in this case we also additionally specify the host:
mysql> UPDATE mysql.user SET Password=PASSWORD('new_password') WHERE User='root' AND Host='localhost';
mysql> flush privileges;

• Changing another user's password
• We log into the mysql console as usual

## method I - local user:
mysql> UPDATE mysql.user SET Password=PASSWORD('new_password') WHERE User='username' AND Host='localhost';
mysql> flush privileges;
## method II - remote user:
mysql> UPDATE mysql.user SET Password=PASSWORD('new_password') WHERE User='username' AND Host='hostname';
mysql> flush privileges;

One last note about the host option. In the default mysql configuration, mysql takes reverse DNS into account. This should be kept in mind when creating a user, because providing the IP address the user will connect from will fail. We can check the reverse of an IP address like this, e.g:

host -t PTR 8.8.8.8
8.8.8.8.in-addr.arpa domain name pointer google-public-dns-a.google.com. (without the trailing dot)

Of course we can force mysql to take the IP address into account instead. In the mysql server's configuration file (my.cnf), in the [mysqld] section, let's add the option skip-name-resolve and, of course, restart mysql.