• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

MySQL : how to set (change) user password

By admin

There are several ways to set a MySQL user password:
– CREATE USER…IDENTIFIED BY
– GRANT…IDENTIFIED BY
– SET PASSWORD
– mysqladmin password
– UPDATE grant tables (not recommended)

For all methods, the privileges required to change a password are:
1. All users can change their own password.
2. Users with UPDATE on mysql database can update passwords for other users using SET PASSWORD or in MySQL 5.7 and later using ALTER USER.
3. If the read_only option is enabled, in addition to the privileges above, SUPER is required.

SET PASSWORD

The most common way to change an existing account’s password without changing any of its privileges is to use the SET PASSWORD statement. For example, to set the password for tom on the local host to new_password, use the following statement:

mysql> SET PASSWORD FOR tom@localhost = PASSWORD('new_password');
Query OK, 0 rows affected (0.00 sec)

If you are logged in as a non-root user and your user does not have the UPDATE privilege for the mysql database, you can change only your own password. Do this by using the SET PASSWORD statement without the FOR clause:

mysql> SET PASSWORD = PASSWORD('new_password');

MySQL 5.7 and later (SET PASSWORD)

In MySQL 5.7 and later it is not necessary to use the PASSWORD() function:

MySQL 5.7> SET PASSWORD FOR myuser@localhost = 'my_new_password';
Query OK, 0 rows affected (0.01 sec)

To change the password for the current user:

MySQL 5.7> SET PASSWORD = 'my_new_password';
Query OK, 0 rows affected (0.00 sec)

mysqladmin

The mysqladmin utility allows you to change the password for a user you know the existing password for. The usage is:

$ mysqladmin --defaults-file=etc/my.cnf --user=tom --password my_new_password
Enter password:
New password:
Confirm new password:

ALTER USER

The ALTER USER statement can also be used to change the password of an existing user:

MySQL 5.7> ALTER USER tom@localhost IDENTIFIED BY 'my_new_password';
Query OK, 0 rows affected (0.01 sec)

ALTER USER also supports manipulating several users with one statement, for example:

MySQL 5.7> ALTER USER myuser@localhost, tom@localhost IDENTIFIED BY 'my_new_password';
Query OK, 0 rows affected (0.01 sec)
Note : It is generally recommended to use different passwords for each account!

Filed Under: mysql

Some more articles you might also be interested in …

  1. How to Configure Multiple MySQL Servers On One System Using mysqld_multi
  2. “expect” script to provide password to mysql_config_editor
  3. How to Restart a MySQL Cluster without downtime
  4. Managing MySQL Using Systemd As A Non Root User
  5. How To Purge Audit Logs in MySQL
  6. How To Skip a Transaction on MySQL replication slave When GTIDs Are Enabled
  7. How To Reset MySQL 8.0 Root Password On Windows
  8. What is the MySQL Enterprise Monitor?
  9. Granting All Privileges On All databases Except One Specific Table in MySQL
  10. MySQL : Stored Procedure For Rotating the Audit Log

You May Also Like

Primary Sidebar

Recent Posts

  • How to Disable IPv6 on Ubuntu 18.04 Bionic Beaver Linux
  • How to Capture More Logs in /var/log/dmesg for CentOS/RHEL
  • Unable to Start RDMA Services on CentOS/RHEL 7
  • How to rename a KVM VM with virsh
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary